ASP에서 ADO를 사용하여 Excel 데이터를 쿼리하고 업데이트하는 방법

요약

이 문서에서는 ASP(Active Server Pages) 페이지에서 ADO(ActiveX Data Objects)를 사용하여 Excel 스프레드시트에서 정보를 쿼리하고 업데이트하는 방법을 보여 줍니다. 이 문서에서는 이러한 유형의 애플리케이션과 관련된 제한 사항도 설명합니다.

추가 정보

이 샘플에 대한 Excel 스프레드시트의 데이터에 액세스하려면 Microsoft ODBC Driver for Excel을 사용합니다. Excel 스프레드시트에서 명명된 범위를 만들어 데이터에 액세스할 테이블을 만듭니다.

샘플 애플리케이션을 만드는 단계

• sheet1에서 다음 데이터를 사용하여 Excel 파일 ADOtest.xls 만듭니다.

  column1	column2	column3
  Rr	이	15
  Bb	test	20
  Ee	작동	25

  참고

  Excel 스프레드시트의 열에 텍스트와 숫자가 모두 포함된 경우 Excel ODBC 드라이버는 열이 있어야 하는 데이터 형식을 올바르게 해석할 수 없습니다. 열의 모든 셀이 동일한 데이터 형식인지 확인하세요. 열의 각 셀이 같은 형식이 아니거나 "text"와 "general" 사이에 형식이 혼합된 경우 다음 세 가지 오류가 발생할 수 있습니다.

  1. Microsoft OLE DB Provider for ODBC Drivers 오류 '80040e21' 요청 속성은 이 ODBC 드라이버에서 지원될 수 없습니다.
  2. Microsoft OLE DB Provider for ODBC Drivers 오류 '80004005' 쿼리는 희망 키로 사용할 검색 가능한 열이 없기 때문에 업데이트할 수 없습니다.
  3. ODBC 드라이버용 Microsoft OLE DB 공급자 오류 '80004005' 쿼리 기반 업데이트가 실패했습니다. 업데이트할 행을 찾을 수 없습니다.

• 스프레드시트에 명명된 범위 myRange1을 만듭니다.

  1. 데이터가 있는 행 및 열 영역을 강조 표시합니다.
  2. 삽입 메뉴에서 이름을 가리킨 다음 정의를 클릭합니다.
  3. 명명된 범위 이름에 myRange1 이름을 입력합니다.
  4. 확인을 클릭합니다.

  명명된 범위 myRange1에는 다음 데이터가 포함됩니다.

  column1	column2	column3
  Rr	이	15
  Bb	test	20
  Ee	작동	25

  참고

  • ADO는 Excel 쿼리의 첫 번째 행에 열 머리글이 포함되어 있다고 가정합니다. 따라서 명명된 범위에는 열 머리글이 포함되어야 합니다. 이는 DAO와 다른 동작입니다.
  • 열 머리글은 숫자가 될 수 없습니다. Excel 드라이버는 이를 해석할 수 없으며 대신 셀 참조를 반환합니다. 예를 들어 "F1"의 열 머리글이 잘못 해석됩니다.

• ADOTest.xls 파일을 가리키는 ODBC DSN(시스템 데이터 원본 이름)을 만듭니다.

  1. 제어판 ODBC 관리자를 엽니다.
  2. 시스템 DSN 탭에서 추가를 클릭합니다.
  3. Microsoft Excel 드라이버(*.xls)를 선택하고 마침을 클릭합니다. 이 옵션이 없으면 Excel 설정에서 Excel용 Microsoft ODBC 드라이버를 설치해야 합니다.
  4. 데이터 원본 이름으로 ADOExcel을 선택합니다.
  5. 버전이 올바른 버전의 Excel로 설정되어 있는지 확인합니다.
  6. "통합 문서 선택..."을 클릭하고 ADOTest.xls 파일을 찾아 확인을 클릭합니다.
  7. "옵션>>" 단추를 클릭하고 "읽기 전용" 검사 상자를 선택 취소합니다.
  8. 확인을 클릭한 다음 확인을 다시 클릭합니다.

