CODEWITHSUNDEEP
c#asp.netms-officeopenxml
wayne-o
wayne-o

Reputation: 91

Add subtotals to data in a spreadsheet

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

Upvotes: 2

Views: 2858

Answers (3)

Michael Paulukonis
Michael Paulukonis

Reputation: 9100

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;

Upvotes: 0

David Thielen
David Thielen

Reputation: 33046

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.

Upvotes: 1

Martin
Martin

Reputation: 40613

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.

Upvotes: 3

Related Questions