分享我基于NPOI+ExcelReport实现的导入与导出EXCEL类库：ExcelUtility （续篇） - 梦在旅(2)_H5之家

/// <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>要导出数据的DataTable</param> /// <param>导出路径，可选</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>要导出数据的DataTable</param> /// <param>导出的列名重命名数组</param> /// <param>工作薄名称，可选</param> /// <param>导出路径，可选</param> /// <param>列格式化集合，可选</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>要导出的DataGridView对象</param> /// <param>工作薄名称，可选</param> /// <param>导出路径，可选</param> /// <param>导出时是否包含隐藏列，可选</param> /// <param>指定导出DataGridView的列标题名数组，可选</param> /// <param>列格式化集合，可选</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>要导出数据的DataTable</param> /// <param>工作薄名称，可选</param> /// <param>导出路径，可选</param> /// <param>需要导出的列名,可选</param> /// <param>导出的列名重命名，可选</param> /// <param>列格式化集合，可选</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>模板路径</param> /// <param>模板中使用的工作薄名称</param> /// <param>模板数据格式化容器</param> /// <param>导出路径，可选</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></param> /// <param></param> /// <param></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：