Proyecto/Referencias y seleccionar Microsoft Excel 15.0 Object Library
Dim Ds As New DataSet
Dim Da As New SqlClient.SqlDataAdapter("select * from MI_TABLA order by CAMPO_NOMBRE ASC ", Cnn)
Da.Fill(Ds, "MI_TABLA")
Dim myDataView As DataView = New DataView(Ds.Tables("MI_TABLA"))
Dim numreg As Integer = myDataView.Count
Dim oExcel As Microsoft.Office.Interop.Excel.ApplicationClass
Dim oBooks As Microsoft.Office.Interop.Excel.Workbooks
Dim oBook As Microsoft.Office.Interop.Excel.WorkbookClass
Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet
' Inicia Excel y abre el workbook
oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
oBooks = oExcel.Workbooks
oBook = oExcel.Workbooks.Add
oSheet = oBook.Sheets(1)
oSheet.Cells(2, 2) = "TITULO"
oSheet.Cells(2, 2).font.bold = True
oSheet.Cells(2, 4) = Now() 'fecha de hoy
oSheet.Cells(2, 4).font.bold = True
oSheet.Range("A4:U4").Interior.ColorIndex = 37 'linea de color de los encabezados
'color = 36 (amarillo claro)
'color = 37 (azul claro)
'color= 27 (amarillo fuerte)
Const ROW_FIRST = 5
Dim iRow As Int64 = 1
' Encabezado
oSheet.Cells(ROW_FIRST, 1) = "clave"
oSheet.Cells(ROW_FIRST, 2) = "Nombre"
oSheet.Cells(ROW_FIRST, 3) = "Paterno"
oSheet.Cells(ROW_FIRST, 4) = "Materno"
oSheet.Cells(ROW_FIRST, 5) = "calle"
oSheet.Cells(ROW_FIRST, 6) = "colonia"
oSheet.Cells(ROW_FIRST, 7) = "estado"
oSheet.Cells(ROW_FIRST, 8) = "delegación"
oSheet.Cells(ROW_FIRST, 9) = "c.p."
oSheet.Cells(ROW_FIRST, 10) = "teléfono"
oSheet.Cells(ROW_FIRST, 11) = "móvil"
'enzacebado en negritas
oSheet.Cells(ROW_FIRST, 1).font.bold = True
oSheet.Cells(ROW_FIRST, 2).font.bold = True
oSheet.Cells(ROW_FIRST, 3).font.bold = True
oSheet.Cells(ROW_FIRST, 4).font.bold = True
oSheet.Cells(ROW_FIRST, 5).font.bold = True
oSheet.Cells(ROW_FIRST, 6).font.bold = True
oSheet.Cells(ROW_FIRST, 7).font.bold = True
oSheet.Cells(ROW_FIRST, 8).font.bold = True
oSheet.Cells(ROW_FIRST, 9).font.bold = True
oSheet.Cells(ROW_FIRST, 10).font.bold = True
oSheet.Cells(ROW_FIRST, 11).font.bold = True
'anchura de las columnas de excel
oSheet.Columns(1).ColumnWidth = 10
oSheet.Columns(2).ColumnWidth = 30
oSheet.Columns(3).ColumnWidth = 30
oSheet.Columns(4).ColumnWidth = 30
oSheet.Columns(5).ColumnWidth = 30
oSheet.Columns(6).ColumnWidth = 30
oSheet.Columns(7).ColumnWidth = 30
oSheet.Columns(8).ColumnWidth = 30
oSheet.Columns(9).ColumnWidth = 10
oSheet.Columns(10).ColumnWidth = 30
oSheet.Columns(11).ColumnWidth = 30
Dim iCurrRow As Int64 = ROW_FIRST + iRow
Dim x As Integer
For x = 0 To numreg - 1
oSheet.Cells(iCurrRow, 1) = (myDataView(x)("campo_clave"))
oSheet.Cells(iCurrRow, 2) = (myDataView(x)("campo_nombre"))
oSheet.Cells(iCurrRow, 3) = (myDataView(x)("campo_paterno"))
oSheet.Cells(iCurrRow, 4) = (myDataView(x)("campo_materno"))
oSheet.Cells(iCurrRow, 5) = (myDataView(x)("campo_calle"))
oSheet.Cells(iCurrRow, 6) = (myDataView(x)("campo_colonia"))
oSheet.Cells(iCurrRow, 7) = (myDataView(x)("campo_estado"))
If myDataView(x)("campo_estado").ToString.Trim = "d.f." Then
oSheet.Cells(iCurrRow, 7) = "Distrito Federal"
End If
oSheet.Cells(iCurrRow, 8) = (myDataView(x)("campo_delegacion"))
oSheet.Cells(iCurrRow, 9) = (myDataView(x)("campo_cp"))
oSheet.Cells(iCurrRow, 10) = (myDataView(x)("campo_telefono"))
oSheet.Cells(iCurrRow, 11) = (myDataView(x)("campo_movil"))
iCurrRow += 1
Next
Cnn.Close()
'Terminada la exportaciòn abre la ventana de browse para salvar el archivo
'despues de teclear el nombre y salvarlo se cierra el archivo de excel
oBook.Close(True)
System.Runtime.InteropServices.Marshal. _
ReleaseComObject(oBook)
oBook = Nothing
System.Runtime.InteropServices.Marshal. _
ReleaseComObject(oBooks)
oBooks = Nothing
oExcel.Quit()
System.Runtime.InteropServices.Marshal. _
ReleaseComObject(oExcel)
oExcel = Nothing
MessageBox.Show("Exportación terminada.")
0 Comentarios