none
Exportar DataGridView a Excell con encabezados RRS feed

  • Pregunta

  • Vb. Net

    Como puedo exportar un datagridview a excell, ya puse una rutina que encontre pero no me exporta los encabezados

    Alguna sugerencia?

    Anexo script

        Private Sub btnExcell_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcell.Click
            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
    
            Dim i As Int16, j As Int16
            If Me.sfdArchivo.ShowDialog() = Windows.Forms.DialogResult.OK Then
    
                If Me.sfdArchivo.FileName Is Nothing Or Me.sfdArchivo.FileName = "" Then Exit Sub
                If InStr(Me.sfdArchivo.FileName, "xls") > 0 Or InStr(Me.sfdArchivo.FileName, "xlsx") > 0 Then
                Else
                    MsgBox("Archivo no tiene extension de excell (xls, xlsx)", MsgBoxStyle.Information, Me.Text)
                    Exit Sub
                End If
    
                Cursor.Current = Cursors.WaitCursor
    
                xlApp = New Excel.ApplicationClass
                xlWorkBook = xlApp.Workbooks.Add(misValue)
                xlWorkSheet = xlWorkBook.Sheets("Hoja1")
    
    
                For i = 0 To dgvDIOT.RowCount - 1
                    For j = 0 To dgvDIOT.ColumnCount - 1
                        xlWorkSheet.Cells(i + 1, j + 1) = dgvDIOT(j, i).Value.ToString()
                    Next
                Next
    
                xlWorkBook.SaveAs(Me.sfdArchivo.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, _
                 Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
                xlWorkBook.Close(True, misValue, misValue)
                xlApp.Quit()
    
                releaseObject(xlWorkSheet)
                releaseObject(xlWorkBook)
                releaseObject(xlApp)
    
                Cursor.Current = Cursors.Default
                MessageBox.Show("Informacion Convertida a Excell")
            End If
    
        End Sub
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString())
            Finally
                GC.Collect()
            End Try
        End Sub
    

    • Cambiado Enrique M. Montejo martes, 4 de noviembre de 2014 8:11 Pregunta relacionada con controles de Windows Forms.
    viernes, 31 de octubre de 2014 22:52

Respuestas

  • Public Class Form1
    
        Private Sub btnExcell_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcell.Click
            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
    
            Dim i As Integer, j As Integer
            If Me.sfdArchivo.ShowDialog() = Windows.Forms.DialogResult.OK Then
    
                If Me.sfdArchivo.FileName Is Nothing Or Me.sfdArchivo.FileName = "" Then Exit Sub
                If InStr(Me.sfdArchivo.FileName, "xls") > 0 Or InStr(Me.sfdArchivo.FileName, "xlsx") > 0 Then
                Else
                    MsgBox("Archivo no tiene extension de excell (xls, xlsx)", MsgBoxStyle.Information, Me.Text)
                    Exit Sub
                End If
    
                Cursor.Current = Cursors.WaitCursor
    
                xlApp = New Excel.Application
                xlWorkBook = xlApp.Workbooks.Add(misValue)
                xlWorkSheet = CType(xlWorkBook.Sheets("Hoja1"), Excel.Worksheet)
    
                For j = 0 To dgvDIOT.ColumnCount - 1
                    xlWorkSheet.Cells(1, j + 1) = dgvDIOT.Columns(j).Name
                Next
    
                For i = 0 To dgvDIOT.RowCount - 1
                    For j = 0 To dgvDIOT.ColumnCount - 1
                        If (dgvDIOT(j, i).Value Is Nothing) Then Continue For
                        xlWorkSheet.Cells(i + 2, j + 1) = dgvDIOT(j, i).Value.ToString()
                    Next
                Next
    
                'xlWorkBook.SaveAs(Me.sfdArchivo.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, _
                ' Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
    
                'xlWorkBook.SaveAs(Me.sfdArchivo.FileName, Excel.XlFileFormat.xlWorkbookNormal, , , , , Excel.XlSaveAsAccessMode.xlExclusive, , , , , )
    
                xlWorkBook.SaveAs(Filename:=Me.sfdArchivo.FileName, FileFormat:=Excel.XlFileFormat.xlWorkbookNormal, AccessMode:=Excel.XlSaveAsAccessMode.xlExclusive)
    
                'xlWorkBook.Close(True, misValue, misValue)
                xlWorkBook.Close(True)
    
                xlApp.Quit()
    
                'releaseObject(xlWorkSheet)
                'releaseObject(xlWorkBook)
                'releaseObject(xlApp)
    
                Cursor.Current = Cursors.Default
                MessageBox.Show("Informacion Convertida a Excell")
            End If
    
        End Sub
    
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString())
            Finally
                GC.Collect()
            End Try
        End Sub
    End Class
    


    /dd

    viernes, 31 de octubre de 2014 23:41