openxml sdk excel total sums

Question

i am having difficulties with open xml sdk: i am trying to generate excel file with several columns that have numbers and i want to have total sum at the end

i have tried to Generate Table Definition Part Content and inside define every column (id, name etC). If column has true for TotalColumn, it adds code (rough example)

var column = new TableColumn{
id = 1, 
name = "example", 
TotalsRowFunction = TotalsRowFunctionValues.Sum,
TotalsRowFormula = new TotalsRowFormula("=SUBTOTAL(109;[" + rowName + "])")
};

I can't get it to work, when i open excel it reports error, but it doesn't explicitly says what the problem is... I tried with microsoft validator but can't figure anything out...

I'd appreciate any help / example code since i can't google anything out

EDIT: i use this at the end:

workbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
workbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;

User11040 User11040 · Accepted Answer · 2018-02-08T00:16:39

Why not use a cell formula?

E.g.

cell.DataType = new EnumValue<CellValues>(CellValues.Number);
cell.CellFormula = new CellFormula(string.Format("=SUBTOTAL({0};[{1}])", "109", rowName));

//This will force a full recalculation of all the cells
workbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
workbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;

openxml sdk excel total sums

2 Answers