Apply Formatting to TotalsRow using EPPlus

Question

Using EPPlus I can create the XSLX file and I can apply formatting to the data, but if I use a TotalsRow function, like SUM, then the formatting is not applied.

Does anyone know how to apply that formatting?

Update:

To load the data into a sheet and get the table I do the following (dtMain being a DataTable):

FileInfo newFile = new FileInfo(@"C:\Temp.xslx");

using (ExcelPackage package = new ExcelPackage(newFile))
{
    //Create the Worksheet
    var sheet = package.Workbook.Worksheets.Add("Sheet1");
    //Read the table into a sheet
    var range = sheet.Cells["A1"].LoadFromDataTable(dtMain, true);

    sheet.Tables.Add(range, "data");
    //Now format the table...
    var tbl = sheet.Tables[0];
    tbl.ShowTotal = true;

    //create a custom style
    string stylename = "StyleName";
    var style = package.Workbook.Styles.CreateNamedStyle(stylename);

    tbl.Columns[SomeName].TotalsRowFunction = RowFunctions.Sum;

    style.Style.Numberformat.Format = "#,###.00";

    //assign the style to the column
    tbl.Columns[SomeName].DataCellStyleName = stylename;
}

range.AutoFitColumns();

// save our new workbook and we are done!
package.Save();

Answer 1

When you say "TotalsRow" do you mean a SUM column at the end of a row? If so, you might be mixing up terms since the TotalsRow refers to a row at the BOTTOM of the table. In order to show it you have to set ShowTotal to true as well.

If you actually want to sum up the columns into the last column you should apply a formula.

See if this helps:

[TestMethod]
public void TotalRows_Format_Test()
{
    //Throw in some data
    const string SomeName = "Totals";
    var dtMain = new DataTable("tblData");
    dtMain.Columns.Add(new DataColumn("Col1", typeof(int)));
    dtMain.Columns.Add(new DataColumn("Col2", typeof(int)));
    dtMain.Columns.Add(new DataColumn("Col3", typeof(int)));
    dtMain.Columns.Add(new DataColumn(SomeName, typeof(int)));

    for (var i = 0; i < 20; i++)
    {
        var row = dtMain.NewRow();
        row["Col1"] = i;
        row["Col2"] = i * 10;
        row["Col3"] = i * 100;
        dtMain.Rows.Add(row);
    }

    FileInfo newFile = new FileInfo(@"C:\Temp\Temp.xlsx");
    if (newFile.Exists)
        newFile.Delete();

    using (ExcelPackage package = new ExcelPackage(newFile))
    {
        //Create the Worksheet
        var sheet = package.Workbook.Worksheets.Add("Sheet1");
        //Read the table into a sheet
        var range = sheet.Cells["A1"].LoadFromDataTable(dtMain, true);

        sheet.Tables.Add(range, "data");
        //Now format the table...
        var tbl = sheet.Tables[0];

        //create a custom style
        string stylename = "StyleName";
        var style = package.Workbook.Styles.CreateNamedStyle(stylename);

        //Add formula for row total in COLUMN
        for (var i = 2; i <= dtMain.Rows.Count + 1; i++)
            sheet.Cells[i, 4].Formula = String.Format("SUM(A{0}:C{0})", i);

        //The totals row at the BOTTOM of the table
        tbl.Columns[SomeName].TotalsRowFunction = RowFunctions.Sum;
        tbl.ShowTotal = true;

        style.Style.Numberformat.Format = "#,###.00";

        //assign the style to the column
        tbl.Columns[SomeName].DataCellStyleName = stylename;

    range.AutoFitColumns();

    // save our new workbook and we are done!
    package.Save();
    }
}

---

UPDATE (in response to comments)

I see what you mean now. How about this:

tbl.TotalsRowCellStyle = stylename;

Answer 2

Add this to the code: sheet.Cells[sheet.Dimension.End.Row, colcount].Style.Numberformat.Format = c.Format;

Full version:

FileInfo newFile = new FileInfo(@"C:\Temp.xslx");

using (ExcelPackage package = new ExcelPackage(newFile))
{
    //Create the Worksheet
    var sheet = package.Workbook.Worksheets.Add("Sheet1");
    //Read the table into a sheet
    var range = sheet.Cells["A1"].LoadFromDataTable(dtMain, true);

    sheet.Tables.Add(range, "data");
    //Now format the table...
    var tbl = sheet.Tables[0];
    tbl.ShowTotal = true;

    //create a custom style
    string stylename = "StyleName";
    var style = package.Workbook.Styles.CreateNamedStyle(stylename);

    tbl.Columns[SomeName].TotalsRowFunction = RowFunctions.Sum;

    style.Style.Numberformat.Format = "#,###.00";
    //apply style to totals row
    sheet.Cells[sheet.Dimension.End.Row, colcount].Style.Numberformat.Format = c.Format;

    //assign the style to the column
    tbl.Columns[SomeName].DataCellStyleName = stylename;
}

range.AutoFitColumns();

// save our new workbook and we are done!
package.Save();