예제 #1
0
        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;
                }
            }
        }
예제 #2
0
        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;
                }
            }
        }