• Home
 · 
• Files/File Controls/Input/Output
 · 
• This code exports datatable to excel

This code exports datatable to excel

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



Rate This code exports datatable to excel

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

        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)