使用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);(如 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就不可能使用Apache POI扩展Excel中的现有表".必须知道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 public的文档.因此，我们需要下载源代码并自己进行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模式的完整罐子-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屋！