星空网 > 软件开发 > ASP.net

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

上周六我发表的文章《分享我基于NPOI+ExcelReport实现的导入与导出EXCEL类库:ExcelUtility》受到了大家的热烈支持与推荐,再此表示感谢,该ExcelUtility类库自上次发文起,又经过了多次的改进,增加了许多的功能与方法,可以全面满足大家的需求,下面先来看一下新方法的测试结果:

第一个新增功能,列宽自适应,当超过30个字符则将单元格内容设为换行

任意一个无模板的导出方法均支持该功能,示例代码如下:

    /// <summary>    /// 测试方法:测试将DataTable导出到EXCEL,无模板    /// </summary>    [TestMethod]    public void TestExportToExcelByDataTable()    {      DataTable dt = GetDataTable();      string excelPath = ExcelUtility.Export.ToExcel(dt, "导出结果");      Assert.IsTrue(File.Exists(excelPath));    }

结果如下图示:

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

 

第二个新增功能,依据数据源(DataTable、DataGridView)的列类型自动将与之对应的EXCEL列的单元格式设为相同的格式内容显示,如:整数类型显示在单元格内无小数的数字格式,有小数位的类显示在单元格内2位小数数字格式,日期类型显示在单元格内日期+时间的日期格式,布尔类型显示在单元格内布尔格式,任意一个无模板的导出方法均支持该功能,示例代码如下:

    /// <summary>    /// 测试方法:测试将DataTable导出到EXCEL,无模板,且指定导出的列名,以及导出列名的重命名    /// </summary>    [TestMethod]    public void TestExportToExcelByDataTable3()    {      DataTable dt = GetDataTable();      string[] expColNames = { "Col1", "Col2", "Col3", "Col4", "Col5", "Col7" };      Dictionary<string, string> expColAsNames = new Dictionary<string, string>() {         {"Col1","列一"},        {"Col2","列二"},        {"Col3","列三"},        {"Col4","数字列"},        {"Col5","列五"},        {"Col7","日期列"}      };      string excelPath = ExcelUtility.Export.ToExcel(dt, "导出结果", null, expColNames, expColAsNames);      Assert.IsTrue(File.Exists(excelPath));    }

结果如下图示:

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

 

第三个新增功能,在第二个新增功能的基础上,增加可以自定义设置列的单元格显示格式(支持日期类型、数字类型),任意一个无模板的导出方法均支持该功能,示例代码如下:

    /// <summary>    /// 测试方法:测试将DataTable导出到EXCEL,无模板,且指定某些列的显示格式    /// </summary>    [TestMethod]    public void TestExportToExcelByDataTable6()    {      DataTable dt = GetDataTable();      var colDataFormatDic = new Dictionary<string, string>      {        {"Col4","0.000"}, //将Col4列DOUBLE类型的EXCEL对应列格式设置为显示成3位小数(默认为2位小数)        {"Col7","yyyy-mm-dd"}//将Col7列DateTime类型的EXCEL对应列格式设置为年月日(默认为yyyy/mm/dd hh:mm:ss)      };      //更多设置格式可在EXCEL的设置单元格格式中的数字选项卡中的自定义格式列表(若无,可自定义,建议先在EXCEL中测试好格式字符串后再用于程序中)      string excelPath = ExcelUtility.Export.ToExcel(dt, "导出结果", colDataFormats: colDataFormatDic);      Assert.IsTrue(File.Exists(excelPath));    }

结果如下图示:

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

 

换种格式定义测试:

    /// <summary>    /// 测试方法:测试将DataTable导出到EXCEL,无模板,且指定某些列的显示格式    /// </summary>    [TestMethod]    public void TestExportToExcelByDataTable7()    {      DataTable dt = GetDataTable();      var colDataFormatDic = new Dictionary<string, string>      {        {"Col4","¥#,##0.00_);(¥#,##0.00)"}, //将Col4列DOUBLE类型的EXCEL对应列格式设置为显示成包含货币格式,如:¥5.00(默认为2位小数)        {"Col7","yyyy\"年\"m\"月\"d\"日\";@"}//将Col7列DateTime类型的EXCEL对应列格式设置为中文年月日,如:2015年12月5日(默认为yyyy/mm/dd hh:mm:ss)      };      //更多设置格式可在EXCEL的设置单元格格式中的数字选项卡中的自定义格式列表(若无,可自定义,建议先在EXCEL中测试好格式字符串后再用于程序中)      string excelPath = ExcelUtility.Export.ToExcel(dt, "导出结果", colDataFormats: colDataFormatDic);      Assert.IsTrue(File.Exists(excelPath));    }

