1. 아별툴
  2. 아별툴 패밀리
  3. 엑셀 질문하기
  4. 엑셀 강좌
  5. 엑셀 팁
  6. 엑셀 자료실
  7. 엑셀 연구과제
  8. 엑셀 북마크
  9. 관련 프로그램 소개

[ XML ] 엑셀로 XML 데이터 다루기..

엑셀일반 조회 수 12423 추천 수 0 2011.12.26 18:10:43

아별툴에 사용자 설정 기능을 외부 파일로 하는 것을 연구하다가..

그냥 텍스트파일로 하는 것보다 XML로 하는 것이 신뢰성이 높고 확장성이 있어

엑셀로 XML 다루는 것은 연구해보았습니다.


XML

: eXtensible Markup Language

SGML(Standardized Generalized Markup Language)을 단순하고 이용하기 쉽게 만든 메타 언어..


좀 더 공부하고 싶다면 아래 사이트를 참조하자..

http://cafe.naver.com/javacommunication/102


일단..  필자(아별)가 만든 샘플 파일을 참고해보자.

다운받기 : abyul.com_XML.zip


1. XML는 데이터가 있는 파일이라고 생각하자.

   → DTD를 내장할 수도 있지만.. 요즘 트렌드는 별도로 파일로 저장하는 거다..

       쉽게 생각하자. XML은 데이터.. XSD는 제약 조건.. DTD에 대한 설명은 빼자..


< XML파일 샘플 >

 

<?xml version="1.0" encoding="UTF-8"?>

<dataroot xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xsi:noNamespaceSchemaLocation="abyul.com_XML.xsd">

<dataSet>

<교수ID>t1</교수ID>

<성명>오주원</성명>

<학과>우리과</학과>

<email>abyul@neinyeon.com</email>

<연락처>1577-1577</연락처>

</dataSet>

<dataSet>

<교수ID>t2</교수ID>

<성명>엑셀의신</성명>

<학과>니네과</학과>

<email>whoami@neinyeon.com</email>

<연락처>1588-0082</연락처>

</dataSet>

</dataroot>




2. XSD(XML 스키마 정의 언어)는 엑셀의 유효성 검사 같은거다.

  → 데이터 형식, 출현 빈도, 순차 증가 등에 대한 제약 조건을 써 넣는다.

  → XSD파일 직접 코딩해서 만들라면.. 힘들다.. 엑서스나 엑셀 추가기능을 사용해라.. ( 저 아래 설명 참고)


< XSD 파일 샘플 >

 

<?xml version="1.0" encoding="UTF-8"?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">


<xsd:element name="dataroot">

  <xsd:complexType>

    <xsd:sequence>

<xsd:element ref="dataSet" minOccurs="0" maxOccurs="unbounded"/>

    </xsd:sequence>

  </xsd:complexType>

</xsd:element>


<xsd:element name="dataSet">

  <xsd:complexType>

    <xsd:sequence>

<xsd:element name="교수ID" minOccurs="0">

</xsd:element>

<xsd:element name="성명" minOccurs="0">

</xsd:element>

<xsd:element name="학과" minOccurs="0">

</xsd:element>

<xsd:element name="email" minOccurs="0">

</xsd:element>

<xsd:element name="연락처" minOccurs="0">

</xsd:element>

    </xsd:sequence>

  </xsd:complexType>

</xsd:element>


</xsd:schema>



엑셀에서 개발도구 탭을 활성화시키면..

XML그룹에 "원본" 이라는 명령 단추와.. "가져오기", "내보내기"  같은 아이들이 보인다.

XML 가져오기는 잘 되는데... 내보내기를 하려고 하면.. 에러가 나면서 안된다.

XML 스키마 파일이 필요하다는 거다.. XSD파일이..



이넘의 XSD파일 어떻게 만드냣!!


어떻게 하냐? 손을 코딩하냐? 미친 짓이다.(초보자 입장에서는.. 전문가는 할 수 있어야한다만..)


