106 lines
3.7 KiB
VB.net
106 lines
3.7 KiB
VB.net
|
Imports System.Data.DataTable
|
|||
|
|
|
|||
|
|
Public Class ClassTablaAExcel
|
|||
|
|
|
|||
|
|
Public Count As Integer 'Se utiliza para la barra de progreso
|
|||
|
|
Dim frmBarra As ExcProgreso = New ExcProgreso 'Se instancia el form de la barra de progreso
|
|||
|
|
|
|||
|
|
Public Function ExportaExcel(sNombreArchivo As String, ExportDataTable As System.Data.DataTable, _
|
|||
|
|
ByRef sError As String) As Boolean
|
|||
|
|
Try
|
|||
|
|
sError = ""
|
|||
|
|
ExportaExcel = False
|
|||
|
|
|
|||
|
|
Dim _excel
|
|||
|
|
Dim wBook
|
|||
|
|
Dim wSheet
|
|||
|
|
_excel = CreateObject("Excel.Application")
|
|||
|
|
wBook = _excel.Workbooks.Add()
|
|||
|
|
wSheet = wBook.Worksheets(1)
|
|||
|
|
|
|||
|
|
Dim dt As System.Data.DataTable = ExportDataTable
|
|||
|
|
Dim dc As System.Data.DataColumn
|
|||
|
|
Dim dr As System.Data.DataRow
|
|||
|
|
Dim colIndex As Integer = 0
|
|||
|
|
Dim rowIndex As Integer = 1
|
|||
|
|
|
|||
|
|
''Barra de avance
|
|||
|
|
'Se obtiene el numero de registros a exportar
|
|||
|
|
Count = dt.Rows.Count
|
|||
|
|
'Se establen las propiedades de barra de progreso
|
|||
|
|
frmBarra.pbrAvance.Maximum = Count + 4
|
|||
|
|
frmBarra.pbExportCancel = False
|
|||
|
|
frmBarra.Show()
|
|||
|
|
|
|||
|
|
''Çoloca el nombre del archivo
|
|||
|
|
_excel.Cells(rowIndex, 1) = sNombreArchivo
|
|||
|
|
rowIndex = rowIndex + 1
|
|||
|
|
_excel.Cells(rowIndex, 1) = ""
|
|||
|
|
rowIndex = rowIndex + 1
|
|||
|
|
|
|||
|
|
''Declara Columnas
|
|||
|
|
For Each dc In dt.Columns
|
|||
|
|
colIndex = colIndex + 1
|
|||
|
|
_excel.Cells(rowIndex, colIndex) = dc.ColumnName
|
|||
|
|
Next
|
|||
|
|
rowIndex = rowIndex + 1
|
|||
|
|
''Escribe Encabezados
|
|||
|
|
For Each dr In dt.Rows
|
|||
|
|
colIndex = 0
|
|||
|
|
For Each dc In dt.Columns
|
|||
|
|
colIndex = colIndex + 1
|
|||
|
|
_excel.Cells(rowIndex, colIndex) = dr(dc.ColumnName)
|
|||
|
|
Next
|
|||
|
|
rowIndex = rowIndex + 1
|
|||
|
|
If (rowIndex Mod 20) = 0 Then
|
|||
|
|
System.Windows.Forms.Application.DoEvents()
|
|||
|
|
If frmBarra.pbExportCancel Then
|
|||
|
|
If MessageBox.Show("¿Desea cancelar la exportación?", "Minds", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
|
|||
|
|
Exit For
|
|||
|
|
Else
|
|||
|
|
frmBarra.pbExportCancel = False
|
|||
|
|
End If
|
|||
|
|
End If
|
|||
|
|
'se va incrementando los cuadritos de la barra
|
|||
|
|
frmBarra.pbrAvance.Value = rowIndex
|
|||
|
|
'Se saca el porcentaje
|
|||
|
|
frmBarra.Text = "Exportando a Excel: " & CInt(((rowIndex) * 100) / frmBarra.pbrAvance.Maximum) & " %"
|
|||
|
|
End If
|
|||
|
|
|
|||
|
|
Next
|
|||
|
|
|
|||
|
|
''Autoajusta el tamaño de las columnas
|
|||
|
|
wSheet.Columns.AutoFit()
|
|||
|
|
|
|||
|
|
' ''Termina excel
|
|||
|
|
'Dim strFileName = SPDestination & sNombreArchivo
|
|||
|
|
|
|||
|
|
'If System.IO.File.Exists(strFileName) Then
|
|||
|
|
' System.IO.File.Delete(strFileName)
|
|||
|
|
'End If
|
|||
|
|
|
|||
|
|
''Guarda el archivo
|
|||
|
|
''wBook.Save() ''.SaveAs(strFileName)
|
|||
|
|
''wBook.Close()
|
|||
|
|
''_excel.Quit()
|
|||
|
|
|
|||
|
|
System.Windows.Forms.Application.DoEvents()
|
|||
|
|
frmBarra.Close() 'Cerramos el form de la barra de pregreso
|
|||
|
|
|
|||
|
|
''Que se muestre el excel
|
|||
|
|
_excel.Visible = True
|
|||
|
|
|
|||
|
|
''exportacion exitosa
|
|||
|
|
ExportaExcel = True
|
|||
|
|
|
|||
|
|
'Dispose of unneeded objects
|
|||
|
|
ExportDataTable.Dispose()
|
|||
|
|
ExportDataTable = Nothing
|
|||
|
|
|
|||
|
|
Catch ex As Exception
|
|||
|
|
sError = "Error: " & ex.Message & "Stored Procedure Running Process " & MessageBoxButtons.OK
|
|||
|
|
End Try
|
|||
|
|
|
|||
|
|
End Function
|
|||
|
|
End Class
|