结果如下图示:

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

注意事项说明:想要实现导出的EXCEL单元格依据数据类型自动设置或手动指定格式,需首先确保数据源的列与自动或手动设置的格式相符,即列类型必需是数字类型、日期类型、布尔类型,不能是以字符串的形式存在的这些所谓的“数字类型、日期类型、布尔类型”


第四个新增的功能,可指定DataGridView是否可以导出隐藏列(不显示的列)、及指定依据DataGridView标题列名导出相应列数据,示例代码如下:

    /// <summary>    /// 测试方法:测试将DataGridView数据导出到EXCEL文件,无模板,且不导出隐藏列    /// </summary>    [TestMethod]    public void TestToExcelByDataGridView()    {      var grid = GetDataGridViewWithData();      string excelPath = ExcelUtility.Export.ToExcel(grid, "导出结果", null, false);      Assert.IsTrue(File.Exists(excelPath));    }

结果如下图示:

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

 

第五个新增功能,DataGridView若改变列的显示位置,导出的数据也能与界面显示的数据同步调整,示例代码如下:

    /// <summary>    /// 测试方法:测试将DataGridView数据导出到EXCEL文件,无模板,改变列的显示位置,导出隐藏列    /// </summary>    [TestMethod]    public void TestToExcelByDataGridView2()    {      var grid = GetDataGridViewWithData();      //模拟改变列的显示位置      grid.Columns[0].DisplayIndex = 1;      grid.Columns[1].DisplayIndex = 0;      string excelPath = ExcelUtility.Export.ToExcel(grid, "导出结果", null, true);      Assert.IsTrue(File.Exists(excelPath));    }

结果如下图示:

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

以下是GetDataGridViewWithData模拟数据方法:

    private DataGridView GetDataGridViewWithData()    {      var grid = new DataGridView();      var dt = GetDataTable();      foreach (DataColumn col in dt.Columns)      {        bool v = col.Ordinal > 4 ? false : true;        grid.Columns.Add(new DataGridViewTextBoxColumn() { DataPropertyName = col.ColumnName, HeaderText ="列名" + col.ColumnName , Visible = v,ValueType=col.DataType });      }      foreach (DataRow row in dt.Rows)      {        ArrayList values = new ArrayList();        foreach (DataColumn col in dt.Columns)        {          values.Add(row[col]);        }        grid.Rows.Add(values.ToArray());      }      return grid;    }

  

我相信这些功能加上上次的功能,应该能满足大家日常工作中所遇到的各种导出EXCEL场景吧,下面重新公布一下两个核心的与导出相关类源代码,以供大家参考,若有不足之处,敬请指出,谢谢!

ExcelUtility.Export:

