Imports System.Data.SqlClient
Imports Microsoft.Office.Interop
Public Class Form1
Private ds As New DataSet
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim cnn As New SqlConnection("Data Source= 192.168.0.116 ;Initial Catalog= DBPriX ;User ID= auchuya;Password=auchuya") Dim da As New SqlDataAdapter("exec Up_Reportes", cnn) da.Fill(ds)
DataGridView1.DataSource = ds.Tables(0)
End Sub
Private Sub btnExportar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportar.Click
Dim m_Excel
Dim objLibroExcel
Dim objHojaExcel
m_Excel = CreateObject("Excel.Application") objLibroExcel = m_Excel.Workbooks.Add()
objHojaExcel = objLibroExcel.Worksheets(1)
objHojaExcel.Name = "Recuperaciones"
objHojaExcel.Visible = Excel.XlSheetVisibility.xlSheetVisible
objHojaExcel.Activate()
Dim fila As Integer = 1
Dim columna As Integer = 1
objHojaExcel.Range("A1").Select() For Each dc In ds.Tables(0).Columns
objHojaExcel.Range(nombreColumna(columna) & 1).Value = dc.ColumnName
columna += 1
Next
fila += 1
Dim objRango As Excel.Range = objHojaExcel.Range("A1:" & nombreColumna(ds.Tables(0).Columns.Count) & "1") objRango.Font.Bold = True
objRango.Cells.Interior.ColorIndex = 35
objRango.Cells.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.XlLineStyle.xlLineStyleNone
objRango.Cells.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.XlLineStyle.xlLineStyleNone
objRango.Cells.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone
objRango.Cells.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous
objRango.Cells.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous
objRango.Cells.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous
ProgressBar1.Maximum = ds.Tables(0).Rows.Count
columna = 1
ProgressBar1.Value = 0
For Each dr In ds.Tables(0).Rows
columna = 1
For Each dc In ds.Tables(0).Columns
objHojaExcel.Range(nombreColumna(columna) & fila).Value = dr(dc.ColumnName)
columna += 1
Next
fila += 1
ProgressBar1.Value += 1
Next
objRango = objHojaExcel.Range("A1:" & nombreColumna(ds.Tables(0).Columns.Count) & ds.Tables(0).Rows.Count.ToString) objRango.Select()
objRango.Columns.AutoFit()
MsgBox("Exportación a Excel completa", MsgBoxStyle.Information, ".:: solovb.net ::.") m_Excel.Visible = True
End Sub
Public Function nombreColumna(ByVal numero As Integer) As String
Dim columna(28) As String
columna(1) = "A"
columna(2) = "B"
columna(3) = "C"
columna(4) = "D"
columna(5) = "E"
columna(6) = "F"
columna(7) = "G"
columna(8) = "H"
columna(9) = "I"
columna(10) = "J"
columna(11) = "K"
columna(12) = "L"
columna(13) = "M"
columna(14) = "N"
columna(15) = "O"
columna(16) = "P"
columna(17) = "Q"
columna(18) = "R"
columna(19) = "S"
columna(20) = "T"
columna(21) = "U"
columna(22) = "V"
columna(23) = "W"
columna(24) = "X"
columna(25) = "Y"
columna(26) = "Z"
columna(27) = "AA"
columna(28) = "AB"
Return columna(numero)
End Function
End Class
