COURSE of the MONTH

Creating XLS (Biff) files

I would like to create an XLS file on our main frame. I am using the Excel 97 Developer's kit with the Biffviewer and also a hex editor for research. The documentation covers the biff records nicely but when I look at an XLS file with a hex editor, the biff codes (bytes) are within some other undocumented bytes. (i.e. the first byte is not the BOF (09h), in fact it is quite far away.) Also, the last few bytes in the file are not 0A 00 00 00. What do these surrounding bytes represent and how do I know how to create them?

Are you sure you can get away with this? I suspect not. A structured storage file is more than just a series of bytes. As an OLE file type, it was not expected that programmers would try to create this file byte by byte. I suspect that if you proceed, you are in for a MAJOR headache.

More importantly, if the data is coming from a non-PC source, why not use the comma-delimited (csv) or fixed-width formats? Each of these formats imports extremely easily in Excel. In addition, the comma-delimited format is associated with Excel in most default MS Office installations. And if you have some sort of proprietary format that you want the data in, I would recommend creating a custom conversion utility using either Excel or VB.

Assumptions:
1. You are using Excel 97 and therefore want BIFF8 files not BIFF5. Consequently, the above references will not help you much. The first has a utility written in Pascal for creating BIFF5 files. The second will point you to a download, providing code to create a C++ console app which creates BIFF5 files. The formats for BIFF5 and BIFF8 are different, therefore you will have significant Pascal or C++ revising to do.

2. You have posted to the MS Office 97 boards so therefore you are not interested in writing Pascal or C++ code, may not know much about using pointers to IStorage or IStream interfaces, and are hoping for (relatively) simple approach not unlike using VBA to do file output.

If you want to create an Excel file, why not go the path of least resistance. Using VB/VBA and a reference to the Microsoft Excel 8 Object Library (excel8.olb), create a new COM object.

Function ufnCreateNewFile() As Long
On Error GoTo Err_ufnCreateNewFile

Also, you asked about the disparity between the record descriptions in the Developer's Kit and what you see in the hex editor.

I am not certain about this, but I believe the answer lies with how you create BIFF5 and later files, that is you create an OLE structured storage file. Office 97 documents are all structure storage files, which, as the documentation suggests, is kind of a file system within a file system. Using C++, you first obtain a pointer to a structure storage file and then obtain pointers to the components of these files using IStorage and IStream interfaces. What the documentation instructs you on is what data ("records") should be passed to these interfaces. I believe that Bruce McKinney's Hardcore Visual Basic (Microsoft Press) provides information on how to use IStorage and IStream interfaces in VB but I could be wrong.

Let me know if the above code satisfies your requirements.

0

agrizzoAuthor Commented: 1999-12-06

I understand how to build from the BOF record to the EOF record. It is the surrounding bytes of data that I am trying to duplicate/create. My program will be written on an AS400. So there will be no VB or VBA programs. I will have to write data on a byte by byte basis. I am trying to understand what precisely the 'header' (before the BOF record) and 'footer' (after the EOF) bytes of data represent so that my program can properly duplicate or create them. I guess the answer comes from the definition of OLE 2 implementation of the structured storage model. Does anyone know where I can find documentation on this surrounding bytes of data? Websites? Books?

And one more afterthought. Even though you posted this in the MS Office area, it sounds like you are planning on writing this in C or C++.

If you pursue your current path, I suspect you will get into faking vtables or recreating non-OLE interfaces. Therefore, you might post this question in the C or C++ boards also. Just create a zero-point question there and refer to this question.

0

agrizzoAuthor Commented: 1999-12-06

I knew would not be easy, maybe unlikely. The original idea started as e-mailing users (also vendors and customers) files instead of generating reports for every bit of data they want. The reports were standard and the users were using them to key data into spreadsheets. The comma delimited files have been created without any problems. However, sending an Excel spreadsheet as an attachment is a better way to go only because some of the users do not know how to import comma delimited files. It may not be difficult process but I would like more a hands off approach. Thanks for the help.