Microsoft Excel позволяет легко импортировать данные XML из других баз данных и приложений, сопоставлять XML-элементы из схемы XML с ячейками листа и экспортировать измененные XML-данные для взаимодействия с другими базами данных и приложениями. Эти функциональные возможности позволяют использовать Office Excel как генератор файлов данных XML со знакомым пользовательским интерфейсом.
В этой статье
-
- Использование области задач источника XML
- Типы элементов и их значки
- Работа с ячейками с точечным сопоставлением
- Работа с повторяющимися ячейками в XML-таблицах
- Обеспечение безопасности карт XML
- Импорт данных XML
- Работа с полученной схемой
- Экспорт данных XML
- Использование файла Excel формата Office Open XML с поддержкой макросов
Причины использования XML в Excel
XML является технологией, разработанной для управления структурированными данными и отображения этих данных в виде удобочитаемого текстового файла. Язык XML соответствует отраслевым стандартам и может быть обработан многими базами данных и приложениями. С помощью XML многие разработчики могут создавать собственные настроенные теги, структуры данных и схемы. В целом XML существенно облегчает определение, передачу, проверку и интерпретацию данных в различных базах данных, приложениях и организациях.
Данные XML и файлы схемы
Excel работает преимущественно с двумя типами XML-файлов:
- файлами данных XML (XML), которые содержат настраиваемые теги и структурированные данные;
- Файлы схемы (XSD), которые содержат теги схемы, которые применяют правила, такие как тип данных и проверка.
В стандарте XML также определяются файлы преобразования XSLT, которые используются для применения стилей и преобразования данных XML в другие форматы. Вы можете использовать эти преобразования перед импортом XML-файлов в Excel и после экспорта XML-файлов из Excel. Если XSLT-файлы связаны с XML-файлами данных, импортированными в Excel, вы можете применить форматирование перед добавлением данных на лист, только открыв XML-файл в Excel с помощью команды Открыть. Прежде чем нажать кнопку Открыть, выберите тип файлов XML (*.XML), чтобы просмотреть XML-файлы в папке.
Ключевые сценарии XML и Excel
С помощью XML и Excel вы можете управлять книгами и данными такими способами, которые ранее были невозможны или представляли трудности. Используя карты XML, вы можете легко добавлять, идентифицировать и извлекать определенные фрагменты бизнес-данных из документов Excel. Например, счет-фактура, содержащий имя и адрес покупателя, или отчет с финансовыми показателями за предыдущий квартал больше не будут статичными отчетами. Эти сведения можно с легкостью импортировать из баз данных и приложений, изменять, а также экспортировать в те или иные базы данных и приложения.
Вот ключевые сценарии, для работы с которыми предназначены возможности XML:
- расширение функциональных возможностей существующих шаблонов Excel путем сопоставления XML-элементов с существующими ячейками. Это облегчает удаление и добавление данных XML в шаблонах, не изменяя сами шаблоны;
- использование данных XML в качестве входных данных для существующих моделей вычислений путем сопоставления XML-элементов с существующими листами;
- импорт файлов данных XML в новую книгу;
- импорт данных XML из веб-службы на лист Excel;
- экспорт данных в сопоставленных ячейках в файлы данных XML независимо от других данных в книге.
Основной процесс использования данных XML в Excel
На следующей схеме показано, как взаимодействуют различные файлы и операции при использовании XML в Excel. Фактически этот процесс состоит из пяти этапов.
Добавление XML-файла схемы (XSD) в книгу
Сопоставление элементов схемы XML с отдельными ячейками или XML-таблицами
Импорт XML-файла данных (.xml) и привязка XML-элементов к сопоставленным ячейкам
Ввод данных, перемещение сопоставленных ячеек и использование функций Excel с сохранением структуры и определений XML
Экспорт измененных данных из сопоставленных ячеек в XML-файл данных
Работа с картами XML
Вы можете создать или открыть книгу в Excel, присоединить к ней файл схемы XML (XSD), а затем использовать область задач Источник XML для сопоставления XML-элементов схемы с отдельными ячейками или таблицами. Выполнив такое сопоставление, вы можете импортировать и экспортировать данные XML в ячейки таблицы и из них соответственно.
При добавлении файла схемы XML (XSD) в книгу создается карта XML. Как правило, карты XML используются для создания сопоставленных ячеек и управления взаимосвязью между такими ячейками и отдельными элементами схемы XML. Кроме того, эти карты используются для привязки содержимого сопоставленных ячеек к элементам схемы при импорте или экспорте файлов данных XML (XML).
Вы можете создавать сопоставленные ячейки двух видов: ячейки с точечным сопоставлением и повторяющиеся ячейки (отображаемые в виде XML-таблиц). Для более гибкой разработки книги вы можете перемещать сопоставленные ячейки в любое место на листе и в любой последовательности, даже отличающейся от последовательности, заданной в схеме XML. Вы также можете выбрать, какие элементы следует сопоставлять.
Важно знать такие правила использования карт XML:
- книга может содержать одну или несколько карт XML;
- одновременно можно сопоставить только один элемент с одним расположением в книге;
- каждая карта XML независима от других, даже если несколько карт XML в одной книге относятся к одной и той же схеме;
- карта XML может содержать только один корневой элемент. При добавлении схемы, определяющей более одного корневого элемента, появится запрос на выбор корневого элемента, который будет использоваться в новой карте XML.
Использование области задач источника XML
Используйте область задач Источник XML для управления картами XML. Чтобы ее открыть, на вкладке Разработчик в группе XML нажмите Источник. На следующей схеме показаны основные функциональные возможности этой области задач.
1. Список КАРТ XML, добавленных в книгу
2. Отображает иерархический список XML-элементов в текущей схеме XML
3. Задает параметры при работе с областью задач "Источник XML " и XML-данными, например способ предварительного просмотра заголовков данных и элементов управления
4. Открывает диалоговое окно "Карты XML ", с помощью которого можно добавлять, удалять или переименовывать XML-карты.
5. Проверяет, можно ли экспортировать XML-данные с помощью текущей карты XML.
Типы элементов и их значки
В следующей таблице приведены все типы XML-элементов, с которыми может работать Excel, и значки, используемые для представления каждого типа элементов.
| Тип элемента | Значок |
|---|---|
| Родительский элемент |
|
| Обязательный родительский элемент |
|
| Повторяющийся родительский элемент |
|
| Обязательный повторяющийся родительский элемент |
|
| Дочерний элемент |
|
| Обязательный дочерний элемент |
|
| Повторяющийся дочерний элемент |
|
| Обязательный повторяющийся дочерний элемент |
|
| Атрибут |
|
| Обязательный атрибут |
|
| Простое содержимое в сложной структуре |
|
| Обязательное простое содержимое в сложной структуре |
|
Работа с ячейками с точечным сопоставлением
Ячейка с точечным сопоставлением — это ячейка, сопоставленная с неповторяющимся XML-элементом. Такую ячейку вы можете создать, переместив неповторяющийся XML-элемент из области задач Источник XML в отдельную ячейку листа.
Название перемещаемого на лист неповторяющегося XML-элемента вы можете назначить заголовком сверху или слева от ячейки с точечным сопоставлением при помощи смарт-тега. В качестве заголовка также можно использовать текущее значение ячейки.
Кроме того, в ячейке с точечным сопоставлением вы можете использовать формулу, если ячейка сопоставлена с XML-элементом, который имеет тип данных определения схемы XML (XSD), интерпретируемый Excel как число, дата или время.
Работа с повторяющимися ячейками в XML-таблицах
По внешнему виду и функциональным возможностям XML-таблицы подобны таблицам Excel. XML-таблицы являются таблицами Excel, сопоставленными с одним или несколькими повторяющимися XML-элементами. Каждый столбец XML-таблицы соответствует XML-элементу.
XML-таблица создается в следующих случаях:
- Используйте команду Импорт (в группе XML на вкладке Разработчик ), чтобы импортировать XML-файл данных.
- Используйте команду Открыть из Excel, чтобы открыть XML-файл данных, а затем выберите В качестве XML-таблицы в диалоговом окне Открыть XML .
- Используйте команду From XML Data Import (с помощью кнопки Из других источников в группе Получить внешние данные на вкладке Данные ), чтобы импортировать XML-файл данных, а затем выберите таблицу XML на существующем листе или Новый лист в диалоговом окне Импорт данных .
- при перетаскивании на лист одного или нескольких повторяющихся элементов из области задач Источник XML.
При создании XML-таблицы названия XML-элементов автоматически используются в качестве названий столбцов. Вы можете изменить эти названия. Но при экспорте данных из сопоставленных ячеек автоматически будут использоваться исходные названия XML-элементов.
При работе с XML-таблицами полезны два параметра, доступные с помощью кнопки Параметры в области задач Источник XML.
- Автоматическое слияние элементов при сопоставлении Если этот параметр выбран, Excel создает одну XML-таблицу из нескольких полей, так как они удаляются на лист. Этот параметр работает, пока поля перемещаются в одну строку в виде последовательного ряда. Если флажок снят, каждый элемент отображается в виде отдельной XML-таблицы.
- Мои данные имеют заголовки Если этот параметр выбран, существующие данные заголовков используются в качестве заголовков столбцов для повторяющихся элементов, сопоставленных с листом. Если флажок снят, названия XML-элементов используются в качестве названий столбцов.
С помощью XML-таблиц вы можете импортировать, экспортировать, сортировать, фильтровать и печатать данные, основанные на источнике данных XML. Но существуют некоторые ограничения относительно расположение XML-таблиц на листе.
- Xml-таблицы основаны на строках, что означает, что они растут из строки заголовка вниз. Нельзя добавлять новые записи над существующими строками.
- Невозможно транспонировать XML-таблицу так, чтобы новые записи добавлялись справа.
Вы можете использовать формулы в столбцах, сопоставленных с XML-элементами, которые имеют тип данных определения схемы XML (XSD), интерпретируемый Excel как число, дата или время. В XML-таблице, как и в таблице Excel, формулы копируются вниз по столбцу по мере добавления новых строк.
Обеспечение безопасности карт XML
Карта XML и содержащиеся в ней сведения об источнике данных сохраняются в книге Excel, а не на определенном листе. Злоумышленник может просмотреть эти данные карты с помощью макроса VBA (Microsoft Visual Basic для приложений). Более того, если книга сохранена в файле Excel формата Open XML с поддержкой макросов, данные карты можно просмотреть с помощью Блокнота или другого текстового редактора.
Если вы хотите по-прежнему использовать сведения о карте, но при этом скрыть конфиденциальные данные, вы можете удалить из книги определение источника данных схемы XML, не прекращая экспорт данных XML. Для этого снимите флажок Сохранять определение источника данных в книге в диалоговом окне Свойства карты XML, которое можно открыть, выбрав Свойства карты в группе XML на вкладке Разработчик.
Если удалить лист до удаления карты, сведения карты об источниках данных и, возможно, другие конфиденциальные сведения останутся в книге. При обновлении книги для удаления конфиденциальных сведений обязательно удалите карту XML до удаления листа, чтобы окончательно удалить данные карты из книги.
Работа с полученной схемой
При импорте данных XML без добавления соответствующей схемы XML для создания карты XML Excel пытается получить схему, основываясь на тегах, определенных в файле данных XML. Полученная схема хранится в книге и позволяет работать с данными XML, даже если файл схемы XML не связан с книгой.
При работе с импортированными данными XML, которые имеют полученную схему, вы можете настроить область задач Источник XML. Выберите параметр Предварительный просмотр данных в области задач на кнопке Параметры , чтобы отобразить первую строку данных в виде примера данных в списке элементов, если вы импортировали XML-данные, связанные с XML-картой в текущем сеансе Excel.
Полученную схему Excel невозможно экспортировать как отдельный файл данных схемы XML (XSD). Несмотря на наличие редакторов схем XML и других методов создания файлов схем XML, они могут оказаться недоступными или неудобными в работе.
Экспорт данных XML
Экспорт данных XML осуществляется путем экспорта содержимого сопоставленных ячеек листа. При экспорте данных в Excel применяются такие правила для определения данных и способов их сохранения:
- пустые элементы не создаются при наличии пустых ячеек для дополнительного элемента, но они создаются при наличии пустых ячеек для обязательного элемента;
- запись данных производится в кодировке UTF-8;
- все пространства имен определяются в корневом XML-элементе;
- Excel перезаписывает существующие префиксы пространства имен. Пространству имен по умолчанию назначается префикс ns0. Последовательные пространства имен обозначаются ns1, ns2 — ns<count> , где <count> — это количество пространств имен, записанных в XML-файл.
- узлы комментариев не сохраняются.
Вы можете отобразить диалоговое окно Свойства карты XML (щелкните Свойства карты в группе XML на вкладке Разработчик ).), а затем использовать параметр Проверить данные по схеме для импорта и экспорта (активен по умолчанию), чтобы указать, проверяет ли Excel данные по xml-карте при экспорте данных. Выберите этот параметр, чтобы убедиться, что экспортируемые XML-данные соответствуют СХЕМе XML.
Использование файла Excel формата Office Open XML с поддержкой макросов
Книгу Excel можно сохранять в различных форматах файла, включая файлы Excel формата Office Open XML (XLSM) с поддержкой макросов. Excel содержит схему XML, определяющую содержимое книги Excel, в том числе теги XML, в которых хранятся все сведения книги (например, данные и свойства), а также всю структуру книги. Файл Excel формата Office XML с поддержкой макросов может применяться в пользовательских приложениях. Например, разработчики могут создать приложение, которое выполняет поиск данных в нескольких книгах, сохраненных в этом формате, и создает систему отчетов на основании полученных данных.
Дополнительные сведения
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществах.