Group and outline Excel data in C#, VB.NET, Java, C++, PHP

Groups and outline levels

EasyXLS™ library allows you to group data on rows and columns, so that the user can show/hide data. The data is organized in levels. The outline levels are determined automatically, based on the group data range.

Source code sample
Data group settings
Formatting group data
Add data into sheets

Source code sample

The below example shows how to group data on the first sheet of an Excel file on two outline levels.

C++

// Create an instance of the class that exports Excel files
EasyXLS::IExcelDocumentPtr workbook;
hr = CoCreateInstance(__uuidof(EasyXLS::ExcelDocument),
		      NULL,
		      CLSCTX_ALL,
		      __uuidof(EasyXLS::IExcelDocument),
		      (void**) &workbook) ;

// Add data to Excel file
...

// Group data on a range of cells, level 1
EasyXLS::IExcelDataGroupPtr xlsFirstDataGroup ;
CoCreateInstance(__uuidof(EasyXLS::ExcelDataGroup), NULL, CLSCTX_ALL, 
                 __uuidof(EasyXLS::IExcelDataGroupPtr), (void**) &xlsFirstDataGroup);
xlsFirstDataGroup->setRange_2("A1:E26");
xlsFirstDataGroup->setGroupType(DATAGROUP_GROUP_BY_ROWS);
xlsFirstDataGroup->setCollapsed(false);

// Format range of cells for first data group
EasyXLS::IExcelAutoFormatPtr xlsAutoFormat;
CoCreateInstance(__uuidof(EasyXLS::ExcelAutoFormat), NULL, CLSCTX_ALL,
                 __uuidof(EasyXLS::IExcelAutoFormat), (void**) &xlsAutoFormat);
xlsAutoFormat->InitAs(AUTOFORMAT_EASYXLS1);
xlsFirstDataGroup->setAutoFormat(xlsAutoFormat);
xlsFirstTab->easy_addDataGroup(xlsFirstDataGroup);

// Group data on a range inside the first range, level 2
EasyXLS::IExcelDataGroupPtr xlsSecondDataGroup;
CoCreateInstance(__uuidof(EasyXLS::ExcelDataGroup), NULL, CLSCTX_ALL,
                 __uuidof(EasyXLS::IExcelDataGroupPtr), (void**) &xlsSecondDataGroup);
xlsSecondDataGroup->setRange_2("A2:E10");
xlsSecondDataGroup->setGroupType(DATAGROUP_GROUP_BY_ROWS);
xlsSecondDataGroup->setCollapsed(false);

// Format range of cells for second data group
EasyXLS::IExcelAutoFormatPtr xlsAutoFormat2;
CoCreateInstance(__uuidof(EasyXLS::ExcelAutoFormat), NULL, CLSCTX_ALL,
                 __uuidof(EasyXLS::IExcelAutoFormat), (void**) &xlsAutoFormat2);
xlsAutoFormat2->InitAs(AUTOFORMAT_EASYXLS2);
xlsSecondDataGroup->setAutoFormat(xlsAutoFormat2);
xlsFirstTab->easy_addDataGroup(xlsSecondDataGroup);

...

// Export Excel file
workbook->easy_WriteXLSXFile("C:\\Samples\\Excel grouping and outline levels.xlsx");

Click here to see Continuous Code Listing


C++.NET

// Create an instance of the class that exports Excel files, having two sheets
ExcelDocument ^workbook = gcnew ExcelDocument(2);

// Add data to Excel file
...

// Group data on a range of cells, level 1
ExcelDataGroup ^xlsFirstDataGroup = 
        gcnew ExcelDataGroup("A1:E26", DataGroup::GROUP_BY_ROWS, false);
xlsFirstDataGroup->setAutoFormat(gcnew ExcelAutoFormat(Styles::AUTOFORMAT_EASYXLS1));
xlsFirstTab->easy_addDataGroup(xlsFirstDataGroup);

// Group data on a range inside the first range, level 2
ExcelDataGroup ^xlsSecondDataGroup = 
        gcnew ExcelDataGroup("A2:E10", DataGroup::GROUP_BY_ROWS, false);
xlsSecondDataGroup->setAutoFormat(gcnew ExcelAutoFormat(Styles::AUTOFORMAT_EASYXLS2)); 
xlsFirstTab->easy_addDataGroup(xlsSecondDataGroup);

...

// Export Excel file
workbook->easy_WriteXLSXFile("C:\\Samples\\Excel grouping and outline levels.xlsx");

Click here to see Continuous Code Listing

.NET:

// Create an instance of the class that exports Excel files
$workbook = new COM("EasyXLS.ExcelDocument");

// Add data to Excel file
...

// Group data on a range of cells, level 1
$xlsFirstDataGroup = new COM("EasyXLS.ExcelDataGroup");
$xlsFirstDataGroup->setRange_2 ("A1:E26");
$xlsFirstDataGroup->setGroupType ($DATAGROUP_GROUP_BY_ROWS);
$xlsFirstDataGroup->setCollapsed (False);

// Format range of cells for first data group
$xlsAutoFormat = new COM("EasyXLS.ExcelAutoFormat");
$xlsAutoFormat->InitAs($AUTOFORMAT_EASYXLS1);
$xlsFirstDataGroup->setAutoFormat($xlsAutoFormat);
$workbook->easy_getSheetAt(0)->easy_addDataGroup($xlsFirstDataGroup);

// Group data on a range inside the first range, level 2
$xlsSecondDataGroup = new COM("EasyXLS.ExcelDataGroup");
$xlsSecondDataGroup->setRange_2("A2:E10");
$xlsSecondDataGroup->setGroupType($DATAGROUP_GROUP_BY_ROWS);
$xlsSecondDataGroup->setCollapsed(False);

// Format range of cells for second data group
$xlsAutoFormat2 = new COM("EasyXLS.ExcelAutoFormat");
$xlsAutoFormat2->InitAs($AUTOFORMAT_EASYXLS2);
$xlsSecondDataGroup->setAutoFormat($xlsAutoFormat2);
$workbook->easy_getSheetAt(0)->easy_addDataGroup($xlsSecondDataGroup);

...

// Export Excel file
$workbook->easy_WriteXLSXFile("C:\Samples\Excel grouping and outline levels.xlsx");

Click here to see Continuous Code Listing


Java:

// Create an instance of the class that exports Excel files
$workbook = new java("EasyXLS.ExcelDocument");

// Add data to Excel file
...

// Group rows and format A1:E26 cell range
$xlsFirstDataGroup = new java("EasyXLS.ExcelDataGroup");
$xlsFirstDataGroup->setRange("A1:E26");
$xlsFirstDataGroup->setGroupType ($DATAGROUP_GROUP_BY_ROWS);
$xlsFirstDataGroup->setCollapsed (False);
$xlsAutoFormat = new java("EasyXLS.ExcelAutoFormat");
$xlsAutoFormat->InitAs($AUTOFORMAT_EASYXLS1);
$xlsFirstDataGroup->setAutoFormat($xlsAutoFormat);
$workbook->easy_getSheetAt(0)->easy_addDataGroup($xlsFirstDataGroup);

// Group rows and format A2:E10 cell range, outline level two, inside previous group
$xlsSecondDataGroup = new java("EasyXLS.ExcelDataGroup");
$xlsSecondDataGroup->setRange("A2:E10");
$xlsSecondDataGroup->setGroupType($DATAGROUP_GROUP_BY_ROWS);
$xlsSecondDataGroup->setCollapsed(False);
$xlsAutoFormat2 = new java("EasyXLS.ExcelAutoFormat");
$xlsAutoFormat2->InitAs($AUTOFORMAT_EASYXLS2);
$xlsSecondDataGroup->setAutoFormat($xlsAutoFormat2);
$workbook->easy_getSheetAt(0)->easy_addDataGroup($xlsSecondDataGroup);
	
// Export Excel file
$workbook->easy_WriteXLSXFile("C:\Samples\Excel grouping and outline levels.xlsx");

Click here to see Continuous Code Listing

The screen shot below represents an Excel report that includes a data group that has two outline levels of details for its data.

Data group settings

EasyXLS enables you to set the direction settings, to summary rows below detail using ExcelWorksheet.setDataGroupSummaryRowsBelow method and to summary columns to right of detail using ExcelWorksheet.setDataGroupSummaryColumnsRight method.

EasyXLS allows you to set the automatic styles for data using ExcelWorksheet.setDataGroupAutomaticStyles method.

Formatting group data

EasyXLS enables you format the data inside the group range of cells using automatic styles.

Add data into sheets

EasyXLS enables you to populate the sheets with data. The supported data types are strings, numbers, dates, booleans, errors and formulas.

Available for: Professional, Excel Writer, Excel Reader

EasyXLS Excel libraries:
.NET Excel Library
full .NET version to import, export or convert Excel files
COM+ Excel Library
full COM+ version to import, export or convert Excel files


Java Excel Library
full Java version to import, export or convert Excel files