• ADOTest.xls 파일에 대한 사용 권한을 설정합니다.

활성 서버 페이지에 익명으로 액세스하는 경우 익명 계정(IUSR_<MachineName>)에 스프레드시트에 대한 RW(읽기/쓰기) 액세스 권한이 있는지 확인해야 합니다. 스프레드시트에서 정보를 삭제하려면 그에 따라 권한을 부여해야 합니다.

활성 서버 페이지에 대한 액세스를 인증하는 경우 애플리케이션에 액세스하는 모든 사용자에게 적절한 권한이 있는지 확인해야 합니다.

스프레드시트에 대한 적절한 권한을 설정하지 않으면 다음과 유사한 오류 메시지가 표시됩니다.

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.

1. 새 ASP 페이지를 만들고 다음 코드를 붙여넣습니다.

      <!-- Begin ASP Source Code -->
      <%@ LANGUAGE="VBSCRIPT" %>
      <%
        Set objConn = Server.CreateObject("ADODB.Connection")
        objConn.Open "ADOExcel"
   
   Set objRS = Server.CreateObject("ADODB.Recordset")
        objRS.ActiveConnection = objConn
        objRS.CursorType = 3                    'Static cursor.
        objRS.LockType = 2                      'Pessimistic Lock.
        objRS.Source = "Select * from myRange1"
        objRS.Open
   %>
   <br>
   <%
      Response.Write("Original Data")
   
   'Printing out original spreadsheet headings and values.
   
   'Note that the first recordset does not have a "value" property
      'just a "name" property.  This will spit out the column headings.
   
   Response.Write("<TABLE><TR>")
      For X = 0 To objRS.Fields.Count - 1
         Response.Write("<TD>" & objRS.Fields.Item(X).Name & "</TD>")
      Next
      Response.Write("</TR>")
      objRS.MoveFirst
   
   While Not objRS.EOF
         Response.Write("<TR>")
         For X = 0 To objRS.Fields.Count - 1
            Response.write("<TD>" & objRS.Fields.Item(X).Value)
         Next
         objRS.MoveNext
         Response.Write("</TR>")
      Wend
      Response.Write("</TABLE>")
   
   'The update is made here
   
   objRS.MoveFirst
      objRS.Fields(0).Value = "change"
      objRS.Fields(1).Value = "look"
      objRS.Fields(2).Value = "30"
      objRS.Update
   
   'Printing out spreadsheet headings and values after update.
   
   Response.Write("<br>Data after the update")
      Response.Write("<TABLE><TR>")
      For X = 0 To objRS.Fields.Count - 1
         Response.Write("<TD>" & objRS.Fields.Item(X).Name & "</TD>")
      Next
      Response.Write("</TR>")
      objRS.MoveFirst
   
   While Not objRS.EOF
         Response.Write("<TR>")
         For X = 0 To objRS.Fields.Count - 1
            Response.write("<TD>" & objRS.Fields.Item(X).Value)
         Next
         objRS.MoveNext
         Response.Write("</TR>")
      Wend
      Response.Write("</TABLE>")
   
   'ADO Object clean up.
   
   objRS.Close
      Set objRS = Nothing
   
   objConn.Close
      Set objConn = Nothing
   %>
   <!-- End ASP Source Code -->
   

2. 활성 서버 페이지를 저장하고 이름을 지정하고 브라우저에서 확인합니다. 다음과 같이 표시됩니다.

   Original Data:
   
   |column1|column2|column3|
   |------------|------------|------------|
   |rr|this|30|
   |bb|test|20|
   |tt|works|25|
   
   Data after the update:
   
   |column1|column2|column3|
   |------------|------------|------------|
   |change|look|30|
   |bb|test|20|
   |tt|works|25|