NativeExcel2 for .NET Developer's Reference

Merged cells

Example

This example demonstrates how to merge cells

Output file: console-mergedcells.xls

[C#]
using System;
using NativeExcel;

namespace Console_MergedCells
{
    class Program
    {
        static void Main(string[] args)
        {
            string FileName = "console-mergedcells.xls";
            CreateWorkbook(FileName);
            OpenWorkbookWithExcel(FileName);
        }

        static void CreateWorkbook(string FileName) {
            //Create a new workbook
            IWorkbook book = NativeExcel.Factory.CreateWorkbook();
            //Add worksheet
            IWorksheet sheet = book.Worksheets.Add();

            //merge column 
            sheet.Range["B2:B5"].Merge(); 
            sheet.Range["B2"].Value = "Merge column";

            //merge row 
            sheet.Range["C2:F2"].Merge(); 
            sheet.Range["C2"].Value = "Merge row";

            //merge area 
            sheet.Range["C3:F5"].Merge(); 
            sheet.Range["C3:F5"].Value = "Merge area";

            //merge each row in the area 
            sheet.Range["B7:F10"].Rows.Merge(); 
            sheet.Range["B7:F10"].Value = "Merge each row in the area";

            //merge each column in the area
            sheet.Range["B11:F20"].Columns.Merge();
            sheet.Range["B11:F20"].Value = "Merge each column in the area";

            //Align text in the UsedRange
            sheet.UsedRange.WrapText = true;
            sheet.UsedRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
            sheet.UsedRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            
            //Unmerge  
            IRange cell = sheet.Range["D11"];
            if (cell.MergeCells) {
                cell.UnMerge();
                cell.Value = "Unmerged";
            } 
            //Borders
            sheet.UsedRange.Borders[XlBordersIndex.xlAround].ColorIndex = XlColorIndex.xlColorIndexAutomatic;
            sheet.UsedRange.Borders[XlBordersIndex.xlInsideAll].ColorIndex = XlColorIndex.xlColorIndexAutomatic;

            book.SaveAs(FileName);
        }

        static void OpenWorkbookWithExcel(string FileName){
            try {
                System.Diagnostics.Process.Start(FileName);
            } catch {
                Console.WriteLine(FileName + " created in application folder");
            }
        }
    }
}
[Visual Basic]
imports System
imports NativeExcel

Module Console_MergedCells

    Sub Main()
        Dim FileName As String = "console-mergedcells.xls"
        CreateWorkbook(FileName)
        OpenWorkbookWithExcel(FileName)
    End Sub

    Sub CreateWorkbook(FileName As String)

        'Create a new workbook
        Dim book As IWorkbook = NativeExcel.Factory.CreateWorkbook()
        'Add worksheet
        Dim sheet As IWorksheet = book.Worksheets.Add()

        'merge column 
        sheet.Range("B2:B5").Merge()
        sheet.Range("B2").Value = "Merge column"

        'merge row 
        sheet.Range("C2:F2").Merge()
        sheet.Range("C2").Value = "Merge row"

        'merge area 
        sheet.Range("C3:F5").Merge() 
        sheet.Range("C3:F5").Value = "Merge area"

        'merge each row in the area 
        sheet.Range("B7:F10").Rows.Merge() 
        sheet.Range("B7:F10").Value = "Merge each row in the area"

        'merge each column in the area
        sheet.Range("B11:F20").Columns.Merge()
        sheet.Range("B11:F20").Value = "Merge each column in the area"

        'Align text in the UsedRange
        sheet.UsedRange.WrapText = true
        sheet.UsedRange.VerticalAlignment = XlVAlign.xlVAlignCenter
        sheet.UsedRange.HorizontalAlignment = XlHAlign.xlHAlignCenter
        
        'Unmerge  
        Dim cell As IRange = sheet.Range("D11")
        If cell.MergeCells Then
            cell.UnMerge()
            cell.Value = "Unmerged"
        End If

        'Borders
        sheet.UsedRange.Borders(XlBordersIndex.xlAround).ColorIndex = XlColorIndex.xlColorIndexAutomatic
        sheet.UsedRange.Borders(XlBordersIndex.xlInsideAll).ColorIndex = XlColorIndex.xlColorIndexAutomatic

        'Save workbook
        book.SaveAs(FileName)

    End Sub

    Sub OpenWorkbookWithExcel(FileName As String)
        Try
           System.Diagnostics.Process.Start(FileName)
        Catch
           Console.WriteLine(FileName + " created in application folder")
        End Try  
    End Sub

End Module