using vb.net export a datatable to Excel and save as file

Export a datatable to Excel and save as file

Dim CurrentCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture

System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")

If (tmpDataTable Is Nothing) Then

Return

End If

Dim rowNum As Integer = tmpDataTable.Rows.Count

Dim columnNum As Integer = tmpDataTable.Columns.Count

Dim rowIndex As Integer = 1

Dim columnIndex As Integer = 0

Dim xlApp As Excel.Application = New ApplicationClass()

xlApp.DefaultFilePath = ""

xlApp.DisplayAlerts = True

xlApp.SheetsInNewWorkbook = 1

Dim xlBook As Workbook = xlApp.Workbooks.Add(True)

'将DataTable的列名导入Excel表第一行

Dim dc As DataColumn

For Each dc In tmpDataTable.Columns

columnIndex = columnIndex + 1

xlApp.Cells(rowIndex, columnIndex) = dc.ColumnName

Next

'将DataTable中的数据导入Excel中

Dim i As Integer = 0

For i = 0 To rowNum - 1

rowIndex = rowIndex + 1

columnIndex = 0

Dim j As Integer = 0

For j = 0 To columnNum - 1

columnIndex = columnIndex + 1

xlApp.Cells(rowIndex, columnIndex) = Convert.ToString(tmpDataTable.Rows(i)(j))

Next

Next

'xlBook.SaveCopyAs(HttpUtility.UrlDecode(strFileName, System.Text.Encoding.UTF8));

xlBook.SaveCopyAs(strFileName)

End