| 网站首页 | 业界新闻 | 群组 | 交易 | 人才 | 下载频道 | 博客 | 代码贴 | 编程论坛
大量收QQ微信精准粉/交友粉,非诚勿扰千里之行 始于足下
共有 2653 人关注过本帖, 2 人收藏
标题:C#简易读取和写入Excel 代码示例
只看楼主 加入收藏
qq1023569223
Rank: 19Rank: 19Rank: 19Rank: 19Rank: 19Rank: 19
来 自:湖南科技大学
等 级:贵宾
威 望:26
帖 子:2753
专家分:13404
注 册:2010-12-22
结帖率:100%
  问题点数:0  回复次数:3   
C#简易读取和写入Excel 代码示例
C#简易读取和写入Excel 示例,有基本的用法,供初学者使用。附件请用VS2015打开。

程序代码:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
//using System.Data.OleDb;
using System.Diagnostics;
using myExcel = Microsoft.Office.Interop.Excel;

namespace excelAppWithDataset
{
    public partial class Form1 : Form
    {
        private string excelfile = string.Empty;

        public Form1()
        {
            InitializeComponent();
        }

        //Fill datagridview with the data in Excel file.
        private void getDataFromExcel()
        {
            //Open a new Excel.exe.
            myExcel.Application excelApp = new myExcel.Application();

            //Open a Excel file.
            System.Reflection.Missing none = System.Reflection.Missing.Value;
            myExcel.Workbook mybook = excelApp.Workbooks.Open(this.excelfile, none, none, none, none, none, none, none, none, none, none, none, none, none, none);

            //Get the first sheet.
            myExcel._Worksheet sheet = (myExcel._Worksheet)mybook.Worksheets[1];
            //myExcel._Worksheet sheet = (myExcel._Worksheet)mybook.Worksheets.Item[1];

            
//Query the total number of rows and columns.
            int rows = sheet.UsedRange.Cells.Rows.Count;
            int cols = sheet.UsedRange.Cells.Columns.Count;
            
            //Add columns to datagridview first.
            for(int i=1;i<= cols;i++)
            {
                this.dgvExcelData.Columns.Add("", i.ToString());
                //Set this to enable change row/column selection mode.
                this.dgvExcelData.Columns[i - 1].SortMode = DataGridViewColumnSortMode.NotSortable;
            }
            
            //Fill data to datagridview.
            for (int i = 1; i <= rows; i++)
            {
                this.dgvExcelData.Rows.Add();// Add row

                for (int j = 1; j <= cols; j++)
                {
                    myExcel.Range rng = (myExcel.Range)sheet.Cells[i, j];
                    this.dgvExcelData.Rows[i-1].Cells[j-1].Value = rng.Value2;
                }
            }

            //Close all.
            mybook.Close();
            excelApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            excelApp = null;
            mybook = null;
            //Force to quit Excel.exe.
            this.killExcel();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.Text = "excelAppWithDataset";
            this.groupBox1.Text = "Data View";
            this.btnOpen.Text = "Open...";
            this.btnBeginEdit.Text = "Start Edit";
            this.btnEndEdit.Text = "End Edit";
            this.btnSave.Text = "Save";
            this.btnDelete.Text = "Delete";
            this.btnRowCol.Text = "RowSelection/ColumnSelection";
            this.btnExit.Text = "Exit";

            this.dgvExcelData.ReadOnly = true;
            this.dgvExcelData.AllowUserToOrderColumns = false;           
            this.dgvExcelData.AllowUserToDeleteRows = false;
            this.dgvExcelData.AllowUserToAddRows = false;

            this.btnEndEdit.Enabled = false;
            this.btnDelete.Enabled = false;
            this.btnSave.Enabled = false;
            this.btnBeginEdit.Enabled = false;
            this.btnRowCol.Enabled = false;      
        }

