private static void CreateHeader(ISheet excelSheet, ICellStyle style, List <ExeclCellStyle> execlCellStyleList) { int cellIndex = 0; IRow newRow = excelSheet.CreateRow(1); //循环导出列 foreach (var de in ListColumnsName) { ICell newCell = newRow.CreateCell(cellIndex); newCell.SetCellValue(de.ToString()); //按表头文字的宽度 excelSheet.SetColumnWidth(cellIndex, de.ToString().Length * 650); ExeclCellStyle execlCellStyle = execlCellStyleList.FirstOrDefault(it => it.ColumnsName == de.ToString()); if (execlCellStyle != null) { newCell.CellStyle = execlCellStyle.TitleStyle;//用户自定义表头样式 } else { newCell.CellStyle = style;//赋默认值 } cellIndex++; } }
/// <summary> /// 合同单元格样式 /// </summary> /// <param name="dicTitle"></param> /// <param name="excelWorkbook">excelWorkbook</param> /// <returns></returns> public static List <ExeclCellStyle> GetContractExeclCellStyleList(Dictionary <string, string> dicTitle, IWorkbook excelWorkbook) { List <ExeclCellStyle> execlCellStyleList = new List <ExeclCellStyle>(); foreach (var item in dicTitle) { string columnsName = item.Key.ToString(); ExeclCellStyle execlCellStyleEntity = new ExeclCellStyle(); execlCellStyleEntity.IsLock = false; execlCellStyleEntity.ColumnsName = columnsName; execlCellStyleEntity.BackGrandIndexed = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; execlCellStyleEntity.IsHidden = false; execlCellStyleEntity.TitleStyle = NoEditTitleStyle(excelWorkbook); execlCellStyleEntity.CellStyle = NoEditStyle(excelWorkbook); switch (columnsName) { case "ProductCode": execlCellStyleEntity.Width = 20; break; case "ProdcutName": execlCellStyleEntity.Width = 30; break; case "ProductTypeName": execlCellStyleEntity.Width = 10; break; case "ProductAttribute": execlCellStyleEntity.Width = 80; break; case "ProductUnit": execlCellStyleEntity.Width = 10; break; case "TacticCgAgreementPrice": execlCellStyleEntity.Width = 30; execlCellStyleEntity.CellStyle.Alignment = HorizontalAlignment.Right; //金额右对齐 break; case "ContractCount": execlCellStyleEntity.Width = 10; execlCellStyleEntity.BackGrandIndexed = NPOI.HSSF.Util.HSSFColor.White.Index; execlCellStyleEntity.TitleStyle = EditTitleStyle(excelWorkbook); execlCellStyleEntity.CellStyle = EditStyle(excelWorkbook); execlCellStyleEntity.CellStyle.Alignment = HorizontalAlignment.Right; //金额右对齐 break; case "ContractPrice": execlCellStyleEntity.Width = 15; execlCellStyleEntity.BackGrandIndexed = NPOI.HSSF.Util.HSSFColor.White.Index; execlCellStyleEntity.TitleStyle = EditTitleStyle(excelWorkbook); execlCellStyleEntity.CellStyle = EditStyle(excelWorkbook); execlCellStyleEntity.CellStyle.Alignment = HorizontalAlignment.Right; //金额右对齐 break; case "TotalPrice": execlCellStyleEntity.Width = 15; execlCellStyleEntity.CellStyle.Alignment = HorizontalAlignment.Right; //金额右对齐 execlCellStyleEntity.SetCellFormula = "I{0}*J{0}"; break; case "ContractProductRemark": execlCellStyleEntity.Width = 80; execlCellStyleEntity.BackGrandIndexed = NPOI.HSSF.Util.HSSFColor.White.Index; execlCellStyleEntity.TitleStyle = EditTitleStyle(excelWorkbook); execlCellStyleEntity.CellStyle = EditStyle(excelWorkbook); break; case "ContractGUID": execlCellStyleEntity.Width = 5; execlCellStyleEntity.IsHidden = true; break; case "ProductGUID": execlCellStyleEntity.Width = 5; execlCellStyleEntity.IsHidden = true; break; default: break; } execlCellStyleList.Add(execlCellStyleEntity); } return(execlCellStyleList); }
/// <summary> /// 单元格格式 /// </summary> /// <param name="execlCell"></param> /// <param name="execlCellStyle"></param> /// <returns></returns> private static void SetExeclCellStyle(ICell execlCell, ExeclCellStyle execlCellStyle) { execlCell.CellStyle = execlCellStyle.CellStyle; }
/// <summary> /// 列锁定 /// </summary> /// <param name="columnStyle"></param> /// <param name="excelWorkBook"></param> /// <param name="execlCellStyle"></param> private static ICellStyle SetExeclColumnStyle(ICellStyle columnStyle, IWorkbook excelWorkBook, ExeclCellStyle execlCellStyle) { if (columnStyle == null) { columnStyle = excelWorkBook.CreateCellStyle(); } if (execlCellStyle != null) { columnStyle.IsLocked = execlCellStyle.IsLock; columnStyle.IsHidden = execlCellStyle.IsHidden; } return(columnStyle); }
/// <summary> /// 导出数据行 /// </summary> /// <param name="dtSource">数据表</param> /// <param name="drSource">来源数据行</param> /// <param name="currentExcelRow">当前数据行</param> /// <param name="excelSheet">sheet集合</param> /// <param name="excelWorkBook">数据表</param> /// <param name="execlCellStyleList">表头样式</param> /// <param name="rowCount">execl当前行</param> private static void InsertCell(DataTable dtSource, DataRow drSource, IRow currentExcelRow, ISheet excelSheet, IWorkbook excelWorkBook, List <ExeclCellStyle> execlCellStyleList, int rowCount) { int cellIndex = 0; foreach (var item in ListColumnsName) { //列名称 string columnsName = item.ToString(); //根据列名称设置列样式 ExeclCellStyle execlCellStyle = execlCellStyleList?.FirstOrDefault(it => it.ColumnsName == columnsName) ?? null; ICellStyle columnStyle = excelSheet.GetColumnStyle(cellIndex); //设置列样式 //excelSheet.SetDefaultColumnStyle(cellIndex, SetExeclColumnStyle(columnStyle, excelWorkBook, execlCellStyle)); //设置列宽 if ((execlCellStyle?.Width ?? 0) > 0) { excelSheet.SetColumnWidth(cellIndex, execlCellStyle.Width); } if (execlCellStyle?.IsHidden ?? false) { excelSheet.SetColumnHidden(cellIndex, true); } ICell newCell = null; System.Type rowType = drSource[columnsName].GetType(); string drValue = drSource[columnsName].ToString().Trim(); //错误消息列 string errColumn = columnsName + "ErrMsg"; string errMsg = string.Empty; if (dtSource.Columns.Contains(errColumn)) { errMsg = drSource[errColumn].ToString().Trim(); } switch (rowType.ToString()) { case "System.String": //字符串类型 drValue = drValue.Replace("&", "&"); drValue = drValue.Replace(">", ">"); drValue = drValue.Replace("<", "<"); newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(drValue); break; case "System.Guid": //GUID类型 newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(dateV); //格式化显示 ICellStyle cellStyle = excelWorkBook.CreateCellStyle(); IDataFormat format = excelWorkBook.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss"); newCell.CellStyle = cellStyle; break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(intV.ToString()); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(""); break; default: throw (new Exception(rowType.ToString() + ":类型数据无法处理!")); } //设置单元格样式 SetExeclCellStyle(newCell, execlCellStyle); //设置批注 if (!string.IsNullOrWhiteSpace(errMsg)) { SetComment(errMsg, currentExcelRow, newCell, excelWorkBook.GetCreationHelper(), (HSSFPatriarch)excelSheet.CreateDrawingPatriarch()); } //设置计算表达式 if (!string.IsNullOrEmpty(execlCellStyle.SetCellFormula)) { int rowIndex = rowCount + 1; newCell.SetCellFormula(string.Format(execlCellStyle.SetCellFormula, rowIndex)); if (excelSheet.ForceFormulaRecalculation == false) { excelSheet.ForceFormulaRecalculation = true;//没有此句,则不会刷新出计算结果 } } cellIndex++; } bool shouldLock = execlCellStyleList?.FirstOrDefault(it => it.IsLock == true)?.IsLock ?? false; if (shouldLock == true) { excelSheet.ProtectSheet("password");//设置密码保护 } }