Solved

Speed Up Write To Excel From VB.NET

Posted on 2010-11-11
8
1,044 Views
Last Modified: 2012-08-22
How can I speed up writing to an Excel file from VB.NET? I run a db query, and I want to populate the results in an Excel sheet.Currently my code takes about 10 minutes to run for a database with 21K rows.

Here's what I'm doing currently... write statements for each value. How can I speed this up?

For i As Integer = 0 To ds1.Tables(0).Rows.Count - 1
            oBook.Sheets(1).Cells(i + 2, 1).Value = ds1.Tables(0).Rows(i).ItemArray(0)
            oBook.Sheets(1).Cells(i + 2, 2).Value = ds1.Tables(0).Rows(i).ItemArray(1)
            oBook.Sheets(1).Cells(i + 2, 3).Value = ds1.Tables(0).Rows(i).ItemArray(2)
            oBook.Sheets(1).Cells(i + 2, 4).Value = ds1.Tables(0).Rows(i).ItemArray(3)
            oBook.Sheets(1).Cells(i + 2, 5).Value = CDbl((ds1.Tables(0).Rows(i).ItemArray(2) + ds1.Tables(0).Rows(i).ItemArray(3)) / 2)
            oBook.Sheets(1).Cells(i + 2, 7).Value = ds1.Tables(0).Rows(i).ItemArray(4)
        Next

Open in new window

0
Comment
Question by:diablo089
  • 3
  • 3
  • 2
8 Comments
 
LVL 13

Expert Comment

by:gbanik
ID: 34113420
The easiest thing u can do during a write for a significant change are the following:

oExcel.ScreenUpdating=false
oExcel.EnableEvents=false

0
 
LVL 13

Expert Comment

by:gbanik
ID: 34113481
By the way, the fastest for a data dump still is CopyFromRecordset. Use a ADODB.Recordset to do so to dump data into a range.


http://support.microsoft.com/kb/306022
'Create a Recordset from all the records in the Orders table.
        Dim sNWind As String
        Dim conn As New ADODB.Connection()
        Dim rs As ADODB.Recordset
        conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            sNorthwind & ";")
        conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        rs = conn.Execute("Orders", , ADODB.CommandTypeEnum.adCmdTable)

        'Create a new workbook in Excel.
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object
        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Add
        oSheet = oBook.Worksheets(1)

        'Transfer the field names to Row 1 of the worksheet:
        'Note: CopyFromRecordset copies only the data and not the field
        '      names, so you can transfer the fieldnames by traversing the
        '      fields collection.
        Dim n As Int32
        For n = 1 To rs.Fields.Count
            oSheet.Cells(1, n).Value = rs.Fields(n - 1).Name
        Next

        'Transfer the data to Excel.
        oSheet.Range("A2").CopyFromRecordset(rs)

        'Save the workbook and quit Excel.
        oBook.SaveAs(sSampleFolder & "Book3.xls")
        oSheet = Nothing
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()

        'Close the connection
        rs.Close()
        conn.Close()

Open in new window

0
 

Author Comment

by:diablo089
ID: 34113942
Do you know which namespace holds the ADODB definitions? As in what I should Import?
0
 
LVL 15

Accepted Solution

by:
x77 earned 250 total points
ID: 34113950
Some time ago I put a response on EE to similiar question.
http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_26162431.html

I use a big two dimension array to send data to excel.
Interop slow-down the comunication with Excel, Word, Outlook ...
I send 8000 rows x 10 columns with an array, it takes about .1 seconds.

In my code, the function

    Public Sub Value(ByVal Pos As String, ByVal Datos(,) As Object)
        Wks.Range(Pos).Resize(Datos.GetLength(0), Datos.GetLength(1)).Value = Datos
    End Sub

Assigns the array to a Range in Excel.

Using x As New ExpExcel

      With x.Wks.Range("A1.L1")

         .Font.Bold = True : .Font.Color = ColorTranslator.ToWin32(Color.Blue)

         .Interior.Color = ColorTranslator.ToWin32(Color.LightCyan)

      End With

      x.Column("A", 13)                               'Trabajo

      x.Column("B", 5)                                'Sup

      x.Column("C:E", 8, , Excel.XlHAlign.xlHAlignRight) 'Fimte,Ffmte,Dur

      x.Column("F", 4)                                'Av

      x.Column("G", 100)                              'Destra



      x.Value("A1", Datos) : Datos = Nothing

  End Using







Imports System.Runtime.InteropServices.Marshal

