Juri Shutenko Personal Homepage. Visual FoxPro.

XML. Экспортируем данные их MS Excel. Часть 1 (новая редакция)
Статьи по   на этом сайте:

Как уже говорилось ранее, XML позиционируется, как универсальное средство обмена данными между разными приложениями и разными платформами. Microsoft в своем бизнес-пакете Office 2003 Professional ввел возможность более широкой работы с файлами в формате XML. Стало быть что? Конец автоматизации экселевских таблиц посредством COM? Чепчики приготовили? Наберите побольше воздуха для громкого "ура!", выйдите из тесного помещения, чтобы повыше подбросить чепчики и чтобы ваше громогласное разнеслось подальше и ... выдохните, вернитесь назад и положите чепчики на место. Не все так просто, как хотелось бы!

А дело в том, что при сохранении рабочей книги Excel в формате XML используется собственная схема разметки XMLSS.

Возьмите здесь простенький файл "oil_consumption.xls" - 10 строчек, 3 столбца.

XLS Source file

Если вы счастливый обладатель MS Excel 2003 Professional, откройте этот файл и сохраните его как XML Spreadsheet. В результате вы получите файл следующиего содержания:

 
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>Juri Shutenko</Author>
  <LastAuthor>Juri Shutenko</LastAuthor>
  <Created>2006-01-20T08:37:36Z</Created>
  <LastSaved>2006-01-20T08:49:46Z</LastSaved>
  <Company>Maardu Linnavalitsus</Company>
  <Version>11.6568</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>8340</WindowHeight>
  <WindowWidth>11355</WindowWidth>
  <WindowTopX>360</WindowTopX>
  <WindowTopY>90</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font x:CharSet="186"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s21">
   <NumberFormat ss:Format="Short Date"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="10" x:FullColumns="1"
   x:FullRows="1">
   <Column ss:Width="53.25"/>
   <Column ss:Index="3" ss:Width="52.5"/>
   <Row>
    <Cell ss:StyleID="s21"><Data ss:Type="DateTime">2005-12-10T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="Number">200.34</Data></Cell>
    <Cell><Data ss:Type="String">AS Petkan</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s21"><Data ss:Type="DateTime">2005-12-11T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="Number">216.55</Data></Cell>
    <Cell><Data ss:Type="String">AS Eurooil</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s21"><Data ss:Type="DateTime">2005-12-12T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="Number">647.28</Data></Cell>
    <Cell><Data ss:Type="String">AS Neste</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s21"><Data ss:Type="DateTime">2005-12-13T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="Number">321.15</Data></Cell>
    <Cell><Data ss:Type="String">AS Statoil</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s21"><Data ss:Type="DateTime">2005-12-14T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="Number">621.47</Data></Cell>
    <Cell><Data ss:Type="String">AS Neste</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s21"><Data ss:Type="DateTime">2005-12-15T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="Number">556.14</Data></Cell>
    <Cell><Data ss:Type="String">AS Statoil</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s21"><Data ss:Type="DateTime">2005-12-16T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="Number">333.58</Data></Cell>
    <Cell><Data ss:Type="String">AS Eurooil</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s21"><Data ss:Type="DateTime">2005-12-17T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="Number">248.36</Data></Cell>
    <Cell><Data ss:Type="String">AS Eurooil</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s21"><Data ss:Type="DateTime">2005-12-18T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="Number">287.32</Data></Cell>
    <Cell><Data ss:Type="String">AS Statoil</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s21"><Data ss:Type="DateTime">2005-12-19T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="Number">867.24</Data></Cell>
    <Cell><Data ss:Type="String">AS Neste</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <RangeSelection>R1C1:R10C3</RangeSelection>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet2">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet3">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

Разберем этот файл. Корневой узел Workbook имеет шесть дочерних узлов:

  1. <DocumentProperties...>
  2. <ExcelWorkbook...>
  3. <Styles>
  4. <Worksheet...>
  5. <Worksheet...>
  6. <Worksheet...>

Стало быть, при попытке импорта этого файла в курсор VFP мы получим шесть записей и чертову кучу полей, имена которых соответствуют именам дочерних узлов второго уровня иерархии - <Author>, <LastAuthor>, <Created>... <WindowHeight> и так далее, абсолютно не интересных нам, с точки зрения получения данных с листа. Правда, среди этих полей будет одно мемо-поле "table", содержащее неформатированную строку с данными.

Content of the Table field

А все дело в том, что при сохранении рабочей книги Excel в формате XML используется собственная схема разметки XMLSS. Причем утверждается, что полученный файл будет хорошо отображаться в Opera, даже при отсутствии инсталяции MS Excel 2003. Ну не знаю, какая опера споет эту песню, моя, 8-ка, показывает сплошную неформатированную строку с кучей False, даже при инсталлированном оффисном продукте. Но вроде как спасательная веревка все-таки имеется. Что это означает? В рабочую книгу можно ввести свою схему сопоставления - XML Mapping, то есть подключить свою собственную XSD-схему. Давайте попробуем! А если учесть, что у нас имеется совершенно замечательный класс XMLAdapter, то и сделать это, должно быть, достаточно просто. Так ли это?

