Microsoft Excel – удобный инструмент для организации и структурирования самых разнообразных данных. Он позволяет обрабатывать информацию разными методами, редактировать массивы данных.
Рассмотрим возможности использования его для формирования и обработки файлов веб-приложений. На конкретном примере изучим основы работы с XML в Excel.
XML – стандарт файла для передачи данных в Сети. Excel поддерживает его экспорт и импорт.
Рассмотрим создание XML-файла на примере производственного календаря.
Сохраняем в XML файл.
Другие способы получения XML-данных (схемы):
Один из вариантов:
Еще варианты:
Жмем ОК. С полученной таблицей можно работать, как с любым файлом Excel.
Созданную таблицу редактируем и сохраняем уже в формате Excel.
Принцип сбора информации из нескольких XML-файлов такой же, как и принцип преобразования. Когда мы импортируем данные в Excel, одновременно передается карта XML. В эту же схему можно переносить и другие данные.
Каждый новый файл будет привязываться к имеющейся карте. Каждому элементу в структуре таблицы соответствует элемент в карте. Допускается только одна привязка данных.
Чтобы настроить параметры привязки, откройте в меню «Разработчик» инструмент «Свойства карты».
Возможности:
Это все ручные способы импорта и экспорта файлов.
После импорта данных XML, сопоставления данных с ячейками листа и внесения изменений в данные часто бывает нужно экспортировать или сохранить данные в виде XML-файла.
Важно:
Найдите разность общего количества строк в файле и числа 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 не удастся экспортировать, если невозможно сохранить отношения сопоставленного элемента с другими элементами. Отношение может не сохраниться по следующим причинам.
Определение схемы сопоставленного элемента содержится в последовательности со следующими атрибутами:
атрибут maxoccurs не равен 1;
последовательность содержит более одного прямого дочернего элемента или включает в себя другой составной объект в качестве такого элемента.
Неповторяющиеся элементы одного уровня с одним и тем же повторяющимся родительским элементом сопоставлены с разными XML-таблицами.
Несколько повторяющихся элементов сопоставлены с одной и той же XML-таблицей, и повторение не определено предком.
Дочерние элементы разных родительских элементов сопоставлены с одной и той же XML-таблицей.
Кроме того, невозможно экспортировать карту XML, если она содержит одну из следующих конструкций схемы XML.
Список списков. В одном списке элементов есть другой список элементов.
Ненормированные данные. XML-таблица содержит элемент, который в соответствии с определением в схеме должен встречаться один раз (атрибуту maxoccurs присвоено значение 1). При добавлении такого элемента в XML-таблицу Excel заполнит столбец таблицы несколькими его экземплярами.
Выбор.
Сопоставленный элемент является частью конструкции схемы
При наличии карты XML сделайте следующее для импорта данных XML в сопоставленные ячейки:
Дополнительные сведения о проблемах см. в разделе в конце этой статьи.
Из импорта данных XML . Excel создаст уникальную карты XML для каждого импортируемого файла данных XML.
Примечание:
Если вы используете Excel с подпиской на Office 365, щелкните Данные > Получить данные > Из файла > Из XML .
Если вы используете Excel 2016 или более раннюю версию, на вкладке Данные нажмите кнопку Из других источников , а затем щелкните Из импорта данных XML .
Выберите диск, папку или расположение в Интернете, где находится файл данных XML (XML-файл), который вы хотите импортировать.
Выберите файл и нажмите кнопку Открыть .
В диалоговом окне Импорт данных выберите один из следующих параметров:
В XML-таблицу в существующей книге. Содержимое файла импортируется в новую таблицу XML на новом листе. Если файл данных XML не ссылается ни на какую схему, Excel создает ее на основе этого файла.
На существующий лист. Данные XML будут импортированы в двумерную таблицу, состоящую из строк и столбцов. Теги XML отображаются как заголовки столбцов, а данные выводятся в строках под соответствующими заголовками. Первый элемент (корневой узел) используется в качестве названия и отображается в указанной ячейке. Остальные теги сортируются в алфавитном порядке во второй строке. В этом случае схема не создается и вы не можете использовать карту XML.
На новый лист. Excel добавляет в книгу новый лист и автоматически помещает данные XML в его левый верхний угол. Если файл данных XML не ссылается ни на какую схему, Excel создает ее на основе этого файла.
Чтобы настроить поведение данных XML, например привязку данных, форматирование и макет, нажмите кнопку Свойства . Откроется диалоговое окно Свойства карты XML . Например, при импорте данных по умолчанию перезаписываются данные в сопоставленном диапазоне, но это поведение можно изменить.
Если не удается выполнить проверку данных на соответствие карте XML, выводится диалоговое окно Ошибка импорта XML . Чтобы получить дополнительные сведения об ошибке, нажмите кнопку Сведения в этом диалоговом окне. В следующей ниже таблице описаны ошибки, которые часто возникают при импорте данных.
|
Ошибка |
Объяснение |
|
Ошибка при проверке схемы |
Когда вы выбрали в диалоговом окне Свойства карты XML параметр Проверять данные на соответствие схеме при импорте и экспорте , данные были импортированы, но не проверены на соответствие указанной карте XML. |
|
Некоторые данные были импортированы как текст |
Часть импортированных данных или все данные были преобразованы из объявленного типа в текст. Чтобы использовать эти данные в вычислениях, необходимо преобразовать их в числа или даты. Например, значение даты, преобразованное в текст, не будет работать как нужно в функции ГОД, пока не будет преобразовано в тип данных "Дата". Excel преобразует данные в текст в следующих случаях: Данные имеют формат, который не поддерживается в Excel. Данные несовместимы с внутренним представлением типа данных XSD в Excel. Чтобы исправить эту проблему, удостоверьтесь, что данные XML соответствуют XML-схеме, проверив каждое из определений типа данных. |
|
Ошибка разбора XML |
Средству синтаксического анализа XML не удается открыть указанный XML-файл. Убедитесь, что в XML-файле отсутствуют синтаксические ошибки и XML построен правильно. |
|
Не удается найти карту XML, соответствующую этим данным |
Эта проблема может возникнуть в том случае, если для импорта выбрано несколько файлов данных XML и Excel не удается найти соответствующую карту XML для одного из них. Импортируйте схему для файла, указанного в строке заголовка этого диалогового окна, сначала следует, а затем повторно выполните импорт файла. |
|
Не удается изменить размер XML-таблицы для включения данных |
Вы пытаетесь добавить строки путем импорта или добавления данных в XML-таблицу, однако таблицу невозможно расширить. XML-таблицу можно дополнять только снизу. Например, сразу под XML-таблицей может находиться объект, такой как рисунок или даже другая таблица, который не позволяет расширить ее. Кроме того, возможно, что при расширении XML-таблицы будет превышен установленный в Excel предел по количеству строк (1 048 576). Чтобы исправить эту проблему, измените расположение таблиц и объектов на листе, чтобы XML-таблица могла дополняться снизу. |
XML-файл, который вы пытаетесь открыть, не ссылается на схему XML. Для работы с данными XML, содержащимися в файле, Excel требуется схема, основанная на его содержимом. Если такая схема неверна или не отвечает вашим требованиям, удалите ее из книги. Затем создайте файл схемы XML и измените файл данных XML так, чтобы он ссылался на схему. Дополнительные сведения см. в статье Сопоставление XML-элементов с ячейками карты XML .
Примечание: Схему, созданную Excel, невозможно экспортировать в качестве отдельного файла данных схемы XML (XSD-файла). Хотя существуют редакторы схем XML и другие способы создания файлов схемы XML, возможно, вы не имеете к ним доступа или не знаете, как ими пользоваться.
Выполните следующие действия, чтобы удалить из книги схему, созданную Excel:
При работе с несколькими файлами данных XML и несколькими схемами XML стандартным подходом является создание карты XML для каждой схемы, сопоставление нужных элементов, а затем импорт каждого из файлов данных XML в соответствующую карту XML. При использовании команды Импорт для открытия нескольких XML-файлов с одним пространством имен можно использовать только одну схему XML. Если эта команда используется для импорта нескольких XML-файлов, использующих одно пространство имен при разных схемах, можно получить непредсказуемые результаты. Например, это может привести к тому, что данные будут перезаписаны или файлы перестанут открываться.
Если нужно импортировать несколько XML-файлов с одним пространством имен, но с разными схемами XML, вы можете воспользоваться командой Из импорта данных XML (выберите Данные > Из других источников ). Эта команда позволяет импортировать несколько XML-файлов с одним пространством имен и разными схемами XML. Excel создаст уникальную карты XML для каждого импортируемого файла данных XML.
Примечание: При импорте нескольких XML-файлов, в которых не определено пространство имен, считается, что они используют одно пространство имен.
