[Bug 62711] New: Calling shiftRows corrupts file in POI 4.0

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 62711] New: Calling shiftRows corrupts file in POI 4.0

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=62711

            Bug ID: 62711
           Summary: Calling shiftRows corrupts file in POI 4.0
           Product: POI
           Version: 4.0.0-FINAL
          Hardware: PC
            Status: NEW
          Severity: normal
          Priority: P2
         Component: XSSF
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

Hi

This code produces a corrupt XLSX with POI 4.0 (works fine in 3.17):

ByteArrayOutputStream os = new ByteArrayOutputStream();
try (Workbook workbook = new XSSFWorkbook()) {
   Sheet worksheet =
workbook.createSheet(WorkbookUtil.createSafeSheetName("Export"));
   for (int rowNum = 0; rowNum < 900; rowNum++) {
      Row row = worksheet.createRow(rowNum);
      int colNum = 0;
      for (Object cellValue : Arrays.asList(new Object(),0,0,0,0,0,0,0,0)) {
         row.createCell(colNum++);
      }
   }
   worksheet.shiftRows(0, worksheet.getLastRowNum(), 3);
   workbook.write(os);
}

If I leave out the shiftRows, everything works fine.
It doesn't matter if I write values into the cells or not, the result is the
same.

running Java 8, POI 4.0, Windows 10, Excel 2016

Error when opening in Excel:
Removed Records: Cell information from /xl/worksheets/sheet1.xml part

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [Bug 62711] New: Calling shiftRows corrupts file in POI 4.0

Tony Falabella
This is happening to me as well.  The shiftRows function is not working
properly.  The row # seems to update properly but the cells within the row
do not have the same row # as the row they reside in so the rows get
corrupted.

In the example below I inserted 1 row (+1) - notice the row go updated but
not the cells within the row (A29 instead of A30).

       <row r="30" spans="1:17" ht="16.5" customHeight="1"
x14ac:dyDescent="0.2">
            <c r="A29" s="1" t="s">
                <v>4</v>
            </c><c r="B29" s="1" t="s">
            <v>5</v>
        </c>
        </row>

For me this makes POI 4.0 unusable for my purposes.  I'll see if I can come
up with a temp workaround to use 3.x methods for this function but ideally
maybe 4.0.2 can address properly.



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-Dev-f2312866.html

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 62711] Calling shiftRows corrupts file in POI 4.0

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=62711

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|                            |All

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 62711] Calling shiftRows corrupts file in POI 4.0

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=62711

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Severity|normal                      |regression

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 62711] Calling shiftRows corrupts file in POI 4.0

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=62711

Wolfgang Fahl <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #1 from Wolfgang Fahl <[hidden email]> ---
see my comment to https://stackoverflow.com/a/3554129/1497139

I tried to remove the superflous rows in sheet 0 of

https://www.bundesnetzagentur.de/SharedDocs/Downloads/DE/Sachgebiete/Energie/Unternehmen_Institutionen/HandelundVertrieb/Ladesaeulen/Ladesaeulenkarte_Datenbankauszug20.xlsx?__blob=publicationFile&v=2

but failed to save the result afterwards

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 62711] Calling shiftRows corrupts file in POI 4.0

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=62711

--- Comment #2 from Wolfgang Fahl <[hidden email]> ---
while at it id love to see a proper removeRow function as outline in the
stackoverflow answer. 50.000 people have viewed that question already so I
assume there is quite some need.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 62711] Calling shiftRows corrupts file in POI 4.0

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=62711

--- Comment #3 from [hidden email] ---
I can confirm this bug. Excel started complaining about generated files after
we upgraded to 4.0.

I've been digging to see what was wrong with the file. For Norman's example, in
xl/worksheets/sheet1.xml you will find :
    <row r="4">
      <c r="A1"/>
      <c r="B1"/>
      <c r="C1"/>
      <c r="D1"/>
      <c r="E1"/>
      <c r="F1"/>
      <c r="G1"/>
      <c r="H1"/>
      <c r="I1"/>
    </row>

Which is obviously wrong.

This looks related to this other corruption bug connected to shiftRows :
https://bz.apache.org/bugzilla/show_bug.cgi?id=57423

Although I do also confirm that this was working fine in 3.17 whereas this
other bug is way older.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 62711] Calling shiftRows corrupts file in POI 4.0

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=62711

--- Comment #4 from jesper Jørgensen <[hidden email]> ---
It seem that in XSSFRow:

    protected void shift(int n) {
        int rownum = getRowNum() + n;
        String msg = "Row[rownum=" + getRowNum() + "] contains cell(s) included
in a multi-cell array formula. " +
                "You cannot change part of an array.";
        for(Cell c : this){
            ((XSSFCell)c).updateCellReferencesForShifting(msg);
          }
        setRowNum(rownum);
    }

the setRowNum(rownum) is called after the updateCellReferencesForShifting and
this causes the cells to have a reference to the old row number and this is
what Excel is complaining about. This means that the row might have a row
number 5 but the cells could have a cell reference that points to A1. Whether
this is the entire issue I cannot say, but moving the setRowNum call up before
the for loop will create a document the Excel can read and which have the rows
shifted correctly.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 62711] Calling shiftRows corrupts file in POI 4.0

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=62711

Mark Stewart <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 62711] Calling shiftRows corrupts file in POI 4.0

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=62711

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |RESOLVED
         Depends on|                            |57423, 63463
         Resolution|---                         |FIXED

--- Comment #5 from Dominik Stadler <[hidden email]> ---
Cannot reproduce this any longer with latest trunk, seems to have been fixed
via r1860384


Referenced Bugs:

https://bz.apache.org/bugzilla/show_bug.cgi?id=57423
[Bug 57423] shiftRows() produces a corrupted xlsx file
https://bz.apache.org/bugzilla/show_bug.cgi?id=63463
[Bug 63463] Corrupt excel file using POI api ver 4.1 with shiftRows()
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 62711] Calling shiftRows corrupts file in POI 4.0

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=62711
Bug 62711 depends on bug 57423, which changed state.

Bug 57423 Summary: shiftRows() produces a corrupted xlsx file
https://bz.apache.org/bugzilla/show_bug.cgi?id=57423

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |RESOLVED
         Resolution|---                         |FIXED

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 62711] Calling shiftRows corrupts file in POI 4.0

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=62711
Bug 62711 depends on bug 57423, which changed state.

Bug 57423 Summary: shiftRows() produces a corrupted xlsx file
https://bz.apache.org/bugzilla/show_bug.cgi?id=57423

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|RESOLVED                    |REOPENED
         Resolution|FIXED                       |---

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]