Bulk insert to excel from vb.net
-
hi, pls anyone try to solve my problem... i need to fetch some thousands of records and insert/export that to excel file using vb.net... Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim cnt As New ADODB.Connection Dim rst As New ADODB.Recordset Dim xlApp As Object Dim xlWb As Object Dim xlWs As Object Dim recArray As Object Dim strDB As String Dim fldCount As Integer Dim recCount As Long Dim iCol As Integer Dim iRow As Integer strDB = "UNOGI\UNO;database=Test;uid=sa;pwd=welcome3#" 'here am getting the following error "Could not find installable ISAM." cnt.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDB & ";") rst.Open("select top 10000 * from Test.dbo.CustomerDetail", cnt) xlApp = CreateObject("Excel.Application") xlWb = xlApp.Workbooks.Add xlWs = xlWb.Worksheets("Sheet1") xlApp.Visible = True xlApp.UserControl = True fldCount = rst.Fields.Count For iCol = 1 To fldCount xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name Next If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then xlWs.Cells(2, 1).CopyFromRecordset(rst) Else recArray = rst.GetRows recCount = UBound(recArray, 2) + 1 For iCol = 0 To fldCount - 1 For iRow = 0 To recCount - 1 If IsDate(recArray(iCol, iRow)) Then recArray(iCol, iRow) = Format(recArray(iCol, iRow)) ElseIf IsArray(recArray(iCol, iRow)) Then recArray(iCol, iRow) = "Array Field" End If Next iRow Next iCol ' Transpose and Copy the array to the worksheet, ' starting in cell A2 xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _ TransposeDim(recArray) End If ' Auto-fit the column widths and row heights xlApp.Selection.CurrentRegion.Columns.AutoFit() xlApp.Selection.CurrentRegion.Rows.AutoFit() ' Close ADO objects rst.Close() cnt.Close() rst = Nothing cnt = Nothing ' Release Excel references xlWs = Nothing xlWb = Nothing xlApp = Nothing End Sub
-
hi, pls anyone try to solve my problem... i need to fetch some thousands of records and insert/export that to excel file using vb.net... Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim cnt As New ADODB.Connection Dim rst As New ADODB.Recordset Dim xlApp As Object Dim xlWb As Object Dim xlWs As Object Dim recArray As Object Dim strDB As String Dim fldCount As Integer Dim recCount As Long Dim iCol As Integer Dim iRow As Integer strDB = "UNOGI\UNO;database=Test;uid=sa;pwd=welcome3#" 'here am getting the following error "Could not find installable ISAM." cnt.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDB & ";") rst.Open("select top 10000 * from Test.dbo.CustomerDetail", cnt) xlApp = CreateObject("Excel.Application") xlWb = xlApp.Workbooks.Add xlWs = xlWb.Worksheets("Sheet1") xlApp.Visible = True xlApp.UserControl = True fldCount = rst.Fields.Count For iCol = 1 To fldCount xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name Next If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then xlWs.Cells(2, 1).CopyFromRecordset(rst) Else recArray = rst.GetRows recCount = UBound(recArray, 2) + 1 For iCol = 0 To fldCount - 1 For iRow = 0 To recCount - 1 If IsDate(recArray(iCol, iRow)) Then recArray(iCol, iRow) = Format(recArray(iCol, iRow)) ElseIf IsArray(recArray(iCol, iRow)) Then recArray(iCol, iRow) = "Array Field" End If Next iRow Next iCol ' Transpose and Copy the array to the worksheet, ' starting in cell A2 xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _ TransposeDim(recArray) End If ' Auto-fit the column widths and row heights xlApp.Selection.CurrentRegion.Columns.AutoFit() xlApp.Selection.CurrentRegion.Rows.AutoFit() ' Close ADO objects rst.Close() cnt.Close() rst = Nothing cnt = Nothing ' Release Excel references xlWs = Nothing xlWb = Nothing xlApp = Nothing End Sub
Sounds like a problem with your connection string... Check out http://www.connectionstrings.com/[^] and find the one that suits you best and try again!!
I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!
-
Sounds like a problem with your connection string... Check out http://www.connectionstrings.com/[^] and find the one that suits you best and try again!!
I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!
-
You've got the only way to do it. Also, be careful you're not adding so many rows of data to a worksheet that Excel runs out of memory. You also didn't say what the error message was.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008
But no longer in 2009... -
hi, pls anyone try to solve my problem... i need to fetch some thousands of records and insert/export that to excel file using vb.net... Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim cnt As New ADODB.Connection Dim rst As New ADODB.Recordset Dim xlApp As Object Dim xlWb As Object Dim xlWs As Object Dim recArray As Object Dim strDB As String Dim fldCount As Integer Dim recCount As Long Dim iCol As Integer Dim iRow As Integer strDB = "UNOGI\UNO;database=Test;uid=sa;pwd=welcome3#" 'here am getting the following error "Could not find installable ISAM." cnt.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDB & ";") rst.Open("select top 10000 * from Test.dbo.CustomerDetail", cnt) xlApp = CreateObject("Excel.Application") xlWb = xlApp.Workbooks.Add xlWs = xlWb.Worksheets("Sheet1") xlApp.Visible = True xlApp.UserControl = True fldCount = rst.Fields.Count For iCol = 1 To fldCount xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name Next If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then xlWs.Cells(2, 1).CopyFromRecordset(rst) Else recArray = rst.GetRows recCount = UBound(recArray, 2) + 1 For iCol = 0 To fldCount - 1 For iRow = 0 To recCount - 1 If IsDate(recArray(iCol, iRow)) Then recArray(iCol, iRow) = Format(recArray(iCol, iRow)) ElseIf IsArray(recArray(iCol, iRow)) Then recArray(iCol, iRow) = "Array Field" End If Next iRow Next iCol ' Transpose and Copy the array to the worksheet, ' starting in cell A2 xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _ TransposeDim(recArray) End If ' Auto-fit the column widths and row heights xlApp.Selection.CurrentRegion.Columns.AutoFit() xlApp.Selection.CurrentRegion.Rows.AutoFit() ' Close ADO objects rst.Close() cnt.Close() rst = Nothing cnt = Nothing ' Release Excel references xlWs = Nothing xlWb = Nothing xlApp = Nothing End Sub
Try to use Spire.DataExport - it is c# data export component that supports data export into MS Excel,MS Word, HTML, XML, PDF, MS Access, DBF, SQL Script, SYLK, DIF, CSV ,MS Clipboard format.