Merge cells using EPPlus?

I’m using the EPPlus library to read/write Excel files: http://epplus.codeplex.com/

I’m trying to simply merge some cells when writing a document:

using (ExcelPackage pck = new ExcelPackage())
{
    //Create the worksheet
    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");

    //Format the header for column 1-3
    using (ExcelRange rng = ws.Cells["A1:C1"])
    {
        bool merge = rng.Merge;
    }
}

There’s a property named Merge that simply returns true or false. I thought maybe that would Merge the cells, but it doesn’t.

Anyone know how to do this?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You have to use it like this:

ws.Cells["A1:C1"].Merge = true;

instead of:

using (ExcelRange rng = ws.Cells["A1:C1"])
{
    bool merge = rng.Merge;
}

Method 2

If you want to merge cells dynamically, you can also use:

worksheet.Cells[FromRow, FromColumn, ToRow, ToColumn].Merge = true;

All these variables are integers.

Method 3

You can create a extension method:

public static void Merge(this ExcelRangeBase range)
{
    ExcelCellAddress start = range.Start;
    ExcelCellAddress end = range.End;
    range.Worksheet.Cells[start.Row, start.Column, end.Row, end.Column].Merge = true;
}

You can use this as you would via interop:

range.Merge();

Method 4

int inicio = CELDA_CUERPO;
bool values = true;

    int COLUMNA_A = 0;
    int finx_a = 0;
    int finy_a = 0;

    int COLUMNA_B = 1;
    int finx_b = 0;
    int finy_b = 0;

//Pintar cabecera:

    for (int i = 0; i < ListaCabecera.Length; i++)
    {
        celda = $"{letras[i]}{CELDA_CABECERA}";

        if (i == 0)
        {
            inicioRango = celda;
        }

        Sheet.Cells[celda].Value = ListaCabecera[i];
        //System.Drawing.Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#B7DEE8");
        Sheet.Cells[celda].Style.Font.Color.SetColor(Color.FromArgb(0, 124, 183));
        Sheet.Cells[celda].Style.Fill.PatternType = ExcelFillStyle.Solid;
        Sheet.Cells[celda].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(232, 235, 249));
        Sheet.Cells[celda].Style.Font.Bold = true;
        Sheet.Cells[celda].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
    }

    //Pintar detalle:
    for (int i = 0; i < Datos.GetLength(0); i++)
    {
        for (int j = 0; j < Datos.GetLength(1); j++)
        {
            celda = $"{letras[j]}{CELDA_CUERPO + i}";
            finalizaRango = celda;

            if (j < 3) if (Datos[i, j].valor != null && Datos[i, j + 1].valor == null)
                {
                    Sheet.Cells[celda].Style.Font.Color.SetColor(Color.FromArgb(156, 0, 6));
                    Sheet.Cells[celda].Style.Fill.PatternType = ExcelFillStyle.Solid;
                    Sheet.Cells[celda].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 199,206));
                }
            Sheet.Cells[celda].Value = Datos[i, j].valor;
        }

        //::::::::::::::::::: MERGE A ::::::::::::::::::::
        if (i < Datos.GetLength(0) - 1)
        {
            // :::::::::::::::::::::: x :::::::::::::::::::::::::::::::::
            if (values)
            {
                if (Datos[i, COLUMNA_A].valor == Datos[i, COLUMNA_A].valor)
                {
                    values = false;
                    finx_a = inicio + i;
                    finx_b = inicio + i;
                    //list_x.Add(finx_b);
                }
            }
            else
            {
                if (Datos[i - 1, COLUMNA_A].valor != Datos[i, COLUMNA_A].valor)
                {
                    finx_a = inicio + i;
                }

                if (Datos[i - 1, COLUMNA_B].valor != Datos[i, COLUMNA_B].valor)
                {
                    finx_b = inicio + i;
                    //list_x.Add(finx_b);
                }
             }

            // :::::::::::::::::::::: y (A) :::::::::::::::::::::::::::::::::
            if (Datos[i, COLUMNA_A].valor != Datos[i + 1, COLUMNA_A].valor)
            {
                finy_a = inicio + i;
                //list_y.Add(finy);
                Sheet.Cells[$"A{finx_a}:A{finy_a}"].Value = Datos[i, COLUMNA_A].valor;
                Sheet.Cells[$"A{finx_a}:A{finy_a}"].Merge = true;
                Sheet.Cells[$"A{finx_a}:A{finy_a}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            }

            // :::::::::::::::::::::: y (B) :::::::::::::::::::::::::::::::::
            if (Datos[i, COLUMNA_B].valor != Datos[i + 1, COLUMNA_B].valor)
            {
                finy_b = inicio + i;
                //list_y.Add(finy_b);
                Sheet.Cells[$"B{finx_b}:B{finy_b}"].Value = Datos[i, COLUMNA_B].valor;
                Sheet.Cells[$"B{finx_b}:B{finy_b}"].Merge = true;
                Sheet.Cells[$"B{finx_b}:B{finy_b}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            }
       
         }
        //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
     }

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0