agrizzo
asked on
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?
cri
Try http://www.wotsit.org
Try these also:
How to create a BIFF5 file: http://support.microsoft.com/support/kb/articles/Q150/4/47.asp
Records Needed to Make a BIFF5 File Microsoft Excel Can Use: http://support.microsoft.com/support/kb/articles/Q147/7/32.asp
How to create a BIFF5 file: http://support.microsoft.com/support/kb/articles/Q150/4/47.asp
Records Needed to Make a BIFF5 File Microsoft Excel Can Use: http://support.microsoft.com/support/kb/articles/Q147/7/32.asp
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
Dim oApp As Excel.Application
Dim oXls As Excel.Workbook
Set oApp = New Excel.Application
Set oXls = oApp.Workbooks.Add
oXls.SaveAs "D:\Data\Excel97\MyFile.xl
Exit_ufnCreateNewFile:
On Error Resume Next
oXls.Close
Set oXls = Nothing
oApp.Quit
Set oApp = Nothing
Exit Function
Err_ufnCreateNewFile:
ufnCreateNewFile = Err.Number
MsgBox "[" & Err.Number & "] " & Err.Number, vbCritical
Resume Exit_ufnCreateNewFile
End Function
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
Dim oApp As Excel.Application
Dim oXls As Excel.Workbook
Set oApp = New Excel.Application
Set oXls = oApp.Workbooks.Add
oXls.SaveAs "D:\Data\Excel97\MyFile.xl
Exit_ufnCreateNewFile:
On Error Resume Next
oXls.Close
Set oXls = Nothing
oApp.Quit
Set oApp = Nothing
Exit Function
Err_ufnCreateNewFile:
ufnCreateNewFile = Err.Number
MsgBox "[" & Err.Number & "] " & Err.Number, vbCritical
Resume Exit_ufnCreateNewFile
End Function
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.
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.
ASKER
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?
Thanks
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.