엑셀로 받은 단가표나 주문 목록을 프로그램에 불러와야 하는 일은 업무용 프로그램에서 거의 빠지지 않습니다. 예전에는 Microsoft.Office.Interop.Excel로 엑셀을 직접 띄워서 읽었는데, 이 방식은 프로그램을 돌리는 PC에 엑셀이 설치돼 있어야 하고 속도도 느립니다. 작업이 끝난 뒤 EXCEL.EXE 프로세스가 작업 관리자에 남아 있는 문제도 자주 생깁니다.

EPPlus는 xlsx 파일 구조(Open XML)를 직접 읽는 라이브러리라 엑셀 설치가 필요 없습니다. 네임스페이스 이름이 OfficeOpenXml이라 코드에서는 이 이름으로 보입니다.

광고

NuGet으로 EPPlus 설치

솔루션 탐색기에서 프로젝트를 오른쪽 클릭해 "NuGet 패키지 관리"를 열고 EPPlus를 검색해서 설치합니다.

NuGet 패키지 관리자에서 EPPlus를 검색해 설치하는 화면

설치 전에 라이선스를 한 번 확인해야 합니다. EPPlus는 5 버전부터 상업용 라이선스 정책으로 바뀌어서, 코드에서 LicenseContext를 지정하지 않으면 실행할 때 예외가 납니다. 개인 프로젝트나 비상업 용도라면 NonCommercial, 회사 제품에 넣는다면 상업 라이선스를 구매하고 Commercial로 지정합니다. 라이선스 비용이 부담된다면 LGPL로 배포되던 마지막 버전인 4.5.3.x를 쓰는 방법도 있는데, 이 경우 LicenseContext 설정 줄이 없어야 하고 워크시트 인덱스가 1부터 시작한다는 차이가 있습니다.

첫 번째 시트를 DataTable로 읽는 함수

Imports System.IO
Imports OfficeOpenXml

Public Class ExcelReader
    Private Shared isOpen As Boolean

    Public Shared Function ReadExcelFile(filePath As String) As DataTable
        If Not isOpen Then OfficeOpenXml.ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial ' 또는 LicenseContext.Commercial
        Dim dt As New DataTable()

        ' 엑셀 파일 열기
        Dim fileInfo As New FileInfo(filePath)
        Using package As New ExcelPackage(fileInfo)
            ' 통합 문서의 첫 번째 워크시트
            Dim worksheet As ExcelWorksheet = package.Workbook.Worksheets(0)
            If worksheet.Dimension Is Nothing Then
                program1472.LOG("데이터 구조가 올바르지 않습니다.",, 1)
                Return Nothing
            End If

            ' 사용된 범위의 첫 행부터 마지막 행까지 반복
            For row As Integer = worksheet.Dimension.Start.Row To worksheet.Dimension.End.Row
                ' 새 DataRow 생성
                Dim newRow As DataRow = dt.NewRow()

                ' 현재 행의 모든 열 반복
                For col As Integer = worksheet.Dimension.Start.Column To worksheet.Dimension.End.Column
                    ' 셀 값을 DataRow에 추가
                    If Not dt.Columns.Contains((col - 1).ToString) Then dt.Columns.Add((col - 1).ToString, GetType(String))
                    newRow(col - 1) = If(worksheet.Cells(row, col).Value IsNot Nothing, worksheet.Cells(row, col).Value.ToString(), Nothing)
                Next

                ' DataRow를 DataTable에 추가
                dt.Rows.Add(newRow)
            Next
        End Using

        Return dt
    End Function
End Class

worksheet.Dimension은 시트에서 실제로 값이 들어 있는 범위입니다. 빈 시트면 Nothing이라 그 경우는 로그를 남기고 Nothing을 돌려줍니다. program1472.LOG는 원래 프로젝트에 있는 로그 함수이니, 가져다 쓸 때는 Debug.WriteLine이나 본인 로그 함수로 바꾸면 됩니다.

열 이름은 "0", "1", "2"처럼 열 번호를 그대로 문자열로 붙이고, 처음 나오는 열일 때만 Columns.Add를 합니다. 모든 값은 ToString()으로 문자열로 넣습니다. 숫자, 날짜가 섞인 시트라도 일단 문자열로 다 가져온 다음 쓰는 쪽에서 변환하는 방식이라, 형식 때문에 오류가 날 일이 적습니다.

