DEV Community

Leon Davis
Leon Davis

Posted on

How to Merge Cells in Excel in C#

#csharp #excel #mergecells

Merging cells in Excel is a common task when formatting spreadsheets. Whether you want to create headings, organize data, or improve the visual appeal of your documents, merging cells allows you to combine multiple cells into a single larger one. In this tutorial, we’ll explore how to merge cells in an Excel spreadsheet using C# and the Spire.XLS library, which is an effective tool for handling Excel files in .NET applications.

Prerequisites

Before you start, make sure you have the following:

  • Visual Studio installed on your machine.
  • Spire.XLS installed. You can download it from the official site or install it via NuGet in Visual Studio.

To install Spire.XLS via NuGet:

  • Open NuGet Package Manager in Visual Studio.
  • Search for Spire.XLS and click Install .

Now you're ready to begin.

1. Setting Up Your Project

Start by creating a new Console Application project in Visual Studio.

  • Open Visual Studio.
  • Select Create a new project .
  • Choose Console App (.NET Core) .
  • Name your project (e.g., MergeCellsInExcel).
  • Click Create .

Next, add the Spire.XLS library to your project via NuGet Package Manager.

2. Writing the Code to Merge Cells in Excel Using C

Once you’ve set up your project, the next step is to write the code that will merge cells in an Excel worksheet.

Here is a simple example of how to do this:

using System;
using Spire.Xls;

namespace MergeCellsExample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a new Workbook
            Workbook workbook = new Workbook();

            // Access the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Set some data in the cells
            sheet.Range["A1"].Text = "Merged Cells Example";
            sheet.Range["A2"].Text = "This is a sample cell content.";

            // Merge cells from A1 to D1
            sheet.Range["A1:D1"].Merge();

            // Format the merged cell
            sheet.Range["A1"].CellStyle.HorizontalAlignment = HorizontalAlignType.Center;
            sheet.Range["A1"].CellStyle.VerticalAlignment = VerticalAlignType.Center;
            sheet.Range["A1"].CellStyle.Font.IsBold = true;
            sheet.Range["A1"].CellStyle.Font.Size = 14;

            // Save the workbook to a file
            workbook.SaveToFile("MergedCellsExample.xlsx", ExcelVersion.Version2013);

            Console.WriteLine("Excel file with merged cells created successfully!");
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Explanation of the Code:

  • Creating a Workbook : 
   Workbook workbook = new Workbook();
Enter fullscreen mode Exit fullscreen mode

This initializes a new Excel workbook. The Workbook class represents the entire Excel file.

  • Accessing the Worksheet : 
   Worksheet sheet = workbook.Worksheets[0];
Enter fullscreen mode Exit fullscreen mode

Excel files can contain multiple worksheets. Here, we access the first worksheet in the workbook by specifying Worksheets[0].

  • Setting Data in Cells : 
   sheet.Range["A1"].Text = "Merged Cells Example";
Enter fullscreen mode Exit fullscreen mode

The Range property refers to a specific cell or a group of cells. In this case, we set the text in cell A1.

  • Merging Cells : 
   sheet.Range["A1:D1"].Merge();
Enter fullscreen mode Exit fullscreen mode

This command merges cells A1 through D1 into a single cell. After merging, only the content of the upper-left cell (A1) will be visible, and the other cells will be empty.

  • Formatting the Merged Cell : 
   sheet.Range["A1"].CellStyle.HorizontalAlignment = HorizontalAlignType.Center;
   sheet.Range["A1"].CellStyle.VerticalAlignment = VerticalAlignType.Center;
   sheet.Range["A1"].CellStyle.Font.IsBold = true;
   sheet.Range["A1"].CellStyle.Font.Size = 14;
Enter fullscreen mode Exit fullscreen mode

These lines apply styling to the merged cell. The text is centered both horizontally and vertically, the font is set to bold, and the font size is increased to 14 points.

  • Saving the Workbook : 
   workbook.SaveToFile("MergedCellsExample.xlsx", ExcelVersion.Version2013);
Enter fullscreen mode Exit fullscreen mode

Finally, the workbook is saved to a file named MergedCellsExample.xlsx. The ExcelVersion.Version2013 ensures compatibility with newer Excel file formats.

3. Running the Code

After writing the code, build and run your application. Once executed, the program will generate an Excel file with the merged cells as specified in the code. You can open this file in Excel to verify that the cells have been successfully merged and formatted.

4. Additional Tips

  • Merging Multiple Rows or Columns : You can merge a range of rows or columns by changing the cell range. For example, merge multiple rows: 
  sheet.Range["A1:A5"].Merge();
Enter fullscreen mode Exit fullscreen mode
  • Unmerging Cells : If you need to unmerge cells, you can use the UnMerge method: 
  sheet.Range["A1:D1"].UnMerge();
Enter fullscreen mode Exit fullscreen mode
  • Formatting : Spire.XLS allows for a lot of flexibility in formatting merged cells. You can apply colors, borders, and other styles, making your Excel documents not only functional but also visually appealing.

Conclusion

Merging cells in Excel is a powerful tool for managing data and creating structured reports. By using Spire.XLS in C#, you can easily automate this process within your applications. The code shown in this tutorial is just a basic example; you can extend it by adding more complex features such as dynamic data input, conditional formatting, or even automating tasks across multiple sheets.

Top comments (0)