SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software

Kodyaz Development Resources

Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.






.NET Framework, ASP.NET

ASP.NET, Windows Forms, Controls, .NET Framework and Visual Studio Articles

ASP.NET Forums

Visual Studio Forums

.NET Development Blog

Certification Exams Blog




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












Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems