Excel - XML 파일 연동
Excel 데이터를 XML로 저장하려면, "Developer" / "XML" / "Export" 버튼을 눌러주거나 다음과 같이 "Save As" 대화상자에서 아예 문서 자체를 XML로 저장할 수도 있습니다.
물론, 애석하게도 일반적인 엑셀 파일의 내용은 다음과 같이 오류를 발생시키며 저장이 되지 않습니다.
Cannot save XML data because the workbook does not contain any XML mappings.
또는,
Cannot save or export XML data. The XML maps in this workbook are not exportable.
즉, XML로 저장하기 위해서는 해당 셀과 XML 사이에 매핑이 이뤄져야 하는데 기본적으로는 이런 정보가 없기 때문에 당연히 오류가 발생할 수밖에 없는 것입니다.
이번 글에서는 어떻게 하면 엑셀 데이터를 XML로 저장하는지에 대해서 알아볼텐데요. 사실 지난번에 쓴 2개의 글은 원래 이것 때문에 테스트하다가 씌여진 것이었습니다. ^^
엑셀 매크로 함수 관련 오류
; https://www.sysnet.pe.kr/2/0/1005
XML/XSD - 외래키처럼 참조 제한 거는 방법
; https://www.sysnet.pe.kr/2/0/1006
1. XSD 정의
엑셀에서 XML과 연동하려면, 제일 먼저 XML의 형식을 정의하는 XSD 파일을 정의해 줘야 합니다. 아래는 제가 만든 예제입니다.
==== test.xsd ====
<?xml version="1.0" encoding="utf-8"?>
<xs:schema targetNamespace="https://www.sysnet.pe.kr/Data.xsd"
elementFormDefault="qualified"
xmlns="https://www.sysnet.pe.kr/Data.xsd"
xmlns:my="https://www.sysnet.pe.kr/Data.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="DataT">
<xs:sequence>
</xs:sequence>
<xs:attribute name="Name" type="xs:string" use="required" />
<xs:attribute name="Scope" type="ScopeEnumT" use="required" />
<xs:attribute name="Description" type="xs:string" use="optional" />
</xs:complexType>
<xs:simpleType name="ScopeEnumT">
<xs:restriction base="xs:string">
<xs:enumeration value="None"/>
<xs:enumeration value="AppDomain"/>
<xs:enumeration value="InProcess"/>
<xs:enumeration value="OutofProcess"/>
<xs:enumeration value="Machine"/>
</xs:restriction>
</xs:simpleType>
<xs:complexType name="GroupDataT">
<xs:attribute name="Name" type="xs:string" use="required" />
<xs:attribute name="DataNameRef" type="xs:string" use="required" />
<xs:attribute name="Description" type="xs:string" use="optional" />
</xs:complexType>
<xs:element name="IntegratedData">
<xs:complexType>
<xs:sequence>
<xs:element name="Item" maxOccurs="unbounded" type="DataT">
</xs:element>
<xs:element name="GroupItem" maxOccurs="unbounded" type="GroupDataT">
</xs:element>
</xs:sequence>
</xs:complexType>
<xs:key name="NameIDDef">
<xs:selector xpath="./my:Item" />
<xs:field xpath="@Name" />
</xs:key>
<xs:keyref name="NameIDRef" refer="NameIDDef">
<xs:selector xpath="./my:GroupItem" />
<xs:field xpath="@DataNameRef" />
</xs:keyref>
</xs:element>
</xs:schema>
위의 스키마가 표현하려는 XML 내용을 간단하게 작성해 보면 다음과 같습니다.
==== test.xml ====
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<IntegratedData xmlns="https://www.sysnet.pe.kr/Data.xsd">
<Item Name="Type" Scope="AppDomain" Description="# of types in AppDomain"/>
...[n 개의 Item 노드]...
<GroupItem Name="PerfGroup" DataNameRef="Type" Description="5"/>
...[n 개의 GroupItem 노드]...
</IntegratedData>
2. XSD 소스 추가
"Developer" 리본에서 XML 그룹의 "Source" 버튼을 누르면 다음과 같이 "XML Source" 밴드가 보이고, 내부의 "XML Maps..." 버튼을 눌러줍니다.
"Add..." 버튼을 눌러 이전에 만들어 둔 "test.xsd" 파일을 추가해 줍니다.
3. 엑셀 Worksheet에 XSD 매핑
제가 만든 XSD는 목록용(element[@maxOccurs='unbounded'])으로 만든 것이기 때문에 "XML Source" 밴드에서 ns1:Item과 ns1:GroupItem을 끌어다 엑셀 워크 시트에 다음과 같은 식으로 배치해 줍니다.
이제, 각각의 셀에 값을 입력해 주고 "Developer" / "XML" / "Export" 버튼을 눌러서 XML 파일로 내보내거나, 기존 XML 파일로 저장된 내용을 "Import" 버튼을 이용해서 불러올 수 있습니다.
기본적인 엑셀과 XML 데이터 연동은 이걸로 모두 설명이 되었습니다.
마무리 하기 전에 한가지 팁이라면, 테이블에 새로운 Row를 추가할 때는 "Home" / "Cells" / "Insert" 기능을 이용하면 되지만 마지막 Row의 아래에 새롭게 추가하고자 할 때는 "Home" / "Cells" / "Insert" 버튼을 아래로 확장해서 나오는 "Insert Table Row Below" 메뉴를 선택해 주시면 됩니다.
유효성 확인
XSD와 연동된 엑셀 데이터를 단순히 XML로 저장만 하는 것은 그다지 큰 의미가 없습니다. 중요한 것은 "값의 유효성 확인"인데요. 기본적으로 엑셀은 XML로 내보내기할 때 XSD에 기반한 유효성 확인을 하지 않습니다. 즉, 다음과 같이 입력된 경우 XSD에 지정한 제약에 위반되었다 해도 아무런 문제 없이 XML 파일로 저장이 됩니다.
사실, 대부분의 경우 이것은 사용자가 의도한 경우가 아닐 수 있는데요. 아쉽게도 유효성 확인 기능이 기본적으로 꺼져 있기 때문에 이런 '문제'가 발생하는 것입니다. 이를 해결하려면, "Developer" / "XML" / "Map Properties"라는 버튼을 눌러서 다음과 같이 "Validate data against schema for import and export" 옵션을 켜 주시면 됩니다.
설정 후에, 다시 한번 "Export"로 저장을 해보면 다음과 같이 오류가 발생하는 것을 볼 수 있습니다.
'scopeTest' violates enumeration constraint of 'None AppDomain InProcess OutofProcess Machine'.
The attribute 'Scope' with value 'scopeTest' failed to parse.
사실 XSD 유효성 확인을 '저장하는 시점'에 하는 것은 그다지 바람직한 방법은 아닙니다. 그보다는, "Data" / "Data Tools" / "Data Validation" 기능을 이용하여 작업 시트를 구성해주는 것이 좋을 것입니다.
첨부된 파일은 예제로 사용된 XSD/XML과 엑셀 파일입니다.
(그나저나,,, 왜 이런 내용은 엑셀 책에 안 나오는 것일까요? 가지고 있는 엑셀 책에서 XML 연동 부분을 찾아보니 없군요. "개발자를 위한 엑셀 2010" ... 이라는 제목으로 출판된 책이 있었으면 좋겠습니다. ^^)
[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]