by Robert Banks (1 Submission)
Category: Files/File Controls/Input/Output
Compatability: VB.NET
Difficulty: Unknown Difficulty
Originally Published: Wed 11th October 2006
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
This code exports datatable to excel
API Declarations
Dim obook As Excel.Workbook = oExcel.Workbooks.Add 'Add Workbook to excel
Dim oSheet As Excel.Worksheet = obook.Worksheets(1) 'Declare Excel Worksheet
Dim dataArray(ds_main.Tables(Export_Table).rows.count - 1, ds_main.Tables(Export_Table).columns.Count - 1) As Object ' Declare Array object with size (row count by column count)
Dim r As Integer
Dim c As Integer
Dim column_count As Integer
Dim ROW_COUNT As Integer
Dim excel_count As Integer
Dim sheet_count As Integer
VBcoders
sheet_count = 1 ' set sheet count to 1 (excel sheet integer starts at 1 not 0
ROW_COUNT = 0 ' set array row count to 0
oSheet.Name = "Page " & sheet_count ' name the first sheet Page 1
'Add columns to Sheet
For column_count = 0 To ds_main.Tables(Export_Table).Columns.Count - 1
oSheet.Cells(1, column_count + 1) = ds_main.Tables(Export_Table).Columns(column_count).ColumnName
Next column_count
ProgressBar.Value = 0
ProgressBar.Maximum = ds_main.Tables(Export_Table).Rows.Count - 1
For r = 0 To ds_main.Tables(Export_Table).Rows.Count - 1 'For each row in dataset
For c = 0 To ds_main.Tables(Export_Table).columns.Count - 1 'for each column
dataArray(ROW_COUNT, c) = ds_main.Tables(Export_Table).Rows(r).Item(c) 'add column data into array
Next c
If excel_count = 65000 Then 'record count is 65000
oSheet.Range("A2").Resize(excel_count, ds_main.Tables(Export_Table).columns.Count - 1).Value = dataArray 'Copy Array into excel sheet
oSheet = obook.Worksheets.Add(After:=obook.Worksheets("Page " & sheet_count)) 'add new sheet to workbook after previous sheet
sheet_count += 1 'increment sheet count by one
oSheet.Name = "Page " & sheet_count 'name new sheet
excel_count = 1 'reset to excel count to 1
ROW_COUNT = 0 'reset the row count for array to 0
'Array.Clear(dataArray, 0, 65000)
'reset the array
ReDim dataArray(ds_main.Tables(Export_Table).rows.count - 1, ds_main.Tables(Export_Table).columns.Count - 1)
'Add column headers to new sheet
For column_count = 0 To ds_main.Tables(Export_Table).Columns.Count - 1
oSheet.Cells(1, column_count + 1) = ds_main.Tables(Export_Table).Columns(column_count).ColumnName
Next column_count
Else 'if records not equal to 65K
excel_count += 1 'Add 1 to excel count
ROW_COUNT += 1 'Add 1 to array row count
End If
If r = ds_main.Tables(Export_Table).rows.count - 1 Then 'If this is the final record
oSheet.Range("A2").Resize(excel_count, ds_main.Tables(Export_Table).columns.Count - 1).Value = dataArray 'copy array to excel sheet
End If
ProgressBar.PerformStep()
Next r
obook.SaveAs(FileName) 'Save the excel sheet
oSheet = Nothing 'set objects to nothing
oExcel = Nothing
GC.Collect() 'Garabarage Collection to reclaim space
ProgressBar.Value = 0
Exporting_Label.Visible = False
MessageBox.Show("Export Complete", "Finish", MessageBoxButtons.OK, MessageBoxIcon.Information)
