Table totalsRowFunction not show and excel show error when open

[goyometeojorito]

When create a table.

var table = ((XSSFSheet)sheet).CreateTable();

... then add the totals row (The column 1 has numbers):

table.GetCTTable().tableColumns.tableColumn[0].totalsRowLabel = "Total: ":
table.GetCTTable().tableColumns.tableColumn[1].totalsRowFunction = NPOI.OpenXmlFormats.Spreadsheet.ST_TotalsRowFunction.sum;			
table.GetCTTable().totalsRowCount = 1; 

the totals row don't show and Excel show a error opening the file. if remove the line table.GetCTTable().totalsRowCount = 1; no error show, but the totals row never show. (if in excel when select the table click in "show totals" , the totals show ok. But not show automatically.

[goyometeojorito]

Attach the generated file (Excel show error on open) and the totals row don't show. Viajes 20221227 191658.xlsx

[tonyqus]

which version of NPOI are you using?

[Bykiev]

I've researched the issue and using tables in POI is not an easy task. When using total rows functions you should manually set cell formula as SUBTOTAL

here is a full sample:

using (var wb = new XSSFWorkbook())
{
    var sheet = (XSSFSheet)wb.CreateSheet("Sheet1");

    var row1 = sheet.CreateRow(0);
    row1.CreateCell(0).SetCellValue("a");
    row1.CreateCell(1).SetCellValue("b");

    var row2 = sheet.CreateRow(1);
    row2.CreateCell(0).SetCellValue(1);
    row2.CreateCell(1).SetCellValue(2);

    var row3 = sheet.CreateRow(2);
    row3.CreateCell(0);
    row3.CreateCell(1);

    CellReference topLeft = new CellReference(sheet.GetRow(0).GetCell(0));
    CellReference bottomRight = new CellReference(sheet.GetRow(2).GetCell(1));

    var table = sheet.CreateTable();
    table.SetCellReferences(new AreaReference(topLeft, bottomRight));
    table.Name = "Test";
    table.DisplayName = "Test";

    var ctTable = table.GetCTTable();
    ctTable.totalsRowShown = true;
    ctTable.totalsRowCount = 1;
    ctTable.id = 1;

    var column = ctTable.AddNewTableColumns();
    column.tableColumn = new List<NPOI.OpenXmlFormats.Spreadsheet.CT_TableColumn>();
    ctTable.tableColumns.count = 2;

    var clmn1 = ctTable.tableColumns.InsertNewTableColumn(0);
    clmn1.id = 1;
    clmn1.name = "a";
    clmn1.totalsRowLabel = "Total: ";

    var clmn2 = ctTable.tableColumns.InsertNewTableColumn(1);
    clmn2.id = 2;
    clmn2.name = "b";
    clmn2.totalsRowFunction = NPOI.OpenXmlFormats.Spreadsheet.ST_TotalsRowFunction.sum;

    var totalsRow = sheet.GetRow(table.EndRowIndex);
    var totalsCell = totalsRow.GetCell(1);
    totalsCell.SetCellFormula($"SUBTOTAL(109,{ctTable.name}[{clmn2.name}])");

    using (var stream = new MemoryStream())
    {
        wb.Write(stream);

        File.WriteAllBytes("test2.xlsx", stream.ToArray());
    }
}