Last post Apr 28, 2011 05:35 AM by sirdneo
Apr 12, 2011 06:33 AM|jagdipa|LINK
I want to export a large amount of data to an excel file (30,000 plus). This is no problem. The problem is that, one of the columns needs to be coloured green according to the data within it.
For example, the colour contains percentages, and when the percentage is over 70%, then the cell needs to be green.
I can do this easily using a Datagrid/Gridview. However, because of the amount of data, my server is crashing with a SystemOutOfMemory exception.
What I would like to do is use a tool to write the data to an excel file, and then transfer the file. Is this possible?
Apr 12, 2011 05:06 PM|Lannie|LINK
Post your current Export to Excel code for forum user review and comment to help you along.
Apr 13, 2011 12:01 PM|jagdipa|LINK
Here is the code that I use. I am not sure which line it is actually crashing on.
Dim dr As SqlDataReader
dr = sqlcomm.ExecuteReader
gridview1.DataSource = dr
'Clear the response
Dim attachment As String = "attachment; filename=Report.xls"
'Tell the response this is an excel file
Response.ContentType = "application/ms-excel"
'Write the gridview to the response
Dim htw As New HtmlTextWriter(Response.Output)
Apr 14, 2011 07:53 PM|Lannie|LINK
Apr 21, 2011 09:30 AM|sirdneo|LINK
Try these two links, they will guide you that how you can export data in Excel with formating.
I have generated around 10~20MB files from first link (.xlsx based) and more than 100MB files from 2nd link (.xls XML based). You can try any of above to solve your problem.
Also have a look at this article, it will guide you how to export data grid to excel:-
Apr 27, 2011 05:17 AM|jagdipa|LINK
Just to update this thread, I am trying out EPPlus - an open source Excel docx builder.
Apr 28, 2011 05:35 AM|sirdneo|LINK
I have posted a sample at this link for EPPlus, that will give you a good start:-