分享我基于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)); }
结果如下图示:
第二个新增功能,依据数据源(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)); }
结果如下图示:
第三个新增功能,在第二个新增功能的基础上,增加可以自定义设置列的单元格显示格式(支持日期类型、数字类型),任意一个无模板的导出方法均支持该功能,示例代码如下:
/// <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)); }
结果如下图示:
换种格式定义测试:
/// <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)); }
结果如下图示:
注意事项说明:想要实现导出的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)); }
结果如下图示:
第五个新增功能,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)); }
结果如下图示:
以下是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> /// 获取模板文件对应的模板格式配置XML文件路径(当不存在或较旧时,将会重新生成) /// </summary> /// <param name="templatePath"></param> /// <param name="newGenerate"></param> /// <returns></returns> public static string GetTemplateConfigFilePath(string templatePath, bool newGenerate = false) { string templateConfigFilePath = Path.ChangeExtension(templatePath, ".xml"); if (newGenerate || !File.Exists(templateConfigFilePath) || File.GetLastWriteTime(templatePath) > File.GetLastWriteTime(templateConfigFilePath)) { WorkbookParameterContainer workbookParameter = ParseTemplate.Parse(templatePath); workbookParameter.Save(templateConfigFilePath); } return templateConfigFilePath; } } }
第六个新增功能,支持模板中包含多个工作薄导出(目前这个方法还没有模拟测试,所以无法保证其一定有效,大家可以试试),该方法定义如下:
/// <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)