Spread generated xls (97-2003) can't be read by custom application unless its opened in Excel97 and 'Saved as Excel97-2003

Posted by: axd on 3 August 2017, 2:34 pm EST

  • Posted 3 August 2017, 2:34 pm EST

    We have an export scenario where we are using a Trial version of Spread that generates output in Xls (Excel97-2003) format that is used as source data for another application.  

    This application throws an exception saying the file is not in Excel97-2003 format, of which if we open the Adapter generated file and perform a 'Save as Excel97-2003' and then retry the same file, the importing application then works fine.

    I notice in the documentation Spread conforms to the BIFF8 specification and after investigating further using BIFFVIEW, I noticed that doing a 'Save as' in Excel adds the following sections into the Adapter generated file, just after the BOF tag.

     Does anyone know what these tags are and why are required? 

     As we are in evaluation phase, this issue is key to us deciding whether to go with Spread or look for another solution 

    <table style="border:1px solid black;empty-cells:show;border-collapse:collapse;font-family:'Times New Roman';"><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF INTERFACEHDR (E1h)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">2</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">B0 04 </td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF MMS (C1h)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">2</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">00 00 </td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF INTERFACEEND (E2h)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">0</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;"></td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF WRITEACCESS (5Ch)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">112</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">04 00 00 6B 79 69 6B 20  20 20 20 20 20 20 20 20 
    20 20 20 20 20 20 20 20  20 20 20 20 20 20 20 20 
    20 20 20 20 20 20 20 20  20 20 20 20 20 20 20 20 
    20 20 20 20 20 20 20 20  20 20 20 20 20 20 20 20 
    20 20 20 20 20 20 20 20  20 20 20 20 20 20 20 20 
    20 20 20 20 20 20 20 20  20 20 20 20 20 20 20 20 
    20 20 20 20 20 20 20 20  20 20 20 20 20 20 20 20 </td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF CODENAME (42h)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">2</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">B0 04 </td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF DSF (161h)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">2</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">00 00 </td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF EXCEL9FILE (1C0h)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">0</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;"></td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF TABID (13Dh)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">12</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">01 00 02 00 03 00 04 00  05 00 06 00 </td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF FNGROUPCOUNT (9Ch)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">2</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">10 00 </td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF WINDOWPROTECT (19h)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">2</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">00 00 </td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF PROTECT (12h)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">2</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">00 00 </td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF PASSWORD (13h)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">2</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">00 00 </td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF PROT4REV (1AFh)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">2</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">00 00 </td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF PROT4REVPASS (1BCh)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">2</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">00 00 </td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF WINDOW1 (3Dh)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">18</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">F0 00 5A 00 4C 2C 8B 1A  38 00 01 00 00 00 01 00 
    58 02 </td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF BACKUP (40h)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">2</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">00 00 </td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF HIDEOBJ (8Dh)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">2</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">00 00 </td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF DATE1904 (22h)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">2</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">00 00 </td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF PRECISION (0Eh)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">2</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">01 00 </td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF REFRESHALL (1B7h)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">2</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">00 00 </td></tr><tr><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">BIFF BOOKBOOL (DAh)</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">2</td><td style="font-family:monospace, Courier;vertical-align:top;border-top-width:1px;border-top-style:solid;border-top-color:black;">00 00 </td></tr></table>
  • Replied 3 August 2017, 2:34 pm EST

    Hello,

    Yes you are right Spread saves Excel file in xls, BIFF8 format and since your application is opening a similar kind of file, the file should open without any issues. Could you please let me know which version of Spread are you currently using and which method are using to export or generate an excel file from Spread.

    I am not very sure how exactly are you generating the file shown in the picture above, could you please elaborate on the same so that I can check any differences at my end.

    Thanks,

    Manpreet Kaur

  • Replied 3 August 2017, 2:34 pm EST

    Hi 

    We are using the Trial version of spread for BizTalk (I assume there is only 1 trial version?)  

    We are using the spread assembler to generate the spreadsheet

     The spreadsheet opens fine in Excel, its only the other application that cant open it unless it is opened and saved as 97-2003 format, which it should already be in.

     The table above is the output when using BIFFVIEW.   What I tried to find out was what Excel was going when you perform a 'Save As' and noticed it added the section that I have pasted in my original post.

    As a side note, the xls also has its table 'Protected', which I have read should have been fixed in ealier versions.  (I dont think this is the cause, but still is annoying for my client)

     Thanks 

     

  • Replied 3 August 2017, 2:34 pm EST

    I just tried this with the latest trial version and could not see the issue. Here are the steps I used to test this:

    (1)  Generated a spreadsheet using the Assembler component.

    (2) Consumed the generated spreadsheet in a .NET Application (by using the OleDB provider).

    Can you please attach the excel file so that I can look further into this?

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels