Äõ¸® ¹× ¾÷µ¥ÀÌÆ® Excel µ¥ÀÌÅÍ ASP ¿øº» ADO »ç¿ë ¹æ¹ý

ÀÌ ¹®¼­¿¡¼­´Â Äõ¸® ¹× ADO (ActiveX µ¥ÀÌÅÍ °³Ã¼) ¿¡¼­ ASP (Active Server Pages) ÆäÀÌÁö¸¦ »ç¿ëÇÏ´Â Excel ½ºÇÁ·¹µå½ÃÆ®ÀÇ Á¤º¸¸¦ ¾÷µ¥ÀÌÆ®ÇÏ´Â ¹æ¹ýÀ» º¸¿© ÁÝ´Ï´Ù. ÀÌ ¹®¼­¿¡¼­´Â ¶ÇÇÑ ÀÌ·¯ÇÑ À¯ÇüÀÇ ÀÀ¿ë ÇÁ·Î±×·¥¿¡ °ü·ÃµÈ Á¦ÇÑ »çÇ×À» ¼³¸íÇÕ´Ï´Ù.

Áß¿ä: ´ÙÁß »ç¿ëÀÚ ¾×¼¼½º ASP/ADO ÀÀ¿ë ÇÁ·Î±×·¥À» Áö¿øÇÏÁö ¾ÊÁö¸¸ Excel ½ºÇÁ·¹µå½ÃÆ®¸¦ ¾Ê½À´Ï´Ù. µû¶ó¼­ Äõ¸® ¹× Á¤º¸¸¦ ¾÷µ¥ÀÌÆ®ÇÏ´Â ÀÌ ¹æ¹ýÀº ´ÙÁß »ç¿ëÀÚ µ¿½Ã ¾×¼¼½º°¡ Áö¿øÇÏÁö ¾Ê½À´Ï´Ù.

ÀÌ »ùÇÃÀÇ Excel ½ºÇÁ·¹µå½ÃÆ®ÀÇ µ¥ÀÌÅ͸¦ ¾×¼¼½ºÇÏ·Á¸é Excel Microsoft ODBC µå¶óÀ̹ö¸¦ »ç¿ëÇϽʽÿÀ. Excel ½ºÇÁ·¹µå½ÃÆ®¿¡¼­ ¸í¸íµÈ ¹üÀ§¸¦ ¸¸µé¾î µ¥ÀÌÅÍ¿¡ ¾×¼¼½ºÇÒ ¼ö ÀÖ´Â Å×À̺íÀ» ¸¸µì´Ï´Ù.

»ùÇà ÀÀ¿ë ÇÁ·Î±×·¥ ¸¸µå´Â ´Ü°è

• Excel ÆÄÀÏÀ» ADOtest.xls sheet1 ´ÙÀ½ µ¥ÀÌÅÍ¿¡ ¸¸µé±â:
  
  
  Ç¥ Ãà¼ÒÇ¥ È®´ë

  column1	column2	column3
  rr	ÀÌ	15
  bb	Test	20
  ee	ÀÛµ¿	25

  
  Âü°í Excel ½ºÇÁ·¹µå½ÃÆ®ÀÇ ¿­À» ÅؽºÆ®¿Í ¼ýÀÚ¸¦ ¸ðµÎ Æ÷ÇԵǾî ÀÖÀ¸¸é Excel ODBC µå¶óÀ̹ö ¿­À̾î¾ß ÇÕ´Ï´Ù µ¥ÀÌÅÍ Çü½ÄÀ» ¿Ã¹Ù¸£°Ô Çؼ®ÇÒ ¼ö ¾ø½À´Ï´Ù. ¿­ÀÇ ¸ðµç ¼¿¿¡ °°Àº µ¥ÀÌÅÍ Çü½ÄÀÇ ÀÖ´ÂÁö È®ÀÎÇϽʽÿÀ. ¿­ÀÇ °¢ ¼¿ Çü½ÄÀÌ ¾ø°Å³ª Çü½Ä È¥ÇÕ »çÀÌ¿¡ "ÅؽºÆ®" ¹× "ÀϹÝ" ±ÇÇÑÀÌ ´ÙÀ½°ú °°Àº ¼¼ °¡Áö ¿À·ù°¡ ¹ß»ýÇÒ ¼ö ÀÖ½À´Ï´Ù.
  1. Microsoft OLE DB °ø±ÞÀÚ¿¡ ´ëÇÑ ODBC µå¶óÀ̹ö ¿À·ù '80040e21'
     ¿äû ¼Ó¼ºÀº ÀÌ ODBC µå¶óÀ̹ö¿¡¼­ Áö¿øµÇÁö ¾ÊÀ» ¼ö ÀÖ½À´Ï´Ù.
  2. Microsoft OLE DB °ø±ÞÀÚ¿Í ODBC µå¶óÀ̹ö ¿À·ù´Â '80004005'
     Áñ°Ì°í Å°·Î »ç¿ëÇÒ °Ë»ö °¡´ÉÇÑ ¿­ÀÌ Æ÷ÇԵǾî Àֱ⠶§¹®¿¡ Äõ¸®¸¦ ¾÷µ¥ÀÌÆ®ÇÒ ¼ö ¾ø½À´Ï´Ù.
  3. Microsoft OLE DB °ø±ÞÀÚ¿Í ODBC µå¶óÀ̹ö ¿À·ù´Â '80004005'
     Äõ¸® ±â¹Ý ¾÷µ¥ÀÌÆ®ÇÏÁö°¡ ¸øÇß½À´Ï´Ù. ¾÷µ¥ÀÌÆ®ÇÒ ÇàÀ» ãÀ» ¼ö ¾ø½À´Ï´Ù.

