Excel is very common practice to share data across Network. Recently one of my Customer wants to provide export an excel button in their products list page. What I did to implement Export DataTable to Excel functionality sharing the same here.
Exporting data from a DataTable to an Excel file is a common requirement in many business applications. Whether for reporting, data analysis, or sharing information, automating this process can save time and reduce errors. VB.NET, with its robust libraries and straightforward syntax, provides multiple ways to accomplish this task. This article explores various methods to export a DataTable to an Excel file, including using Microsoft Office Interop, OpenXML, and third-party libraries like EPPlus.
Prerequisites
Before proceeding, ensure that you have the following:
1. Visual Studio installed on your machine.
2. Microsoft Excel available, as the export will be to the Excel format.
3. Microsoft Office Interop for Excel or a third-party library such as EPPlus or ClosedXML.
I added a asp.net server side button with text “Export to Excel”. Then inside this button click event I am calling a function Export2Excel. Export2Excel function accepts 2 parameters Select Query & output File Name. Inside the function first I am clearing response object. Then defining Content Type & File Header information. Then fetching data in SQLDataAdapter & Cloning the same data to DataTable. To Create table running loop over TR. According to requirement showing the number of TD’s. Finally writing the complete string using response.write.
Button Click
Protected Sub btnExportToExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click
If Request("Keyword") = 1 Then
Export2Excel("SELECT SearchString, DateOfSearch FROM SearchString WHERE FoundNotFound=1 ORDER BY DateOfSearch DESC", "SearchStrings.xls")
Else
Export2Excel("SELECT SearchString, DateOfSearch FROM SearchString WHERE FoundNotFound=0 ORDER BY DateOfSearch DESC", "SearchStrings.xls")
End If
End Sub
Export DataTable 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 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 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
Best Practices
1. Validation: Always validate the DataTable before exporting. Check for null or empty values that might cause issues when exporting.
2. File Overwrite: Be cautious with file paths to prevent unintentional overwrites. Consider prompting the user or implementing a versioning system for exported files.
3. Performance: For large DataTables, consider using asynchronous programming to avoid freezing the UI in a Windows Forms application.
4. Testing: Thoroughly test the export functionality to handle various data types and sizes to ensure robustness.
Conclusion
Exporting a DataTable to Excel in VB.NET can be achieved through multiple methods, each with its advantages. Microsoft Office Interop is feature-rich but requires Excel installation, OpenXML is lightweight but complex, and EPPlus offers a balanced approach with easy implementation. Choose the method that best fits your project requirements to ensure efficient and reliable Excel exports.