После импорта данных XML, сопоставления данных с ячейками листа и внесения изменений в данные часто бывает нужно экспортировать или сохранить данные в виде XML-файла.
Важно:
Экспорт данных XML (с не более 65 536 строк)
Экспорт данных XML (для более 65 536 строк)
Найдите разность общего количества строк в файле и числа 65 537. Обозначим это число как x.
Удалите x строк с начала листа Excel.
Экспортируйте лист в виде файла данных XML (смотрите в предыдущем разделе процедуры).
Нажмите кнопку Закрыть
, но не сохранить
лист. Снова откройте лист Excel.
Удалите все, кроме первых x строк, а затем экспортируйте в виде файла данных XML (смотрите в предыдущем разделе процедуры).
Эта процедура позволит оставшейся части строки. На данный момент имеется два файлы Экспорт XML, объединяющие Создание дубликата исходного листа.
Сохранение данных XML в сопоставленных ячейках в файле данных XML
Если нужно обеспечить обратную совместимость с более ранними версиями функциональности XML, можно сохранить файл в виде файла данных XML, а не использовать команду Экспорт
.
Примечание:
Если лист содержит заголовки или метки, отличающиеся от имен XML-элементов на карте XML, то при экспорте или сохранении данных XML приложение Excel использует имена XML-элементов.
Распространенные проблемы при экспорте данных XML
При экспорте данных XML могут появляться сообщения, подобные приведенным ниже.
Эту карту XML можно экспортировать, но некоторые необходимые элементы не сопоставлены
Такое сообщение может появиться по следующим причинам.
На карте XML, связанной с этой XML-таблицей, есть один или несколько необходимых элементов, которые с ней не сопоставлены.
В иерархическом списке элементов в области задач "Источник XML" необходимые элементы выделены красной звездочкой. Она находится в верхнем правом углу значка, который расположен слева от элемента. Чтобы сопоставить необходимый элемент, перетащите его туда, где он должен отображаться.
Элемент представляет собой рекурсивную структуру.
Типичный пример рекурсивной структуры - иерархия сотрудников и руководителей, в которой одни и те же XML-элементы вложены на нескольких уровнях. Возможно, вы сопоставили все элементы в области задач "Источник XML". Но Excel не поддерживает рекурсивные структуры глубиной более одного уровня, поэтому сопоставить все элементы не удастся.
XML-таблица содержит смешанное содержимое.
Смешанное содержимое возникает, когда элемент содержит дочерний элемент и простой текст за пределами дочернего элемента. Это часто бывает в случае, когда теги форматирования (например, теги полужирного начертания) используются для пометки данных внутри элемента. Дочерний элемент может отображаться (если он поддерживается в Excel), но текстовое содержимое теряется при импорте данных и недоступно при экспорте, т. е не используется ни при прямой, ни при обратной операции.
Невозможно экспортировать карты XML в книге
Карту XML не удастся экспортировать, если невозможно сохранить отношения сопоставленного элемента с другими элементами. Отношение может не сохраниться по следующим причинам.
Определение схемы сопоставленного элемента содержится в последовательности со следующими атрибутами:
Неповторяющиеся элементы одного уровня с одним и тем же повторяющимся родительским элементом сопоставлены с разными XML-таблицами.
Несколько повторяющихся элементов сопоставлены с одной и той же XML-таблицей, и повторение не определено предком.
Дочерние элементы разных родительских элементов сопоставлены с одной и той же XML-таблицей.
Кроме того, невозможно экспортировать карту XML, если она содержит одну из следующих конструкций схемы XML.
Список списков.
В одном списке элементов есть другой список элементов.
Ненормированные данные.
XML-таблица содержит элемент, который в соответствии с определением в схеме должен встречаться один раз (атрибуту maxoccurs
присвоено значение 1). При добавлении такого элемента в XML-таблицу Excel заполнит столбец таблицы несколькими его экземплярами.
Выбор.
Сопоставленный элемент является частью конструкции схемы
.
При разработке системы электронного документооборота потребовалось реализовать функции для экспорта данных в популярных форматах. В частности, в формате 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();
}
В моем случае, каждый ряд записывается на диск, что, пока, является вполне приемлемым, но в будущем, возможно, потребует изменений. Например, будет разумно сохранять не каждый ряд, а каждый десяток или даже сотню рядов за раз. Тогда скорость экспорта увеличится.
Скорость
Кстати, на собственном опыте убедился, как важно предполагать возможность существования больших объемов данных при пакетной операции, такой как экспорт.
Изначально, пытался осуществлять экспорт данных используя
XML – это универсальный формат работы с данными. Его поддерживает множество программ, в том числе из сферы СУБД. Поэтому конвертация информации в XML важна именно с точки зрения взаимодействия и обмена данными между различными приложениями. Excel как раз входит в число программ, которые работают с таблицами, и даже может выполнять манипуляции с базами данных. Разберемся, как конвертировать файлы Эксель в XML.
Конвертация данных в формат XML не такой уж простой процесс, так как в его ходе должна быть создана специальная схема (schema.xml). Впрочем, для преобразования информации в простейший файл данного формата достаточно иметь под рукой обычные инструменты сохранения в Excel, а вот для создания хорошо структурированного элемента придется основательно повозиться с составлением схемы и её подключением к документу.
Способ 1: простое сохранение
В Excel можно сохранить данные в формате XML, просто воспользовавшись меню «Сохранить как…»
. Правда, нет гарантии, что потом все программы будут корректно работать с файлом, который был создан таким способом. Да и не во всех случаях этот способ работает.
Таким образом, преобразование файла из формата Excel в XML будет совершено.
Способ 2: инструменты разработчика
Конвертировать формат Excel в XML можно с помощью инструментов разработчика на вкладке программы. При этом, если пользователь все выполнит правильно, то на выходе он получит, в отличие от предыдущего способа, полноценный XML-файл, который корректно будет восприниматься сторонними приложениями. Но сразу нужно сказать, что не каждому новичку может хватить знаний и умений сразу научится конвертировать данные таким способом.
- По умолчанию вкладка инструментов разработчика отключена. Поэтому, прежде всего, нужно её активировать. Переходим во вкладку «Файл»
и кликаем по пункту «Параметры»
.
- В открывшемся окне параметров перемещаемся в подраздел «Настройка ленты»
. В правой части окна устанавливаем галочку около значения «Разработчик»
. После этого жмем на кнопку «OK»
, размещенную в нижней части окна. Вкладка инструментов разработчика теперь включена.
- Далее открываем таблицу Excel в программе любым удобным способом.
- На её основе нам предстоит создать схему, которая формируется в любом текстовом редакторе. Для этих целей можно использовать и обычный Блокнот Windows, но лучше применять специализированное приложение для программирования и работы с языками разметки Notepad++ . Запускаем эту программу. В ней создаем схему. На нашем примере она будет выглядеть, как показано внизу на скриншоте окна Notepad++.
Как видим, открывающим и закрывающим тегом для документа в целом служит «data-set»
. В этой же роли для каждой строки выступает тег «record»
. Для схемы вполне будет достаточно, если мы возьмем всего две строки таблицы, а не будем переводить её всю вручную в XML. Название открывающего и закрывающего тега столбца может быть произвольным, но в данном случае для удобства мы предпочли просто перевести русскоязычные наименования колонок на английский язык. После того, как данные внесены, просто сохраняем их через функционал текстового редактора в любом месте на жестком диске в формате XML под названием «schema»
.
- Опять переходим к программе Excel с уже открытой таблицей. Перемещаемся во вкладку «Разработчик»
. На ленте в блоке инструментов «XML»
кликаем по кнопке «Источник»
. В открывшемся поле в левой части окна жмем на кнопку «Карты XML…»
.
- В открывшемся окне кликаем по кнопке «Добавить…»
.
- Запускается окно выбора источника. Переходим в директорию расположения схемы составленной ранее, выбираем её и кликаем по кнопке «Открыть»
.
- После того, как элементы схемы появились в окне, перетаскиваем их с помощью курсора в соответствующие ячейки наименования столбцов таблицы.
- Кликаем правой кнопкой мыши по полученной таблице. В контекстном меню последовательно переходим по пунктам «XML»
и «Экспорт…»
. После этого сохраняем файл в любой директории.
Как видим, существуют два основных способа конвертации файлов XLS и XLSX в формат XML программой Microsoft Excel. Первый из них предельно простой и заключается в элементарной процедуре сохранения с заданным расширением через функцию «Сохранить как…»
. Простота и понятность данного варианта, несомненно, являются преимуществами. Но у него есть один очень серьезный недостаток. Преобразование выполняется без учета определенных стандартов, и поэтому файл, конвертируемый таким способом, сторонними приложениями может просто не распознаваться. Второй вариант предполагает составление карты XML. В отличие от первого способа, таблица, преобразованная по данной схеме, будет соответствовать всем стандартам качества XML. Но, к сожалению, не каждый пользователь сможет быстро разобраться с нюансами этой процедуры.
error: Content is protected !!