Подготовим таблицу для приема данных с листа рабочей книги.

Structure of the Oil.dbf

Далее с помощью замечательного класса XMLAdapter подготовим тестовый XML-файл со схемой, вынесенной в отдельный файл.

oXA = CREATEOBJECT("XMLAdapter")
oXA.XMLSchemaLocation="c:\VFPTests\oil_consumption_from_xml_adapter.xsd"
oXA.AddTableSchema("oil")
oXA.ToXML(cFile,,.T.)

Содержание полученного файла приведено ниже:

<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
<xsd:schema id="VFPDataSet" xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xsd:element name="VFPDataSet" msdata:IsDataSet="true">
    <xsd:complexType>
      <xsd:choice maxOccurs="unbounded">
        <xsd:element name="oil" minOccurs="0" maxOccurs="unbounded">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="pdate" type="xsd:date"/>
              <xsd:element name="pvolume">
                <xsd:simpleType>
                  <xsd:restriction base="xsd:decimal">
                    <xsd:totalDigits value="9"/>
                    <xsd:fractionDigits value="2"/>
                  </xsd:restriction>
                </xsd:simpleType>
              </xsd:element>
              <xsd:element name="seller">
                <xsd:simpleType>
                  <xsd:restriction base="xsd:string">
                    <xsd:maxLength value="20"/>
                  </xsd:restriction>
                </xsd:simpleType>
              </xsd:element>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:choice>
      <xsd:anyAttribute namespace="http://www.w3.org/XML/1998/namespace" 
             processContents="lax"/>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Осталось только подсунуть схему в рабочую книгу Excel. Если вы еще не открыли указанный тестовый файл, то откройте его. В меню View отметьте Task Pane. Из его меню Task Panes выберите XML Source:

MS Excel 2003 Task Pane

Откроется XML Source. Пока он пуст.

Щелкаем по кнопке "XML Maps". Откроется диалоговый блок XML Maps." Пока он тоже пуст. Щелкаем по кнопке "Add...

В открывшемся стандартном окне "Select XML Source" выбираем тип файла "XML schema files (.xsd)"... и далее выбираем подготовленный с помощью XMLAdapter'а файл схемы и получаем предупреждение:

Если мы согласимся с этим предупреждением и щелкнем по "Yes" и затем подтвердим наш выбор в диалоговом блоке XML Maps, по клавише "OK" мы получим элементы сопоставления в Task Pane "XML Source", как это показано на рис внизу:

Выполним сопоставления для нашей таблички. Вставим перед блоком данных пустую строку и попробуем выполнить сопоставления данных - схеме. Перетащим мышкой соответствующие элементы сопоставления на блоки данных. Первый - первую колонку, второй на вторую и третий на третью.

Внизу окна "XML Source" есть ссылка - "Verify MAP for export...". Щелкнем по ней! И получим:

Что скажет нам Help по этому поводу? А следующее!

Содержание XML сопоставления не может быть экспортировано в виду следующих обстоятельств:
 
XML сопоставление не может быть экспортировано, если не сохраняется связь размеченных элементов с другими элементами. Эта связь не может быть сохранена по следующим причинам:

  • Определение сопоставляемого элемента содержится внутри последовательности со следующими аттрибутами:
    • Аттрибут "maxoccurs" не равен 1.
    • Последовательность имеет более одного определенного дочернего элемента или имеет другой наборщик, определенный как прямой дочерний.
    • Неповторяющиеся прямородственные элементы одного и того же повторяющегося родителя сопоставлены с различными XML списками.
    • Множественные повторяющиеся элементы сопоставлен с одним и тем же XML списком, а повторение не определено родительским элементом.
    • Дочерние элементы от разных родителей сопоставлены с одним и тем же XML списком.
  • XML сопоставление содержит один из приведенных ниже конструкторов XML схемы:
    • Список списков - Один список элементов содержит другой список элементов.
    • Денормализованные данные - XML список содержит элемент, который был определен в схеме, как встречающийся только один раз (аттрибут "maxoccurs" установлен равным 1). Когда вы добавляете такой элемент в XML лист, Excel заполняет колонку списка множественными инстанциями элемента.
    • Choice - Имеется сопостовляемый элемент, который является частью конструктора <choice> схемы.

Well! Взглянем еще раз на схему, сгенерированную объектом класса XMLAdapter Visual FoxPro. Как видим, конструктор <choice> в схеме присутствует. (подробно о составлении схем поговорим в одной из следующих частей). Придется "приложить ручки" чтобы добиться корректного экспорта данных из листа Excel.

В результате мы получаем файл схемы, который выглядит так:

