miércoles, 13 de marzo de 2013

Exportar contenido de una tabla SQL a excel con formato


 
Adiciona en el proyecto la referencia:
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.")

Comparte