C# DataTable 导出excel （excel文件的行数可以控制excel文件的个数可以控制）

using System; using System.Collections.Generic; using System.Linq; using System.Text; //1.添加Microsoft.Office.Interop.Excel的引用 using Excel = Microsoft.Office.Interop.Excel; using System.Collections; using System.Windows.Forms; using System.Data; namespace TableToExcel { class Table_ToExcel { public Excel.Application m_xlApp = null; #region 外部接口 /// <summary>将一个DataTable的数据导出多个Excel文件（每一个Excel文件的数据行数由函数控制） /// 将一个DataTable的数据导出多个Excel文件（每一个Excel文件的数据行数由函数控制） /// </summary> /// <param name="tempDataTable">数据源</param> /// <param name="PathFileName">保存excel的路径</param> /// <param name="ExcelRows">每一个Excel文件的行数</param> /// <param name="ExcelVersion">导出Excel的版本(2003,2007)</param> public void u_DataTableToExcel1(DataTable tempDataTable, string PathFileName, long ExcelRows, string ExcelVersion) { if (tempDataTable == null) { return; } long rowNum = tempDataTable.Rows.Count;//导出数据的行数 int columnNum = tempDataTable.Columns.Count;//导出数据的列数 string sFileName = ""; if (rowNum > ExcelRows) { long excelRows = ExcelRows;//定义个excel文件显示的行数，最大的行数为65535，不能超过65535 int scount = (int)(rowNum / excelRows);//生成excel文件的个数 if (scount * excelRows < rowNum)//当总行数不被excelRows整除时，经过四舍五入可能excel的个数不准 { scount = scount + 1; } for (int sc = 1; sc <= scount; sc++) { int init = int.Parse(((sc - 1) * excelRows).ToString()); sFileName = PathFileName + sc.ToString(); long start = init; long end = sc * excelRows - 1; if (sc == scount) end = rowNum-1; u_OutExcel(tempDataTable, start, end, sFileName, ExcelVersion); } } else { u_OutExcel(tempDataTable, 0, rowNum-1, PathFileName, ExcelVersion); } } /// <summary>将一个DataTable的数据导出一个Excel文件:可能包含多个sheet文件，由sheet行数决定 (每一个sheet文件的行数由函数控制) /// 将一个DataTable的数据导出一个Excel文件:可能包含多个sheet文件，由sheet行数决定 (每一个sheet文件的行数由函数控制) /// </summary> /// <param name="tempDataTable">数据源</param> /// <param name="PathFileName">导出excel的路径</param> /// <param name="SheetRows">excel的文件中sheet的行数</param> /// <param name="ExcelVersion">导出Excel的版本</param> public void u_DataTableToExcel2(DataTable tempDataTable, string PathFileName, long SheetRows, string ExcelVersion) { if (tempDataTable == null) { return; } long rowNum = tempDataTable.Rows.Count;//行数 int columnNum = tempDataTable.Columns.Count;//列数 Excel.Application m_xlApp = new Excel.Application(); m_xlApp.DisplayAlerts = false;//不显示更改提示 m_xlApp.Visible = false; Excel.Workbooks workbooks = m_xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 try { if (rowNum > SheetRows)//单张Sheet表格最大行数 { long sheetRows = SheetRows;//定义每页显示的行数,行数必须小于65536 int scount = (int)(rowNum / sheetRows);//导出数据生成的表单数 if (scount * sheetRows < rowNum)//当总行数不被sheetRows整除时，经过四舍五入可能页数不准 { scount = scount + 1; } for (int sc = 1; sc <= scount; sc++) { if (sc > 1) { object missing = System.Reflection.Missing.Value; worksheet = (Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//添加一个sheet } else { worksheet = (Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1 } string[,] datas = new string[sheetRows + 1, columnNum]; for (int i = 0; i < columnNum; i++) //写入字段 { datas[0, i] = tempDataTable.Columns[i].Caption;//表头信息 } Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]); range.Interior.ColorIndex = 15;//15代表灰色 range.Font.Bold = true; range.Font.Size = 9; int init = int.Parse(((sc - 1) * sheetRows).ToString()); int r = 0; int index = 0; int result; if (sheetRows * sc >= rowNum) { result = (int)rowNum; } else { result = int.Parse((sheetRows * sc).ToString()); } for (r = init; r < result; r++) { index = index + 1; for (int i = 0; i < columnNum; i++) { object obj = tempDataTable.Rows[r][tempDataTable.Columns[i].ToString()]; datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式 } System.Windows.Forms.Application.DoEvents(); //添加进度条 } Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]); fchR.Value2 = datas; worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。 m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;//Sheet表最大化 range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]); //range.Interior.ColorIndex = 15;//15代表灰色 range.Font.Size = 9; range.RowHeight = 14.25; range.Borders.LineStyle = 1; range.HorizontalAlignment = 1; } } else { string[,] datas = new string[rowNum + 1, columnNum]; for (int i = 0; i < columnNum; i++) //写入字段 { datas[0, i] = tempDataTable.Columns[i].Caption; } Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]); range.Interior.ColorIndex = 15;//15代表灰色 range.Font.Bold = true; range.Font.Size = 9; int r = 0; for (r = 0; r < rowNum; r++) { for (int i = 0; i < columnNum; i++) { object obj = tempDataTable.Rows[r][tempDataTable.Columns[i].ToString()]; datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式 } System.Windows.Forms.Application.DoEvents(); //添加进度条 } Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]); fchR.Value2 = datas; worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。 m_xlApp.WindowState = Excel.XlWindowState.xlMaximized; range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]); //range.Interior.ColorIndex = 15;//15代表灰色 range.Font.Size = 9; range.RowHeight = 14.25; range.Borders.LineStyle = 1; range.HorizontalAlignment = 1; } workbook.Saved = true; switch (ExcelVersion) { case "2003": string s2003 = PathFileName + ".xls"; object ob = System.Reflection.Missing.Value; workbook.SaveAs(s2003, Excel.XlFileFormat.xlExcel8, ob, ob, ob, ob, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, ob, ob, ob, ob, ob); break; case "2007": string s2007 = PathFileName + ".xlsx"; workbook.SaveCopyAs(s2007); break; default: break; } KillProcess("EXCEL");//杀死excel进程 } catch (Exception ex) { MessageBox.Show("导出异常：" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning); } finally { KillProcess("EXCEL");//杀死excel进程 } } #endregion #region 内部接口 //作用将dt的（startindex到endindex的数据导出到filename）---用于将海量数据导出到多个excel文件 private void u_OutExcel(DataTable dt, long startindex, long endindex, string filename, string ExcelVersion) { try { long columnNum = dt.Columns.Count; long excelRows = endindex - startindex-1; Excel.Application m_xlApp = new Excel.Application(); m_xlApp.DisplayAlerts = false;//不显示更改提示 m_xlApp.Visible = false; Excel.Workbooks workbooks = m_xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 string[,] datas = new string[excelRows + 1, columnNum]; for (int i = 0; i < columnNum; i++) //写入表头字段 { string sTitle = dt.Columns[i].ColumnName; datas[0, i] = sTitle;//表头信息 } Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]); range.Interior.ColorIndex = 15;//15代表灰色 range.Font.Bold = true; range.Font.Size = 9; int r = 0; int row = 0; for (r = Convert.ToInt32(startindex); r < =endindex; r++) { row++; for (int i = 0; i < columnNum; i++) { string sname = dt.Columns[i].ToString().Trim(); object obj = dt.Rows[r][sname]; datas[row, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式 } System.Windows.Forms.Application.DoEvents(); //添加进度条 } Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[excelRows + 1, columnNum]); fchR.Value2 = datas; worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。 m_xlApp.WindowState = Excel.XlWindowState.xlMaximized; range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[excelRows + 1, columnNum]); //range.Interior.ColorIndex = 15;//15代表灰色 range.Font.Size = 9; range.RowHeight = 14.25; range.Borders.LineStyle = 1;//1边框为实线 0为excel样式 range.HorizontalAlignment = 1; workbook.Saved = true; switch (ExcelVersion) { case "2003": string s2003 = filename + ".xls"; object ob = System.Reflection.Missing.Value; workbook.SaveAs(s2003, Excel.XlFileFormat.xlExcel8, ob, ob, ob, ob, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, ob, ob, ob, ob, ob); break; case "2007": string s2007 = filename + ".xlsx"; workbook.SaveCopyAs(s2007); break; default: break; } KillProcess("EXCEL");//杀死excel进程 } catch (Exception ex) { ; } } //关闭进程 private void KillProcess(string processName) { System.Diagnostics.Process myproc = new System.Diagnostics.Process(); try { foreach (System.Diagnostics.Process thisproc in System.Diagnostics.Process.GetProcessesByName(processName)) { thisproc.Kill(); } } catch { } } #endregion #region 附调用方法 //Table_ToExcel outtoexcel = new Table_ToExcel(); //SaveFileDialog sfd = new SaveFileDialog(); //sfd.Filter = "Excel File|*.xlsx"; //sfd.FileName = DateTime.Now.ToString("yyyy.MM.dd HH.MM.SS"); //DataTable dt = gridControl1.DataSource as DataTable; //DialogResult dd = sfd.ShowDialog(this); //if (dd == DialogResult.OK) //{ // outtoexcel.u_DataTableToExcel1(dt, sfd.FileName, 100, "2003"); //} #endregion } }