Apache POI Format Table Not showing Total Row

user_15049903 0
Viewed 288

i have a class to create formatted table using the code below :

public class formatAsTable {

    public static void main(String[] args)
            throws FileNotFoundException, IOException {

        Workbook wb = new XSSFWorkbook();
        XSSFSheet sheet = (XSSFSheet) wb.createSheet();


        XSSFTable my_table = sheet.createTable();


        CTTable cttable = my_table.getCTTable();

        /* Let us define the required Style for the table */
        CTTableStyleInfo table_style = cttable.addNewTableStyleInfo();
        table_style.setName("TableStyleMedium9");


        table_style.setShowColumnStripes(false); //showColumnStripes=0
        table_style.setShowRowStripes(true); //showRowStripes=1


        AreaReference my_data_range = wb.getCreationHelper().createAreaReference(new CellReference(0, 0), new CellReference(4, 2));

        cttable.setRef(my_data_range.formatAsString());
        cttable.setDisplayName("MYTABLE");

        cttable.addNewAutoFilter();

        cttable.setName("Test");
        cttable.setId(1L);

        CTTableColumns columns = cttable.addNewTableColumns();
        columns.setCount(3L); //define number of columns

        for (int i = 0; i < 3; i++) {
            CTTableColumn column = columns.addNewTableColumn();
            column.setName("Column" + i);
            column.setId(i + 1);
        }

        /* Add And Show TotelRow */
        cttable.setTotalsRowShown(true);

        for (int x = 0; x < 3; x++) {
            cttable.getTableColumns().getTableColumnArray(x).setId(x + 1);

            switch (x) {
                case 0 ->
                    cttable.getTableColumns().getTableColumnArray(x).setTotalsRowLabel("Totales: ");
                default ->
                    cttable.getTableColumns().getTableColumnArray(x).setTotalsRowFunction(org.openxmlformats.schemas.spreadsheetml.x2006.main.STTotalsRowFunction.SUM);
            }
        }

        for (int i = 0; i <= 4; i++) //we have to populate 4 rows
        {
            XSSFRow row = sheet.createRow(i);
            for (int j = 0; j < 3; j++) //Three columns in each row
            {
                XSSFCell localXSSFCell = row.createCell(j);
                if (i == 0) {
                    localXSSFCell.setCellValue("Heading" + j);
                } else {
                    localXSSFCell.setCellValue(i + j);
                }
            }
        }

        FileOutputStream fileOut = new FileOutputStream("Excel_Format_As_Table.xlsx");
        wb.write(fileOut);
        fileOut.close();
    }
}

it working fine but even if i set cttable.setTotalsRowShown(true); it is not showing the total row !

And i Must open the file and Chick on Total Row in the table Design to make is visible :

enter image description here

I changed the name and content and tried to add different formats, but no result

xssf apache-poi xml
1 Answers

Main problem is that it needs to be set CTTable.setTotalsRowCount(1) to show one totals row. Your used CTTable.setTotalsRowShown(true) has other meaning.

According the ECMA-376 Office Open XML file formats attribute totalsRowShown means:

A Boolean indicating whether the totals row has ever been shown in the past for this table. True if the totals row has been shown, false otherwise.

But attribute totalsRowCount means:

An integer representing the number of totals rows that shall be shown at the bottom of the table.

0 means that the totals row is not shown. It is up to the spreadsheet application to determine if numbers greater than 1 are allowed. Unless the spreadsheet application has a feature where their might ever be more than one totals row, this number should not be higher than 1.

But in Excel values and settings for tables always must be synchronised in table settings and the sheet where the table is present. Else it will not work and even might lead to corrupt workbooks. So, as well as for the headers row,, the sheet also needs to have the totals row. And values and formulas in that totals row in sheet must correspond to the table settings.

Following complete example shows this all. It uses high level classes of apache poi, when possible. It is tested and works using apache poi 5.0.0 as well as with apache poi 4.1.2. Lower versions are not supported as those had bugs in table creating.

