最近在做asp.net项目,在此过程中用到Excel导出,我整理了导出数据源到Excel模版文件的共同方法,分享给大家!
如果大家还有更好的方法,可以分享给我,谢谢!
方法一:利用Microsoft.Office.Interop.Excel
首先要引用using Excel = Microsoft.Office.Interop.Excel;
/// <summary>
/// 导出数据到Excel模板文件
/// </summary>
/// <param name="DT">数据源</param>
/// <param name="templateFile">模板文件</param>
/// <param name="tempFile">临时文件</param>
/// <param name="Response">客户端响应</param>
/// <param name="fileName">文件名称</param>
public static bool ExportExcel(DataTable DT, string templateFile, string tempFile, HttpResponse Response,string fileName)
{
try
{
Excel.Application myExcel = new Excel.Application();
//不单独显示Excel,最后在IE中显示
myExcel.Visible = false;
//指定模板文件
FileInfo mode = new FileInfo(templateFile);
//打开复制后的文件X
object missing = Missing.Value;
//打开模板文件
myExcel.Application.Workbooks.Open
(mode.FullName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Excel.Workbook myBook = myExcel.Workbooks[1];
Excel.Worksheet mySheet = myBook.Worksheets[1] as Excel.Worksheet;
Excel.Range range1 = mySheet.Rows[2, missing] as Excel.Range;
//复制行(在指定行下面复制指定数量行)
for (int j = 1; j <= DT.Rows.Count - 1; j++)
{
Excel.Range range2 = mySheet.Rows[2 + j, missing] as Excel.Range;
range1.Copy(range2);
}
//设置Sheet名
mySheet.Name = fileName;