Export Excel

I was talking with Stephen about a project that requires an excel export and I remembered that Michelle from Student Affairs implemented a fix for their Duck Tracks application.
I sent her an email and here’s some of the great content of her response:

For a .net project, this is what we did:
Add the new doctypes for Office 2007 to IIS.
http://www.bram.us/2007/05/25/office-2007-mime-types-for-iis/
then we use a Gridview to display the data and use a button to call the function that exports the page

   Protected Sub BtnExportGrid_Click(ByVal sender As Object, ByVal args As EventArgs)
        '  pass the grid that for exporting ...
        GridViewExportUtil.Export("ComplianceGrid.xls", Me.GridView1)
    End Sub

Then here is the separate VB that is called by the GridViewExportUtil.Export

Imports System
Imports System.Data
Imports System.Configuration
Imports System.IO
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls

Public Class GridViewExportUtil

    Public Shared Sub Export(ByVal fileName As String, ByVal gv As GridView)
        HttpContext.Current.Response.Clear()
        HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", fileName))
        HttpContext.Current.Response.ContentType = "application/ms-excel"
        Dim sw As StringWriter = New StringWriter
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
        '  Create a form to contain the grid
        Dim table As Table = New Table
        table.GridLines = gv.GridLines
        '  add the header row to the table
        If (Not (gv.HeaderRow) Is Nothing) Then
            GridViewExportUtil.PrepareControlForExport(gv.HeaderRow)
            table.Rows.Add(gv.HeaderRow)
        End If
        '  add each of the data rows to the table
        For Each row As GridViewRow In gv.Rows
            GridViewExportUtil.PrepareControlForExport(row)
            table.Rows.Add(row)
        Next
        '  add the footer row to the table
        If (Not (gv.FooterRow) Is Nothing) Then
            GridViewExportUtil.PrepareControlForExport(gv.FooterRow)
            table.Rows.Add(gv.FooterRow)
        End If
        '  render the table into the htmlwriter
        table.RenderControl(htw)
        '  render the htmlwriter into the response
        HttpContext.Current.Response.Write(sw.ToString)
        HttpContext.Current.Response.End()
    End Sub

    ' Replace any of the contained controls with literals
    Private Shared Sub PrepareControlForExport(ByVal control As Control)
        Dim i As Integer = 0
        Do While (i < control.Controls.Count)
            Dim current As Control = control.Controls(i)
            If (TypeOf current Is LinkButton) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, LinkButton).Text))
            ElseIf (TypeOf current Is ImageButton) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, ImageButton).AlternateText))
            ElseIf (TypeOf current Is HyperLink) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, HyperLink).Text))
            ElseIf (TypeOf current Is DropDownList) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, DropDownList).SelectedItem.Text))
            ElseIf (TypeOf current Is CheckBox) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, CheckBox).Checked))
                'TODO: Warning!!!, inline IF is not supported ?
            End If
            If current.HasControls Then
                GridViewExportUtil.PrepareControlForExport(current)
            End If
            i = (i + 1)
        Loop
    End Sub
End Class

For PHP stuff try
http://www.google.com/search?q=PHP+export+to+excel+2007

Good stuff. Thanks Michelle.

Comments
  • Vid says:

    I talked with Travis the other day and he said he still used the tab delimited content (with the excel mime type and/or extension?) but he tells his customers to acknowledge the security warning, accept it and it opens just fine in excel 2003 and 2007.

  • Vid says:

    I just stumbled across this in the php docs and sent it over to Stephen. Something to put in your excel export tool kit:

    milin_mestry at yahoo dot com
    23-May-2008 07:54
    Hi,

    I was trying to save the ‘.xls’ on user machine, which works correctly on
    Firefox(developer friendly) but not on Microsoft’s IE(Microsoft friendly);

    after searching on the net, i found this code which works on both the browser.
    Source link: http://www.webdeveloper.com/forum/archive/index.php/t-30248.html

    here is the code:
    ————————-

    kenaniah at bestphp dot net
    15-May-2008 06:51
    If you are looking to send files such as PDFs or Excel spreadsheets or Microsoft Office documents and are having issues with IE7, IE6, or IE5.5 not being able to open/download the files over an SSL connection, but still need not allow caching, then set these two headers:

    Granted, this will cache your file for one second, but it’s as close to an un-cached download as you can get when using IE over SSL.

    Some basic info on the issue can be found here: http://support.microsoft.com/kb/812935

Leave a Comment