/// <summary>
/// 取余数
/// </summary>
/// <param name="cell"></param>
/// <param name="numRow"></param>
/// <param name="numCol"></param>
/// <param name="divRow"></param>
/// <param name="divCol"></param>
public static void Mod(this ICell cell, int numRow, int numCol, int divRow, int divCol)
{
string begin = string.Concat(ExcelExtend.ColumnIndexToName(numCol), (numRow + 1));
string end = string.Concat(ExcelExtend.ColumnIndexToName(divCol), (divRow + 1));
cell.CellFormula = string.Concat("MOD(", begin, ",", end, ")");
}
/// <summary>
/// 返回从开始时间 + N个工作日 - 节假日
/// <para>注意：得到的是一个日期序列数</para>
/// </summary>
/// <param name="cell"></param>
/// <param name="beginDateRow"></param>
/// <param name="beginDateCol"></param>
/// <param name="days"></param>
/// <param name="beginHRow"></param>
/// <param name="beginHCol"></param>
/// <param name="endHRow"></param>
/// <param name="enndHCol"></param>
public static void WorkDay(this ICell cell, DateTime dateText, int days, int beginHRow, int beginHCol, int endHRow, int enndHCol)
{
string bh = string.Concat(ExcelExtend.ColumnIndexToName(beginHCol), (beginHRow + 1));
string eh = string.Concat(ExcelExtend.ColumnIndexToName(enndHCol), (endHRow + 1));
cell.CellFormula = string.Concat("WORKDAY(\"", dateText, "\",", days, ",", bh, ":", eh, ")");
}
/// <summary>
/// 根据给定的X/Y轴坐标值，返回正切值
/// </summary>
/// <param name="cell"></param>
/// <param name="x_row"></param>
/// <param name="x_col"></param>
/// <param name="y_row"></param>
/// <param name="y_col"></param>
public static void Atan2(this ICell cell, int x_row, int x_col, int y_row, int y_col)
{
string x = string.Concat(ExcelExtend.ColumnIndexToName(x_col), (x_row + 1));
string y = string.Concat(ExcelExtend.ColumnIndexToName(y_col), (y_row + 1));
cell.CellFormula = string.Concat("ATAN2(", x, ",", y, ")");
}
/// <summary>
/// 设置单独单元格 引用地址
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowIndex">行位置：从0开始</param>
/// <param name="colIndex">列位置：从0开始</param>
public static string RefersToFormula(this ISheet sheet, int rowIndex, int colIndex)
{
string refer = string.Empty;
refer = string.Concat(sheet.SheetName, "!", "$", ExcelExtend.ColumnIndexToName(colIndex), "$", (rowIndex + 1));
return(refer);
}
/// <summary>
/// 计算两个时间内工作日数
/// </summary>
/// <param name="cell"></param>
/// <param name="beginRow"></param>
/// <param name="beginCol"></param>
/// <param name="endRow"></param>
/// <param name="endCol"></param>
public static void NetWorkDays(this ICell cell, int beginRow, int beginCol, int endRow, int endCol)
{
string begin = string.Concat(ExcelExtend.ColumnIndexToName(beginCol), (beginRow + 1));
string end = string.Concat(ExcelExtend.ColumnIndexToName(endCol), (endRow + 1));
cell.CellFormula = string.Concat("NETWORKDAYS(", begin, ",", end, ")");
}
/// <summary>
/// 按一年360天，计算两个（单元格）时间差时间
/// </summary>
/// <param name="cell"></param>
/// <param name="beginRow"></param>
/// <param name="beginCol"></param>
/// <param name="endRow"></param>
/// <param name="endCol"></param>
public static void Days360(this ICell cell, int beginRow, int beginCol, int endRow, int endCol)
{
string begin = string.Concat(ExcelExtend.ColumnIndexToName(beginCol), (beginRow + 1));
string end = string.Concat(ExcelExtend.ColumnIndexToName(endCol), (endRow + 1));
cell.CellFormula = string.Concat("DAYS360(", begin, ",", end);
}
/// <summary>
/// 标准差
/// </summary>
/// <param name="cell"></param>
/// <param name="beginRow"></param>
/// <param name="beginCol"></param>
/// <param name="endRow"></param>
/// <param name="endCol"></param>
public static void Stdev(this ICell cell, int beginRow, int beginCol, int endRow, int endCol)
{
string begin = string.Concat(ExcelExtend.ColumnIndexToName(beginCol), (beginRow + 1));
string end = string.Concat(ExcelExtend.ColumnIndexToName(endCol), (endRow + 1));
cell.CellFormula = string.Concat("STDEV(", begin, ":", end, ")");
}
/// <summary>
/// 平均值
/// </summary>
/// <param name="cell"></param>
/// <param name="beginRow"></param>
/// <param name="beginCol"></param>
/// <param name="endRow"></param>
/// <param name="endCol"></param>
public static void Average(this ICell cell, int beginRow, int beginCol, int endRow, int endCol)
{
string begin = string.Concat(ExcelExtend.ColumnIndexToName(beginCol), beginRow + 1);
string end = string.Concat(ExcelExtend.ColumnIndexToName(endCol), endRow + 1);
cell.CellFormula = string.Concat("COUNT(", begin, ":", end, ")");
}
/// <summary>
/// 获取区域单元格 引用地址
/// </summary>
/// <param name="sheet"></param>
/// <param name="beginRow">开始行：从0开始</param>
/// <param name="beginCol">开始列：从0开始</param>
/// <param name="endRow">结束行：从0开始</param>
/// <param name="endCol">结束列：从0开始</param>
/// <returns></returns>
public static string RefersToFormula(this ISheet sheet, int beginRow, int beginCol, int endRow, int endCol)
{
string refer = string.Empty;
refer = string.Concat(sheet.SheetName, "!", "$", ExcelExtend.ColumnIndexToName(beginCol), "$", (beginRow + 1)) + ":" +
string.Concat("$", ExcelExtend.ColumnIndexToName(endCol), "$", (endRow + 1));
return(refer);
}
/// <summary>
/// 返回一数组所代表的矩阵行列式的值
/// <para>数组：行数和列数必须相等</para>
/// </summary>
/// <param name="cell"></param>
/// <param name="beginRow"></param>
/// <param name="beginCol"></param>
/// <param name="endRow"></param>
/// <param name="endCol"></param>
public static void Moeterm(this ICell cell, int beginRow, int beginCol, int endRow, int endCol)
{
if (endRow - beginRow == endCol - beginCol)
{
string begin = string.Concat(ExcelExtend.ColumnIndexToName(beginCol), (beginRow + 1));
string end = string.Concat(ExcelExtend.ColumnIndexToName(endCol), (endRow + 1));
cell.CellFormula = string.Concat("MDETERM(", begin, ":", end, ")");
}
}
/// <summary>
/// 对区域进行处理，返回行位置和列位置
/// </summary>
/// <param name="postion"></param>
/// <param name="rowIndex"></param>
/// <param name="colIndex"></param>
private static void DealPostion(string postion, ref int rowIndex, ref int colIndex)
{
if (postion.Length < 2)
{
throw new Exception("invalid parameter");
}
colIndex = ExcelExtend.ColumnNameToIndex(postion.Substring(0, 1));
rowIndex = int.Parse(postion.Substring(1, 1)) - 1;
}
/// <summary>
/// 按一年360天，计算两个时间差时间
/// 一个是单元数据，一个是固定日期
/// </summary>
/// <param name="cell"></param>
/// <param name="dateText"></param>
/// <param name="rowIndex"></param>
/// <param name="colIndex"></param>
/// <param name="flag">true：dateText作为结束日期，否则作为开始日期</param>
public static void Days360(this ICell cell, DateTime dateText, int rowIndex, int colIndex, bool flag = true)
{
string begin = string.Concat(ExcelExtend.ColumnIndexToName(colIndex), (rowIndex + 1));
if (flag)
{
cell.CellFormula = string.Concat("DAYS360(", begin, ",\"", dateText, "\")");
}
else
{
cell.CellFormula = string.Concat("DAYS360(\"", dateText, "\",", begin, ")");
}
}
// 获取色值
public static short GetCustomColor(this IWorkbook workBook, ColorType colorType)
{
short indexed = defaultColorIndexed;
string rgb = ExcelExtend.GetColor(colorType).Item2;
if (workBook is HSSFWorkbook)
{
var tempWork = (HSSFWorkbook)workBook;
indexed = tempWork.GetCustomColor(rgb);
}
else if (workBook is XSSFWorkbook)
{
var tempWork = (XSSFWorkbook)workBook;
indexed = tempWork.GetCustomColor(rgb);
}
return(indexed);
}
/// <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>
/// 返回某数的乘幂
/// </summary>
/// <param name="cell"></param>
/// <param name="numRow"></param>
/// <param name="numCol"></param>
/// <param name="pow"></param>
public static void Power(this ICell cell, int numRow, int numCol, double pow)
{
string begin = string.Concat(ExcelExtend.ColumnIndexToName(numCol), (numRow + 1));
cell.CellFormula = string.Concat("POWER(", begin, ",", pow, ")");
}
/// <summary>
/// 设置颜色
/// </summary>
/// <param name="workBook"></param>
/// <param name="colorType"></param>
private static void SetCustomColor(this HSSFWorkbook workBook, ColorType colorType)
{
string rgb = ExcelExtend.GetColor(colorType).Item2;
workBook.SetCustomColor(rgb);
}
/// <summary>
/// 表示指定月数之前或之后的月份的最后一天
/// <para>注意：得到的结果是一个日期序列</para>
/// </summary>
/// <param name="cell"></param>
/// <param name="rowIndex"></param>
/// <param name="colIndex"></param>
/// <param name="months"></param>
public static void EoMonth(this ICell cell, int rowIndex, int colIndex, int months)
{
string begin = string.Concat(ExcelExtend.ColumnIndexToName(colIndex), (rowIndex + 1));
cell.CellFormula = string.Concat("EOMONTH(", begin, ",", months, ")");
}
/// <summary>
/// 返回从开始时间 + N个工作日
/// <para>注意：得到的是一个日期序列数</para>
/// </summary>
/// <param name="cell"></param>
/// <param name="beginDateRow"></param>
/// <param name="beginDateCol"></param>
/// <param name="days"></param>
public static void WorkDay(this ICell cell, int beginDateRow, int beginDateCol, int days)
{
string begin = string.Concat(ExcelExtend.ColumnIndexToName(beginDateCol), (beginDateRow + 1));
cell.CellFormula = string.Concat("WEEKDAY(", begin, ",", days, ")");
}
/// <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;
}
/// <summary>
/// 返回某数的双阶乘
/// </summary>
/// <param name="cell"></param>
/// <param name="rowIndex"></param>
/// <param name="colIndex"></param>
public static void FactDouble(this ICell cell, int rowIndex, int colIndex)
{
string begin = string.Concat(ExcelExtend.ColumnIndexToName(colIndex), (rowIndex + 1));
cell.CellFormula = string.Concat("FACTDOUBLE(", begin, ")");
}
/// <summary>
/// 返回e的n次方
/// <para>常数e = 2.71828182845904</para>
/// </summary>
/// <param name="cell"></param>
/// <param name="rowIndex"></param>
/// <param name="colIndex"></param>
public static void Exp(this ICell cell, int rowIndex, int colIndex)
{
string begin = string.Concat(ExcelExtend.ColumnIndexToName(colIndex), (rowIndex + 1));
cell.CellFormula = string.Concat("EXP(", begin, ")");
}
/// <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;
}
/// 根据给定底数返回数字的对数
/// <para>底数默认10</para>
public static void Log(this ICell cell, int rowIndex, int colIndex, int bs = 10)
{
string begin = string.Concat(ExcelExtend.ColumnIndexToName(colIndex), (rowIndex + 1));
cell.CellFormula = string.Concat("LOG(", begin, ",", bs, ")");
}