使用 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 {

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

   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(id -1); if (cell == null) cell = row.createCell(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);

  } 

 }

}