none
bulkcopy data from Vb.net to Excel RRS feed

  • Question

  •  

    Dear Friends,

     

    I need some help from you, how i copy bulk data from access table to excel sheet, i am using loop system, it is very slow, i appreciate if i get reply from you.

     

    Thanks

     

    Niaz

    Wednesday, December 26, 2007 1:41 PM

Answers

  • I would think that using a datareader to populate the excel file would be fairly fast.

    i wonder what kind of loop you are using, and i am guessing that you are automating excel???

     

    There is a program that will do what you want though

    It is called spreadsheet gear and it is free in your registration benefits portal

    it does not require you to have excel

     

    Here is a sample code to do a bulk copy

    The only thing is that it will copy everything into a datatable first

    so it will read it once, create the datatable and then bulk copy it

    so you will have two copies in memory

     

    it does seem it would be faster and more efficient to copy the data to a sheet using a datareader as it is being read - record by record.

     

     

    Note: you can also use spreadsheet gear with the datareader.  And in many ways it is faster and does not require you to open excel.  It just requires you to create a view and a spreadsheet in memory.  So it does not have another process running with excel in the background.

     

    Here is sample code

     

    Dim Conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\yourdatabase.mdb")

            Dim cmd As New OleDb.OleDbCommand("Select * from yourtable", Conn)

            Dim da As New OleDb.OleDbDataAdapter(cmd)

            Dim dt As New DataTable

            cmd.Connection.Open()

            da.Fill(dt)

     

            Dim sgview As New SpreadsheetGear.Windows.Forms.WorkbookView

            Dim sgbook As SpreadsheetGear.IWorkbook = sgview.ActiveWorkbook

            Dim sgsheet As SpreadsheetGear.IWorksheet = sgbook.Worksheets("Sheet1")

            Dim range As SpreadsheetGear.IRange = sgsheet.Cells("A1")

     

            sgview.GetLock()

            range.CopyFromDataTable(dt, SpreadsheetGear.Data.SetDataFlags.None)

            sgsheet.UsedRange.Columns.AutoFit()

            sgview.ActiveWorkbook.SaveAs("C:\TestSG.xls", SpreadsheetGear.FileFormat.XLS97)

            sgview.ReleaseLock()

     

     

    This is all done in memory
    Sunday, December 30, 2007 12:45 PM

All replies

  • If you have SQL Server installed, then you could use SQL DTS (in SQL Server 2000) or Integration Services (in SQL Server 2005), which allow transferring data in bulk between different data sources (they do not have to be SQL Server databases). If this is not the option, you could load data from Excel into DataSet using Jet OLEDB provider. After that you would need to use some sort of third-party component for fast export. I have created one couple of years ago and it works really fast, you could try it from my web site. Another option is to loop and insert records one-by-one. In this case Jet OLEDB provider is probably the fastest way.

    Thursday, December 27, 2007 8:11 PM
    Moderator
  • Dear VMazur,

     

    Thank you very much for help,

     

    i think this is not way of solution, i asked that i need help to transfer records access table to excel sheet  without loop, such as import, bulkcopy, copy or another way.

     

    Thanks & Regards,

    Niaz

     

    Sunday, December 30, 2007 7:02 AM
  • I would think that using a datareader to populate the excel file would be fairly fast.

    i wonder what kind of loop you are using, and i am guessing that you are automating excel???

     

    There is a program that will do what you want though

    It is called spreadsheet gear and it is free in your registration benefits portal

    it does not require you to have excel

     

    Here is a sample code to do a bulk copy

    The only thing is that it will copy everything into a datatable first

    so it will read it once, create the datatable and then bulk copy it

    so you will have two copies in memory

     

    it does seem it would be faster and more efficient to copy the data to a sheet using a datareader as it is being read - record by record.

     

     

    Note: you can also use spreadsheet gear with the datareader.  And in many ways it is faster and does not require you to open excel.  It just requires you to create a view and a spreadsheet in memory.  So it does not have another process running with excel in the background.

     

    Here is sample code

     

    Dim Conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\yourdatabase.mdb")

            Dim cmd As New OleDb.OleDbCommand("Select * from yourtable", Conn)

            Dim da As New OleDb.OleDbDataAdapter(cmd)

            Dim dt As New DataTable

            cmd.Connection.Open()

            da.Fill(dt)

     

            Dim sgview As New SpreadsheetGear.Windows.Forms.WorkbookView

            Dim sgbook As SpreadsheetGear.IWorkbook = sgview.ActiveWorkbook

            Dim sgsheet As SpreadsheetGear.IWorksheet = sgbook.Worksheets("Sheet1")

            Dim range As SpreadsheetGear.IRange = sgsheet.Cells("A1")

     

            sgview.GetLock()

            range.CopyFromDataTable(dt, SpreadsheetGear.Data.SetDataFlags.None)

            sgsheet.UsedRange.Columns.AutoFit()

            sgview.ActiveWorkbook.SaveAs("C:\TestSG.xls", SpreadsheetGear.FileFormat.XLS97)

            sgview.ReleaseLock()

     

     

    This is all done in memory
    Sunday, December 30, 2007 12:45 PM
  • The way that I did this is write the data to XML interpreted by Excel using a streamwriter (very fast).  Then I opened the new Excel XML file using office.interop.excel from the Com objects and used saveas to save the document as a bonafide Excel document.

    useful links:

    http://articles.techrepublic.com.com/5100-3513_11-6163451.html

    This one is primo -
    http://www.codeproject.com/KB/dotnet/ExportToExcel.aspx

    http://www.dynamicajax.com/fr/Creating_Excel_From_VB_DOT_NET-.html

    Frank

    Wednesday, January 9, 2008 3:14 PM