Search code examples
javaexcelapache-poipivot-tablepivot-chart

Repaired Records: Table from /xl/tables/table1.xml part (Table)

I am looking for code that will display the totals row in an excel table at the end using apache poi.

To simplify the problem, I implemented sample code to generate a table in an Excel sheet with some dummy data.

The code is working, but when I open the generated Excel workbook, I receive the warning message shown below.

Is there a problem with the code?

import java.awt.Desktop;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTable;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable;

public class CreateTableExample {

    public static void main(String[] args) {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Sheet1");

            // Create headers
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < 3; i++) {
                Cell headerCell = headerRow.createCell(i);
                headerCell.setCellValue("Header " + (i + 1));
            }

            // Create sample data
            for (int i = 1; i <= 10; i++) {
                Row dataRow = sheet.createRow(i);
                for (int j = 0; j < 3; j++) {
                    Cell dataCell = dataRow.createCell(j);
                    dataCell.setCellValue( (j + 1));
                }
            }

            Row totalRow = sheet.createRow(11);
            totalRow.createCell(0).setCellValue("Total");;

            
            // Define the data range for the table
            AreaReference areaReference = new AreaReference("A1:C12", SpreadsheetVersion.EXCEL2007);

            // Create the table
            XSSFTable table = ((XSSFSheet) sheet).createTable(areaReference);
            String tableName = "MyTable";
            table.setName(tableName);
            CTTable ctTable = table.getCTTable();
            ctTable.setDisplayName(tableName);
            ctTable.setId(1);
            ctTable.setTotalsRowShown(true);
            ctTable.setTotalsRowCount(1);
            // Set the table style
            table.getCTTable().addNewTableStyleInfo();
            table.getCTTable().getTableStyleInfo().setName("TableStyleMedium9");

            
            
            String format = String.format("SUBTOTAL(109,%s[%s])",tableName,  "Header 2");
            totalRow.createCell(1).setCellFormula(String.format("SUBTOTAL(109,%s[%s])",tableName,  "Header 2"));
            totalRow.createCell(2).setCellFormula(String.format("SUBTOTAL(109,%s[%s])",tableName,  "Header 3"));
            
            
            
            // Save the workbook
            try (FileOutputStream fileOut = new FileOutputStream("workbook_with_table.xlsx")) {
                workbook.write(fileOut);
            }
            
            Desktop.getDesktop().open(new File("workbook_with_table.xlsx"));

            System.out.println("Excel file with table created successfully.");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

enter image description here

enter image description here

enter image description here

Solution

  • Welcome to the brave world of Microsoft's Office Open XML. For tables one always needs both. Once the settings in table XML and second the settings in the corresponding worksheet.

    In terms of a totals row, that are the totals row label for first table column and the corresponding string cell value in sheet and the totals row functions for the other table columns and the corresponding formulas in sheet.

    ...
        // add totals row to table
        
        Row totalsRow = sheet.createRow(11);
        // set totals row label for table column 0
        table.getCTTable().getTableColumns().getTableColumnList().get(0).setTotalsRowLabel("Totals");
        // set sheet cell value for this            
        totalsRow.createCell(0).setCellValue("Totals");;

        table.getCTTable().setTotalsRowShown(true);
        table.getCTTable().setTotalsRowCount(1);

        //avoid bug in version 5.2.0 to 5.2.3
        //https://stackoverflow.com/questions/52877212/expanding-an-existing-table-in-excel-using-apache-poi/52904452#52904452
        ((XSSFWorkbook)workbook).setCellFormulaValidation(false);

        // set totals row function for table column
        table.getCTTable().getTableColumns().getTableColumnList().get(1).setTotalsRowFunction(
            org.openxmlformats.schemas.spreadsheetml.x2006.main.STTotalsRowFunction.SUM);   
        // set sheet cell formula for this          
        totalsRow.createCell(1).setCellFormula(String.format("SUBTOTAL(109,%s[%s])",tableName,  "Header 2"));
            
        // set totals row function for table column
        table.getCTTable().getTableColumns().getTableColumnList().get(2).setTotalsRowFunction(
            org.openxmlformats.schemas.spreadsheetml.x2006.main.STTotalsRowFunction.SUM);   
        // set sheet cell formula for this          
        totalsRow.createCell(2).setCellFormula(String.format("SUBTOTAL(109,%s[%s])",tableName,  "Header 3"));
...

    Complete example:

    import java.awt.Desktop;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTable;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CreateTableExample {

    public static void main(String[] args) {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Sheet1");

            // Create headers
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < 3; i++) {
                Cell headerCell = headerRow.createCell(i);
                headerCell.setCellValue("Header " + (i + 1));
            }

            // Create sample data
            for (int i = 1; i <= 10; i++) {
                Row dataRow = sheet.createRow(i);
                for (int j = 0; j < 3; j++) {
                    Cell dataCell = dataRow.createCell(j);
                    dataCell.setCellValue( (j + 1));
                }
            }

            // Define the data range for the table
            AreaReference areaReference = new AreaReference("A1:C12", SpreadsheetVersion.EXCEL2007);

            // Create the table
            XSSFTable table = ((XSSFSheet) sheet).createTable(areaReference);
            // Set the table style
            table.getCTTable().addNewTableStyleInfo();
            table.getCTTable().getTableStyleInfo().setName("TableStyleMedium9");
            
            String tableName = "MyTable";
            table.setName(tableName);
            table.getCTTable().setDisplayName(tableName);

            // add totals row to table
            
            Row totalsRow = sheet.createRow(11);
            // set totals row label for table column 0
            table.getCTTable().getTableColumns().getTableColumnList().get(0).setTotalsRowLabel("Totals");
            // set sheet cell value for this            
            totalsRow.createCell(0).setCellValue("Totals");;

            table.getCTTable().setTotalsRowShown(true);
            table.getCTTable().setTotalsRowCount(1);
            
            //avoid bug in version 5.2.0 to 5.2.3
            //https://stackoverflow.com/questions/52877212/expanding-an-existing-table-in-excel-using-apache-poi/52904452#52904452
            ((XSSFWorkbook)workbook).setCellFormulaValidation(false);

            // set totals row function for table column
            table.getCTTable().getTableColumns().getTableColumnList().get(1).setTotalsRowFunction(
                org.openxmlformats.schemas.spreadsheetml.x2006.main.STTotalsRowFunction.SUM);   
            // set sheet cell formula for this          
            totalsRow.createCell(1).setCellFormula(String.format("SUBTOTAL(109,%s[%s])",tableName,  "Header 2"));
                
            // set totals row function for table column
            table.getCTTable().getTableColumns().getTableColumnList().get(2).setTotalsRowFunction(
                org.openxmlformats.schemas.spreadsheetml.x2006.main.STTotalsRowFunction.SUM);   
            // set sheet cell formula for this          
            totalsRow.createCell(2).setCellFormula(String.format("SUBTOTAL(109,%s[%s])",tableName,  "Header 3"));
            
            // Save the workbook
            try (FileOutputStream fileOut = new FileOutputStream("workbook_with_table.xlsx")) {
                workbook.write(fileOut);
            }
            
            Desktop.getDesktop().open(new File("workbook_with_table.xlsx"));

            System.out.println("Excel file with table created successfully.");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}