你的位置:首页 > ASP.net教程

[ASP.net教程]分享我基于NPOI+ExcelReport实现的导入与导出EXCEL类库:ExcelUtility (续3篇


ExcelUtility 类库经过我(梦在旅途)近期不断的优化与新增功能,现已基本趋向稳定,功能上也基本可以满足绝大部份的EXCEL导出需求,该类库已在我们公司大型ERP系统全面使用,效果不错,今天应用户的特殊需求,我又新增了一个功能,导出时动态生成多Sheet EXCEL。

 

新增方法一:由GetFormatterContainer Func委托导出基于EXCEL模板的多Sheet文件,方法定义如下:

    /// <summary>    /// 由GetFormatterContainer Func委托导出基于EXCEL模板的多工作薄文件    /// </summary>    /// <typeparam name="T">数据源可枚举项类型</typeparam>    /// <param name="templatePath">模板路径</param>    /// <param name="sheetName">模板中使用的工作薄名称</param>    /// <param name="dataSource">数据源</param>    /// <param name="getFormatterContainer">生成模板数据格式化容器(SheetFormatterContainer)委托,在委托方法中实现模板的格式化过程</param>    /// <param name="sheetSize">每个工作薄显示的数据记录数</param>    /// <param name="filePath">导出路径,可选</param>    /// <returns></returns>    public static string ToExcelWithTemplate<T>(string templatePath, string sheetName, IEnumerable<T> dataSource, Func<IEnumerable<T>, SheetFormatterContainer> getFormatterContainer, int sheetSize, string filePath = null)    {      if (!File.Exists(templatePath))      {        throw new FileNotFoundException(templatePath + "文件不存在!");      }      bool isCompatible = Common.GetIsCompatible(templatePath);      if (string.IsNullOrEmpty(filePath))      {        filePath = Common.GetSaveFilePath(isCompatible);      }      else if (isCompatible && !Path.GetExtension(filePath).Equals(".xls", StringComparison.OrdinalIgnoreCase))      {        throw new ArgumentException("当模板采用兼容模式时(低版本格式,如:xls,xlt),则指定的导出文件格式必需为xls。");      }      if (string.IsNullOrEmpty(filePath)) return null;      int sheetCount = 0;      var formatterContainers = new Dictionary<string, SheetFormatterContainer>();      IEnumerable<T> data = null;      while ((data = dataSource.Take(sheetSize)).Count() > 0)      {        var sheetFormatterContainer = getFormatterContainer(data);        sheetCount++;        if (sheetCount == 1)        {          formatterContainers.Add(sheetName, sheetFormatterContainer);        }        else        {          formatterContainers.Add(sheetName + sheetCount.ToString(), sheetFormatterContainer);        }        dataSource = dataSource.Skip(sheetSize);      }      string temp_templatePath = null;      try      {        temp_templatePath = Common.CreateTempFileByTemplate(templatePath, sheetName, sheetCount);        filePath = ToExcelWithTemplate(temp_templatePath, formatterContainers, filePath);      }      finally      {        if (!string.IsNullOrEmpty(temp_templatePath) && File.Exists(temp_templatePath))        {          File.Delete(temp_templatePath);        }        string temp_templateConfigFilePath = Path.ChangeExtension(temp_templatePath, ".

  

简要说明上述方法实现原理步骤:

1.指定模板路径、初始工作薄名称、导出的数据源、每个工作薄显示的记录数、封装生成模板数据格式化容器(SheetFormatterContainer)委托,在委托方法中实现模板的格式化过程;

2.依据每个工作薄显示的记录数,循环拆分数据源,并计算出需要的工作薄总数以及生成模板数据格式化容器字典(Key:Sheet名称,Value:模板数据格式化容器对象);

3.生成2中计算的所需的工作薄的临时模板文件(存放在系统的本地临时目录:Temp)

4.调用ToExcelWithTemplate的其它重载方法(基于模板+多sheet生成EXCEL方法)来完成EXCEL的导出;

5.无论最终导出成功与否,将删除临时模板及临时模板配置文件;

测试示例代码如下:

    /// <summary>    /// 测试方法:测试依据模板+DataTable来生成多工作薄的EXCEL    /// </summary>    [TestMethod]    public void TestExportToExcelWithTemplateByDataTable2()    {      DataTable dt = GetDataTable();      string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/excel.xls"; //获得EXCEL模板路径      string excelPath = ExcelUtility.Export.ToExcelWithTemplate<DataRow>(templateFilePath, "table", dt.Select(), (data) =>      {        SheetFormatterContainer formatterContainers = new SheetFormatterContainer(); //实例化一个模板数据格式化容器        PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();//实例化一个局部元素格式化器        partFormatterBuilder.AddFormatter("Title", "跨越IT学员");//将模板表格中Title的值设置为跨越IT学员        formatterContainers.AppendFormatterBuilder(partFormatterBuilder);//添加到工作薄格式容器中,注意只有添加进去了才会生效        CellFormatterBuilder cellFormatterBuilder = new CellFormatterBuilder();//实例化一个单元格格式化器        cellFormatterBuilder.AddFormatter("rptdate", DateTime.Today.ToString("yyyy-MM-dd HH:mm"));//将模板表格中rptdate的值设置为当前日期        formatterContainers.AppendFormatterBuilder(cellFormatterBuilder);//添加到工作薄格式容器中,注意只有添加进去了才会生效        //实例化一个表格格式化器,data是已拆分后的数据源(这里是10条记录),name表示的模板表格中第一行第一个单元格要填充的数据参数名        TableFormatterBuilder<DataRow> tableFormatterBuilder = new TableFormatterBuilder<DataRow>(data, "name");//这里的数据源设置:data是重点        tableFormatterBuilder.AddFormatters(new Dictionary<string, Func<DataRow, object>>{        {"name",r=>r["Col1"]},//将模板表格中name对应DataTable中的列Col1        {"sex",r=>r["Col2"]},//将模板表格中sex对应DataTable中的列Col2        {"km",r=>r["Col3"]},//将模板表格中km对应DataTable中的列Col3        {"score",r=>r["Col4"]},//将模板表格中score对应DataTable中的列Col4        {"result",r=>r["Col5"]}//将模板表格中result对应DataTable中的列Co5      });        formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);//添加到工作薄格式容器中,注意只有添加进去了才会生效        return formatterContainers;//返回一个模板数据格式化容器      }, 10);//注意这里的10表示动态生成新的工作薄,且每个工作薄显示10条记录      Assert.IsTrue(File.Exists(excelPath));    }

测试结果如下:

1.生成的临时模板及模板配置文件:

 

2.导出的EXCE结果如下:

 

3.临时模板及模板配置文件已被清除。

 

新增方法二:增加由DataTable导出多Sheet Excel方法(准确的说是修改ToExcel方法,增加一个sheetSize参数),方法定义如下:

    /// <summary>    /// 由DataTable导出Excel    /// </summary>    /// <param name="sourceTable">要导出数据的DataTable</param>    /// <param name="sheetName">工作薄名称,可选</param>    /// <param name="filePath">导出路径,可选</param>    /// <param name="colNames">需要导出的列名,可选</param>    /// <param name="colAliasNames">导出的列名重命名,可选</param>    /// <param name="colDataFormats">列格式化集合,可选</param>    /// <param name="sheetSize">指定每个工作薄显示的记录数,可选(不指定或指定小于0,则表示只生成一个工作薄)</param>    /// <returns></returns>    public static string ToExcel(DataTable sourceTable, string sheetName = "result", string filePath = null, string[] colNames = null, IDictionary<string, string> colAliasNames = null, IDictionary<string, string> colDataFormats = null, int sheetSize = 0)    {      if (sourceTable.Rows.Count <= 0) return null;      if (string.IsNullOrEmpty(filePath))      {        filePath = Common.GetSaveFilePath();      }      if (string.IsNullOrEmpty(filePath)) return null;      bool isCompatible = Common.GetIsCompatible(filePath);      IWorkbook workbook = Common.CreateWorkbook(isCompatible);      ICellStyle headerCellStyle = Common.GetCellStyle(workbook, true);      //ICellStyle cellStyle = Common.GetCellStyle(workbook);      if (colNames == null || colNames.Length <= 0)      {        colNames = sourceTable.Columns.Cast<DataColumn>().OrderBy(c => c.Ordinal).Select(c => c.ColumnName).ToArray();      }      IEnumerable<DataRow> batchDataRows, dataRows = sourceTable.Rows.Cast<DataRow>();      int sheetCount = 0;      if (sheetSize <= 0)      {        sheetSize = sourceTable.Rows.Count;      }      while ((batchDataRows = dataRows.Take(sheetSize)).Count() > 0)      {        Dictionary<int, ICellStyle> colStyles = new Dictionary<int, ICellStyle>();        ISheet sheet = workbook.CreateSheet(sheetName + (++sheetCount).ToString());        IRow headerRow = sheet.CreateRow(0);        // handling header.        for (int i = 0; i < colNames.Length; i++)        {          ICell headerCell = headerRow.CreateCell(i);          if (colAliasNames != null && colAliasNames.ContainsKey(colNames[i]))          {            headerCell.SetCellValue(colAliasNames[colNames[i]]);          }          else          {            headerCell.SetCellValue(colNames[i]);          }          headerCell.CellStyle = headerCellStyle;          sheet.AutoSizeColumn(headerCell.ColumnIndex);          if (colDataFormats != null && colDataFormats.ContainsKey(colNames[i]))          {            colStyles[headerCell.ColumnIndex] = Common.GetCellStyleWithDataFormat(workbook, colDataFormats[colNames[i]]);          }          else          {            colStyles[headerCell.ColumnIndex] = Common.GetCellStyle(workbook);          }        }        // handling value.        int rowIndex = 1;        foreach (DataRow row in batchDataRows)        {          IRow dataRow = sheet.CreateRow(rowIndex);          for (int i = 0; i < colNames.Length; i++)          {            ICell cell = dataRow.CreateCell(i);            //cell.SetCellValue((row[colNames[i]] ?? "").ToString());            //cell.CellStyle = cellStyle;            Common.SetCellValue(cell, (row[colNames[i]] ?? "").ToString(), sourceTable.Columns[colNames[i]].DataType, colStyles);            Common.ReSizeColumnWidth(sheet, cell);          }          rowIndex++;        }        sheet.ForceFormulaRecalculation = true;        dataRows = dataRows.Skip(sheetSize);      }      FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);      workbook.Write(fs);      fs.Dispose();      workbook = null;      return filePath;    }

修改代码部份说明:增加依据指定的每个工作薄显示的记录数(sheetSize)来循环拆分数据源及创建多个工作薄;

测试示例代码如下:

    /// <summary>    /// 测试方法:测试将DataTable导出到多工作薄EXCEL    /// </summary>    [TestMethod]    public void TestExportToExcelByDataTable8()    {      DataTable dt = GetDataTable();      string excelPath = ExcelUtility.Export.ToExcel(dt, "sheet", sheetSize: 10);//指定每个工作薄显示的记录数      Assert.IsTrue(File.Exists(excelPath));    }

导出的EXCE结果如下:

 源代码同步更新至开源社区的GIT目录中,具体地址请看我该系列之前的文章有列出,在此就不再说明。

 

分享我基于NPOI+ExcelReport实现的导入与导出EXCEL类库:ExcelUtility 其它相关文章链接:

分享我基于NPOI+ExcelReport实现的导入与导出EXCEL类库:ExcelUtility

 

分享我基于NPOI+ExcelReport实现的导入与导出EXCEL类库:ExcelUtility (续篇)

 

分享我基于NPOI+ExcelReport实现的导入与导出EXCEL类库:ExcelUtility (续2篇-模板导出综合示例)