Hi
I’d like to report an issue we have with using Workbook_GetBinaryData which leads to the vast majority of blank rows being hidden depending upon the circumstances of a worksheet.
This leads to presentation issues in the sheet to the user for text and shapes that will then be squashed together due to blank rows around them being hidden.
The temporary workaround is to not have all rows in a sheet past the 'used range' hidden.
Background and example
Unfortunately we often could have .xslm Excel files that have rows hidden past the Excel used range for the sheet.
This is usually done to make the presentation of the sheet look better by showing the user there is no need to scroll further down the sheet.
To highlight the issue I have a simple process called ‘Convert_Workbook’.
In the screen the user selects an Excel file to work with.
The process opens the Excel file and calls Workbook_GetBinaryData and then passes out the binary file which is then downloaded to user.
I have attached the module, the Excel file used (MT_TestExcelFile3.xlsm) and the Excel file that is downloaded at the end showing the issues (OS_MT_TestExcelFile3.xlsm).
What the example demonstrates is that the issue only occurs if rows on a sheet past the Excel used range down to the very bottom row (1048576) are all hidden.
Situations when the issue does not occur
For information, a subsequent Google search provided me with this
AI Overview
The Workbook_GetBinaryData error, especially when dealing with hidden rows, often arises from Excel's internal handling of the file structure and the way it's accessed when retrieving data.
Specifically, using this action can lead to issues if bottom rows are hidden and the user scrolls down with the mouse, or if frozen columns are used and the user moves to the right.
The problem stems from Excel's internal mechanisms and is not directly related to VBA or any specific code, as noted in a post on OutSystems forums.
Understanding the Issue:
Hidden Rows/Columns:
When rows or columns are hidden, Excel still maintains their presence in the file structure. When you try to get the binary data, Excel's internal structures might get confused or misinterpret the file's layout, especially if you're interacting with hidden rows or columns.
Thanks
Pete
Hi @Peter Jonathan Hinckes,
Thanks for sharing the file, I was able to reproduce the issue and pin down the cause.
Your worksheet is saved with the "all rows hidden by default" flag (sheetFormatPr zeroHeight="1"). On a sheet like that, the rows you see are only visible because the file carries an explicit record for each one. The library the extension uses to write the file strips out empty row records when it generates the binary data in Workbook_GetBinaryData, so on a zeroHeight sheet those previously-visible blank rows fall back to zero height — which is exactly the collapsed/hidden-rows result you're getting.
So it's a genuine bug in the extension, not something in your app. The good news: it's already fixed. Before writing the binary data, the extension now re-pins the visible rows on zeroHeight sheets so they survive the save (sheets that don't use that flag are unaffected). I tested it against your exact file — all 49 visible rows are preserved instead of collapsing down to 13.
The fix will ship in v2.7.0, which will be published on the Forge in the near future. If you can hold off a little, just grab that version once it's out and your files will round-trip correctly. In the meantime, if you're able to edit the source file, turning off the "default hidden rows" setting before processing also avoids the problem.
I'll update this thread once v2.7.0 is live.
Hi @Peter Jonathan Hinckes, v2.7.0 is live.