使用Apache POI扩展现有的Excel表格

Question

我在Excel中有一张带有公式的表格，我想要添加数据。

我的动机是因为Excel中的表格可以根据添加的数据动态扩展，这意味着公式行会自动跟随数据行的数量。

然而，我很难确定是否可以使用apache-POI实现这一点。

我想尝试的一件事情（请参见下面的代码）是将表格的AreaReference扩展到覆盖数据，但是AreaReference(CR,CR2);（如this示例中所用）和AreaReference(CR,CR2, SpreadsheetVersion.EXCEL2007)（在apache docs中看到）都会出现“构造函数未定义”的错误。

我不知道是什么原因导致了构造函数错误，因为我已经导入了org.apache.poi.ss.util。

在 Apache 文档中，另一个选项 AreaReference(java.lang.String reference) 让我编译和运行，但是却出现了“NoSuchMethod”错误。

        List<XSSFTable> tableList = spreadSheet.getTables();
        CellReference CR = new CellReference(0, 0); 
        CellReference CR2 = new CellReference(5, 2);
        AreaReference my_data_range = new AreaReference(CR,CR2);
        tableList.get(0).setArea(my_data_range);

任何帮助都将不胜感激。

- Zyzyx

---

使用AreaReference构造函数时出现多个问题的原因可能是在编译时和/或运行时类路径中存在多个不同版本的apache poi jar。不要混合不同的apache poi版本。 - Axel Richter

Answer 1

使用apache poi的主要问题是它需要对Microsoft Office及其文件存储有详细了解才能使用。许多功能只是半成品，新版本中经常出现退步（已解决的错误再次出现）。

所以您的需求：“使用Apache POI扩展Excel中的现有表格”只是简单地使用apache poi是不可能实现的。我们必须知道Office Open XML文件*.xlsx只是可以解压缩的ZIP归档文件。解压缩后，我们可以找到用于存储表格的/xl/tables/table1.xml。我们可以分析这个XML并将其与使用Excel的GUI创建的XML进行比较。因此，我们可以发现由于apache poi的缺陷而导致的问题。在/xl/tables/sheet1.xml中也是如此。

此外，我们需要知道 apache poi 是基于 ooxml-schemas 的低级类构建的。部分原因是由于 apache poi 的中途准备不足，我们需要使用这些类。在下面的示例中，我们需要额外使用 ooxml-schemas-1.4.jar，因为 apache poi 的 poi-ooxml-schemas-4.0.0.jar 直到现在才包含了 org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableFormula 。不幸的是，没有关于 ooxml-schemas 的公共文档可供参考。因此，我们需要下载源代码并自己进行 javadoc 说明。

以下示例对我有效，使用的是 apache poi 4.0.0。如果您在编译或运行过程中遇到问题，可能是由于同时在编译时和/或运行时类路径中存在多个不同版本的 apache poi jar。请勿混合使用不同版本的 apache poi。另外，就像之前所说，我的代码需要 ooxml-schemas-1.4.jar 的所有模式的完整 jar 包。

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.SpreadsheetVersion;

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;

class ExcelExpandingTable {

 static void addRowToTable(XSSFTable table) {

   int lastTableRow = table.getEndCellReference().getRow();
   int totalsRowCount = table.getTotalsRowCount();
   int lastTableDataRow = lastTableRow - totalsRowCount;

   // we will add one row in table data
   lastTableRow++;
   lastTableDataRow++;

   // new table area plus one row
   AreaReference newTableArea = new AreaReference(
                                 table.getStartCellReference(), 
                                 new CellReference(
                                  lastTableRow, 
                                  table.getEndCellReference().getCol()
                                 ),
                                 SpreadsheetVersion.EXCEL2007
                                );

   // new table data area plus one row
   AreaReference newTableDataArea = new AreaReference(
                                     table.getStartCellReference(), 
                                      new CellReference(
                                       lastTableDataRow, 
                                       table.getEndCellReference().getCol()
                                      ),
                                      SpreadsheetVersion.EXCEL2007
                                    );
   
   XSSFSheet sheet = table.getXSSFSheet();
   if (totalsRowCount > 0) {
    //if we have totals rows, shift totals rows down
    sheet.shiftRows(lastTableDataRow, lastTableRow, 1);

    // correcting bug that shiftRows does not adjusting references of the cells
    // if row 3 is shifted down, then reference in the cells remain r="A3", r="B3", ...
    // they must be adjusted to the new row thoug: r="A4", r="B4", ...
    // apache poi 3.17 has done this properly but had have other bugs in shiftRows.
    for (int r = lastTableDataRow; r < lastTableRow + 1; r++) {
     XSSFRow row = sheet.getRow(r); 
     if (row != null) {
      long rRef = row.getCTRow().getR();
      for (Cell cell : row) {
       String cRef = ((XSSFCell)cell).getCTCell().getR();
       ((XSSFCell)cell).getCTCell().setR(cRef.replaceAll("[0-9]", "") + rRef);
      }
     }
    }
    // end correcting bug

   }

   // if there are CalculatedColumnFormulas do filling them to the new row
   XSSFRow row = sheet.getRow(lastTableDataRow); if (row == null) row = sheet.createRow(lastTableDataRow);
   int firstTableCol = table.getStartCellReference().getCol();
   for (CTTableColumn tableCol : table.getCTTable().getTableColumns().getTableColumnList()) {
    if (tableCol.getCalculatedColumnFormula() != null) {
     int id = (int)tableCol.getId();
     String formula = tableCol.getCalculatedColumnFormula().getStringValue();
     XSSFCell cell = row.getCell(firstTableCol + id - 1); if (cell == null) cell = row.createCell(firstTableCol + id - 1);
     cell.setCellFormula(formula);
    }
   }


   table.setArea(newTableArea);

   // correcting bug that Autofilter includes possible TotalsRows after setArea new
   // Autofilter must only contain data area
   table.getCTTable().getAutoFilter().setRef(newTableDataArea.formatAsString());
   // end correcting bug

   table.updateReferences();

 }

