How to Merge Cells in Excel in C#

Source: Dev.to

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:

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

Now you’re ready to begin.

Setting Up Your Project

1. Create 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.

2. Add the Spire.XLS library to your project via NuGet Package Manager.

Writing the Code to Merge Cells in Excel Using C#

Here is a simple example of how to merge cells:

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!");
        }
    }
}

Explanation of the Code

Creating a Workbook

Workbook workbook = new Workbook();

Initializes a new Excel workbook.

Accessing the Worksheet

Worksheet sheet = workbook.Worksheets[0];

Gets the first worksheet in the workbook.

Setting Data in Cells

sheet.Range["A1"].Text = "Merged Cells Example";

Assigns text to cell A1.

Merging Cells

sheet.Range["A1:D1"].Merge();

Merges the range A1:D1 into a single cell.

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;

Centers the text, makes it bold, and sets the font size to 14.

Saving the Workbook

workbook.SaveToFile("MergedCellsExample.xlsx", ExcelVersion.Version2013);

Saves the file as MergedCellsExample.xlsx compatible with Excel 2013 and later.

Running the Code

Build and run your application. The program will generate an Excel file with the merged cells as specified. Open the file in Excel to verify that the cells have been merged and formatted correctly.

Additional Tips

Merging Multiple Rows or Columns

You can merge a range of rows or columns by changing the cell range. For example, to merge cells vertically:

sheet.Range["A1:A5"].Merge();

Unmerging Cells

To unmerge a previously merged range:

sheet.Range["A1:D1"].UnMerge();

Formatting

Spire.XLS offers extensive formatting options, including colors, borders, and other styles, allowing you to create visually appealing Excel documents.

Conclusion

Merging cells in Excel is a powerful tool for organizing data and creating structured reports. Using Spire.XLS in C# lets you automate this process within your applications. The example above provides a basic foundation that you can extend with dynamic data input, conditional formatting, or automation across multiple sheets.