Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1220
  • Last Modified:

Speed Up Write To Excel From VB.NET

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
diablo089
Asked:
diablo089
  • 3
  • 3
  • 2
2 Solutions
 
gbanikCommented:
The easiest thing u can do during a write for a significant change are the following:

oExcel.ScreenUpdating=false
oExcel.EnableEvents=false

0
 
gbanikCommented:
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
 
diablo089Author Commented:
Do you know which namespace holds the ADODB definitions? As in what I should Import?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
x77Commented:
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
 
gbanikCommented:
Check the KB article... it has everything u need

http://support.microsoft.com/kb/306022
0
 
diablo089Author Commented:
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
 
x77Commented:
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
 
x77Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now