1. 엑세스에서 테이블을 추가한 다음.. 다른 이름으로 저정할때 XML으로 저장하면 

     XSD파일도 같이 만들어준다. 그걸 쓰면 된다.


     아래 사이트에 자세히 설명해주셨다. (회원 가입하고 방문 5번 하며 읽을 수 있는 권한이 생긴다.)

     http://cafe.naver.com/office2080/129



2. MS에서 만들어서 배포하는 XML관련 추가기능이 있다.

    아래 사이트에서 다운 받으면 된다. 좀 대충 만들어주지만.. 일단 동작은 한다.

    제약 조건이 많을 수록 XSD파일은 복잡해지지만.. 데이터 무결성은 향상된다. 머.. 그런 식이다.

     http://msdn.microsoft.com/ko-kr/library/aa203739.aspx

     직접받기 : OfficeExcel2003XMLToolsAddin.exe 

     (또는 그냥 추가기능 파일만 바다서 추가기능에 등록해도 된다. XmlTools.xla )



이렇게하면..

XML파일을 엑셀의 스프레드 시트에 import하거나 export시킬 수 있게 된다.



그런데.. 나는 xml파일 전체를 임포트해서 사용할게 아니다.. 라면..

필요한 엘리먼트만 불러서 사용하고 싶다면.. 어떻게 해야할까?



그럴때면.. DOM을 사용하면 된다.

