VB.NET에서 엑셀 Application객체사용법

http://www.java-samples.com/showtutorial.php?tutorialid=1054

http://www.vbdotnetforums.com/vb-net-general-discussion/33955-save-all-data-datagrid-ms-excel-visual-basic-net.html

위의 주소를 참조해보시면.. 엑셀과 관련된 내용이 있습니다.

간단히 사용할 수 있는 방법은 다음을 참조하세요.

-----------------------------

Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ExcelApp As Object
Dim WorkB As Object
ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = False
WorkB = ExcelApp.Workbooks.Open("c:\book1.xls")

Dim a As Integer
Dim b As Integer
Dim strTxt As String = ""

For a = 1 To 4
For b = 1 To 4
strTxt &= WorkB.sheets("sheet1").cells(a, b).value & "/"
Next
strTxt &= vbCrLf
Next
MsgBox(strTxt)

WorkB.close()
WorkB = Nothing
ExcelApp = Nothing

End Sub
End Class

-----------------------------

위의 예는 Excel의 값을 읽어오는 내용이고.. 값을 집어 넣는다면..

WorkB.sheets("sheet1").cells(a, b).value ="xxx" 와 같이 하시면 됩니다.

또 다른 방법으로는 Excel Library를 참조하여 사용할 수 있는데.. 방법은 다음과 같습니다.

먼저 메뉴의 [프로젝트]->[참조추가] 를 클릭합니다.

참조 추가 창에서 Excel Object Library를 선택하고 확인을 누릅니다.

그런 다음 아래 예제 코딩으로 테스트를 해보시기 바랍니다.

--------------------------------

Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim range As Excel.Range
Dim rCnt As Integer
Dim cCnt As Integer
Dim Obj As Object

Dim strTxt As String = ""

xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Open("c:\book1.xls")
xlWorkSheet = xlWorkBook.Worksheets("sheet1")

range = xlWorkSheet.UsedRange

For rCnt = 1 To range.Rows.Count
For cCnt = 1 To range.Columns.Count
Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
strTxt &= Obj.value & "/"
Next
strTxt &= vbCrLf
Next

MsgBox(strTxt)

xlWorkBook.Close()
xlApp.Quit()

releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
End Sub

Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
End Class
--------------------------------

도움 되시길 바라며 즐거운 하루 되세요.