/// <summary>
/// 根据键值对列表的替换模板内容，导出Excel文件。
/// </summary>
/// <param name="templateFile">模板文件路径</param>
/// <param name="saveFileName">保存的文件名称,如a.xls</param>
/// <param name="dictReplace">待替换内容和替换值的键值对</param>
public static string ExportWithReplace(string templateFile, string saveFileName, Dictionary <string, string> dictReplace)
{
if (!File.Exists(templateFile))
{
throw new ArgumentException(templateFile, string.Format("{0} 文件不存在，", Path.GetFileName(templateFile)));
}
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(templateFile);
Aspose.Cells.Worksheet worksheet = designer.Workbook.Worksheets[0];
foreach (string name in dictReplace.Keys)
{
worksheet.Replace(name, dictReplace[name]);
}
designer.Process();
string saveFile = FileDialogHelper.SaveExcel(saveFileName, "C:\\");
if (!string.IsNullOrEmpty(saveFile))
{
designer.Workbook.Save(saveFile, SaveFormat.Excel97To2003);
}
return(saveFile);
}
/// <summary>
/// 根据键值对列表的替换模板内容，在Web环境中导出Excel文件。
/// </summary>
/// <param name="templateFile">模板文件路径</param>
/// <param name="saveFileName">保存的文件名称,如a.xls</param>
/// <param name="dictReplace">待替换内容和替换值的键值对</param>
public static void WebExportWithReplace(string templateFile, string saveFileName, Dictionary <string, string> dictReplace)
{
HttpContext curContext = HttpContext.Current;
string physicPath = curContext.Server.MapPath(templateFile);
if (!File.Exists(physicPath))
{
throw new ArgumentException(templateFile, string.Format("{0} 文件不存在，", templateFile));
}
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(physicPath);
Aspose.Cells.Worksheet worksheet = designer.Workbook.Worksheets[0];
foreach (string name in dictReplace.Keys)
{
worksheet.Replace(name, dictReplace[name]);
}
designer.Process();
HttpResponse response = curContext.Response;
designer.Workbook.Save(response.OutputStream, SaveFormat.Excel97To2003);
response.Charset = "GB2312";
response.ContentEncoding = Encoding.GetEncoding("GB2312");
response.ContentType = "application/ms-excel/msword";
response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(saveFileName));
response.Flush();
}
protected void btnGenExcel_Click(object sender, EventArgs e)
{
Dictionary <string, string> dictSource = new Dictionary <string, string>();
dictSource.Add("TIS_HANDLE_NO", "T0001");
dictSource.Add("ACCUSE_INDUSTRY", "出租车");
dictSource.Add("ACCUSER_NAME", "张三");
string templateFile = Server.MapPath("./Templates/Advice.xls");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(templateFile);
Aspose.Cells.Worksheet worksheet = designer.Workbook.Worksheets[0];
//使用文本替换
foreach (string name in dictSource.Keys)
{
worksheet.Replace(name, dictSource[name]);
}
//使用绑定数据方式替换
designer.SetDataSource("ACCUSER_SEX", "男");
designer.SetDataSource("ACCUSER_TEL", "1862029207*");
designer.Process();
string saveFileName = "testAdvice.xls";
designer.Workbook.Save(Response.OutputStream, SaveFormat.Excel97To2003);
Response.Charset = "GB2312";
Response.ContentEncoding = Encoding.GetEncoding("GB2312");
Response.ContentType = "application/ms-excel/msword";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(saveFileName));
Response.Flush();
}