using ExcelReport;using ExcelUtility.Base;using NPOI.SS.UserModel;using System;using System.Collections.Generic;using System.Data;using System.IO;using System.Linq;using System.Text;using System.Windows.Forms;namespace ExcelUtility{  /// <summary>  /// EXCEL导出功能集合类  /// 作者:Zuowenjun  /// 日期:2016/1/15  /// </summary>  public sealed class Export  {    /// <summary>    /// 由DataSet导出Excel    /// </summary>    /// <param name="sourceTable">要导出数据的DataTable</param>    /// <param name="filePath">导出路径,可选</param>    /// <returns></returns>    public static string ToExcel(DataSet sourceDs, string filePath = 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);      for (int i = 0; i < sourceDs.Tables.Count; i++)      {        DataTable table = sourceDs.Tables[i];        string sheetName = string.IsNullOrEmpty(table.TableName) ? "result" + i.ToString() : table.TableName;        ISheet sheet = workbook.CreateSheet(sheetName);        IRow headerRow = sheet.CreateRow(0);        Dictionary<int, ICellStyle> colStyles = new Dictionary<int, ICellStyle>();        // handling header.        foreach (DataColumn column in table.Columns)        {          ICell headerCell = headerRow.CreateCell(column.Ordinal);          headerCell.SetCellValue(column.ColumnName);          headerCell.CellStyle = headerCellStyle;          sheet.AutoSizeColumn(headerCell.ColumnIndex);          colStyles[headerCell.ColumnIndex] = Common.GetCellStyle(workbook);        }        // handling value.        int rowIndex = 1;        foreach (DataRow row in table.Rows)        {          IRow dataRow = sheet.CreateRow(rowIndex);          foreach (DataColumn column in table.Columns)          {            ICell cell = dataRow.CreateCell(column.Ordinal);            //cell.SetCellValue((row[column] ?? "").ToString());            //cell.CellStyle = cellStyle;            Common.SetCellValue(cell, (row[column] ?? "").ToString(), column.DataType,colStyles);            Common.ReSizeColumnWidth(sheet, cell);          }          rowIndex++;        }        sheet.ForceFormulaRecalculation = true;      }      FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);      workbook.Write(fs);      fs.Dispose();      workbook = null;      return filePath;    }    /// <summary>    /// 由DataTable导出Excel    /// </summary>    /// <param name="sourceTable">要导出数据的DataTable</param>    /// <param name="colAliasNames">导出的列名重命名数组</param>    /// <param name="sheetName">工作薄名称,可选</param>    /// <param name="filePath">导出路径,可选</param>    /// <param name="colDataFormats">列格式化集合,可选</param>    /// <returns></returns>    public static string ToExcel(DataTable sourceTable, string[] colAliasNames, string sheetName = "result", string filePath = null, IDictionary<string, string> colDataFormats = null)    {      if (sourceTable.Rows.Count <= 0) return null;      if (string.IsNullOrEmpty(filePath))      {        filePath = Common.GetSaveFilePath();      }      if (string.IsNullOrEmpty(filePath)) return null;      if (colAliasNames == null || sourceTable.Columns.Count != colAliasNames.Length)      {        throw new ArgumentException("列名重命名数组与DataTable列集合不匹配。", "colAliasNames");      }      bool isCompatible = Common.GetIsCompatible(filePath);      IWorkbook workbook = Common.CreateWorkbook(isCompatible);      ICellStyle headerCellStyle = Common.GetCellStyle(workbook, true);      //ICellStyle cellStyle = Common.GetCellStyle(workbook);      Dictionary<int, ICellStyle> colStyles = new Dictionary<int, ICellStyle>();      ISheet sheet = workbook.CreateSheet(sheetName);      IRow headerRow = sheet.CreateRow(0);      // handling header.      foreach (DataColumn column in sourceTable.Columns)      {        ICell headerCell = headerRow.CreateCell(column.Ordinal);        headerCell.SetCellValue(colAliasNames[column.Ordinal]);        headerCell.CellStyle = headerCellStyle;        sheet.AutoSizeColumn(headerCell.ColumnIndex);        if (colDataFormats != null && colDataFormats.ContainsKey(column.ColumnName))        {          colStyles[headerCell.ColumnIndex] = Common.GetCellStyleWithDataFormat(workbook, colDataFormats[column.ColumnName]);        }        else        {          colStyles[headerCell.ColumnIndex] = Common.GetCellStyle(workbook);        }      }      // handling value.      int rowIndex = 1;      foreach (DataRow row in sourceTable.Rows)      {        IRow dataRow = sheet.CreateRow(rowIndex);        foreach (DataColumn column in sourceTable.Columns)        {          ICell cell = dataRow.CreateCell(column.Ordinal);          //cell.SetCellValue((row[column] ?? "").ToString());          //cell.CellStyle = cellStyle;          Common.SetCellValue(cell, (row[column] ?? "").ToString(), column.DataType, colStyles);          Common.ReSizeColumnWidth(sheet, cell);        }        rowIndex++;      }      sheet.ForceFormulaRecalculation = true;      FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);      workbook.Write(fs);      fs.Dispose();      sheet = null;      headerRow = null;      workbook = null;      return filePath;    }    /// <summary>    /// 由DataGridView导出    /// </summary>    /// <param name="grid">要导出的DataGridView对象</param>    /// <param name="sheetName">工作薄名称,可选</param>    /// <param name="filePath">导出路径,可选</param>    /// <param name="includeHiddenCol">导出时是否包含隐藏列,可选</param>    /// <param name="colHeaderTexts">指定导出DataGridView的列标题名数组,可选</param>    /// <param name="colDataFormats">列格式化集合,可选</param>    /// <returns></returns>    public static string ToExcel(DataGridView grid, string sheetName = "result", string filePath = null, bool includeHiddenCol = false, string[] colHeaderTexts = null, IDictionary<string, string> colDataFormats = null)    {      if (grid.Rows.Count <= 0) return null;      if (string.IsNullOrEmpty(filePath))      {        filePath = Common.GetSaveFilePath();      }      if (string.IsNullOrEmpty(filePath)) return null;      bool isCompatible = Common.GetIsCompatible(filePath);      DataGridViewColumn[] expCols = null;      expCols = grid.Columns.Cast<DataGridViewColumn>().OrderBy(c => c.DisplayIndex).ToArray();      if (!includeHiddenCol)      {        expCols = expCols.Where(c => c.Visible).ToArray();      }      if (colHeaderTexts != null && colHeaderTexts.Length > 0)      {        expCols = expCols.Where(c => colHeaderTexts.Contains(c.HeaderText)).ToArray();      }      IWorkbook workbook = Common.CreateWorkbook(isCompatible);      ICellStyle headerCellStyle = Common.GetCellStyle(workbook, true);      //ICellStyle cellStyle = Common.GetCellStyle(workbook);      ISheet sheet = workbook.CreateSheet(sheetName);      IRow headerRow = sheet.CreateRow(0);      Dictionary<int, ICellStyle> colStyles = new Dictionary<int, ICellStyle>();      for (int i = 0; i < expCols.Length; i++)      {        ICell headerCell = headerRow.CreateCell(i);        headerCell.SetCellValue(expCols[i].HeaderText);        headerCell.CellStyle = headerCellStyle;        sheet.AutoSizeColumn(headerCell.ColumnIndex);        if (colDataFormats != null && colDataFormats.ContainsKey(expCols[i].HeaderText))        {          colStyles[headerCell.ColumnIndex] = Common.GetCellStyleWithDataFormat(workbook, colDataFormats[expCols[i].HeaderText]);        }        else        {          colStyles[headerCell.ColumnIndex] = Common.GetCellStyle(workbook);        }      }      int rowIndex = 1;      foreach (DataGridViewRow row in grid.Rows)      {        IRow dataRow = sheet.CreateRow(rowIndex);        for (int n = 0; n < expCols.Length; n++)        {          ICell cell = dataRow.CreateCell(n);          //cell.SetCellValue((row.Cells[expCols[n].Index].Value ?? "").ToString());          //cell.CellStyle = cellStyle;          Common.SetCellValue(cell, (row.Cells[expCols[n].Index].Value ?? "").ToString(), expCols[n].ValueType, colStyles);          Common.ReSizeColumnWidth(sheet, cell);        }        rowIndex++;      }      sheet.ForceFormulaRecalculation = true;      FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);      workbook.Write(fs);      fs.Dispose();      sheet = null;      headerRow = null;      workbook = null;      return filePath;    }    /// <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>    /// <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)    {      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);      Dictionary<int, ICellStyle> colStyles = new Dictionary<int, ICellStyle>();      ISheet sheet = workbook.CreateSheet(sheetName);      IRow headerRow = sheet.CreateRow(0);      if (colNames == null || colNames.Length <= 0)      {        colNames = sourceTable.Columns.Cast<DataColumn>().OrderBy(c => c.Ordinal).Select(c => c.ColumnName).ToArray();      }      // 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 sourceTable.Rows)      {        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;      FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);      workbook.Write(fs);      fs.Dispose();      sheet = null;      headerRow = null;      workbook = null;      return filePath;    }    /// <summary>    ///由SheetFormatterContainer导出基于EXCEL模板的文件    /// </summary>    /// <param name="templatePath">模板路径</param>    /// <param name="sheetName">模板中使用的工作薄名称</param>    /// <param name="formatterContainer">模板数据格式化容器</param>    /// <param name="filePath">导出路径,可选</param>    /// <returns></returns>    public static string ToExcelWithTemplate<T>(string templatePath, string sheetName, SheetFormatterContainer<T> formatterContainer, string filePath = null)    {      if (!File.Exists(templatePath))      {        throw new FileNotFoundException(templatePath + "文件不存在!");      }      if (string.IsNullOrEmpty(filePath))      {        filePath = Common.GetSaveFilePath();      }      if (string.IsNullOrEmpty(filePath)) return null;      string templateConfigFilePath = Common.GetTemplateConfigFilePath(templatePath, false);      var workbookParameterContainer = new WorkbookParameterContainer();      workbookParameterContainer.Load(templateConfigFilePath);      SheetParameterContainer sheetParameterContainer = workbookParameterContainer[sheetName];      ExportHelper.ExportToLocal(templatePath, filePath, new SheetFormatter(sheetName, formatterContainer.GetFormatters(sheetParameterContainer)));      return filePath;    }    /// <summary>    /// 由SheetFormatterContainer集合导出基于EXCEL模板(多工作薄)的文件    /// </summary>    /// <param name="templatePath"></param>    /// <param name="formatterContainers"></param>    /// <param name="filePath"></param>    /// <returns></returns>    public static string ToExcelWithTemplate(string templatePath,IDictionary<string,SheetFormatterContainer<dynamic>> formatterContainers, string filePath = null)    {      if (!File.Exists(templatePath))      {        throw new FileNotFoundException(templatePath + "文件不存在!");      }      if (string.IsNullOrEmpty(filePath))      {        filePath = Common.GetSaveFilePath();      }      if (string.IsNullOrEmpty(filePath)) return null;      string templateConfigFilePath = Common.GetTemplateConfigFilePath(templatePath, false);      var workbookParameterContainer = new WorkbookParameterContainer();      workbookParameterContainer.Load(templateConfigFilePath);      List<SheetFormatter> sheetFormatterList = new List<SheetFormatter>();      foreach (var item in formatterContainers)      {        SheetParameterContainer sheetParameterContainer = workbookParameterContainer[item.Key];        sheetFormatterList.Add(new SheetFormatter(item.Key, item.Value.GetFormatters(sheetParameterContainer)));      }      ExportHelper.ExportToLocal(templatePath, filePath,sheetFormatterList.ToArray());      return filePath;    }  }}

ExcelUtility.Base.Common:

using ExcelReport;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using NPOI.XSSF.UserModel;using System;using System.Collections.Generic;using System.Data;using System.IO;using System.Linq;using System.Text;using System.Text.RegularExpressions;using System.Windows.Forms;namespace ExcelUtility.Base{  /// <summary>  /// ExcelUtility类库内部通用功能类  /// 作者:Zuowenjun  /// 日期:2016/1/15  /// </summary>  internal static class Common  {    public static string DesktopDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);    /// <summary>    /// 获取要保存的文件名称(含完整路径)    /// </summary>    /// <returns></returns>    public static string GetSaveFilePath()    {      SaveFileDialog saveFileDig = new SaveFileDialog();      saveFileDig.Filter = "Excel Office97-2003(*.xls)|*.xls|Excel Office2007及以上(*.xlsx)|*.xlsx";      saveFileDig.FilterIndex = 0;      saveFileDig.Title = "导出到";      saveFileDig.OverwritePrompt = true;      saveFileDig.InitialDirectory = DesktopDirectory;      string filePath = null;      if (saveFileDig.ShowDialog() == DialogResult.OK)      {        filePath = saveFileDig.FileName;      }      return filePath;    }    /// <summary>    /// 获取要打开要导入的文件名称(含完整路径)    /// </summary>    /// <returns></returns>    public static string GetOpenFilePath()    {      OpenFileDialog openFileDig = new OpenFileDialog();      openFileDig.Filter = "Excel Office97-2003(*.xls)|*.xls|Excel Office2007及以上(*.xlsx)|*.xlsx";      openFileDig.FilterIndex = 0;      openFileDig.Title = "打开";      openFileDig.CheckFileExists = true;      openFileDig.CheckPathExists = true;      openFileDig.InitialDirectory = Common.DesktopDirectory;      string filePath = null;      if (openFileDig.ShowDialog() == DialogResult.OK)      {        filePath = openFileDig.FileName;      }      return filePath;    }    /// <summary>    /// 判断是否为兼容模式    /// </summary>    /// <param name="filePath"></param>    /// <returns></returns>    public static bool GetIsCompatible(string filePath)    {      return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);    }    /// <summary>    /// 创建工作薄    /// </summary>    /// <param name="isCompatible"></param>    /// <returns></returns>    public static IWorkbook CreateWorkbook(bool isCompatible)    {      if (isCompatible)      {        return new HSSFWorkbook();      }      else      {        return new XSSFWorkbook();      }    }    /// <summary>    /// 创建工作薄(依据文件流)    /// </summary>    /// <param name="isCompatible"></param>    /// <param name="stream"></param>    /// <returns></returns>    public static IWorkbook CreateWorkbook(bool isCompatible, dynamic stream)    {      if (isCompatible)      {        return new HSSFWorkbook(stream);      }      else      {        return new XSSFWorkbook(stream);      }    }    /// <summary>    /// 创建单元格样式    /// </summary>    /// <param name="sheet"></param>    /// <returns></returns>    public static ICellStyle GetCellStyle(IWorkbook workbook, bool isHeaderRow = false)    {      ICellStyle style = workbook.CreateCellStyle();      if (isHeaderRow)      {        style.FillPattern = FillPattern.SolidForeground;        style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;        IFont f = workbook.CreateFont();        f.Boldweight = (short)FontBoldWeight.Bold;        style.SetFont(f);      }            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;      style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;      style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;      style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;      return style;    }    /// <summary>    /// 根据单元格内容重新设置列宽    /// </summary>    /// <param name="sheet"></param>    /// <param name="cell"></param>    public static void ReSizeColumnWidth(ISheet sheet, ICell cell)    {      int cellLength = (Encoding.Default.GetBytes(cell.ToString()).Length + 2) * 256;      const int maxLength = 60 * 256; //255 * 256;      if (cellLength > maxLength) //当单元格内容超过30个中文字符(英语60个字符)宽度,则强制换行      {        cellLength = maxLength;        cell.CellStyle.WrapText = true;      }      int colWidth = sheet.GetColumnWidth(cell.ColumnIndex);      if (colWidth < cellLength)      {        sheet.SetColumnWidth(cell.ColumnIndex, cellLength);      }    }    /// <summary>    /// 创建单元格样式并设置数据格式化规则    /// </summary>    /// <param name="cell"></param>    /// <param name="format"></param>    public static ICellStyle GetCellStyleWithDataFormat(IWorkbook workbook, string format)    {      var style = GetCellStyle(workbook);      var dataFormat = workbook.CreateDataFormat();      short formatId = -1;      if (dataFormat is HSSFDataFormat)      {        formatId = HSSFDataFormat.GetBuiltinFormat(format);      }      if (formatId != -1)      {        style.DataFormat = formatId;      }      else      {        style.DataFormat = dataFormat.GetFormat(format);      }      return style;    }    /// <summary>    /// 依据值类型为单元格设置值    /// </summary>    /// <param name="cell"></param>    /// <param name="value"></param>    /// <param name="colType"></param>    public static void SetCellValue(ICell cell, string value, Type colType, IDictionary<int, ICellStyle> colStyles)    {      string dataFormatStr = null;      switch (colType.ToString())      {        case "System.String": //字符串类型          cell.SetCellType(CellType.String);          cell.SetCellValue(value);          break;        case "System.DateTime": //日期类型          DateTime dateV = new DateTime();          DateTime.TryParse(value, out dateV);          cell.SetCellValue(dateV);          dataFormatStr = "yyyy/mm/dd hh:mm:ss";          break;        case "System.Boolean": //布尔型          bool boolV = false;          bool.TryParse(value, out boolV);          cell.SetCellType(CellType.Boolean);          cell.SetCellValue(boolV);          break;        case "System.Int16": //整型        case "System.Int32":        case "System.Int64":        case "System.Byte":          int intV = 0;          int.TryParse(value, out intV);          cell.SetCellType(CellType.Numeric);          cell.SetCellValue(intV);          dataFormatStr = "0";          break;        case "System.Decimal": //浮点型        case "System.Double":          double doubV = 0;          double.TryParse(value, out doubV);          cell.SetCellType(CellType.Numeric);          cell.SetCellValue(doubV);          dataFormatStr = "0.00";          break;        case "System.DBNull": //空值处理          cell.SetCellType(CellType.Blank);          cell.SetCellValue("");          break;        default:          cell.SetCellType(CellType.Unknown);          cell.SetCellValue(value);          break;      }      if (!string.IsNullOrEmpty(dataFormatStr) && colStyles[cell.ColumnIndex].DataFormat <= 0) //没有设置,则采用默认类型格式      {        colStyles[cell.ColumnIndex] = GetCellStyleWithDataFormat(cell.Sheet.Workbook, dataFormatStr);      }      cell.CellStyle = colStyles[cell.ColumnIndex];    }    /// <summary>    /// 从工作表中生成DataTable    /// </summary>    /// <param name="sheet"></param>    /// <param name="headerRowIndex"></param>    /// <returns></returns>    public static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex)    {      DataTable table = new DataTable();      IRow headerRow = sheet.GetRow(headerRowIndex);      int cellCount = headerRow.LastCellNum;      for (int i = headerRow.FirstCellNum; i < cellCount; i++)      {        if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")        {          // 如果遇到第一个空列,则不再继续向后读取          cellCount = i;          break;        }        DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);        table.Columns.Add(column);      }      for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)      {        IRow row = sheet.GetRow(i);        //如果遇到某行的第一个单元格的值为空,则不再继续向下读取        if (row != null && !string.IsNullOrEmpty(row.GetCell(0).ToString()))        {          DataRow dataRow = table.NewRow();          for (int j = row.FirstCellNum; j < cellCount; j++)          {            dataRow[j] = row.GetCell(j).ToString();          }          table.Rows.Add(dataRow);        }      }      return table;    }    /// <summary>    /// 获取模板文件对应的模板格式配置


第六个新增功能,支持模板中包含多个工作薄导出(目前这个方法还没有模拟测试,所以无法保证其一定有效,大家可以试试),该方法定义如下:

    /// <summary>    /// 由SheetFormatterContainer集合导出基于EXCEL模板(多工作薄)的文件    /// </summary>    /// <param name="templatePath"></param>    /// <param name="formatterContainers"></param>    /// <param name="filePath"></param>    /// <returns></returns>    public static string ToExcelWithTemplate(string templatePath,IDictionary<string,SheetFormatterContainer<dynamic>> formatterContainers, string filePath = null)    {      if (!File.Exists(templatePath))      {        throw new FileNotFoundException(templatePath + "文件不存在!");      }      if (string.IsNullOrEmpty(filePath))      {        filePath = Common.GetSaveFilePath();      }      if (string.IsNullOrEmpty(filePath)) return null;      string templateConfigFilePath = Common.GetTemplateConfigFilePath(templatePath, false);      var workbookParameterContainer = new WorkbookParameterContainer();      workbookParameterContainer.Load(templateConfigFilePath);      List<SheetFormatter> sheetFormatterList = new List<SheetFormatter>();      foreach (var item in formatterContainers)      {        SheetParameterContainer sheetParameterContainer = workbookParameterContainer[item.Key];        sheetFormatterList.Add(new SheetFormatter(item.Key, item.Value.GetFormatters(sheetParameterContainer)));      }      ExportHelper.ExportToLocal(templatePath, filePath,sheetFormatterList.ToArray());      return filePath;    }

 

该类库源码已分享到该路径中:http://git.oschina.net/zuowj/ExcelUtility    GIT Repository路径:git@git.oschina.net:zuowj/ExcelUtility.git   (会持续更新及修正BUG)




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

关键词:excel

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

Bloomingdale's 布鲁明戴尔百货店 :https://www.goluckyvip.com/tag/16531.html
Bloomingdale's 布鲁明黛 :https://www.goluckyvip.com/tag/16532.html
bloomingltd:https://www.goluckyvip.com/tag/16533.html
Bloomscape:https://www.goluckyvip.com/tag/16534.html
BLS Int'l:https://www.goluckyvip.com/tag/16535.html
Blue Apron:https://www.goluckyvip.com/tag/16536.html
长治婚庆女司仪和主持人:https://www.vstour.cn/a/366176.html
北京丰台区水上乐园哪家好玩?:https://www.vstour.cn/a/366177.html
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流