[RESOLVED] Export data to Excel

[MMock]

I'm converting ASP to .NET. There is a page in the ASP application that does this: You click on an agent and a month and you get a report of his commission for that month in Excel.

I don't know if looking at the ASP code will help me but I am thinking I have to start from scratch.

So here is my environment: VS 2005, VB.NET, ASP.NET 2.0, Office 2007.

The report is very simple and I have the queries that generate the data, I just need to give the data to Excel and bring the user there to view it.

Googling is finding me plenty of resources but I was hoping people I trust here could point me to the best resource. Because I've found "resources" whose solution was to use Reponse.Write in asp.net code when we all know that that should be RegisterClientScriptBlock !

Thanks.

[Besoup]

Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("Content-Disposition", "inline;filename=Filename.xls")
        Response.Charset = ""
        Me.EnableViewState = False
 
        Dim strStyle As String = "<style>.text { mso-number-format:\@; } </style>"
        Dim oStringWriter As New System.IO.StringWriter
        Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)
 
       
        oHtmlTextWriter.WriteLine(strStyle)
        GridView1.RenderControl(oHtmlTextWriter)
 
        Response.Write(oStringWriter.ToString())
 
        Response.End()

I usually throw my data into a gridview and use the above code to export it.

[mendhak]

I'm converting ASP to .NET. There is a page in the ASP application that does this: You click on an agent and a month and you get a report of his commission for that month in Excel.

I don't know if looking at the ASP code will help me but I am thinking I have to start from scratch.

So here is my environment: VS 2005, VB.NET, ASP.NET 2.0, Office 2007.

The report is very simple and I have the queries that generate the data, I just need to give the data to Excel and bring the user there to view it.

Googling is finding me plenty of resources but I was hoping people I trust here could point me to the best resource. Because I've found "resources" whose solution was to use Reponse.Write in asp.net code when we all know that that should be RegisterClientScriptBlock !

Thanks.

Besoup's example is what I was going to show you, but he cheated and beat me to it. You set the content type as ms-excel so that the browser/OS knows what it is, and then write some HTML into it.

It's alright to use Response.Write in this case because you are encapsulating the entire "page" (which is actually an XLS in this specific scenario) in one method, so it doesn't interfere with anything else. The Response.Write vs RegisterClientScriptBlock becomes relevant when you're write JavaScript out to the page. HTH

[MMock]

Besoup's example is what I was going to show you, but he cheated and beat me to it.

It's nice that you both agree and you were both very fast responding (thanks!) though I am not sure how he cheated you. That is for the two of you to straighten out...

[MMock]

It's alright to use Response.Write

I actually wasn't referring to Response.Write in this case. When I wrote that I didn't even know I'd be using it yet (I soon found out)! Funny.

Well, I was in here to say the snippet I tried didn't work (and it's from Microsoft - there's no "I'm aghast" smilie?!) but now I'll try the good stuff.

[MMock]