• ¸í¸íµÈ ¹üÀ§, myRange1À» »ç¿ëÇÏ¿© ½ºÇÁ·¹µå½ÃÆ®¸¦ ¸¸µì´Ï´Ù.
  
  
  1. µ¥ÀÌÅÍ°¡ »óÁÖÇÏ´Â Çà ¹× ¿­À» ¿µ¿ªÀ» °­Á¶ Ç¥½ÃÇϽʽÿÀ.
  2. »ðÀÔ ¸Þ´º¿¡¼­ À̸§À» °¡¸®Å² ´ÙÀ½ Á¤ÀǸ¦ Ŭ¸¯ÇϽʽÿÀ.
  3. ÇØ´ç À̸§ ¹üÀ§ À̸§À» myRange1 ÀÔ·ÂÇϽʽÿÀ À̸§.
  4. È®ÀÎÀ» Ŭ¸¯ÇϽʽÿÀ.
  ¸í¸íµÈ ¹üÀ§ myRange1 ´ÙÀ½ µ¥ÀÌÅÍ°¡ Æ÷ÇԵǾî ÀÖ½À´Ï´Ù.
  
  
  Ç¥ Ãà¼ÒÇ¥ È®´ë

  column1	column2	column3
  rr	ÀÌ	15
  bb	Test	20
  ee	ÀÛµ¿	25

  
  
  Âü°í ADO Excel Äõ¸®ÀÇ Ã¹ ¹ø° Çà¿¡ ¿­ ¸Ó¸®±ÛÀÌ µé¾î ÀÖ´Ù°í °¡Á¤ÇÕ´Ï´Ù. µû¶ó¼­, ¸í¸íµÈ ¹üÀ§ ¿­ ¸Ó¸®±ÛÀÌ Æ÷ÇԵǾî¾ß ÇÕ´Ï´Ù. DAO¿¡¼­ µ¿ÀÛÀÌ ÀÖ½À´Ï´Ù.
  
  Âü°í ¿­ ¸Ó¸®±ÛÀ» ¼ýÀÚ¸¦ »ç¿ëÇÒ ¼ö ¾ø½À´Ï´Ù. Excel µå¶óÀ̹ö´Â Çؼ®ÇÒ ¼ö ¹× ´ë½Å ¼¿ ÂüÁ¶¸¦ ¹ÝȯÇÕ´Ï´Ù. ¿¹¸¦ µé¾î, "F1" ¿­ ¸Ó¸®±Û Çؼ®µÉ °ÍÀÔ´Ï´Ù.
