C# 快速填充excel 数据表导出excel

using System.Reflection;

using Excel = Microsoft.Office.Interop.Excel;

 

private string NumTochr(int Num)

        {

            int n = 64 + Num;

            return "" + (Char)n;

 

        }

        private string NumToExeclRowStr(int Num)

        {

            int X, Y;

            if (Num < 27)

            {

                return NumTochr(Num);

            }

            X = Num / 26;

            Y = Num - X * 26;

            return NumTochr(X) + NumTochr(Y);

 

        }

        /// <summary>

        /// 将DataTable中的列名及数据导出到Excel表中

        /// </summary>

        /// <param name="tmpDataTable">要导出的DataTable</param>

        /// <param name="strFileName">Excel的保存路径及名称</param>

        public void DataTabletoExcelkk(System.Data.DataTable tmpDataTable, string strFileName)

        {

            if (tmpDataTable == null)

                return;

            int rowNum = tmpDataTable.Rows.Count;

            int columnNum = tmpDataTable.Columns.Count;

            int rowIndex = 1;

            int columnIndex = 0;

 

            Excel.Application xlApp = new Excel.ApplicationClass();

            xlApp.DefaultFilePath = "";

            xlApp.DisplayAlerts = true;

            xlApp.SheetsInNewWorkbook = 1;

            Excel.Workbook xlBook = xlApp.Workbooks.Add(true);

            Excel.Worksheet ws = (Excel.Worksheet)xlBook.Worksheets[1];

            int colnum = tmpDataTable.Columns.Count;

            Excel.Range r = ws.get_Range("A1", NumToExeclRowStr(colnum) + "1");

            object[] objHeader = new object[colnum];

 

            //将DataTable的列名导入Excel表第一行

            foreach (DataColumn dc in tmpDataTable.Columns)

            {

                objHeader[columnIndex] = dc.ColumnName;

                columnIndex++;

 

            }

            r.Value2 = objHeader;

 

            //将DataTable中的数据导入Excel中

            for (int i = 0; i < rowNum; i++)

            {

                rowIndex++;

                columnIndex = 0;

                for (int j = 0; j < columnNum; j++)

                {

                    objHeader[columnIndex] = tmpDataTable.Rows[i][j].ToString();

                    columnIndex++;

 

                }

                r = ws.get_Range("A" + (i + 2), NumToExeclRowStr(colnum) + (i + 2));

                r.Value2 = objHeader;

               

 

            }

            r.EntireColumn.AutoFit();

            xlBook.SaveCopyAs(strFileName);

        }

调用---

   private void button5_Click(object sender, EventArgs e)

        {

//测试 闫磊 Email:[e-mail],[e-mail] 2008.1.5

 

            DbClass = new Db_Class();

            System.Diagnostics.Stopwatch MyWatch = new System.Diagnostics.Stopwatch();

            MyWatch.Start();

            DataTabletoExcelkk(DbClass.Db_CreateDataSet("select * from tab”).Tables[0], "C://pp.xls");

            MyWatch.Stop();

            MessageBox.Show(MyWatch.ElapsedMilliseconds.ToString() + "毫秒");

 

 

        }

使用单元填充Cells[rowIndex, columnIndex]一般慢的多，rangle提高的columnNUM倍