/// <summary>
/// 根据模板生成Excel表
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="strExcelName">输出excel</param>
/// <param name="strTemplateName">模板名称</param>
/// <param name="Title">表格标题</param>
public void CreateByTemplate(System.Data.DataTable dt, string strExcelName, string strTemplateName, string Title)
{
Excel.Application objExcel = new Excel.Application(); //EXCEL实例
Workbooks objBooks; //工作簿 集合
Workbook objBook; //工作簿
Sheets objSheets; //工作表集合
Worksheet objSheet; //工作表
Range objCells; //单元格
string strFile = System.Web.HttpContext.Current.Request.PhysicalApplicationPath + ExcelPath + strExcelName; //下载路径
string strTemplate = System.Web.HttpContext.Current.Request.PhysicalApplicationPath + TempletPath + strTemplateName; //模板路径
objExcel.Visible = false;
objExcel.DisplayAlerts = false;
//定义一个新的工作簿
objBooks = objExcel.Workbooks;
objBooks.Open(strTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
objBook = objBooks.get_Item(1);
objSheets = objBook.Worksheets;
objSheet = (Worksheet)objSheets.get_Item(1);
//命名该sheet
objSheet.Name = strSheetName;
objCells = objSheet.Cells;
//将数据导入到Excel中去
int intRow; //行号
int intCol; //列号
//修改大标题
objCells[1, 1] = Title;
////添加标题
//for (intCol = 0; intCol < dt.Columns.Count; intCol++)
//{
// objCells[2, intCol + 1] = dt.Columns[intCol].ToString();
//}
//添加内容
for (intRow = 0; intRow < dt.Rows.Count; intRow++)
{
DataRow dr = dt.NewRow();
dr = dt.Rows[intRow];
for (intCol = 0; intCol < dt.Columns.Count; intCol++)
{
objCells[intRow + 3, intCol + 1] = dr[intCol].ToString();
}
}
//保存到临时文件夹
objSheet.SaveAs(strFile, Excel.XlFileFormat.xlTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
objBook.Close(false, Type.Missing, Type.Missing);
//退出Excel，并且释放调用的COM资源
objExcel.Quit();
//回收资源
GC.Collect();
//关闭进程
//KillProcess("Excel");
//以字符流的形式下载文件
FileStream fs = new FileStream(strFile, FileMode.Open);
byte[] bytes = new byte[(int)fs.Length];
fs.Read(bytes, 0, bytes.Length);
fs.Close();
CFile.FileDown(strFile, "1");
//Response.ContentType = "application/octet-stream";
////通知浏览器下载文件而不是打开
//string strValue = "attachment; filename=" + HttpUtility.UrlEncode(strFile, System.Text.Encoding.UTF8) ;
//Response.AddHeader("Content-Disposition", strValue);
//Response.BinaryWrite(bytes);
//Response.Flush();
////删除临时EXCEL文件
File.Delete(strFile);
//Response.End();
}