C++: Group or Ungroup Rows and Columns in Excel

Grouping rows and columns in Excel is a valuable feature that can simplify working with large or complex datasets. This feature enables you to hide or show individual sections of data for better viewing and analysis. For instance, if you have a worksheet with data for various regions or departments, you can group the rows for each region or department together. Once grouped, you can collapse or expand each group as required, which makes it easier for you to focus on specific parts of the data while keeping the rest of the worksheet unaffected. In this article, you will learn how to group or ungroup rows and columns, along with how to expand or collapse groups in Excel in C++ using Spire.XLS for C++.

• Group Rows and Columns in Excel in C++
• Ungroup Rows and Columns in Excel in C++
• Expand or Collapse Groups in Excel in C++

Install Spire.XLS for C++

There are two ways to integrate Spire.XLS for C++ into your application. One way is to install it through NuGet, and the other way is to download the packageThere are two ways to integrate Spire.XLS for C++ into your application. One way is to install it through NuGet, and the other way is to download the package from our website and copy the libraries into your program. Installation via NuGet is simpler and more recommended. You can find more details by visiting the following link.from our website and copy the libraries into your program. Installation via NuGet is simpler and more recommended. You can find more details by visiting the following link.

Integrate Spire.XLS for C++ in a C++ Application

Group Rows and Columns in Excel in C++

The XlsWorksheet->GroupByRows(int firstRow, int lastRow, bool isCollapsed) and XlsWorksheet->GroupByColumns (int firstColumn, int lastColumn, bool isCollapsed) methods in Spire.XLS for C++ can be used to group specific rows and columns in an Excel worksheet. The detailed steps are as follows:

• Initialize an instance of the Workbook class.
• Load an Excel file using the Workbook->LoadFromFile(LPCWSTR_S fileName) method.
• Get a specific worksheet by its index using the Workbook->GetWorksheets()->Get(int index) method.
• Group specific rows in the worksheet using the XlsWorksheet->GroupByRows(int firstRow, int lastRow, bool isCollapsed) method.
• Group specific columns in the worksheet using the XlsWorksheet->GroupByColumns (int firstColumn, int lastColumn, bool isCollapsed) method.
• Save the result file using the Workbook->SaveToFile (LPCWSTR_S fileName, ExcelVersion version) method.

#include "Spire.Xls.o.h";

using namespace Spire::Xls;


int main()
{
	//Initialize an instance of the Workbook class
	Workbook* workbook = new Workbook();

	//Load an Excel file
	workbook->LoadFromFile(L"Sample.xlsx");

	//Get the first worksheet
	Worksheet* sheet = workbook->GetWorksheets()->Get(0);

	//Group the 2nd, 3rd, 4th, 5th and 6th rows
	sheet->GroupByRows(2, 6, false);
	//Group the 3rd and 4th columns
	sheet->GroupByColumns(3, 4, false);

	//Save the result file
	workbook->SaveToFile(L"GroupRowsAndColumns.xlsx", ExcelVersion::Version2013);
	workbook->Dispose();
	delete workbook;
}

Ungroup Rows and Columns in Excel in C++

By ungrouping rows and columns, you can restore each cell to its initial, standalone state, helping you view the data as it was originally presented.

Spire.XLS for C++ offers the XlsWorksheet->UngroupByRows(int firstRow, int lastRow) and XlsWorksheet->UngroupByColumns (int firstColumn, int lastColumn) methods to help you ungroup rows and columns. The detailed steps are as follows:

• Initialize an instance of the Workbook class.
• Load an Excel file using the Workbook->LoadFromFile(LPCWSTR_S fileName) method.
• Get a specific worksheet by its index using the Workbook->GetWorksheets()->Get(int index) method.
• Ungroup specific rows in the worksheet using the XlsWorksheet->UngroupByRows(int firstRow, int lastRow) method.
• Ungroup specific columns in the worksheet using the XlsWorksheet->UngroupByColumns (int firstColumn, int lastColumn) method.
• Save the result file using the Workbook->SaveToFile (LPCWSTR_S fileName, ExcelVersion version) method.

#include "Spire.Xls.o.h";

using namespace Spire::Xls;


int main()
{
	//Initialize an instance of the Workbook class
	Workbook* workbook = new Workbook();

	//Load an Excel file
	workbook->LoadFromFile(L" GroupRowsAndColumns.xlsx");

	//Get the first worksheet
	Worksheet* sheet = workbook->GetWorksheets()->Get(0);

	//Ungroup the 2nd, 3rd, 4th, 5th and 6th rows
	sheet->UngroupByRows(2, 6);

	//Ungroup the 3rd and 4th columns
	sheet->UngroupByColumns(3, 4);

	//Save the result file
	workbook->SaveToFile(L"UngroupRowsAndColumns.xlsx", ExcelVersion::Version2013);
	workbook->Dispose();
	delete workbook;
}

Expand or Collapse Groups in Excel in C++

When grouping rows or columns in Excel, you can click the expand (+) or collapse (-) button to show or hide the grouped data. In Spire.XLS for C++, you can use the Worksheet->GetRange(LPCWSTR_S name)->ExpandGroup(GroupByType groupBy) method or the Worksheet->GetRange(LPCWSTR_S name)->CollapseGroup(GroupByType groupBy) method to achieve the same expanding or collapsing effect. The detailed steps are as follows:

• Initialize an instance of the Workbook class.
• Load an Excel file using the Workbook->LoadFromFile(LPCWSTR_S fileName) method.
• Get a specific worksheet by its index using the Workbook->GetWorksheets()->Get(int index) method.
• Expand a specific group using the Worksheet->GetRange(LPCWSTR_S name)->ExpandGroup(GroupByType groupBy) method.
• Collapse a specific group using the Worksheet->GetRange(LPCWSTR_S name)->CollapseGroup(GroupByType groupBy) method.
• Save the result file using the Workbook->SaveToFile (LPCWSTR_S fileName, ExcelVersion version) method.

#include "Spire.Xls.o.h";

using namespace Spire::Xls;


int main()
{
	//Initialize an instance of the Workbook class
	Workbook* workbook = new Workbook();

	//Load an Excel file
	workbook->LoadFromFile(L"Input.xlsx");

	//Get the first worksheet
	Worksheet* sheet = workbook->GetWorksheets()->Get(0);

	//Expand a group
	sheet->GetRange(L"A2:E6")->ExpandGroup(GroupByType::ByRows);

	//Collapse a group
	sheet->GetRange(L"C1:D11")->CollapseGroup(GroupByType::ByColumns);

	//Save the result file
	workbook->SaveToFile(L"ExpandOrCollapseGroups.xlsx", ExcelVersion::Version2013);
	workbook->Dispose();
	delete workbook;
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.