public void FormatAsSubheaderRow(int columnCount, NPOI.SS.UserModel.HorizontalAlignment xAlign = NPOI.SS.UserModel.HorizontalAlignment.CENTER) { for (int index = 0; index < columnCount; index++) { _HSSFCurrentRow.CreateCell(index + offset).CellStyle = HeaderCellStyle(xAlign); } }
public void AddFooterCellToCurrentRow(string text, int index, NPOI.SS.UserModel.HorizontalAlignment xAlign = NPOI.SS.UserModel.HorizontalAlignment.LEFT) { //HSSFCellStyle style = FooterCellStyle(xAlign); int idx = (index < 0) ? columnIndex:index; ManualAdjustColumnWidth(text, idx); //HSSFCell cellFtr = (HSSFCell)_HSSFCurrentRow.GetCell(idx); HSSFFont fFooter = (HSSFFont)workbook.CreateFont(); fFooter.FontName = "Calibri"; fFooter.Boldweight = (short)FontBoldWeight.BOLD; fFooter.Color = HSSFColor.BLACK.index; HSSFCellStyle cFooterStyle = (HSSFCellStyle)workbook.CreateCellStyle(); cFooterStyle.SetFont(fFooter); cFooterStyle.BorderTop = CellBorderType.THIN; cFooterStyle.TopBorderColor = HSSFColor.GREY_50_PERCENT.index; HSSFCell cellFtr = (HSSFCell)_HSSFCurrentRow.CreateCell(idx, CellType.STRING); cellFtr.CellStyle = cFooterStyle; cellFtr.CellStyle.BorderLeft = CellBorderType.NONE; cellFtr.CellStyle.BorderRight = CellBorderType.NONE; cellFtr.SetCellValue(text); ManualAdjustColumnWidth(text, idx); cellFtr.CellStyle.Alignment = xAlign; columnIndex++; }
private string ConvertHorizontalAlignmentToString(NPOI.SS.UserModel.HorizontalAlignment alignment) { string Result = "text-align:"; switch (alignment) { case NPOI.SS.UserModel.HorizontalAlignment.Left: return(Result + "LEFT;"); case NPOI.SS.UserModel.HorizontalAlignment.Center: return(Result + "CENTER;"); case NPOI.SS.UserModel.HorizontalAlignment.CenterSelection: return(Result + "CENTER_SELECTION;"); case NPOI.SS.UserModel.HorizontalAlignment.Right: return(Result + "RIGHT;"); case NPOI.SS.UserModel.HorizontalAlignment.Distributed: return(Result + "DISTRIBUTED;"); case NPOI.SS.UserModel.HorizontalAlignment.Fill: return(Result + "FILL;"); case NPOI.SS.UserModel.HorizontalAlignment.Justify: return(Result + "JUSTIFY;"); default: return(""); } }
private ICellStyle CellHeaderStyle(IWorkbook wb, HorizontalAlignment alignment) { ICellStyle cell = CreateBorderedCellStyle(wb); cell.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cell.Alignment = alignment; cell.WrapText = true; return(cell); }
public void AddNumericCellToCurrentRow(double value, NPOI.SS.UserModel.HorizontalAlignment xAlign = NPOI.SS.UserModel.HorizontalAlignment.RIGHT) { try { _HSSFCurrentRow.GetCell(columnIndex).SetCellValue(value); ManualAdjustColumnWidth(Convert.ToString(value)); _HSSFCurrentRow.GetCell(columnIndex).CellStyle.Alignment = xAlign; } catch { } columnIndex++; }
public void AddCurrencyFooterCellToCurrentRow(decimal amount, int index, NPOI.SS.UserModel.HorizontalAlignment xAlign = NPOI.SS.UserModel.HorizontalAlignment.RIGHT) { HSSFCell cell = (HSSFCell)_HSSFCurrentRow.CreateCell(index, CellType.STRING); ManualAdjustColumnWidth(Convert.ToString(amount), index); HSSFCellStyle style = FooterCellStyle(xAlign, df.GetFormat("$#,##0.00")); cell.SetCellValue(Convert.ToDouble(amount)); cell.CellStyle = style; columnIndex++; }
public void AddHeaderCell(string caption, NPOI.SS.UserModel.HorizontalAlignment xAlign = NPOI.SS.UserModel.HorizontalAlignment.CENTER) { HSSFCell cell = (HSSFCell)_HSSFCurrentRow.CreateCell(columnIndex, CellType.STRING); cell.CellStyle = GetDefaultHeaderStyle(); cell.CellStyle.BorderLeft = CellBorderType.NONE; cell.CellStyle.BorderRight = CellBorderType.NONE; cell.CellStyle.BorderBottom = CellBorderType.MEDIUM; cell.CellStyle.BottomBorderColor = HSSFColor.GREY_50_PERCENT.index; cell.SetCellValue(caption); ManualAdjustColumnWidth(caption); cell.CellStyle.Alignment = xAlign; columnIndex++; }
private HSSFCellStyle HeaderCellStyle(NPOI.SS.UserModel.HorizontalAlignment xHAlignment) { //start as per marty's suggestions //cellHeaderStyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index; //cellHeaderStyle.FillPattern = FillPatternType.SOLID_FOREGROUND; fontHeader.Color = HSSFColor.BLACK.index; //end as per marty's suggestions fontHeader.FontName = "Calibri"; fontHeader.Boldweight = (short)FontBoldWeight.BOLD; cellHeaderStyle.SetFont(fontHeader); cellHeaderStyle.Alignment = xHAlignment; cellHeaderStyle.BorderBottom = CellBorderType.THIN; cellHeaderStyle.BottomBorderColor = HSSFColor.BLACK.index; return(cellHeaderStyle); }
private HSSFCellStyle FooterCellStyle(NPOI.SS.UserModel.HorizontalAlignment xAlign = HorizontalAlignment.GENERAL, short dataFormat = -1) { //cellFooterStyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index; //cellFooterStyle.FillPattern = FillPatternType.SOLID_FOREGROUND; fontFooter.FontName = "Calibri"; fontFooter.Boldweight = (short)FontBoldWeight.BOLD; fontFooter.Color = HSSFColor.BLACK.index; cellFooterStyle.SetFont(fontFooter); if (dataFormat > 0) { cellFooterStyle.DataFormat = dataFormat; } cellFooterStyle.BorderTop = CellBorderType.THIN; cellFooterStyle.TopBorderColor = HSSFColor.GREY_50_PERCENT.index; cellFooterStyle.Alignment = xAlign; return(cellFooterStyle); }
public ICellStyle MergeCellStyle(IWorkbook wb, HorizontalAlignment horizontalAlignment) { ICellStyle cell = CreateBorderedCellStyle(wb); IFont font = wb.CreateFont(); font.FontName = "Times New Roman"; font.FontHeightInPoints = 12; font.Boldweight = (short)FontBoldWeight.Bold; font.Color = HSSFColor.Black.Index; cell.Alignment = horizontalAlignment; cell.VerticalAlignment = VerticalAlignment.Center; cell.FillForegroundColor = IndexedColors.LightCornflowerBlue.Index; cell.FillPattern = FillPattern.SolidForeground; //cell.ShrinkToFit = true; cell.SetFont(font); return(cell); }
public void AddCurrencyCell(decimal amount, NPOI.SS.UserModel.HorizontalAlignment xAlign = NPOI.SS.UserModel.HorizontalAlignment.GENERAL) { HSSFCell cell = (HSSFCell)_HSSFCurrentRow.CreateCell(columnIndex); cell.SetCellValue(Convert.ToDouble(amount)); cell.CellStyle = GetCurrencyStyleFormat(); cell.CellStyle.BorderLeft = CellBorderType.NONE; cell.CellStyle.BorderRight = CellBorderType.NONE; ManualAdjustColumnWidth(Convert.ToString(amount)); if (xAlign != HorizontalAlignment.GENERAL) { cell.CellStyle.Alignment = xAlign; } else { cell.CellStyle.Alignment = HorizontalAlignment.RIGHT; } columnIndex++; }
private ICellStyle NomalBoldCellStyle(IWorkbook wb, HorizontalAlignment horizontalAlignment) { ICellStyle cell = CreateBorderedCellStyle(wb); cell.VerticalAlignment = VerticalAlignment.Center; cell.Alignment = horizontalAlignment; cell.WrapText = true; cell.ShrinkToFit = true; IFont font = wb.CreateFont(); font.FontName = "Times New Roman"; font.FontHeightInPoints = 11; font.Boldweight = (short)FontBoldWeight.Bold; font.Color = HSSFColor.Black.Index; cell.SetFont(font); return(cell); }
/// <summary> /// 设置单元格对齐 /// </summary> /// <param name="rowIdx">行号</param> /// <param name="colIdx">列号</param> /// <param name="style">单元格格式</param> public void SetCellStyleAlignment(int rowIdx, int colIdx, HorizontalAlignment horizontal, VerticalAlignment vertical) { IRow row = currentSheet.GetRow(rowIdx); if (row == null) { return; } ICell cell = row.GetCell(colIdx); if (cell == null) { return; } ICellStyle style = workbook.CreateCellStyle(); style.Alignment = horizontal; style.VerticalAlignment = vertical; cell.CellStyle = style; }
/// <summary> /// 获取单元格样式 /// </summary> /// <param name="hssfworkbook">Excel操作类</param> /// <param name="border">是否有边框</param> /// <param name="font">单元格字体</param> /// <param name="ha">垂直对齐方式</param> /// <param name="va">垂直对齐方式</param> /// <returns></returns> public static ICellStyle GetCellStyle(HSSFWorkbook hssfworkbook, HorizontalAlignment ha, VerticalAlignment va, bool border = true, IFont font = null) { ICellStyle cellstyle = hssfworkbook.CreateCellStyle(); cellstyle.Alignment = ha; cellstyle.VerticalAlignment = va; if (font != null) { cellstyle.SetFont(font); } //有边框 if (border) { cellstyle.BorderBottom = BorderStyle.Thin; cellstyle.BorderLeft = BorderStyle.Thin; cellstyle.BorderRight = BorderStyle.Thin; cellstyle.BorderTop = BorderStyle.Thin; } return(cellstyle); }
/// <summary> /// 返回列的样式并存储到样式列表 /// </summary> /// <param name="font"></param> /// <param name="alignment"></param> /// <param name="b">是否加边框</param> /// <returns></returns> public static ICellStyle GetCellStyle(IFont font, HorizontalAlignment alignment, bool b = false) { var cs = IsHasCellStyle(font, alignment, b); if (cs != null) return cs; ICellStyle cellStyle = wb.CreateCellStyle(); //水平对齐 cellStyle.Alignment = alignment; //垂直对齐 cellStyle.VerticalAlignment = VerticalAlignment.Center; //自动换行 cellStyle.WrapText = true; //缩进;当设置为1时,前面留的空白太大了。或者是我设置的不对 cellStyle.Indention = 0; //设置字体样式 cellStyle.SetFont(font); if (b) { //边框 cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; cellStyle.BorderTop = BorderStyle.Thin; //边框颜色 cellStyle.BottomBorderColor = HSSFColor.Black.Index; cellStyle.TopBorderColor = HSSFColor.Black.Index; } cellStyleList.Add(cellStyle); return cellStyle; }
/// <summary> /// 增加一个合并功能 /// </summary> /// <param name="fristRow">开始行号</param> /// <param name="lastRow">结束行号</param> /// <param name="fristColumn">开始列号</param> /// <param name="lastColumn">结束列号</param> /// <param name="title">内容</param> /// <param name="hssfworkbook">EXCEL工作簿</param> /// <param name="sheet">工作薄</param> /// <param name="fontSize">字体大小</param> /// <param name="alignment">字体对齐方式</param> protected void AddRow(int fristRow, int lastRow, int fristColumn, int lastColumn, string title, IWorkbook hssfworkbook, ISheet sheet, short fontSize, NPOI.SS.UserModel.HorizontalAlignment alignment) { sheet.AddMergedRegion(new CellRangeAddress(fristRow, lastRow, fristColumn, lastColumn - 1)); sheet.GetRow(fristRow).CreateCell(fristColumn).SetCellValue(title); for (var i = 1; i < lastColumn; i++) { sheet.GetRow(fristRow).CreateCell(i).SetCellValue(""); } var subtotalStyle = hssfworkbook.CreateCellStyle(); subtotalStyle.Alignment = alignment; //subtotalStyle.VerticalAlignment = VerticalAlignment.CENTER; //subtotalStyle.BorderTop = BorderStyle.THIN; //subtotalStyle.BorderBottom = BorderStyle.THIN; //subtotalStyle.BorderLeft = BorderStyle.THIN; //subtotalStyle.BorderRight = BorderStyle.THIN; //subtotalStyle.TopBorderColor = IndexedColors.BLACK.Index; //subtotalStyle.BottomBorderColor = IndexedColors.BLACK.Index; //subtotalStyle.LeftBorderColor = IndexedColors.BLACK.Index; //subtotalStyle.RightBorderColor = IndexedColors.BLACK.Index; var font = hssfworkbook.CreateFont(); font.FontHeightInPoints = fontSize; font.Boldweight = 100 * 100; var currentRow = sheet.GetRow(fristRow); for (var i = 0; i < lastColumn; i++) { currentRow.GetCell(i).CellStyle = subtotalStyle; currentRow.GetCell(i).CellStyle.SetFont(font); } }
/// <summary> /// 创建head 黑体 /// </summary> /// <param name="str">要特殊显示的文字,多个内容用'|'分隔开</param> /// <param name="msg">显示的文字</param> /// <param name="row">哪一行</param> /// <param name="fontSize">字体大小</param> /// <param name="ha">居中/居左/居右</param> public void CreateHeadCell(string str, string msg, int row, short fontSize, HorizontalAlignment ha = HorizontalAlignment.Center) { var cell = NpoiStyle.MergedRegion(sheet, row, row, 0, count); var font = NpoiStyle.GetFont(fontSize, "黑体"); var font2 = NpoiStyle.GetFont(fontSize, "黑体", 1); var style = NpoiStyle.GetCellStyle(font, ha); if (!string.IsNullOrEmpty(str)) { var s = str.Split('|'); var richText = NpoiStyle.RichText(s, msg, font, font2); cell.SetCellValue(richText); } else { cell.SetCellValue(msg); } cell.CellStyle = style; }
/// <summary> /// 是否存在-列样式 /// </summary> /// <param name="font">字体样式</param> /// <param name="alignment">单元格水平对齐方式</param> /// <param name="b"></param> /// <returns></returns> private static ICellStyle IsHasCellStyle(IFont font, HorizontalAlignment alignment, bool b) { if (wb == null) wb = new HSSFWorkbook(); foreach (var cellStyle in cellStyleList) { if (cellStyle.Alignment == alignment && cellStyle.GetFont(wb) == font) { if ( (cellStyle.TopBorderColor == HSSFColor.Black.Index && b) || (cellStyle.TopBorderColor != HSSFColor.Black.Index && !b) ) { return cellStyle; } break; } } return null; }