PROGRAMMING WORKSHOP

.Net FrameWork,VB.Net | DataSet,DataTable

DataTable에 엑셀데이타를 담아서...

엑셀에서는 하나의 통합문서내에서 데이타도 있고, 보고서도 있고,
분석을 하기위한 수식이 혼란스럽게 들어가기도 하고
아무튼 크게 나누어서 [데이타]와 [사용자인터페이스]가 짬뽕으로 같이 들어 있다
하지만 앞페이지에서 하였던 내용은 데이타는
외부의 데이타전용장치(XML이던, DB이던, Excel화일이던,Text화일이던)에
별도로 보관하고 이 데이타를 받아들일수 있는 구조설계를 DataTable에서 하고
DataTable을 윈도우의 콘트롤등에 연동하여서 사용하는 각각 기능별로 분산된 형식이 되는 셈이다
그래서 엑셀에서 VB.Net으로 오면 혼란 스러워진다
하지만 , 차근 차근 이해하면 대단한 개념이 잡힌 정보관리 프로그래머가 되게 된다

DataTable, DataSet개체에 WriteXML, ReadXML등올 간단하게 XML과 연동이 되었었다
이번에는 엑셀화일의 내용을 DataTable에 넣어보자
이제는 디자인 타임에 하지 말고
런타임에 DataTable을 만들고 사용하도록 하자
이것이 훨씬 있어보이고, 융통성있는 작업이 될 수 있는 것이니까..

VB.Net에서 엑셀을 사용하겠다고 하는 순간..
이런 생각이 자동으로 들어야 한다
흠..그렇다면 Excel라이브러리를 참조하여야 겠군
엑셀 VBA에서 외부참조를 많이 하였던 기억이 난다면 바로 그 짓을 VB.Net에서도
해야 하는 것이다
그림과 같이 Add Reference, 참조하기메뉴로 참조대화상자에서



.Net탭, Com탭등이 있는 중에서 만약 .Net탭에
Microsoft.Office.Interop.Excel 라는 Interop이 있으면 이것을 참조해도 되고
없으면 COM탭에서 Microsoft Excel을 선택하여도 되고..
사용하는 버전에 따라서 다를수 있으니 찾아 보시면 된다
엑셀라이브러리를 선택하여 참조시키면 엑셀을 VBA에서 사용하듯이 사용하게 된다

엑셀의 틀에서 벗어나서 큰틀로 데이타라는 것을 본다면



데이타는 이곳,저곳 널려 있다, 여러분의 노트에 기록된 것도 있을 것이고
DB에 정리정돈이 된 것도 있을 것이고, 웹의 어떤 노트에 기록이 되었을수도 있고
XML문으로 정리가 된 것도 있을 것이고
Text문서로 보관된 것도 있을수 있고, 엑셀화일에 보관되었을수도 있고
다양하게 사람생긴것만큼이나 정보가 널려있다
프로그래머는 이런 산발적으로 널려있는 정보를 원하는 목적대로 볼수 있게
잘 재가공하는 기술자들인 셈이다
이런 고민을 해결하려고 .Net FrameWork에서는 다양한 도구를 제공하려고 노력하고
개선해 나가게 되는 것
그 중의 최근기술이 바로 DataTable을 여러개 갖거나
하나만 갖거나 하는 DataSet이라는 개체이고
이것 외에 다양한 집합체와 배열등의 개선된 내용들을 제공하게 되는 것이다
그리고 또한 이런 개체에 임시 모아진 정보를 원하는대로 또한번 정리해주는
LINQ라는 언어가 최근까지의 기술인 것이다

물론 LINQ는 발전하여 DB나 XML에서
중간개체(DataTable이나 Collection들) 걸치지 않고 접근처리도 하게 된다
그런것들을 하나, 하나 전개해가는 것중에
이번페이지에서는 엑셀화일의 정보를 불러 들여서
DataTable에 넣고 LINQ를 사용해보도록 하자

RunTime으로 엑셀화일을 열고 정보를 읽어도 좋겠지만
여기에서는 프로젝트에 화일을 그림과 같이 삽입하고 읽어들이도록 하자



항상 개체는 속성이라는 것이 있다는 것을 잊지 말고
속성에 디자인타임에 설정을 하던, 런타임에 코딩으로 하던 항상 개체의
성질머리를 관리한다는 것을 잊지 마시고..
여기에서는 화일 속성을 디폴트로 되어있는 Do Not Copy를
Copy If Newer 로 바꿔주자, 이것은 복사본을 항상 배포하는 디렉토리에 포함시키는 것..

