Apache POI set cell Format That include the result of Table Total Row

Question

it is possible to set a cell formula on a row table that is include total row ?

For Example : i have a total row Like bellow :

Col[B] Has a SUBTOTAL And Col[C] Also Has a SUBTOTAL

i need Col[D] to be The Totals of Col[B] + Totals of Col[C] Or / Them !

i tried this code :

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", "Heading4"};
            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 = 4; //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++) {
                switch (c) {
                    case 0:
                        dataTable.getCTTable().getTableColumns().getTableColumnList().get(c).setTotalsRowLabel("Totals: ");
                        totalsRow.getCell(tableArea.getFirstCell().getCol() + c).setCellValue("Totals: ");
                        break;
                    case 1:
                        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] + "])");
                        break;
                    case 2:
                        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] + "])");
                        break;
                    case 3:
                        /// Formila on a totil row ??
                        totalsRow.getCell(tableArea.getFirstCell().getCol() + c).setCellFormula("Table1[[#Totals],[Heading2]]+Table1[[#Totals],[Heading3]]");
                        break;
                    default:
                        break;
                }
            }
            //this shows the totals row
            dataTable.getCTTable().setTotalsRowCount(1);

            workbook.write(fileout);
        }

    }
}

but it is not working and it ends up to automatic remove the total row !

Also i tried another way that to set the cell formula out of the loop but it ends to the same error !

any help ?

i think we need to use setFuncshin instead of setFormila but i cannot find setFuncshin ! — Adeeb Mark, Aug 14 '21 at 13:00

score 3 · Accepted Answer · answered Aug 14 '21 at 13:10

Do it using Excel's GUI. Then save the *.xlsx file. Then unzip that *.xlsx file and have a look at /xl/tables/table1.xml. There you will find:

...
<tableColumn name="Heading4" id="4" totalsRowFunction="custom">
 <totalsRowFormula>Table1[[#Totals],[Heading2]]+Table1[[#Totals],[Heading3]]</totalsRowFormula>
...

So the fourth table column needs a totalsRowFunction of type custom and a totalsRowFormula which stores that custom formula.

But the sheet also needs that formula in the appropriate cell.

So translated into Java code:

...
dataTable.getCTTable().getTableColumns().getTableColumnList().get(c).setTotalsRowFunction(
 org.openxmlformats.schemas.spreadsheetml.x2006.main.STTotalsRowFunction.CUSTOM); 
dataTable.getCTTable().getTableColumns().getTableColumnList().get(c).addNewTotalsRowFormula().setStringValue(
 tableName+"[[#Totals],["+tableHeadings[c-2]+"]]+"+tableName+"[[#Totals],["+tableHeadings[c-1]+"]]");
totalsRow.getCell(tableArea.getFirstCell().getCol()+c).setCellFormula(
 tableName+"[[#Totals],["+tableHeadings[c-2]+"]]+"+tableName+"[[#Totals],["+tableHeadings[c-1]+"]]");
...

I know I shouldn't write this in a comment but there are no private messages but I have to say That Not even a single misspelled and unintelligible letter ! You are a genius thank you — Adeeb Mark, Aug 14 '21 at 17:37

Apache POI set cell Format That include the result of Table Total Row

1 Answers1