// 由于 DataGridColumn 需要 WPF 的才能使用 暂时注释 ///// <summary> ///// 导出Excel ///// 将表格控件的表头和内容导出到Excel文件 ///// </summary> ///// <typeparam name="T"></typeparam> ///// <param name="filePath">保存路径</param> ///// <param name="dgColumns">表格控件表头</param> ///// <param name="entitys">数据源</param> ///// <param name="isAddInExistWorksheets">追加内容到Excel文件中</param> ///// <returns>导出结果</returns> //public static bool ExportToExcel<T>(string filePath, ObservableCollection<DataGridColumn> dgColumns, List<T> entitys, bool isAddInExistWorksheets = false) //{ // List<ExportColumn> transDgColumns = new List<ExportColumn>(); // foreach (DataGridColumn column in dgColumns) // { // ExportColumn ecModel = new ExportColumn(); // System.Windows.Data.Binding columnPath = column.ClipboardContentBinding as System.Windows.Data.Binding; // ecModel.Name = columnPath.Path.Path; // ecModel.Header = column.Header.ToString(); // transDgColumns.Add(ecModel); // } // return NPOIHelper.ExportToExcelForAll<T>(filePath, transDgColumns, entitys, isAddInExistWorksheets); //} /// <summary> /// 导出Excel /// 将自定义的表头和内容导出到Excel文件 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="filePath">保存路径</param> /// <param name="dgColumns">自定义表头集合</param> /// <param name="entitys">数据源</param> /// <param name="isAddInExistWorksheets">追加内容到Excel文件中</param> /// <returns>导出结果</returns> public static bool ExportToExcelForAll <T>(string filePath, IEnumerable <ExportColumn> dgColumns, List <T> entitys, bool isAddInExistWorksheets = false) { if (entitys == null) { throw new Exception("导出数据为 null。"); } bool isSucceed = false; // 导出结果 IWorkbook iworkbook; if (isAddInExistWorksheets == false) // 覆盖已存在的Excel文件 { if (filePath.EndsWith("xlsx", StringComparison.InvariantCultureIgnoreCase)) { iworkbook = new XSSFWorkbook(); } else if (filePath.EndsWith("xls", StringComparison.InvariantCultureIgnoreCase)) { iworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); } else { throw new Exception("必须保存为Excel标准后缀(.xls, .xlsx)。"); } ISheet sheet = iworkbook.CreateSheet(); NPOIHelper.fillSheet <T>(sheet, dgColumns, entitys); // 填充工作表 using (FileStream fOut = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write, FileShare.ReadWrite)) // 写入流 { fOut.Flush(); iworkbook.Write(fOut);//写入文件 isSucceed = true; } } else // 向已存在的Excel文件中追加内容 { if (!File.Exists(filePath)) // 追加的文件不存在 { return(NPOIHelper.ExportToExcelForAll <T>(filePath, dgColumns, entitys, false)); } using (FileStream fRead = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) // 读取流 { if (filePath.EndsWith("xlsx", StringComparison.InvariantCultureIgnoreCase)) { iworkbook = new XSSFWorkbook(fRead); } else if (filePath.EndsWith("xls", StringComparison.InvariantCultureIgnoreCase)) { iworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fRead); } else { throw new Exception("必须保存为Excel标准后缀(.xls, .xlsx)。"); } } ISheet sheet = iworkbook.GetSheetAt(0); // 默认追加到第一个工作表 NPOIHelper.fillSheet <T>(sheet, dgColumns, entitys, true); // 填充工作表 using (FileStream fOut = new FileStream(filePath, FileMode.Open, FileAccess.Write, FileShare.ReadWrite)) // 写入流 { fOut.Flush(); iworkbook.Write(fOut);//写入文件 isSucceed = true; } } iworkbook = null; return(isSucceed); }
// IWorkbook // HSSFWorkbook -- XLS // XSSFWorkbook -- XLSX /// <summary> /// 读取Excel文件 /// </summary> /// <param name="filePath">文件路径</param> /// <param name="fileName">文件名称</param> /// <returns>DataSet 结果集</returns> public static DataSet Excel2DataSet(string filePath, ExcelReaderConfig config = null) { DataSet result = new DataSet(); IWorkbook iWorkbook = null; using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { if (filePath.EndsWith("xlsx")) { iWorkbook = new XSSFWorkbook(fs); } else if (filePath.EndsWith("xls")) { iWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs); } else { throw new Exception("必须保存为Excel标准后缀(.xls, .xlsx)。"); } int sheetCount = iWorkbook.NumberOfSheets; // 工作表总数 for (int sheetNo = 0; sheetNo < sheetCount; sheetNo++) { SheetReadConfig matchReadConfig = null; ISheet sheet = iWorkbook.GetSheetAt(sheetNo); if (config != null && config.Config != null) { matchReadConfig = config.Config.FirstOrDefault(j => j.SheetName == sheet.SheetName || (j.SheetIndex.HasValue == true && j.SheetIndex == sheetNo)); if (matchReadConfig == null) { continue; } } DataTable dt = new DataTable(); #region 表头 IRow header = sheet.GetRow(sheet.FirstRowNum); if (header == null) { continue; } List <int> columns = new List <int>(); for (int i = 0; i < header.LastCellNum; i++) { if (header.GetCell(i) != null) { object obj = NPOIHelper.GetValueType(header.GetCell(i) as ICell); if (obj == null || obj.ToString() == string.Empty) { dt.Columns.Add(new DataColumn("Columns" + i.ToString())); //continue; } else { dt.Columns.Add(new DataColumn(obj.ToString())); } columns.Add(i); } } #endregion #region 数据 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow dr = dt.NewRow(); bool rowHasValue = false; // TODO : 遇到空行 计数问题未解决 // TODO : 计算行号 foreach (int j in columns) { if (sheet.GetRow(i) != null && sheet.GetRow(i).GetCell(j) != null) { if (matchReadConfig != null && matchReadConfig.CellReadRule != null && matchReadConfig.CellReadRule.ContainsKey(j)) { CellType t = CellType.Blank; if (matchReadConfig.CellReadRule.TryGetValue(j, out t)) { try { switch (t) { case CellType.String: { dr[j] = sheet.GetRow(i).GetCell(j).StringCellValue; if (rowHasValue != true && dr[j] != null && string.IsNullOrEmpty(dr[j].ToString()) == false) { rowHasValue = true; } } break; case CellType.Formula: { dr[j] = sheet.GetRow(i).GetCell(j).CellFormula; if (rowHasValue != true && dr[j] != null && string.IsNullOrEmpty(dr[j].ToString()) == false) { rowHasValue = true; } } break; case CellType.DateTime: { dr[j] = sheet.GetRow(i).GetCell(j).DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff"); if (rowHasValue != true && dr[j].Equals(DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss.fff")) == false) { rowHasValue = true; } } break; case CellType.Blank: dr[j] = string.Empty; break; default: dr[j] = string.Empty; break; } } catch (Exception ex) { throw new Exception("NPOIHelperV3 - matchReadConfig.CellReadRule Swicth Throw Exception", ex); } } else { dr[j] = NPOIHelper.GetValueType(sheet.GetRow(i).GetCell(j) as ICell); if (rowHasValue != true && dr[j] != null && dr[j].ToString() != string.Empty) { rowHasValue = true; } } } else { // TODO : 读取日期Cell问题仍需解决 dr[j] = NPOIHelper.GetValueType(sheet.GetRow(i).GetCell(j) as ICell); if (rowHasValue != true && dr[j] != null && dr[j].ToString() != string.Empty) { rowHasValue = true; } } } } if (rowHasValue) { dt.Rows.Add(dr); } } #endregion result.Tables.Add(dt); } } return(result); }