EPPlus: How to style merged cells?

Question

Edit: These examples should all work. My problem was actually unrelated to epplus and this code as well as the marked answer works for styling merged cells.

I'd like to be able to style a merged cell, however, my attempts to style it have no effect. Here is how I am merging the cells:

WorkSheet.Cells["A1:K1"].Merge = true;

Here is how I have tried to set the background and font color on this merged cell:

WorkSheet.Cells["A1:K1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
WorkSheet.Cells["A1:K1"].Style.Fill.BackgroundColor.SetColor(Color.Black);
WorkSheet.Cells["A1:K1"].Style.Font.Color.SetColor(Color.Red);

Another way I have tried:

WorkSheet.Cells["A1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
WorkSheet.Cells["A1"].Style.Fill.BackgroundColor.SetColor(Color.Black);
WorkSheet.Cells["A1"].Style.Font.Color.SetColor(Color.Red);

Yet another way I have tried:

WorkSheet.Cells[1, 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
WorkSheet.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(Color.Black);
WorkSheet.Cells[1, 1].Style.Font.Color.SetColor(Color.Red);

What am I missing here? I am able to style other cells that haven't been merged with no difficulty, but my merged cells won't change.

Beeman

Just tried looping through each cell that gets merged and styling it, also tried WorkSheet.Cells[1, 1, 1, 11].... with no success. – Beeman

Answer 1

I can't reproduce the problem.. Here's an example of styling merged cells. See if you can find what you did wrong. All you have to do is run it.

Please let me know if you have any doubt.

    var stream = new MemoryStream();

// print header
using (var package = new ExcelPackage(stream))
{
    // add a new worksheet to the empty workbook
    var worksheet = package.Workbook.Worksheets.Add("testsheet");
    for(var i = 0; i < 10; i++)
        worksheet.Cells[i + 1, 1].Value = i.ToString();

    worksheet.Cells["A1:A3"].Merge = true;
    worksheet.Cells["A1:A3"].Style.VerticalAlignment = ExcelVerticalAlignment.Top;
    worksheet.Cells["A1:A3"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
    worksheet.Cells["A1:A3"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
    worksheet.Cells["A1:A3"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
    worksheet.Cells["A1:A3"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
    worksheet.Cells["A1:A3"].Style.Fill.PatternType = ExcelFillStyle.Solid;
    worksheet.Cells["A1:A3"].Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#f0f3f5"));

     package.Save();
}

stream.Seek(0, SeekOrigin.Begin);

using (Stream file = File.Open("sample.xlsx", FileMode.Create))
{
    var buffer = new byte[8 * 1024];
    int len;
    while ((len = stream.Read(buffer, 0, buffer.Length)) > 0)
        file.Write(buffer, 0, len);
}

Danettedaney

Thanks. My example also works, the code I showed was just not being executed due to an unrelated issue. I mistakenly assumed I was using EPPlus incorrectly. – Beeman