1. using System; 
  2. using System.Collections.Generic; 
  3. using System.Linq; 
  4. using System.Text; 
  5. using Excel = Microsoft.Office.Interop.Excel; 
  6. using System.Collections; 
  7. using System.Windows.Forms; 
  8. using System.Data; 
  9. namespace TableToExcel 
  10. { 
  11.     class Table_ToExcel 
  12.     { 
  13.         public Excel.Application m_xlApp = null; 
  14.         #region 外部接口 
  15.         /// <summary>将一个DataTable的数据导出多个Excel文件（每一个Excel文件的数据行数由函数控制） 
  16.         /// 将一个DataTable的数据导出多个Excel文件（每一个Excel文件的数据行数由函数控制） 
  17.         /// </summary> 
  18.         /// <param name="tempDataTable">数据源</param> 
  19.         /// <param name="PathFileName">保存excel的路径</param> 
  20.         /// <param name="ExcelRows">每一个Excel文件的行数</param> 
  21.         /// <param name="ExcelVersion">导出Excel的版本(2003,2007)</param> 
  22.         public void u_DataTableToExcel1(DataTable tempDataTable, string PathFileName, long ExcelRows, string ExcelVersion) 
  23.         { 
  24.             if (tempDataTable == null) 
  25.             { 
  26.                 return; 
  27.             } 
  28.             long rowNum = tempDataTable.Rows.Count;//导出数据的行数    
  29.             int columnNum = tempDataTable.Columns.Count;//导出数据的列数    
  30.             string sFileName = ""; 
  31.             if (rowNum > ExcelRows) 
  32.             { 
  33.                 long excelRows = ExcelRows;//定义个excel文件显示的行数，最大的行数为65535，不能超过65535                      
  34.                 int scount = (int)(rowNum / excelRows);//生成excel文件的个数    
  35.                 if (scount * excelRows < rowNum)//当总行数不被excelRows整除时，经过四舍五入可能excel的个数不准    
  36.                 { 
  37.                     scount = scount + 1; 
  38.                 } 
  39.                 for (int sc = 1; sc <= scount; sc++) 
  40.                 { 
  41.                     int init = int.Parse(((sc - 1) * excelRows).ToString()); 
  42.                     sFileName = PathFileName + sc.ToString(); 
  43.                     long start = init; 
  44.                     long end = sc * excelRows - 1; 
  45.                     if (sc == scount) 
  46.                         end = rowNum-1; 
  47.                     u_OutExcel(tempDataTable, start, end, sFileName, ExcelVersion); 
  48.                 } 
  49.             } 
  50.             else 
  51.             { 
  52.                 u_OutExcel(tempDataTable, 0, rowNum-1, PathFileName, ExcelVersion); 
  53.             } 
  54.         } 
  55.         /// <summary>将一个DataTable的数据导出一个Excel文件:可能包含多个sheet文件，由sheet行数决定 (每一个sheet文件的行数由函数控制) 
  56.         /// 将一个DataTable的数据导出一个Excel文件:可能包含多个sheet文件，由sheet行数决定 (每一个sheet文件的行数由函数控制) 
  57.         /// </summary> 
  58.         /// <param name="tempDataTable">数据源</param> 
  59.         /// <param name="PathFileName">导出excel的路径</param> 
  60.         /// <param name="SheetRows">excel的文件中sheet的行数</param> 
  61.         /// <param name="ExcelVersion">导出Excel的版本</param> 
  62.         public void u_DataTableToExcel2(DataTable tempDataTable, string PathFileName, long SheetRows, string ExcelVersion) 
  63.         { 
  64.             if (tempDataTable == null) 
  65.             { 
  66.                 return; 
  67.             } 
  68.             long rowNum = tempDataTable.Rows.Count;//行数    
  69.             int columnNum = tempDataTable.Columns.Count;//列数    
  70.             Excel.Application m_xlApp = new Excel.Application(); 
  71.             m_xlApp.DisplayAlerts = false;//不显示更改提示    
  72.             m_xlApp.Visible = false; 
  73.             Excel.Workbooks workbooks = m_xlApp.Workbooks; 
  74.             Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); 
  75.             Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1  
  76.             try 
  77.             { 
  78.                 if (rowNum > SheetRows)//单张Sheet表格最大行数    
  79.                 { 
  80.                     long sheetRows = SheetRows;//定义每页显示的行数,行数必须小于65536    
  81.                     int scount = (int)(rowNum / sheetRows);//导出数据生成的表单数    
  82.                     if (scount * sheetRows < rowNum)//当总行数不被sheetRows整除时，经过四舍五入可能页数不准    
  83.                     { 
  84.                         scount = scount + 1; 
  85.                     } 
  86.                     for (int sc = 1; sc <= scount; sc++) 
  87.                     { 
  88.                         if (sc > 1) 
  89.                         { 
  90.                             object missing = System.Reflection.Missing.Value; 
  91.                             worksheet = (Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//添加一个sheet    
  92.                         } 
  93.                         else 
  94.                         { 
  95.                             worksheet = (Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1    
  96.                         } 
  97.                         string[,] datas = new string[sheetRows + 1, columnNum]; 
  98.                         for (int i = 0; i < columnNum; i++) //写入字段    
  99.                         { 
  100.                             datas[0, i] = tempDataTable.Columns[i].Caption;//表头信息    
  101.                         } 
  102.                         Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]); 
  103.                         range.Interior.ColorIndex = 15;//15代表灰色    
  104.                         range.Font.Bold = true; 
  105.                         range.Font.Size = 9; 
  106.                         int init = int.Parse(((sc - 1) * sheetRows).ToString()); 
  107.                         int r = 0; 
  108.                         int index = 0; 
  109.                         int result; 
  110.                         if (sheetRows * sc >= rowNum) 
  111.                         { 
  112.                             result = (int)rowNum; 
  113.                         } 
  114.                         else 
  115.                         { 
  116.                             result = int.Parse((sheetRows * sc).ToString()); 
  117.                         } 
  118.                         for (r = init; r < result; r++) 
  119.                         { 
  120.                             index = index + 1; 
  121.                             for (int i = 0; i < columnNum; i++) 
  122.                             { 
  123.                                 object obj = tempDataTable.Rows[r][tempDataTable.Columns[i].ToString()]; 
  124.                                 datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式   
  125.                             } 
  126.                             System.Windows.Forms.Application.DoEvents(); 
  127.                             //添加进度条    
  128.                         } 
  129.                         Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]); 
  130.                         fchR.Value2 = datas; 
  131.                         worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。    
  132.                         m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;//Sheet表最大化    
  133.                         range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]); 
  134.                         //range.Interior.ColorIndex = 15;//15代表灰色    
  135.                         range.Font.Size = 9; 
  136.                         range.RowHeight = 14.25; 
  137.                         range.Borders.LineStyle = 1; 
  138.                         range.HorizontalAlignment = 1; 
  139.                     } 
  140.                 } 
  141.                 else 
  142.                 { 
  143.                     string[,] datas = new string[rowNum + 1, columnNum]; 
  144.                     for (int i = 0; i < columnNum; i++) //写入字段    
  145.                     { 
  146.                         datas[0, i] = tempDataTable.Columns[i].Caption; 
  147.                     } 
  148.                     Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]); 
  149.                     range.Interior.ColorIndex = 15;//15代表灰色    
  150.                     range.Font.Bold = true; 
  151.                     range.Font.Size = 9; 
  152.                     int r = 0; 
  153.                     for (r = 0; r < rowNum; r++) 
  154.                     { 
  155.                         for (int i = 0; i < columnNum; i++) 
  156.                         { 
  157.                             object obj = tempDataTable.Rows[r][tempDataTable.Columns[i].ToString()]; 
  158.                             datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式    
  159.                         } 
  160.                         System.Windows.Forms.Application.DoEvents(); 
  161.                         //添加进度条    
  162.                     } 
  163.                     Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]); 
  164.                     fchR.Value2 = datas; 
  165.                     worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。    
  166.                     m_xlApp.WindowState = Excel.XlWindowState.xlMaximized; 
  167.                     range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]); 
  168.                     //range.Interior.ColorIndex = 15;//15代表灰色    
  169.                     range.Font.Size = 9; 
  170.                     range.RowHeight = 14.25; 
  171.                     range.Borders.LineStyle = 1; 
  172.                     range.HorizontalAlignment = 1; 
  173.                 } 
  174.                 workbook.Saved = true; 
  175.                 switch (ExcelVersion) 
  176.                 { 
  177.                     case "2003": 
  178.                         string s2003 = PathFileName + ".xls"; 
  179.                         object ob = System.Reflection.Missing.Value; 
  180.                         workbook.SaveAs(s2003, Excel.XlFileFormat.xlExcel8, ob, ob, ob, ob, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, ob, ob, ob, ob, ob); 
  181.                         break; 
  182.                     case "2007": 
  183.                         string s2007 = PathFileName + ".xlsx"; 
  184.                         workbook.SaveCopyAs(s2007); 
  185.                         break; 
  186.                     default: break; 
  187.                 } 
  188.                 KillProcess("EXCEL");//杀死excel进程 
  189.             } 
  190.             catch (Exception ex) 
  191.             { 
  192.                 MessageBox.Show("导出异常" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning); 
  193.             } 
  194.             finally 
  195.             { 
  196.                 KillProcess("EXCEL");//杀死excel进程 
  197.             } 
  198.         } 
  199.         #endregion 
  200.         #region 内部接口 
  201.         //作用将dt的（startindex到endindex的数据导出到filename）---用于将海量数据导出到多个excel文件 
  202.         private void u_OutExcel(DataTable dt, long startindex, long endindex, string filename, string ExcelVersion) 
  203.         { 
  204.             try 
  205.             { 
  206.                 long columnNum = dt.Columns.Count; 
  207.                 long excelRows = endindex - startindex-1; 
  208.                 Excel.Application m_xlApp = new Excel.Application(); 
  209.                 m_xlApp.DisplayAlerts = false;//不显示更改提示    
  210.                 m_xlApp.Visible = false; 
  211.                 Excel.Workbooks workbooks = m_xlApp.Workbooks; 
  212.                 Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); 
  213.                 Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1   
  214.                 string[,] datas = new string[excelRows + 1, columnNum]; 
  215.                 for (int i = 0; i < columnNum; i++) //写入表头字段    
  216.                 { 
  217.                     string sTitle = dt.Columns[i].ColumnName; 
  218.                     datas[0, i] = sTitle;//表头信息    
  219.                 } 
  220.                 Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]); 
  221.                 range.Interior.ColorIndex = 15;//15代表灰色    
  222.                 range.Font.Bold = true; 
  223.                 range.Font.Size = 9; 
  224.                 int r = 0; 
  225.                 int row = 0; 
  226.                 for (r = Convert.ToInt32(startindex); r < =endindex; r++) 
  227.                 { 
  228.                     row++; 
  229.                     for (int i = 0; i < columnNum; i++) 
  230.                     { 
  231.                         string sname = dt.Columns[i].ToString().Trim(); 
  232.                         object obj = dt.Rows[r][sname]; 
  233.                         datas[row, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式    
  234.                     } 
  235.                     System.Windows.Forms.Application.DoEvents(); 
  236.                     //添加进度条    
  237.                 } 
  238.                 Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[excelRows + 1, columnNum]); 
  239.                 fchR.Value2 = datas; 
  240.                 worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。    
  241.                 m_xlApp.WindowState = Excel.XlWindowState.xlMaximized; 
  242.                 range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[excelRows + 1, columnNum]); 
  243.                 //range.Interior.ColorIndex = 15;//15代表灰色    
  244.                 range.Font.Size = 9; 
  245.                 range.RowHeight = 14.25; 
  246.                 range.Borders.LineStyle = 1;//1边框为实线 0为excel样式 
  247.                 range.HorizontalAlignment = 1; 
  248.                 workbook.Saved = true; 
  249.                 switch (ExcelVersion) 
  250.                 { 
  251.                     case "2003": 
  252.                         string s2003 = filename + ".xls"; 
  253.                         object ob = System.Reflection.Missing.Value; 
  254.                         workbook.SaveAs(s2003, Excel.XlFileFormat.xlExcel8, ob, ob, ob, ob, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, ob, ob, ob, ob, ob); 
  255.                         break; 
  256.                     case "2007": 
  257.                         string s2007 = filename + ".xlsx"; 
  258.                         workbook.SaveCopyAs(s2007); 
  259.                         break; 
  260.                     default: break; 
  261.                 } 
  262.                 KillProcess("EXCEL");//杀死excel进程 
  263.             } 
  264.             catch (Exception ex) 
  265.             { 
  266.                 ; 
  267.             } 
  268.         } 
  269.         //关闭进程  
  270.         private void KillProcess(string processName) 
  271.         { 
  272.             System.Diagnostics.Process myproc = new System.Diagnostics.Process(); 
  273.             try 
  274.             { 
  275.                 foreach (System.Diagnostics.Process thisproc in System.Diagnostics.Process.GetProcessesByName(processName)) 
  276.                 { 
  277.                     thisproc.Kill(); 
  278.                 } 
  279.             } 
  280.             catch 
  281.             { 
  282.             } 
  283.         } 
  284.         #endregion 
  285.         #region 附调用方法 
  286.         //Table_ToExcel outtoexcel = new Table_ToExcel(); 
  287.         //SaveFileDialog sfd = new SaveFileDialog(); 
  288.         //sfd.Filter = "Excel File|*.xlsx"; 
  289.         //sfd.FileName = DateTime.Now.ToString("yyyy.MM.dd HH.MM.SS"); 
  290.         //DataTable dt = gridControl1.DataSource as DataTable; 
  291.         //DialogResult dd = sfd.ShowDialog(this); 
  292.         //if (dd == DialogResult.OK) 
  293.         //{ 
  294.         //    outtoexcel.u_DataTableToExcel1(dt, sfd.FileName, 100, "2003"); 
  295.         //} 
  296.         #endregion 
  297.     } 
  298. }