internal void UpdateUnderlyingCellFormula()
{
if (TotalsRowFunction != XLTotalsRowFunction.None && TotalsRowFunction != XLTotalsRowFunction.Custom)
{
var cell = table.TotalsRow().Cell(Index + 1);
String formula = String.Empty;
switch (TotalsRowFunction)
{
case XLTotalsRowFunction.Sum: formula = "109"; break;
case XLTotalsRowFunction.Minimum: formula = "105"; break;
case XLTotalsRowFunction.Maximum: formula = "104"; break;
case XLTotalsRowFunction.Average: formula = "101"; break;
case XLTotalsRowFunction.Count: formula = "103"; break;
case XLTotalsRowFunction.CountNumbers: formula = "102"; break;
case XLTotalsRowFunction.StandardDeviation: formula = "107"; break;
case XLTotalsRowFunction.Variance: formula = "110"; break;
}
cell.FormulaA1 = "SUBTOTAL(" + formula + ",[" + Name + "])";
var lastCell = table.LastRow().Cell(Index + 1);
if (lastCell.DataType != XLCellValues.Text)
{
cell.DataType = lastCell.DataType;
cell.Style.NumberFormat = lastCell.Style.NumberFormat;
}
}
}
internal void UpdateTableFieldTotalsRowFormula()
{
if (TotalsRowFunction != XLTotalsRowFunction.None && TotalsRowFunction != XLTotalsRowFunction.Custom)
{
var cell = table.TotalsRow().Cell(Index + 1);
var formulaCode = String.Empty;
switch (TotalsRowFunction)
{
case XLTotalsRowFunction.Sum: formulaCode = "109"; break;
case XLTotalsRowFunction.Minimum: formulaCode = "105"; break;
case XLTotalsRowFunction.Maximum: formulaCode = "104"; break;
case XLTotalsRowFunction.Average: formulaCode = "101"; break;
case XLTotalsRowFunction.Count: formulaCode = "103"; break;
case XLTotalsRowFunction.CountNumbers: formulaCode = "102"; break;
case XLTotalsRowFunction.StandardDeviation: formulaCode = "107"; break;
case XLTotalsRowFunction.Variance: formulaCode = "110"; break;
}
var modifiedName = Name;
QuotedTableFieldCharacters.ForEach(c => modifiedName = modifiedName.Replace(c, "'" + c));
if (modifiedName.StartsWith(" ") || modifiedName.EndsWith(" "))
{
modifiedName = "[" + modifiedName + "]";
}
var prependTableName = modifiedName.Contains(" ");
cell.FormulaA1 = $"SUBTOTAL({formulaCode},{(prependTableName ? table.Name : string.Empty)}[{modifiedName}])";
var lastCell = table.LastRow().Cell(Index + 1);
if (lastCell.DataType != XLDataType.Text)
{
cell.DataType = lastCell.DataType;
cell.Style.NumberFormat = lastCell.Style.NumberFormat;
}
}
}