• ODBC ½Ã½ºÅÛ µ¥ÀÌÅÍ ¿øº» À̸§ (ADOTest.xls ÆÄÀÏÀ» °¡¸®Å°´Â DSN)À» ¸¸µì´Ï´Ù.
  1. Á¦¾îÆÇ¿¡¼­ ODBC ¿­ °ü¸®ÀÚ.
  2. ½Ã½ºÅÛ DSN ÅÇ¿¡¼­ Ãß°¡ Ŭ¸¯ÇϽʽÿÀ.
  3. Microsoft Excel µå¶óÀ̹ö (*.xls) ¼±ÅÃÇÏ°í ¸¶Ä§ À» Ŭ¸¯ÇϽʽÿÀ. ÀÌ ¿É¼ÇÀº Á¸ÀçÇÏÁö ¾ÊÀ» °æ¿ì Excel ¼³Á¤¿¡¼­ Excel Microsoft ODBC µå¶óÀ̹ö¸¦ ¼³Ä¡ÇØ¾ß ÇÕ´Ï´Ù.
  4. ADOExcel¿¡ ´ëÇÑ µ¥ÀÌÅÍ ¿øº» À̸§À» ¼±ÅÃÇϽʽÿÀ.
  5. ¿Ã¹Ù¸¥ ¹öÀüÀÇ Excel ¹öÀü ¼³Á¤µÇ¾î ÀÖ´ÂÁö È®ÀÎÇϽʽÿÀ.
  6. ¼±Åà ÅëÇÕ... ¸¦ Ŭ¸¯ÇÏ°í ÇØ´ç ADOTest.xls ãÀº ÆÄÀÏ°ú È®ÀÎÀ» Ŭ¸¯ÇÕ´Ï´Ù.
  7. »ç¿ëÇÏ¿© "¿É¼Ç >>" ´ÜÃß ¹× "Àбâ Àü¿ë" È®ÀζõÀÇ ¼±ÅÃÀ» Ãë¼ÒÇÕ´Ï´Ù.
  8. È®ÀÎÀ» Ŭ¸¯ÇÑ ´ÙÀ½ ´Ù½Ã È®ÀÎÀ» Ŭ¸¯ÇϽʽÿÀ.
• ADOTest.xls ÆÄÀÏ¿¡ ´ëÇÑ »ç¿ë ±ÇÇÑÀ» ¼³Á¤ÇϽʽÿÀ.

Active Server Page À͸íÀ¸·Î ¾×¼¼½ºÇÒ °æ¿ì ÀÍ¸í °èÁ¤ (IUSR_ <machinename>) ÀÌ»óÀÌ È®ÀÎÇØ¾ß ÇÒ ½ºÇÁ·¹µå½ÃÆ® RW (Àбâ/¾²±â ¾×¼¼½º¸¦. ½ºÇÁ·¹µå½ÃÆ®¿¡¼­ Á¤º¸¸¦ »èÁ¦ÇÒ °æ¿ì ±×¿¡ µû¶ó »ç¿ë ±ÇÇÑÀ» ºÎ¿©ÇØ¾ß ÇÕ´Ï´Ù.

»ç¿ëÀÚÀÇ Active Server Page ¾×¼¼½º¸¦ ÀÎÁõÇÏ´Â °æ¿ì ÀÀ¿ë ÇÁ·Î±×·¥ÀÌ ¾×¼¼½ºÇÏ´Â ¸ðµç »ç¿ëÀÚ¿¡°Ô ÀûÀýÇÑ ±ÇÇÑÀÌ ÀÖ´ÂÁö È®ÀÎÇØ¾ß ÇÕ´Ï´Ù.

Âü°í ½ºÇÁ·¹µå½ÃÆ®ÀÇ ÀûÀýÇÑ »ç¿ë ±ÇÇÑÀ» ¼³Á¤ÇÑ °æ¿ì »ç¿ëÀÚ°¡ ¿À·ù ´ÙÀ½°ú À¯»çÇÑ ¸Þ½ÃÁö°¡:

Microsoft OLE DB °ø±ÞÀÚ¿Í ODBC µå¶óÀ̹ö ¿À·ù´Â '80004005'

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. ¹× Active Server Page À̸§À» ÀúÀåÇÏ°í ºê¶ó¿ìÀú¿¡¼­ º¾´Ï´Ù. ´ÙÀ½ Ç¥½ÃµË´Ï´Ù:

         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
   					

Âü°í ¾÷µ¥ÀÌÆ® À̸§ ¹üÀ§ ù ¹ø° Çà¿¡ ¸Ó¸®±ÛÀÌ ÈÄ ¼öÇàµÇ¾ú½À´Ï´Ù.