/// <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>
/// 按一年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 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="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="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="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>
/// 计算两个时间内工作日数
/// </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>
/// 获取区域单元格 引用地址
/// </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>
/// 按一年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, ")");
}
}
/// 根据给定底数返回数字的对数
/// <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, ")");
}
/// <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="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="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, ")");
}