물론 엑셀화일은 화일열기로 사용자의 화일을 열어서 처리하기도 할수 있는 것이고
여기에서는 프로젝트에 임베드시켜놓고 해보자

라이브러리도참조하고
엑셀화일도 프로젝트에 포함시켰고
윈도우폼의 크래스모듈에서

Imports XL = Microsoft.Office.Interop.Excel
Public Class Form1
...
...
...
End Class

와 같이 라이브러리를 사용하겠다고 Imports 시키자..
XL이라고 표시한 것은 앞으로 엑셀을 사용할때는 XL을 앞에 붙이기만 하면 된다
그리고 엑셀의 개체는 VBA에서 사용하던 것과 똑같다
그러니 VB.Net의 윈도우에서 엑셀을 사용하던 어디에서 사용하던
라이브러리만 참조하고 엑셀의 기능을 사용하면 된다
여기에서는 엑셀의 기능을 사용할 것은 없고, 엑셀을 단순한 정보를 보관하고 있는
창고역할로 본다
앞페이지에서 XML을 사용하였던것 대신에 엑셀을 사용하는 것

Windows Form은 빈탕으로 아무컨트롤도 그리지 않고 Run-Time에 그려 넣기로 한다
아래와 같이 Form_Load이벤트프로시져에 작성하자..

