ASP.NET 2.0 웹 사이트와 Excel 및 Word 통합

2007. 1. 21. 23:23 IT 및 개발/ASP.NET & AJAX
요약: Microsoft Office Word 2003 및 Microsoft Office Excel 2003을 ASP.NET 2.0 웹 사이트에 통합하여 Word에서 편지 양식과 Excel에서 그래픽 보고서를 생성하는 방법에 대해 살펴봅니다.

개요

Microsoft는 Microsoft .NET Framework 2.0 및 Microsoft Visual Studio 2005를 선보이면서 Microsoft Office System용 Microsoft Visual Studio 도구 버전 2003을 사용하는 Microsoft Office 프로그래밍 기능에 새로운 방식을 추가했습니다. Microsoft Office System용 Microsoft Visual Studio 2005 도구는 Visual Studio에서 Microsoft Office Word 2003 또는 Microsoft Office Excel 2003의 IDE(통합 개발 환경)에 직접 프로그래밍하는 기능을 초기 버전에 추가하여 더욱 개선된 기능을 제공합니다. 이로써 작성한 구성 요소와 상호 작용하는 Office 응용 프로그램을 더욱 간편하게 만들 수 있게 되었습니다.

이 기사에서는 웹 서비스, 작업창, Word 및 Excel과의 상호 작용을 비롯한 Office 프로그래밍 기능의 몇 가지 새로운 측면을 활용하는 방법을 살펴보겠습니다. 웹 서비스를 사용하여 ASP.NET 2.0 웹 사이트에서 Microsoft SQL Server 데이터베이스에 연결하고, 데이터베이스의 데이터를 바탕으로 Word 편지 및 Excel 그래프를 만드는 방법을 보여 주는 코드를 설명할 것입니다. 이 기사에는 두 가지 웹 서비스를 만드는 Microsoft Visual Basic .NET 코드 샘플이 포함되어 있습니다. 첫 번째 웹 서비스는 모든 판매 직원 정보를 지역별로 추출하고 Word에서 감사 편지를 만듭니다. 두 번째 웹 서비스는 특정 기간에 대한 모든 구매 정보를 추출하고 Excel 2003에서 그래프를 작성합니다.

그림 1에서는 이 솔루션의 논리 구성도를 보여 줍니다.

그림 1. 응용 프로그램의 논리 구성도


예에서 사용된 웹 사이트는 Visual Studio 2005 시작 이벤트 중에 사용된 Adventure Works 웹 사이트에 바탕을 두고 있습니다. Adventure Works 웹 사이트는 ASP.NET 2.0 기술을 사용하여 작성되었으며 데이터베이스로 Microsoft SQL Server 2005를 사용합니다.

필요한 소프트웨어

이 솔루션에는 다음 소프트웨어가 필요합니다.

  • Microsoft Windows Server 2003 Standard Edition
  • Microsoft Office 2003 Professional Edition
  • 2007 Microsoft Office system용 Microsoft Visual Studio 2005 도구
  • Microsoft Visual Studio 2005 Professional Edition
  • Microsoft SQL Server 2005
  • Microsoft 인터넷 정보 서비스(IIS) 6.0
  • Microsoft ASP.NET 2.0


Visual Studio 2005를 사용하여 웹 서비스 만들기

먼저 Adventure Works라는 빈 프로젝트를 만듭니다.

  1. Visual Studio 2005에서 Adventure Works 프로젝트에 AdventureWeb이라는 ASP.NET 웹 서비스 프로젝트를 추가합니다.
  2. 그 다음 AdvOrders.asmx라는 첫 번째 웹 서비스를 추가합니다. 이 웹 서비스는 Excel에서 회계 연도의 판매 그래프를 만드는 데 사용됩니다. 웹 서비스 코드가 웹 프로젝트에 추가됩니다.
  3. Verify라는 함수를 만듭니다.
    참고참고:
    이 함수는 웹 서비스가 올바르게 작동하는지 확인합니다. 이를 통해 응용 프로그램을 시작하기 전에 기본 웹 서비스 기능을 확인할 수 있습니다. 웹 서비스가 작동하지 않으면 모든 응용 프로그램 상호 작용이 차단됩니다.

    함수를 만들려면 다음 코드를 웹 서비스 앞에 삽입합니다.

    <WebMethod(Description:="Returns OK if the Web service is online", _
          EnableSession:=False)> _
          Public Function Verify() As String
                Return "OK"
          End Function

    함수는 OK 값을 반환해야 합니다.


