VB.NET으로 대용량 엑셀파일 읽기 (3/3)

지난 강좌에서는 대용량 엑셀파일을 열고, 닫는 방법을 알아보았다. 해당 내용이 궁금하면 아래 강좌를 참고하면 된다.

 

2023.09.02 - [VB.NET/왕초보] - VB.NET으로 대용량 엑셀파일 읽기 (1/3)

VB.NET으로 대용량 엑셀파일 읽기 (1/3)

2023.09.04 - [VB.NET/왕초보] - VB.NET으로 대용량 엑셀파일 읽기 (2/3)

VB.NET으로 대용량 엑셀파일 읽기 (2/3)

 

이번 강좌에서는 대용량 엑셀파일을 읽어서 원하는 데이터를 가져오는 방법에 대해 알아보자.

 

원리에 대해 간략히 정리하자면

1. 엑셀의 한줄에 매칭되는 구조체 생성

2. 엑셀의 모든 내용을 메모리에 불러오기

3. 루프를 돌며 한줄씩 구조체 형식에 데이터를 넣기

 

뭐... 이정도로 정리할 수 있겠다. 아래 엑셀을 기준으로 하나씩 살펴보도록 하자.

이름	국어	영어	수학
김철수	100	90	80
홍길동	90	80	70
장말자	80	70	60

 

위의 구조를 불러온다고 가정하고 위 정리 내용대로 하나씩 구현하면 된다.

 

1. 엑셀의 한줄에 매칭되는 구조체 생성

Public Structure EXCEL_DATA
   Dim user_name As String     ' 이름
   Dim kor_score As Integer    ' 국어
   Dim eng_scord As Integer    ' 영어
   Dim math_score As Integer   ' 수학
End Structure

 

이처럼 엑셀의 한줄에 해당하는 구조체를 먼저 생성하자.

 

2. 엑셀의 모든 내용을 메모리에 불러오기

아래 소스를 이해하면 열려있는 엑셀파일을 기준으로 한방에 엑셀의 모든 데이터를 메모리로 불러올 수 있다. 이렇게 불러오면 각각의 Cell들을 2차원 배열에 담아서 반환하게 된다.

Public Function GetExcelRange(pStartRow As Integer, pEndRow As Integer) As Object
   Dim lpList As Object = Nothing
   Dim lpColCount As Integer = WorkSheet.UsedRange.Columns.Count
   Dim lpRowCount As Integer = WorkSheet.UsedRange.Rows.Count
   If pStartRow <= lpRowCount Then
      Dim lpEndRow As Integer = IIf(pEndRow <= lpRowCount, pEndRow, lpRowCount)
      lpList = WorkSheet.Range(WorkSheet.Cells(pStartRow, 1), WorkSheet.Cells(lpEndRow, lpColCount)).Value
   End If
   Return lpList
End Function

 

나름 복잡해 보이지만 한줄씩 설명하면 그리 복잡하지 않으니 차근차근 알아보도록 하자.

파라미터로 사용된 pStartRow, pEndRow는 읽어오려는 범위를 지정하도록 했다. 이런 식으로 원하는 위치를 지정해서 불러올 수 있으며, 이 예제에서 다루지는 않았지만 Col도 임의로 지정해서 불러올 수 있으니 참고하자. 데이터가 10만개, 100만개 단위가 되면 아무래도 Row 기준으로 특정 범위만 불러오는게 훨씬 효율적이니 참고하자.

 

보통 엑셀을 사용할때는 첫줄과 둘째줄 정도는 각 항목의 제목으로 사용되니 이 부분은 건너뛰고 읽는게 일반적이다. 시작은 1열, 1행이니 보통 pStartRow에는 2가 들어가게 된다.

Dim lpColCount As Integer = WorkSheet.UsedRange.Columns.Count
Dim lpRowCount As Integer = WorkSheet.UsedRange.Rows.Count

 

읽어온 엑셀의 총 크기를 반환한다. 여기서 WorkSheet 변수가 갑자기 어디서 튀어나왔는지 모르겠다면 첫번째 강좌를 참고하도록 하자.

If pStartRow <= lpRowCount Then
   Dim lpEndRow As Integer = IIf(pEndRow <= lpRowCount, pEndRow, lpRowCount)
   lpList = WorkSheet.Range(WorkSheet.Cells(pStartRow, 1), WorkSheet.Cells(lpEndRow, lpColCount)).Value
End If

 

혹시 모를 오류에 대응하기 위해 If문을 하나 써줬다. 시작열이 엑셀의 전체열보다 크면 작업할 항목이 없으니 건너뛰어야 한다.

lpEndRow를 따로 정의해서 사용하는것도 동일한 이유다. 총 10열밖에 없는 엑셀파일을 읽었는데 20열까지 읽도록 호출하면 알아서 10열까지만 읽어야 하기에 넣어놨다. 시간별, 날짜별로 열의 수가 변하는 다양한 엑셀파일을 대처하려면 반드시 필요한 구문이다.

결국 이 함수의 핵심은 WorkSheet.Range 라는 함수를 호출하는 부분이다. 불러오려는 데이터의 좌상단과 우하단을 지정해주면 해당 범위만큼 반환하는 함수니 꼭 기억하도록 하자.

 

3. 루프를 돌며 한줄씩 구조체 형식에 데이터를 넣기

이제 데이터를 2차원 배열로 불러왔기 때문에 아래 함수는 엑셀 관련이라기 보다는 2차원 배열을 다루는게 핵심이 된다.

Public Function GetExcelRow(pList As Object, pRow As Integer) As EXCEL_DATA
   Dim lpData As EXCEL_DATA
   
   lpData.user_name = pList(pRow, 1)
   lpData.kor_score = pList(pRow, 2)
   lpData.eng_score = pList(pRow, 3)
   lpData.math_score = pList(pRow, 4)
   
   Return lpData
End Function

 

2차원 배열에서 각 Cell을 순차적으로 EXCEL_DATA라는 구조체 형식에 넣어주면 된다. 여기서 주의할 점은 이 2차원배열의 인덱스가 1부터 시작한다는 점이다. 1열 1행은 pList(1, 1)이 되니 이 부분 헷갈리지 않도록 주의하고, 2차원 배열에 (열,행)의 순서로 데이터가 들어간다는 점만 기억하면 된다.

 

마치며...

이번 강좌는 여기까지입니다. VB.NET으로 대용량 엑셀을 열때는 신경써야될 부분들이 제법 있고, 이 부분들을 최대한 강좌에 녹여 넣었으니 총 3개의 강좌를 꼼꼼하게 살펴본다면 어렵지 않게 엑셀을 다룰 수가 있을 거에요. 사실 다음 강좌에서 엑셀파일을 생성해서 엑셀에 데이터를 쓰는 강의를 해보려고 했는데, 일반적으로 엑셀을 읽을 일은 많아도 만들 일은 별로 없기에 어떤 강좌로 돌아올지는 좀더 고민하고 결정해야 할듯 합니다.

 

여기까지 읽어주셔서 감사합니다. 즐거운 하루 보내세요.