/// <summary>
/// 设置千分位
/// </summary>
/// <param name="cell"></param>
public static void SetThousandsSeparator(this ICell cell)
{
if (cell == null)
{
return;
}
cell.DealParam();
IDataFormat sdf = workBook.CreateDataFormat();
cellStyle.DataFormat = sdf.GetFormat("_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * ' - '??_ ;_ @_ ");
cell.CellStyle = cellStyle;
}
/// <summary>
/// 设置百分百：%
/// </summary>
/// <param name="workBook"></param>
/// <param name="rgbs"></param>
public static void SetPercent(this ICell cell)
{
if (cell == null)
{
return;
}
cell.DealParam();
IDataFormat sdf = workBook.CreateDataFormat();
cellStyle.DataFormat = sdf.GetFormat("0%");
cell.CellStyle = cellStyle;
}
/// <summary>
/// 设置下划线
/// </summary>
/// <param name="cell"></param>
/// <param name="dbLine"></param>
/// <param name="lineType"></param>
public static void SetUnderline(this ICell cell, bool dbLine = false, FontUnderlineType lineType = FontUnderlineType.Single)
{
if (cell == null)
{
return;
}
cell.DealParam();
IFont font = cellStyle.GetFont(workBook);
font.Underline = lineType;
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
}
/// <summary>
/// 设置字体颜色
/// </summary>
/// <param name="cell"></param>
/// <param name="colorType"></param>
public static void SetFontColor(this ICell cell, ColorType colorType)
{
if (cell == null)
{
return;
}
cell.DealParam();
IFont font = workBook.GetFontAt(cellStyle.FontIndex);
font.Color = workBook.GetCustomColor(colorType);
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
}
/// <summary>
/// 字体倾斜
/// </summary>
/// <param name="cell"></param>
public static void SetItalic(this ICell cell)
{
if (cell == null)
{
return;
}
cell.DealParam();
IFont font = cellStyle.GetFont(workBook);
font.IsItalic = true;
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
}
/// <summary>
/// 字体加粗
/// </summary>
/// <param name="cell"></param>
public static void SetBold(this ICell cell, FontBoldWeight bold = FontBoldWeight.Bold)
{
if (cell == null)
{
return;
}
cell.DealParam();
IFont font = cellStyle.GetFont(workBook);
font.Boldweight = (short)bold;
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
}
/// <summary>
/// 设置值：自定义 DataFormat
/// <para>先设置样式，再调用此赋值</para>
/// </summary>
/// <param name="cell"></param>
/// <param name="value"></param>
/// <param name="dataFormat"></param>
private static void SetCellValue(this ICell cell, object value, string dataFormat)
{
if (cell == null)
{
return;
}
cell.DealParam();
if (value == null)
{
cell.SetCellValue(string.Empty);
return;
}
IDataFormat sdf = workBook.CreateDataFormat();
cellStyle.DataFormat = sdf.GetFormat(dataFormat);
cell.SetCellValue(value.ToString());
cell.CellStyle = cellStyle;
}
/// <summary>
/// 设置值：科学计数法
/// <para>先设置样式，再调用此赋值</para>
/// </summary>
/// <param name="cell"></param>
/// <param name="workBook"></param>
/// <param name="value"></param>
/// <param name="countType"></param>
/// <param name="cellStyle"></param>
public static void SetCellValue(this ICell cell, object value, ExcelCountType countType, int dot = 2)
{
if (cell == null)
{
return;
}
cell.DealParam();
if (value == null)
{
cell.SetCellValue(string.Empty);
return;
}
string dt = ExcelExtend.GetDot(dot);
string strDataFormat = string.Empty;
switch (countType)
{
case ExcelCountType.科学计数1:
strDataFormat = "0{0}E+00";
if (dot == 0)
{
dt = ".";
}
strDataFormat = string.Format(strDataFormat, dt);
break;
case ExcelCountType.自定义1:
strDataFormat = "##0.0E+0";
break;
}
double dbValue = 0;
double.TryParse(value.ToString(), out dbValue);
cell.SetCellValue(dbValue);
IDataFormat sdf = workBook.CreateDataFormat();
cellStyle.DataFormat = sdf.GetFormat(strDataFormat);
cell.CellStyle = cellStyle;
}
/// <summary>
/// 设置值：特殊类型 数字转中文
/// <para>先设置样式，再调用此赋值</para>
/// </summary>
/// <param name="cell"></param>
/// <param name="workBook"></param>
/// <param name="value"></param>
/// <param name="specType"></param>
/// <param name="cellStyle"></param>
public static void SetCellValue(this ICell cell, object value, ExcelSpecialType specType)
{
if (cell == null)
{
return;
}
cell.DealParam();
if (value == null)
{
cell.SetCellValue(string.Empty);
return;
}
string strDataFormat = string.Empty;
switch (specType)
{
case ExcelSpecialType.特殊1:
strDataFormat = "000000";
break;
case ExcelSpecialType.特殊2:
strDataFormat = "[DBNum1][$-804]G/通用格式";
break;
case ExcelSpecialType.特殊3:
strDataFormat = "[DBNum2][$-804]G/通用格式";
break;
}
double dbValue = 0;
double.TryParse(value.ToString(), out dbValue);
cell.SetCellValue(dbValue);
IDataFormat sdf = workBook.CreateDataFormat();
cellStyle.DataFormat = sdf.GetFormat(strDataFormat);
cell.CellStyle = cellStyle;
}
/// <summary>
/// 设置值：数值类型（自定义、货币、会计专用）
/// <para>先设置样式，再调用此赋值</para>
/// </summary>
/// <param name="cell"></param>
/// <param name="workBook"></param>
/// <param name="value"></param>
/// <param name="cellStyle"></param>
/// <param name="dot"></param>
public static void SetCellValue(this ICell cell, object value, ExcelNumberType numberType, ExcelCurrencyType currencyType = ExcelCurrencyType.人民币, int dot = 2)
{
if (cell == null)
{
return;
}
cell.DealParam();
if (value == null)
{
cell.SetCellValue(string.Empty);
return;
}
string strDataFormat = string.Empty;
string dt = ExcelExtend.GetDot(dot);
string ct = string.Empty;
// 设置货币类型
switch (currencyType)
{
case ExcelCurrencyType.人民币:
ct = "￥";
break;
case ExcelCurrencyType.美元:
ct = "$";
break;
case ExcelCurrencyType.欧元:
ct = "€";
break;
case ExcelCurrencyType.英镑:
ct = "£";
break;
}
IDataFormat sdf = workBook.CreateDataFormat();
// 获取类型
switch (numberType)
{
case ExcelNumberType.会计专用1:
strDataFormat = " _ * #,##0_ ;_ * -#,##0_ ;_ * ' - '_ ;_ @_ ";
break;
case ExcelNumberType.会计专用2:
strDataFormat = "_ * #,##0{0}_ ;_ * -#,##0{0}_ ;_ * ' - '??_ ;_ @_ ";
strDataFormat = string.Format(strDataFormat, dt);
break;
case ExcelNumberType.会计专用3:
strDataFormat = "_ {0}* #,##0_ ;_ {0}* -#,##0_ ;_ {0}* ' - '_ ;_ @_ ";
strDataFormat = string.Format(strDataFormat, ct);
break;
case ExcelNumberType.会计专用4:
strDataFormat = "_ {0}* #,##0{1}_ ;_ {0}* -#,##0{1}_ ;_ {0}* ' - '??_ ;_ @_ ";
strDataFormat = string.Format(strDataFormat, ct, dt);
break;
case ExcelNumberType.自定义1:
strDataFormat = "0";
break;
case ExcelNumberType.自定义2:
strDataFormat = "0.00";
break;
case ExcelNumberType.自定义3:
strDataFormat = "#,##0;-#,##0";
break;
case ExcelNumberType.自定义4:
strDataFormat = "#,##0{0};-#,##0{0}";
strDataFormat = string.Format(strDataFormat, dt);
break;
case ExcelNumberType.货币1:
strDataFormat = "#,##0";
break;
case ExcelNumberType.货币2:
strDataFormat = "#,##0{0}";
strDataFormat = string.Format(strDataFormat, dt);
break;
case ExcelNumberType.货币3:
strDataFormat = "#,##0;[Red]-#,##0";
break;
case ExcelNumberType.货币4:
strDataFormat = "#,##0{0};[Red]-#,##0{0}";
strDataFormat = string.Format(strDataFormat, dt);
break;
case ExcelNumberType.货币5:
strDataFormat = "{0}#,##0;{0}-#,##0";
strDataFormat = string.Format(strDataFormat, ct);
break;
case ExcelNumberType.货币6:
strDataFormat = "{0}#,##0;[Red]{0}-#,##0";
strDataFormat = string.Format(strDataFormat, ct);
break;
case ExcelNumberType.货币7:
strDataFormat = "{0}#,##0{1};{0}-#,##0{1}";
strDataFormat = string.Format(strDataFormat, ct, dt);
break;
case ExcelNumberType.货币8:
strDataFormat = "{0}#,##0{1};[Red]{0}-#,##0{1}";
strDataFormat = string.Format(strDataFormat, ct, dt);
break;
case ExcelNumberType.货币9:
strDataFormat = "{0}#,##0_);({0}#,##0)";
strDataFormat = string.Format(strDataFormat, ct);
break;
case ExcelNumberType.货币10:
strDataFormat = "{0}#,##0_);[Red]({0}#,##0)";
strDataFormat = string.Format(strDataFormat, ct);
break;
case ExcelNumberType.货币11:
strDataFormat = "{0}#,##0{1}_);({0}#,##0{1})";
strDataFormat = string.Format(strDataFormat, ct, dt);
break;
case ExcelNumberType.货币12:
strDataFormat = "{0}#,##0{1}_);[Red]({0}#,##0{1})";
strDataFormat = string.Format(strDataFormat, ct, dt);
break;
default:
break;
}
double dbValue = 0;
double.TryParse(value.ToString(), out dbValue);
// 设置值
cell.SetCellValue(dbValue);
// 设置样式
cellStyle.DataFormat = sdf.GetFormat(strDataFormat);
cell.CellStyle = cellStyle;
}
/// <summary>
/// 设置值：分数
/// </summary>
/// <param name="cell"></param>
/// <param name="workBook"></param>
/// <param name="value"></param>
/// <param name="facType"></param>
/// <param name="cellStyle"></param>
public static void SetCellValue(this ICell cell, object value, ExcelFractionType facType)
{
if (cell == null)
{
return;
}
cell.DealParam();
if (value == null)
{
cell.SetCellValue(string.Empty);
return;
}
string strDataFormat = string.Empty;
switch (facType)
{
case ExcelFractionType.分数1:
strDataFormat = "# ?/?";
break;
case ExcelFractionType.分数2:
strDataFormat = "# ??/??";
break;
case ExcelFractionType.分数3:
strDataFormat = "# ???/???";
break;
case ExcelFractionType.分数4:
strDataFormat = "# ?/2";
break;
case ExcelFractionType.分数5:
strDataFormat = "# ?/4";
break;
case ExcelFractionType.分数6:
strDataFormat = "# ?/8";
break;
case ExcelFractionType.分数7:
strDataFormat = "# ?/16";
break;
case ExcelFractionType.分数8:
strDataFormat = "# ?/10";
break;
case ExcelFractionType.分数9:
strDataFormat = "# ??/100";
break;
}
DateTime dtDate = DateTime.Parse(value.ToString());
cell.SetCellValue(dtDate);
IDataFormat sdf = workBook.CreateDataFormat();
cellStyle.DataFormat = sdf.GetFormat(strDataFormat);
cell.CellStyle = cellStyle;
}
/// <summary>
/// 设置类型：日期、时间
/// <para>先设置样式，再调用此赋值</para>
/// </summary>
/// <param name="cell"></param>
/// <param name="workBook"></param>
/// <param name="value"></param>
/// <param name="dateType"></param>
/// <param name="cellStyle"></param>
public static void SetCellValue(this ICell cell, object value, ExcelDateType dateType)
{
if (cell == null)
{
return;
}
cell.DealParam();
if (value == null)
{
cell.SetCellValue(string.Empty);
return;
}
string strDataFormat = string.Empty;
// 获取类型
switch (dateType)
{
case ExcelDateType.日期1:
strDataFormat = "yyyy/m/d";
break;
case ExcelDateType.日期2:
strDataFormat = "[$-F800]dddd, mmmm dd, yyyy";
break;
case ExcelDateType.日期3:
strDataFormat = "[DBNum1][$-804]yyyy年m月d日;@";
break;
case ExcelDateType.日期4:
strDataFormat = "[DBNum1][$-804]yyyy年m月;@";
break;
case ExcelDateType.日期5:
strDataFormat = "[DBNum1][$-804]m月d日;@";
break;
case ExcelDateType.日期6:
strDataFormat = "[$-804]aaaa;@";
break;
case ExcelDateType.日期7:
strDataFormat = "[$-804]aaa;@";
break;
case ExcelDateType.时间1:
strDataFormat = "[DBNum1][$-804]h时mm分;@";
break;
case ExcelDateType.时间2:
strDataFormat = "[DBNum1][$-804]上午/下午h时mm分;@";
break;
case ExcelDateType.自定义1:
strDataFormat = "yyyy年m月";
break;
case ExcelDateType.自定义2:
strDataFormat = "m月d日";
break;
case ExcelDateType.自定义3:
strDataFormat = "yyyy年m月d日";
break;
case ExcelDateType.自定义4:
strDataFormat = "m/d/yy";
break;
case ExcelDateType.自定义5:
strDataFormat = "d-mmm-yy";
break;
case ExcelDateType.自定义6:
strDataFormat = "d-mmm";
break;
case ExcelDateType.自定义7:
strDataFormat = "mmm-yy";
break;
case ExcelDateType.自定义8:
strDataFormat = "h:mm AM/PM";
break;
case ExcelDateType.自定义9:
strDataFormat = "h:mm:ss AM/PM";
break;
case ExcelDateType.自定义10:
strDataFormat = "h:mm";
break;
case ExcelDateType.自定义11:
strDataFormat = "h:mm:ss";
break;
case ExcelDateType.自定义12:
strDataFormat = "h时mm分";
break;
case ExcelDateType.自定义13:
strDataFormat = "h时mm分ss秒";
break;
case ExcelDateType.自定义14:
strDataFormat = "上午/下午h时mm分";
break;
case ExcelDateType.自定义15:
strDataFormat = "上午/下午h时mm分ss秒";
break;
case ExcelDateType.自定义16:
strDataFormat = "yyyy/m/d h:mm";
break;
case ExcelDateType.自定义17:
strDataFormat = "mm:ss";
break;
case ExcelDateType.自定义18:
strDataFormat = "mm:ss.0";
break;
}
IDataFormat sdf = workBook.CreateDataFormat();
cellStyle.DataFormat = sdf.GetFormat(strDataFormat);
cell.CellStyle = cellStyle;
}
/// <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;
}
}
/// <summary>
/// 设置边框
/// </summary>
/// <param name="cell"></param>
/// <param name="boderType"></param>
/// <param name="lineColor"></param>
public static void SetBoderLine(this ICell cell, ExcelBorderType boderType = ExcelBorderType.BorderAll, ColorType lineColor = ColorType.black)
{
if (cell == null)
{
return;
}
cell.DealParam();
string rgb = ExcelExtend.GetColor(lineColor).Item2;
short indexed = workBook.GetCustomColor(rgb);
switch (boderType)
{
case ExcelBorderType.BorderAll:
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BottomBorderColor = indexed;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.TopBorderColor = indexed;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.LeftBorderColor = indexed;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.RightBorderColor = indexed;
break;
case ExcelBorderType.BorderAllBold:
cellStyle.BorderBottom = BorderStyle.Thick;
cellStyle.BottomBorderColor = indexed;
cellStyle.BorderTop = BorderStyle.Thick;
cellStyle.TopBorderColor = indexed;
cellStyle.BorderLeft = BorderStyle.Thick;
cellStyle.LeftBorderColor = indexed;
cellStyle.BorderRight = BorderStyle.Thick;
cellStyle.RightBorderColor = indexed;
break;
case ExcelBorderType.BorderBottomBold:
cellStyle.BorderBottom = BorderStyle.Thick;
cellStyle.BottomBorderColor = indexed;
break;
case ExcelBorderType.BorderBottom:
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BottomBorderColor = indexed;
break;
case ExcelBorderType.BorderBottomDouble:
cellStyle.BorderBottom = BorderStyle.Double;
cellStyle.BottomBorderColor = indexed;
break;
case ExcelBorderType.BorderLeft:
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.LeftBorderColor = indexed;
break;
case ExcelBorderType.BorderRight:
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.RightBorderColor = indexed;
break;
case ExcelBorderType.BorderTop:
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.TopBorderColor = indexed;
break;
case ExcelBorderType.BorderTopAndBotoomBold:
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.TopBorderColor = indexed;
cellStyle.BorderBottom = BorderStyle.Thick;
cellStyle.BottomBorderColor = indexed;
break;
case ExcelBorderType.BorderTopAndBottom:
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.TopBorderColor = indexed;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BottomBorderColor = indexed;
break;
case ExcelBorderType.BorderTopAndBottomDouble:
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.TopBorderColor = indexed;
cellStyle.BorderBottom = BorderStyle.Double;
cellStyle.BottomBorderColor = indexed;
break;
}
cell.CellStyle = cellStyle;
}