[QUOTE=BesoupI usually throw my data into a gridview and use the above code to export it.[/QUOTE]

The snippet I found was using a gridview too (actually a datagrid), so I'll put one on my page that won't be visible to the user, just to have something to throw my data in?

[MMock]

I tried it, and I have some problems:

I am getting an error:

Control 'gvAgentCommissions' of type 'GridView' must be placed inside a form tag with runat=server.

To fix that I need to have this?:
Public Overrides Sub VerifyRenderingInServerForm(ByVal control As System.Web.UI.Control)

End Sub


That evades the exception, but when Excel opens it says it is opening my aspx file:

The file you are trying to open Commissions.aspx is in a different format than specified by the file extenstion. Do you want to open the file now?

If I say yes, it is a blank worksheet that it has given the name Commissions.aspx.

Thanks.

[MMock]

Never mind, the worksheet isn't blank anymore.

It helped that I defined Columns in my gridview!

[MMock]

Okay, my blank worksheet problem is fixed.

I changed this:

' Response.AddHeader("Content-Disposition", "inline;filename=Filename.xls")

to this

Response.AddHeader("Content-Disposition", "attachment; filename=ExcelFile.xls")

which I think got rid of the problem where it thought it wanted to open my aspx file (if I'm remembering correctly)
but I am still having a problem where it thinks it's an invalid extension. Is there a different ContentType string I need for Excel 2007?

Thanks, this is coming together very quickly thanks to you guys.

[mendhak]

Can you show your code even if it's similar to B's?

[MMock]

Of course! Thank you for asking. Here it is, from where I call my stored proc.

        Dim adapter As New SqlDataAdapter(command)
        Try
            adapter.Fill(ds)
        Catch ex As Exception
            Response.Write("Submit() Exception " + ex.Message)
            conn.Close()
            conn.Dispose()
            conn = Nothing
            Exit Sub
        End Try

        Me.gvAgentCommissions.DataSource = ds
        Me.gvAgentCommissions.DataBind()

        'Try
        '    ' Set the content type to Excel.
        '    Response.ContentType = "application/vnd.ms-excel"
        '    ' Remove the charset from the Content-Type header.
        '    Response.Charset = ""
        '    ' Turn off the view state.
        '    Me.EnableViewState = False

        '    Dim tw As New System.IO.StringWriter()
        '    Dim hw As New System.Web.UI.HtmlTextWriter(tw)

        '    ' Get the HTML for the control.
        '    Me.gvAgentCommissions.RenderControl(hw)
        '    ' Write the HTML back to the browser.
        '    Response.Write(tw.ToString())
        '    ' End the response.
        '    Response.End()
        'Catch ex As Exception
        '    ' Response.Write("Going to Excel() Exception " + ex.Message)
        '    Dim s As String
        '    s = ex.Message
        'End Try


        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        ' Response.AddHeader("Content-Disposition", "inline;filename=Filename.xls")

        Response.AddHeader("Content-Disposition", "attachment; filename=ExcelFile.xls")

        Response.Charset = ""
        Me.EnableViewState = False
        Dim strStyle As String = "<style>.text { mso-number-format:\@; } </style>"
        Dim oStringWriter As New System.IO.StringWriter
        Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)
        oHtmlTextWriter.WriteLine(strStyle)
        Me.gvAgentCommissions.RenderControl(oHtmlTextWriter)
        Response.Write(oStringWriter.ToString())
        Response.End()

(Please ignore the response.write()'s that don't belong there )

[MMock]

I found this thread where others are having my exact problem but unfortunately there were lots of posts and no solution.
http://forums.asp.net/t/1221467.aspx

[MMock]

Oh brother.
http://support.microsoft.com/kb/948615

Thoughts?

I'm just worried I'll go through that process only to find it doesn't work, as others have posted.

[MMock]

As I said I am converting from ASP to .NET.

I have observed that the ASP application is behaving the same way as my new .NET code in regard to Excel 2007. On a machine with Office 2003, the data opens in Excel fine (but strangely it is embedded inside a web page, not in the Excel app, but let's not go there...)

So my users have been living with it all along (since Office 2007 came around). I am not introducing a new error message, but I'd like to be there hero that eliminates it!

mendhak and Besoup - what version of Office do you run?

[MMock]

Hi guys,

I've been working on a project that requires this code.
However, I need to store international phone codes and I've run across a problem.
For example, when I try to put in "+1-939", it will appear in Excel as "939".

I've checked to make sure the class="text" attribute is set on the cell within the datagrid so the text style will work with the code snippet I borrowed from you guys below.
I can change the value to "1-939" and that will store correctly. It looks like it only gets funky when I have a "+" sign in front of it.

Any ideas on what I can do for this?
Thanks!

How does what you said in any way contribute to this thread?

Could you please start your own thread?

[mendhak]

I found this thread where others are having my exact problem but unfortunately there were lots of posts and no solution.
http://forums.asp.net/t/1221467.aspx

Well, that thread seems to be about XLSX (Office 2007), but you want .XLS. Try any of these mime types:
application/vnd.ms-excel

			application/x-msexcel
			application/ms-excel
			application/msexcel
			application/x-excel

While you're at it,compare your code to this sample here:
http://www.developer.com/net/asp/article.php/3633891

To see if you've done anything differently. In fact, try his code out yourself to see if you get the same results.

[mendhak]

As I said I am converting from ASP to .NET.

I have observed that the ASP application is behaving the same way as my new .NET code in regard to Excel 2007. On a machine with Office 2003, the data opens in Excel fine (but strangely it is embedded inside a web page, not in the Excel app, but let's not go there...)

So my users have been living with it all along (since Office 2007 came around). I am not introducing a new error message, but I'd like to be there hero that eliminates it!

mendhak and Besoup - what version of Office do you run?

I use Office 2003 at home and 2007 at work.

So you're saying if the user has Office 2003 and they attempt to access your excel page, it does open Excel.exe on their machine but that remains blank and instead the data is shown in their browsers? Is it displayed as plain text in the browser or is it in an excel plug in?

Screenshots (with data blanked out) would help. Maybe we can help you be the hero, but no guarantees!

[MMock]

In the course of developing this entire page I had a couple issues. The code I wrote for this thread http://www.vbforums.com/showthread.p...67#post3218267 fixed my problem here.

Thanks for all your help.

And just to tie up loose ends - I did want xlsx. Sorry if I misled you, but it's probably buried somewhere in all these posts that it's Office 2007.

Enjoy your weekend. I feel like I'm very deserving of mine after today!

[mendhak]

No problem, always good to see an excel problem go away and not come back.