java - Expanding an existing table in Excel using Apache POI

Question

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

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.

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

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".

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

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

Any help will be appreciated.

Answer 1

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

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.

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.

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