Преобразование в формат XML. Экспорт данных XML Создать xml из excel

Те, кто владеет замечательной программой TextKit, или по крайней мере слышали о ней, (кто не слышал, срочно ) должны были заметить в числе ее возможностей разбор XML-документов в любой шаблон. Т.е, если у нас есть XML документ, мы без проблем стряпаем сайт при помощи ТекстКита.

Остается одна маленькая проблема, как получить этот XML. В действительности, для целей создания сайта можно брать готовые документы различных партнерок, типа МиксМаркета или Озона . Но про них как-нибудь в другой раз. Я расскажу, как получить XML из обычной XLS-таблицы. Фактически, это формат MS Excel.

Владение этим знанием позволит нам делать сайты при помощи ТекстКита из любых баз в Экселе. Работать будем в 2007-м офисе.

1) Загружаем надстройку для работы с XML отсюда .

2) Заходим в главное меню экселя, параметры.

3) В открывшемся меню выбираем «Надстройка». Ищем пункт «Управление», выбираем «Надстройки Excel» и жмем «Перейти»:

4) В открывшемся окне через «Обзор» выбираем нужный нам файл XmlTools.xla , добавляем его в панель и активируем, поставив галочку напротив него.

По умолчанию этот файл хранится в следующей папке на жестком диске:
\Office Samples\OfficeExcel2003XMLToolsAddin.

5) Если вы все сделали правильно, то в «Параметрах» в разделе «Надостройки» вы увидите установленное расширение:

Теперь берем наши данные для создания XML документа и работаем с ними.

1) Жмем на кнопку на панели. Выбираем первый пункт:

2) В появившемся окне выбираем yes/no если наши данные содержат / не содержат заголовка, в моем случае содержат, потому переставляю галочку на yes. Здесь же зажимаем маленькую «пипку», чтобы выбрать ячейки с данными:

3) Протягиванием выбираем данные для создания XML — документа. Отжимаем «пипку» и жмем ОК.

При разработке системы электронного документооборота потребовалось реализовать функции для экспорта данных в популярных форматах. В частности, в формате Microsoft Excel. Требования к экспорту были довольно простые – экспортировать данные с минимумом форматирования, т.е. никаких объединенных ячеек, игр со шрифтами и т.п. Форматы экспорта XLSX и Excel XML.

В данном случае расскажу про Excel XML .

Итак, в любой системе оперирующей табличными данными раной или поздно возникает потребность экспорта данных. Цели экспорта разные:

Реализовать в классе набор функций для записи значений ячеек и ряда – основное требование, подразумевающее создание функций для записи значений ячеек указанных типов и возможность записи готового ряда в файл.

Возможность работы с неограниченным объемом данных – разумеется, сам класс экспорта отвечать за записываемый объем не сможет, но он должен предоставить функции для записи данных на диск и освобождения оперативной памяти для следующей порции данных.

Помимо описанных требований, понадобилось добавить сервисные функции:

• Включение автофильтра
• Сжатия файла в zip .

Реализация

Прежде всего, при создании класса осуществляю проверку конечного имени файла и запрашиваю количество колонок и рядов. Файл должен иметь корректное имя, а папка, в которую он будет сохранен, должна существовать. Все как обычно.
Формат Excel XML позволяет сохранять в файле информацию о пользователе, который его создал, поэтому, при создании заголовка записываю название организации, информация о пользователе и дате создания файла.

Public function writeDocumentProperties($organization = null, $user = null) { fwrite($this->file, ""); if (!is_null($user)) { fwrite($this->file, "".$user->description.""); fwrite($this->file, "".$user->description.""); } $dt = new Datetime(); $dt_string = $dt->format("Y-m-d\TH:i:s\Z"); fwrite($this->file, "".$dt_string.""); fwrite($this->file, "".$dt_string.""); if (!is_null($organization)) fwrite($this->file, "".$organization->name.""); fwrite($this->file, "12.00"); fwrite($this->file, ""); }
Правда, именно в этой функции используются сущности системы документооборота - organization (организация) и user (пользователь). Заменить эти сущности на, скажем, строковые значения, не проблема.

