/// <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="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;
}
