Bug 58221 - Shifting rows while trying to sort causes XmlValueDisconnectedException
Summary: Shifting rows while trying to sort causes XmlValueDisconnectedException
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.13-dev
Hardware: PC All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on: 59733
  Show dependency tree
Reported: 2015-08-06 21:50 UTC by Indian
Modified: 2020-10-25 21:12 UTC (History)
1 user (show)

Shifting Rows Stacktrace (2.59 KB, text/plain)
2015-08-06 21:50 UTC, Indian

Note You need to log in before you can comment on or make changes to this bug.
Description Indian 2015-08-06 21:50:41 UTC
Created attachment 32977 [details]
Shifting Rows Stacktrace

I was trying to find a way to sort data in Apache poi. I found this solution on Stack but it's not working for it. It seems like the sheet index isn't counting right and it can't find the original row after the shift. I've basically just comparing letters. 

I've attached an text document with the stacktrace.

Here is the code I'm trying to sort the rows.

	 * Sorts (A-Z) rows by String column
	 * @param sheet - sheet to sort
	 * @param column - String column to sort by
	 * @param rowStart - sorting from this row down
	private void sortSheet(Sheet sheet, int column, int rowStart) {
	    boolean sorting = true;
	    int lastRow = sheet.getLastRowNum();
	    while (sorting == true) {
	        sorting = false;
	        for (Row row : sheet) {
	            // skip if this row is before first to sort
	            if (row.getRowNum()<rowStart) continue;
	            // end if this is last row
	            if (lastRow==row.getRowNum()) break;
	            Row row2 = sheet.getRow(row.getRowNum()+1);
	            if (row2 == null) continue;
	            String firstValue = (row.getCell(column) != null) ? row.getCell(column).getStringCellValue() : "";
	            String secondValue = (row2.getCell(column) != null) ? row2.getCell(column).getStringCellValue() : "";
	            //compare cell from current row and next row - and switch if secondValue should be before first
	            if (secondValue.compareToIgnoreCase(firstValue)<0) {                    
	                sheet.shiftRows(row2.getRowNum(), row2.getRowNum(), -1);
	                sheet.shiftRows(row.getRowNum(), row.getRowNum(), 1);
	                sorting = true;

Any suggestions would be greatly appreciate! Not being able to sort is killing me.
Comment 1 Dominik Stadler 2016-01-02 21:33:55 UTC
Can you attach a sample file that goes along with the sorting? Otherwise it may be hard to reproduce/see where the actual problem is.
Comment 2 Javen O'Neal 2016-01-02 22:53:44 UTC
shiftRows is relatively expensive because it has to shift values, styles, conditional formatting, references, merged ranges, formulas, etc. Doing this n*log(n) or nĀ² times is extremely expensive. It might be better to use a sorting algorithm that makes minimal moves [1], or copy the cell values into an array, sort the array and calculate the row offsets, shift each row by the row offsets.

The Stack example below looks like a bubble sort. If you get yoyr example working and discover that sorting speed is an issue, you might be able to get better speed if you had access to POI's internals by adding a sortRows method to POI. This could reduce the amount of recalculating merged ranges, formulas, and references.

[1] Cycle sort, theoretically minimal number of writes https://en.wikipedia.org/wiki/Cycle_sort
Comment 3 Dominik Stadler 2020-10-25 21:12:34 UTC
Might have been fixed by 59733 which fixed a case of XmlValueDisconnectedException when shifting rows. 

As we never received a sample file it is hard to reproduce, so I am closing this one for now as LATER, please reopen with more information if necessary.