static ICellStyle Getcellstyle(IWorkbook wb, stylexls str)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 10;
font12.FontName = "微软雅黑";
IFont font = wb.CreateFont();
font.FontName = "微软雅黑";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OLIVE_GREEN.BLUE.index;
fontcolorblue.IsItalic = true;//下划线
fontcolorblue.FontName = "微软雅黑";
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.HAIR;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.HAIR;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.DOTTED;
//边框颜色
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 stylexls.header:
// cellStyle.FillPattern = FillPatternType.LEAST_DOTS;
cellStyle.SetFont(font12);
break;
case stylexls.time:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
cellStyle.SetFont(font);
break;
case stylexls.number:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.SetFont(font);
break;
case stylexls.money:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.SetFont(font);
break;
case stylexls.url:
fontcolorblue.Underline = 1;
cellStyle.SetFont(fontcolorblue);
break;
case stylexls.percent:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case stylexls.CHupper:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case stylexls.Tnumber:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case stylexls.defaults:
cellStyle.SetFont(font);
break;
}
return(cellStyle);
}
public static ICellStyle Getcellstyle(IWorkbook wb, stylexls str)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font10 = wb.CreateFont();
font10.FontHeightInPoints = 10;
font10.FontName = "宋体";
font10.Boldweight = 700;
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 10;
font12.FontName = "宋体";
font12.Boldweight = 700;
IFont fontw = wb.CreateFont();
fontw.FontName = "宋体";
fontw.Boldweight = 700;
IFont font14 = wb.CreateFont();
font14.FontHeightInPoints = 14;
font14.FontName = "宋体";
font14.Boldweight = 700;
IFont font = wb.CreateFont();
font.FontHeightInPoints = 10;
font.FontName = "宋体";
IFont fontHT = wb.CreateFont();
fontHT.FontHeightInPoints = 10;
fontHT.FontName = "宋体";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OliveGreen.Blue.Index;
fontcolorblue.IsItalic = true;//下划线
fontcolorblue.FontName = "宋体";
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.TopBorderColor = HSSFColor.OliveGreen.Black.Index;
//背景图形，我没有用到过。感觉很丑
//cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
//cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
// cellStyle.FillPattern = FillPatternType.NO_FILL;
//cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index2;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//自动换行
cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
cellStyle.Indention = 0;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case stylexls.投缴头:
cellStyle.FillForegroundColor = HSSFColor.PaleBlue.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFont(font12);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.月报头:
cellStyle.FillForegroundColor = HSSFColor.Rose.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFont(font10);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.月报底:
cellStyle.FillForegroundColor = HSSFColor.Rose.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFont(font10);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
break;
case stylexls.月报内容:
cellStyle.FillForegroundColor = HSSFColor.White.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFont(font10);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
break;
case stylexls.补缴头:
cellStyle.FillForegroundColor = HSSFColor.Rose.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.SetFont(font12);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.时间:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
cellStyle.SetFont(font);
break;
case stylexls.数字:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.SetFont(font);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.钱:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.SetFont(font);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.url:
fontcolorblue.Underline = FontUnderlineType.Single;
cellStyle.SetFont(fontcolorblue);
break;
case stylexls.百分比:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case stylexls.中文大写:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case stylexls.科学计数法:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case stylexls.默认:
cellStyle.SetFont(font);
break;
case stylexls.表头:
cellStyle.SetFont(font14);
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.加粗:
cellStyle.SetFont(fontw);
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
break;
case stylexls.迟到早退标题:
cellStyle.SetFont(fontHT);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.迟到早退内容:
cellStyle.SetFont(font);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
break;
case stylexls.不含底线:
cellStyle.SetFont(font);
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.None;
break;
case stylexls.迟到早退标题加左线:
cellStyle.SetFont(fontHT);
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case stylexls.迟到早退标题加左线左对齐:
cellStyle.SetFont(fontHT);
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
break;
}
return cellStyle;
}
static ICellStyle Getcellstyle(IWorkbook wb, stylexls str = stylexls.默认)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 10;
font12.FontName = "微软雅黑";
IFont font = wb.CreateFont();
font.FontName = "微软雅黑";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OliveGreen.Black.Index;
fontcolorblue.IsItalic = true;//下划线
fontcolorblue.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.OliveGreen.Black.Index;
cellStyle.TopBorderColor = HSSFColor.OliveGreen.Black.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.Maroon.Index;
//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//自动换行
cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
cellStyle.Indention = 0;
return(cellStyle);
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case stylexls.头:
// cellStyle.FillPattern = FillPatternType.LEAST_DOTS;
cellStyle.SetFont(font12);
break;
case stylexls.时间:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
cellStyle.SetFont(font);
break;
case stylexls.数字:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.SetFont(font);
break;
case stylexls.钱:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.SetFont(font);
break;
case stylexls.url:
fontcolorblue.Underline = FontUnderlineType.Single;
cellStyle.SetFont(fontcolorblue);
break;
case stylexls.百分比:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case stylexls.中文大写:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case stylexls.科学计数法:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case stylexls.默认:
cellStyle.SetFont(font);
break;
}
return(cellStyle);
}
private static ICellStyle Getcellstyle(IWorkbook wb, stylexls str)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 14;
font12.FontName = "宋体";
IFont font = wb.CreateFont();
font.FontName = "宋体";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OLIVE_GREEN.BLUE.index;
fontcolorblue.IsItalic = true; //下划线
fontcolorblue.FontName = "宋体";
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.HAIR;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.HAIR;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.DOTTED;
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index;
cellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index;
//背景图形，我没有用到过。感觉很丑
// cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.GREEN.index;
//cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
// cellStyle.FillForegroundColor = HSSFColor.WHITE.index;
// cellStyle.FillPattern = FillPatternType.NO_FILL;
cellStyle.FillBackgroundColor = HSSFColor.MAROON.index;
//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
//自动换行
// cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
cellStyle.Indention = 0;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case stylexls.头:
// cellStyle.FillPattern = FillPatternType.LEAST_DOTS;
cellStyle.SetFont(font12);
break;
case stylexls.时间:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
cellStyle.SetFont(font);
break;
case stylexls.数字:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.SetFont(font);
break;
case stylexls.钱:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.SetFont(font);
break;
case stylexls.url:
fontcolorblue.Underline = 1;
cellStyle.SetFont(fontcolorblue);
break;
case stylexls.百分比:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case stylexls.中文大写:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case stylexls.科学计数法:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case stylexls.默认:
cellStyle.SetFont(font);
break;
}
return cellStyle;
}
static ICellStyle Getcellstyle(IWorkbook wb, stylexls str, string smoney)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
//IFont font12 = wb.CreateFont();
//font12.FontHeightInPoints = 10;
//font12.FontName = "微软雅黑";
//IFont font = wb.CreateFont();
//font.FontName = "微软雅黑";
//font.Underline = 1;下划线
//IFont fontcolorblue = wb.CreateFont();
//fontcolorblue.Color = HSSFColor.OLIVE_GREEN.BLUE.index;
//fontcolorblue.IsItalic = true;//下划线
//fontcolorblue.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 stylexls.货币:
IDataFormat format = wb.CreateDataFormat();
switch (smoney)
{
case "￥":
cellStyle.DataFormat = format.GetFormat("￥#,##0.00;￥-#,##0.00");
break;
case "$":
cellStyle.DataFormat = format.GetFormat("$#,##0.00;-$#,##0.00");
break;
case "€":
cellStyle.DataFormat = format.GetFormat("[$€-C07] #,##0.00;-[$€-C07] #,##0.00");
break;
}
//cellStyle.SetFont(font);
break;
}
return(cellStyle);
}
public static ICellStyle Getcellstyle(IWorkbook wb, stylexls str, NPOI.SS.Util.CellRangeAddress region, HSSFSheet sh)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 12;
font12.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font12.FontName = "微软雅黑";
IFont font = wb.CreateFont();
font.FontName = "微软雅黑";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OliveGreen.Black.Index;
fontcolorblue.IsItalic = true;//下划线
fontcolorblue.FontName = "微软雅黑";
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //BorderLeft
if (region != null)
{
for (int i = region.FirstRow; i <= region.LastRow; i++)
{
IRow row = HSSFCellUtil.GetRow(i, sh);
for (int j = region.FirstColumn; j <= region.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = cellStyle;
}
}
}
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.TopBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.LeftBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.RightBorderColor = HSSFColor.OliveGreen.Black.Index;
//背景图形，我没有用到过。感觉很丑
//cellStyle.FillBackgroundColor = HSSFColor.OliveGreen.Black.Index;
//cellStyle.FillForegroundColor = HSSFColor.OliveGreen.Black.Index;
//cellStyle.FillPattern = FillPatternType.NO_FILL;
//cellStyle.FillForegroundColor = HSSFColor.White.Index;
//cellStyle.FillBackgroundColor = HSSFColor.Black.Index;
//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//自动换行
cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
//cellStyle.Indention = 0;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case stylexls.头:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette palette = ((HSSFWorkbook)wb).GetCustomPalette();
//HSSFColor newColor = palette.AddColor((byte)153, (byte)204, (byte)255);
palette.SetColorAtIndex((short)10, (byte)227, (byte)232, (byte)227);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
cellStyle.SetFont(font12);
break;
case stylexls.时间:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy-mm-dd");
cellStyle.SetFont(font);
break;
case stylexls.数字:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.SetFont(font);
break;
case stylexls.钱:
//IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("¥#,##0");
cellStyle.SetFont(font);
break;
case stylexls.url:
fontcolorblue.Underline = FontUnderlineType.None;
cellStyle.SetFont(fontcolorblue);
break;
case stylexls.百分比:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case stylexls.中文大写:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case stylexls.科学计数法:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case stylexls.默认:
cellStyle.SetFont(font);
break;
case stylexls.居中:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.SetFont(font);
break;
case stylexls.商品导入头:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette pale = ((HSSFWorkbook)wb).GetCustomPalette();
pale.SetColorAtIndex((short)30, (byte)11, (byte)87, (byte)235);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
cellStyle.SetFont(font12);
break;
case stylexls.导入说明:
HSSFPalette pale1 = ((HSSFWorkbook)wb).GetCustomPalette();
pale1.SetColorAtIndex((short)61, (byte)227, (byte)232, (byte)227);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightCornflowerBlue.Index;
IFont font2 = wb.CreateFont();
font2.FontName = "微软雅黑";
font2.FontHeightInPoints = 9;
cellStyle.SetFont(font2);
break;
case stylexls.导入红字:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette palebgss = ((HSSFWorkbook)wb).GetCustomPalette();
palebgss.SetColorAtIndex((short)62, (byte)255, (byte)0, (byte)0);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
IFont fontRed = wb.CreateFont();
fontRed.FontName = "微软雅黑";
fontRed.Color = HSSFColor.OliveGreen.Red.Index;
cellStyle.SetFont(fontRed);
break;
case stylexls.导入错误提示:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette paleError = ((HSSFWorkbook)wb).GetCustomPalette();
paleError.SetColorAtIndex((short)10, (byte)255, (byte)0, (byte)0);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
cellStyle.SetFont(font);
break;
case stylexls.导入背景色:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette palebgs = ((HSSFWorkbook)wb).GetCustomPalette();
palebgs.SetColorAtIndex((short)62, (byte)255, (byte)0, (byte)0);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
cellStyle.SetFont(font);
break;
}
return(cellStyle);
}
/// <summary>
/// 创建多样的格式 add by xuj
/// </summary>
public ICellStyle Getcellstyle(IWorkbook wb, stylexls str)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 10;
font12.FontName = "微软雅黑";
IFont font = wb.CreateFont();
font.FontName = "微软雅黑";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OLIVE_GREEN.BLUE.index;
fontcolorblue.IsItalic = true;//下划线
fontcolorblue.FontName = "微软雅黑";
IFont fontcoloryellow = wb.CreateFont();
fontcoloryellow.Color = HSSFColor.YELLOW.index;
fontcoloryellow.FontName = "微软雅黑";
//边框
//cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
//cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.HAIR;
//cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.HAIR;
//cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.DOTTED;
//边框颜色
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;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
cellStyle.FillBackgroundColor = 13;
cellStyle.BorderBottom = CellBorderType.DOTTED;
cellStyle.BorderLeft = CellBorderType.DOTTED;
cellStyle.BorderRight = CellBorderType.DOTTED;
cellStyle.BorderTop = CellBorderType.DOTTED;
switch (str)
{
case stylexls.头:
// cellStyle.FillPattern = FillPatternType.LEAST_DOTS;
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
cellStyle.SetFont(font12);
break;
case stylexls.时间:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
cellStyle.SetFont(font);
break;
case stylexls.数字:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.SetFont(font);
break;
case stylexls.钱:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.SetFont(font);
break;
case stylexls.url:
fontcolorblue.Underline = 1;
cellStyle.SetFont(fontcolorblue);
break;
case stylexls.百分比:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case stylexls.中文大写:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case stylexls.科学计数法:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case stylexls.黄色标题:
//fontcoloryellow.Underline = 1;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index2;
cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND; //这句话一定要加，否则无法取到背景色
//cellStyle.BorderBottom = CellBorderType.DOTTED;
//cellStyle.BorderLeft = CellBorderType.DOTTED;
//cellStyle.BorderRight = CellBorderType.DOTTED;
//cellStyle.BorderTop = CellBorderType.DOTTED;
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
cellStyle.SetFont(font);
break;
case stylexls.默认:
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;;
cellStyle.SetFont(font);
break;
}
return(cellStyle);
}
/// <summary>
/// 定义单元格常用到样式
/// 创建者：戚鹏
/// 创建日期：2013.5.20
/// </summary>
/// <param name="wb"></param>
/// <param name="str"></param>
/// <returns></returns>
private static ICellStyle GetCellStyle(IWorkbook wb, stylexls str)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 12;
font12.FontName = "Arial";
font12.Boldweight = 600;
IFont font = wb.CreateFont();
font.FontName = "微软雅黑";
font.Color = HSSFColor.OliveGreen.Red.Index;
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OliveGreen.Blue.Index;
fontcolorblue.IsItalic = true;//下划线
fontcolorblue.FontName = "微软雅黑";
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Dotted;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Dotted;
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.TopBorderColor = HSSFColor.OliveGreen.Black.Index;
//背景图形
//cellStyle.FillBackgroundColor = HSSFColor.OliveGreen.BLUE.index;
//cellStyle.FillForegroundColor = HSSFColor.OliveGreen.BLUE.index;
cellStyle.FillForegroundColor = HSSFColor.White.Index;
// cellStyle.FillPattern = FillPatternType.NO_FILL;
//cellStyle.FillBackgroundColor = HSSFColor.OliveGreen.BRIGHT_GREEN.index;
//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.FillBackgroundColor = HSSFColor.OliveGreen.Blue.Index;
//自动换行
cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
cellStyle.Indention = 0;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case stylexls.Title:
cellStyle.SetFont(font12);
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.White.Index;
cellStyle.FillPattern = FillPattern.FineDots;
cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.LightCornflowerBlue.Index;
cellStyle.IsLocked = false;
break;
case stylexls.DateTime:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
cellStyle.SetFont(font);
break;
case stylexls.Integer:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
cellStyle.SetFont(font);
break;
case stylexls.Currency:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.SetFont(font);
break;
case stylexls.url:
fontcolorblue.Underline = FontUnderlineType.Single;
cellStyle.SetFont(fontcolorblue);
break;
case stylexls.Percent:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case stylexls.CEN:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case stylexls.Scientific:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case stylexls.Default:
cellStyle.SetFont(font);
break;
}
return(cellStyle);
}
