/// <summary> /// 将datatable数据生成为excel文件 /// </summary> /// <param name="info">导出配置信息/param> /// <param name="dt">导出数据</param> /// <param name="fileName">文件名</param> public bool ExportExcelAuto(string tempFileFullPath, SheetInfo info, DataTable dt, LAF.Common.ExcelOperation.ExcelOperationHelper.DelegateCheck dCheck, params string[] Edition) { info.DataArray = this.ConvertDtToList(info, dt); return(new ExcelOperationHelper().WriteWorkbookAuto(tempFileFullPath, info, dt, dCheck, Edition)); }
/// <summary> /// 将数据生成为excel文件 /// 创建者:戚鹏 /// 创建日期:2013.5.20 /// <param name="info">配置信息</param> /// <param name="tempFileFullPath">文件名</param> /// </summary> public bool WriteWorkbookAuto(string tempFileFullPath, SheetInfo info, DataTable dt, LAF.Common.ExcelOperation.ExcelOperationHelper.DelegateCheck dCheck, params string[] Edition) { try { XSSFWorkbook workbook; workbook = new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet(info.SheetName); //冻结列 sheet.CreateFreezePane(0, 2, 0, 2); //工作簿加密 //TODO:暂时不加密 为了标题能够过滤 if (info.Protect) { sheet.ProtectSheet(EXCELPWD); } #region 主体内容 #region Title样式 IRow rows = sheet.CreateRow(1); rows.HeightInPoints = 30; #endregion #region 隐藏行 IRow rowHidden = sheet.CreateRow(0); rowHidden.Height = 1; #endregion for (int i = 0; i < info.ColInfos.Count; i++) { int ColumnWidth = info.ColInfos[i].ColumnWidth == 0 ? 20 : info.ColInfos[i].ColumnWidth; if (info.ColInfos[i].ColumnHidden) { sheet.SetColumnWidth(i, 0); } else { sheet.SetColumnWidth(i, ColumnWidth * 256); } #region Title样式 ICell cell1 = rows.CreateCell(i); cell1.CellStyle = GetCellStyle(workbook, stylexls.Title); string titleStr = info.ColInfos[i].ColumnTitle; //标题*号进行样式设定 IRichTextString rt = workbook.GetCreationHelper().CreateRichTextString(titleStr); if (titleStr.EndsWith("*")) { IFont fontStar = workbook.CreateFont(); fontStar.Boldweight = 600; fontStar.Color = HSSFColor.OliveGreen.Red.Index; rt.ApplyFont(titleStr.Length - 1, titleStr.Length, fontStar); } cell1.SetCellValue(rt); #endregion #region 写入数据KEY ICell cell = rowHidden.CreateCell(i); cell.SetCellValue(info.ColInfos[i].ColumnName); cell.CellStyle.IsLocked = true; #endregion #region 添加验证 IDataValidationHelper helper = sheet.GetDataValidationHelper(); CellRangeAddressList range = getCellRangeAddressList(info.DataArray.Count, i); if (info.ColInfos[i].ColumnRangeValues != null) { sheet.AddValidationData(ExcelOperationValidationHelper.getValidationExplicitList(helper, range, info.ColInfos[i].ColumnRangeValues)); } if (info.ColInfos[i].ColValMaxLength.HasValue) { sheet.AddValidationData(ExcelOperationValidationHelper.getValidationTextLength(helper, range, 1, info.ColInfos[i].ColValMaxLength.GetValueOrDefault())); } switch (info.ColInfos[i].ColumnValidation) { case EmuExcelCellType.Integer: sheet.AddValidationData(ExcelOperationValidationHelper.getValidationInt(helper, range)); break; case EmuExcelCellType.Decimal: sheet.AddValidationData(ExcelOperationValidationHelper.getValidationInt(helper, range)); break; case EmuExcelCellType.YearMonth: sheet.AddValidationData(ExcelOperationValidationHelper.getValidationYearMonth(helper, range)); break; default: break; } #endregion #region 筛选 CellRangeAddress CellRange = new CellRangeAddressList(1, 1, 0, i).CellRangeAddresses[0]; sheet.SetAutoFilter(CellRange); #endregion } ICellStyle style = SetCellBorder(workbook, false); ICellStyle styleLock = SetCellBorder(workbook, true); foreach (List <CellInfo> items in info.DataArray) { foreach (CellInfo item in items) { IRow row = sheet.GetRow(int.Parse(item.YPosition)); if (row == null) { row = sheet.CreateRow(int.Parse(item.YPosition)); } ICell cell = row.GetCell(int.Parse(item.XPosition)); if (cell == null) { cell = row.CreateCell(int.Parse(item.XPosition)); } double cellValue = 0; //如果是数值 把单元格格式设置为数值 if (double.TryParse(item.Value, out cellValue)) { cell.SetCellType(CellType.Numeric); cell.SetCellValue(cellValue); //XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle(); //IDataFormat dataFormat = workbook.CreateDataFormat(); //cellStyle.DataFormat = dataFormat; //cellStyle. } else { cell.SetCellType(CellType.String); cell.SetCellValue(item.Value); } cell.CellStyle = item.ColumnLock ? styleLock : style; } } #endregion #region 数据校验 bool hasErr = false; if (dCheck != null) { //复制dt结构(不复制数据) DataTable dtTemp = dt.Clone(); DataRow[] rules = dt.Select(); foreach (var dr in rules) { //int RowCount = sheet.GetRow(dt.Rows.IndexOf(dr) + 2).LastCellNum; //for (int i = 2; i < RowCount; i++) //{ // ICell Cell = sheet.GetRow(dt.Rows.IndexOf(dr) + 2).Cells[i]; // Cell.CellComment = null; // Cell.CellStyle = style; //} dtTemp.Rows.Add(dr.ItemArray); Tuple <List <string[]> > tu = dCheck.Invoke(dtTemp); if (tu.Item1.Count > 0) { hasErr = true; for (int i = 0; i < tu.Item1.Count; i++) { int iConNum = int.Parse(tu.Item1[i][0]); string strMsg = tu.Item1[i][1]; ICell Cell = sheet.GetRow(dt.Rows.IndexOf(dr) + 2).GetCell(iConNum); Cell.CellStyle = GetCellBorderException(workbook); Cell.CellComment = null; Cell.CellComment = GetCellComment(sheet, strMsg); } } else { //隐藏正确行 sheet.GetRow(dt.Rows.IndexOf(dr) + 2).Height = 1; } dtTemp.Rows.Clear(); } } #endregion #region 版本号 ISheet sheetEdition = null; sheetEdition = workbook.CreateSheet("Property"); for (int r = 0; r < Edition.Length; r++) { sheetEdition.CreateRow(r).CreateCell(0).SetCellValue(Edition[r]); } workbook.SetSheetHidden(1, SheetState.VeryHidden); #endregion //写入文件 using (FileStream fs = new FileStream(tempFileFullPath, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { workbook.Write(fs); fs.Close(); } return(hasErr); } catch (Exception ex) { throw ex; } }