How do I save a DataSet to Excel in ASP.NET?

What if, in your ASP.NET application, you want the user to be able to retrieve data, export it to Excel, and then download the file? This presents a number of challenges. How do you save the data to Excel? How do you store it in a file on the web server? How do you give the user the option to download or view the file from their browser? How do you clean up old files?

Presented here is a quick and dirty ASP.NET/VB.NET way to do this. It is not the cleanest or best method but, depending on your needs, it works.

A few assumptions first:

Retrieved data will be stored in a Report folder which is a sub-folder off your web's root folder; The ASP process must have sufficient security privileges to write to this folder. Typically, it will not. The easiest way around this is to grant the IIS_WPG group, in IIS 6, modify access to this folder. With IIS 5, grant modify access to the ASPNET ID.

On to the code. We will get the current user's ID so we can store each user's data in files prefixed with their ID. This way, there is no collisions between users. Also, It makes cleaning up files easier so we don't litter the report folder with old junk.

Using ASP.NET's Request object we get the path to the report folder. Then, using the System.IO namespace, we create a DirectoryInfo structure and fill it with the names of all the user's old reports via a call to the GetFiles method. Armed with this, we can do some houscleaning and delete all the user's old files.

Now, to be safe, we can get a unique file name to insure we don't overwrite anything. The filename is created via a call to the System.IO.Path.GetTempFileName method. This returns a complete path including unique filename. The filename itself, without the extension is peeled from the result using the System.IO.Path.GetFileNameWithoutExtension method. To this, the user's ID and ".XLS" extension are added. The reason for the .XLS extension will be apparent shortly.

The actual data is then retrieved into a DataSet using whatever means your application uses to create DataSets. In this example, both the DataSet and Table Names are set to something more user friendly than the typical default values. When Excel opens the data, it will prepend the Table Name to every column. So the table name should be meaningful and short.

Once the data is retrieved into the DataSet, the DataSet's WriteXml method is invoked to write the data to the temporary file. As implied, it is written out as an XML document.

All that's left is to navigate to that file using Response.Redirect. Because the file was given an ".XLS" extension, the user will be presented with the standard File Download dialog where they can choose to open or save the file.

When the file is opened, it will be opened with Microsoft Excel due to its extension. Excel will automatically import the XML and display the data. However, since XML documents can contain many levels of nesting and Excel is only 2 dimensional, Excel will flatten the data. It does this by adding two additional columns: an #id column that associates a row with the parent table, and an #agg column that represents what can be considered a FillDown. With #agg, the value of the first node in the level above it is listed once and then omitted for remaining rows. You can read Microsoft Knowledge Base Article 288215 for more information.

Here is the VB.NET code

    Imports System.Security.Principal
    . . .

    Dim blnOpen As Boolean = False
    Dim strUniqueFn As String = ""

    Try
        ' Get the user id.
        Dim strUser As String = _
            Thread.CurrentPrincipal.Identity.Name.Substring( _
              Thread.CurrentPrincipal.Identity.Name.IndexOf("\") + 1).ToUpper()

        ' Get the folder to store files in.
        Dim strFolder As String = Request.MapPath(".") & "\report"

        ' Create a reference to the folder.
        Dim di As New IO.DirectoryInfo(strFolder)

        ' Create a list of files in the directory.
        Dim fi As IO.FileInfo() = di.GetFiles(strUser & "*.*")
        Dim i As Integer

        For i = 0 To fi.Length - 1
            IO.File.Delete(strFolder & "\" & fi(i).Name)
        Next

        ' Get a unique file name.
        strUniqueFn = strUser & _
        IO.Path.GetFileNameWithoutExtension(IO.Path.GetTempFileName()) & ".xls"

        ' Get the full path to the file.
        Dim strPath As String = strFolder & "\" & strUniqueFn

        ' Get the data for the report.
        Dim ds As DataSet = DAL.GetData()

        ' Tweak the dataset so it displays meaningful DataSet and Table Names.
        ds.DataSetName = "My_Report"
        ds.Tables(0).TableName = "Data"

        ' Write the data out as XML with an Excel extension.
        ds.WriteXml(strPath, System.Data.XmlWriteMode.IgnoreSchema)
        blnOpen = True

    Catch ex As Exception
        '...

    End Try

    ' Prompt the user to open or save the file.
    If blnOpen Then Response.Redirect("report/" & strUniqueFn)

As stated, this may not produce the cleanest Excel file. You can use a stylesheet to remove the added columns and format the data. See the above referenced MSDN article.