asp.net 根据html模板导出excel
public class ExcelHelper { /// <summary> /// 根据html模板文件生成excel文件 /// </summary> /// <param name="ds">数据源</param> /// <param name="TemplateFilePath">html模板文件路径（虚拟路径而非物理路径）</param> /// <param name="ToFileName">生成的excel文件名，带后缀不带路径</param> /// <returns></returns> public static string GetExcel(DataSet ds, string TemplateFilePath, string ToFileName) { string ToFilePath = SysHelper.GetVirtualPath() + "upload/export_excel/"; string strOutPath = HttpContext.Current.Server.MapPath(ToFilePath); if (!Directory.Exists(strOutPath)) { Directory.CreateDirectory(strOutPath); } string TemplateContent = GetStringFromTemplate(ds, TemplateFilePath); //保存文件 using (FileStream fs = new FileStream(strOutPath + ToFileName, FileMode.Create)) { using (StreamWriter sw = new StreamWriter(fs)) { sw.Write(TemplateContent); } } return ToFilePath + ToFileName; } /// <summary> /// 根据模板生成字符换 /// </summary> /// <param name="ds"></param> /// <param name="TemplateFilePath"></param> /// <returns></returns> private static string GetStringFromTemplate(DataSet ds, string TemplateFilePath) { //读取模板 string TemplateContent = ""; using (StreamReader sr = new StreamReader(HttpContext.Current.Server.MapPath(TemplateFilePath))) { TemplateContent = sr.ReadToEnd(); } //解析每个表 for (int TableIndex = 0; TableIndex < ds.Tables.Count; TableIndex++) { DataTable dt = ds.Tables[TableIndex]; //获取表的模板集合 string TableTag = string.Format(@"#table.{0}#", TableIndex);//表的标签 string pattern = string.Format(@"#table.{0}#.*?#table.{0}#", TableIndex); Regex reg = new Regex(pattern, RegexOptions.Singleline); MatchCollection matchs = reg.Matches(TemplateContent); //解析每个模板 foreach (Match match in matchs) { string tableTemplate = match.Value; string table = "";//解析后内容 //解析每行数据 for (int i = 0; i < dt.Rows.Count; i++) { string rowTemplate = tableTemplate.Replace(TableTag, "");//去掉模板标签 //解析行模板 for (int j = 0; j < dt.Columns.Count; j++) { string ColumnName = "#table." + TableIndex + "." + dt.Columns[j].ColumnName + "#"; rowTemplate = rowTemplate.Replace(ColumnName, dt.Rows[i][j].ToString()); } table += rowTemplate; } //解析完之后替换到模板 TemplateContent = TemplateContent.Replace(tableTemplate, table); } } return TemplateContent; } }
html模板格式
<!DOCTYPE html> <html> <head> <title></title> <meta http-equiv="content-type" content="text/html; charset=UTF-8" /> </head> <body> <table border="1"> <tr><td colspan="4" style="text-align:center;">事件汇总表</td></tr> <tr> <td style="200px;">事件名称</td> <td style="500px;">事件简要情况</td> <td style="100px;">发生时间</td> <td style="100px;">发生地区</td> </tr> #table.0# <tr> <td>#table.0.omtb_title#</td> <td>#table.0.omtb_content#</td> <td>#table.0.omtb_date#</td> <td>#table.0.omtb_address#</td> </tr> #table.0# </table> </body> </html>
调用方式
string templatepath = SysHelper.GetVirtualPath() + "zfb/pro_list_excel.html", outfile = "事件汇总表" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; string excel_file_path = ExcelHelper.GetExcel(dsSource, templatepath, outfile);
附加获取应用程序虚拟目录方法
public class SysHelper { public static string GetVirtualPath() { string path = HttpContext.Current.Request.ApplicationPath; return path + (path != "/" ? "/" : ""); //以/结尾 } }