Como dar formato a un excel desde vb.net

Question

Hola a todos, necesito exportar datos de un Datagridview a un excel,

pero dicho excel tiene que tener un formato , como que las cabeceras de las columnas tengan un color de fondo

un tipo de letra, tamaño, los bordes , etc

como podria hacer esto desde vb.net???

me han dicho que tambien se podria hacer con una plantilla, ¿como es esto?

---

jeferson

Answer 1

El crear la plantilla es facil ya que desde excel te configuras tu documento y al guardar simplemente tienes que darle a guardar como... y en tipo seleccionar plantilla excel. Si lo has hecho bien verás que la extension no es xlsx.

Si quieres hacerlo mediante código todo se resume a actuar sobre las propiedades de una celda (objeto Cell) o un rango (objeto Range)

Te pongo algunos ejemplos que he encontrado por ahí

Sub Cambia_propiedades() 
With Hoja1.Range("B1") 
.ColumnWidth = 17 'Cambia el ancho de columna 
.RowHeight = 27.75 'Cambia el alto de celda 
.Font.Bold = True 'Pone negritas al font 
.Font.Italic = True 'Pone italica al font 
.Font.Underline = xlUnderlineStyleSingle 'Pone subrrayado 
.HorizontalAlignment = xlCenter 'Alinea el texto en el centro de la celda 
.VerticalAlignment = xlBottom 'Alinea el texto en el fonto de la celda 
.Style = "Currency" 'Pone el formato de la celda en moneda 
With .Interior 
.ColorIndex = 6 'Pone color amarillo la celda 
.Pattern = xlSolid 'Color solido 
End With 
With Selection.Font 
.Name = "Courier" 'Pone tipo de letra courier al font 
.Size = 10 
.ColorIndex = 3 'Pone color rojo el font 
End With 
End With 
End Sub

Answer 2

hola gracias me sirvio, de mucho pero tengo un pequeño error 

Cuando exporto el datagridview a excel me lo exporta normal la primera vez

pero al intentar abrir el archivo me sale el siguiente mensaje

cuando le doy si , me aparece normal los datos, pero no quisiera que apareciera ese mensaje

ahora eso que paso si exporto por segunda vez, lo ke sucede es que se pone lenta mi maquina como si 

estuviera cargando algo. y cuando me fijo en el administrador de tareas aparece esto, aun despues de haber cerrado todos los archivos excel.

este es mi codigo que uso para dar formato y exportar a un excel el datagridview, espero

