How to set formula have table column field in Apache POI

Question

I created an XSSFTable with below example code:

https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateTable.java

One column in my XSSFTable is a formula that referencing to another column in this table.

For example, in XSSFTable TBL column ColumnA, the formula is: =[@[ColumnB]], I can set the formula on each cell in ColumnA via cell.setCellFormula("TBL[[#This Row],[ColumnB]]"), but it will have problem while opened in Excel and Excel has to remove the formula in order to display the worksheet correctly.

This problem only happened in creating blank new XSSFWorkbook, if it is loaded from an existing .xlsx file created by Excel, it is able to modify the formula via cell.setCellFormula() and able to open in Excel correctly.

If there are any sample code can work correctly in this situation?

Answer 1

Main problem with the linked example is that it names all columns equal "Column":

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

So formula parser cannot difference between them.

But the whole logic of filling the table column headers and filling the sheet contents using one loop is not really comprehensible. So here is a more appropriate example:

public class CreateTable {

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

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

        //Create 
        XSSFTable table = sheet.createTable();
        table.setDisplayName("Test");       
        CTTable cttable = table.getCTTable();

        //Style configurations
        CTTableStyleInfo style = cttable.addNewTableStyleInfo();
        style.setName("TableStyleMedium2");
        style.setShowColumnStripes(false);
        style.setShowRowStripes(true);

        //Set which area the table should be placed in
        AreaReference reference = new AreaReference(new CellReference(0, 0), 
                new CellReference(4,2));
        cttable.setRef(reference.formatAsString());
        cttable.setId(1);
        cttable.setName("Test");
        cttable.setTotalsRowCount(1);

        CTTableColumns columns = cttable.addNewTableColumns();
        columns.setCount(3);
        CTTableColumn column;
        XSSFRow row;
        XSSFCell cell;

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

        //Create sheet contents
        for(int i=0; i<5; i++) {//Create 5 rows
            row = sheet.createRow(i);
            for(int j=0; j<3; j++) {//Create 3 cells each row
                cell = row.createCell(j);
                if(i == 0) { //first row is for column headers
                    cell.setCellValue("Column"+j);
                } else if(i<4){ //next rows except last row are data rows, last row is totals row so don't put something in
                    if (j<2) cell.setCellValue((i+1)*(j+1)); //two data columns
                    else cell.setCellFormula("Test[[#This Row],[Column0]]*Test[[#This Row],[Column1]]"); //one formula column
                }
            }
        }

        FileOutputStream fileOut = new FileOutputStream("ooxml-table.xlsx");
        wb.write(fileOut);
        fileOut.close();
        wb.close();
    }
}