엑셀 정보를 윈도우 컨트롤 DataGridView에 올려보기
정보의 보관은
DB를 당초부터 만들어서 사용할수도 있고
또 엑셀에 어떤 정보를 보관하다보니 너무 양이 많아지고 버벅거리기 시작하면
DB로 옮기게 되는 경우가 허다하기도 하고
엑셀에서 분석작업을 하는 것은 DB엔진을 사용하여 하고 싶으면
DB로 옮기기도 하고 왔다,갔다 필요에 따라 적절히 활용할수 있어야 한다
그래서 UNO_Weekly에서 시리즈로 하던 정보시트(유경수님이 보내주셨던 문제시트)를
그대로 옮겨보도록 하자
이내용은 다른 분이 질문하신 워크시트를 VB.Net으로 다룰때 에러가 난다고 하였던것에
대한 답이 되었으면 하는 다목적 시트가 되겠다
대개의 에러는 정보의 타입이나 정보의 크기를 다루는데 있어서 발생한다
엑셀을 데이타로 사용하는 것은 서식같은 것은 모두 없애고 순수한 값만
보관하는 것으로 유지하는 것이 좋다
순수한 데이타와 서식을 요하는 보고서등과 헷갈리지 않는 것이
엑셀 활용의 첫단계라고 할수 있다
엑셀의 화일위치를 알아야 하는 것이 중요할 것이다
그냥 지정된 경로에 화일명을 지정한 상태로 하고 처리하여도 되겠지만
실제 화일의 위치를 사용자가 찾아야 한다는 시나리오로 해보자
그래야 다양한 경험을 할수 있을 것이니까..
이 강좌화일의 목적은 무엇을 만드는 목적물이 아니고, 만드는 과정을
다양하게 경험하게 하기 위한 것이고, 이런 경험과 코드의 스니펫을 활용하여
각자 원하는 소루션들을 만들게 하여 드리는 것이 목적이다
위의 그림의 데이타엑셀화일을 적당한 폴더에 보관했다치고
사용자로 하여금 어디에 DB로 옮길 화일이 있는지 알아내어 코드에
전달하도록 하자
Shared Function getXLfileNameAndPath()
Dim oFileOpen As New Windows.Forms.OpenFileDialog
Dim oResult As DialogResult
Dim sResult As String = ""
oFileOpen.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\"
oFileOpen.Filter = "XL files(*.xlsx)|*.xlsx|XLS files(*.xls)|*.xls|CSV file(*.csv)|*.csv"
oFileOpen.FilterIndex = 1
oFileOpen.RestoreDirectory = True
oFileOpen.Title = "DB로 변환할 통합문서를 선택하세요"
oResult = oFileOpen.ShowDialog()
If oResult = Windows.Forms.DialogResult.Cancel Then
Else
sResult = oFileOpen.FileName
End If
Return sResult
End Function
계속 작업을 하다 보면 함수나 프로시져들이 점점 많아 질 것이다
VB.Net을 좀더 활용하는 것은 크래스모듈을 맘껏 삽입하고 사용하는 것이다
크래스모듈하나에 변수하나만 있어도 괞찮다
기능별로 함수나 프로시져를 모아 놓는 것이 좋다
위의 함수를 ,물론 크래스에서는 메소드로 모두 통하는 것이고,
myFile이라는 크래스모듈을 하나 만들어 넣고 작성해 두고..
지난화일에서 했던 DB관련 메소드는 모두 myDB라는 크래스에 모아 놓고
그리고 위의 함수(메소드)에 Shared라는 키워드(Modifier)를 붙였다
대개가 크래스모듈을 사용할때
Dim oClass As New myClass 와 같이 New로 생성하고 사용하지만
만약 Shared라는 키워드를 붙이면 New라고 크래스의 개체(Instance of Class)를 생성하지 않고
그냥 모듈시트의 함수와 같이 접근할수 있어 편리하다
한장의 크래스모듈에 아주 많은 크래스를 만들수도 있고
크래스명대로 크래스모듈을 별도로 삽입하여 하여도 좋고
자신이 효율적이라고 생각하는 프로젝트관리방식에 따라서 하면 된다
어떻게 하던 VB.Net콤파일러는 알아서 콤파일하여 결과는 같은 소루션이 되는 것이니까..
어떤 방법으로 해야 한다!! 라는 것은 없다
원칙과 기본을 이해하고 응용하면서 하면 된다
여기에서도 그냥 폼과 일반모듈시트하나로 하려다가 이왕이면 다양한 방식으로
표현해 보는 것이 학습하는데 다양함을 느끼고 감을 잡게 하기 위하여
분리 관리하는 방식으로 정리 해나가보도록 하자
이렇게 하면 좋은 것은 재활용이라는 점이다..
다른 유사한 프로젝트에서 그냥 크래스만 옮겨서 사용하면 된다
그래서 실은 기능별 크래스로 분리관리하는 것이 바람직 한 것이다
예를 들어서 위의 화일의 위치를 사용자가 알아 보는 것은 어느 프로젝트에서나
공통으로 사용하는 것들이다
그러니 그냥 크래스모듈만 가져다가 끼워서 사용하면 되는 셈이다
엑셀 화일열고 작업후 닫기-----
이제 엑셀을 열고 데이타를 어디엔가에 쏟아놓고 사라지게 만들어야 한다
어디에 쏟아 놓은 것인가..??메모리..배열...변수...어떤 집합체..다양한 장치가 있을 것이다
두말할 것없이 DataTable이라는 단어가 머리에 튀어나와야 한다
DataTable은 아마도 VB.Net(엄밀히 말하자면 .NetFrameWork)의 가장 파워풀한 메모리속의 저장장치다
이것을 활용하지 못하면 안된다, 이곳,저곳에서 많이 이야기 하는 개체이다
엑셀을 열려면 엑셀라이브러리를 참조시켜야 한다
참조탭에 보면 Net탭도 있고 Com탭도 있다
Expression 버전으로 VS를사용하시는 분들은 Net탭상에 엑셀라이브러리가 나타나지 않을수있으니
여기에서는 COM탭에서 가져오도록 하자
결과는 두개 모두 마찬가지다, VB.Net콤파일러가 같은 결과로 컴파일하니까..
현재 프로젝트에서 ADO도 참조하고, Excel도 참조했다
VBA에서는 특별한 경우에만 외부라이브러리참조를 하지만, VB.Net에서는 참조하는 것이
흥미로워지게 된다
그것이 VB.Net을 적극 활용하게 되는것이니까..
이제 이것을 사용할 myXL 이라는 크래스를 하나 만들자..
'' NameSpace를 Imports 시켜서 사용하는 것이 편리하다
'' 그렇지 않으면 매번 개체의 전체경로를 매번 작성해야 하니까..
'' 아래와 같이 하면 oXL로 엑셀에 접근하게 되고, 이것만 있으면 여러분이 VBA서 사용하던
'' 엑셀개체를 그대로 사용하는 것
Imports oXL = Microsoft.Office.Interop.Excel
Public Class myXL
'' 작업을 하면서 어떤 문제가 있을지 예측할수 있을 것이다
'' 문제가 발생하는 것에 대한 메시지를 상수에 정리해두는 습관을 갖게 되면
'' 코드관리에도 편리하고 전체를 한눈에 볼수 있는 환경을 구축하게 되는 셈이다
#Region "Variable"
Const MSG_NO_BOOK As String = "전달받은 경로상에 화일이 없습니다"
Const MSG_NO_SHEET As String = "해당 통합문서에 데이타시트가 불분명합니다"
Const MSG_NO_RANGE As String = "테이블의 구성이 충분하지 않습니다"
Const MSG_NO_TABLE As String = "시트의 테이블은 A1셀을 포함하여 구성되어야 합니다"
#End Region
'' 어떤 것이 또 필요하게 될지 모르는 것이지만
'' 1)우선 엑셀을 열고
'' 2)시트의 범위에 접근하여 내용을 DataTable에 담고
'' 3)엑셀은 임무가 끝났으니 버려버리는 과정이다
'' 4)중요한 것은 COM개체를 사용하였을때는 쓰레기 처리를 잘해야 한다
Shared Function getTableOfExcel(sPathAndFile As String, sShtName As String, iDBColCount As Integer) As DataTable
Dim oTable As DataTable = Nothing
Dim oApp As oXL.Application = Nothing
Dim oBook As oXL.Workbook = Nothing
Dim oSheet As oXL.Worksheet = Nothing
Try
oApp = New oXL.Application
oBook = oApp.Workbooks.Open(sPathAndFile)
oSheet = oBook.Worksheets(sShtName)
Dim rXLRange As oXL.Range = oSheet.Range("A1")
If rXLRange.Value Is Nothing Then
'' Throw New Exception("문제가 되는내용 메시지") 는 아주 편리한 작업상의
'' 문제를 사용자에게 보여줄수 있는 방식이다..
'' 일부러 에러가 난것 같이 하여 Exception(에러)를 발생시켜 놓고
'' 이것을 Catch 부분에서 처리하게 하는 것이다, 즉 한장소에서 처리
'' 그렇지 않으면 코드가 길어지고, 프로시져가 많아지면 어디시점에서
'' 문제가 생겨서 완전히 Thread를 빠져 나가려면 애를 먹는다
'' 무슨소린지,감이 안올수 있으니 다음 설명에서 더 이야기 하도록 하자
Throw New Exception(MSG_NO_TABLE)
End If
rXLRange = rXLRange.CurrentRegion
''현재 만들어진 DB테이블과 엑셀시트상의 테이블의 열의 갯수가 다르면
'' 유효하지 않은 것으로 퇴자를 놓도록 한다
If rXLRange.Rows.Count = 1 Or rXLRange.Columns.Count <> iDBColCount Then
Throw New Exception("[" & iDBColCount & "]개의 열이 있는 시트여야 합니다")
End If
''///////////////////////////////////////////////
'' 이부분 DataTable에 담는 부분은 다음 설명에서 하자..
'' 기본프레임만 우선 보고...
'' //////////////////////////////////////////////
Catch ex As Exception
''중간에서 문제가 생겨 Throw Exception 을 하면 이 Catch 브록에 걸려서
''처리 되는것...GoTo어쩌구 할 필요없다
''영어 그대로다..Throw 던지다 무엇을 New Exception( 새로운 예상치도 않은 경우발생메시지)
''이것을 Try Catch문의 Catch가 받아 먹는 것..
MsgBox(ex.Message)
'' Finally 블록또한 아주 편리한 장소다...위에서 어떤 에러가 났던..
'' 마지막에는 항상 아래의 구문을 처리하라!!! 라는 곳이다
Finally
'' 이부분이 중요한 부분이다..이것을 해주지 않으면
'' 메모리에 엑셀프로그램이 생성된후 닫아도 머물로 있는다..
'' 쓰레기를 버려야 한다
oApp.Quit()
releaseObject(oSheet)
releaseObject(oBook)
releaseObject(oApp)
End Try
Return oTable
End Function
....
....
위에서 releaseObject메소드를 호출하지 않고 쓰레기 처리를 하지 않으면
아래의 그림과 같이 프로세스메모리에 계속 엑셀이 남아 있는 것을 볼수 있다
아래의 프로시져는 잘 보관하시고 엑셀이던 워드던 파워포인트이던
VB.Net에서 개체생성후 작업후 버릴때 필수적으로 사용하여야 한다
Shared Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
'' GC 는 Garbage Collector의 약자..말그대로 쓰레기장, 이것의 Collect메소드로 수거하게 한다
GC.Collect()
End Try
End Sub
End Class
엑셀의 데이타를 만들어진 DB의 어떤 테이블에 옮기려고 할때
데이타가 유효한지의 유효성검사가 있는 것은 당연할 것이다
가장 기본은 열의 갯수가 엑셀의 열의 갯수와 DB의 지정한 테이블의 열의 갯수와 같은가??
그러려면 현재 만들어진 DB의 테이블의 열의 갯수를 알아내야 한다
역시 이런 작업들을 위한 함수들이 준비가 되어야 할 것이다
아래의 메소드는 지정한 테이블의 휠드명을 가져 온다, 휠드명을 가져 온다는 것은
열의 갯수를 알수 있는 것
DB를 처리하는 크래스에 아래의 메소드를 포함시키자
Shared Function getFieldList(ByVal sTableName As String, ByVal sconnectionstring As String)
Dim oCon As New OleDb.OleDbConnection(sconnectionstring)
Dim oFieldList As New List(Of String)
Try
oCon.Open()
Dim schemaTable As DataTable = oCon.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, sTableName, Nothing})
Dim DataRowArray() As DataRow = schemaTable.Select(Nothing, "ORDINAL_POSITION", DataViewRowState.CurrentRows)
For Each rRow As DataRow In DataRowArray
oFieldList.Add(rRow.Item(3))
Next
Catch ex As Exception
Finally
oCon.Close()
oCon = Nothing
End Try
Return oFieldList
End Function
위의 메소드도 자주 사용하는 것은 아니니 잘 보관하였다가 테이블의 휠드명만
알고 싶다면 그냥 복사하여 붙여 넣고 사용하시면 편리할 것이다
이 씨리즈를 마치면 많은 DB관련 라이브러를 하나의 크래스모듈에 보관하고
두고, 두고 사용하실수 있을 것이다
엑셀에서 정보를 불러서 해당 DB테이블에 넣기 전에
다른 경험을 또 해보도록 하자
.NetFramework에서는 항상 사용하여야 하는 데이타처리의 가장 뛰어난 도구
DataTable에 엑셀의 정보를 담아 보고
이 DataTable을 DataGridView의 DataSource속성에 주어 아래의 그림과 같이
표현해보고, DB테이블에 전달하는 것을 해보자
엑셀의 범위작업을 할때 배열로 하나의 행을 한꺼번에 입력하였었다
Dim rRow As Range
For Each rRow In rTable.Rows
rRow.Value=Array("X","Y","Z")
Next
와 같이 하나의 행을 한꺼번에 처리하는 것이 흔히 효율적으로 하는 방법이였다
그래서 .NetFrameWork 의 DataTable도 마치 엑셀의 범위나 흡사하다
그래서 아래와 같이
For Each rRow As oXL.Range In rXLRange.Offset(1).Resize(rXLRange.Rows.Count - 1).Rows
Dim oDataRow As Data.DataRow = oTable.NewRow
oDataRow.ItemArray = rRow.Value
oTable.Rows.Add(oDataRow)
Next
기특하게 잘 들어갈 것 같은데 그렇지 않다
왜냐면 엑셀의 범위는 2차배열이고
DataTable의 DataRow의 ItemArray속성은 1차 배열이다
그래서 받아들이지 못하고 에러를 내게 된다
엑셀에서는 엑셀이 알아서 1차배열이 들어오면 스스로가 2차배열이라도 알아서
조정해 주니까, 실감하지 못한고 있었던 것이다
이런 아주 미묘한 것들이 VB.Net에서 엑셀을 처리하면서 겪는 경험들이 된다
이 페이지에서 그런 경험의 시간을 단축시켜드리도록 노력하는 것
또한 위의 문제와 더불어..엑셀의 셀의 값이 빈값일때
If rX.Value="" Then
......
End If
와 같이 하면 에러가 난다
어라..VBA에서는 잘 되던것인데??!!#@
VB.Net은 철저한 개체중심의 언어다
그래서 위의 것은 아래와 같이 해야 된다
If rX.Value Is Nothing Then
......
End If
아래화일에는 restaurants.xlsx 화일이 같이 포함되어 있으니
적당한 폴더에 풀어 놓고, 엑셀을 DataTable로 변환하고
DataTable을 윈도우 콘트롤인 DataGridView에 연결하는 것을 해보시면서 내용을 보시기를..
***[LOG-IN]***