SQL Server 2005 데이터베이스에 연결

다음은 Excel 워크시트의 ListObject 개체를 채우는 데 사용되는 데이터 집합을 생성하는 함수를 만들어야 합니다.

  1. 먼저 필요한 SQL 관련 네임스페이스를 가져옵니다. 이를 위해 웹 서비스 코드에 다음 줄을 삽입합니다.
    Imports System.Web
    Imports System.Data Imports System.Data.SqlClient
    Imports System.Web.Services
    Imports System.Web.Services.Protocols

  2. 기본 SQL 관련 네임스페이스를 가져온 다음에는 데이터 집합을 생성하는 함수를 만들어야 합니다. RequestData라는 이 함수는 SQL 데이터베이스에 연결하고 Sales.SalesOrderDetail 테이블에서 데이터를 추출하는 쿼리를 시작합니다. 이 쿼리는 판매 금액을 수집하고 이를 해당 연도 값으로 합산합니다. 예를 들어 2004년에 해당하는 모든 판매 금액을 추출하여 합산합니다.

  3. 다음 함수를 코드에 추가합니다.
    <WebMethod(Description:="Returns dataset with information about orders", _
          EnableSession:=False, BufferResponse:=True, CacheDuration:=600000)> _
          Public Function RequestData() As DataSet

                Dim OrdersData As New DataSet   '반환할 데이터 집합입니다.
                Dim ConnectionToSql As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionSql"))
                ConnectionToSql.Open()
                Dim daLinks As New SqlDataAdapter("SELECT TOP (100) PERCENT SUM(Sales.SalesOrderDetail.LineTotal) AS Total, DATEPART(yy, Sales.SalesOrderHeader.OrderDate) AS Year FROM Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID GROUP BY DATEPART(yy, Sales.SalesOrderHeader.OrderDate)ORDER BY Year", ConnectionToSql)
                daLinks.Fill(OrdersData, "View1")

                Return OrdersData
          End Function

  4. 웹 서비스를 완성하였으면 저장하고 Microsoft 인터넷 정보 서비스(IIS) 6.0 및 ASP.NET 2.0이 실행되고 있는 웹 서버에 게시합니다.
    참고참고:
    web.config 파일에서 데이터베이스에 대한 연결 문자열을 설정해야 합니다.

Excel 워크시트 만들기

