In application development generally during report generation Customer wants to add a button which can create an excel file from the filtered report grid. Recently during I worked for a filter page where I wrote a function (Export2Excel) related to this export excel. The environment where I wrote the function is in VB.NET. About the function Export2Excel() it accepts 2 parameters. SQL Query to extract data from the database and Name of the Excel file which you want to create. Look at the function below.
Under .NET Framework normally to fetch data from a database to code-behind we use SqlDataAdapter. SqlDataAdapter accepts 2 parameters. First one is SQL Query and database Connection String. Using popular data adapter Fill method here I am binding data from adapter to data table.
Export SQL Query to Excel Function
Private Sub Export2Excel(ByVal SqlQry As String, ByVal FileName As String) Response.ClearContent() Response.ContentType = "Application/vnd.ms-excel" Response.AddHeader("Content-Disposition", "attachment; filename=" & FileName & "") Response.Flush() Dim SqlDataAdp As New SqlDataAdapter(SqlQry, sqlConn) Dim DT As New DataTable SqlDataAdp.Fill(DT) sqlConn.Open() Dim tempTable As String = String.Empty Dim DTCol As DataColumn Dim DTRow As DataRow tempTable = "<table cellpadding='2' cellspacing='2' border='1' width='100%'>" tempTable = tempTable & "<tr>" For Each DTCol In DT.Columns tempTable = tempTable & "<td valign='top' style='font-weight:bold'>" tempTable = tempTable & DTCol.ColumnName.ToString() tempTable = tempTable & "</td>" Next tempTable = tempTable & "</tr>" For Each DTRow In DT.Rows tempTable = tempTable & "<tr>" For i = 0 To DT.Columns.Count - 1 tempTable = tempTable & "<td valign='top' align='left'>" tempTable = tempTable & DTRow(i).ToString() tempTable = tempTable & "</td>" Next tempTable = tempTable & "</tr>" Next tempTable = tempTable & "</table>" Response.Write(tempTable) Response.End() End Sub
In the above function I am fetching SQL data using a Data Adapter. Named as SqlDataAdp. Then assigning data from Data Adapter to a Data Table. Using a variable tempTable I am creating the tabular structure of data from data table. To do this here I used for loops for data table columns and rows. To run the function I am calling it from the button click event of “Export to Excel” button.
btnExportToExcel Click
Protected Sub btnExportToExcel_Click(ByVal sender As Object, ByVal e AsSystem.EventArgs) Handles btnExportToExcel.Click Export2Excel("SELECT * FROM EmployeeTable", "Employees.xls") End Sub
Set the connectiong String in your web.config file as below.
<connectionStrings> <add name="sqlConn" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\gadgetDatabase.mdf;Integrated Security=True" /> </connectionStrings>
You must noticed here I added below 3 lines inside my button click event. The first line Response.ClearContent() helps to create a new excel file. If there is a existing file on same name it helps to clear the file before append a new table.
Response.ClearContent() Response.ContentType = "Application/vnd.ms-excel" Response.AddHeader("Content-Disposition", "attachment; filename=" & FileName & "")
Response.ContentType = “Application/vnd.ms-excel” this line helps to generate the file type excel. Without using this line of Code its not possible to Create an excel file using VB.NET. AddHeader helps to write file header information.