HTML Export to Excel
In your many applications you might have needed a utility which will help the users to export data to Excel. For HTML export to Excel, the key is using "application/vnd.ms-excel" for the content type of the response. In your ASP or ASP.Net applications you may use the same methods. I'm going to try to create a sample ASP.NET application for ASP.Net export to Excel application.
I had the data which will be exported to excel from an sql database. You can change and edit the connection string for a valid sql database server and a valid database in the sql server. You should also edit the username and password for sql connection.
My connection string for the data source that will be exported to excel is
Dim sqlConnStr As String = "workstation id=pcSQL;packet size=4096;user=sqladmin;password=sqladmin;data
source=""."";persist security info=False;initial catalog=dbSampleDatabase"
I will display the data from sql database on a datagrid DataGrid1 on the Page_Load event of the page. The data that will be displayed on the datagrid will be fetched from the database using the sql query "SELECT Name FROM cs_Content (NoLock)". This sample ExcelSample application will export datagrid data to excel.
Data from database is saved into a dataset and the dataset is converted into a string by using a StringBuilder in an HTML format. And the final HTML is exported to excel in the Button1_Click event.
If you download the sample project which I have added the download link at the end of the article, you will see the below code statements which form an HTML document.
resultXML.Append("<HTML><BODY>")
resultXML.Append("<TABLE>")
resultXML.Append("<TR>")
For Each objColumn In ds.Tables(0).Columns
ColumnName = objColumn.ColumnName
resultXML.Append("<TD>")
resultXML.Append(ColumnName)
resultXML.Append("</TD>")
Next
resultXML.Append("</TR>")
Dim i As Int32
For i = 0 To ds.Tables(0).Rows.Count - 1
resultXML.Append("<TR>")
For Each objColumn In ds.Tables(0).Columns
ColumnName = objColumn.ColumnName
ColumnValue = CType(ds.Tables(0).Rows(i)(ColumnName), String)
resultXML.Append("<TD>" & ColumnValue & "</TD>")
Next
resultXML.Append("</TR>")
Next
resultXML.Append("</TABLE>")
resultXML.Append("</BODY></HTML>")
The resultXML StringBuilder object has the html data for export to Excel.
The below codes clears the buffer and sets the content type to "application/vnd.ms-excel" for exporting to excel process. And ContentEncoding property is set to System.Text.Encoding.UTF7 for language support where character problems occur after data is exported to Excel.
Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Response.ContentEncoding = System.Text.Encoding.UTF7
Response.Write(resultXML.ToString)
With the Write method of the Response object html data is sent to client and exported to Excel.
You will find the sample Visual Studio .NET 2003 project ExcelSample in the files section of the Kodyaz. The download link for the sample asp.net project is HTML Export to Excel Sample ASP.NET Application