Fast export to excel

fastest way is writing the grid result in a CSV (Comma Separated Values) file, which can be open in excel by default. In that case you just nead a StreamWriter of text format and write it with commas

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

Select allOpen in new window

@ x77:

Can the above code be converted to a DLL?

I wrote the code in vb, the class uses the Primary Interop Excel Net module included by Excel intallation.
You can write a new Dll project in Vb.Net and copy the code.
You need include in the references section the reference to
    Microsoft.Office.Interop.Excel

Thanks