C++: Merge or Unmerge Cells in Excel

Merging and unmerging cells are two essential features in Microsoft Excel that allow users to create a more organized and visually appealing spreadsheet. Merging cells enables users to combine adjacent cells to create a single cell that spans multiple columns or rows. This feature is particularly useful for creating headers, titles, or labels for tables, as well as for combining data into a more concise format.

Unmerging cells, on the other hand, is the process of separating a merged cell back into individual cells. This feature is useful when users need to apply different formatting or styles to individual cells within a merged cell or when they want to separate data that were previously combined.

In this article, we will demonstrate how to merge or unmerge cells in an Excel file in C++ using Spire.XLS for C++ library.

• Merge Specific Cells in Excel in C++
• Unmerge Specific Merged Cells in Excel in C++
• Unmerge All Merged Cells 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 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.

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

Merge Specific Cells in Excel in C++

You can easily use the IXLSRange->Merge() method provided by Spire.XLS for C++ to merge a specific range of cells into a single cell. The detailed steps are as follows:

• Initialize an instance of the Workbook instance.
• Load an Excel file using the Workbook->LoadFromFile(LPCWSTR_S fileName) method.
• Get a specific worksheet of the file using the Workbbok->GetWorksheets()->Get(int index) method.
• Get the cell range that you want to merge using the Worksheet->GetRange(LPCWSTR_S name) method.
• Merge the cell range using the IXLSRange->Merge() method.
• Center the text in the merged cell using the IXLSRange->GetStyle()->SetHorizontalAlignment(HorizontalAlignType::Center) method.
• Save the result file to a specific location using the Workbook->SaveToFile(LPCWSTR_S fileName, ExcelVersion version) method.

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

using namespace Spire::Xls;
using namespace std;


int main()
{
	//Specify the input and output file paths
	wstring inputFile = L"Template.xlsx";
	wstring outputFile = L"MergeCells.xlsx";

	//Initialize an instance of the Workbook instance
	intrusive_ptr<Workbook> workbook = new Workbook();

	//Load an Excel file
	workbook->LoadFromFile(inputFile.c_str());

	//Get the first worksheet of the file
	intrusive_ptr<Worksheet> sheet = dynamic_pointer_cast<Worksheet>(workbook->GetWorksheets()->Get(0));

	//Merge a particular range of cells in the worksheet
	intrusive_ptr<IXLSRange> range = sheet->GetRange(L"A1:D1");
	range->Merge();

	//Center the text in the merged cell
	range->GetStyle()->SetHorizontalAlignment(HorizontalAlignType::Center);

	//Save the result file to the specific path
	workbook->SaveToFile(outputFile.c_str(), ExcelVersion::Version2013);
	workbook->Dispose();
}

Unmerge Specific Merged Cells in Excel in C++

To unmerge merged cells, you can use the IXLSRange->UnMerge() method. The detailed steps are as follows:

• Initialize an instance of the Workbook instance.
• Load an Excel file using the Workbook->LoadFromFile(LPCWSTR_S fileName) method.
• Get a specific worksheet of the file using the Workbbok->GetWorksheets()->Get(int index) method.
• Get the cell that you want to unmerge using the Worksheet->GetRange(LPCWSTR_S name) method.
• Unmerge the cell using the IXLSRange->UnMerge() method.
• Save the result file to a specific location using the Workbook->SaveToFile(LPCWSTR_S fileName, ExcelVersion version) method.

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

using namespace Spire::Xls;
using namespace std;

int main()
{
	//Specify the input and output file paths
	wstring inputFile = L"MergeCells.xlsx";
	wstring outputFile = L"UnmergeCells.xlsx";

	//Initialize an instance of the Workbook instance
	intrusive_ptr<Workbook> workbook = new Workbook();

	//Load an Excel file
	workbook->LoadFromFile(inputFile.c_str());

	//Get the first worksheet of the file
	intrusive_ptr<Worksheet> sheet = dynamic_pointer_cast<Worksheet>(workbook->GetWorksheets()->Get(0));

	//Unmerge a specific merged cell in the worksheet
	intrusive_ptr<IXLSRange> range = sheet->GetRange(L"A1");
	range->UnMerge();

	//Save the result file to the specific path
	workbook->SaveToFile(outputFile.c_str(), ExcelVersion::Version2013);
	workbook->Dispose();
}

Unmerge All Merged Cells in Excel in C++

Spire.XLS for C++ provides the Worksheet->GetMergedCells() method which enables you to obtain all merged cells in a specific worksheet. Once the merged cells are obtained, you can use the IXLSRange->UnMerge() method to unmerge them. The detailed steps are as follows:

• Initialize an instance of the Workbook instance.
• Load an Excel file using the Workbook->LoadFromFile(LPCWSTR_S fileName) method.
• Get a specific worksheet of the file using the Workbbok->GetWorksheets()->Get(int index) method.
• Get the merged cells in the worksheet using the Worksheet->GetMergedCells() method.
• Iterate through all the merged cells, then unmerge each merged cell using the IXLSRange->UnMerge() method.
• Save the result file to a specific location using the Workbook->SaveToFile(LPCWSTR_S fileName, ExcelVersion version) method.

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

using namespace Spire::Xls;
using namespace std;

int main()
{
	//Specify the input and output file paths
	wstring inputFile = L"MergeCells.xlsx";
	wstring outputFile = L"UnmergeAllCells.xlsx";

	//Initialize an instance of the Workbook class
	intrusive_ptr<Workbook> workbook = new Workbook();

	//Load an Excel file
	workbook->LoadFromFile(inputFile.c_str());

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

	//Get the merged cell ranges in the first worksheet
	intrusive_ptr<Spire::Common::IList<XlsRange>> range = sheet->GetMergedCells();

	//Iterate through the merged cells
	for (int i = 0; i < range->GetCount(); i++)
	{
		intrusive_ptr<XlsRange> cell = range->GetItem(i);
		//Unmerge each merged cell
		cell->UnMerge();
	}

	//Save the result file to the specific path
	workbook->SaveToFile(outputFile.c_str(), ExcelVersion::Version2013);
	workbook->Dispose();
}

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.