如何在Apache POI中设置公式具有表列字段

1 回答

• 1

  链接示例的主要问题是它将所有列命名为“Column”：

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

  所以公式解析器不能区分它们 .

  但是填充表列 Headers 并使用一个循环填充表单内容的整个逻辑并不是很容易理解 . 所以这是一个更合适的例子：

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

  回复于 2024-04-26T17:45:05+08:00