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;
}
