This module uses the DevExpress Spreadsheet API to group rows and columns in a predefined Excel document.
Specify group settings. Select whether to group rows, columns, or both. You can also include subtotals and make all groups initially collapsed when you open the document.
Use the Group Data and Save as ... dropdown button to select output format, process the document, and download the result.
Sample Document
OutlineGrouping_Template.xlsx
using DevExpress.Spreadsheet;
Stream GroupData(Stream inputStream, bool groupRows, bool groupColumns, bool showSubtotals, bool collapsed, DocumentFormat outputFormat) {
using Workbook workbook = new Workbook();
workbook.LoadDocument(inputStream);
// Obtain the first worksheet
Worksheet firstWorksheet = workbook.Worksheets.First();
// Remove existing subtotals before creating groups
if(!showSubtotals) {
firstWorksheet.RemoveSubtotal(firstWorksheet["B3:G35"]);
}
// Group rows and columns
if(groupRows) {
// Create Quarterly total groups
firstWorksheet.Rows.Group(3, 8, collapsed);
firstWorksheet.Rows.Group(showSubtotals ? 11 : 10, showSubtotals ? 16 : 15, collapsed);
firstWorksheet.Rows.Group(showSubtotals ? 19 : 17, showSubtotals ? 24 : 22, collapsed);
firstWorksheet.Rows.Group(showSubtotals ? 27 : 24, showSubtotals ? 32 : 29, collapsed);
// Create Grand Total group only if subtotals are shown
if(showSubtotals)
firstWorksheet.Rows.Group(2, 33, collapsed);
}
if(groupColumns) {
// Group columns
firstWorksheet.Columns.Group("C", "F", collapsed);
}
// Create output stream
var outputStream = new MemoryStream();
// Save document in target format
workbook.SaveDocument(outputStream, outputFormat);
outputStream.Seek(0, SeekOrigin.Begin);
return outputStream;
}