从数据库中查询数据,生成EXCEL表格的方法:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Runtime.InteropServices;
namespace Test2
{
class DataBaseToExcel
{
public static void DataToExcel (DataTable dataTable,string saveFile) //dataTable 为从数据库中查出的数据放到DataTable里面
{
Microsoft.Office.Interop.Excel._Application excel;
Microsoft.Office.Interop.Excel._Workbook workbook;
Microsoft.Office.Interop.Excel._Worksheet worksheet;
object misValue = System.Reflection.Missing.Value;
excel = new Microsoft.Office.Interop.Excel.ApplicationClass ();
workbook = excel.Workbooks.Add (misValue);
worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
int rowIndex = 1;
int colIndex = 0;
//取得表头
foreach(DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1,colIndex] = col.ColumnName;
}
//取得表中的数据
foreach(DataRow row in dataTable.Rows)
{
rowIndex++;
colIndex = 0;
foreach(DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString ().Trim ();
//置表中的内容为居中
Microsoft.Office.Interop.Excel.Range range=(Microsoft.Office.Interop.Excel.Range)worksheet.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]);
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range.EntireColumn.AutoFit ();//设置列宽为自动调整
}
}
excel.Visible = false;
workbook.SaveAs (
saveFile,Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
misValue,misValue,misValue,misValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
misValue,misValue,misValue,misValue,misValue);
dataTable = null;
workbook.Close (true,misValue,misValue);
excel.Quit ();
PublicMethod.Kill (excel);//kill 当前excel进程
releaseObject (worksheet);
releaseObject (workbook);
releaseObject (excel);
}
private static void releaseObject (object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject (obj);
obj = null;
}
catch
{
obj = null;
}
finally
{
GC.Collect ();
}
}
}
public class PublicMethod
{
[DllImport ("User32.dll",CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProccesID (IntPtr hwnd,out int ID);
public static void Kill (Microsoft.Office.Interop.Excel._Application excel)
{
try
{
IntPtr t = new IntPtr (excel.Hwnd);
int k = 0;
GetWindowThreadProccesID (t,out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById (k);
p.Kill ();
}
catch (Exception ex)
{
// Console.WriteLine (ex.StackTrace+ex.Message);
}
}
}
}