APACHE POI set formula by column name format

Have small problem when I want to set formula to cell by column name format. Let me show example:

In excel file I can do something like this =[Name]

So value from colmn B is copied to column A

But when I try to do something like this in Apache POI

  cell.setCellFormula("[Name]");

I get exception:

Parse error near char 0 ‘[‘ in specified formula ‘[Name]’. Expected number, string, defined name, or data table”

How can I handle such situation?

Answer

The formula =[Name] is a structured reference to an Excel table. But it is a very short unqualified form. The fully qualified form would be =tableName[[#This Row],[Name]], And that fully qualified form also will be stored and so must be set using apache poi.

Let’s have a complete example:

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 CreateExcelTableUsingStructuredReferences {

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

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

   //data
   String[] tableHeadings = new String[]{"Id", "Name", "Description"};
   String[] tableContent = new String[]{null, "Name1", "Description1"};
   //variables
   String tableName = "Table1";
   int firstRow = 0; //start table in row 1
   int firstCol = 0; //start table in column A
   int rows = 2; //we have to populate headings row and 1 data row
   int cols = 3; //three columns in each row
   
   //prepairing the sheet
   XSSFSheet sheet = workbook.createSheet();
   
   //set sheet content  
   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 {
      localXSSFCell.setCellValue(tableContent[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);
   
   //set table column formula
   dataTable.getCTTable().getTableColumns().getTableColumnList().get(0).addNewCalculatedColumnFormula().setStringValue(
      tableName + "[[#This Row],[Name]]");

   //set the formula in sheet
   XSSFCell formulaCell = sheet.getRow(firstRow+1).getCell(firstCol);
   formulaCell.setCellFormula(tableName + "[[#This Row],[Name]]");
   //following is not necessary up to apache poi 5.1.0, but later versions of apache poi uses formula parser which damages structured table formulas
   formulaCell.getCTCell().getF().setStringValue(tableName + "[[#This Row],[Name]]");
   
   workbook.write(fileout);
  }

 }
}

This works using apache poi 4 or later.

Note, the additional formulaCell.getCTCell().getF().setStringValue(tableName + "[[#This Row],[Name]]"); is only needed using apache poi versions after 5.1.0. Later versions of apache poi uses a formula parser which damages the structured table formula and so it must be reset using this code line.