In application development generally during report generation Customer wants to add a button which can create an excel file from the filtered report data. Recently during I worked for a filter page here I wrote a function (Export2Excel) related to this issue. The environment where I wrote the function is VB.NET. About the function Export2Excel() it accepts 2 parameters SQL Query & Name of the Excel file you want to create. Look at the function below.
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 (SqlDataAdp). Then assigning data from Data Adapter to Data Table. Using a variable tempTable I am creating the tabular format of data from data table. To do this here I used for loops for data table columns & 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