Add subtotals to data in a spreadsheet

Question

I'm doing some server side excel output using the OpenXML Sdk v2 ctp. Has anyone here had any experience using this - mpre specifically:

Is there a way to add subtotals to data in a spreadsheet using the SDK? or do i need to manually iterate over the data adding the OutlineLevel property?

rows.Add(new Row(cells.ToArray()) { 
    RowIndex = (UInt32Value)Convert.ToUInt32(idx), 
    Spans = new ListValue<StringValue>() { InnerText = "1:2" }, 
    OutlineLevel = (ByteValue)groupLevelCount 
});

this would be a real PITA if I have to do it manually as there I need to do multi level grouping and sorting

score 3 · Answer 1 · answered May 27 '09 at 14:10

3

Have you tried to use the document DocumentReflector included in the SDK? DocumentReflector tool can load an OpenXML document and reverse engineer the code for generating that document with the SDK. In other words, it generates C# code from an xlsx file.

The DocumentReflector tool is located in the Tools folder under the Open XML Format SDK 2.0 installation folder.

Maybe it can help to fix your issue.

answered May 27 '09 at 14:10

Martin

39,309
62
192
278

It does to an extent - but I'm translating from an IList to excel so - where in the reflector tool it uses a procedural method to add rows to the worksheet - I need to iterate through the collection and was hoping there was some fucntion I could apply to the worksheet after the data was added to create the subtotals for me. The only way I can see to do this right now is to iterate over the data once it's added, do the grouping and sumarising manuall - which is an utter PITA :s – wayne-o May 27 '09 at 14:27

score 1 · Answer 2 · answered Sep 09 '11 at 15:16

1

The short answer is no. The OpenXML SDK gives you full access to the XLSX file. You can add, remove, and/or change elements in the workbook. But that's all it is - low level access to the elements. It does not have any kind of processing functionality.

answered Sep 09 '11 at 15:16

David Thielen

28,723
34
119
193

score 0 · Answer 3 · edited May 23 '17 at 12:20

0

If you have a macro or formula covering the cell-range in the workbook, you can set it to execute when the spreadsheet is opened:

See OpenXmlSdk: make Excel Recalculate formula.

spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;

edited May 23 '17 at 12:20

Community

1
1

answered Dec 08 '11 at 14:06

Michael Paulukonis

9,020
5
48
68

Add subtotals to data in a spreadsheet

3 Answers3