Friend Class ExpExcel

    Implements IDisposable



    Private Shared WB As Excel.Workbook

    Public Wks As Excel.Worksheet

    Sub New()

        Try

            If WB Is Nothing Then

               Dim App = New Excel.Application

               App.SheetsInNewWorkbook = 1

               WB = App.Workbooks.Add

               AddHandler WB.BeforeClose, AddressOf WB_BeforeClose

               App.Visible = True

               App.Caption = Proyecto.Actprj.RevProyecto

               Wks = DirectCast(WB.ActiveSheet, Excel.Worksheet)

            Else

               Wks = DirectCast(WB.Worksheets.Add, Excel.Worksheet)

            End If

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        End Try

    End Sub



    Private disposedValue As Boolean = False        ' Para detectar llamadas redundantes



    ' IDisposable

    Protected Overridable Sub Dispose(ByVal disposing As Boolean)

        If Not Me.disposedValue Then

            If disposing Then

                ' TODO: Liberar otro estado (objetos administrados).

            End If

            If Wks IsNot Nothing Then

               With Wks.Application

                    AppActivate(.Caption)

                    .WindowState = Excel.XlWindowState.xlMaximized

               End With

               Wks = Nothing

               GC.Collect()

            End If

            ' TODO: Liberar su propio estado (objetos no administrados).

            ' TODO: Establecer campos grandes como Null.

        End If

        Me.disposedValue = True

    End Sub



#Region " IDisposable Support "

    ' Visual Basic agregó este código para implementar correctamente el modelo descartable.

    Public Sub Dispose() Implements IDisposable.Dispose

        ' No cambie este código. Coloque el código de limpieza en Dispose (ByVal que se dispone como Boolean).

        Dispose(True)

        GC.SuppressFinalize(Me)

    End Sub

#End Region



    Public Sub Value(ByVal Pos As String, ByVal Datos(,) As Object)

        Wks.Range(Pos).Resize(Datos.GetLength(0), Datos.GetLength(1)).Value = Datos

    End Sub

    Public Sub Column(ByVal Col As String, ByVal Width As Double, Optional ByVal Format As String = Nothing, Optional ByVal Align As Integer = 0)

        Dim r = DirectCast(Wks.Columns(Col), Excel.Range)

        If Width <> 0 Then r.ColumnWidth = Width

        If Format IsNot Nothing Then r.NumberFormat = Format

        If Align <> 0 Then r.HorizontalAlignment = Align

    End Sub



    Private Shared Sub WB_BeforeClose(ByRef Cancel As Boolean)

        If WB IsNot Nothing Then ReleaseComObject(WB) : WB = Nothing

    End Sub

End Class

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 13

Assisted Solution

by:gbanik
gbanik earned 250 total points
ID: 34114017
Check the KB article... it has everything u need

http://support.microsoft.com/kb/306022
0
 

Author Comment

by:diablo089
ID: 34114080
x77,

What is this block doing?

Using x As New ExpExcel
      With x.Wks.Range("A1.L1")
         .Font.Bold = True : .Font.Color = ColorTranslator.ToWin32(Color.Blue)
         .Interior.Color = ColorTranslator.ToWin32(Color.LightCyan)
      End With
      x.Column("A", 13)                               'Trabajo
      x.Column("B", 5)                                'Sup
      x.Column("C:E", 8, , Excel.XlHAlign.xlHAlignRight) 'Fimte,Ffmte,Dur
      x.Column("F", 4)                                'Av
      x.Column("G", 100)                              'Destra

      x.Value("A1", Datos) : Datos = Nothing
  End Using

Especially the x.Column and x.Value calls?
0
 
LVL 15

Expert Comment

by:x77
ID: 34114143
I send the data using
    x.Value("A1", Datos)

But I format columns also  :
   Column A - Width 13 chr
               b              5

Also can set format and alignement.

If you have a DataTAble with 1000 rows and 10 Columns

   Dim Datos(NumRows, NumCols)

   Then Fill Datos with each data on your DataTable
   Then use the ExpExcel class.

The ExpExce class, knows if Wookbook is closed.
If you maintain the Excel WookBook opene, you can add more wookseets to same WookBook.
0
 
LVL 15

Expert Comment

by:x77
ID: 34114188
I have write some samples on EE from ExpExcel class.

http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_26533988.html?sfQueryTermInfo=1+1+10+30+datos.getlength+valu

In this sample, I Export the DataGridView content to Excel.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel 2016 - Black cell borders 11 27
Highlighting cells in Excel 9 16
Advice on Xojo as a development tool over VB. 4 31
Sum iF  based on a null cell 11 29
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now