 public static void main(String[] args) throws Exception {
  try (Workbook workbook = WorkbookFactory.create(new FileInputStream("SAMPLE.xlsx"));
       FileOutputStream out = new FileOutputStream("SAMPLE_NEW.xlsx")) {
   
   XSSFSheet sheet = ((XSSFWorkbook)workbook).getSheetAt(0);
   XSSFTable table = sheet.getTables().get(0);

   addRowToTable(table);

   workbook.write(out);
  } 
 }
}

---

上面是2018年的情况，现在已经是2022年了。

使用当前的apache poi 5.2.2版本，代码可能如下所示：

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.SpreadsheetVersion;

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;

class ExcelExpandingTable {

 static XSSFRow addRowToTable(XSSFTable table) {

   int lastTableRow = table.getEndCellReference().getRow();
   int totalsRowCount = table.getTotalsRowCount();
   int lastTableDataRow = lastTableRow - totalsRowCount;
   int firstTableCol = table.getStartCellReference().getCol();

   // we will add one row in table data
   lastTableRow++;
   lastTableDataRow++;

   // new table area plus one row
   AreaReference newTableArea = new AreaReference(
                                 table.getStartCellReference(), 
                                 new CellReference(
                                  lastTableRow, 
                                  table.getEndCellReference().getCol()
                                 ),
                                 SpreadsheetVersion.EXCEL2007
                                );

   XSSFSheet sheet = table.getXSSFSheet();
   if (totalsRowCount > 0) {
    //if we have totals rows, shift totals rows down
    sheet.shiftRows(lastTableDataRow, lastTableRow, 1);
    //correct all sheet table-reference-formulas which probably got damaged after shift rows
    for (CTTableColumn tableCol : table.getCTTable().getTableColumns().getTableColumnList()) {
     if (tableCol.getCalculatedColumnFormula() != null) {
      int id = (int)tableCol.getId();
      String formula = tableCol.getCalculatedColumnFormula().getStringValue();
      int rFirst = table.getStartCellReference().getRow() + table.getHeaderRowCount();
      int rLast = table.getEndCellReference().getRow() - table.getTotalsRowCount();
      int c = table.getStartCellReference().getCol() + id - 1;
      sheet.getWorkbook().setCellFormulaValidation(false);
      for (int r = rFirst; r <= rLast; r++) {
       XSSFRow row = sheet.getRow(r); if (row == null) row = sheet.createRow(r);
       XSSFCell cell = row.getCell(c); if (cell == null) cell = row.createCell(c);
       cell.setCellFormula(formula);
      }
     }
    }
   }
   
   // if there are CalculatedColumnFormulas do filling them to the new row
   XSSFRow row = sheet.getRow(lastTableDataRow); if (row == null) row = sheet.createRow(lastTableDataRow);
   for (CTTableColumn tableCol : table.getCTTable().getTableColumns().getTableColumnList()) {
    if (tableCol.getCalculatedColumnFormula() != null) {
     int id = (int)tableCol.getId();
     String formula = tableCol.getCalculatedColumnFormula().getStringValue();
     XSSFCell cell = row.getCell(firstTableCol + id - 1); if (cell == null) cell = row.createCell(firstTableCol + id - 1);
     cell.getSheet().getWorkbook().setCellFormulaValidation(false); // see https://bz.apache.org/bugzilla/show_bug.cgi?id=66039
     cell.setCellFormula(formula);
    }
   }
   
   // copy cell styles to the new row from the row above
   row = sheet.getRow(lastTableDataRow); if (row == null) row = sheet.createRow(lastTableDataRow);
   XSSFRow rowAbove = sheet.getRow(lastTableDataRow - 1); if (row == null) row = sheet.createRow(lastTableDataRow - 1);
   for (CTTableColumn tableCol : table.getCTTable().getTableColumns().getTableColumnList()) {
    int id = (int)tableCol.getId();
    XSSFCell cellAbove = rowAbove.getCell(firstTableCol + id - 1); 
    if (cellAbove != null) {
     XSSFCellStyle styleAbove = cellAbove.getCellStyle();
     XSSFCell cell = row.getCell(firstTableCol + id - 1); if (cell == null) cell = row.createCell(firstTableCol + id - 1);
     cell.setCellStyle(styleAbove);
    }
   }

   // set new table area
   table.setArea(newTableArea);

   // update table references
   table.updateReferences();
      
   return sheet.getRow(lastTableDataRow);

 }

 public static void main(String[] args) throws Exception {
  try (Workbook workbook = WorkbookFactory.create(new FileInputStream("SAMPLE.xlsx"));
       FileOutputStream out = new FileOutputStream("SAMPLE_NEW.xlsx")) {
           
   XSSFSheet sheet = ((XSSFWorkbook)workbook).getSheetAt(0);
   XSSFTable table = sheet.getTables().get(0);

   XSSFRow row = addRowToTable(table);

   workbook.write(out);
  } 
 }
}

根据常见问题解答，此代码现在需要使用poi-ooxml-full-5.2.2.jar而非ooxml-schemas-1.4.jar才能运行apache poi 5.2.2。一些错误已经得到修复，但是当使用包含表格引用的公式时，使用XSSFCell.setCellFormula会出现一个新错误。但是，使用XSSFWorkbook.setCellFormulaValidation(false)可以避免这种情况。

- Axel Richter