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
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
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
. . .
Dim blnOpen As Boolean = False
Dim strUniqueFn As String = ""
' Get the user id.
Dim strUser As String = _
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)
' 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.
blnOpen = True
Catch ex As Exception
' 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.