Does Snaplogic support writing Grid format headers for Excel Binary Spreadsheet(.xslb files)?

[RakeshKumar1]

Hello Team,

I have requirement of writing excel binary spreadsheet .xslb extension type.

Used Excel formatter snap followed by file writer and able to write the .xslb file ,
But how do i apply Grid format for Excel table headers for filters on each column ?

Is there any way in Snaplogic to write .xslb files with grid on table columns(the drop down list in screenshot for headers) , Please suggest ?

Example of grid Excel file is attached, where we can do filtertation, sort etc on each column.

Thanks,
Rakesh

[RakeshKumar1]

Initially i was looking for writing .xlsb file with the filters and some colouring/formating grid options on file headers as in attachment, But found that In sldb SL manager tab it shows .xlsb file but on downloading from sldb, the file is converting to xlsx.
TestFileWithFilters.xlsx (19.1 KB)

[robin]

To be clear, you wish to write an .XLSB file and then be able to read/parse it later within a SnapLogic pipeline? The reason I ask is that when reading about the Binary format for Excel, one of the listed disadvantages I found was

Disadvantages Of Binary Files

Binary files cannot not be accessed by programs that understand the XSLX, XLSM file format only.

Thank you for the sample file. I’ll attempt to reproduce and report back to you on my findings.

[RakeshKumar1]

Yes i see some cons, I was looking for xlsb to send file as an email attachment as .xlsb will reduce size and we can easily open xlsb file with excel sheet. If i use xlsx the file size is more than twice and exchange servers will reject it due to size limits.

To reproduce please upload any xlsb file in file reader followed by excel parser it throws error, Excel formatter snap output is xlsx binary data.

As per this blog xlsb is not supported. Load .xlxb workbook with multiple sheets to oracle table. Load only one or specific sheet data only ...

Regards,
Rakesh

[robin]

Update on some research I’ve done. The library we use for Excel file reading/writing is Apache POI. It states:

POI does not yet support opening .xlsb files with XSSFWorkbook, but you can read files with XSSFBReader in org.apache.poi.xssf.eventusermodel.

That would translate to only being able to read .xlsb files, not being able to write them. Our current Excel Formatter + File Writer wouldn’t be able to save to the Excel Binary format.

If i use xlsx the file size is more than twice and exchange servers will reject it due to size limits.

If this is the primary reason to consider Binary format, I’d like to read up a bit more about whether those limits can be relaxed (depending on the Exchange Server version) or if there are other potential patterns to you (like the Claim Check pattern, where a link [for example, to a OneDrive or Box location] could be provided rather than an attachment.

What if the .xlsx file was zipped first via the Zip File Writer - would that reduce the file size enough and would the receiver be able to unzip?

[RakeshKumar1]

Thanks for the update,
I need to check with user for zipping but i am replacing an existing process with Snaplogic tool so user may not agree and would like to see the file in mail same as as-is process where he can easily open it without downloading etc.

• If this is the primary reason to consider Binary format, I’d like to read up a bit more about whether those limits can be relaxed (depending on the Exchange Server version)---->

xlsx extension is not an issue but its making the size twice+ which can cause issues in mail exch server limits. I am not aware if these is anything which can reduce xlsx attachment size.

Any plan on supporting xlsb in near time?