先添加引用:Microsoft.Office.Interop.Excel;、System.Runtime.InteropServices;、System.Data;、System.Web.Configuration;
namespace WebApplication1
{
public class DataExcel
{
private static string conn_str = WebConfigurationManager.AppSettings["connString"]; //数据库连接字符串
#region 启动导出数据的方法
public static System.Data.DataTable ExecQue()
{
System.Data.DataTable dt = new System.Data.DataTable();
using (OracleConnection conn=new OracleConnection(conn_str))
{
conn.Open(); //SQL查询语句
OracleDataAdapter adp = new OracleDataAdapter(" select * from db_lwj", conn_str);
adp.Fill(dt);
}
return dt;
}
#endregion
}
{
public class DataExcel
{
private static string conn_str = WebConfigurationManager.AppSettings["connString"]; //数据库连接字符串
#region 启动导出数据的方法
public static System.Data.DataTable ExecQue()
{
System.Data.DataTable dt = new System.Data.DataTable();
using (OracleConnection conn=new OracleConnection(conn_str))
{
conn.Open(); //SQL查询语句
OracleDataAdapter adp = new OracleDataAdapter(" select * from db_lwj", conn_str);
adp.Fill(dt);
}
return dt;
}
#endregion
}
public class DataChangeExcel
{
public static void DataToExcel(System.Data.DataTable dt, string SaveFile)
{
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,colIndex=0;
//遍历数据表,将字段名填充到Excel中第一行
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
//遍历数据表,将数据填充到Excel中
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim();
//遍历数据表,将字段名填充到Excel中第一行
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
//遍历数据表,将数据填充到Excel中
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim();
//设置表格内容居中对齐
workSheet.get_Range(excel.Cells[rowIndex, colIndex],excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
}
}
workSheet.get_Range(excel.Cells[rowIndex, colIndex],excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
}
}
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);
dt = null;
workBook.Close(true, misValue, misValue);
excel.Quit();
PublicMethod.Kill(excel);
releaseObject(workSheet);
releaseObject(workBook);
releaseObject(excel);
}
workBook.SaveAs(SaveFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
dt = null;
workBook.Close(true, misValue, misValue);
excel.Quit();
PublicMethod.Kill(excel);
releaseObject(workSheet);
releaseObject(workBook);
releaseObject(excel);
}
private static void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception)
{
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception)
{
obj = null;
}
finally
{
GC.Collect();
}
}
}
public class PublicMethod
{
[DllImport("User32.dll",CharSet=CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
}
finally
{
GC.Collect();
}
}
}
public class PublicMethod
{
[DllImport("User32.dll",CharSet=CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
public static void Kill(Microsoft.Office.Interop.Excel.Application excel)
{
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t,out k);
{
IntPtr t = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(t,out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
}
}
p.Kill();
}
}
}
//设置导出路径
调用:DataChangeExcel.DataToExcel(DataExcel.ExecQue(),@"F:\outputFormDataBase.xls");