Export Data to Excel File not in HTML Table Format using VB.NET

If you would like to see how to export data to Excel file not in HTML table using C#, click here.

There are a few ways to export data to Excel file, either in HTML Table or in Excel’s original table. Previously, I use the method that export data to Excel file in HTML Table (with extension .xls). However, I faced with troubles when I want to re-import the Excel File using System.Data.OleDb method (OleDbConnection, OleDbDataAdapter, etc).

Firstly, I set the connection string like below:
strConn = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source= “ & strFilePath & “; Extended Properties=Excel 8.0;”
With this totally cannot import Excel file in HTML Table.

Secondly, I tried to change the Extended Properties=HTML Import, the connection string is like below:
strConn = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source= “ & strFilePath & “; Extended Properties=HTML Import;”
With this, the Excel file in HTML Table can be imported. However, when I add in new row record manually to that file, save it and re-import it; the new row record I manually added is not be selected. This is because this method only will select the records in HTML Table but the new record I added is not in HTML Table (is in Excel’s original table).

Therefore, what I can do to solve this problem is to change the method I export data to Excel file. I need to find another way to export data to Excel file that not in HTML Table. I had searched around internet and finally I had found the way to export data into the actual Excel table format that using Exel.Application.

Firstly, you need to add a component known as Excel.dll (Microsoft excel 11.0 Object Library) into your .NET project references. The codes of the function to export data to Excel file is like below:-

Private Funtion ExportToExcelFile(ByVal dt As System.Data.DataTable)
   Dim xlsApp As New Excel.Application
   Dim xlsWorkbook As Excel.Workbook
   Dim xlsWorksheet As Excel.Worksheet
   Dim strhdr As String
   Dim row As Integer
   Dim dr As DataRow
   Dim strFile As String = "file1.xls"
   Dim filename As String = Server.MapPath(strFile)

   If dt.Rows.Count > 0 Then
      'Create new workbook
      xlsWorkbook = xlsApp.Workbooks.Add

      'Get the first worksheet
      xlsWorksheet = CType(xlsWorkbook.Worksheets(1), Excel.Worksheet)

      'Activate current worksheet
      xlsWorksheet.Activate()

      'Set header row to row 1
      row = 1

      'Add table headers to worksheet
      xlsWorksheet.Cells(row, 1).Value = "Name"
      xlsWorksheet.Cells(row, 2).Value = "Gender"
      xlsWorksheet.Cells(row, 3).Value = "Age"

      'Format header row (bold, extra row height, autofit width)
      With xlsWorksheet.Range("A" & row, "C" & row)
         .Font.Bold = True
         .Rows(row).RowHeight = 1.5 * xlsWorksheet.StandardHeight
         .EntireRow.AutoFit()
      End With

      'Freeze the column headers
      With xlsWorksheet.Range("A" & row + 1, "C" & row + 1).Select
         xlsApp.ActiveWindow.FreezePanes = True
      End With

      'Write data to Excel worksheet
      For Each dr In dt.Rows
         row += 1
         If Not IsDBNull(dr.Item("Name")) Then xlsWorksheet.Cells(row, 1).Value = dr.Item("Name")
         If Not IsDBNull(dr.Item("Gender ")) Then xlsWorksheet.Cells(row, 2).Value = dr.Item("Gender")
         If Not IsDBNull(dr.Item("Age")) Then xlsWorksheet.Cells(row, 3).Value = dr.Item("Age")
      Next

      'Format data rows (align to center and left, autofit width and height)
      With xlsWorksheet.Range("A2", "C" & row)
         .VerticalAlignment = CType(XlVAlign.xlVAlignCenter, Excel.XlVAlign)
         .HorizontalAlignment = CType(XlHAlign.xlHAlignLeft, Excel.XlHAlign)
         .EntireColumn.AutoFit()
         .EntireRow.AutoFit()
      End With

      'Make excel workbook visible to user after all data has been added to worksheet.
      xlsApp.DisplayAlerts = False
      xlsWorkbook.Close(True, filename)

      'Export data to client machine
      strhdr = "attachment;filename=" & strFile
      With Response
         .Clear()
         .ContentType = "application/vnd.ms-excel"
         .ContentEncoding = System.Text.Encoding.Default
         .AppendHeader("Content-Disposition", strhdr)
         .WriteFile(filename)
         .Flush()
         .Clear()
         .Close()
      End With
   End If
End Function