Exporting to Excel in a .NET application

[Matt Bailey]

Good afternoon,

I have an asp.net project wherein I am trying to export data into
excel. I have that working, however, I am typically dealing with a
large volume of data when I do this. Can anyone suggest a better
method for this job other than reading the data into a datagrid and
then rendering the datagrid as an excel spreadsheet?

Any and all help is greatly appreciated.

Thanks

 

[Mike]

Hi Matt,

We used the OLEDB provider and SQL statements in the web server to export
data to Excel. Here is a sample.

' Opening connections to Excel file.
''connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Book1.xls;Extended Properties=""Excel 8.0;HDR=NO;"";"
cnn = New OleDbConnection(connString)
cnn.Open()

Dim cmd As New OleDbCommand
cmd.Connection = cnn

' Reading the number of rows that are present in the data tab
where the population will occur.
cmd.CommandText = QUERY_EXCEL_ROW_COUNT
Dim rdr As OleDbDataReader = cmd.ExecuteReader
Dim rowCount As Integer = 1
While rdr.Read()
rowCount += 1
End While
rdr.Close()

' Reading fields values from Excel template.
cmd.CommandText = QUERY_EXCEL_TEMPLATE_FIELDS
rdr = cmd.ExecuteReader
rdr.Read()
Dim index As Integer
For index = 0 To rdr.FieldCount - 1
fieldsNames.Add(rdr.GetString(index))
Next
rdr.Close()


Mike

 

[Matt Bailey]

This method would work for an existing file only though, right? If so,
I wonder if there is a way to have it create the file first?

Thanks

 

[sloan]

If you have ExcelXP... you can mimic "Save as Xml".

If you take a regular spreadsheet, and "save as xml" on the file menu. you
can then open it in notepad and look at the xml.

You could mimic this functionality ... by looping over a DataSet or
IDataReader.

Its not a "quick fix". It'll take probably a week to code it up.

You will lose all imbedded object also, as a side note.

If you don't have Excel XP...then you can use the Excel (com) object
library.

...

Lastly, you could use ActiveReports. and programmatically export to excel
your in memory report.

 

[AtariPete]

Hi Matt,

If you don't mind purchasing a commercial component I recommend
SpreadsheetGear (http://www.spreadsheetgear.com/). Its an excel
compatible control written entirely in .net. Its available as a visual
component or just a calculation engine. Most importantly its fast and
lets you save as an excel binary document rather than xml.

Cheers,
Pete

 

[Matt Bailey]

Thanks for your response. I'll check that out for sure