Datagrid to Excel problem (ASP dot net)

[Steve Chatham]

I use the following code:

Private Sub RbtnExport_SelectedIndexChanged(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles RbtnExport.SelectedIndexChanged
Dim sFile As String = Session("User") & "-Customer List-" & Today()
sFile = sFile.Replace("/", "")
RbtnExport.Visible = False
Select Case RbtnExport.SelectedItem.Value
Case "Excel"
Response.ContentType = "application/x-msexcel"
Case "Word"
Response.ContentType = "application/msword"
End Select
Response.AddHeader("Content-Disposition", "Attachment; filename=" & sFile)
End Sub

To allow a user to save a web page with a datagrid on it into an Excel
sheet.

They'll run a page using a Web check box, which renders it to an ASPX page,
and then when they have the final product on the screen, they click the
radio button for Excel, and it prompts them to Open or to Save the file.

The problem occurs when you save or open the file.

The datagrid has 9 columns in it, a product code, description, unit of
measure, price, product hierarchy number, weight and UPC code, etc.

The data grid has a header & a footer on it, and each column in the datagrid
is sortable.

If I have a short list, one of maybe less than 20 records, I can click the
Excel button, it prompts me to save it, and it'll save as a document with a
..xls extension. You can then open it up without incident.

If the list is longer - say maybe over 40-50 rows in length (up to the
longest list, with up to 500 or more items in it), it will save it with a
..xls extension, but you cannot open it.

Excel attempts to open it, but it immediately gives you an OK box that says
"Unable to read file". It works fine if the number of rows is small (under
25 or so), but not if it's much more than that. Thus far, I have not seen a
pattern in it. The file that will open is 20K in size, the one that will
not is 400K in size. I have figured that the difference is between 30 and 50
records before it gives me this problem. I've noticed that each line
appears to add about 1K in file size to it the excel file.

It doesn't appear to be a memory issue, as excel works fine any other time,
and will open spreadsheets much larger than this. I've confirmed that it's
not version specific in Excel, as it barfs on every version of excel it
tries to open it in.

Any idea as to what is going on here on this problem? Is there any way I
can step thru the open of the file in Excel, to see the specific problem
Excel has with opening the file?

Any help/advice appreciated.

Thanks,

SC

 

[Peter Bromberg [C# MVP]]

Steve,
If there are no formulas to be involved, you might want to look at using the
RenderControl method of the DataGrid (and most other ASP.NET controls)
This will spit out the HTML representation of the DataGrid.

You can find plenty of examples of doing this on the .NET.
--Peter

 

[Steve Chatham]

Peter:

Before I get too far into looking into this option (RenderControl) - a quick
question:

On our web pages, the datagrids may have a number of columns - like for
complete shipments, you'd have $$ ordered & shipped, and for incomplete
ones, you'd have those 2 colums + an open $$ and qty - meaning, that we'd
have 6 columns in some datagrids, and 10 in others. Too, the columns would
be named differently in each of the datagrids, as they're built from a SQL
command.

Does the Rendercontrol command force you to write routines to render the
datagrid based on the number of columns you have?

To answer your question, there are formulas involved, but they're in sql -
i.e. ordered-shipped = open etc.

SC

 

[Steve Chatham]

A follow-up -

I have found some code:

Sub Page_Load(sender as Object, e as EventArgs)
'1. Create a connection
Dim myConnection as New
SqlConnection(ConfigurationSettings.AppSettings("connectionString"))

'2. Create the command object, passing in the SQL string
Const strSQL as String = "sp_Popularity"
Dim myCommand as New SqlCommand(strSQL, myConnection)

'Set the datagrid's datasource to the datareader and databind
myConnection.Open()
dgPopularFAQs.DataSource =
myCommand.ExecuteReader(CommandBehavior.CloseConnection)
dgPopularFAQs.DataBind()


'Get the rendered HTML
Dim SB as New StringBuilder()
Dim SW as New StringWriter(SB)
Dim htmlTW as New HtmlTextWriter(SW)
dgPopularFAQs.RenderControl(htmlTW)

Dim dataGridHTML as String = SB.ToString()

ltlHTMLOutput.Text = Server.HtmlEncode(dataGridHTML)
End Sub

which I think will render in HTML, only question is - how does this work
with Excel? Would I still use the same method I did before?

Case "Excel"
Response.ContentType = "application/x-msexcel"
Case "Word"
Response.ContentType = "application/msword"
End Select
Response.AddHeader("Content-Disposition", "Attachment; filename=" & sFile)

(where I am using the response.contenttype property)?

SC