Hi,
We are using an Excel resource, this is used as the BinaryData input for Workbook_Open_BinaryData, Worksheet_SelectByName and using Cell_WriteByIndex to fill cells with specific values, then using Workbook_GetBinaryData to set the output parameter of an action as the binary data, and ultimately Workbook_Close.
For some reason, the formatting of cells which should not be edited (are not the subject of Cell_WriteByIndex) is being changed. The format of these cells is changed to the format of the cells which are being filled through Cell_WriteByIndex. Cells which already contained different formatting and text in the resource, are not changed - only empty cells in the same columns as the ones being filled.
For example, this are the cells in the resource, with no borders:
After downloading the Excel file with data filled, these cells now have borders:
I have confirmed that the formatting of these cells is correct in the original Excel being used as resource. Even after clearing the formatting in the resource, re-add these columns, even creating a new version in a clean Excel, the same issue applies.
When I disabled the Excel Package actions so the resource file is directly downloaded without being opened and filled, the formatting is not changed. It’s only when the Excel Package actions are executing using the binary data from the resource, and the outcome of these actions is set as the binary data to be downloaded, that the formatting of these cells is changed.
We were able to get around this issue by locking the cells and protecting the worksheet, then the format stayed as desired. So this is not an active issue, but I would still like to find out what was causing this behaviour.
Is it possible that this is related to any of the Excel Package actions?
Kind regards,
Zoë
Hi Zoë,
Thank you for sending the files.
This issue is quite interesting.
After some investigation, I found that EPPlus, the library used in this component, might reapply default settings to borders or styles, even if they haven't been explicitly defined. This could cause the borders to appear when the file is opened again in Excel.
I then checked the cells and confirmed that they did indeed have borders. Specifically, the border style was set to "Thin". The borders might not be visible due to the applied width or style (such as a very thin border or no color).
I manually adjusted the border style to "None" in the first sheet, imported the updated file into OutSystems, and when downloaded, the cells no longer contain the borders.
Hope this helps.
Would you be able to share an example Excel file and .oml file? That would help clarify what the issue might be.
hi @ZoëLK , hope you're doing well.As mentioned by @Beatriz Sabino , it would be great if you could provide an example OML file along with the corresponding Excel file. This would greatly help in understanding the context and identifying the root cause of the issue more effectively.
Thanks and Regards!
@Wahaj Adil @Beatriz Sabino Hi both, thanks for your replies. I can't share the original OML and Excel files but I've been able to reproduce it in a test app using a fresh Excel file with similar formatting. Please find these attached.
This test app doesn't even write any data to the Excel, but even so the formatting in the downloaded Excel file is different compared to the resource. If I don't use any of the Excel Package actions and instead just download the binary file from the resource (either without saving to entity first or saving to entity and getting binary data from entity), the formatting doesn't change.
Hi Beatriz, thanks, I tried this and it also worked for me -- changing the border style in the first sheet also was enough to fix it for the second. Does this mean the default is taken from some of the cells of the first sheet only? And do you know why the borders were showing up on some cells on the first and second sheet but not all?
This is just a curiosity question to understand the issue better -- it is all working OK now.
I noticed that only cells A1 and B2 on the second sheet had a border style applied. I tested cell A2, and the style was set to "None".
I believe some cells may not have any border style, while others might have a border color set to white or something similar.
OK, makes sense. Thanks for your help!