Наиболее интересной в заголовке является информация о стилях. В формате Excel XML они реализованы очень удобно, поэтому просто создаю таблицу со стилями для строк, даты/времени и гиперссылки.

Public function writeStyles() { fwrite($this->file, ""); //default style fwrite($this->file, ""); //Datetime style fwrite($this->file, ""); fwrite($this->file, ""); fwrite($this->file, ""); //Hyperlink style fwrite($this->file, ""); //Bold fwrite($this->file, ""); fwrite($this->file, ""); }

Подготовительные работы закончил, можно переходить к записи данных. Открытие рабочего листа – это всего пара тэгов, как раз в этот момент используется информация о количестве колонок и рядов.

Public function openWorksheet() { fwrite($this->file, ""); fwrite($this->file, strtr("

", array("{col_count}"=>$this->colCount, "{row_count}"=>$this->rowCount))); }
Но вот запись рядов – процесс поинтереснее. Класс должен работать быстро и обрабатывать неограниченный объем данных, ведь записей может быть тысяч сто или даже миллион! Хочешь скорости – работай с памятью, хочешь неограниченный объем данных – работай с диском. Чтобы помирить требования, реализовал функции resetRow и flushRow.
Первая – очищает текущий ряд, после чего его снова можно наполнять данными, а вторая – записывает текущий ряд в открытый файл на диск. Их совместное использование позволяет выдерживать баланс между скоростью и объемом используемой памяти.

Public function resetRow() { $this->currentRow = array(); } public function flushRow() { fwrite($this->file, implode("", $this->currentRow)); unset($this->currentRow); }
Каждая ячейка записывается функцией соответствующей типу данных, а именно appendCellxxx, где xxx – тип данных. Допустимые типы данных: Num, String, Real, DateTime, Date, Time, Link. Пример функции для записи числового значения:

Public function appendCellNum($value) { $this->currentRow = "".$value.""; }
После записи всех данных остается закрыть рабочий лист и рабочую книгу.

Применение

Использование описанного класса основано на экспорте данных с помощью провайдера CArrayDataProvider . Однако, предполагая, что объем экспортируемых данных может оказаться очень большим применен специальный итератор CDataProviderIterator , который перебирает возвращаемые данные по 100 записей (можно указать иное число записей).

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(); }
В моем случае, каждый ряд записывается на диск, что, пока, является вполне приемлемым, но в будущем, возможно, потребует изменений. Например, будет разумно сохранять не каждый ряд, а каждый десяток или даже сотню рядов за раз. Тогда скорость экспорта увеличится.

Скорость

Кстати, на собственном опыте убедился, как важно предполагать возможность существования больших объемов данных при пакетной операции, такой как экспорт.
Изначально, пытался осуществлять экспорт данных используя CActiveDataProvider , что требовало при экспорте 1000 записей порядка 240 секунд! Изменив запрос так, чтобы использовать CArrayDataProvider сократил время экспорта 1000 записей до 0.5 секунды!
Специально для этой публикации замерил показатели экспорта.
Экспортировал 1626 записей с 9 атрибутами, представляющих собой информацию о закрытых инцидентах (см. ITSM).

Исходный вид экспортируемой таблицы

Результат

(извините, картинка исчезает после публикации)

Показатели экспорта

Объем конечного файла: 1 312 269
Объем сжатого файла: 141 762
Затраченное время: примерно 0.5 сек

Кому интересно, может получить исходный код моего класса безвозмездно. Только надо не забыть поправить функцию writeDocumentProperties , чтобы отвязаться от сущностей системы документооборота organization и user, или использовать свои аналогичные сущности с соответствующими свойствами.

Объявление

Формат файлов XML Document

Будучи созданным в качестве формата для хранения текстовых данных, формат XML представляет собой документ, который понимают не только люди, но и машины. XML - это язык, не зависящий от платформы, который был создан для хранения различных данных. Обладая простотой и легкостью в использовании, данный язык не уступает в популярности языку HTML. Он также весьма распространен в Интернете. Тот факт, что файлы XML можно легко редактировать простейшими текстовыми редакторами, только увеличивает его популярность.

Технические сведения о файлах XML

Документ XML представляет собой последовательность символов, в которой каждый символ Unicode можно найти в каждом отдельном документе. Такой код из символов Unicode, который составляет документ XML, разделен на маркеры и текстовое содержание на основе простых синтаксических правил. У этого формата имеется важное преимущество перед HTML: формат XML поддерживает произвольное размещение тегов для четкого определения тех данных, которые такие теги охватывают.

Дополнительная информация о формате XML

Вопрос о том, как с 1С сохранить в XML файл, волнует людей, которые работают с документацией фирмы и отчётами. В число таких специалистов входят бухгалтеры. Сегодняшние стандарты предполагают хранение большей части информации предприятия в программе 1С. При этом формирование документов в Эксель остаётся востребованным, потому что это необходимо для обработки данных, обмена счетами с контрагентами. Использование современных и прогрессивных способов передачи информации между 1С и Excel сильно экономит время человека, который этим занимается.

Для быстрого осуществления этой операции предусмотрена процедура выгрузки. Зайдя в 1С, пользователь выбирает раздел «Файл», затем «Выгрузка». На жёстком диске компьютера либо на съёмном устройстве выбирается конкретная локация, куда будет отправлен файл, и его формат. Если нужен файл Эксель, выбираем формат XML. Выгрузка больших документов занимает много времени. Перед её началом желательно разобраться, какой конкретно объём информации вас интересует, и выгрузить именно его. Это достигается путём выделения нужных блоков при работе в 1С. Если нужно добавить новые позиции к уже имеющимся данным в XML, это осуществляется путём выгрузки в существующий документ, во время которой происходит слияние. Когда нужно добавить несколько строк, это проще сделать через буфер обмена.

Как перенести данные из XML файла в 1С?

Чаще при работе предприятия нужно провести обратную процедуру: получить файл Эксель в 1С. Рассмотрим, как это сделать. Современные версии 1С предлагают осуществлять подобную процедуру через вкладку «Импорт» либо «Загрузка». Они расположены в той же категории «Файл». Пользователь выбирает нужный документ на компьютере или съёмном диске, после чего он загружается в базу 1С. Разработчики 1С предусмотрели «карантинную» зону, куда сначала отправляются файлы после загрузки из других программ. Такая зона нужна, чтобы предварительно проверить достоверность данных и особенно форматирование. Если форматирование сбито, его сначала нужно восстановить с помощью специальных сервисов или вручную. Загрузка данных с нарушенным форматированием в 1С приводит к сбоям и искажениям в базе данных.

Если возникают сложности в загрузке данных либо постоянно сбивается формат, то нужно проверить актуальность версий программ 1С и Эксель. Если использовать современные версии, то шанс возникновения проблем минимальный.

Настройка сериализации при переносе в 1С

Сериализацией называется процесс преобразования данных из 1С в Эксель и обратно. Процедура сериализации запускается автоматически, когда пользователь выбирает импорт либо экспорт данных из 1С. Не помешает предварительная настройка переноса и сериализации файлов. Предусмотрена возможность выбрать формат файлов, разрядность и другие параметры, которые помогут избежать ошибок при обмене данными между разными программами.

Чтобы загружать в 1С большое количество файлов из Эксель, не получится обойтись стандартным копированием. Для таких целей разрабатывается специальный загрузчик, который осуществляет подключение к 1С и контролирует процедуру загрузки. Часто компании приходится заказывать такую программу для переноса данных у разработчиков с учётом особенностей конкретно данного предприятия. Это требует затрат, но окупается благодаря тому, что скорость загрузки файлов с зарплатой и других документов заметно возрастает, а ошибки при переносе исчезают.

Использование веб-сервисов для переноса данных в 1С

Для загрузки данных в 1С можно использовать специализированные веб-сервисы. Они помогают сформировать файл Эксель в 1С и не требуют финансовых затрат. Подгрузить информацию с их помощью можно однократно либо использовать такие сервисы постоянно. Желательно выбрать сервисы, которые совместимы с вашими версиями программ, тогда вероятность сбоев станет низкой.

Работа по отправке данных с 1С 7.7 не подразумевает специального образования, но требует навыков и опыта. Если к делу приступает необученный сотрудник, его желательно контролировать, чтобы база данных организации не наполнилась недостоверной либо повреждённой информацией.

При любых процедурах переноса данных большое внимание следует уделять сохранности структуры данных. В рамках этого желательно выполнять следующие рекомендации.

Перед началом загрузки удостовериться, что версии программ совместимы.

Загружать данные в «карантинную» зону 1С, чтобы непроверенные данные не попадали сразу в единую базу данных и не искажали её.

Проверять формат данных, которые были получены из Эксель. Если в процессе переноса формат был сбит, потребуется потратить время, чтобы его восстановить. Если этого не сделать, то отдельные ячейки могут суммироваться непредсказуемым образом: количество товара с массой, цена с общей стоимостью. Это собьёт систему статистики и анализа данных.

Желательно, чтобы загруженные данные перед отправкой в базу проверял опытный сотрудник: владелец предприятия, главный бухгалтер или иное ответственное лицо.

Для персонала полезно выработать и озвучить правила загрузки файлов. Часто ошибки и сбои возникают по причине невнимательности, рассеянности, халатности отдельных сотрудников. Они заносят данные из двух ячеек в одну, применяют разные сокращения, разные знаки (точку и запятую) при записи дробных чисел, совершают другие подобные оплошности, которые сильно сбивают формат и приводят к нежелательным сбоям и ошибкам. Для руководства предприятия стоит разработать единый формат записи данных, тогда при переносе через загрузчик или вручную будет меньше проблем. Автоматизация компании с помощью разработок 1С предполагает, что сотрудники всех уровней будут применять единую программу для ввода данных. Если отдельные кадры не умеют пользоваться такой системой или допускают оплошности при вводе значений, полезно провести дополнительное обучение.

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

Рассмотрим процедуру преобразования объекта базы данных в формат XML:

1. В окне базы данных выделите необходимый объект щелчком левой кнопки мыши и выберите команду Файл, Экспорт (File, Export) или щелкните по объекту правой кнопкой и выберите в контекстном меню команду Экспорт (Export).
2. Появится диалоговое окно Экспорт объекта (Export To). В раскрывающемся списке Типы (Save as type) выделите элемент Документы XML (XML Documents). В поле Имя файла (File name) введите название документа HTML и нажмите кнопку Экспорт (Export).
3. Появится диалоговое окно Экспорт XML (Export XML) (рис. 12.9).
4. Это диалоговое окно содержит три флажка для трех составляющих документа XML: данных, схемы данных и представления данных (о них см. разд. "Формат XML в Microsoft Office 2002" данной главы). Установите флажки для тех частей документа XML, которые необходимо получить в результате экспорта. Например, если установить одновременно флажки данные (XML) (Data (XML)) и схема данных (Schema of the data), то схема данных будет экспортирована и сохранена отдельно от файла данных XML, в файле XSD. Для таблиц можно экспортировать только данные или их структуру или данные и структуру вместе и не экспортировать представление таблицы в виде Web-страницы: флажок презентация данных (XSL) (Presentation of your data (XSL)) можно снять.

Рис. 12.9. Диалоговое окно Экспорт XML

Замечание

Флажок данные (XML) следует сбросить лишь в том случае, если вы хотите экспортировать только новое представление объекта базы данных, а данные были экспортированы в файл XML раньше. Иначе при открытии полученной Web-страницы будет выдано сообщение об ошибке подключения к источнику данных, а сама страница окажется практически пустой.

1. Чтобы установить дополнительные параметры преобразования, нажмите кнопку Дополнительно (Advanced) (рис. 12.10).

Рис. 12.10. Вкладка Данные диалогового окна Экспорт XML

1. Кроме того, это диалоговое окно содержит еще две вкладки для двух оставшихся составляющих документа XML: схемы данных и представления данных. На каждой из этих вкладок можно установить флажок, позволяющий экспортировать соответствующую часть документа XML, указать путь и имя файла, в котором будет сохранена эта часть документа, и задать некоторые дополнительные параметры:
   • на вкладке Данные (Data) с помощью раскрывающегося списка Кодировка (Encoding) можно выбрать кодировку для сохранения содержимого страницы;
   • на вкладке Схема (Schema) с помощью переключателей можно выбрать, экспортировать ли структуру данных в отдельный файл XSD или внедрить ее в файл XML вместе с данными;

Рис. 12.11. Отчет "Счет", открытый в базе данных

• на вкладке Презентация (Presentation) с помощью переключателей можно выбрать формат представления Web-страницы: обрабатываемый на стороне клиента HTML или на стороне сервера ASP. Если экспортируемый объект содержит рисунки, которые необходимо включить в результирующий документ, можно указать имя папки, в которой они расположены. На этой же вкладке есть поле для ввода имени файла XSL с описанием оформления страницы. Если указанный файл не существует, Access 2002 создаст файл в формате XSL описанием внешнего представления документа (разработчик может использовать его в дальнейшем как основу для разработки файла в формате XSLT для преобразования документа XML в другой формат).

Если потребуется дополнительная информация о представлении данных в формате XML, нажмите кнопку Справка (Help). Информацию о разработке и преобразовании документов XML можно найти на Web-узле Microsoft для разработчиков: http://msdn.microsoft.com/ .

Рис. 12.12. Отчет "Счет", данные которого преобразованы в формат XML, открытый в Internet Explorer

1. Нажмите кнопку ОК.
2. Если предложенные параметры преобразования не были изменены, в результате будут созданы три файла: XML - с данными, XSL - со стилями и НТМ - с представлением преобразованного объекта. Чтобы открыть полученную Web-страницу, требуется открыть в Internet Explorer файл НТМ или ASP, в зависимости от того, какой формат представления был выбран..

К сожалению, подчиненные формы и отчеты, а также связанные рисунки не будут добавлены в документ XML, полученный из объекта базы данных. Однако простые объекты с внедренными рисунками, экспортированные в формат XML, будут выглядеть практически точно так же, как исходные в Access. Вы можете в этом убедиться, сравнив, к примеру, исходный и преобразованный в формат XML отчет "Счет" (Invoice) из базы данных "Борей" (Northwind) (рис. 12.11 и 12.12).

Если данных в источнике данных выбранного объекта много, загрузка Web-страницы в программу просмотра Интренета может занять существенное время.

Замечание

Поскольку сами данные, содержащиеся в таком документе Web, полностью хранятся "в файле XML, нет нужды в доступе к базе данных, из которой изначально были взяты эти данные (как, например, и в случае преобразования отчета в формат XML). Это означает, что для обеспечения доступа к такому документу достаточно поместить только упомянутый набор файлов, содержащих описание этого документа, на сервер Web. С другой стороны, это означает, что изменение данных в таком документе возможно лишь с помощью приложений, имеющих доступ к файлу XML и способных воспринять этот формат.

Расширение файла	.xml
Категория файлов
Файл-пример	(252,17 KiB)
Связанные программы	Microsoft Visual Studio 2013 JAPISoft EditiX Wattle XMLwriter MacroMates TextMate