how to export to excel fatser than cell by cell writing with multiple datatable in dataset into the multiple sheets of workbook in vb.net

Question

I want to export multiple data tables of one data set into excel so that each data table put in separate sheet faster than cell by cell copy. my example code that i work is below. but is not working correctly.

please guide and give better solution.

Dim ds_temp As DataTable
    Dim exc As New Excel.Application
    If exc Is Nothing Then
        MsgBox("ERROR: EXCEL couldn't be started!", MsgBoxStyle.Critical, "error")
    Else
        exc.UserControl = True
        Dim oldci As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
        System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
        ''
        'Dim obook As Excel.Workbook = exc.Workbooks.Add 'Add Workbook to excel
        'Dim oSheet As Excel._Worksheet = obook.Worksheets(1)
        exc.Workbooks.Add()
        Dim obook As Excel.Sheets = exc.Workbooks(1).Worksheets
        Dim oSheet As Excel._Worksheet = CType(obook(1), Excel._Worksheet)
        ''
        'Dim style As Microsoft.Office.Interop.Excel.Style
        'style = obook.w.Styles.Add("Style1")
        'style.Font.Name = "Arial"
        'style.Font.Bold = True
        'style.Font.Size = 12
        'style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
        'style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft
        'style.NumberFormat = "@"
        ''
        If oSheet Is Nothing Then
            MsgBox("ERROR: worksheet == null", MsgBoxStyle.Critical, "0")
        Else
            Dim s = 1
            Dim l = 0
            Dim w = 0
            For Each ds_temp In ds.Tables
                If Not ds_temp Is Nothing Then
                    oSheet = CType(obook(s), Excel._Worksheet)
                    oSheet.DisplayRightToLeft = True
                    oSheet.Columns.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
                    oSheet.Columns.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    oSheet.Name = ds_temp.TableName.ToString
                    oSheet.Select()
                    ' oSheet.Columns.AutoFit()
                    'oSheet.Range("A1:X1").EntireColumn.AutoFit()
                    Dim k = 0
                    'oSheet = .Worksheets(l + 1)
                    'oSheet.Name = dg_temp.Name.Remove(0, 3)
                    'oSheet.DisplayRightToLeft = True
                    'oSheet.Activate()
                    'oSheet.Cells.Style = style
                    'obook.Worksheets(l + 1).Select()


                    For Each Dc As DataColumn In ds_temp.Columns
                        k += 1

                        oSheet.Cells(1, k).Value = Dc.ColumnName.ToString.Trim
                        oSheet.Cells(1, k).Font.Bold = True

                    Next
                    Try
                        For i As Integer = 0 To ds_temp.Rows.Count - 1
                            For j As Integer = 0 To ds_temp.Columns.Count - 1


                                Try
                                    oSheet.Cells(i + 2, j + 1) = ds_temp.Rows(i).Item(j).ToString.Trim
                                Catch ex As Exception
                                    oSheet.Cells(i + 2, j + 1) = String.Empty
                                End Try

                            Next
                        Next
                    Catch ex As Exception
                        MsgBox(ex.ToString, MsgBoxStyle.Critical, "2-Office ")
                        Return False
                    End Try
                End If
                oSheet.Columns.ColumnWidth = 30
                s = s + 1
            Next
            ' oSheet.Range("A1").ColumnWidth = 30
            exc.Visible = True
            exc.Workbooks(1).SaveAs(PathForSavaFile & "" & FileName)
            'exc.SaveWorkspace(PathForSavaFile & "" & FileName)
            '.SaveAs(PathForSavaFile & "" & FileName)
            System.Threading.Thread.CurrentThread.CurrentCulture = oldci
            oSheet = CType(obook(1), Excel._Worksheet)
            oSheet.Select()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(exc)
            frm.Cursor = Cursors.Default
            Return True

        End If

Answer 1

Instead of iterating through all rows and columns, try it this way.

Protected Sub ExportExcel(sender As Object, e As EventArgs)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim query As String = "SELECT TOP 10 ContactName, City, Country FROM Customers;"
    query &= "SELECT TOP 10 (FirstName + ' ' + LastName) EmployeeName, City, Country FROM Employees"
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand(query)
            Using sda As New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using ds As New DataSet()
                    sda.Fill(ds)

                    'Set Name of DataTables.
                    ds.Tables(0).TableName = "Customers"
                    ds.Tables(1).TableName = "Employees"

                    Using wb As New XLWorkbook()
                        For Each dt As DataTable In ds.Tables
                            'Add DataTable as Worksheet.
                            wb.Worksheets.Add(dt)
                        Next

                        'Export the Excel file.
                        Response.Clear()
                        Response.Buffer = True
                        Response.Charset = ""
                        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                        Response.AddHeader("content-disposition", "attachment;filename=DataSet.xlsx")
                        Using MyMemoryStream As New MemoryStream()
                            wb.SaveAs(MyMemoryStream)
                            MyMemoryStream.WriteTo(Response.OutputStream)
                            Response.Flush()
                            Response.End()
                        End Using
                    End Using
                End Using
            End Using
        End Using
    End Using
End Sub

http://www.aspsnippets.com/Articles/Export-DataSet-DataTables-to-multiple-Excel-Sheets-Worksheets-in-ASPNet-using-C-and-VBNet.aspx