엑셀 - DataGridView 내용을 엑셀로 출력하는 방법

 

Imports Microsoft.Office.Interop.Excel

 

Public Class MyExcelControl

 

    Private exl As New Application
    Private book As Workbook
    Private sheet As Worksheet

 

    Public Sub New()
        ' 엑셀을 연 후 시트를 추가한다.
        book = exl.Workbooks.Add()
        sheet = book.Worksheets(1)
    End Sub

 

    ' DataGridView의 내용을 그대로 엑셀로 표시한다.
    Public Sub ShowDataGridViewContent(ByVal list As DataGridView)
        Try
            exl.Visible = True
            ' DataGridView 컬럼 헤더 텍스트를 첫 줄에 입력한다.
            Dim columnIndex As Integer = 1
            For Each col As DataGridViewColumn In list.Columns
                sheet.Cells(1, columnIndex) = col.HeaderText
                columnIndex += 1
            Next

            ' 데이터를 입력한다.
            Dim rowIndex As Integer = 1
            For Each row As DataGridViewRow In list.Rows
                rowIndex += 1
                Dim colIndex As Integer = 1
                For Each cell As DataGridViewCell In row.Cells
                    sheet.Cells(rowIndex, colIndex) = cell.Value
                    colIndex += 1
                Next
            Next

        Catch ex As Exception
            MsgBox(ex.Message & vbCrLf & ex.ToString, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

 

End Class