问APACHE按列名格式设置公式

EN

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);
  }

 }
}