다음은 웹 서비스를 사용하여 추출한 데이터를 바탕으로 그래프를 생성하는 Excel 워크시트를 만듭니다.

  1. 먼저 Visual Studio 2005에서 AdventureWorks 프로젝트에 DiagramOrder라는 이름으로 Excel 프로젝트를 추가합니다. DiagramOrder.xls라는 이름의 빈 Excel 문서를 만들지 여부를 묻는 메시지가 표시됩니다.
  2. 확인을 클릭하여 문서를 만듭니다. Visual Studio 2005 내에서 Excel IDE가 열립니다.
  3. 앞에서 만든 웹 서비스에 대한 참조를 추가합니다. 이를 위해 Excel 프로젝트를 마우스 오른쪽 단추로 클릭하고 웹 참조 추가 옵션을 선택합니다. 웹 서비스를 찾기 위한 메시지가 표시됩니다.
  4. 이 솔루션 옵션에서 웹 서비스를 선택하고 이름을 AdvWebservice로 지정합니다.
  5. Visual Studio 도구 상자에서 ListObject 컨트롤을 DiagramOrder.xls에 있는 sheet1의 A-5 상자로 끌어서 놓고 이름을 List1로 지정합니다.
  6. 다음과 같이 프로젝트에 웹 서비스의 네임스페이스에 대한 참조를 추가합니다.
    Imports DiagramOrder.AdvWebservice

  7. 다음 코드를 워크시트의 시작 섹션에 삽입합니다.
    '웹 서비스가 온라인 상태인지 확인합니다.
          If AdvWebService.Verify.ToString = "OK" Then
             '웹 서비스가 온라인 상태이면 새 데이터 집합을 만들고 List1을 채웁니다.
             Dim ds As New DataSet
             ds = AdvWebService.RequestData

             List1.AutoSetDataBoundColumnHeaders = True
             '셀 자동 설정
             List1.DataSource = ds
             List1.DataMember = "View1"
          Else
             '웹 서비스가 온라인 상태가 아닌 경우 Excel에서 사용자에게 메시지를 표시합니다.
             MessageBox.Show("Attenction: The Web service is unreacheable", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly, False)
          End If

  8. 이 코드는 웹 서비스가 온라인 상태인지 여부를 확인합니다. 온라인 상태인 경우 코드는 데이터 집합을 생성하고 앞서 만든 List1 컨트롤을 채웁니다.
  9. Excel 워크시트에서 ListObject 컨트롤을 선택합니다. 컨트롤에서 그래프를 볼 수 있는 메뉴가 열립니다.

    그림 2. 그래프 마법사

  10. 차트 단추를 클릭하여 차트 마법사를 시작합니다.
  11. 3차원 세로 막대형을 클릭하고 다음을 클릭합니다. 데이터 원본을 확인합니다.
  12. 계열 탭을 클릭합니다.
  13. 이름 필드에 올바른 열 머리글이 선택되어 있는지 확인합니다.
  14. 필드에 올바른 데이터가 선택되어 있는지 확인합니다.
  15. 원하는 경우 차트 제목과 축 제목을 입력한 다음 마침을 클릭합니다.

    그림 3. 차트 작성

    사용자 삽입 이미지

  16. DiagramOrder 프로젝트를 마우스 오른쪽 단추로 클릭하고 디버그를 선택합니다.
  17. 새 인스턴스 시작을 클릭합니다. 응용 프로그램이 시작됩니다.

    그림 4. 샘플 Excel 워크시트

    사용자 삽입 이미지



판매 데이터를 추출하는 웹 서비스 만들기

Word 2003에서 웹 서비스를 통해 Adventure Works 데이터베이스를 쿼리하여 판매 사원들에게 연간 판매 실적에 대한 감사 편지를 작성합니다.

이 웹 서비스에는 다음 4개의 공용 함수가 포함됩니다.

  • Verify. Word에서 웹 서비스가 올바르게 작동하는지 확인하는 데 사용됩니다.
  • TerritoryReturn. 데이터베이스에서 모든 필드를 추출합니다.
  • ListEmployeeSales. 지역에 따라 모든 직원 판매를 추출합니다.
  • EmployeeInformation. ID에 따라 모든 직원 정보를 추출합니다.

