[vb.net] EPPlus로 엑셀 파일을 읽어 DataTable로 가져오기

업무 프로그램에서 "엑셀 파일 올리면 목록에 쭉 들어가게 해 주세요"는 정말 자주 듣는 요구입니다. 예전에는 Excel Interop으로 엑셀을 띄워서 읽었는데, 사용자 PC에 엑셀이 깔려 있어야 하고 프로세스가 안 죽고 남는 문제가 늘 따라다녔습니다. xlsx 파일만 다룬다면 EPPlus 라이브러리로 파일을 직접 읽는 쪽이 훨씬 가볍고 빠릅니다. EPPlus의 네임스페이스가 OfficeOpenXml이라 코드에서는 이 이름으로 보입니다.

패키지 설치

Visual Studio의 NuGet 패키지 관리자에서 "EPPlus"를 검색해 설치하면 됩니다.

EPPlus는 버전 5부터 라이선스 정책이 바뀌어서, 코드에서 상업용인지 비상업용인지 지정하지 않으면 파일을 여는 순간 예외가 납니다. 아래 함수 첫 줄이 그 설정입니다. 회사 업무용으로 쓰는 프로그램이라면 비상업 라이선스 대상이 아닐 수 있으니 라이선스 조건을 먼저 확인해야 하고, 최신 메이저 버전에서는 설정하는 API 모양도 달라졌으니 설치한 버전의 문서를 같이 보는 게 좋습니다. 라이선스가 걸린다면 4.x 이하 버전이나 다른 라이브러리(ClosedXML 등)를 고려하게 됩니다.

읽기 함수

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
            ' Loop through all rows from the first row (start from 1 to skip header row)
            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

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

        Return dt
    End Function
End Class

worksheet.Dimension은 값이 들어 있는 영역(예: A1:F120)을 알려 주는 속성입니다. 시트가 완전히 비어 있으면 Nothing이 되기 때문에, 그 경우는 로그를 남기고 Nothing을 돌려줍니다. program1472.LOG는 제 프로젝트에서 쓰는 로그 함수라, 각자 환경에서는 Debug.WriteLine이나 사용하는 로거로 바꾸면 됩니다.

열 이름은 "0", "1", "2"…처럼 열 번호로 만들고, 모든 값을 문자열로 넣습니다. 숫자·날짜 형식이 섞인 엑셀이라도 일단 문자열로 다 받아 두고 나중에 필요한 칼럼만 변환하는 방식이라, 형식이 제멋대로인 사용자 파일을 받을 때 오히려 덜 터집니다. EPPlus 5 이상에서는 Worksheets 인덱스가 0부터 시작하므로 Worksheets(0)이 첫 시트입니다(4.x는 1부터였습니다).

코드 주석에는 "헤더를 건너뛴다"고 되어 있지만, 실제 루프는 Dimension.Start.Row부터 돌기 때문에 첫 행(헤더)도 데이터 행으로 들어갑니다. 첫 행을 열 이름으로 쓰고 싶다면 첫 행에서 dt.Columns.Add(셀값)을 하고, 행 루프는 Start.Row + 1부터 돌리도록 고치면 됩니다. 그리고 isOpen을 True로 바꾸는 줄이 없어서 라이선스 설정이 호출될 때마다 실행되는데, 같은 값을 다시 넣는 것뿐이라 동작에는 문제가 없습니다.

위와 같은 엑셀 파일을 읽어서 DataTable로 바꾼 뒤 DataGridView에 그대로 연결하면 아래처럼 보입니다.

버튼에서 파일 고르고 불러오기

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

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에 넘기면 FileInfo 생성에서 예외가 납니다. 버튼 이벤트에서 If filePath = "" Then Return 한 줄을 먼저 넣어 두세요.

또 하나, 필터는 .xls도 고를 수 있게 되어 있지만 EPPlus는 Office Open XML 형식(xlsx, xlsm)만 읽습니다. 97-2003 형식의 xls 파일을 고르면 열 때 오류가 나니, 필터에서 *.xls를 빼거나 "다른 이름으로 저장해서 xlsx로 바꿔 주세요" 같은 안내를 띄우는 게 좋습니다. 사용자가 엑셀에서 파일을 열어 둔 상태라면 파일이 잠겨서 읽기에 실패할 수 있다는 점도 함께 안내해 두면 문의가 줄어듭니다.