C# DataTable 导出excel 同时生成多个Sheet
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
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
}
}