다음 절차에서는 판매 데이터를 추출하는 웹 서비스를 만드는 방법을 보여 줍니다.

  1. 먼저 AdventureWeb 웹 프로젝트에 EmployeeSales.asmx라는 웹 서비스를 만듭니다.
  2. 그런 다음 데이터베이스에서 모든 지역을 추출하고 지역의 모든 데이터를 포함하는 DataSet 개체를 반환하는 TerritoryReturn 함수를 만듭니다.

    '데이터베이스에서 모든 지역을 반환합니다.
          <WebMethod(Description:="Return all Territory", _
             EnableSession:=False)> _
             Public Function TerritoryReturn() As DataSet
             Dim TerritoryTable As New DataSet
             Dim ConnectionToSQL As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionSql"))
             Dim daLinks As New SqlDataAdapter("SELECT Sales.SalesTerritory.*" _
             & "FROM Sales.SalesTerritory", ConnectionToSQL)

             ConnectionToSQL.Open()
             daLinks.Fill(TerritoryTable, "Territory")

             Return TerritoryTable

             ConnectionToSQL.Close()

          End Function

  3. 지역에 따라 모든 판매 직원 데이터를 추출하고 DataSet 개체를 반환하는 ListEmployeeSales 함수를 만듭니다.

    '지역에 따라 판매 직원 데이터를 포함하는 DataSet 개체를 반환합니다.
          <WebMethod(Description:="Returns dataset with information about Sales Employee", _
          EnableSession:=False, BufferResponse:=True, CacheDuration:=600000)> _
          Public Function ListEmployeeSales(ByVal Territory As String) As DataSet

             Dim SalesData As New DataSet
             '반환할 데이터 집합입니다.
             Dim ConnectionToSql As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionSql"))
             ConnectionToSql.Open()
             Dim daLinks As New SqlDataAdapter("SELECT Person.Contact.FirstName, Person.Contact.LastName, Sales.SalesTerritory.TerritoryID, HumanResources.Employee.EmployeeID " _
       & "FROM Person.Contact INNER JOIN HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID " _
       & "INNER JOIN Sales.SalesPerson AS SalesPerson_1 ON HumanResources.Employee.EmployeeID = SalesPerson_1.SalesPersonID " _
       & "INNER JOIN Sales.SalesTerritory ON SalesPerson_1.TerritoryID = Sales.SalesTerritory.TerritoryID " _
       & "WHERE (SalesPerson_1.TerritoryID = " & Territory & ")", ConnectionToSql)
             daLinks.Fill(SalesData, "SalesEmployeeTable")

             Return SalesData

             ConnectionToSql.Close()
          End Function

  4. 마지막으로 만들 함수는 직원의 ID에 따라 해당 직원과 관련된 모든 정보(예: 이름 및 주소)를 추출합니다. 이 함수는 추출한 모든 정보를 포함하는 DataSet 개체를 반환합니다.

    '각 판매 직원에 대한 모든 정보를 반환합니다.
          <WebMethod(Description:="Return all information about sales employee", _
          EnableSession:=False)> _
          Public Function EmployeeInformation(ByVal EmployeeID As String) As DataSet
             Dim EmployeeInfo As New DataSet
             Dim ConnectionToSQL As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionSql"))
             Dim daLinks As New SqlDataAdapter("SELECT Person.Contact.FirstName, Person.Contact.LastName, Sales.SalesTerritory.TerritoryID, HumanResources.Employee.EmployeeID, " _
             & "Person.Address.AddressLine1, Person.Address.City, Person.Address.PostalCode, SalesPerson_1.SalesLastYear, SalesPerson_1.SalesQuota " _
             & "FROM Person.Contact INNER JOIN " _
                & "HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID INNER JOIN " _
             & "Sales.SalesPerson AS SalesPerson_1 ON HumanResources.Employee.EmployeeID = SalesPerson_1.SalesPersonID INNER JOIN " _
             & "Sales.SalesTerritory ON SalesPerson_1.TerritoryID = Sales.SalesTerritory.TerritoryID INNER JOIN " _
                & "HumanResources.EmployeeAddress ON HumanResources.Employee.EmployeeID = HumanResources.EmployeeAddress.EmployeeID INNER JOIN " _
             & "Person.Address ON HumanResources.EmployeeAddress.AddressID = Person.Address.AddressID AND " _
                & "HumanResources.EmployeeAddress.AddressID = Person.Address.AddressID AND " _
             & "HumanResources.EmployeeAddress.AddressID = Person.Address.AddressID AND " _
             & "HumanResources.EmployeeAddress.AddressID = Person.Address.AddressID " _
             & "WHERE (HumanResources.Employee.EmployeeID = " & EmployeeID & ")", ConnectionToSQL)

             ConnectionToSQL.Open()
             daLinks.Fill(EmployeeInfo, "EmployeeInformation")

             Return EmployeeInfo

             ConnectionToSQL.Close()

          End Function

  5. 마지막으로, ASP.NET 2.0 및 IIS 6.0이나 IIS 5.0을 실행하는 서버에 AdventureWeb 웹 프로젝트를 다시 게시합니다.
    참고참고:
    Visual Studio 2005에서 디버그 요청을 시작하여 웹 서비스가 작동하는지 확인할 수 있습니다. localhost 모드를 사용하여 시작하면 데이터를 삽입하여 웹 서비스 및 응용 프로그램이 의도한 대로 작동하는지 확인할 수 있습니다.
