C#操作EXCEL全解（代码）

提示：运行之前必须先引用In***op.Excel.dll模块

using System;
using Sy***m.Collections.Generic;
using Sy***m.Text;
using Sy***m.Data;
using Sy***m.Data.OleDb;
using Sy***m.Windows.Forms;
using Sy***m.Reflection;
using Excel;

namespace An***sisSystem.DB
{
    public class ExcelOperation
    {
        private string _fileName;//保存路径名
        public ExcelOperation(string fileName)
        {
            _fileName = fileName;
        }

        private OleDbConnection GetConnection()
        {
            string connectString = st***g.Format("Pr***der=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0",_fileName);
            OleDbConnection myConn = new OleDbConnection(connectString);//建立链接
            return myConn;
        }

        public Sy***m.Data.DataTable ExecuteTableResult(string strSql)
        {
            Sy***m.Data.DataTable dt = new Sy***m.Data.DataTable();
            try
            {
                OleDbConnection conn = th***GetConnection();
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);//执行相关SQL语句
                da.Fill(dt);
            }
            catch (Sy***m.Exception ex)
            {
                //do nothing
            }
            return dt;
        }

        public DataSet ExecuteSetResult(string strSql,string table_name)
        {

            DataSet ds = new DataSet();
            string temp_name = "[" + table_name + "$]";
            try
            {
                OleDbConnection conn = th***GetConnection();
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
                da.Fill(ds,temp_name);
            }
            catch (Sy***m.Exception ex)
            {
                //do nothing
            }
            return ds;
        }

        public string ExecuteOneResult(string strSql)
        {
            string result = "";
            Sy***m.Data.DataTable dt = new Sy***m.Data.DataTable();
            try
            {
                OleDbConnection conn = th***GetConnection();
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
                da.Fill(dt);
            }
            catch (Sy***m.Exception ex)
            {
                //do nothing
            }
            if (dt != null && dt***ws.Count > 0)
            {
                result = dt.Rows[0][0].ToString();
            }
            return result;
        }

        public void ExecuteNonResult(string strSql)
        {
            try
            {
                OleDbConnection conn = th***GetConnection();
                OleDbCommand cmd = new OleDbCommand(strSql, conn);
                cm***xecuteNonQuery();
            }
            catch (Sy***m.Exception ex)
            {
                //do nothing
            }
        }

       
        private _Workbook W_B(Ex***.Application app)
        {
            Workbooks workbooks = ap***orkbooks;
            _Workbook workbook = wo***ooks.Add(Xl***Template.xlWBATWorksheet);
            return workbook;
        }
        private _Worksheet W_S(_Workbook work_book)
        {
            Sheets sheets = wo***book.Worksheets;
            _Worksheet worksheet = (_Worksheet)sh***s.get_Item(1);//获取选择第一个表，本身默认名称为Sheet1
            return worksheet;
        }
        //从DataGridView中导出数据到Excel表,单表导出
        public void Excel_out(DataGridView dataGridView1)
        {
            //建立Excel对象
            Ex***.Application app = new Ex***.Application();
            try
            {
                _Workbook workbook = this.W_B(app);
                _Worksheet worksheet = this.W_S(workbook);

                string sLen = "";
                //取得最后一列列名
                char H = (char)(64 + da***ridView1.ColumnCount / 26);
                char L = (char)(64 + da***ridView1.ColumnCount % 26);
                if (da***ridView1.ColumnCount < 26)
                {
                    sLen = L.***tring();
                }
                else
                {
                    sLen = H.***tring() + L.***tring();
                }

                //标题
                string sTmp = sLen + "1";
                Range ranCaption = wo***heet.get_Range(sTmp, "A1");
                string[] asCaption = new string[da***ridView1.ColumnCount];
                for (int i = 0; i < da***ridView1.ColumnCount; i++)
                {
                    asCaption[i] = da***ridView1.Columns[i].HeaderText;
                }
                ra***ption.Value2 = asCaption;
                //数据
                object[] obj = new object[da***ridView1.Columns.Count];
                for (int r = 0; r < da***ridView1.RowCount - 1; r++)
                {
                    for (int l = 0; l < da***ridView1.Columns.Count; l++)
                    {
                        if (dataGridView1[l, r].ValueType == typeof(DateTime))
                        {
                            obj[l] = dataGridView1[l, r].V***e.ToString();
                        }
                        else
                        {
                            obj[l] = dataGridView1[l, r].Value;
                        }
                    }
                    string cell1 = sLen + ((int)(r + 2)).ToString();
                    string cell2 = "A" + ((int)(r + 2)).ToString();
                    Range ran = wo***heet.get_Range(cell1, cell2);
                    ra***alue2 = obj;
                }
                //保存
                wo***ook.SaveCopyAs(th***_fileName);
                wo***ook.Saved = true;
            }
            finally
            {
                //关闭
                ap***serControl = false;
                app.Quit();
            }
        }

