Merging and unmerging cells is a simple and commonly used feature of Microsoft Excel. Merging cells might prove to be helpful in scenarios, for example, when you have a worksheet with multiple columns that share the same heading. You can merge the cells above the columns to give them a common heading. In case merged cells are no longer required, you can unmerge them just as easily. You might need to perform these tasks within your C++ applications. For that, this article will teach you how to merge and unmerge cells in Excel worksheets programmatically using C++.
- C++ API for Merging and Unmerging Cells
- Merge Cells in an Excel Worksheet using C++
- Unmerge Cells in an Excel Worksheet using C++
- Merge a Range of Cells in an Excel Worksheet using C++
- Unmerge a Range of Cells in an Excel Worksheet using C++
- Merge Cells of a Named Range in an Excel Worksheet using C++
C++ API for Merging and Unmerging Cells
Aspose.Cells for C++ is a native C++ library that allows you to create, read and modify Excel files without requiring Microsoft Excel to be installed. The API also supports merging and unmerging cells in an Excel worksheet. You can either install the API through NuGet or download it directly from the Downloads section.
PM> Install-Package Aspose.Cells.Cpp
Merge Cells in an Excel Worksheet using C++
In this example, we will create an empty Excel worksheet and merge a few cells by following the steps given below.
- Firstly, create an instance of the IWorkbook class.
- Retrieve the worksheet using the IWorkbook->GetIWorksheets()->GetObjectByIndex (Aspose::Cells::Systems::Int32 index) method.
- Retrieve the cells using the IWorksheet->GetICells() method.
- Merge the cells using the ICells->Merge(Aspose::Cells::Systems::Int32 firstRow, Aspose::Cells::Systems::Int32 firstColumn, Aspose::Cells::Systems::Int32 totalRows, Aspose::Cells::Systems::Int32 totalColumns) method.
- Apply styles to the merged cells.
- Finally, save the output Excel file using the IWorkbook->Save (intrusive_ptrAspose::Cells::Systems::String fileName) method.
The following sample code shows how to merge cells in an Excel worksheet using C++.
| // Output directory path. | |
| StringPtr outDir = new String("OutputDirectory\\"); | |
| // Load the input Excel file | |
| intrusive_ptr<IWorkbook> workbook = Factory::CreateIWorkbook(); | |
| // Access the first worksheet in the Excel file | |
| intrusive_ptr<IWorksheet> worksheet = workbook->GetIWorksheets()->GetObjectByIndex(0); | |
| // Create a Cells object ot fetch all the cells. | |
| intrusive_ptr<ICells> cells = worksheet->GetICells(); | |
| // Merge cells | |
| cells->Merge(5, 2, 2, 3); | |
| // Put data into the cell | |
| cells->GetObjectByIndex(5, 2)->PutValue((StringPtr)new String("This is a test value")); | |
| // Create a Style object | |
| intrusive_ptr<IStyle> style = cells->GetICell(5, 2)->GetIStyle(); | |
| // Create a Font object | |
| intrusive_ptr<Aspose::Cells::IFont> font = style->GetIFont(); | |
| // Set the name | |
| font->SetName(new String("Times New Roman")); | |
| // Set the font size | |
| font->SetSize(18); | |
| // Set the font color | |
| font->SetColor(Systems::Drawing::Color::GetCyan()); | |
| // Make the text bold | |
| font->SetBold(true); | |
| // Make the text italic | |
| font->SetItalic(true); | |
| // Set the Foreground color | |
| style->SetForegroundColor(Systems::Drawing::Color::GetRed()); | |
| // Set the Pattern | |
| style->SetPattern(BackgroundType_Solid); | |
| // Apply the Style | |
| cells->GetICell(5, 2)->SetIStyle(style); | |
| // Save the Excel file | |
| workbook->Save(outDir->StringAppend(new String("MergeCells_out.xlsx"))); |
Image of the output Excel file generated by the sample code
Unmerge Cells in an Excel Worksheet using C++
The following are the steps to unmerge cells in an Excel worksheet.
- Load the Excel file using the IWorkbook class.
- Retrieve the worksheet containing merged cells using the IWorkbook->GetIWorksheets()->GetObjectByIndex (Aspose::Cells::Systems::Int32 index) method.
- Retrieve the cells using the IWorksheet->GetICells() method.
- Unmerge cells using the ICells->UnMerge(Aspose::Cells::Systems::Int32 firstRow, Aspose::Cells::Systems::Int32 firstColumn, Aspose::Cells::Systems::Int32 totalRows, Aspose::Cells::Systems::Int32 totalColumns) method.
- Finally, save the output Excel file using the IWorkbook->Save (intrusive_ptrAspose::Cells::Systems::String fileName) method.
The following sample code demonstrates how to unmerge cells in an Excel worksheet using C++.
| // Source directory path. | |
| StringPtr srcDir = new String("SourceDirectory\\"); | |
| // Output directory path. | |
| StringPtr outDir = new String("OutputDirectory\\"); | |
| // Load the input Excel file | |
| intrusive_ptr<IWorkbook> workbook = Factory::CreateIWorkbook(srcDir->StringAppend(new String("SampleMergedCells.xlsx"))); | |
| // Access the first worksheet in the Excel file | |
| intrusive_ptr<IWorksheet> worksheet = workbook->GetIWorksheets()->GetObjectByIndex(0); | |
| // Create a Cells object ot fetch all the cells. | |
| intrusive_ptr<ICells> cells = worksheet->GetICells(); | |
| // Unmerge cells. | |
| cells->UnMerge(5, 2, 2, 3); | |
| // Save the Excel file | |
| workbook->Save(outDir->StringAppend(new String("UnmergeCells_out.xlsx"))); |
Image of the output Excel file generated by the sample code
Merge a Range of Cells in an Excel Worksheet using C++
The following are the steps to merge a range of cells in an Excel worksheet.
- Firstly, create an instance of the IWorkbook class.
- Retrieve the desired worksheet using the IWorkbook->GetIWorksheets()->GetObjectByIndex (Aspose::Cells::Systems::Int32 index) method.
- Insert data into the cell.
- Create the range using the IWorksheet->GetICells()->CreateIRange(intrusive_ptrAspose::Cells::Systems::String address) method.
- Merge the range using the IRange->Merge() method.
- Finally, save the output Excel file using the IWorkbook->Save (intrusive_ptrAspose::Cells::Systems::String fileName) method.
The following sample code shows how to merge a range of cells in an Excel worksheet using C++.
| // Output directory path. | |
| StringPtr outDir = new String("OutputDirectory\\"); | |
| // Load the input Excel file | |
| intrusive_ptr<IWorkbook> workbook = Factory::CreateIWorkbook(); | |
| // Access the first worksheet in the Excel file | |
| intrusive_ptr<IWorksheet> worksheet = workbook->GetIWorksheets()->GetObjectByIndex(0); | |
| // Input data into A1 Cell. | |
| worksheet->GetICells()->GetObjectByIndex(0, 0)->PutValue((StringPtr)new String("This is a test value")); | |
| // Create a range | |
| intrusive_ptr<IRange> range = worksheet->GetICells()->CreateIRange(new String("A1:D4")); | |
| // Merge range into a single cell | |
| range->Merge(); | |
| // Save the Excel file | |
| workbook->Save(outDir->StringAppend(new String("MergeRangeOfCells_out.xlsx"))); |
Image of the output Excel file generated by the sample code
Unmerge a Range of Cells in an Excel Worksheet using C++
The following are the steps to unmerge a range of cells in an Excel worksheet.
- Load the Excel file using the IWorkbook class.
- Retrieve the worksheet containing merged cells using the IWorkbook->GetIWorksheets()->GetObjectByIndex (Aspose::Cells::Systems::Int32 index) method.
- Create the range using the IWorksheet->GetICells()->CreateIRange(intrusive_ptrAspose::Cells::Systems::String address) method.
- Unmerge the range using the IRange->UnMerge() method.
- Finally, save the output Excel file using the IWorkbook->Save (intrusive_ptrAspose::Cells::Systems::String fileName) method.
The following sample code shows how to unmerge a range of cells in an Excel worksheet using C++.
| // Source directory path. | |
| StringPtr srcDir = new String("SourceDirectory\\"); | |
| // Output directory path. | |
| StringPtr outDir = new String("OutputDirectory\\"); | |
| // Load the input Excel file | |
| intrusive_ptr<IWorkbook> workbook = Factory::CreateIWorkbook(srcDir->StringAppend(new String("SampleMergedRangeOfCells.xlsx"))); | |
| // Access the first worksheet in the Excel file | |
| intrusive_ptr<IWorksheet> worksheet = workbook->GetIWorksheets()->GetObjectByIndex(0); | |
| // Create a range | |
| intrusive_ptr<IRange> range = worksheet->GetICells()->CreateIRange(new String("A1:D4")); | |
| // UnMerge range | |
| range->UnMerge(); | |
| // Save the Excel file | |
| workbook->Save(outDir->StringAppend(new String("UnmergeRangeOfCells_out.xlsx"))); |
Image of the output Excel file generated by the sample code
Merge Cells of a Named Range in an Excel Worksheet using C++
Aspose.Cells for C++ also provides you the ability to merge the cells of a named range. To achieve this, please follow the steps given below.
- Load the Excel file using the IWorkbook class.
- Retrieve the worksheet using the IWorkbook->GetIWorksheets()->GetObjectByIndex (Aspose::Cells::Systems::Int32 index) method.
- Create the range using the IWorksheet->GetICells()->CreateIRange(intrusive_ptrAspose::Cells::Systems::String address) method.
- Set the name of the range using the IRange->SetName(intrusive_ptrAspose::Cells::Systems::String value) method.
- Create and apply styles to the named range.
- Merge the cells of the named range using the IRange->Merge() method.
- Finally, save the output Excel file using the IWorkbook->Save (intrusive_ptrAspose::Cells::Systems::String fileName) method.
The following sample code demonstrates how to merge the cells of a named range using C++.
| // Source directory path. | |
| StringPtr srcDir = new String("SourceDirectory\\"); | |
| // Output directory path. | |
| StringPtr outDir = new String("OutputDirectory\\"); | |
| // Load the input Excel file | |
| intrusive_ptr<IWorkbook> workbook = Factory::CreateIWorkbook(srcDir->StringAppend(new String("SampleMergedRangeOfCells.xlsx"))); | |
| // Access the first worksheet in the Excel file | |
| intrusive_ptr<IWorksheet> worksheet = workbook->GetIWorksheets()->GetObjectByIndex(0); | |
| // Create a range | |
| intrusive_ptr<IRange> range = worksheet->GetICells()->CreateIRange(new String("A1:D4")); | |
| // Set Range name | |
| range->SetName(new String("Named_Range")); | |
| // Define style object | |
| intrusive_ptr<IStyle> style = workbook->CreateIStyle(); | |
| // Set horizontal alignment | |
| style->SetHorizontalAlignment(TextAlignmentType::TextAlignmentType_Center); | |
| // Create a StyleFlag object | |
| intrusive_ptr<IStyleFlag> styleFlag = Factory::CreateIStyleFlag(); | |
| // Set horizontal alignment to true | |
| styleFlag->SetHorizontalAlignment(true); | |
| // Apply the style to the range | |
| range->ApplyIStyle(style, styleFlag); | |
| // Put data in the range | |
| range->GetObjectByIndex(0, 0)->PutValue((StringPtr)new String("This is a test value")); | |
| // Merge Range | |
| range->Merge(); | |
| // Save the Excel file | |
| workbook->Save(outDir->StringAppend(new String("MergeNamedRange_out.xlsx"))); |
Image of the output Excel file generated by the sample code
Get a Free License
You can try the API without evaluation limitations by requesting a free temporary license.
Conclusion
In this article, you have learned how to merge and unmerge cells in an Excel worksheet using C++. Furthermore, you have seen how to merge and unmerge ranges and named ranges using Aspose.Cells for C++ API. The API provides many additional features for working with Excel files that you can explore in detail by visiting the official documentation. In case of any questions, please feel free to reach us on our free support forum.