VB ASP.Net Export to Excel: Name worksheet tab

Question

Below is the method I'm using to export my gridview data to Excel. The user has asked if I can name the worksheet tab. Any ideas? Thanks in advance!

Private Sub btnExportToExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click        
    Dim form As New HtmlForm
    Dim strAttachment As String
    Dim stw As StringWriter
    Dim htextw As HtmlTextWriter

        stw = New StringWriter
        strAttachment = "attachment; filename=" & strAppName & ".xls"
        HttpContext.Current.Response.ClearContent()
        HttpContext.Current.Response.AddHeader("content-disposition", strAttachment)
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"
        htextw = New HtmlTextWriter(stw)
        form.Controls.Add(CType(Session("gridViewControl"), Control))
        Me.Controls.Add(form)
        form.RenderControl(htextw)
        Response.Write("<b>" & txtTitle.Text & "</b><br />")
        Response.Write(stw.ToString())            
        Response.Flush()
        Response.Close()
        HttpContext.Current.ApplicationInstance.CompleteRequest()

End Sub

score 1 · Answer 1 · answered Apr 25 '12 at 15:14

Its not possible because your output is really HTML with an HTML Table - which Excel will happily "parse"/translate into rows and columns (because that's what a table is). You're not really creating a "native" Excel file.

You can have more options (assuming all your clients do have Excel) by using Excel XML using native .Net (LINQ to XML, don't be scared by that term). I believe Excel XML support goes back to Office 2003 (maybe even Office XP/2002, but I could be wrong).

You can do so in C# or VB.net, but in VB.net it's almost trivial because of VB.Net's XML literals. See this MSDN video by Beth Massi for inspiration (magic starts around 5:00, but the entire video is worth every minute).

score 0 · Answer 2 · answered Apr 25 '12 at 14:55

0

No, there is no way to be able to do that using this method. You would have to use a specialized library like Gembox to do this.

answered Apr 25 '12 at 14:55

Jeff

13,943
11
55
103

score 0 · Answer 3 · edited May 23 '17 at 10:28

0

You can't do it via html, but you can do it via xml (SpreadsheetML). Take a look at the sample I posted in the answer here:
Generating an Excel file in ASP.NET

edited May 23 '17 at 10:28

Community

1
1

answered Apr 25 '12 at 18:55

Joel Coehoorn

399,467
113
570
794

VB ASP.Net Export to Excel: Name worksheet tab

3 Answers3