C#从大量数据的DataTable高效率快速导出到Excel

using Microsoft.Office.Interop.Excel;

using System.Runtime.InteropServices;

 

 

//dt：从数据库读取的数据；file_name：保存路径；sheet_name：表单名称

private void DataTableToExcel(DataTable dt, string file_name, string sheet_name)

{

       Microsoft.Office.Interop.Excel.Application Myxls = new  Microsoft.Office.Interop.Excel.Application();

       Microsoft.Office.Interop.Excel.Workbook Mywkb = Myxls.Workbooks.Add();

       Microsoft.Office.Interop.Excel.Worksheet MySht = Mywkb.ActiveSheet;

       MySht.Name = sheet_name;

       Myxls.Visible = false;

       Myxls.DisplayAlerts = false;

       try

       {

              //写入表头

              object[] arrHeader = new object[dt.Columns.Count];

              for(int i = 0; i < dt.Columns.Count; i++)

              {

                     arrHeader[i] = dt.Columns[i].ColumnName;

              }

              MySht.Range[Mysht.Cells[1,1], MySht.Cells[1,dt.Columns.Count]].Value2 = arrHeader;

              //写入表体数据

              object[,] arrBody = new object[dt.Rows.Count, dt.Columns.Count];

              for(int i = 0; i < dt.Rows.Count; i++)

              {

                      for(int j = 0; j < dt.Columns.Count; j++)

                      {

                              arrBody[i,j] = dt.Rows[i][j].ToString();

                      }

              }

              MySht.Range[MySht.Cells[2,1], MySht.Cells[dt.Rows.Count + 1, dt.Columns.Count]].Value2 = arrBody;

              if(Mywkb != null)

              {

                      Mywkb.SaveAs(file_name);

                      Mywkb.Close(Type.Missing, Type.Missing, Type.Missing);

                      Mywkb = null;

              }

       }

       catch(Exception ex)

       {

               MessageBox.Show(ex.Message, "系统提示");

       }

       finally

       {

                //彻底关闭Excel进程

                if(Myxls != null)

                {

                       Myxls.Quit();

                       try

                       {

                              if(Myxls != null)

                              {

                                    int pid;

                                    GetWindowThreadProcessId(new IntPtr(Myxls.Hwnd), out pid);

                                    System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(pid);

                                     p.Kill();

                              }

                       }

                       catch(Exception ex)

                       {

                              MessageBox.Show("结束当前EXCEL进程失败：" + ex.Message);

                       }

                       Myxls = null;

                }

                GC.Collect();

       }

}