실제로 써 보면 알아야 할 점이 몇 가지 있습니다.

  • 머리글 행도 데이터의 첫 행으로 들어옵니다. 1행을 열 이름으로 쓰고 싶다면 첫 번째 루프에서 Columns.Add에 셀 값을 이름으로 넣고, 데이터는 Start.Row + 1부터 읽도록 바꾸면 됩니다.
  • 열 이름은 col - 1인데 값은 newRow(col - 1) 인덱스로 넣습니다. 시트 데이터가 A열부터 시작하면 문제없지만 B열부터 시작하는 시트라면 열은 하나만 만들어졌는데 값은 newRow(1)에 넣으려 해서 인덱스 범위 오류가 납니다. 이런 시트를 다룬다면 인덱스를 col - Dimension.Start.Column으로 맞추는 게 안전합니다.
  • 날짜 셀은 Value가 Date 또는 숫자(OADate)로 올 수 있어서 ToString() 결과가 기대와 다를 수 있습니다. 화면에 보이는 그대로가 필요하면 worksheet.Cells(row, col).Text를 쓰는 것도 방법입니다.
  • 사용자가 그 파일을 엑셀에서 열어 둔 상태면 파일 잠금 때문에 읽기가 실패할 수 있습니다. 호출하는 쪽에서 예외를 잡아 "엑셀을 닫고 다시 시도하세요" 정도의 메시지를 띄워 주면 문의가 줄어듭니다.

파일 선택부터 DataGridView 표시까지

아래와 같은 엑셀 파일을 열어 보겠습니다.

DataGridView로 불러올 예제 엑셀 파일

버튼 클릭에서 파일 선택 창을 띄우고, 고른 파일을 ReadExcelFile로 읽어 그대로 DataSource에 넣으면 끝입니다.

Private Sub BhsButton3_Click(sender As Object, e As EventArgs) Handles BhsButton3.Click
    Dim filePath As String = program1472.Functions.File.OpenFilePicker(program1472.Functions.File.getfilter("excel"))
    template = ExcelReader.ReadExcelFile(filePath)
    BhsDataGridView1.DataSource = template
End Sub

엑셀 데이터를 DataTable로 변환해 DataGridView에 표시한 결과

BhsButton3, BhsDataGridView1은 일반 Button, DataGridView를 상속한 사용자 정의 컨트롤이고 template은 폼에 선언해 둔 DataTable 변수입니다. 일반 컨트롤로 바꿔도 똑같이 동작합니다.

파일 선택과 필터 문자열은 자주 쓰니 따로 묶어 두었습니다.

Imports System.IO
Imports System.Windows.Forms

Namespace Functions
    Public Class [File]
        Public Shared Function getfilter(datatype As String) As String
            Select Case datatype.ToLower
                Case "img", "image", "이미지", "사진" : Return "이미지 파일 선택 (*.jpg;*.jpeg;*.png)|*.jpg;*.jpeg;*.png"
                Case "pdf" : Return "PDF 파일 선택 (*.pdf)|*.pdf"
                Case "excel" : Return "Excel 파일 선택 (Excel Files)|*.xls;*.xlsx;*.xlsm"
                Case Else : Return String.Format("{0} 파일 (*.{0})|*.{0}", datatype)
            End Select
        End Function

        Public Shared Function OpenFilePicker(filter As String, Optional title As String = "Please select a file") As String
            Using openFileDialog As New OpenFileDialog()
                openFileDialog.Title = title
                openFileDialog.Filter = filter
                openFileDialog.RestoreDirectory = True

                If openFileDialog.ShowDialog() = DialogResult.OK Then
                    Return openFileDialog.FileName
                Else
                    Return String.Empty
                End If
            End Using
        End Function
    End Class
End Namespace

클래스 이름 File을 대괄호로 감싼 건 System.IO.File과 이름이 겹쳐서 키워드처럼 구분하려는 것입니다. OpenFilePicker는 취소하면 빈 문자열을 돌려주는데, 위 버튼 코드는 이 경우를 따로 거르지 않아 ReadExcelFile("")이 호출됩니다. If filePath = "" Then Exit Sub 한 줄을 넣어 두세요.

필터에 *.xls가 들어 있지만 EPPlus는 xlsx 계열(Open XML)만 읽습니다. 97-2003 형식의 xls 파일을 고르면 열 때 예외가 나니, xls까지 받아야 한다면 NPOI나 ExcelDataReader 같은 다른 라이브러리를 함께 쓰거나 필터에서 xls를 빼는 편이 낫습니다.