Generate an Excel file (without Excel)

[John Bailo]

I want to generate an Excel file (.xls format) from some database data.

I don't want to use Excel.exe because of all the automation and security
issues.

Does Microsoft document the .xls file format anywhere?

[Rad [Visual C# MVP]]

On Mon, 26 Feb 2007 08:41:29 -0800, John Bailo wrote:

I want to generate an Excel file (.xls format) from some database data.

I don't want to use Excel.exe because of all the automation and security
issues.

Does Microsoft document the .xls file format anywhere?

I don't think documenting the format would be of much use, because
'recreating excel is just not worth the bother. You do though have a couple
of options:

- You could use the well documented XML Excel format, if your clients have
a version of Excel capable of opening the same. You code would thus spit
out Excel XML
- Even easier, You could use a third party library to generate the files.
Aspose make a pretty fully functional library for this.
--
Bits.Bytes
http://bytes.thinkersroom.com

[John Bailo]

This looks like a good library on SourceForge:

http://sourceforge.net/projects/koogra/

".Net implementation of an Excel file reader for reading unencrypted
Excel 97 and up BIFF based files."

I'm wondering if it can write the files as well...

Rad [Visual C# MVP] wrote:

On Mon, 26 Feb 2007 08:41:29 -0800, John Bailo wrote:

>>I want to generate an Excel file (.xls format) from some database data.

I don't want to use Excel.exe because of all the automation and security
issues.

Does Microsoft document the .xls file format anywhere?

I don't think documenting the format would be of much use, because
'recreating excel is just not worth the bother. You do though have a couple
of options:

- You could use the well documented XML Excel format, if your clients have
a version of Excel capable of opening the same. You code would thus spit
out Excel XML
- Even easier, You could use a third party library to generate the files.
Aspose make a pretty fully functional library for this.

[PS]

"Rad [Visual C# MVP]" <no****@nospam.comwrote in message
news:1i****************@thinkersroom.com...

On Mon, 26 Feb 2007 08:41:29 -0800, John Bailo wrote:

>I want to generate an Excel file (.xls format) from some database data.

I don't want to use Excel.exe because of all the automation and security
issues.

Does Microsoft document the .xls file format anywhere?

I don't think documenting the format would be of much use, because
'recreating excel is just not worth the bother. You do though have a
couple
of options:

- You could use the well documented XML Excel format, if your clients have
a version of Excel capable of opening the same. You code would thus spit
out Excel XML
- Even easier, You could use a third party library to generate the files.
Aspose make a pretty fully functional library for this.

TMS Software (www.tmssoftware.com) have a product called FlexCel that costs
125 Euros.

PS

--
Bits.Bytes
http://bytes.thinkersroom.com

[Paul Clement]

On Mon, 26 Feb 2007 08:41:29 -0800, John Bailo <ja*****@texeme.comwrote:

¤
¤ I want to generate an Excel file (.xls format) from some database data.
¤
¤ I don't want to use Excel.exe because of all the automation and security
¤ issues.
¤
¤ Does Microsoft document the .xls file format anywhere?

You can create an Excel Workbook file using Jet OLEDB and Jet SQL:

Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test Files\ExcelWB.xls;Extended Properties=Excel 8.0"

Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString )
ExcelConnection.Open()

Dim SQLDDLCommand As String = "CREATE TABLE tblCustomers " & _
"(CustomerID INTEGER, " & _
"[Last Name] TEXT(50), " & _
"[First Name] TEXT(50), " & _
"Phone TEXT(10), " & _
"Email TEXT(50))"

Dim ExcelCommand As New System.Data.OleDb.OleDbCommand(SQLDDLCommand, ExcelConnection)
ExcelCommand.ExecuteNonQuery()

ExcelConnection.Close()
Paul
~~~~
Microsoft MVP (Visual Basic)

[Kam]

Any idea how to apply an existing template when saving the file?!

I am producing a CSV file which I then want to save as xls using a
particular template.

Any help would highly be appreciated. Thank you.

Kam

Paul Clement wrote:

On Mon, 26 Feb 2007 08:41:29 -0800, John Bailo <ja*****@texeme.comwrote:

¤
¤ I want to generate an Excel file (.xls format) from some database data.
¤
¤ I don't want to use Excel.exe because of all the automation and security
¤ issues.
¤
¤ Does Microsoft document the .xls file format anywhere?

You can create an Excel Workbook file using Jet OLEDB and Jet SQL:

Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test Files\ExcelWB.xls;Extended Properties=Excel 8.0"

Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString )
ExcelConnection.Open()

Dim SQLDDLCommand As String = "CREATE TABLE tblCustomers " & _
"(CustomerID INTEGER, " & _
"[Last Name] TEXT(50), " & _
"[First Name] TEXT(50), " & _
"Phone TEXT(10), " & _
"Email TEXT(50))"

Dim ExcelCommand As New System.Data.OleDb.OleDbCommand(SQLDDLCommand, ExcelConnection)
ExcelCommand.ExecuteNonQuery()

ExcelConnection.Close()
Paul
~~~~
Microsoft MVP (Visual Basic)

[Paul Clement]

On Wed, 14 Mar 2007 14:57:09 +0000, Kam <ka**@NOSPAM.comwrote:

¤
¤ Any idea how to apply an existing template when saving the file?!
¤
¤ I am producing a CSV file which I then want to save as xls using a
¤ particular template.
¤
¤ Any help would highly be appreciated. Thank you.
¤

Template files are only known by Microsoft Excel, so you would have to create the Workbook using the
Excel application first.
Paul
~~~~
Microsoft MVP (Visual Basic)

[Kam]

I got a custom template ready in a local folder, are you saying that I
would not be able to use it when saving the workbook from C# application?!
Thanks for the quick response.
Kam

Paul Clement wrote:

On Wed, 14 Mar 2007 14:57:09 +0000, Kam <ka**@NOSPAM.comwrote:

¤
¤ Any idea how to apply an existing template when saving the file?!
¤
¤ I am producing a CSV file which I then want to save as xls using a
¤ particular template.
¤
¤ Any help would highly be appreciated. Thank you.
¤

Template files are only known by Microsoft Excel, so you would have to create the Workbook using the
Excel application first.
Paul
~~~~
Microsoft MVP (Visual Basic)

[Paul Clement]

On Wed, 14 Mar 2007 15:55:45 +0000, Kam <ka**@NOSPAM.comwrote:

¤ I got a custom template ready in a local folder, are you saying that I
¤ would not be able to use it when saving the workbook from C# application?!
¤

No, what I am saying is that you can't use ADO.NET to do it. Instead you would need to use
automation with the Excel application.

http://support.microsoft.com/kb/302084
Paul
~~~~
Microsoft MVP (Visual Basic)

[Kam]

Firstly, apologise for the not getting back to you any earlier as I was
dealing with other probs and projects...

Anyhow, the process is as follow:

- Produce an XML document which then passed to BizTalk server to process

- BizTalk could either produce XML or CSV file but not Excel spreedsheet

- I got a local spreedsheet template that I need to utilise when
creating the Excel file (i.e. Mapping the XML document to Excel)

I need to be able to map the XML to Excel in code and utilise the
template to store the Excel file.

Any sample code or links or suggestions will highly be appreciated.

Thank you and hope to hear from you very soon.........

Kam


Paul Clement wrote:

On Wed, 14 Mar 2007 15:55:45 +0000, Kam <ka**@NOSPAM.comwrote:

¤ I got a custom template ready in a local folder, are you saying that I
¤ would not be able to use it when saving the workbook from C# application?!
¤

No, what I am saying is that you can't use ADO.NET to do it. Instead you would need to use
automation with the Excel application.

http://support.microsoft.com/kb/302084
Paul
~~~~
Microsoft MVP (Visual Basic)