import java.io.FileOutputStream;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;

class CreateExcelTable {

 public static void main(String[] args) throws Exception {

  try (XSSFWorkbook workbook = new XSSFWorkbook();
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   //prepairing the sheet
   XSSFSheet sheet = workbook.createSheet();
   
   String[] tableHeadings = new String[]{"Heading1", "Heading2", "Heading3"};
   String tableName = "Table1";
   int firstRow = 0; //start table in row 1
   int firstCol = 0; //start table in column A
   int rows = 6; //we have to populate headings row, 4 data rows and 1 totals row
   int cols = 3; //three columns in each row
  
   for (int r = 0; r < rows; r++) { 
    XSSFRow row = sheet.createRow(firstRow+r);
    for (int c = 0; c < cols; c++) { 
     XSSFCell localXSSFCell = row.createCell(firstCol+c);
     if (r == 0) {
      localXSSFCell.setCellValue(tableHeadings[c]);
     } else if (r == 5) {
      //totals row content will be set later
     } else {
      localXSSFCell.setCellValue(r + c);
     }
    }
   }
   
   //create the table
   CellReference topLeft = new CellReference(sheet.getRow(firstRow).getCell(firstCol));
   CellReference bottomRight = new CellReference(sheet.getRow(firstRow+rows-1).getCell(firstCol+cols-1));
   AreaReference tableArea = workbook.getCreationHelper().createAreaReference(topLeft, bottomRight);
   XSSFTable dataTable = sheet.createTable(tableArea);
   dataTable.setName(tableName);
   dataTable.setDisplayName(tableName);

   //this styles the table as Excel would do per default
   dataTable.getCTTable().addNewTableStyleInfo();
   XSSFTableStyleInfo style = (XSSFTableStyleInfo)dataTable.getStyle();
   style.setName("TableStyleMedium9");
   style.setShowColumnStripes(false);
   style.setShowRowStripes(true);
   style.setFirstColumn(false);
   style.setLastColumn(false);

   //this sets auto filters
   dataTable.getCTTable().addNewAutoFilter().setRef(tableArea.formatAsString());
   
   //this sets totals properties to table and totals formulas to sheet
   XSSFRow totalsRow = dataTable.getXSSFSheet().getRow(tableArea.getLastCell().getRow());
   for (int c = 0; c < dataTable.getCTTable().getTableColumns().getTableColumnList().size(); c++) {
    if (c == 0) {
     dataTable.getCTTable().getTableColumns().getTableColumnList().get(c).setTotalsRowLabel("Totals: ");
     totalsRow.getCell(tableArea.getFirstCell().getCol()+c).setCellValue("Totals: ");
   } else {
     dataTable.getCTTable().getTableColumns().getTableColumnList().get(c).setTotalsRowFunction(org.openxmlformats.schemas.spreadsheetml.x2006.main.STTotalsRowFunction.SUM); 
     totalsRow.getCell(tableArea.getFirstCell().getCol()+c).setCellFormula("SUBTOTAL(109," + tableName + "[" + tableHeadings[c] + "])");
    }
   }
   //this shows the totals row
   dataTable.getCTTable().setTotalsRowCount(1);

   workbook.write(fileout);
  }

 }
}

The usage of all org.openxmlformats.schemas.spreadsheetml.x2006.main.* classes needs ooxml-schemas-1.4.jar for apache poi 4.1.2 or poi-ooxml-full-5.0.0.jar for apache poi 5.0.0. The light versions of ooxml doesn't contain all classes. See https://poi.apache.org/help/faq.html#faq-N10025

Using current apache poi 5.2.2 there is a bug while XSSFCell.setCellFormula. The formula evaluator damages the table-structured-references in formulas. So, one need set XSSFWorkbook.setCellFormulaValidation(false) before XSSFCell.setCellFormula.

user_39154310
Related