Hi !

Does somebody has experiences with the BIFF format ?
It is in fact the Excel file format and it was partly documented by Microsoft some time ago...

I would like to use an Excel spreadsheet to output some reports from my software.
I first thought of using macros to connect the spreadsheet to my database, but it seems to not be very secure : crashes randomly with absolutely no reason though my queries and my code are OK and all Office updates are installed. And this solution will force the software to have an official version of Excel.

The solution I thought about would be to output an Excel file directly, in raw binary, from my program. The spreadsheet could be then read with the free Excel viewer...

I have looked at the file format : it is a bit complex but with some time it should be OK.
Do somebody has ever done this work and if yes, what were the difficulties encoutered ?

Any advices or other solutions are welcome.

Thanks.
Posted on 2003-03-28 01:46:05 by JCP
Why do you want to reinvent the wheel?


ExcelAPI Source Code (freeware)
Create BIFF 2.1 compatible Excel files directly without using any DLL's (no need for JET, ADO/DAO). Handles writing text, numbers and dates, headers, footers, fonts, cell formatting, column and row heights, hidden cells, locked cells and protected spreadsheets.


http://planetsquires.com/files/excelapi_vb.zip (Visual Basic Demo)
http://planetsquires.com/files/excelapi_pb.zip (Power Basic Demo)

Or use HTML:



<table border="1">
<tr>
<td>100</td>
<td>200</td>
<td>300</td>
</tr>
<tr>
<td>400</td>
<td>500</td>
<td>600</td>
</tr>
<tr>
<td>=SUM(A1:A2)</td>
<td>=SUM(B1:B2)</td>
<td>=SUM(C1:C2)</td>
</tr>
</table>


(save as .xls and open with Excel)
Posted on 2003-03-28 02:46:01 by bazik
use csv

Very easy to use and excel can read the file format.

the commas means another column and CRLF means another row.
Posted on 2003-03-28 03:30:50 by roticv

use csv

Very easy to use and excel can read the file format.

the commas means another column and CRLF means another row.


Thanks, but I can not use this : it would not be very user friendly to have a raw format... I also must write the formatting in the file and I don't think CSV is able to do that.

Bazik : Thanks a lot for these source codes : very useful and exactly what I am looking for.
As my program is in C do you think the PowerBasic version could be compiled as a DLL (or even better a static library) and use it from my C program ?

Thanks.

PS : The html trick is also nice, but I also need to set header for printing and adjusts pages sizes, etc... but still it is very nice to be able to do that.
Posted on 2003-03-28 04:00:54 by JCP

As my program is in C do you think the PowerBasic version could be compiled as a DLL (or even better a static library) and use it from my C program ?


Yes, you could create a DLL.

<advertise>BTW, PowerBasic is worth the money ;) </advertise>
Posted on 2003-03-28 04:03:59 by bazik
I worked on just this for a month or so. Then i discovered that using COM automation is much better and cleaner. I would recomend doing this. I would be glad to help you where i can.

However if your still bent on writing your own BIFF files, i was focused on BIFF 8. The early versions are a joke, but to handle that latter is far more complicated. And you still need to use COM (IStream intefaces).

The doc i have are to big to post at once... here they are. (just Biff stuff).
Posted on 2003-03-28 09:15:56 by NaN
And part 2
Posted on 2003-03-28 09:17:04 by NaN
And here is one of my test programs of using excel through COM automation.

I dont have a "SaveAs" routine in this, but its very easy to implement.

I suggest you seriously look down this road. Japheth had done alot of work revising his ComView tool such that workng on projects like this is drasticly simplified.

:alright:
NaN
Posted on 2003-03-28 09:20:23 by NaN
Very interesting stuff !

Thanks a lot, NaN ! :alright:
I never used COM but your examples are clean and well written : I think I will understand what is going on relatively easily.

Does it works on older Excel versions (ie 5 ?)

bazik : if it is not much work at all, please would you mind to compile this DLL for me ? ;)
I have heard good things about Power Basic, maybe I will buy it one day but C allows me to do nearly everything I want...
If it is longer than simply run a command-line, do not mind about it, I will port the code by myself.

All solutions seems interesting and I don't know which one I will use yet, so I would like to try them all...

By the way, your html tip gave me an idea : Excel saves also to html format but with their own tags that allows to do many more things, to control nearly everything : ie. set the header section, the print scale etc...he bad side is that html is not appropriated to write complex spreadsheets and that creating multi-tabbed spreadsheets requires to write one file for each tab...
Posted on 2003-03-28 10:57:40 by JCP

bazik : if it is not much work at all, please would you mind to compile this DLL for me ? ;)


I'll post it here later today.
Posted on 2003-03-28 11:39:40 by bazik
Excel 5 must be pretty old.. i cant say.

I have Excel 97, which itself supports BIFF8. If your only interseted in these early versions (assuming its cause this is the software you have) you may want to write your own output directly as you first queried on. Its very simple, and i gave you infor on how to do this in one of the above files..

To my knowledge, Excel introduced automation at Excel 97...

Hope it helps..
:alright:
NaN
Posted on 2003-03-29 02:00:34 by NaN