Imports XL = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Try
            '## 엑셀개체 생성하고, 대상이 되는 통합문서 열고..
            '## 엑셀테이블 얻어내고..
            Dim oApp As New XL.Application
            Dim oBook As XL.Workbook = oApp.Workbooks.Open(My.Application.Info.DirectoryPath & "\sampleData.xls")
            Dim oDataSheet As XL.Worksheet = oBook.Worksheets(1)
            Dim oXLTable As XL.Range = oDataSheet.Range("A1").CurrentRegion
            Dim oXLTableHeadRow As XL.Range = oXLTable.Rows(1)
            Dim oXLTableData As XL.Range = oXLTable.Offset(1).Resize(oXLTable.Rows.Count - 1)

            '## DataTable 개체를 생성한다

            Dim sColName As Object
            Dim oData As Object
            Dim iCol As Integer = 1
            Dim oTable As New DataTable
            Dim oCol As DataColumn
            '## 엑셀의 열머리 행을 셀별로 순환하면서

            For Each rColHead As XL.Range In oXLTableHeadRow.Cells
                ' 순환할때마다 DataTable의 열개체를 생성한다
                oCol = New DataColumn
                ' 엑셀의 열머리값을 받아서
                sColName = rColHead.Value
                ' DataColumn의 ColumnName 속성과 Caption속성의 값을 준다
                oCol.ColumnName = CStr(sColName).Replace(" ", "")
                oCol.Caption = CStr(sColName).Replace(" ", "")
                ' 그리고 중요한 것은 DataTable의 열의 데이타타입을 
                ' 엑셀의 데이타행의 각 열에 해당하는 것을 검토하여..
                oData = oXLTableData.Rows(1).cells(iCol).value
                Try
                    ' TypeName함수로 해당 셀값의 타입을 알아낸다
                    ' 엑셀은 빈셀의 데이타타입은 디폴트로 Double타입이다, 즉 숫자는 모두 Double로 간주한다
                    ' 그리고 여기에서는 문자열(String)과 숫자(Double)그리고 날짜(Date)타입을 알아보면 된다
                    Select Case TypeName(oData)
                        Case "Double"
                            oCol.DataType = GetType(Double)
                        Case "Date"
                            oCol.DataType = GetType(Date)
                        Case "String"
                            oCol.DataType = GetType(String)
                    End Select
                Catch ex As Exception
                    ' 만약 데이타타입을 읽으면서 에러가 났다면 문자열로 처리하고..
                    oCol.DataType = GetType(String)
                End Try
                '' 이렇게 생성된 열을 DataTable의 Columns 집합체에 추가한다
                oTable.Columns.Add(oCol)
                '' 엑셀의 각열의 값에 접근하기 위한 iCol변수를 1씩추가하고
                iCol += 1
            Next

            '## 이제 데이타를 채운다, 엑셀테이블의 행을 순환하면서
            For Each rRow As XL.Range In oXLTableData.Rows
                '' DataTable의 새행을 만들고
                Dim oRow As DataRow = oTable.NewRow
                '' 순환된 엑셀행의 각셀을 다시 순환하면서 각 값을 DataTable의 셀(Item)에 옮긴다
                iCol = 0
                For Each rX As XL.Range In rRow.Cells
                    Try
                        oRow.Item(iCol) = rX.Value
                    Catch ex As Exception
                        oRow.Item(iCol) = Nothing
                    End Try

                    iCol += 1
                Next
                '' 만들어진 행을 DataTable의 Rows집합체에 추가시킨다
                oTable.Rows.Add(oRow)
            Next
            '' 엑셀의 임무는 끝났으니 , 엑셀을 닫고
            oApp.Quit()
            '' 그런데 그냥 엑셀 Application을 Quit 시키면 될까???
            '' 아니다.!!!
            '' .Net FrameWork 환경에서 Com 오브젝트를 만들어서 사용하면 
            '' excel 개체쓰레기가 메모리에 차곡차곡 쌓인다
            ''물론 이렇게 .Net FrameWork가 설계된 것은 아니라고 하는데
            '' 아무튼 쌓인다
            '' 한참 하다보면 엑셀이 버벅거리기 시작한다
            '' 엑셀 Application이 계속 만들어져서 메모리에 쌓여 버리는 것이다
            '' 즉 Quit로 처리되지 않는다
            '' 별도의 무언가를 쓰레기 처리작업을 누군엔가에게 시켜야 한다

            '' 엑셀같은 Com개체는 별도의 사용하고 남은 개체를 메모리에서
            '' 안전하게 쓰레기를 제거하여야 한다
            '' 아래 두줄을 넣어주면 된다
            ''안전을 위하여 두번 반복하여 넣어주는 것이 좋다
            ''타이밍이 안맞아서 놓치고 가면 쓰레기를 못치울수도 있으니까.... 
                                                                                         
            GC.Collect()
            GC.WaitForPendingFinalizers()
            '' 확인 사살..
            GC.Collect()
            GC.WaitForPendingFinalizers()

            GC는 garbage collection 의 약자이다, 말그대로 쓰레기수집인셈이다

            ''############
            ''DataTable의 완성되고
            ''
            ''## DataGridView 생성..

            Dim oDGV As New Windows.Forms.DataGridView
            ''DataSource 속성에 위에서 만든 DataTable을 연결하고
            oDGV.DataSource = oTable

            '' DataGridView의 속성..크기,
            'oDGV.Size = New Size(Me.ClientSize.Width, Me.ClientSize.Height)
            'oDGV.Location = New Point(0, 0)

            ' DataGridView의 Dock속성을 Fill로 하면 폼의 크기에 꽉차고
            ' 폼의 크기를 늘리거나 줄이거나 같이 움직인다
            ' 그래서 위와 같이 크기나 위치를 정해줄 필요없는 편리한 것이다
            oDGV.Dock = DockStyle.Fill
            ' 정보를 추가하지 않는 그냥 보는 것으로 하기 위하여 정보추가 행없애고..
            oDGV.AllowUserToAddRows = False
            ' 폰트서식하고
            oDGV.Font = New Font("맑은 고딕", 9)
            ' DataGridView를 폼의 Controls집합체에 추가하고..
            Me.Controls.Add(oDGV)



        Catch ex As Exception
           
        End Try

    End Sub




    

엑셀의 Application개체를 생성해야 되는 것이 우선이라는 것은
Word에 VBA로 엑셀을 다루어 보았다면 이해가 되실 것이다
라이브러리를 참조한후
Imports시키지않고 아래와 같이 작성해도 된다
Dim oApp As New Microsoft.Office.Interop.Excel.Application
하지만 모든 엑셀의 개체(Worksheet,Workbook,Range)등을 생성하려 할때마다
긴경로를 사용하면 비생산적이다
그래서
Imports Microsoft.Office.Interop.Excel
이라고 Imports를 시키면 아래와 같이 간단하게 처리된다
Dim oApp As New Application
그런데 마침 워드 문서도 같이 처리하려고 워드도 Imports시켰다면
워드이던, 엑셀이던 모두 Application개체를 갖고 있다
그러니 작성하는 사람도 헷갈리고, 컴퓨터의 컴파일러도 헷갈려서 에러를 낼 것이다
그래서...
Imports XL=Microsoft.Office.Interop.Excel
Imports WD=Microsoft.Office.Interop.Word
라고 XL이라는 것에 Excel 의 네임스페이스를 담아 놓으면
Dim oAppXL=New XL.Application
Dim oAPPWD=New WD.Application
과 같이 식별하기 좋게 표현될수 있는 것이다

