EPPlus isn't honoring ExcelHorizontalAlignment Center or Right

Question

I've tried this in EPPlus versions 3.1.3.0 and the latest 4.0.4.0 and both are exhibiting the same behavior.

I'm trying to Center align text in cells but it's just not working. Numbers in cells work fine, strings do not. Following is an example of code that fails to produce the desired ExcelHorizontalAlignment:

var newFile = new FileInfo(@"C:\Temp\sample.xlsx");
using (var package = new ExcelPackage(newFile))
{
    var workSheet = package.Workbook.Worksheets.Add("Content");

    workSheet.Column(1).Width = 50;

    workSheet.Cells["A1"].Value = "This should be left-aligned";
    workSheet.Cells["A1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;


    workSheet.Cells["A2"].Value = "This should be center-aligned";
    workSheet.Cells["A2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;      // <- This  doesn't work.

    workSheet.Cells["A3"].RichText.Add("This should be center-aligned");
    workSheet.Cells["A3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;      // <- This  doesn't work.


    workSheet.Cells["A4"].Value = "This should be right-aligned";
    workSheet.Cells["A4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;       // <- This  doesn't work.

    workSheet.Cells["A5"].RichText.Add("This should be right-aligned");
    workSheet.Cells["A5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;       // <- This  doesn't work.


    //workSheet.Cells["A2:A3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // <- This  doesn't work.
    //workSheet.Cells["A4:A5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // <- This  doesn't work.

    package.Save();
}

It's making me a little crazy. Any ideas why strings cannot be aligned?

Answer 1

It seems as though LibreOffice is partially at fault. If I generate the file with EPPlus, and open it in Calc, the Center and Right alignment is not displayed. If I then open the file in EPPlus, the formatting is General.

If, however, I generate the file using EPPlus and immediately read it in EPPlus, the alignment is as specified.

I do have a solution that works in Calc and Excel. If I create a NamedStyle, and apply it to cells or cell-ranges, everything works as expected.

var newFile = new FileInfo(filePath);
using (var package = new ExcelPackage(newFile))
{
    var workSheet = package.Workbook.Worksheets.Add("Content");

    workSheet.Column(1).Width = 50;

    workSheet.Cells["A1"].Value = "This should be left-aligned";
    workSheet.Cells["A1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

    var centerStyle = package.Workbook.Styles.CreateNamedStyle("Center");
    centerStyle.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

    workSheet.Cells["A2"].Value = "This should be center-aligned";
    workSheet.Cells["A3"].RichText.Add("This should be center-aligned");
    workSheet.Cells["A2:A3"].StyleName = "Center";

    var rightStyle = package.Workbook.Styles.CreateNamedStyle("Right");
    rightStyle.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;

    workSheet.Cells["A4"].Value = "This should be right-aligned";
    workSheet.Cells["A5"].RichText.Add("This should be right-aligned");
    workSheet.Cells["A4:A5"].StyleName = "Right";

    package.Save();
}