При разработке системы электронного документооборота потребовалось реализовать функции для экспорта данных в популярных форматах. В частности, в формате Microsoft Excel. Требования к экспорту были довольно простые – экспортировать данные с минимумом форматирования, т.е. никаких объединенных ячеек, игр со шрифтами и т.п. Форматы экспорта XLSX и Excel XML.
В данном случае расскажу про Excel XML .
Итак, в любой системе оперирующей табличными данными раной или поздно возникает потребность экспорта данных. Цели экспорта разные:
Реализовать в классе набор функций для записи значений ячеек и ряда – основное требование, подразумевающее создание функций для записи значений ячеек указанных типов и возможность записи готового ряда в файл.
Возможность работы с неограниченным объемом данных – разумеется, сам класс экспорта отвечать за записываемый объем не сможет, но он должен предоставить функции для записи данных на диск и освобождения оперативной памяти для следующей порции данных.
Помимо описанных требований, понадобилось добавить сервисные функции:
- Включение автофильтра
- Сжатия файла в zip .
Реализация
Прежде всего, при создании класса осуществляю проверку конечного имени файла и запрашиваю количество колонок и рядов. Файл должен иметь корректное имя, а папка, в которую он будет сохранен, должна существовать. Все как обычно.Формат Excel XML позволяет сохранять в файле информацию о пользователе, который его создал, поэтому, при создании заголовка записываю название организации, информация о пользователе и дате создания файла.
Public function writeDocumentProperties($organization = null, $user = null)
{
fwrite($this->file, "
Правда, именно в этой функции используются сущности системы документооборота - organization (организация) и user (пользователь). Заменить эти сущности на, скажем, строковые значения, не проблема.
Наиболее интересной в заголовке является информация о стилях. В формате Excel XML они реализованы очень удобно, поэтому просто создаю таблицу со стилями для строк, даты/времени и гиперссылки.
Public function writeStyles()
{
fwrite($this->file, "
Подготовительные работы закончил, можно переходить к записи данных. Открытие рабочего листа – это всего пара тэгов, как раз в этот момент используется информация о количестве колонок и рядов.
Public function openWorksheet()
{
fwrite($this->file, " Public function resetRow()
{
$this->currentRow = array();
}
public function flushRow()
{
fwrite($this->file, implode("", $this->currentRow));
unset($this->currentRow);
}
Public function appendCellNum($value)
{
$this->currentRow = " Public function exportExcelXML($organization, $user, &$filename)
{
$this->_provider = new CArrayDataProvider(/*query*/);
Yii::import("ext.AlxdExportExcelXML.AlxdExportExcelXML");
$export = new AlxdExportExcelXML($filename, count($this->_attributes), $this->_provider->getTotalItemCount() + 1);
$export->openWriter();
$export->openWorkbook();
$export->writeDocumentProperties($organization, $user);
$export->writeStyles();
$export->openWorksheet();
//title row
$export->resetRow();
$export->openRow(true);
foreach ($this->_attributes as $code => $format)
$export->appendCellString($this->_objectref->getAttributeLabel($code));
$export->closeRow();
$export->flushRow();
//data rows
$rows = new CDataProviderIterator($this->_provider, 100);
foreach ($rows as $row)
{
$export->resetRow();
$export->openRow();
foreach ($this->_attributes as $code => $format)
{
switch ($format->type)
{
case "Num":
$export->appendCellNum($row[$code]);
/*other types*/
default:
$export->appendCellString("");
}
}
$export->closeRow();
$export->flushRow();
}
//close all
$export->closeWorksheet();
$export->closeWorkbook();
$export->closeWriter();
//zip file
$export->zip();
$filename = $export->getZipFullFileName();
}
Объявление Будучи созданным в качестве формата для хранения текстовых данных, формат XML представляет собой документ, который понимают не только люди, но и машины. XML - это язык, не зависящий от платформы, который был создан для хранения различных данных. Обладая простотой и легкостью в использовании, данный язык не уступает в популярности языку HTML. Он также весьма распространен в Интернете. Тот факт, что файлы XML можно легко редактировать простейшими текстовыми редакторами, только увеличивает его популярность. Документ XML представляет собой последовательность символов, в которой каждый символ Unicode можно найти в каждом отдельном документе. Такой код из символов Unicode, который составляет документ XML, разделен на маркеры и текстовое содержание на основе простых синтаксических правил. У этого формата имеется важное преимущество перед HTML: формат XML поддерживает произвольное размещение тегов для четкого определения тех данных, которые такие теги охватывают. XML – это универсальный формат работы с данными. Его поддерживает множество программ, в том числе из сферы СУБД. Поэтому конвертация информации в XML важна именно с точки зрения взаимодействия и обмена данными между различными приложениями. Excel как раз входит в число программ, которые работают с таблицами, и даже может выполнять манипуляции с базами данных. Разберемся, как конвертировать файлы Эксель в XML. Конвертация данных в формат XML не такой уж простой процесс, так как в его ходе должна быть создана специальная схема (schema.xml). Впрочем, для преобразования информации в простейший файл данного формата достаточно иметь под рукой обычные инструменты сохранения в Excel, а вот для создания хорошо структурированного элемента придется основательно повозиться с составлением схемы и её подключением к документу. В Excel можно сохранить данные в формате XML, просто воспользовавшись меню «Сохранить как…»
. Правда, нет гарантии, что потом все программы будут корректно работать с файлом, который был создан таким способом. Да и не во всех случаях этот способ работает. Таким образом, преобразование файла из формата Excel в XML будет совершено. Конвертировать формат Excel в XML можно с помощью инструментов разработчика на вкладке программы. При этом, если пользователь все выполнит правильно, то на выходе он получит, в отличие от предыдущего способа, полноценный XML-файл, который корректно будет восприниматься сторонними приложениями. Но сразу нужно сказать, что не каждому новичку может хватить знаний и умений сразу научится конвертировать данные таким способом. Как видим, открывающим и закрывающим тегом для документа в целом служит «data-set»
. В этой же роли для каждой строки выступает тег «record»
. Для схемы вполне будет достаточно, если мы возьмем всего две строки таблицы, а не будем переводить её всю вручную в XML. Название открывающего и закрывающего тега столбца может быть произвольным, но в данном случае для удобства мы предпочли просто перевести русскоязычные наименования колонок на английский язык. После того, как данные внесены, просто сохраняем их через функционал текстового редактора в любом месте на жестком диске в формате XML под названием «schema»
. Как видим, существуют два основных способа конвертации файлов XLS и XLSX в формат XML программой Microsoft Excel. Первый из них предельно простой и заключается в элементарной процедуре сохранения с заданным расширением через функцию «Сохранить как…»
. Простота и понятность данного варианта, несомненно, являются преимуществами. Но у него есть один очень серьезный недостаток. Преобразование выполняется без учета определенных стандартов, и поэтому файл, конвертируемый таким способом, сторонними приложениями может просто не распознаваться. Второй вариант предполагает составление карты XML. В отличие от первого способа, таблица, преобразованная по данной схеме, будет соответствовать всем стандартам качества XML. Но, к сожалению, не каждый пользователь сможет быстро разобраться с нюансами этой процедуры. Если необходимо создать файл XML-данных и файл схемы XML из диапазона ячеек на листе, можно использовать версию 1.1 надстройки "Средства XML для Excel 2003" для расширения существующих возможностей XML в Microsoft Excel 2007 и более поздних версиях. Примечание:
Эта надстройка была разработана для Excel 2003. Документация и пользовательский интерфейс ссылаются на списки, которые в версиях приложения позднее Excel 2003 называются таблицами Excel. Дополнительные сведения о работе с этой надстройкой см. в статье Использование надстройки "Средства XML" версии 1.1 для Excel 2003 . Действие 2. Преобразование диапазона ячеек в XML-таблицу Введите данные, для которых необходимо создать файл XML-данных и файл схемы XML. Данные должны быть представлены в табличном формате в виде столбцов и строк (так называемые обычные данные). На вкладке Надстройки
в группе Команды меню
щелкните стрелку возле надписи Средства XML
и нажмите кнопку Преобразовать диапазон в список XML
. Введите диапазон ячеек с данными, которые необходимо преобразовать, как абсолютную ссылку в текстовом поле. В поле Первая строка содержит имена столбцов
выберите Нет
, если первая строка содержит данные, или Да
, если первая строка содержит заголовки столбцов, и нажмите кнопку ОК
. Excel автоматически создаст схему XML, выполнит привязку ячеек к схеме и создаст XML-таблицу. Важно:
Если откроется редактор Visual Basic и отобразится сообщение об ошибке Visual Basic for Applications (VBA), выполните следующие действия. Нажмите кнопку ОК
. В выделенной строке модуля кода VBA удалите из строки "50". Другими словами, измените: Нажмите клавишу F5 для поиска следующей строки, содержащей текст "XMLDoc As msxml2.DOMDocument50", нажмите кнопку ОК
и измените строку, как в предыдущем пункте. Нажмите F5 еще раз, чтобы найти и изменить другие экземпляры строки. Если после нажатия клавиши F5 сообщения об ошибке VBA больше не отображается, закройте редактор Visual Basic, чтобы вернуться в книгу. Диапазон ячеек будет преобразован в XML-таблицу. Примечание:
Чтобы отобразить все карты XML в книге, на вкладке Разработчик
в группе XML
нажмите кнопку Источник
для отображения области задач "Источник XML". В нижней части области задач "Источник XML" нажмите кнопку Карты XML
. Если вкладка Разработчик
не видна, выполните три первых действия, указанных в следующем разделе, чтобы добавить ее на ленту Excel. Действие 3. Экспорт XML-таблицы в файл XML-данных (XML) Примечание:
При создании карт XML и экспорте данных в Excel в XML-файлы существует ограничение на количество строк, которые можно экспортировать. При экспорте в XML-файл из Excel можно сохранить до 65 536 строк. Если файл содержит более 65 536 строк, Excel сможет экспортировать только первые строки (число строк mod 65 537). Например, если лист содержит 70 000 строк, Excel экспортирует 4464 строки (70 000 mod 65 537). Мы рекомендуем последовать одному из следующих советов: 1) используйте формат XLSX; 2) сохраните файл в формате "Таблица XML 2003 (*.xml)" (при этом будут потеряны сопоставления); 3) удалите все строки после 65 536 и затем снова выполните экспорт (при этом сопоставления сохранятся, но будут потеряны строки в конце файла). Если кто-либо пришлет файл XML, содержащий данные в таблицах, вам не придется читать весь текст и все угловые скобки с тегами. Можно загрузить этот документ напрямую в Excel, сообщить Excel, как следует отобразить этот документ, и работать с данными при помощи карт. В этом трюке используются возможности Excel, доступные только в Excel для Windows старше 2003 года. Более ранние версии Excel не поддерживают их; эти возможности не поддерживаются и в существующих, и планируемых версиях Excel для Macintosh. Начнем с простого документа XML, приведенного в листинге 8.1. // Листинг 8.1. Простой документ XML для анализа в Excel
< ?xml version-"1.0" encoding-"UTF-8"?>
Этот документ можно открыть непосредственно в Excel командой Файл → Открыть (File → Open). Откроется диалоговое окно (рис. 8.1). Если вы выберете переключатель XML-список (As an XML list), то увидите предупреждение, что Excel создаст собственную схему для этого документа, не имеющего схемы (рис. 8.2). Щелкнув кнопку ОК, вы увидите, какой способ Excel выбрал для представления информации в открываемом документе в виде электронной таблицы (рис. 8.3). Обратите внимание, что Excel ожидает встретить формат даты, который используется для элемента даты, поэтому даты, импортированные как 2003-10-05, будут отображаться как 10/5/2003. Теперь, когда документ загружен в Excel, обрабатывать данные можно так же, как любые другие данные в Excel, - вставлять их в формулы, создавать именованные диапазоны, строить диаграммы на основе содержимого и т. д. Чтобы помочь вам, в Excel предусмотрено несколько встроенных возможностей анализа данных. Раскрывающиеся списки в заголовках столбцов позволяют выбирать способ сортировки данных (по умолчанию данные выводятся в том порядке, в котором они записаны в исходном документе). Можно также включить отображение строки итогов Итог (Total); для этого можно воспользоваться панелью инструментов Список (List) или щелкнуть правой кнопкой мыши в любом месте списка и в контекстном меню выбрать команду Список → Строка итогов (List → Total Row). Когда строка итогов появится, выбрать вид итоговой информации можно будет в раскрывающемся меню (рис. 8.4). Рис. 8.4. Выбор итогов для списка XML в Excel Данные можно обновить, добавив при этом в обновляемую область информацию из документа XML с такой же структурой. Если у вас есть еще один документ с такой структурой, можете щелкнуть список правой кнопкой мыши, в контекстном меню выбрать команду XML → Импорт (XML → Import) и выбрать второй документ. Кроме того, после редактирования данные можно экспортировать обратно в файл XML, щелкнув список правой кнопкой мыши и выбрав в контекстном меню команду XML → Экспорт (XML → Export). Это превращает Excel в очень удобный инструмент редактирования простых XML-документов с табличной структурой. Если данные достаточно простые, вы чаще всего можете доверить Excel выбор способа представления содержимого файла и использовать предусмотренные настройки по умолчанию. Если же данные усложняются, особенно если они содержат даты или текст, который выглядит как числа, то вы, возможно, захотите использовать схемы XML, чтобы указать Excel, как следует читать данные и какие данные подойдут к данной карте. Для нашего документа XML-схема может выглядеть, как в листинге 8.2. // Листинг 8.2. Схема для данных о продаже книг
< ?xml version="1.0" encoding="UTF-8"?>
Обратите внимание, что элемент date определен как дата, а элемент ISBN определен как строка, а не как целое число. Если вы начнете с открытия этой схемы, а не документа, то заставите Excel загружать документ, сохранив ведущий нуль в ISBN. На этот раз вы создадите список до загрузки документа XML, начав с пустого рабочего листа. Вам понадобится открыть область задач Источник XML (XML Source). Если она еще не открыта, нажмите сочетание клавиш Ctrl+Fl. Затем в раскрывающемся списке вверху области задач выберите Источник XML (XML Source) и вы увидите что-то похожее на рис. 8.6. Чтобы загрузить схему, щелкните кнопку Карты XML (XML Maps). Откроется диалоговое окно Карты XML (XML Maps) (рис. 8.7). Щелкните кнопку Добавить (Add), чтобы открыть схему, и выберите схему (рис. 8.8). Если схема не ограничивает документы одним начальным элементом, появится диалоговое окно с просьбой выбрать корневой элемент. Так как документы в этом примере начинаются с элемента sales, выберите «sales». Когда вы щелкнете кнопку ОК, появится предупреждение о возможных сложностях с интерпретацией схем. XML-схема (XML Schema) - это огромная спецификация, поддерживающая чрезвычайно много структур, не отвечающих способу восприятия информации в Excel, поэтому в Excel есть некоторые ограничения. В диалоговом окне Карты XML (XML Maps) Excel сообщит, что схема была добавлена к электронной таблице. Если вы щелкнете кнопку ОК, то вернетесь в главное окно Excel и в области задач Источник XML (XML Source) появится диаграмма, отображающая структуру схемы. Теперь, когда у вас есть структура, можно создать список. Самый простой способ сделать это, особенно с небольшими документами, как наш, - перетащить значок sales на ячейку А1. Теперь, обустроив дом для данных, нужно заселить его. Можно щелкнуть кнопку Импорт XML-данных (Import XML Data) на панели инструментов Список (List) или щелкнуть правой кнопкой мыши список и в контекстном меню выбрать кнопку XML → Импорт (XML → Import). Если вы выберете файл, который уже открывали ранее (в листинге 8.1), то увидите результат, как на рис. 8.3. Обратите внимание на добавление ведущих нулей к значениям, которые теперь являются текстовыми, как и должно быть. Элементы можно перетаскивать и по отдельности, если вы хотите поменять их местами, или помещать разные фрагменты информации в разные места электронной таблицы. Поддержка XML-карт и списков в Excel означает, что можно создавать электронные таблицы, работающие с данными, которые поступают в отдельных файлах, с большей гибкостью, чем это было с предыдущими форматами, например CSV (с разделителями-запятыми) или форматом с разделительной табуляцией. Вместо того чтобы подключаться к базе данных для интерактивного редактирования данных, пользователь сможет отредактировать файл XML, находясь в самолете, и передать его заказчику сразу же после приземления. Возможно, лучшее свойство новых возможностей XML Excel - это их гибкость. Пока данные организованы в структуру, соответствующую сетке таблицы, у Excel имеется совсем немного правил относительно того, какие виды XML можно передавать туда. Несколько раз щелкнув мышью и совершенно ничего не программируя, можно интегрировать данные XML в электронные таблицы.", array("{col_count}"=>$this->colCount, "{row_count}"=>$this->rowCount)));
}
Но вот запись рядов – процесс поинтереснее. Класс должен работать быстро и обрабатывать неограниченный объем данных, ведь записей может быть тысяч сто или даже миллион! Хочешь скорости – работай с памятью, хочешь неограниченный объем данных – работай с диском. Чтобы помирить требования, реализовал функции resetRow и flushRow.
Первая – очищает текущий ряд, после чего его снова можно наполнять данными, а вторая – записывает текущий ряд в открытый файл на диск. Их совместное использование позволяет выдерживать баланс между скоростью и объемом используемой памяти.
Каждая ячейка записывается функцией соответствующей типу данных, а именно appendCellxxx, где xxx – тип данных. Допустимые типы данных: Num, String, Real, DateTime, Date, Time, Link. Пример функции для записи числового значения:
После записи всех данных остается закрыть рабочий лист и рабочую книгу.Применение
Использование описанного класса основано на экспорте данных с помощью провайдера CArrayDataProvider . Однако, предполагая, что объем экспортируемых данных может оказаться очень большим применен специальный итератор CDataProviderIterator , который перебирает возвращаемые данные по 100 записей (можно указать иное число записей).
В моем случае, каждый ряд записывается на диск, что, пока, является вполне приемлемым, но в будущем, возможно, потребует изменений. Например, будет разумно сохранять не каждый ряд, а каждый десяток или даже сотню рядов за раз. Тогда скорость экспорта увеличится.Скорость
Кстати, на собственном опыте убедился, как важно предполагать возможность существования больших объемов данных при пакетной операции, такой как экспорт.
Изначально, пытался осуществлять экспорт данных используя
Формат файлов XML Document
Технические сведения о файлах XML
Дополнительная информация о формате XML
Расширение файла
.xml
Категория файлов
Файл-пример
(252,17 KiB)
Связанные программы
Microsoft Visual Studio 2013
JAPISoft EditiX
Wattle XMLwriter
MacroMates TextMate
Способ 1: простое сохранение
Способ 2: инструменты разработчика
XMLDoc в качестве MSXML
2
. DOMDocument50
Кому:
XMLDoc как Msxml2. Домдокумент
В последние несколько лет язык XML (Extensible Markup Language, букв, «расширяемый язык разметки») стал распространенным форматом обмена информацией, и нет ничего необычного в том, что люди и организации отправляют друг другу файлы XML. Простые структуры, лежащие в основе XML, делают обмен информацией чрезвычайно простым, причем неважно, используют ли все стороны одно и то же программное обеспечение и браузеры . Однако до недавних пор, хотя общие утилиты XML стали широко распространены, заполнить пробел между документами XML и пользовательским интерфейсом было еще достаточно трудно. Microsoft Excel упрощает эту задачу, по крайней мере, для данных в сетке таблицы. 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
<
?xml version-
"1.0"
encoding-
"UTF-8"
?>
<
sales>
<
sale>
<
date>
2003
-
10
-
05
<
isbn>
0596005385
<
title>
Off1ce 2003
XML Essentia1s
<
priceus>
34.95
<
quantity>
200
<
customer IO=
"1025"
>
Zork"s Books
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<
?xml version=
"1.0"
encoding=
"UTF-8"
?>
<
xs:
schema xmlns:
xs=
"http://www.w3.org/2001/XMLSchema"
elementFormDefault=
"qualified"
>
<
xs:
element name=
"sales"
>
<
xs:
complextype>
<
xs:
sequence>
<
xs:
element maxOccurs=
"unbounded"
ref=
"sale"
>
<
xs:
element name=
"sale"
>
<
xs:
complextype>
<
xs:
sequence>
<
xs:
element ref=
"date"
>
<
xs:
element ref=
"ISBN"
>
<
xs:
element ref=
"T1tle"
>
<
xs:
element ref=
"PriceUS"
>
<
xs:
element ref=
"quantity"
>
<
xs:
element ref=
"customer"
>
<
xs:
element name=
"date"
type=
"xs:date"
>
<
xs:
element name=
"ISBN"
type=
"xs:string"
>
<
xs:
element name=
"Title"
type=
"xs:string"
>
<
xs:
e1ement name=
"PriceUS"
type=
"xs:decimal"
>
<
xs:
element name=
"quant1ty"
type=
"xs:integer"
>
<
xs:
element name=
"customer"
>
<
xs:
complextype mixed=
"true"
>
<
xs:
attribute name=
"ID"
use
=
"required"
type=
"xs:integer"
>
