Parsing ODF files generated by Excel

[wetneb]

Excel seems to generate ODS files in a way which confuses this library (0.9.0-RC1).
LibreOffice can import this ODS file just fine.
made-with-excel.ods.zip

Opening this file with odftoolkit and calling OdfTableRow.getCellCount() on its rows seems to be running in an infinite loop.

[svanteschubert]

The DOM implementation should not try to expand the XML to a table without repeated. ;-)

                <table:table-row table:number-rows-repeated="1048573" table:style-name="ro1">
                    <table:table-cell table:number-columns-repeated="16384"/>
                </table:table-row>

[kcech]

It seems that this issue is currently preventing to open any ODS file from Libre Office 7.3 (stack Ubuntu 22.04).
Is there any workaround? Or maybe could somebody point to the class where we could fix it?

[thadguidry]

I debugged this a bit and it seems that while trying to generate the coverList in OdfTableRow.getCellCount() that if there are billions of empty cells because of table:number-rows-repeated="1048543" times the table:table-cell table:number-columns-repeated="1024" then it just takes a really really long time.

Perhaps there could be a check (or parameter option, or new method getRealRealCellCount()) that discards empty rows and thus skip trying to get the billions of Cell Cover Infos for them?

odftoolkit/odfdom/src/main/java/org/odftoolkit/odfdom/doc/table/OdfTableRow.java

Lines 289 to 295 in 36ef92c

	public int getCellCount() {
	OdfTable table = getTable();
	Set<OdfTableCell> realCells = new HashSet<OdfTableCell>();
	List<CellCoverInfo> coverList =
	table.getCellCoverInfos(0, 0, table.getColumnCount() - 1, table.getRowCount() - 1);
	int rowIndex = getRowIndex();
	for (int i = 0; i < table.getColumnCount(); i++) {

[thadguidry]

It's also interesting that Apache Hop stops checking at the first non-empty row and checks if rows have non-empty cells, etc.
https://github.com/apache/hop/blob/main/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/ods/OdfSheet.java

[svanteschubert]

Any repeated number should not be iterated but be "added"/"embraced" to the internal model.
In the following, the ColumnsRepeatedNumber was added, but the Rows RepeatedNumber is looped. Looks like a bug to me!

odftoolkit/odfdom/src/main/java/org/odftoolkit/odfdom/doc/table/OdfTable.java

Lines 2286 to 2298 in 36ef92c

	if (cell != null) {
	nColSpan = cell.getColumnSpannedNumber();
	nRowSpan = cell.getRowSpannedNumber();
	if ((nColSpan > 1) || (nRowSpan > 1)) {
	for (int k = 0; k < row.getRowsRepeatedNumber(); k++) {
	coverList.add(new CellCoverInfo(j, i + k, nColSpan, nRowSpan));
	}
	}
	j += cell.getColumnsRepeatedNumber();
	} else {
	j++;
	}
	}

I would greatly appreciate it if you could help stop this, Thad! 👍

[thadguidry]

@svanteschubert Sure, but also go ahead and label this issue as "help wanted". It attracts folks willing to dive in and give a hand.

[thadguidry]

Found terrific background information in PR #285 to help tackle this issue.

[driesva]

@thadguidry @svanteschubert

I ran into the same issue for an ODS file saved by Excel.

If I look at the Javadoc of this method:

  /**
   * Return the count of real cells in this row. The cells covered by top cells are not counted.
   *
   * <p>Please note it might not equal to the column count of the owner table, because some of them
   * are the covered cells.
   *
   * @return the cell count
   */
  public int getCellCount() {

I'm wondering, if there are no covered cells, is the cell count equal to the column count?

If so, is a quick fix/workaround not the following?

   // ...
    List<CellCoverInfo> coverList =
       table.getCellCoverInfos(0, 0, table.getColumnCount() - 1, table.getRowCount() - 1);
   // proposed change: return column count if no covered cells
   if (coverList.isEmpty()) {
     return table.getColumnCount();
   }
   // ...

That would mean for the case were Excel adds

<table:table-cell table:number-columns-repeated="16384"/>

not all 16384 have to be iterated to obtain the actual count.

[svanteschubert]

@driesva, I am missing a bit of background information on what problem you are facing.
In general, the repeated attribute is part of the count, but should not be iterated; rather be embraced/overtaken into the output.

Unfortunately, this is not always easy:
For example, in some spreadsheets, the background colour is being emulated by cell colour, and the repetition of rows, columns and cells is high as the maximum supported internal cell model is being used.
Exporting such a spreadsheet to HTML will result in an incredibly large file - but usually the XSL transformation dies at a out-of-memory-exception. If I remember it well, the workaround would be if the colour is attached to the columns.

In the XSL HTML export of LibreOffice, I limited the repetition if there is only background colour and no content (AFAIR).

Does this help?

[driesva]

@svanteschubert

The problem is in the method OdfTableRow::getCellCount:

odftoolkit/odfdom/src/main/java/org/odftoolkit/odfdom/doc/table/OdfTableRow.java

Lines 295 to 298 in ae2cbee

	for (int i = 0; i < table.getColumnCount(); i++) {
	OdfTableCell cell = table.getOwnerCellByPosition(coverList, i, rowIndex);
	realCells.add(cell);
	}

Imagine the following code where you want list cell values:

      OdfTable sheet = // ...
      sheet.getRowList().forEach(row -> {
        for (int cellIdx = 0; cellIdx < row.getCellCount(); cellIdx++) {
          // ...

for this case:

<table:table-cell table:number-columns-repeated="16384"/>

it will obtain all those 16384 cells just to perform the count of cells in the row!