( DOM은 Document Object Model의 약자인데.. 

  XML의 구성요소에 대한 접근이 가능하게 해주는 인터페이스 같은거라고 생각하자.

  좀 더 정보를 얻으려면 다음 사이트를 참조하자 : http://blog.naver.com/whwlfnsl/70018615171  )

XML DOM을 사용하려면.. VBE창의 메뉴에서 "도구" > "참조" 한 다음.. 

Microsoft XML, v3.0을 체크해서 참조시키면 쓸 수 있다.


아래 사이트를 참조해보자..

http://cafe.naver.com/helpexcel/48



대략.. 이런 느낌하시면 됩니다.

다운 받기 : abyul.com_XML.xlsm



특정 노드로 바로 접근하려면 아래처럼 하면 된다.

아래 3개의 코드들이 전부 첫번째 교수ID인 t1을 리턴한다.

? XmlDoc.SelectNodes("/dataroot/dataSet").item(0).SelectSingleNode("교수ID").Text

? xmldoc.selectNodes("//교수ID")(0).text
? XmlDoc.getElementsByTagName("교수ID").item(0).text

음..
교수ID를 순환문으로 하나씩 확인하고.. 일치하는 값일 경우, 상위 노드의 "성명"을 갖고 와야하나..
? iif(xmldoc.selectNodes("//교수ID")(0).text = "t1",xmldoc.selectNodes("//교수ID")(0).parentNode.SelectSingleNode("성명").Text,"아님")





Option Explicit


'### Refer to XML DOM

'### code by Joowon Oh  '### 2011.12.26 21:05

'### http://abyul.com/  '### abyul@naver.com

Sub xmlDomControl()

    Dim XmlDoc As DOMDocument: Dim blnXml As Boolean

    Dim strFileName As String: strFileName = "abyul.com_XML.xml"

    Dim strPath As String: strPath = ThisWorkbook.Path & Application.PathSeparator & strFileName

    Dim strTitle As String: strTitle = " XML 파일 불러오기 에라.. abyul.com"

    Dim strMsg As String: strMsg = strPath & "파일을 불러오다가 에러가 발생했습니다."

    

    Set XmlDoc = CreateObject("Microsoft.XMLDom")

    blnXml = XmlDoc.Load(strPath)

    Dim shtTarget As Worksheet: Set shtTarget = Sheets("XMLDOM")

    Dim rngTarget As Range: Set rngTarget = shtTarget.Range("B4")

    Dim i As Integer, j As Integer

    

    If blnXml = True Then

        With XmlDoc.ChildNodes(1)

            For i = 0 To .ChildNodes(0).ChildNodes.Length - 1

                rngTarget.Offset(0, i) = .ChildNodes(0).ChildNodes(i).nodeName

            Next i

            For i = 0 To .ChildNodes.Length - 1

                For j = 0 To .ChildNodes(i).ChildNodes.Length - 1

                    rngTarget.Offset(i + 1, j) = .ChildNodes(i).ChildNodes(j).Text

                Next j

            Next i

        End With

        Set XmlDoc = Nothing

    Else

        MsgBox strMsg & vbCrLf & Err.Number & " : " _

               & Err.Description, vbCritical, strTitle

    End If


End Sub

 






댓글 '2'

profile

[레벨:30]아별

2015.04.13 19:16:37
*.132.210.135

아래와 같이 접근하는 것도 참고해보자..

출처 : http://www.experts-exchange.com/Software/Office_Productivity/Q_24186814.html



XML file:

 

<?xml version="1.0"?>

<MyGroups>

     <GROUPS>

          <TITLE title= "Group 1"> 

               <ITEM firstname="Jane" surname="Wilson" maritalstatus="single"/>

               <ITEM firstname="Susan" surname="Jones" maritalstatus="divorced"/>

               <ITEM firstname="Amanda" surname="Roberts" maritalstatus="married"/>

          </TITLE>

          <TITLE title= "Group 2">

               <ITEM firstname="Juliet" surname="Stephens" maritalstatus="single"/>

               <ITEM firstname="Anne" surname="Lewis" maritalstatus="single"/>

               <ITEM firstname="Lorraine" surname="Pearson" maritalstatus="married"/>

               <ITEM firstname="Karen" surname="Fielding" maritalstatus="divorced"/>

          </TITLE>

     </GROUPS>

</MyGroups>

 

 

VBA Code:

 

Option Explicit

 

Sub ShowMyXML()

    Dim myXMLDocument As New DOMDocument30

    Dim myTitles As IXMLDOMNodeList

    Dim myTitle As IXMLDOMNode

    Dim myItems As IXMLDOMNodeList

    Dim myItem As IXMLDOMNode

    

    myXMLDocument.Load ("C:\Temp\DelMar.xml")

    

    Set myTitles = myXMLDocument.SelectNodes("//TITLE")

    For Each myTitle In myTitles

        Debug.Print myTitle.Attributes.getNamedItem("title").Text

        Set myItems = myTitle.SelectNodes("//ITEM")

        For Each myItem In myItems

            Debug.Print myItem.Attributes.getNamedItem("firstname").Text & " | " & _

                myItem.Attributes.getNamedItem("surname").Text & " | " & _

                myItem.Attributes.getNamedItem("maritalstatus").Text

            

        Next myItem

    Next myTitle

    

End Sub



친절하게도.. 변경 후에 꼭 저장하라는 코멘트도 잊지 않고 남겨줌..

Just how you deal with the data in your macro is up to you. But this should get you started. BTW, if you want to write data to your xml file, use something like:
myItem.Attributes.getNamedItem("firstname").Text = "Hello, sailor!"

At the end of your macro, put a:
myXMLDocument.save("C:\temp\SomeOtherName.xml")



profile

[레벨:30]아별

2015.04.13 22:16:15
*.132.210.135

아래의 코드도 참고해보자..


http://www.freevbcode.com/ShowCode.asp?ID=6378



      Console.WriteLine()

      Console.WriteLine("Write value to STRING KEY: 'new value'")

      Dim tempValue As String

      tempValue = config.GetString("RELEASE", "STRING", "")

      config.WriteString("RELEASE", "STRING", "new value")

      Console.WriteLine()



문서 첨부 제한 : 0Byte/ 2.00MB
파일 제한 크기 : 2.00MB (허용 확장자 : *.*)
List of Articles
번호 제목 글쓴이 날짜sort 조회 수
공지 엑셀일반 엑셀 짜투리 팁 모음.. imagefile [13] [레벨:30]아별 2005-07-22 52695
공지 엑셀일반 엑셀 잘 하는 요령은? _ 엑셀 추천사이트 포함 [2] [레벨:30]아별 2009-11-12 40730
공지 엑셀일반 엑셀 단축키(바로가는키, ShortCut) 총정리입니다. imagefile [247] [레벨:30]아별 2010-08-29 106144