private void SetHeadStyles(string headName, MyColor color, StyleXls style)
{
if (!headStyles.ContainsKey(headName + color.ToString()))
{
IFont font;
ICellStyle cellstyle = ExportCoreHelper.Getcellstyle(this.WorkBook, style);
if (style == StyleXls.url)
{
font = ExportCoreHelper.GetFont(this.WorkBook, 2);
}
else if (style == StyleXls.头)
{
font = ExportCoreHelper.GetFont(this.WorkBook, 0);
}
else
{
font = ExportCoreHelper.GetFont(this.WorkBook, 1);
}
font.Color = ExportCoreHelper.GetColor(color);
cellstyle.SetFont(font);
headStyles.Add(headName + color.ToString(), 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.超链接:
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);
}
public static ICellStyle Getcellstyle(IWorkbook wb, StyleXls str)
{
ICellStyle style = wb.CreateCellStyle();
//设置单元格上下左右边框线
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BottomBorderColor = 12;
style.TopBorderColor = 12;
//文字水平和垂直对齐方式
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
//是否换行 (不换行)
style.WrapText = false;
style.Indention = 0;
switch (str)
{
case StyleXls.默认:
return(style);
case StyleXls.url:
style.SetFont(GetFont(wb, 2));
return(style);
case StyleXls.时间:
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
style.SetFont(GetFont(wb, 1));
return(style);
case StyleXls.数字:
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
style.SetFont(GetFont(wb, 1));
return(style);
case StyleXls.钱:
style.DataFormat = wb.CreateDataFormat().GetFormat("￥#,##0");
style.SetFont(GetFont(wb, 1));
return(style);
case StyleXls.百分比:
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
style.SetFont(GetFont(wb, 1));
return(style);
case StyleXls.中文大写:
style.DataFormat = wb.CreateDataFormat().GetFormat("[DbNum2][$-804]0");
style.SetFont(GetFont(wb, 1));
return(style);
case StyleXls.科学计数法:
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
style.SetFont(GetFont(wb, 1));
return(style);
case StyleXls.头:
style.FillBackgroundColor = 0x17;
style.FillForegroundColor = 0x17;
style.FillPattern = FillPattern.AltBars;
style.SetFont(GetFont(wb, 0));
return(style);
}
return(style);
}
public void SetCellValue(string headName, Guid value, MyColor color = MyColor.Black, StyleXls style = StyleXls.默认)
{
string str = value.ToString();
this.SetCellValue(headName, str, color, style);
}
public void SetCellValue(string headName, decimal value, MyColor color = MyColor.Black, StyleXls style = StyleXls.数字)
{
double num = (double)value;
this.SetCellValue(headName, num, color, style);
}
public void SetCellValue(string headName, bool value, MyColor color = MyColor.Black, StyleXls style = StyleXls.默认)
{
this.validate(headName);
this.SetHeadStyles(headName, color, style);
ICell cell = this.row.CreateCell(this.defineLocation[headName]);
cell.SetCellValue(value);
cell.CellStyle = headStyles[headName + color.ToString()];
}