엑셀의 자동화를 vb.net에서
앞페이지에서는 통합문서를 윈도우폼에서 띄웠었다
이제 버튼을 크릭하면 통합문서, 즉 엑셀을 자동화해 보이면서
그렇게 자동화하려면 VBA로는 어떻게 작성하는 지를 보여주는 학습도구의
쌤플을 만들어 보자
응용하여 여러분들이 후학들이나 후배들에게 엑셀프로그래밍을 학습시키는
더 발전된 도구로 만들어 보시겠다는 생각을 갖고 보도록 하자
VB.Net에서 엑셀을 자동화하는 코드를 별도의 프로젝트로 하는 것이 좋을 것이다
이렇게 프로그램을 하다가 보면, 이것을 별도의 프로젝트로 할까..
아니면 하나의 프로젝트에 몽땅 털어 넣을까??
고민을 하게 되는 부분들이다..
윈도우 프로그램이 아닌, 엑셀 VBA에서도 라이브러리를 참조하게 하면
좋을 것 같은 생각도 들고,
또 다른 인터페이스에 엑셀을 다루는 부분을 별도로 불러서 쓸수도 있을 것이고..
그런 생각을 하게 될때는 별도의 프로젝트를 만드는 것이 좋다
소루션에 Add/New Project 하여 크래스모듈프로젝트를 하나 추가한다
프로젝트명을 UNO_VB_XL 로 지어주고..
크래스모듈명은 XLApplication으로 지어준다
그래고 계속 크래스모듈을 삽입하여 확장하여 나가게 된다
XLRange라는 크래스를 만들고..
XLGeneral이라는 크래스를 우선 만들자
나중에 필요에 따라서 추가하면서 만들어 나가고..
각각의 모듈시트는 아래와 같이 제목만 있고 빈탕으로 만들어지게 되겠지..
그리고 ,엑셀을 다루어야 하니까..
엑셀 라이브러리(Microsoft.Office.Interop.Excel)를 참조하여야 한다
그리고 임포트(Imports)시켜야 코딩이 순조롭고
그리고, 추가된 프로젝트가 COM라이브러리로도 사용할수 있게
(VBA편집기의 참조에서 참조하여 VBA에서도 사용할수 있는 라이브러리 화일)
프로젝트화일에서
Application/Assembly Information/Make Assembly COM-Visible을 체크해주고
Compile/Register for COM Interop을 체크해주고
크래스모듈에 몇줄 추가 해준다
--------------------
Imports XL = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
<ClassInterface(ClassInterfaceType.AutoDual)>
Public Class XLRange
...
...
End Class
--------------------
Imports XL = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
<ClassInterface(ClassInterfaceType.AutoDual)>
Public Class XLApplication
...
...
End Class
-------------------
Imports XL = Microsoft.Office.Interop.Excel
Imports FM = System.Windows.Forms
Imports SD = System.Drawing
Public Class XLGeneral
Public Shared Function getSampleTable1(..)
...
...
End Function
...
...
End Class
-------------------
위의 크래스중 XLGeneral 크래스는 공통으로 사용하는 프로시져등을
Shared 멤버( 공유멤버)로 활용하도록 한다
Shared 라는 키워드를 붙이면 해당 크래스를
Dim oX As New XLGneral
같이 개체를 만들지 않고, 그냥 크래스명으로 모듈시트의 프로시져에 접근하듯이
접근할수 있어서 편리하다
이제 메인프로젝트 윈도우폼에서 크래스모듈을 사용하려면
크래스모듈프로젝트를 참조하여야 한다
아니 같은 소루션속에 있는데 그냥 사용하면 안되나요?
아니다...소루션속에는 본래 하나의 메인프로젝트가 있고 다른 프로젝트는
그냥 편의상 작업을 할수 있게 하고 관리할수 있게 나타날뿐이지
전혀 다른 프로젝트인 것이다
그래서 참조를 하기 위하여서는 저장하고
크래스모듈프로젝트를 빌드하여야 참조목록에 나타난다
빌드하면 아래의 그림과 같이 소루션폴더내에 두개의 별개의 프로젝트폴더를
유지하게 된다
아래와 같이 프로젝트속성창에서 참조시키고..
전체의 흐름은
윈도우폼에서 탭콘트롤마다 엑셀의 각각의 개체를 다루고
각 탭페이지에서는 해당 개체의 구성원의 코딩쌤플을 구현해볼수 있는 버튼을 만든다
버튼을 크릭하면...
VBA에서 사용하는 코드와 VB.Net에서 엑셀을 자동화하는 코드는
똑같지만, VB.Net상에서는 무언가 약속한 단어들을 사용하는 것이 있다
많지는 않지만, 이것을 통하여 엑셀이라는 개체를 다른 개체에서
사용할때는 외부 개체이므로 당연히 무언가를 표시해야 한다는 점..
이것을 같이 숙달 시키는 것이 이 화일의 목적이다
그러니, VBA에 숙달이 되었다면, VB.Net을 통하여 윈도우폼에서
마음대로 활용할수 있다점, 신나는 일 아닌가??!!!
이미 배웠던 것을 버려버려야 한다면 골에 지진이 날일이지만
이미 배우 것을 확장하는 것이니, 신이 나는 것이다
윈도우폼에서 버튼을 크릭하면 VB.Net코드에서 엑셀화일의 시트에
원하는 작업을 하게 한다..
그런데 이것을 학습하는 사람들에게 VBA로는 어떻게 구현하는지 코드를
보여주고 싶은 것이다..
그럼 이 VBA코드를 어디엔가 보관시켜야 한다
다양한 방법이 있겠지만, 이곳에서는 이미 만들어 놓은 자원(Resource)화일인
텍스트화일에 이렇게 해당 작업에 대한 VBA코드를 작성해주고
버튼을 크릭하면 이것을 읽어서 시트에 친절하게 써준다
문자열 정보를 다루는 것은 프로그래밍의 기본이고
문자열을 다루는 것이 능숙하면 천하무적이다
안드로이드던,무엇이던, 문자열정보에 능숙해야 한다
위의 허접해 보이는 텍스트화일의 문장을 요리 조리 읽어서
제목은 제목대로, 코드는 코드대로 구분하여 분리 처리하는 문자열정보의 다룸
자꾸 숙달, 발전시키면 된다
앞페이지에서 자원화일을 개체로 접근하는 것도 좋지만,
자원의 아이템(개체)가 계속 생긴다면, Select Case로 접근하는 것은
뭔가 무식한 짓 같다
변수로 자원에 접근할수 있는 방법은 없을까?
이런 문제를 갖고 있는 것은 이미 모두 준비되어 있다
우리가 모르고 있을 뿐이지..
아래의 그림과 같이
My.Resources.개체명
대신에 ResourceManager의 GetObject메소드로 접근하면 두줄이면 끝난다
My.Resources.ResourceManager.GetObject(변수)
학습도구를 만들다보면 쌤플테이블을 많이 만들게 된다
이런 공통적인 작업은 하나의 공통크래스에 넣고 사용한다
피벗테이블을 만들기 위한 하나의 테이블을 만드는 예로 아래의 그림과 같이 만든다고 하면
VBA에서는 하나의 행에 같은 한꺼번에 값을 넣기 위하여
Array함수를 자주 사용하였다
VB.Net에서는 Array함수대신 {"A","B","C"} 같이 사용하면 배열이다
위의 테이블쌤플을 만들기 위한 코드를 보자면..
Public Shared Function getTableForPivot(shtX As XL.Worksheet)
Dim oRandom As New Random
Dim rTbl As XL.Range = Nothing
With shtX
''Array함수 대신에 배열을 직접 표현한다
.Range("A1").Resize(, 4).Value = {"날짜", "품목", "판매수량", "단가"}
For iRow As Integer = 2 To 50
''Totay개체의 AddDays 메소드로 날짜를 만들고
''품목도 배열에서 랜덤으로 골라내고
.Range("A" & iRow).Resize(, 4).Value = {Today.AddDays(oRandom.Next(1, 100)), {"A", "B", "C", "D"}(oRandom.Next(0, 3)), oRandom.Next(10, 100), ""}
''단가 부분은 품목에 따른 단가가 같아야 하니까..
''단가표를 배열로 한후..앞의 셀의 품목이 "ABCD"라는 문자열에 몇번째에 위치하는지 문자열개체의 IndextOf 메소드로 찾아서 단가배열에서 단가를 찾는다
.Range("A" & iRow).Offset(, 3).Value = {100, 150, 200, 250}("ABCD".IndexOf(.Range("A" & iRow).Offset(, 1).Value))
''위에서 판매수량이 문자로 입력이 되었을수 있으니, CInt함수로 숫자로 바꿔준다
.Range("A" & iRow).Offset(, 2).Value = CInt(.Range("A" & iRow).Offset(, 2).Value)
Next
rTbl = .UsedRange
rTbl.Columns.AutoFit()
End With
Return rTbl
End Function
VBA에서 날짜를 다루려면,
Dim datX As Date
datX = DateAdd("d", 2, Date)
와 같이 DateAdd같은 함수를 사용하지만, VB.Net에서는 Today라는 개체에 AddDays메소드등을
사용하여 편리하게 작업한다
랜덤값도 Int(Rnd()*3)+1 등과 같이 하는 함수를 사용하지 않고
Dim oX As New Random 하고 ,
oX.Next(1,30)하면 1과 3사이의 랜덤값을 만들어준다
문자열정보 "ABCD"에서 B라는 문자가 몇번째에 있는지를 알기 싶다면
VBA에서는 inStr("ABCD","B") 와 같이 InStr함수를 사용하지만
VB.Net에서는 "ABCD"라는 것 자체가 문자열개체이다
그래서 그냥
"ABCD".IndexOf("B") 라고 하면 몇번째 인는지 알수 있다
이때 IndexOf가 찾는 위치는 0부터시작한다
즉 "A"라는 문자는 "ABCD"의 0번째 있는 값이다
배열에서 값을 찾는 것은
{100,200,300,400}(0) 이라고 하면 100을 찾아준다
CommandBar,CommandBars개체는 어디에..
엑셀이나 워드, 파워포인트를 프로그래밍할때 CommandBar에 접근을 할필요가 있을때가 있다
VB.Net에서 엑셀의 CommandBar개체에 접근하려면..
Microsoft.Office.Interop.Excel 라이브러리만 참조한상태에서는 에러가 난다
왜냐면 Commandbar개체들은 엑셀의 소유물이 아니다..
Office가 공통으로 엑셀,파워포인트,워드등에 공급해주는 개체들이다
그래서 라이브러리 하나를 더 참조시켜야 한다
Microsoft Office Object Library 룰 참조시키면
화일목록상에는 Microsoft.Office.Core 라는 명칭으로 나타난다
그리고 이것을 크래스모듈전역에 Imports 시키고 작업해야
CommandBar개체를 인식할수 있다
이것을 참조할때 참조목록의 Assemblies탭에서 없고 ,Com탭에 있으니
없다고 투덜대지 마시기를..
그렇게 필요하 라이브러리를 참조하여야 아래와 같이 CommandBar에 접근하는 구문에서
에러가 나지 않는다
Dim oBar As CommandBar = shtX.Parent.CommandBars.item("PivotTable")
oBar.Visible = False
또한 중요한 것은 집합체를 처리할때
VBA에서는 CommandBars("PivotTable") 이라고 해도 되는데
VB.Net에서는 반드시 CommandBars.Item("PivotTable")
와 같이 Item 메소드를 사용하여야 이해한다
VBA에서는 코딩의 편의를 위하여 Item을 생략해도 디폴트로 받아주기 때문이다
VBA에서 참조사용하기..
크래스라이브러리를 COM 소루션에서볼수 있게 하였으니
엑셀 VBA편집기를 열고 참조하면 아래의 그림과 같이 참조가 되고
원하는 크래스를 선택하고 해당 개체의 메소드를 실행하면 VB.Net에서 한것과
같은 내용이 실행되는 것을 볼수 있을 것이다
VBA설명부분은 제외되게 설계되었으니 이 부분은 VBA에서 호출할때는
나타나지 않는다는 점을 감안하시고...
그런데 엑셀 VBA에서 참조를 하게 되면,
VB.Net을 개발하는 디버깅에서 에러가 날 것이다
왜냐면,개발된 크래스를 다른 곳에서 참조하고 사용중이니..접근할수 없게 되는 것이
당연하다는 것을 이해하시게 될 것이고
이 학습도구면에서는 실용적인것은 아니지만, VB.Net에서 Com소루션을 어떻게 만드는지를
연습한다는 개념을 갖으시면 좋겠다
그런데 고민이 많이 생긴다..
VBA코드를 텍스트화일로 Resources(프로그램의 자원)으로 활용하자면
이것의 크기가 점점 커질 것이다
아무튼 우선 몇가지 예제적 코드를
실행시켜보면서 자원에 대한 문제의식을 갖여 보자
그리고 도형을 그리기 위하여 엑셀이 아닌 오피스라이브러리를 참조시킬때
오피스와 엑셀의 버전은 반드시 같아야 한다,
그렇지 않으면 에러가 난다
***[LOG-IN]***