c# - 使用 EPPlus 将格式应用于 TotalsRow

使用 EPPlus 我可以创建 XSLX 文件，我可以对数据应用格式，但如果我使用 TotalsRow 函数，如 SUM，则不会应用格式。

有谁知道如何应用该格式？

更新:

要将数据加载到工作表中并获取表格，我执行以下操作(dtMain 是 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();

最佳答案

当您说“TotalsRow”时，您的意思是一行末尾的 SUM 列吗？如果是这样，您可能混淆了术语，因为 TotalsRow 指的是表底部的一行。为了显示它，您还必须将 ShowTotal 设置为 true。

如果您确实想将列汇总到最后一列，则应应用公式。

看看这是否有帮助:

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

更新(回应评论)

我明白你的意思了。这个怎么样:

tbl.TotalsRowCellStyle = stylename;

关于c# - 使用 EPPlus 将格式应用于 TotalsRow，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/29208459/