<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
<xsd:schema id="VFPDataSet" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
   <xsd:element name="VFPDataSet" msdata:IsDataSet="true">
      <xsd:complexType>
         <xsd:sequence maxOccurs="unbounded">
            <xsd:element name="oil" minOccurs="0" maxOccurs="1">
               <xsd:complexType>
                  <xsd:sequence>
                     <xsd:element name="pdate" type="xsd:date"/>
                     <xsd:element name="pvolume">
                        <xsd:simpleType>
                           <xsd:restriction base="xsd:decimal">
                              <xsd:totalDigits value="9"/>
                              <xsd:fractionDigits value="2"/>
                           </xsd:restriction>
                        </xsd:simpleType>
                     </xsd:element>
                     <xsd:element name="seller">
                        <xsd:simpleType>
                           <xsd:restriction base="xsd:string">
                              <xsd:maxLength value="20"/>
                           </xsd:restriction>
                        </xsd:simpleType>
                     </xsd:element>
                  </xsd:sequence>
               </xsd:complexType>
            </xsd:element>
         </xsd:sequence>
      </xsd:complexType>
   </xsd:element>
</xsd:schema>

Что собственно сделано? Убираем из схемы конструктор (правильнее говорить "индикатор", как определено стандартом WEB-консорциума) <choice> и поскольку нам нужно получить все данные, заменяем его на на конструктор <sequence>, для которого определяем аттрибут "maxOccurs" с установленным значением "unbounded". Соответственно заменяем и закрывающий тэг конструктора <choice>. Кроме того, поскольку мы не предполагаем любых других аттрибутов, убираем их схемы конструктор AnyAttribute.

Добавляем схему в наш файл oil_consumption.xls. Проходит на "ура!" и через вызов меню Data->XML->Export экспортируем XML-файл, как "c:\vfptests\oil_excel.xml". В итоге получаем:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<VFPDataSet>
	<oil>
		<pdate>2005-12-10</pdate>
		<pvolume>204.35</pvolume>
		<seller>AS Petkam</seller>
	</oil>
	<oil>
		<pdate>2005-12-11</pdate>
		<pvolume>221.56</pvolume>
		<seller>AS Neste</seller>
	</oil>
	<oil>
		<pdate>2005-12-12</pdate>
		<pvolume>451.23</pvolume>
		<seller>AS Neste</seller>
	</oil>
	<oil>
		<pdate>2005-12-13</pdate>
		<pvolume>564.21</pvolume>
		<seller>AS Statoil</seller>
	</oil>
	<oil>
		<pdate>2005-12-14</pdate>
		<pvolume>323.98</pvolume>
		<seller>AS Statoil</seller>
	</oil>
	<oil>
		<pdate>2005-12-15</pdate>
		<pvolume>123.45</pvolume>
		<seller>AS Neste</seller>
	</oil>
	<oil>
		<pdate>2005-12-16</pdate>
		<pvolume>452.35</pvolume>
		<seller>AS Petkam</seller>
	</oil>
	<oil>
		<pdate>2005-12-17</pdate>
		<pvolume>138.21</pvolume>
		<seller>AS Neste</seller>
	</oil>
	<oil>
		<pdate>2005-12-18</pdate>
		<pvolume>549.21</pvolume>
		<seller>AS Statoil</seller>
	</oil>
	<oil>
		<pdate>2005-12-19</pdate>
		<pvolume>662.1</pvolume>
		<seller>AS Statoil</seller>
	</oil>
</VFPDataSet>

Остается проверить импорт этого файла в курсор VFP. Выполним приведенный ниже фрагмент кода:

oXMLAdapter=CREATEOBJECT("XMLAdapter")
oXMLAdapter.XMLSchemaLocation="c:\vfptests\oil_consumption_from_xml_adapter.xsd"
oXMLAdapter.LoadXML("c:\vfptests\oil_excel.xml",.T.)
* убедимся, что таблица создана
? oXMLAdapter.Tables.Count
oXMLAdapter.Tables.Item(1).ToCursor
BROWSE LAST

И имеем в итоге:

Data imported from Excel

И все-таки, как быть с экспортом XML данных из Excel. Идем на сайт Microsfot и после непродолжительного поиска находим, что Excel не может формировать Generic XML документы. Стало быть для каждого случая требуется подгтовка отдельной схемы? Ну ладно, оставим этот вопрос на дальнейшее рассмотрение.

Если обратиться к поиску по Инет, то вы найдете немало конверторов Excel XML-документов в generic XML-формат, ценою от 159$ до 459$. Поставим жирный крест на связке Excel+VFP посредством XML? Рановато, так как подобный файл, можно достаточно просто обработать через XML DOM. Об этом отдельный разговор и отдельная статья.

Удачи!

Статьи по   на этом сайте:
Cелектор для быстрого перехода на сайты, связанные с Visual FoxPro.