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();
}
}