그림 5. Employee Sales 웹 서비스 사용자 인터페이스
사용자 삽입 이미지


그림 6. Employee Sales 웹 서비스
사용자 삽입 이미지



Word 2003 문서 서식 파일 만들기

이 Word 문서 서식 파일은 웹 서비스에 연결하고 사용자 지정 작업창에서 드롭다운 목록 컨트롤을 사용하여 모든 지역을 표시합니다. 사용자는 지역을 선택하고 Search를 클릭하여 해당 지역의 모든 판매 직원을 추출할 수 있습니다.

작업창에 표시되는 다른 목록 컨트롤에는 직원의 성이 들어 있습니다. 사용자는 직원 이름을 클릭하고 Select Employee를 클릭하여 직원 데이터를 추출하고 이 데이터로 워드 문서를 채울 수 있습니다.

웹 서비스를 만든 다음에는 Word 2003에서 Microsoft Office System용 Microsoft Visual Studio 2005 도구(Office용 Visual Studio 도구)를 사용하여 (동적) 문서 서식 파일을 만들어야 합니다.

  1. 먼저 AdventureWorks 솔루션에 Visual Basic .NET으로 작성된 새 Word 2003 프로젝트를 추가합니다. 프로젝트의 이름을 CongratulationLetter로 지정합니다.
  2. Excel 프로젝트와 마찬가지로 프로젝트에서 기존 문서를 사용할 것인지 새 문서를 사용할 것인지 묻는 메시지가 표시됩니다.
  3. 기존 문서 복사를 선택하고 찾아보기를 클릭합니다.
  4. CongratulationLetter.doc을 선택하고 확인을 클릭합니다.

    그림 7. Visual Studio 내의 축하 편지

    사용자 삽입 이미지


Excel 프로젝트와 비슷한 방식으로, Visual Studio 2005 내에 Word 2003 IDE가 표시됩니다.

참고참고:
워드 문서 내에서 기존 양식 필드(회색 대괄호)를 볼 수 있습니다. 이러한 양식 필드는 Office용 Visual Studio 2005 도구의 개체이며 관련 속성을 가지고 있습니다. 필드의 속성을 보려면 필드 내부로 커서를 이동합니다.


작업 컨트롤 작업창 만들기

