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