search

mbleron / ExcelGen

ExcelGen is an Oracle PL/SQL utility to create MS Excel files (.xlsx, .xlsb)
MIT License
43 stars 9 forks source link

bug putCell relative positioning #35

Closed lee-lindley closed 1 year ago

lee-lindley commented 1 year ago

Using putStringCell with relative positioning can cause Excel to give error "We found a problem with some content in 'blahblah.xlsx'. Do you want us to try to recover as much as we can?...". Selecting Yes causes it to throw out the cell content that was added with putStringCell. The log of repairs says "Removed Records: Cell information from /xl/worksheets/sheet1.xml part". Happens with XLSB also.

Took several hours to isolate this into a simple, reproducible test case. There must be something subtle going on because adding more elements to the sheet, as you had in the sample named relative-positioning.sql, makes the problem go away. Using absolute positioning, the problem does not happen. Found in a much more complex program. Workaround for me is to use absolute positioning.

relative-positioning-bug.zip

mbleron commented 1 year ago

Thanks for reporting this one. I really need to improve my test plans.

It happens when the sheet has a single table, at least one individual cell and no row properties defined. This is due to an incomplete condition right here : https://github.com/mbleron/ExcelGen/blob/86c15245f03cff386ea27d9d60e615aa8ff890f9/plsql/ExcelGen.pkb#L3404 which should be : sd.streamable := ( sd.tableList.count = 1 and sd.data.rows.count = 0 and sd.floatingCells.count = 0 );

lee-lindley commented 1 year ago

I tried setRowProperties as a workaround. It has the advantage of setting the background color on my additional header row cells that are empty. The disadvantage is that it sets the background color across all all columns, even past the edge of where we have any data. I assume setTableRowProperties only sets the background for the columns populated by the query. I could use a query binding a PL/SQL collection as a table to build my extra column headers. Those woudl be table1 and the actual query would be table2. That may be a better option than putCell. Meanwhile, I implemented your code fix in my version and validated the reported issue is fixed. Thanks Marc.

mbleron commented 1 year ago

Fixed in v3.1.