Search code examples
c#excelepplus

C# EPPlus multiple cell formatting not applying

I am using EPPlus to generate excel file from data table. i have only two rows. i am applying % formatting on first row and $ formatting on second row but my two row has getting same % formatting for first two row which is wrong. i am not being able to capture the reason why this is happening. why second formatting not being applied on second row which is $ formatting.

See this line where i use range to apply formatting.

    ws.Cells["C0:P0"].Style.Numberformat.Format = "#,###,##0.0%;(#,###,##0.0%)";
    ws.Cells["C1:P1"].Style.Numberformat.Format = "$##,##0.0;($##,##0.0)";

in the above code i mention cell range with formatting but my two row getting only first formatting and second formatting not consider...not clear why this is happening?

Sample Code

using (OfficeOpenXml.ExcelPackage obj = new OfficeOpenXml.ExcelPackage(FileLoc))
{
                // creating work sheet object
                OfficeOpenXml.ExcelWorksheet ws = obj.Workbook.Worksheets.Add("Vertical");
                // freezing work sheet columns and rows
                ws.View.FreezePanes(2, 3);
                // exporting data to excel
                ws.Cells["A1"].LoadFromDataTable(selected, true);
                // setting calumns as autofit
                ws.Cells[ws.Dimension.Address].AutoFitColumns();
                //fixing height of column
                ws.Row(1).Height = 16;
                ws.Row(1).Style.Fill.PatternType = ExcelFillStyle.Solid;
                ws.Row(1).Style.Fill.BackgroundColor.SetColor(Color.LightGray);
                
                obj.Save();
                
                ws.Cells["C0:P0"].Style.Numberformat.Format = "#,###,##0.0%;(#,###,##0.0%)";
                ws.Cells["C1:P1"].Style.Numberformat.Format = "$##,##0.0;($##,##0.0)";  
}

screen shot of excel data. see first two line in picture and definitely understand #,###,##0.0%;(#,###,##0.0%) this format is applying on first two row but in my code i have given different format for second records.

please help me to find the wrong things in my code. thanks

enter image description here

Solution

  • Well, there are a couple of errors. First, you're saving before setting the formatting, so it's not being applied.

    Second, Excel addresses are base 1, it doesn't exist "C0" and "P0". Also note that in the first row is the columns titles, so you probably want rows 2 and 3. Try the following:

    ws.Cells["C2:P2"].Style.Numberformat.Format = "#,###,##0.0%;(#,###,##0.0%)";
ws.Cells["C3:P3"].Style.Numberformat.Format = "$##,##0.0;($##,##0.0)";
obj.Save();