使用 Apache POI 在 Excel 中扩展现有表格

Question

我有一个 excel 表格，里面有我想向其中添加数据的公式。

我这样做的动机是 Excel 中的表格可以动态扩展到您添加到其中的数据范围，这意味着公式行会自动跟上数据行的数量。

但是，我很难确定是否可以使用 apache-POI。

我要尝试的一件事（参见下面的代码）是扩展AreaReference表的 以覆盖数据，但是AreaReference(CR,CR2);（如本示例中使用的）和AreaReference(CR,CR2, SpreadsheetVersion.EXCEL2007)（在 apache文档中看到的）都给出“构造函数未定义”。

不知道是什么导致了我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);

任何帮助将不胜感激。

Answer 1

apache poi到目前为止，使用的主要问题是，如果没有Microsoft Office关于Microsoft Office文件存储的详细知识，还不能使用它。有很多东西只准备了一半，并且在新版本中经常出现回归（错误再次发生，但已经解决了）。因此，您的要求：“使用 Apache POI 在 Excel 中扩展现有表格”是不可能的，仅使用apache poi. 必须知道Office Open XML文件*.xlsx只是ZIP可以解压缩的档案。解压后我们找到/xl/tables/table1.xml了表的存储空间。这个XML我们可以分析，并将其与比较XML，其使用创建Excel的GUI。所以我们可以发现由于 的缺点而导致的问题apache poi。与工作表的XMLin 相同/xl/tables/sheet1.xml。我们还需要知道，它apache poi建立在ooxml-schemas. 部分我们需要使用这些类，因为apache poi. 在下面的例子中，我们还需要ooxml-schemas-1.4.jar额外的，因为apache poi's直到现在poi-ooxml-schemas-4.0.0.jar还没有包括在内org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableFormula。不幸的是，没有关于ooxml-schemaspublic 的文档可用。所以我们需要下载源代码并javadoc自己做。以下示例适用于我使用apache poi 4.0.0. 如果在编译或运行时遇到问题，原因可能是apache poi jar在编译时和/或运行时类路径中存在多个不同版本的s。不要混合不同的 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 {&nbsp;static void addRowToTable(XSSFTable table) {&nbsp; &nbsp;int lastTableRow = table.getEndCellReference().getRow();&nbsp; &nbsp;int totalsRowCount = table.getTotalsRowCount();&nbsp; &nbsp;int lastTableDataRow = lastTableRow - totalsRowCount;&nbsp; &nbsp;// we will add one row in table data&nbsp; &nbsp;lastTableRow++;&nbsp; &nbsp;lastTableDataRow++;&nbsp; &nbsp;// new table area plus one row&nbsp; &nbsp;AreaReference newTableArea = new AreaReference(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;table.getStartCellReference(),&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;new CellReference(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; lastTableRow,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; table.getEndCellReference().getCol()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SpreadsheetVersion.EXCEL2007&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; );&nbsp; &nbsp;// new table data area plus one row&nbsp; &nbsp;AreaReference newTableDataArea = new AreaReference(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;table.getStartCellReference(),&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new CellReference(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;lastTableDataRow,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;table.getEndCellReference().getCol()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SpreadsheetVersion.EXCEL2007&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; );&nbsp; &nbsp;XSSFSheet sheet = table.getXSSFSheet();&nbsp; &nbsp;if (totalsRowCount > 0) {&nbsp; &nbsp; //if we have totals rows, shift totals rows down&nbsp; &nbsp; sheet.shiftRows(lastTableDataRow, lastTableRow, 1);&nbsp; &nbsp; // correcting bug that shiftRows does not adjusting references of the cells&nbsp; &nbsp; // if row 3 is shifted down, then reference in the cells remain r="A3", r="B3", ...&nbsp; &nbsp; // they must be adjusted to the new row thoug: r="A4", r="B4", ...&nbsp; &nbsp; // apache poi 3.17 has done this properly but had have other bugs in shiftRows.&nbsp; &nbsp; for (int r = lastTableDataRow; r < lastTableRow + 1; r++) {&nbsp; &nbsp; &nbsp;XSSFRow row = sheet.getRow(r);&nbsp;&nbsp; &nbsp; &nbsp;if (row != null) {&nbsp; &nbsp; &nbsp; long rRef = row.getCTRow().getR();&nbsp; &nbsp; &nbsp; for (Cell cell : row) {&nbsp; &nbsp; &nbsp; &nbsp;String cRef = ((XSSFCell)cell).getCTCell().getR();&nbsp; &nbsp; &nbsp; &nbsp;((XSSFCell)cell).getCTCell().setR(cRef.replaceAll("[0-9]", "") + rRef);&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp;}&nbsp; &nbsp; }&nbsp; &nbsp; // end correcting bug&nbsp; &nbsp;}&nbsp; &nbsp;// if there are CalculatedColumnFormulas do filling them to the new row&nbsp; &nbsp;XSSFRow row = sheet.getRow(lastTableDataRow); if (row == null) row = sheet.createRow(lastTableDataRow);&nbsp; &nbsp;for (CTTableColumn tableCol : table.getCTTable().getTableColumns().getTableColumnList()) {&nbsp; &nbsp; if (tableCol.getCalculatedColumnFormula() != null) {&nbsp; &nbsp; &nbsp;int id = (int)tableCol.getId();&nbsp; &nbsp; &nbsp;String formula = tableCol.getCalculatedColumnFormula().getStringValue();&nbsp; &nbsp; &nbsp;XSSFCell cell = row.getCell(id -1); if (cell == null) cell = row.createCell(id -1);&nbsp; &nbsp; &nbsp;cell.setCellFormula(formula);&nbsp; &nbsp; }&nbsp; &nbsp;}&nbsp; &nbsp;table.setArea(newTableArea);&nbsp; &nbsp;// correcting bug that Autofilter includes possible TotalsRows after setArea new&nbsp; &nbsp;// Autofilter must only contain data area&nbsp; &nbsp;table.getCTTable().getAutoFilter().setRef(newTableDataArea.formatAsString());&nbsp; &nbsp;// end correcting bug&nbsp; &nbsp;table.updateReferences();&nbsp;}&nbsp;public static void main(String[] args) throws Exception {&nbsp; try (Workbook workbook = WorkbookFactory.create(new FileInputStream("SAMPLE.xlsx"));&nbsp; &nbsp; &nbsp; &nbsp;FileOutputStream out = new FileOutputStream("SAMPLE_NEW.xlsx")) {&nbsp; &nbsp;XSSFSheet sheet = ((XSSFWorkbook)workbook).getSheetAt(0);&nbsp; &nbsp;XSSFTable table = sheet.getTables().get(0);&nbsp; &nbsp;addRowToTable(table);&nbsp; &nbsp;workbook.write(out);&nbsp; }&nbsp;&nbsp;}}