/// <summary>
/// 单元格区域 设置样式：Excel同样样式
/// </summary>
/// <param name="cell"></param>
/// <param name="csType"></param>
public static void SetCellStyle(this ISheet sheet, int beginRow, int beginCol, int endRow, int endCol, ExcelCellStyleType type)
{
IWorkbook workBook = sheet.Workbook;
for (var rowIndex = beginRow; rowIndex <= endRow; rowIndex++)
{
IRow row = sheet.GetRow(rowIndex);
for (var colIndex = beginCol; colIndex <= endCol; colIndex++)
{
ICell cell = row.GetCell(colIndex);
cell.SetCellStyle(type);
}
}
}
/// <summary>
/// 设置样式：Excel同样样式
/// </summary>
/// <param name="cell"></param>
/// <param name="csType"></param>
public static void SetCellStyle(this ICell cell, ExcelCellStyleType csType)
{
if (cell == null)
{
return;
}
cell.DealParam();
cellStyle = workBook.CreateCellStyle();
IFont font = workBook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
short indexed = defaultColorIndexed;
switch (csType)
{
case ExcelCellStyleType.好:
font.Color = workBook.GetCustomColor("0,97,0");
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("198,239,206");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.差:
font.Color = workBook.GetCustomColor("156,0,6");
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("255,199,206");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.适中:
font.Color = workBook.GetCustomColor("156,101,0");
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("255,235,156");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.计算:
font.Color = workBook.GetCustomColor("250,125,0");
font.Boldweight = (short)FontBoldWeight.Bold;
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("242,242,242");
cell.CellStyle = cellStyle;
cell.SetBoderLine(ExcelBorderType.BorderAll);
break;
case ExcelCellStyleType.强调文字颜色1:
font.Color = workBook.GetCustomColor("255,255,255");
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("79,129,189");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色1_20:
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("220,230,241");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色1_40:
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("184,204,228");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色1_60:
font.Color = workBook.GetCustomColor("255,255,255");
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("149,179,215");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色2:
font.Color = workBook.GetCustomColor("255,255,255");
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("192,80,77");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色2_20:
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("242,220,219");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色2_40:
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("230,184,183");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色2_60:
font.Color = workBook.GetCustomColor("255,255,255");
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("218,150,148");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色3:
font.Color = workBook.GetCustomColor("255,255,255");
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("155,187,89");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色3_20:
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("235,241,222");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色3_40:
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("216,228,188");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色3_60:
font.Color = workBook.GetCustomColor("255,255,255");
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("196,215,155");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色4:
font.Color = workBook.GetCustomColor("255,255,255");
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("128,100,162");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色4_20:
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("228,223,236");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色4_40:
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("204,192,218");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色4_60:
font.Color = workBook.GetCustomColor("255,255,255");
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("177,160,199");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色5:
font.Color = workBook.GetCustomColor("255,255,255");
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("75,172,198");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色5_20:
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("218,238,243");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色5_40:
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("183,222,232");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色5_60:
font.Color = workBook.GetCustomColor("255,255,255");
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("146,205,220");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色6:
font.Color = workBook.GetCustomColor("255,255,255");
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("247,150,70");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色6_20:
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("253,233,217");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色6_40:
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("252,213,180");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.强调文字颜色6_60:
font.Color = workBook.GetCustomColor("255,255,255");
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("250,191,143");
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.标题:
font.Boldweight = (short)FontBoldWeight.Bold;
font.FontHeightInPoints = 18;
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.标题1:
font.Boldweight = (short)FontBoldWeight.Bold;
font.FontHeightInPoints = 15;
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
cell.SetBoderLine(ExcelBorderType.BorderBottomBold);
break;
case ExcelCellStyleType.标题2:
font.Boldweight = (short)FontBoldWeight.Bold;
font.FontHeightInPoints = 13;
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
cell.SetBoderLine(ExcelBorderType.BorderBottomBold);
break;
case ExcelCellStyleType.标题3:
font.Boldweight = (short)FontBoldWeight.Bold;
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
cell.SetBoderLine(ExcelBorderType.BorderBottomBold);
break;
case ExcelCellStyleType.标题4:
font.Boldweight = (short)FontBoldWeight.Bold;
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.汇总:
font.Boldweight = (short)FontBoldWeight.Bold;
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
cell.SetBoderLine(ExcelBorderType.BorderTopAndBottomDouble);
break;
case ExcelCellStyleType.检查单元格:
font.Boldweight = (short)FontBoldWeight.Bold;
font.Color = workBook.GetCustomColor("255,255,255");
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("165,165,165");
cell.CellStyle = cellStyle;
cell.SetBoderLine(ExcelBorderType.BorderAll);
break;
case ExcelCellStyleType.解释性文本:
font.Color = workBook.GetCustomColor("127,127,127");
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
cell.SetItalic();
break;
case ExcelCellStyleType.警告文本:
font.Color = workBook.GetCustomColor("255,0,0");
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
break;
case ExcelCellStyleType.链接单元格:
font.Color = workBook.GetCustomColor("250,125,0");
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
cell.SetBoderLine(ExcelBorderType.BorderBottomDouble);
break;
case ExcelCellStyleType.输出:
font.Boldweight = (short)FontBoldWeight.Bold;
font.Color = workBook.GetCustomColor("63,63,63");
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("242,242,242");
cell.CellStyle = cellStyle;
cell.SetBoderLine(ExcelBorderType.BorderAll);
break;
case ExcelCellStyleType.输入:
font.Boldweight = (short)FontBoldWeight.Bold;
font.Color = workBook.GetCustomColor("63,63,118");
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("255,204,153");
cell.CellStyle = cellStyle;
cell.SetBoderLine(ExcelBorderType.BorderAll);
break;
case ExcelCellStyleType.注释:
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = workBook.GetCustomColor("255,255,204");
cell.CellStyle = cellStyle;
cell.SetBoderLine(ExcelBorderType.BorderAll);
break;
}
}
