A common operation in Microsoft Excel is to merge two or more cells. This gives a more organized look and feels to your data with the sense of data grouping and headers information. You can merge as many cells as well as rows and columns using Excel. As a .NET application developer, you may be interested in providing the functionality of merging cells in Excel spreadsheets from within your application. You can achieve this using NPOI API in your application that can merge cells or cell ranges using C# or VB.NET as shown in this article.
But before we can jump into writing a .NET application for this purpose, let’s have a look at how to merge cells using Microsoft Excel.
Merge Cells using Microsoft Excel
Merging cells using Microsoft Excel is very easy and is a common operation. You can merge cells, rows, or columns from within Excel. However, note that if both cells contain data, then one of the data in this process will be lost.
Steps to Merge Cells with Microsoft Excel
If you want to merge cells using Microsoft Excel, you can use the following steps.
- Open Microsoft Excel and populate the spreadsheet with data
- Select the cells to be merged by dragging the mouse over them while holding the left click
- In the Ribbon, locate the “Merge” icon in “Home” tab and click on “Merge Cells”
This will merge the selected cells into a single cell.
Merge Cells in Excel using NPOI API in C#
Now that we have seen how to merge cells using Microsoft Excel, let us now have a look at how we can do the same in our .NET application. We’ll be using the open-source API NPOI in our .NET application and will write the code in C# though the same can easily be converted to VB.NET.
But before that, you need to install NPOI in your .NET project to get started. You can learn about this in our detailed instructions guide for Installing NPOI for .NET.
Merge Cells in Excel Spreadsheet using C#
Now that your development environment is ready, create a console application and add the following code to it.
//Create workbook IWorkbook wb = new XSSFWorkbook(); ISheet ws = wb.CreateSheet("MySheet"); //Set the value of the cell ws.CreateRow(0).CreateCell(0).SetCellValue("FileFormat.com"); //Merge the cell CellRangeAddress region = new CellRangeAddress(0, 1, 0, 1); ws.AddMergedRegion(region); //Save the file FileStream file = File.Create("CellsMerge.xlsx"); wb.Write(file, false); file.Close();
In this article, we showed how to merge cells in an Excel workbook using NPOI API. You can further explore the API functionality by further studying the API documentation. If you would like to know more about working with Excel workbooks using NPOI, stay tuned for more examples in this section.