me puedan aconsejar, y ayudar a resolver mi problema . gracias

 Dim saveFileDialog1 As New SaveFileDialog()
        saveFileDialog1.Filter = "Excel (*.xls)|*.xls"
        saveFileDialog1.Title = "excel File"
        saveFileDialog1.ShowDialog()

       
        If Not (Directory.Exists(saveFileDialog1.FileName)) Then
            
            Directory.Exists(saveFileDialog1.FileName)
        End If
       
        Dim m_Excel As New Excel.Application
        m_Excel.Cursor = Excel.XlMousePointer.xlWait
       
        Dim objLibroExcel As Excel.Workbook = m_Excel.Workbooks.Add
        Dim objHojaExcel As Microsoft.Office.Interop.Excel.Worksheet = objLibroExcel.Worksheets(1)

        With objHojaExcel
                        .Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVisible
            .Activate()

            ''Encabezado
            .Range("C2:E2").Merge()
            .Range("C2:E2").Font.Name = "Calibri"
            ' .Range("C2:E2").Font.ThemeColor = Color.Aqua
            .Range("C2:E2").Value = "REPORTE DE EXCEL"
            '.Range("C2:E2").Font.Bold = True
            .Range("C2:E2").Font.Size = 15
            .Range("C2:E2").Font.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
            '.Range("C2:E2").Font.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent3
            .Range("C2:E2").Font.ThemeFont = Excel.XlThemeFont.xlThemeFontMajor
            .Range("C2:E2").Font.Strikethrough = False
            .Range("C2:E2").Font.Superscript = False
            .Range("C2:E2").Font.Subscript = False
            .Range("C2:E2").Font.OutlineFont = False
            .Range("C2:E2").Font.Shadow = False
            .Range("C2:E2").Font.Bold = True
            .Range("C2:E2").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
            .Range("C2:E2").Font.ColorIndex = 1
            .Range("C2:E2").Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent1
            Dim objRangoTitulo As Microsoft.Office.Interop.Excel.Range = .Range("C2", "E2")
            objRangoTitulo.Rows.BorderAround()
            ' objRangoTitulo.Font.Background = Color.Aqua
            objRangoTitulo.Columns.BorderAround(1, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium)
            objRangoTitulo.Select()
            .Range("C3").Value = "Codigo" : .Range("D3").Value = "Descripcion" : .Range("E3").Value = "Operacion"
            .Range("C3").Merge() : .Range("D3").Merge() : .Range("E3").Merge()
            .Range("C3:E3").Font.Name = "Calibri"
            .Range("C3:E3").Font.Size = 12
            .Range("C3:E3").Font.ColorIndex = 2
            .Range("C3:E3").Interior.ColorIndex = 15
            .Range("C3:E3").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
            objRangoTitulo = .Range("C3", "E3")
            objRangoTitulo.Rows.BorderAround()
            objRangoTitulo.Columns.BorderAround(1, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium)
           
            Const primeraLetra As Char = "C"
            Const primerNumero As Short = 4
            Dim Letra As Char, UltimaLetra As Char
            Dim Numero As Integer, UltimoNumero As Integer
            Dim cod_letra As Byte = Asc(primeraLetra) - 1
            Dim sepDec As String = Application.CurrentCulture.NumberFormat.NumberDecimalSeparator
            Dim sepMil As String = Application.CurrentCulture.NumberFormat.NumberGroupSeparator
            'Establecer formatos de las columnas de la hija de cálculo  
            Dim strColumna As String = ""
            Dim LetraIzq As String = ""
            Dim cod_LetraIzq As Byte = Asc(primeraLetra) - 1
            Letra = primeraLetra
            Numero = primerNumero
            Dim objCelda As Microsoft.Office.Interop.Excel.Range
            For Each c As DataGridViewColumn In DataGridView1.Columns
               
                If c.Visible Then
                    If Letra = "Z" Then
                        Letra = primeraLetra
                        cod_letra = Asc(primeraLetra)
                        cod_LetraIzq += 1
                        LetraIzq = Chr(cod_LetraIzq)
                    Else
                        cod_letra += 1
                        Letra = Chr(cod_letra)
                    End If
                    strColumna = LetraIzq + Letra + Numero.ToString
                    objCelda = .Range(strColumna, Type.Missing)

                    objCelda.Value = c.HeaderText
                    objCelda.EntireColumn.Font.Size = 8
                    
                End If
                
            Next
            Dim objRangoEncab As Microsoft.Office.Interop.Excel.Range = .Range(primeraLetra + Numero.ToString, LetraIzq + Letra + Numero.ToString)
            objRangoEncab.BorderAround(1, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium)
            UltimaLetra = Letra
            Dim UltimaLetraIzq As String = LetraIzq
            'CARGA DE DATOS  
            Dim i As Integer = Numero '+ 1
            For Each reg As DataGridViewRow In DataGridView1.Rows
                LetraIzq = ""
                cod_LetraIzq = Asc(primeraLetra) - 1
                Letra = primeraLetra
                cod_letra = Asc(primeraLetra) - 1
                For Each c As DataGridViewColumn In DataGridView1.Columns
                    If c.Visible Then
                        If Letra = "Z" Then
                            Letra = primeraLetra
                            cod_letra = Asc(primeraLetra)
                            cod_LetraIzq += 1
                            LetraIzq = Chr(cod_LetraIzq)
                        Else
                            cod_letra += 1
                            Letra = Chr(cod_letra)
                        End If
                        : strColumna = LetraIzq + Letra
                        ' acá debería realizarse la carga  
                        .Cells(i, Trim(strColumna)) = IIf(IsDBNull(Trim(reg.ToString)), "", Trim(reg.Cells(c.Index).Value))
                        
                    End If
                Next
                Dim objRangoReg As Microsoft.Office.Interop.Excel.Range = .Range(primeraLetra + Trim(i.ToString), strColumna + Trim(i.ToString))
                objRangoReg.Rows.BorderAround()
                objRangoReg.Select()
                i += 1
            Next
            UltimoNumero = i
            'Dibujar las líneas de las columnas  
            LetraIzq = ""
            cod_LetraIzq = Asc("C")
            cod_letra = Asc(primeraLetra)
            Letra = primeraLetra
            For Each c As DataGridViewColumn In DataGridView1.Columns
                If c.Visible Then
                    objCelda = .Range(LetraIzq + Letra + primerNumero.ToString, LetraIzq + Letra + (UltimoNumero - 1).ToString)
                    objCelda.BorderAround()
                    If Letra = "Z" Then
                        Letra = primeraLetra
                        cod_letra = Asc(primeraLetra)
                        LetraIzq = Chr(cod_LetraIzq)
                        cod_LetraIzq += 1
                    Else
                        cod_letra += 1
                        Letra = Chr(cod_letra)
                    End If
                End If
            Next
            'Dibujar el border exterior grueso  
            Dim objRango As Microsoft.Office.Interop.Excel.Range = .Range("C" + primerNumero.ToString, UltimaLetraIzq + "E" + (UltimoNumero - 1).ToString)
            objRango.Select()

            objRango.Columns.AutoFit()
            objRango.Columns.BorderAround(1, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium)
        End With
        'esta siguiente linea comentada
        
        'Reemplaza automaticamente el archivo
        If Not (File.Exists(saveFileDialog1.FileName)) Then
            m_Excel.ActiveWorkbook.SaveAs(saveFileDialog1.FileName)
            'Filename:="D:\Analisis de compra\" + text + ".xlsx")
        Else
            File.Delete(saveFileDialog1.FileName)
            
            m_Excel.ActiveWorkbook.SaveAs(saveFileDialog1.FileName)
        End If
        

        m_Excel.ActiveWorkbook.Close(SaveChanges:=False)
        'Cierra el archivo y elimina la variable
        m_Excel.Quit()
        m_Excel = Nothing
        objLibroExcel = Nothing
        Process.Start(saveFileDialog1.FileName)

---

jeferson

Answer 3

Lo del aviso me suena a que no cierras bien el fichero y por eso da error. al abrirlo el propio excel lo remienda.

En cuanto al problema del proceso es algo bastante común. Te paso un link donde lo solucionan de una forma un tanto chapucera pero es que creo que no hay otra. 

http://support.microsoft.com/kb/317109/es

No obstante tienes que tener mucho cuidado cuando uses interop porque este tipo de "fugas" de memoria es muy común si no lo controlas bien

Answer 4

Amigo una pregunta como tendría que hacer para que un rango de celdas especificadas tengo borde...por ejemplo para poner color de fondo a un rango de celdas lo hice asi     With osheet.range("A1:G1")
                                                                                 .interior.color = RGB(167, 167, 167)
                                                                                 End With                                                                               

espero su respuesta mi querido amigo...gracias.