При разработке системы электронного документооборота потребовалось реализовать функции для экспорта данных в популярных форматах. В частности, в формате 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();
}
Кому интересно, может получить исходный код моего класса безвозмездно. Только надо не забыть поправить функцию writeDocumentProperties
, чтобы отвязаться от сущностей системы документооборота organization и user, или использовать свои аналогичные сущности с соответствующими свойствами. У вас есть идеальный файл со всеми вашими данными о продукте или статистике, и теперь вам нужны эти данные в формате XML. Преобразование файла в XML теперь намного проще, чем раньше, XML стал популярным. Теперь доступно множество инструментов для конвертирования файлов из одного формата в другой. Конечно, эти инструменты абсолютно бесполезны, если ваши данные не готовы к конверсии. Данные должны быть структурированы и организованы таким образом, чтобы их можно было правильно преобразовать в XML. Тип вашего файла будет определяющим фактором того, как вы приближаетесь к преобразованию. Если ваш файл является одним из следующих типов, вам повезло. Примечание. Важно отметить, что файлы Microsoft экспортируются в определенном формате. Эти документы могут быть или не быть тем, что вы ожидаете. Они содержат элементы, относящиеся к форматированию вашего документа Excel. Это было бы не идеально, если вы используете данные в приложении, которое относится к формату входящих данных. Если, с другой стороны, ваш документ не находится ни в одном из этих форматов, вам придется либо искать инструмент конвертера, либо конвертировать его вручную (если у вас слишком много для преобразования вручную). Программное обеспечение, такое как XML Spy, может конвертировать ваш код на основе SQL в документы XML. Когда у вас есть большой набор данных, настоятельно рекомендуется конвертировать. Tab Delimited, CSV Существуют другие инструменты конвертера, такие как XML-конвертер Rustemsoft или XML-Buddy (плагин Eclipse) из программного обеспечения Bocaloca. Эти приложения могут экспортировать ваши данные, а также предоставить вам файл XML Schema или DTD (определение типа документа), если это необходимо. Эти приложения очень удобны и делают процесс преобразования вашего файла или базы данных в XML более удобным для пользователя. Возьмем базовый текстовый документ и преобразуем его в XML. Но сначала о том, как быстро просмотреть основные правила XML: XML. Расширяемый язык разметки представляет собой текстовый кросс-платформенный язык, который позволяет вам структурировать данные (например, адреса в адресной книге). Ожидается, что XML-документ будет иметь правильный синтаксис. Поэтому при создании XML-документов они должны быть хорошо сформированы. Хорошо сформированный документ имеет следующее: Закрытые теги (<> или ) Атрибут элемента должен быть заключен в двойные кавычки (<страна country id = «7»>) XML чувствителен к регистру. Другими словами, начальный и конечный теги должны использовать один и тот же случай Вот основной список голландских художников XVII века. Рембрандт Ван Рейн, 1606-1669, Ночной дозор Как видите, данные разделяются запятыми. Каждая строка представляет собой отдельный фрагмент данных. Каждый кусок имеет имя художника, год рождения и до смерти и, наконец, одну из их картин. Поэтому нам нужно создать 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 и затем снова выполните экспорт (при этом сопоставления сохранятся, но будут потеряны строки в конце файла). Действие 4. Сохранение схемы XML в файле схемы XML (XSD) Примечание:
Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также (на английском языке). Представляющими собой данные на основе ввода описания с тегами или настроек программ. Открыть их для редактирования обычным двойным кликом не получается. Связано это с тем, что для ассоциации с расширением не установлено нужное приложение, которое применяется по умолчанию. Но если нужно получить удобочитаемый файл в виде таблицы с возможностью редактирования, можно открыть файл XML в Excel. При этом не нужны никакие конверторы, способные преобразовывать форматы между собой. Единственное замечание: такая возможность имеется только в версиях Office версии 2003 и выше. Рассмотрим импорт данных на основе Excel версии 2016 года. Первый и самый простой способ состоит в том, чтобы изначально запустить программу Excel. При старте приложение вместо приветствия и логотипа выдаст специальное окно входа, в котором слева в меню присутствует строка «Открыть другие книги». После этого используется пункт обзора, а в новом окне в качестве открываемого формата выбирается XML. После этого привычным методом находим нужный файл и жмем кнопку открытия. В этом случае распознает не как текстовый документ, содержащий описания и теги, а как самую обычную таблицу. Естественно, данные можно редактировать по своему усмотрению, но об этом позже. Практически ничем не отличается от первого еще один предлагаемый метод. Файл XML в Excel можно открыть из файлового меню или использовать для этого быстрое сочетание Ctrl + O. Опять же, сначала выбирается тип формата, подлежащего открытию, после этого находится нужный файл и нажимается соответствующая кнопка. Есть еще несколько методов XML в Excel. Так, в версии программы 2016 года можно использовать меню верхней панели, где выбирается раздел «Данные», а затем нажимается кнопка получения внешних данных. В выпадающем меню нужно просто выбрать раздел «Из других источников» и в новом меню использовать строку «Из импорта XML». После этого следует стандартная процедура поиска нужного файла с последующим открытием. При использовании любого из этих методов пользователь получает структуру таблицы. Редактирование производится точно так же, как это делается со стандартными файлами XLS. Иногда для удобства редактирования и сохранения данных целесообразно использовать меню для разработчиков. В этом случае можно импортировать не все содержимое XML-файла, а только то, что действительно необходимо, вставляя информацию в соответствующие столбцы и строки, указав в качестве источника данных именно XML-объект. Но для этого необходимо в самой программе выполнить вход в аккаунт, используя свою регистрацию в Microsoft. Сохранить измененный файл можно сразу же в оригинальном формате, выбрав соответствующий тип из списка. Из файлового меню, если объект был сохранен в «родном» формате Excel, можно выбрать функцию экспорта, нажать на изменение типа файла и установить в качестве конечного формата именно XML. Если пользователю лень заниматься подобными преобразованиями, или в работе он использует версию Office ниже версии 2003, для открытия данного формата в виде таблицы придется использовать специальный конвертор. Таких программ сейчас предлагается достаточно много. В крайнем случае, если и это не подходит, без проблем можно обратиться к специализированным онлайн-сервисам, на которых формат будет изменен в течение пары десятков секунд. По окончании таких действий останется только загрузить готовый результат в формате XLS на жесткий диск, а потом открыть его в Excel. Впрочем, в большинстве случаев такие действия не требуются, поскольку в том же Office 2003 возможность прямого открытия (импорта) формата XML уже предусмотрена изначально. И думается, мало кто сегодня использует устаревшие офисные продукты Microsoft. Вопрос о том, как с 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С предлагают постоянные обновления, упрощающие работу с данными, поэтому не помешает проверять обновления и загружать их для программы. Это поможет использовать последние разработки для упрощения загрузки и выгрузки информации.
Бытовая техника ТВ и Проекторы Фото и видеотехника ", array("{col_count}"=>$this->colCount, "{row_count}"=>$this->rowCount)));
}
Но вот запись рядов – процесс поинтереснее. Класс должен работать быстро и обрабатывать неограниченный объем данных, ведь записей может быть тысяч сто или даже миллион! Хочешь скорости – работай с памятью, хочешь неограниченный объем данных – работай с диском. Чтобы помирить требования, реализовал функции resetRow и flushRow.
Первая – очищает текущий ряд, после чего его снова можно наполнять данными, а вторая – записывает текущий ряд в открытый файл на диск. Их совместное использование позволяет выдерживать баланс между скоростью и объемом используемой памяти.
Каждая ячейка записывается функцией соответствующей типу данных, а именно appendCellxxx, где xxx – тип данных. Допустимые типы данных: Num, String, Real, DateTime, Date, Time, Link. Пример функции для записи числового значения:
После записи всех данных остается закрыть рабочий лист и рабочую книгу.Применение
Использование описанного класса основано на экспорте данных с помощью провайдера CArrayDataProvider . Однако, предполагая, что объем экспортируемых данных может оказаться очень большим применен специальный итератор CDataProviderIterator , который перебирает возвращаемые данные по 100 записей (можно указать иное число записей).
В моем случае, каждый ряд записывается на диск, что, пока, является вполне приемлемым, но в будущем, возможно, потребует изменений. Например, будет разумно сохранять не каждый ряд, а каждый десяток или даже сотню рядов за раз. Тогда скорость экспорта увеличится.Скорость
Кстати, на собственном опыте убедился, как важно предполагать возможность существования больших объемов данных при пакетной операции, такой как экспорт.
Изначально, пытался осуществлять экспорт данных используя CActiveDataProvider , что требовало при экспорте 1000 записей порядка 240 секунд! Изменив запрос так, чтобы использовать CArrayDataProvider сократил время экспорта 1000 записей до 0.5 секунды!
Специально для этой публикации замерил показатели экспорта.
Экспортировал 1626
записей с 9
атрибутами, представляющих собой информацию о закрытых инцидентах (см. ITSM).Исходный вид экспортируемой таблицы
Результат
(извините, картинка исчезает после публикации)Показатели экспорта
Объем конечного файла: 1 312 269
Объем сжатого файла: 141 762
Затраченное время: примерно 0.5
секШаг 1 Какой формат является вашим исходным файлом?
Ваше приложение будет конвертировать его для вас:
Шаг 2 Если вы на этом этапе, должны преобразовать его вручную.
Хендрик Аверкамп, 1585-1634, зима
Ян де Бисхоп, 1628-1671, Оостропорт
Год рождения художника
Год смерти художника
Живопись художникаШаг 3 С учетом этого формата мы можем создать XML-файл.
XMLDoc в качестве MSXML
2
. DOMDocument50
Кому:
XMLDoc как Msxml2. Домдокумент
Как открыть XML в Excel: способ первый
Как открыть формат XML в Excel: способ второй
Открытие XML: способ третий
Редактирование, сохранение и экспорт
Заряжаем аккумулятор телефона без телефона
Создать аккаунт Google без телефона: пошаговая инструкция
Вирусы HTLV-I: эпидемиология Другие заболевания из группы Болезни крови, кроветворных органов и отдельные нарушения, вовлекающие иммунный механизм
