private static ICellStyle GetCellStyle(IWorkbook wb, XlsStyle str, Font headFont = null, Font contentFont = null, Font titleFont = null, Font bottomFont = null)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font = wb.CreateFont();
font.FontName = "微软雅黑";
font.FontHeightInPoints = 10;
if (contentFont != null)
{
font.FontName = contentFont.Name;
if (contentFont.Bold)
{
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
}
font.FontHeightInPoints = (short)contentFont.SizeInPoints;
font.IsItalic = contentFont.Italic;
font.IsStrikeout = contentFont.Strikeout;
font.Underline = (byte)(contentFont.Underline ? 1 : 0);
}
IFont linkAddresFont = wb.CreateFont();
linkAddresFont.Color = HSSFColor.OLIVE_GREEN.BLUE.index;
linkAddresFont.IsItalic = true;//下划线
linkAddresFont.FontName = "微软雅黑";
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
//边框颜色
//cellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index;
//cellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index;
//背景图形，我没有用到过。感觉很丑
//cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
//cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
cellStyle.FillForegroundColor = HSSFColor.WHITE.index;
// cellStyle.FillPattern = FillPatternType.NO_FILL;
cellStyle.FillBackgroundColor = HSSFColor.BLUE.index;
//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
//自动换行
cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
cellStyle.Indention = 0;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case XlsStyle.Title:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
if (titleFont != null)
{
font.FontName = titleFont.Name;
if (titleFont.Bold)
{
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
}
font.FontHeightInPoints = (short)titleFont.SizeInPoints;
font.IsItalic = titleFont.Italic;
font.IsStrikeout = titleFont.Strikeout;
font.Underline = (byte)(titleFont.Underline ? 1 : 0);
}
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.NONE;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.NONE;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.NONE;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.NONE;
cellStyle.SetFont(font);
break;
case XlsStyle.Header:
// cellStyle.FillPattern = FillPatternType.LEAST_DOTS;
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
if (headFont != null)
{
font.FontName = headFont.Name;
if (headFont.Bold)
{
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
}
font.FontHeightInPoints = (short)headFont.SizeInPoints;
font.IsItalic = headFont.Italic;
font.IsStrikeout = headFont.Strikeout;
font.Underline = (byte)(headFont.Underline ? 1 : 0);
}
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM;
cellStyle.SetFont(font);
break;
case XlsStyle.Bottom:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
if (bottomFont != null)
{
font.FontName = bottomFont.Name;
if (bottomFont.Bold)
{
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
}
font.FontHeightInPoints = (short)bottomFont.SizeInPoints;
font.IsItalic = bottomFont.Italic;
font.IsStrikeout = bottomFont.Strikeout;
font.Underline = (byte)(bottomFont.Underline ? 1 : 0);
}
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.NONE;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.NONE;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.NONE;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.NONE;
cellStyle.SetFont(font);
break;
case XlsStyle.Serial:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
cellStyle.SetFont(font);
break;
case XlsStyle.Time:
IDataFormat dataStyle = wb.CreateDataFormat();
cellStyle.DataFormat = dataStyle.GetFormat("yyyy-mm-dd");
cellStyle.SetFont(font);
break;
case XlsStyle.Number:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.GENERAL;
cellStyle.SetFont(font);
break;
case XlsStyle.Money:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.GENERAL;
cellStyle.SetFont(font);
break;
case XlsStyle.Url:
linkAddresFont.Underline = 1;
cellStyle.SetFont(linkAddresFont);
break;
case XlsStyle.Percent:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.GENERAL;
cellStyle.SetFont(font);
break;
case XlsStyle.Chupper:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case XlsStyle.Tnumber:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case XlsStyle.Default:
cellStyle.SetFont(font);
break;
}
return(cellStyle);
}
/// <summary> 创建workbook样式 </summary>
/// <param name="wb"></param>
/// <param name="str"></param>
/// <returns></returns>
public static ICellStyle GetCellStyle(IWorkbook wb, XlsStyle str)
{
var cellStyle = wb.CreateCellStyle();
//边框
cellStyle.BorderBottom =
cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = BorderStyle.Thin;
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.Black.Index;
cellStyle.TopBorderColor = HSSFColor.Black.Index;
//背景图形，我没有用到过。感觉很丑
cellStyle.FillForegroundColor = HSSFColor.White.Index;
cellStyle.FillBackgroundColor = HSSFColor.Blue.Index;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//自动换行
//cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
cellStyle.Indention = 0;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case XlsStyle.Header:
// cellStyle.FillPattern = FillPatternType.LEAST_DOTS;
cellStyle.FillForegroundColor = 41;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.Alignment = HorizontalAlignment.Center;
var heaerFont = wb.CreateFont();
heaerFont.FontHeightInPoints = 12;
heaerFont.Boldweight = 600;
heaerFont.FontName = "微软雅黑";
cellStyle.SetFont(heaerFont);
break;
case XlsStyle.DateTime:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
cellStyle.SetFont(SetCellNormalFont(wb));
break;
case XlsStyle.Number:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.SetFont(SetCellNormalFont(wb));
break;
case XlsStyle.Money:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.SetFont(SetCellNormalFont(wb));
break;
case XlsStyle.Url:
var fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.Black.Index;
fontcolorblue.IsItalic = true; //下划线
fontcolorblue.FontName = "微软雅黑";
fontcolorblue.Underline = FontUnderlineType.Single;
cellStyle.SetFont(fontcolorblue);
break;
case XlsStyle.Percent:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(SetCellNormalFont(wb));
break;
//case XlsStyle.中文大写:
// IDataFormat format1 = wb.CreateDataFormat();
// cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
// cellStyle.SetFont(font);
// break;
//case XlsStyle.科学计数法:
// cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
// cellStyle.SetFont(font);
// break;
case XlsStyle.Default:
cellStyle.SetFont(SetCellNormalFont(wb));
break;
}
return(cellStyle);
}