一、导出Excel
public enum ReportTemplateMode
{
WebOrder,
WebOrderDetails,
WebSend,
WebSendDetails,
}
public class TemplateFileUtility
{
public static string CreateTemplateFile(ReportTemplateMode model)
{
string filePath = HttpContext.Current.Server.MapPath("~/Template");
string templateFile = string.Empty;
string createFile = string.Empty;
switch (model)
{
case ReportTemplateMode.WebOrder:
case ReportTemplateMode.WebOrderDetails:
case ReportTemplateMode.WebSend:
case ReportTemplateMode.WebSendDetails:
createFile = Guid.NewGuid() + ".xls";
templateFile = Path.Combine(filePath, model.ToString() + ".xls");
break;
default:
createFile = Guid.NewGuid() + ".xls";
templateFile = Path.Combine(filePath, model.ToString() + ".xls");
break;
}
createFile = Path.Combine(GetTempFile(), createFile);
if (!File.Exists(templateFile))
{
throw new Exception("Find Not TemplateFile Path: " + templateFile);
}
File.Copy(templateFile, createFile, true);
File.SetAttributes(createFile, FileAttributes.Normal);
return createFile;
}
public static string GetTempFile()
{
string tempFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Temp");
if (!System.IO.Directory.Exists(tempFile))
{
System.IO.Directory.CreateDirectory(tempFile);
}
return tempFile;
}
}
//点击"导出"按钮
protected void btn_Export_OnClick(object sender, EventArgs e)
{
GC.Collect();
string targetExcelFileName = TemplateFileUtility.CreateTemplateFile(ReportTemplateMode.WebOrder);
OrderSearchFilter filter = GetFilter();
DataTable dtbl = BizOrder.ExportOrderListByFilter(filter);
//Excel模型,用来操作Excel文件
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelWb = null;
Microsoft.Office.Interop.Excel.Worksheet excelWs = null;
Microsoft.Office.Interop.Excel.Range cellR = null;
try
{
//打开Excel工作簿文件
excelWb = excel.Workbooks.Open(targetExcelFileName, System.Type.Missing, false, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
excelWs = (Microsoft.Office.Interop.Excel.Worksheet)(excelWb.Sheets.get_Item(1)); //选择工作簿中第一个工作表
//查询条件
excelWs.Cells[2, 3] = this.txt_Keywords.Value;
excelWs.Cells[3, 3] = GetSearchDate();
excelWs.Cells[2, 12] = this.ddl_OrderState.SelectedIndex == 0 ? string.Empty : this.ddl_OrderState.SelectedItem.Text;