Merge cells in excel using OfficeOpenXml

Question

I am creating a excel document via x++ using OfficeOpenXml Api, but I have no idea how to merge cells (Columns specifically). I have found how to do it with COM, there is a way to do it with the mentioned Api?

If the solution from Merge cells using EPPlus? is used, an Invalid token '.'. compiler error is shown:

CustTableWorksheet.Cells["B1:D1"].Merge = true;

Answer 1

Using .NET libraries in x++ can be tricky sometimes, which is why there is documentation on the differences: .NET Interop from X++ (X++ and C# comparison may also be of interest).

Looking at some other examples how the EEPlus library is used in x++, e.g. in class CustCollectionsExcelStatement, I noticed that instead of the Cells["address"] syntax to determine a cell range, method get_Item is used instead. This is because .NET arrays have restricted support in x++ (see How to: Use X++ Syntax for CLR Arrays for more information).

So I rewrote the statement as

CustTableWorksheet.get_Item("B1:D1").Merge = true;

Unfortunately, this causes other compiler errors. After some further trial and error I ended up with the following:

ExcelRange cells = worksheet.Cells.get_Item("B1:D1");
cells.Merge = true;

I don't have a source for this, but I think this is because chaining multiple expressions for .NET objects can cause issues in x++.

Here is my full test sample code:

using OfficeOpenXml;

class CreateExcelWithMergedCells
{
    public static void main(Args _args)
    {
        using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
        {
            using (var package = new ExcelPackage(stream))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add('Merged cells');
            
                ExcelRange cell = worksheet.Cells.get_Item('B1');
                cell.Value = 'Hello World';

                ExcelRange cells = worksheet.Cells.get_Item('B1:D1');
                cells.Merge = true;
            
                package.Save();
            }
            File::SendFileToUser(stream, 'Merged cells demo.xlsx');
        }
    }
}

And this is what the resulting Excel file looks like: