/// <summary>
/// 设置单元格公式
/// </summary>
/// <param name="range"></param>
/// <param name="formula"></param>
public static void SetFormula(this ExcelReference range, string formula)
{
if (string.IsNullOrEmpty(formula))
{
//删除公式
range.ClearFormula();
return;
}
// Get the formula and convert to R1C1 mode
var isR1C1Mode = (bool)XlCall.Excel(XlCall.xlfGetWorkspace, 4);
string formulaR1C1 = formula;
if (!isR1C1Mode)
{
formulaR1C1 = (string)
XlCall.Excel(XlCall.xlfFormulaConvert, formula, true, false, ExcelMissing.Value, range);
}
XlCall.XlReturn retval = XlCall.TryExcel(XlCall.xlcFormula, out var ignoredResult, formulaR1C1, range);
if (retval != XlCall.XlReturn.XlReturnSuccess)
{
// TODO: Consider what to do now!?
// Might have failed due to array in the way.
range.SetValue("'" + formula);
}
}