다음으로, Word 2003의 사용자 지정 작업창에 로드할 컨트롤을 만들어야 합니다.

  1. Word 2003 IDE에서 솔루션 탐색기를 마우스 오른쪽 단추로 클릭하고 TaskPane이라는 새 폴더를 추가합니다.
  2. TaskPane 폴더에서 새 항목을 추가합니다. 작업 창 컨트롤 유형을 선택하고 이름을 TaskMenu.vb로 지정합니다.
  3. 이 개체에 다음 컨트롤을 추가합니다.
    1. DropDownList 2개
    2. Label 3개
    3. Button 2개
  4. 그림 8은 이러한 컨트롤을 추가한 뒤의 작업창을 보여 줍니다.

    그림 8. Word 2003의 샘플 작업창

    사용자 삽입 이미지


  5. TaskMenu.vb 컨트롤에 앞서 다음 코드를 첫 번째 DropDownList에 추가합니다.

    Private Sub TaskMenu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
          Dim Ds As New DataSet

          Ds = AdvWebService.TerritoryReturn

          ListTerritory.DataSource = Ds
          ListTerritory.DisplayMember = "Territory.Name"
          ListTerritory.ValueMember = "Territory.TerritoryID"

    End Sub

  6. 그 다음 두 개의 단추에 대한 함수를 만듭니다.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
          ListEmployee.Visible = True
          Label3.Visible = True

          Dim Ds2 As New DataSet

          Ds2 = AdvWebService.ListEmployeeSales(ListTerritory.SelectedValue.ToString)
          ListEmployee.DataSource = Ds2
          ListEmployee.DisplayMember = "SalesEmployeeTable.LastName"
          ListEmployee.ValueMember = "SalesEmployeeTable.EmployeeID"

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
          Dim Ds3 As New DataSet
          Ds3.Clear()
          Ds3 = AdvWebService.EmployeeInformation(ListEmployee.SelectedValue.ToString)

          Dim FName As New Binding("Text", Ds3, "EmployeeInformation.FirstName")
          Dim LName As New Binding("Text", Ds3, "EmployeeInformation.LastName")
          Dim AddressL As New Binding("Text", Ds3, "EmployeeInformation.AddressLine1")
          Dim PCode As New Binding("Text", Ds3, "EmployeeInformation.PostalCode")
          Dim City As New Binding("Text", Ds3, "EmployeeInformation.City")
          Dim DearName As New Binding("Text", Ds3, "EmployeeInformation.FirstName")
          Dim TotalSales As New Binding("Text", Ds3, "EmployeeInformation.SalesLastYear")
          Dim TotalRevenue As New Binding("Text", Ds3, "EmployeeInformation.SalesQuota")


          CongratulationLetter.Globals.ThisDocument.FirstName.DataBindings.Clear()
          CongratulationLetter.Globals.ThisDocument.FirstName.DataBindings.Add(FName)

          CongratulationLetter.Globals.ThisDocument.LastName.DataBindings.Clear()
          CongratulationLetter.Globals.ThisDocument.LastName.DataBindings.Add(LName)

          CongratulationLetter.Globals.ThisDocument.address.DataBindings.Clear()
          CongratulationLetter.Globals.ThisDocument.address.DataBindings.Add(AddressL)

          CongratulationLetter.Globals.ThisDocument.zip.DataBindings.Clear()
          CongratulationLetter.Globals.ThisDocument.zip.DataBindings.Add(PCode)

          CongratulationLetter.Globals.ThisDocument.city.DataBindings.Clear()
          CongratulationLetter.Globals.ThisDocument.city.DataBindings.Add(City)

          CongratulationLetter.Globals.ThisDocument.Dear.DataBindings.Clear()
          CongratulationLetter.Globals.ThisDocument.Dear.DataBindings.Add(DearName)

          CongratulationLetter.Globals.ThisDocument.TotalSales.DataBindings.Clear()
          CongratulationLetter.Globals.ThisDocument.TotalSales.DataBindings.Add(TotalSales)

          CongratulationLetter.Globals.ThisDocument.revenue.DataBindings.Clear()
          CongratulationLetter.Globals.ThisDocument.revenue.DataBindings.Add(TotalRevenue)

    End Sub

  7. "ThisDocument_Startup" 서브루틴 내에 다음 코드를 추가합니다.

    '작업창에 사용자 컨트롤 TaskMenu.vb를 추가합니다.
    Me.ActionsPane.Controls.Add(ActionMenu)
    Me.ActionsPane.Visible = True

Word 문서 서식 파일에 사용자 지정 작업창과 TaskMenu 컨트롤이 추가되었습니다.

CongratulationLetter.doc 응용 프로그램의 디버그 버전을 시작한 다음, 지역 및 직원 데이터를 변경하면서 Word 문서의 데이터가 어떻게 달라지는지 확인해 보십시오.


결론

이 기사에서는 ASP.NET 2.0 웹 사이트의 웹 서비스를 사용하여 Office 2003 응용 프로그램을 통합하는 방법을 살펴보았습니다. 구매서, 운송 서류 등의 다른 시나리오에 대해 만들 수 있는 응용 프로그램도 생각해 보십시오.

추가 리소스

자세한 내용은 다음 리소스를 참조하십시오.

출처 : 한국 마이크로소프트 MSDN (2006년 11월)