EPPlus: How to style merged cells?

Jakotheshadows picture Jakotheshadows · Dec 12, 2013 · Viewed 17.9k times · Source

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.

Answer

André Pena picture André Pena · Dec 12, 2013

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