화일의 보관장소의 접근은 프로젝트에 포함된 화일은
My.Application.Info.DirectoryPath "\" & 화일명 으로 접근한다
Dim oBook As XL.Workbook = oApp.Workbooks.Open(My.Application.Info.DirectoryPath & "\sampleData.xls")
My라는 네임스페이스 이하에는 하위 네임스페이스와 개체즉
많은 꿀단지들이 있으니 애용하게 될 것이다

아래구문은 VBA에서 하였던 것 그대로이다, 단지 네임스페이스만 앞에 붙어서
VB.Net내에서식별하게 한다는 점만 유의하시면 된다

Dim oXLTable As XL.Range = oDataSheet.Range("A1").CurrentRegion
Dim oXLTableHeadRow As XL.Range = oXLTable.Rows(1)
Dim oXLTableData As XL.Range = oXLTable.Offset(1).Resize(oXLTable.Rows.Count - 1)

엑셀의 테이블범위에 접근하여 이제
.Net FrameWork 환경에서 제공해주는 개체를 사용할 단계..
VBA의 언어는 언어이고
엑셀개체는 별개의 것이듯이
VB.Net에서도 VB는 언어는 언어일뿐
필요한 것은
.Net FrameWork의 개체를 활용하는 것이다
헷갈리지 마시기를, 언어와 개체는 다르다....
엑셀의 테이블의 정보를 .Net FrameWork 개체들이 자유롭게 이해하고 가공할수 있는 개체에
담아주어야 하는 것
데이타를 담당하는 대표적인 것은 .Net FrameWork환경의
System.Data 라는 네임스페이스상의 DataTable이라는 개체를 사용하게 된다

앞페이지에서 Design Time에 DataTable을 만들어 보았었다
여기에서는 Run Time으로 DataTable을 만들어 보는 것
DataTable개체는 엑셀의 시트와 같이 열과 행으로 구성된다
그러니, DataTable을 만들려면
Dim oTable As New DataTable
과 같이 New로 생성한다..
DataTable은 Syetem.Data 라는 네임스페이스에 들어 있지만, DataTable은 디폴트로
이미 항상 라이브러리가 참조되어 있으니 Imports시키거나 System.Data라는 네임스페이스를
붙일 필요 없다
그리고 열(Column)개체를 만들어 붙여나간다..
엑셀의 시트와 같이 시트를 삽입할때마다 행과 열이 만들어지는 것이 아니고
사용자가 만들어서 사용한다고 생각하시면 좋다
그러니 사용하고 싶은 열만큼, 행만큼 마음대로 만들어서 사용할수 있는 것, 편리하고 좋지 않은가??
데이타를 보관하고 싶은 창고를 스스로 짓고, 부시고,
변수선언을 하는 것을 좀더 큰 사이즈의 변수선언쯤으로 쉽게 생각하시면 된다
엑셀의 Range개체에도 속성이 아주 많이 있듯이
DataTable,DataColumn,DataRow 개체에도 많은 속성도 있고,메소드도 있고,이벤트도 있다
그런 것들과 친숙하게 되고,
이런 .Net FrameWork환경에서 제공하는 개체를 사용하려고 하는 것은
LINQ라는 VB속의 또 다른 확장된 쿼리언어(원하는 값을 찾는)를 사용하기 위함이다

VBA에서 엑셀시트의 정보를 읽어서 VB.Net환경의 개체에 옮길때
검증이 철저하지 않으면 안되는점
그래서 항상 Dim oX As Object =엑셀의 셀의 값
VBA에서는 Range("A1") 이라고 하면 그냥 Default로 Value속성이 붙어있는 것으로
자동처리되었다
하지만 VB.Net에서는 엑셀은 외계인이다..그러니 Range개체의 Default속성을 너그럽게
인정 하지 않는다
반드시 Range("A1").Value 혹은 Range("A1").Text등 속성을 붙여주어야 한다
Object개체는 성격은 좀 다르지만, VBA에서 Variant변수타입과 같다고 보시면 우선
어떤 타입의 값이라도 담을수 있다는 것으로 챙겨두시면 된다

아래화일에서 엑셀의 정보를 DataTable개체에 옮겨서
DataTable개체를 DataGridView에 바인딩시키는 요령을 잘 이해할수 있을 것이다
다음페이지에서는 DataGridView의 내용을 LINQ를 사용하여 쿼리를 해보면서
점점 VB.Net과 .Net FrameWork와 친숙해지도록 하자

***[LOG-IN]***