VB.NET 엑셀 업로드 (Import Excel)

VB.NET으로 개발하다 엑셀 업로드를 해야 하는데 전체 행 중에서 특정 데이터만 선택해서 그리드에 입력해야 하는 일이 있었습니다.

 

엑셀 업로드 방법은 여러 가지 방법이 있지만 Dataset에 저장해서 필요한 데이터만 선택하던지 혹은 전체를 올리는 방식은 추후 시간이 되면 따로 작성하도록 하고 이번에는 필요한 데이터의 위치를 지정하고 그 값을 얻고자 합니다.

 

실제 적용도 해보고 정리도 할 겸 글로 남깁니다.

---

* 예제

예제

- 위와 같은 예제에서 필요한 데이터는 빨간 테두리 안의 데이터입니다.

---

* 코드

'프로젝트의 참조에 Microsoft.Office.Interop.Excel 추가


'선언(필수)
Imports Excel = Microsoft.Office.Interop.Excel


'원하는 위치에서 사용 가능(버튼 이벤트, 폼 로드 등등 활용 가능)
Dim xlapp As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlws As Excel.Worksheet
Dim range As Excel.Range


xlapp = New Excel.Application
'엑셀 파일 경로 지정
xlwb = xlapp.Workbooks.Open("c:\test.xls")
'업로드에 사용할 sheet의 index
'시트명으로 사용 가능
xlws = xlwb.Worksheets(1)


'추가


'필요한 데이터 위치 지정 및 값을 얻기 위해 for문 사용
'예제의 빨간 테두리의 데이터가 필요하므로 시작 위치는 5부터 시작
'불러온 엑셀의 인덱스 넘버링은 0이 아닌 1
For i = 5 To xlws.Rows.Count
    '데이터 위치 지정 예제 : xlws.Cells(row, col).value
    'col은 문자 또는 숫자로 사용 가능
	
    Trim(xlws.Cells(i, 1).value) '값 = ""
    Trim(xlws.Cells(i, "B").value) '값 = 대표
    Trim(xlws.Cells(i, "C").value) '값 = 이바니
    Trim(xlws.Cells(i, 4).value) '값 = 85
    Trim(xlws.Cells(i, "E").value) '값 = 회사
    Trim(xlws.Cells(i, 6).value) '값 = ""
Next i

'엑셀 파일 연결 해제
xlwb.Close()
xlapp.Quit()

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlapp) : xlapp = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlwb) : xlwb = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlws) : xlws = Nothing

- 위와 같이 하면 간단하게 필요한 데이터의 위치를 지정하고 값을 얻을 수 있습니다. 하지만 위 코드를 사용하게 될 경우 불러온 엑셀 파일의 인식 가능한 전체 행(Excel 2010 기준 1,048,576행)을 대상으로 하기에 시간 및 자원의 낭비가 초래되므로 조금의 수정이 필요합니다.

---

* 코드 수정

'추가
range = xlws.UsedRange


'For i = 5 To xlws.Rows.Count <- 미사용
For i = 5 To range.Rows.Count

- 원본 코드 중 '추가'라고 적힌 곳이든 혹은 적합한 위치에 'range = xlws.UsedRange'를 추가하고

for문은 'For i = 5 To range.Rows.Count'로 변경합니다.

 

- UsedRange는 불러온 엑셀 파일에서 마지막 데이터가 있는 행과 열의 위치를 반환하기에 시간 및 자원의 낭비를 막을 수 있습니다. (예제 데이터에 UesdRange 적용 시 행 17, 열 5 반환)

 

- 예제에서는 range의 row만 사용하였으나 같은 방법으로 col도 사용 가능하므로 활용하시면 됩니다.

---

이상입니다.

 

※ 잘못된 부분이나 틀린 내용이 있다면 지적해주세요.