使用 Apache POI 在 Excel 中扩展现有表格 [英] Expanding an existing table in Excel using Apache POI

问题描述

我有一个 excel 表格，里面有我想要添加数据的公式.

I've got an table in excel with formulae I would like to add data to.

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

My motivation for this is the fact that tables in excel can dynamically expand to the range of data you add to them, meaning that the formula rows automatically keep up with the amount of data rows.

然而，我很难确定是否可以使用 apache-POI.

I'm however having a hard time finding out if this is possible using apache-POI.

我要尝试的一件事(见下面的代码)是扩展表的 AreaReference 以覆盖数据，但是 AreaReference(CR,CR2); (如 this 示例)和 AreaReference(CR,CR2, SpreadsheetVersion.EXCEL2007)(见 apache docs) 给出构造函数未定义".

One thing I was going to try (see code below) was to expand the AreaReference of the table to cover the data, however both AreaReference(CR,CR2); (as used in this example) and AreaReference(CR,CR2, SpreadsheetVersion.EXCEL2007) (seen in the apache docs) give "constructor is undefined".

不知道是什么导致了构造器错误，因为我确实导入了 org.apache.poi.ss.util.

No idea what is causing that constructor error as I do have org.apache.poi.ss.util imported.

apache 文档中的另一个选项 AreaReference(java.lang.String reference) 让我编译并运行，但给出了NoSuchMethod"错误.

The other option on the apache docs AreaReference(java.lang.String reference) lets me compile and run but instead gives a "NoSuchMethod" error.

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

任何帮助将不胜感激.

推荐答案

到目前为止，使用 apache poi 的主要问题是，如果没有对 Microsoft 的详细了解，还不能使用它Office 等以及关于 Microsoft Office 文件的存储.很多东西只准备了一半，而且在新版本中经常出现回归(已经解决的错误再次出现).

The main problem using apache poi until now is that it is not ready to be used without having detailed knowledge about Microsoft Office as such and about the storage of Microsoft Office files. There are many things only half way ready and there are regressions often in new versions (bugs occur again which were solved already).

因此，您的要求:使用 Apache POI 在 Excel 中扩展现有表格"是不可能的，仅使用 apache poi 是不可能的.必须知道 Office Open XML 文件 *.xlsx 只是可以解压缩的 ZIP 档案.解压后我们找到/xl/tables/table1.xml 用于存储表.这个XML我们可以分析和比较它与使用Excel的GUI创建的XML.这样我们就可以发现apache poi 的缺点所导致的问题.与 /xl/tables/sheet1.xml 中工作表的 XML 相同.

So your requirement: "Expanding an existing table in Excel using Apache POI" is not possible only simply using apache poi. One must know that Office Open XML files *.xlsx are simply ZIP archives which can be unzipped. And after unzipping we find /xl/tables/table1.xml for storage of the table. This XML we can analyzing and comparing it with XML which was created using Excel's GUI. So we can find problems which results from shortcomings of apache poi. Same is with the sheet's XML in /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.

Also we need to know that apache poi builds on the low level classes of ooxml-schemas. Partially we need using those classes because of the halfway readiness of apache poi. In the following example we need ooxml-schemas-1.4.jar additionally because apache poi's poi-ooxml-schemas-4.0.0.jar has not included org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableFormula until now. Unfortunately there is no documentation about ooxml-schemas public available. So we need downloading the sources and doing javadoc our own.

以下示例适用于我使用 apache poi 4.0.0.如果在编译或运行时遇到问题，原因可能是多个不同版本的 apache poi jar 在编译时和/或运行时在类路径中.不要混用不同的 apache poi 版本.另外，如前所述，我的代码需要 所有模式 ooxml 的完整 jar-schemas-1.4.jar.

The following example works for me using apache poi 4.0.0. If you get problems while compiling or running, the reason might be that multiple different versions of apache poi jars are in class path while compile time and/or run time. Do not mix different apache poi versions. Also, as said already, my code needs the full jar of all of the schemas ooxml-schemas-1.4.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);
  } 
 }
}

这篇关于使用 Apache POI 在 Excel 中扩展现有表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！