        private void btnOpen_Click(object sender, EventArgs e)
        {
            OpenFileDialog o = new OpenFileDialog();
            o.CheckFileExists = true;
            o.Filter = "Excel 2007 file(*.xlsx)|*.xlsx";
            o.Multiselect = false;

            if(o.ShowDialog()==DialogResult.OK)
            {
                try
                {
                    //Clear datagridview if load data the second time.
                    this.dgvExcelData.Rows.Clear();
                    this.dgvExcelData.Columns.Clear();

                    this.excelfile = o.FileName;
                    //Load data to datagridview.
                    this.getDataFromExcel();
                    //Enable the btnBeginEdit-button after load file.
                    this.btnBeginEdit.Enabled = true;
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        //The function to kill Excel.exe.
        private void killExcel()
        {
            try
            {
                Process[] ps = Process.GetProcessesByName("EXCEL");
                foreach (Process p in ps)
                {
                    p.Kill();
                }
            }catch { }
        }
        
        //Stop edit the datagridview,Readonly=true.
        private void btnEndEdit_Click(object sender, EventArgs e)
        {
            this.dgvExcelData.ReadOnly = true;
            this.dgvExcelData.AllowUserToOrderColumns = false;
            this.dgvExcelData.AllowUserToDeleteRows = false;
            this.dgvExcelData.AllowUserToAddRows = false;

            this.btnEndEdit.Enabled = false;
            this.btnBeginEdit.Enabled = true;
            this.btnDelete.Enabled = false;
            this.btnSave.Enabled = true;
            this.btnRowCol.Enabled = false;
        }
        
        //Start edit the datagridview,Readonly=false.
        private void btnBeginEdit_Click(object sender, EventArgs e)
        {
            this.dgvExcelData.ReadOnly = false;
            this.dgvExcelData.AllowUserToOrderColumns = true;
            this.dgvExcelData.AllowUserToDeleteRows = true;
            this.dgvExcelData.AllowUserToAddRows = true;

            this.btnBeginEdit.Enabled = false;
            this.btnEndEdit.Enabled = true;
            this.btnDelete.Enabled = true;
            this.btnSave.Enabled = false;
            this.btnRowCol.Enabled = true;
        }

        //Change select mode,full-row-select or full-column-select.
        private void btnRowCol_Click(object sender, EventArgs e)
        {
            if(this.dgvExcelData.SelectionMode==DataGridViewSelectionMode.ColumnHeaderSelect)
            {
                this.dgvExcelData.SelectionMode = DataGridViewSelectionMode.RowHeaderSelect;
            }
            else
            {
                this.dgvExcelData.SelectionMode = DataGridViewSelectionMode.ColumnHeaderSelect;
            }
        }

        //Delete columns or rows.
        private void btnDelete_Click(object sender, EventArgs e)
        {
            if(this.dgvExcelData.SelectionMode==DataGridViewSelectionMode.RowHeaderSelect)  //Delete rows
            {
                if(this.dgvExcelData.SelectedRows.Count>0)  //If datagridview has selected rows
                {
                    if(MessageBox.Show("Are you sure to delete row(s)?","Info",MessageBoxButtons.OKCancel,MessageBoxIcon.Question)==DialogResult.OK) //Ask again before delete
                    {
                        foreach(DataGridViewRow row in this.dgvExcelData.SelectedRows)
                        {
                            this.dgvExcelData.Rows.Remove(row);
                        }
                    }
                    else
                    {
                        return;
                    }
                }
            }
            else
            {
                if(this.dgvExcelData.SelectedColumns.Count>0)  //Delete columns if datagridview has selected columns
                {
                    if (MessageBox.Show("Are you sure to delete column(s)?", "Info", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK) //Ask again before delete
                    {
                        foreach (DataGridViewColumn col in this.dgvExcelData.SelectedColumns)
                        {
                            this.dgvExcelData.Columns.Remove(col);
                        }
                    }
                    else
                    {
                        return;
                    }
                }
            }
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            if(this.dgvExcelData.Columns.Count==0)  //If there is nothing in datagridview,then exit
            {
                Application.Exit();
                return;
            }

            if(MessageBox.Show("Make sure you have saved changes,are you sure to exit?", "Info", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)  //Remind to save changes
            {
                Application.Exit();
            }
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            if(this.btnEndEdit.Enabled==true)  //Can't save unless end edit datagridview
            {
                MessageBox.Show("Please end edit first!");
                return;
            }

            if(this.dgvExcelData.Columns.Count==0)  //If there is nothing in datagridview,don't save
            {
                return;
            }

            try
            {
                this.saveExcelFile();
                MessageBox.Show("Successfully!");
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void saveExcelFile()
        {
            //Open a new Excel.exe.
            myExcel.Application excelApp = new myExcel.Application();

            //Add workbook,there is only a sheet in it defaultly.
            myExcel.Workbook mybook = excelApp.Workbooks.Add(true);
            myExcel._Worksheet sheet = (myExcel._Worksheet)mybook.Worksheets[1];

            //Write data to the sheet.
            
//sheet.Name = "1";

            for (int i = 0; i < this.dgvExcelData.Rows.Count; i++)
            {
                for (int j = 0; j < this.dgvExcelData.Columns.Count; j++)
                {
                    myExcel.Range rng = (myExcel.Range)sheet.Cells[i+1, j+1];
                    rng.Value2 = this.dgvExcelData.Rows[i].Cells[j].Value;
                }
            }

            //Don't show save dialogue when saving file.
            excelApp.DisplayAlerts = false;
            excelApp.AlertBeforeOverwriting = false;

            //Save the file.   
            mybook.SaveAs(this.excelfile);
            mybook.Saved = true;

            //End the Excel.exe.
            excelApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            excelApp = null;
            mybook = null;
            //Force to quit Excel.exe.
            this.killExcel();
        }
    }
}


[此贴子已经被作者于2016-1-29 23:53编辑过]

附件: 您没有浏览附件的权限,请 登录注册
搜索更多相关主题的帖子: Excel  color  
2016-01-29 12:30
迪拜axq
Rank: 2
等 级:论坛游民
帖 子:29
专家分:11
注 册:2012-9-21
  得分:0 
学习了 ,请问下 运行程序的电脑必须需要安装office2015么
2016-02-01 11:58
qq1023569223
Rank: 19Rank: 19Rank: 19Rank: 19Rank: 19Rank: 19
来 自:湖南科技大学
等 级:贵宾
威 望:26
帖 子:2753
专家分:13404
注 册:2010-12-22
  得分:0 
回复 2楼 迪拜axq
不太清楚,应该office2007以上版本就可以。

[此贴子已经被作者于2016-2-1 12:22编辑过]


   唯实惟新 至诚致志
2016-02-01 12:21
迪拜axq
Rank: 2
等 级:论坛游民
帖 子:29
专家分:11
注 册:2012-9-21
  得分:0 
回复 3楼 qq1023569223
好吧 肯定不行的 请路过的经验者分享下
2016-02-01 16:06







关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.027448 second(s), 8 queries.
Copyright©2004-2018, BCCN.NET, All Rights Reserved