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

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

엑셀일반 조회 수 12367 추천 수 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
번호 제목 글쓴이 날짜 조회 수
공지 엑셀일반 엑셀 짜투리 팁 모음.. imagefile [13] [레벨:30]아별 2005-07-22 50371
공지 엑셀일반 엑셀 잘 하는 요령은? _ 엑셀 추천사이트 포함 [2] [레벨:30]아별 2009-11-12 38057
공지 엑셀일반 엑셀 단축키(바로가는키, ShortCut) 총정리입니다. imagefile [247] [레벨:30]아별 2010-08-29 102758
75 엑셀에러 【 엑셀에러 】 죄송합니다. Excel에 오류가 생겨 제대로 작동할 수 없습니다. Excel을(를) 닫아야 합니다. 에러 처리 [레벨:30]아별 2023-03-28 547
74 엑셀기초 엑셀 수식에 메모를 달아보자 [레벨:30]아별 2021-11-08 2261
73 엑셀일반 Excel 2010의 중단된 기능 및 수정된 기능 [레벨:30]아별 2017-01-20 8826
72 엑셀일반 Excel 2013에서 중단된 기능 및 변경된 기능 image [레벨:30]아별 2017-01-20 4784
71 엑셀VBA [VBA Tips] GetAttr() 디렉토리 속성 확인 에러 (8208을 리턴할 경우) [레벨:30]아별 2016-04-06 5142
70 엑셀기초 엑셀 언어 설정 변경 방법.. imagefile [레벨:30]아별 2015-05-21 9040
69 엑셀기초 감사합니다~ [1] 열시히 2014-07-21 5831
68 엑셀일반 [엑셀에러] stdole32.tlb imagefile [레벨:30]아별 2013-09-27 112921
67 엑셀기초 [초보용 팁] 엑셀 명칭 알아보기.. imagefile [7] [레벨:30]아별 2013-05-27 7489
66 엑셀기초 [초보용 팁] 수식을 값으로 만들기 imagefile [레벨:30]아별 2013-05-15 6644
65 API [펌] 윈도우 메시지(Windows Message) 목록 및 설명.. [레벨:30]아별 2013-03-06 5898
64 엑셀함수 INDEX와 SUMPRODUCT를 사용하여 2가지 이상 조건에 맞는 값 찾기.. imagefile [레벨:30]아별 2012-11-07 5790
63 엑셀일반 오피스 2013 프리뷰 버전 발표.. imagefile [레벨:30]아별 2012-07-24 5790
62 엑셀함수 [링크] (초보자를 위한) '엑셀 달인'으로 인정받는 '함수' 사용법 imagefile [레벨:30]아별 2012-05-25 7828
» 엑셀일반 [ XML ] 엑셀로 XML 데이터 다루기.. file [2] [레벨:30]아별 2011-12-26 12367
60 엑셀일반 인쇄 설정 팁.. 머리글, 바닥글에 페이지 번호 설정하는 방법 imagefile [2] [레벨:30]아별 2011-08-03 22376
59 엑셀일반 조건부 서식 적용하기 (로또 번호 맞춰 보기) imagefile [4] [레벨:30]아별 2011-04-11 15987
58 엑셀일반 엑셀 기본 제공 셀 표시형식입니다. file [레벨:30]아별 2011-03-05 7551
57 엑셀일반 젠쟝 2010.. 사라져 버린 Ctrl+Shift+0 [레벨:30]아별 2011-02-18 11385
56 엑셀일반 외부 파일에 연결된(외부 파일 참조) 셀 찾는 방법입니다. imagefile [4] [레벨:30]아별 2011-02-08 24279