        ///

        /// 多表导出
        ///

        /// DataGridView列表集合
        /// 表名称集合
        public void Excel_out_MulTable(List dataGridView, string[] TableList)
        {
            //建立Excel对象
            Ex***.Application app = new Ex***.Application();
            try
            {
                Workbooks workbooks = ap***orkbooks;//定义一个工作簿集合
                _Workbook workbook = wo***ooks.Add(Xl***Template.xlWBATWorksheet);//向工作簿添加一个新工作簿
               
                Sheets sheets = wo***ook.Worksheets;//定义一个工作表集合
                _Worksheet worksheet ;
                int wnumber = 0;
                while (wnumber++ < (Ta***List.GetLength(0) - 1))
                {
                    sh***s.Add(Mi***ng.Value, Mi***ng.Value, Mi***ng.Value, Mi***ng.Value);//向一个工作表集合添加一个新工作表
                }

                /*提醒：Missing类为命名空间Sy***m.Reflection中的类，所以记得引入*/
               
                wnumber = 0;
                foreach (DataGridView dataGridView1 in dataGridView)
                {
                   
                    worksheet = null;
                    worksheet = (_Worksheet)sh***s.get_Item(wnumber + 1);//取出需要进行操作的工作表
                    wo***heet.Name = TableList[wnumber];//设置改工作表名称
                    if (wnumber != 0)
                        sh***s.Select(wnumber);//选中操作表
                   

                    string sLen = "";
                    //取得最后一列列名
                    char H = (char)(64 + da***ridView1.ColumnCount / 26);
                    char L = (char)(64 + da***ridView1.ColumnCount % 26);
                    if (da***ridView1.ColumnCount < 26)
                    {
                        sLen = L.***tring();
                    }
                    else
                    {
                        sLen = H.***tring() + L.***tring();
                    }

                    //标题
                    string sTmp = sLen + "1";
                    Range ranCaption = wo***heet.get_Range(sTmp, "A1");
                    string[] asCaption = new string[da***ridView1.ColumnCount];
                    for (int i = 0; i < da***ridView1.ColumnCount; i++)
                    {
                        asCaption[i] = da***ridView1.Columns[i].HeaderText;
                    }
                    ra***ption.Value2 = asCaption;
                    //数据
                    object[] obj = new object[da***ridView1.Columns.Count];
                    for (int r = 0; r < da***ridView1.RowCount - 1; r++)
                    {
                        for (int l = 0; l < da***ridView1.Columns.Count; l++)
                        {
                            if (dataGridView1[l, r].ValueType == typeof(DateTime))
                            {
                                obj[l] = dataGridView1[l, r].V***e.ToString();
                            }
                            else
                            {
                                obj[l] = dataGridView1[l, r].Value;
                            }
                        }
                        string cell1 = sLen + ((int)(r + 2)).ToString();
                        string cell2 = "A" + ((int)(r + 2)).ToString();
                        Range ran = wo***heet.get_Range(cell1, cell2);//设置单元格                       
                        ra***alue2 = obj;
                       
                    }      
                    wnumber++;
                }
                //保存               
                wo***ook.SaveCopyAs(th***_fileName);
                wo***ook.Saved = true;
            }
            finally
            {
                //关闭
                ap***serControl = false;
                app.Quit();
            }
           
        }

    }
}