Meld Homepage , Electronic PCB PCBA , Software .NET AVR PLM BOM , Network TCP UDP MQTT , Microcontrollers AVR ARM , Powersupplys Switchmode , Nursecall MC5000 CD2000

Date: 2023-05-07
Updated: 2023-06-12
Author: Erik Leth Danielsen
Excel Binary File Format (.xls) Structure, BiffView

Excel Binary File Format (.xls) Structure, BiffView

Problem:
I have used Excel xls files for many years for sending Part List (BOM) to external PCBA delers.
The only possibility to generate XLS in .Net, was to controll Excel trugh
Microsoft.Office.Interop.Excel.Application.
I have always wanted a way to do it without having excel installed.
The benefit of the old Excel XLS format is that everyone can open it.
Info: The Newer format XLSX is just a zip file you can unzip it change it in notepad and zip it agen.
Test Software


Short:
I looked for a lib and found xlsLib see link. it's developed in c++ very complicated, 
my head almost fell off :-), trying to figure out how it worked it took 14 days just to understand it a littel,
it could do everything except what i needed i want to insert images in my xls.
my first idea was to add the feature to xlsLib, but over time i found out it was way to complicated, Then i found BiffView see Link,
and started to understand how it worked, i made my own BiffView that also supports "Office 97-2007 Drawing DATA".
two BiffView examples see:
Test1.htm
Test2.htm
nyp447 210315.htm
The Excel Binary Structure:
1. The Workbook file Excel97-2007 Binary File Format
is split up into small parts called Biff Records
Type 16 bit  (2 bytes) lsb byte first
Length 16 bit (2 bytes) lsb byte first

Two of the recoards Type (EBh) and (ECh) Contains MS Drawing Data

If excel can't load the file, be !!OBS on address
(85h)BOUNDSHEET //points to sheat (809h)BOF address
The Next 3 is optional.
(20Bh)INDEX //points to DBCELL address it also work without it
(D7h)DBCELL //points to Rows address it also work without it
(FFh)EXTSST //points to SST address it also work without it

2. Office 97-2007 Drawing DATA 
Type 32 bit  (4 bytes) lsb byte first 
Length 32 bit (4 bytes) lsb byte first

3. Windows Compound Binary File Format
All the data is saven in a Hardisk file format with sectors, I used openmcdf to do this see Link.
as standard an xls containes 3 files the only one thats matters is the Workbook file
File1: Workbook
File2: chr(5) & SummaryInformation
File3: chr(5) & DocumentSummaryInformation

For more info see:
Excel97-2007BinaryFileFormat(xls)Specification.pdf
OfficeDrawing97-2007BinaryFormatSpecification.pdf
WindowsCompoundBinaryFileFormatSpecification.pdf

Download complete code:
I have made a BiffView tool, that also can generate xls with images
it requres Framework 2.0
Complete Code VB.Net
To Run it \MeldcoBiffView\bin\Release\MeldcoBiffView.exe

The program contains 
1. BiffView, select xls file and it generates an htm file that shows the Biff Recoards.

2. Test1 XLS generates a small test xls file 

3. Test2 XLS generates a test xls file contaning 3 images

To view code open \MeldcoBiffView.sln in .Net2010 or newer

Links:
xlsLib
openmcdf
BiffView by DIaLOGIKa

Privacy Policy © MELDCO APS | skovsgaardsparken 6 | 8362 hoerning | E-mail: info@meldco.dk | VAT ID: 34576149 411571