Export to Excel in BIFF format

[ggupta78]

Hi,

Currently in our application we use the MHTML way of exporting to Excel
i.e we render the datagrid HTML and set the MIME type to Excel.
This works great but has the following limitation:
1. File size is extremely large in some cases
2. Our clients using older versions of Excel or OpenOffice 2.0 cannot
open these files.

So, we would want to export to excel using the BIFF format. But from
what i have understood after googling for BIFF is that:
* Microsoft has just provided documentation on this format, but there
are no API's in the .Net framework that would help us to save data in
the BIFF format.
* We would either have to do Excel automation using COM interop, which
is generally not recommended, or use a pure .net third party component
to create excel files in the BIFF format.
Also hav'nt found any free third party components so far, we would have
to purchase one.

So basically i need to know if there is any easier way of exporting to
excel in BIFF format? or if there are any free components/libraries
available?
Would be grateful if any one who has solved this kind of an issue
before can point us in the right direction.

TIA,
Gautam

 

[Edwin Knoppert]

See www.connectionstrings.com

The extended property can be set to "Excel 8.0"
Then you can use the oledb provider to create an xls using Create Table
statements and such.
We use it, it's super.
Wonder how we'll be able to embed formulas this way, i don't know.
You can do the same for csv but you can make csv 'manually' as well and old
excel could reado those as well.

 

[ggupta78]

Hi Edwin,

Thanks for your reply.

I have understood a little of what you meant. Basically i have seen
code that can treat an excel file as a datasource and read data using
the OleDb connection. So similarly i guess what you are saying is that
we can create a table in excel and populate it with data.

But it would be great if you can give me some sample code.

Also, I guess its not possible to introduce certain formatting
attributes like font size, weight or colors using this method, right?
since we would like to set formatting as well.

thanks,
gautam

 

[Edwin Knoppert]

For reading i have:

http://www.hellobasic.com/cgi-bin/forum/YaBB.pl?board=dotnet;action=display;num=1133003929

For creating and such not at this time, maybe later.. ever.

I learn about everything from here:
http://www.w3schools.com/sql/sql_create.asp