rpkhare
asked on
Fast export to excel
I am showing some records on DataGridView. On click of a button I want to export the results to Excel. I Googled and found few code samples but the performance is very slow. It takes too much time to export. Also, when the export is complete, the column widths in Excel doesn't get auto-adjusted to cell content width.
Is there any fast code to achieve all the above?
Is there any fast code to achieve all the above?
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(Dato s.GetLengt h(0), Datos.GetLength(1)).Value = Datos
End Sub
Assigns the array to a Range in 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(Dato
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
ASKER
@ x77:
Can the above code be converted to a DLL?
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.E xcel
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.E
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks