Формат XML, как создать из Excel

Обновлено: 05.07.2024

Если вы ранее создали карту XML, вы можете использовать ее для импорта данных XML в сопоставленные ячейки, но также существует несколько методов и команд для импорта данных XML без карты XML.

Если у вас есть карта XML, сделайте следующее, чтобы импортировать данные XML в сопоставленные ячейки:

На карте XML выберите одну из сопоставленных ячеек.

Нажмите "Разработчик" > "Импорт".

Если вы не видите вкладку "Разработчик", см. раздел Показать вкладку "Разработчик".

В диалоговом окне "Импорт XML" найдите и выберите файл данных XML (.xml), который вы хотите импортировать, и нажмите "Импорт".

Другие способы импорта XML-данных

Дополнительную информацию о проблемах см. в разделе Общие проблемы с импортом данных XML в конце этой статьи.

Импорт файла данных XML в виде таблицы XML

Нажмите "Разработчик" > "Импорт".

Если вы не видите вкладку "Разработчик", см. раздел Показать вкладку "Разработчик".

Если файл данных XML не ссылается на схему, Excel выводит схему из файла данных XML.

В диалоговом окне "Импорт данных" выполните одно из следующих действий:

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

Выберите таблицу XML на новом листе, чтобы импортировать содержимое файла в таблицу XML на новом листе, начиная с ячейки A1. Схема файла данных XML отображается в области задач "Источник XML".

Если файл данных XML не ссылается на схему, Excel выводит схему из файла данных XML.

Чтобы управлять поведением XML-данных (например, привязкой данных, форматом и макетом), нажмите «Свойства», чтобы открыть диалоговое окно свойств XML-карты. Например, существующие данные в сопоставленном диапазоне будут перезаписаны при импорте данных по умолчанию, но это можно изменить.

Импорт нескольких файлов данных XML

Выберите сопоставленную ячейку, чтобы импортировать несколько XML-файлов в один набор сопоставленных ячеек.

Если вы хотите импортировать несколько XML-файлов в несколько наборов сопоставленных ячеек, щелкните ячейку в любом месте листа, которая не сопоставлена.

Нажмите "Разработчик" > "Импорт".

Если вы не видите вкладку "Разработчик", см. раздел Показать вкладку "Разработчик".

В диалоговом окне "Импорт XML" найдите и выберите файл данных XML (.xml), который вы хотите импортировать.

Если файлы расположены рядом, нажмите клавишу Shift и щелкните первый и последний файлы в списке. Все данные из XML-файлов будут импортированы и добавлены к сопоставленным ячейкам.

Если файлы не являются смежными, нажмите и удерживайте клавишу Ctrl и щелкните каждый файл, который вы хотите импортировать, в списке.

Нажмите «Импорт».

Если вы выбрали файлы, которые не являются смежными, появится диалоговое окно Импорт .xml. Выберите карту XML, соответствующую файлу данных XML, который вы импортируете для каждого файла.

Чтобы использовать одну карту для всех выбранных файлов, которые еще не импортированы, выберите Использовать эту карту XML для всех выбранных файлов этой схемы.

Импорт нескольких файлов данных XML в качестве внешних данных

Чтобы импортировать несколько XML-файлов, использующих одно и то же пространство имен, но разные XML-схемы, можно использовать команду «Импорт данных из XML». Excel создает уникальную карту XML для каждого импортируемого файла данных XML.

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

Если вы используете Excel с подпиской на Microsoft 365, нажмите Данные > Получить данные > Из файла > Из XML.

Если вы используете Excel 2016 или более раннюю версию, нажмите Данные > Из других источников > Импорт данных XML.

Перейдите к диску, папке или местоположению в Интернете, где находится файл данных XML (.xml), который вы хотите импортировать.

Выберите файл и нажмите «Открыть».

В диалоговом окне "Импорт данных" выполните одно из следующих действий:

Таблица XML на существующем рабочем листе Содержимое файла импортируется в новую таблицу XML на новом рабочем листе. Если файл данных XML не ссылается на схему, Excel выводит схему из файла данных XML.

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

Новый рабочий лист Excel добавит новый рабочий лист в вашу рабочую книгу и автоматически поместит XML-данные в левый верхний угол нового рабочего листа. Если файл данных XML не ссылается на схему, Excel выводит схему из файла данных XML.

Чтобы управлять поведением XML-данных, например привязкой данных, форматом и макетом, нажмите «Свойства», чтобы открыть диалоговое окно свойств XML-карты.Например, существующие данные в сопоставленном диапазоне перезаписываются при импорте данных по умолчанию, но это можно изменить.

Откройте файл данных XML, чтобы импортировать его данные

Нажмите «Файл» > «Открыть».

Если вы используете Excel 2007, нажмите кнопку Microsoft Office > Открыть.

В диалоговом окне "Открыть" выберите диск, папку или место в Интернете, где находится файл, который вы хотите открыть.

Выберите файл и нажмите «Открыть».

Если появится диалоговое окно «Импорт XML», открытый файл ссылается на одну или несколько таблиц стилей Extensible Stylesheet Language Transformation (XSLT), поэтому вы можете выбрать один из следующих вариантов:

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

Откройте файл со следующей примененной таблицей стилей (выберите одну). Выберите таблицу стилей, которую вы хотите применить, и нажмите OK. Данные XML форматируются в соответствии с выбранной вами таблицей стилей.

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

Если появится диалоговое окно Open XML, XML-файл не содержит ссылок на таблицы стилей XSLT. Чтобы открыть файл, выберите один из следующих вариантов:

Нажмите Как таблица XML, чтобы создать таблицу XML в новой книге.

Содержимое файла импортируется в таблицу XML. Если файл данных XML не ссылается на схему, Excel выводит схему из файла данных XML.

Нажмите Как книга только для чтения.

Данные XML импортируются в двумерную таблицу со строками и столбцами, в которой XML-теги отображаются в виде заголовков столбцов, а данные — в строках под заголовками столбцов. Первый элемент (корневой узел) используется как заголовок и отображается в указанной ячейке. Остальные теги отсортированы по алфавиту во второй строке. В этом случае Excel не выводит схему, и вы не можете использовать карту XML.

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

Нажмите "Использовать область задач источника XML".

Схема файла данных XML отображается в области задач "Источник XML". Затем вы можете перетащить элементы схемы на рабочий лист, чтобы сопоставить эти элементы с рабочим листом.

Если файл данных XML не ссылается на схему, Excel выводит схему из файла данных XML.

Распространенные проблемы с импортом XML-данных

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

Что произошло

Ошибка проверки схемы

Когда вы щелкнули Проверить данные по схеме для импорта и экспорта в диалоговом окне Свойства карты XML, данные были импортированы, но не были проверены на соответствие указанной карте XML.

Некоторые данные были импортированы в виде текста

Некоторые или все импортированные вами данные были преобразованы из заявленного типа данных в текст. Чтобы использовать эти данные в расчетах, вам необходимо преобразовать данные из текста в числа или даты. Например, значение даты, преобразованное в текст, не будет работать должным образом в функции YEAR, пока вы не преобразуете его в тип данных Date. Excel преобразует данные в текст, когда:

Данные представлены в формате, который Excel не поддерживает.

Данные несовместимы с внутренним представлением Excel типа данных XSD. Чтобы решить эту проблему, проверьте каждое объявление типа данных, чтобы убедиться, что ваши данные XML допустимы в соответствии со схемой XML.

Ошибка синтаксического анализа XML

Синтаксический анализатор XML не может открыть указанный файл XML. Убедитесь, что в файле XML нет синтаксических ошибок и что XML правильно сформирован.

Не удается найти карту XML, соответствующую данным

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

Размер XML-таблицы нельзя изменить для размещения данных

Вы пытаетесь добавить строки, импортируя или добавляя данные в таблицу XML, но в таблице нет места для расширения. Таблица XML может расширяться только снизу вниз.Например, может быть объект, такой как изображение или другая таблица, непосредственно под таблицей XML, который препятствует расширению таблицы XML. Или для расширения XML-таблицы будет превышено ограничение на количество строк Excel, равное 1 048 576. Чтобы решить эту проблему, измените порядок таблиц и объектов на листе, чтобы таблица XML расширялась снизу вниз.

Указанный файл XML не ссылается на схему

Файл XML, который вы пытаетесь открыть, не относится к схеме XML. Для работы с XML-данными в файле Excel требуется схема, основанная на содержимом XML-файла. Если эта схема неверна или недостаточна, удалите ее из книги. Затем создайте файл схемы XML и отредактируйте файл данных XML, чтобы файл данных XML ссылался на схему. Дополнительные сведения см. в разделе Сопоставление элементов XML с ячейками на карте XML.

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

Выполните следующие действия, чтобы удалить схему, созданную Excel, из вашей книги:

Нажмите "Разработчик" > "Источник".

Если вы не видите вкладку "Разработчик", см. раздел Показать вкладку "Разработчик".

На панели задач "Источник XML" нажмите "Карты XML".

В диалоговом окне "Карты XML" щелкните созданную карту XML в формате Excel и нажмите "Удалить".

Импорт нескольких XML-файлов, использующих одно и то же пространство имен, но разные схемы, не работает должным образом

При работе с несколькими файлами данных XML и схемами XML вы обычно создаете карту XML для каждой схемы, сопоставляете нужные элементы, а затем импортируете каждый файл данных XML в соответствующую карту XML. Используя команду «Импорт» для открытия нескольких XML-файлов с одним и тем же пространством имен, вы можете использовать только одну XML-схему. Когда вы используете эту команду для открытия нескольких XML-файлов, использующих одно и то же пространство имен, но разные схемы, вы можете получить неожиданные результаты. Например, данные могут быть перезаписаны или файлы не будут открываться.

Чтобы импортировать несколько XML-файлов, использующих одно и то же пространство имен, но разные XML-схемы, попробуйте использовать команду «Импорт данных из XML» (щелкните «Данные» > «Из других источников»). Эта команда позволяет нескольким файлам XML с одним и тем же пространством имен использовать несколько схем XML. Excel создает уникальную карту XML для каждого файла данных XML, который вы хотите импортировать.

Показать вкладку "Разработчик"

Если вы не видите вкладку "Разработчик", сделайте следующее, чтобы отобразить ее:

В Excel 2013 и Excel 2010:

Нажмите "Файл" > "Параметры".

Нажмите на категорию "Настроить ленту".

В разделе "Основные вкладки" установите флажок "Разработчик" и нажмите "ОК".

Нажмите кнопку Microsoft Office > Параметры Excel.

Нажмите на категорию «Популярные».

В разделе "Верхние параметры работы с Excel" установите флажок "Показать вкладку "Разработчик"" на ленте и нажмите "ОК".

Таблицы Excel отлично подходят для хранения больших объемов данных, которыми невозможно управлять в другом месте. Но что происходит, когда вам нужно передать эти данные куда-то еще? Что, если у вас есть сотни или тысячи ячеек, переполненных информацией? Для повторного ввода вручную потребуется много данных, особенно если они нужны в другом формате.

Расширяемый язык разметки, или XML, – это простой в использовании, чтении и форматировании язык разметки документов. Его можно использовать для хранения данных в файлах Excel и для передачи их в другие приложения. Это широко используемый формат в Интернете и других приложениях.

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

Последнее обновление: сентябрь 2021 г.

Получите сертификат эксперта! Уровни 6–10 для Microsoft Excel 2010, 2013, 2016 и Excel 2019. Охватывает 77–728, 77–427 и 77–428. | Филипп Бертон

Шаг 1. Создайте электронную таблицу Excel

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

Шаг 2. Создайте XML-схему

Первая строка — стандартная. Он определяет, что это XML-файл с использованием кодировки UTF-8 и XML версии 1.0.

Вторая строка указывает, что данные относятся к «данным учащегося». Если вы упростите эту строку и последнюю строку, у вас будет:

Первая строка выше открывает элемент, а последняя строка, содержащая символ «/», завершает этот элемент. Важно, чтобы у вас был закрывающий тег для каждого открывающего тега и чтобы они были в правильном порядке. Все, что находится между этими линиями, является частью этого элемента. Другими словами, это «студенческие данные». Поскольку это первый элемент, его также называют «корневым элементом».

Приведенные выше «данные учащегося» включают в себя элемент «record», содержащий три элемента данных: TAGNAME, TAGNAME2 и TAGNAME3.

Вы можете изменить название тегов на имена ваших столбцов. Они не обязательно должны быть точными именами столбцов, главное, чтобы они были в правильном порядке. Вам нужно заполнить только несколько примеров вручную, но мы можем попросить Excel сделать все остальное за нас позже.

Вот пример XML-схемы для нашей электронной таблицы Excel:

Как видите, элементы данных превратились в и в соответствии со столбцами в нашем наборе данных. Вам не нужно делать отступ между строками, но это улучшает читабельность.

Также обратите внимание на теги, содержащие содержимое каждого тега. Он должен быть переименован в соответствии с названием вашей электронной таблицы Excel. В этом примере наша рабочая книга Excel называется student-data.xlsx, поэтому наш тег корневого элемента называется .

Не забудьте назвать свою XML-схему с помощью .xml в конце. В этом примере наша схема называется просто schema.xml, если вы хотите продолжить.

Если вы хотите узнать больше о документах XML, о том, как они работают и для чего они полезны, ознакомьтесь с этим курсом «Введение в XML». Вы также можете начать работу с этим учебным курсом по XML для начинающих.

Шаг 3. Включите вкладку "Разработчик"

Если вкладка "Разработчик" недоступна, ее необходимо включить. Нажмите «Файл» — «Параметры», а затем на страницу «Настроить ленту». В столбце справа находится список вкладок под названием «Главные вкладки» с галочками.

Убедитесь, что установлен флажок рядом с вкладкой "Разработчик", затем нажмите "ОК". Вкладка "Разработчик" теперь должна быть видна в главном окне Excel.

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

Если вам нужны более продвинутые навыки, то почему бы не пройти курсы повышения квалификации по Excel, если вам нужен опыт работы с более сложными элементами Excel.

Шаг 4. Откройте панель "Источник XML"

Теперь, когда вы открыли вкладку "Разработчик" и нажали на нее, вы должны увидеть ряд разделов с названиями "Код", "Надстройки", "Элементы управления" и "XML".

В разделе XML нажмите большую кнопку с надписью Источник. В правой части экрана откроется панель задач «Источник XML», где у вас будет возможность управлять картами XML. Это то, что мы хотим сделать дальше.

Шаг 5. Добавьте XML-карту

На панели задач "Источник XML" выберите параметр "Карты XML" в самом нижнем правом углу. Появится диалоговое окно. Нажмите «Добавить», чтобы добавить новую XML-схему, найдите файл schema.xml в браузере и нажмите «ОК».

После добавления XML-файла снова нажмите кнопку "ОК".

Компьютер может выдать вам предупреждение — это нормально.

Нажмите "ОК" и вернитесь на панель задач "Источник XML".

Шаг 6. Перетащите XML-элементы в электронную таблицу

Каждый из ваших столбцов должен отображаться как новый элемент в дереве записей, созданном на панели задач "Источник XML". Перетащите каждый элемент по отдельности в соответствующий столбец, начиная с строки 1.

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

Шаг 7. Экспорт данных XML

После того, как вы закончите преобразование каждого столбца в XML-элемент, вы можете либо нажать «Разработчик» — «Экспорт», либо перейти в «Файл» — «Сохранить как» и изменить тип файла на «Данные XML (*.xml)».< /p>

Сохраните XML-файл, и Excel экспортирует его в XML. Он может предупредить вас, что это приведет к потере функций, отличных от XML, таких как форматирование, изображения и объекты. Опять же, все в порядке.

Затем вы можете открыть его в Блокноте, чтобы увидеть результаты. Перейдите в меню «Файл» — «Открыть» и измените тип файла с «Текстовые файлы (*.txt)» на «Все файлы»:

Excel может автоматически форматировать всю эту информацию, поэтому вам не нужно этого делать.

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

Однако это был очень простой пример, и в будущем вы можете столкнуться с более сложными сценариями.Если это произойдет, это поможет, если у вас есть продвинутые навыки работы с Excel. Если вы хотите узнать, как справляться с этими сценариями, ознакомьтесь с этим курсом по сводным таблицам, Power Query, Power Pivot и DAX.

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

Если вы заинтересованы в более продвинутых навыках работы с Excel, давайте перейдем к изучению того, как использовать SQL в Excel.

Часто задаваемые вопросы

Что такое формат Excel XML?

Excel может экспортировать данные в формат XML (или расширяемый язык разметки). Он основан на схеме XML, которая определяет структуру файла. Вы можете создать его в Блокноте. Чтобы преобразовать данные, перейдите на вкладку «Разработчик» — «Источник». Затем вы можете загрузить свою XML-схему и сопоставить ее с данными. Затем вы можете перейти в «Разработчик» — «Экспорт» или «Файл» — «Сохранить как — файл XML».

Как открыть файл XML в Excel?

Чтобы открыть XML-файл в Excel, выберите «Файл» — «Открыть». На ПК вы должны изменить тип файла с «Все файлы Excel (*.xl*)» на «XML-файл (*.xml)». Затем вы должны перейти к тому месту, где хранится файл, щелкнуть по нему и нажать «ОК». Ваш файл будет открыт в Excel.

Как просмотреть файл XML?

Вы можете просмотреть XML-файл в Блокноте или Excel. Чтобы просмотреть его в Блокноте, перейдите в «Файл» — «Открыть» и измените тип файла с «Текстовые документы (*.txt)» на «Все файлы (*.*)». Чтобы просмотреть его в Excel, выберите «Файл» — «Открыть» и измените тип файла на «XML-файлы (*.xml)».

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

Перед экспортом XML-данных вам необходимо активировать вкладку "Разработчик" на ленте. См. раздел Показать вкладку "Разработчик".

Экспорт XML-данных (не более 65 536 строк)

Нажмите "Разработчик" > "Экспорт".

Если вы не видите вкладку "Разработчик", см. раздел Показать вкладку "Разработчик".

Если появится небольшое диалоговое окно «Экспорт XML», щелкните XML-карту, которую хотите использовать, и нажмите «ОК».

Это диалоговое окно появляется только в том случае, если XML-таблица не выбрана и в книге есть более одной XML-карты.

В большом диалоговом окне "Экспорт XML" в поле "Имя файла" введите имя файла данных XML.

Нажмите «Экспорт».

Примечание. Если заголовки или метки вашего рабочего листа отличаются от имен элементов XML на карте XML, Excel будет использовать имена элементов XML при экспорте или сохранении XML-данных из рабочего листа.

Экспорт XML-данных (более 65 536 строк)

Вычтите 65 537 из общего количества строк в вашем файле. Это даст вам x количество строк.

Удалить x строк с начала рабочего листа Excel.

Экспортируйте рабочий лист как файл данных XML (процедуру см. в предыдущем разделе).

Нажмите «Закрыть», но не сохраняйте лист. Затем снова откройте рабочий лист Excel.

Удалите все после общего x, а затем экспортируйте в виде файла данных XML (процедуру см. в предыдущем разделе).

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

Для обратной совместимости с более ранними функциями XML вы можете сохранить файл в файл данных XML (.xml) вместо использования команды «Экспорт».

Нажмите Ctrl+S, чтобы сохранить файл.

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

Нажмите «Файл» > «Сохранить как» и выберите место, где вы хотите сохранить файл.

Только в Excel 2007: нажмите кнопку Microsoft Office , наведите указатель мыши на стрелку рядом с кнопкой "Сохранить как" и выберите "Другие форматы".

В поле Имя файла введите имя файла данных XML.

В списке "Тип файла" выберите "Данные XML" и нажмите "Сохранить".

Если вы получите сообщение о том, что сохранение файла в виде XML-данных может привести к потере функций, нажмите «Продолжить».

В большом диалоговом окне "Экспорт XML" в поле "Имя файла" введите имя файла данных XML.

Нажмите «Экспорт».

При экспорте данных XML вы можете получить сообщения, подобные приведенным ниже.

Это сообщение может быть вызвано несколькими причинами:

Карта XML, связанная с этой таблицей XML, содержит один или несколько обязательных элементов, которые не сопоставлены с таблицей XML.

В иерархическом списке элементов на панели задач источника XML необходимые элементы обозначены красной звездочкой в правом верхнем углу значка слева от каждого элемента. Чтобы сопоставить требуемый элемент, перетащите его в нужное место на листе.

Элемент является рекурсивной структурой.

Распространенным примером рекурсивной структуры является иерархия сотрудников и менеджеров, в которой одни и те же элементы XML вложены на нескольких уровнях. Хотя вы, возможно, сопоставили все элементы в области задач "Источник XML", Excel не поддерживает рекурсивные структуры более чем на один уровень в глубину и, следовательно, не может сопоставить все элементы.

Таблица XML содержит смешанный контент.

Смешанное содержимое возникает, когда у элемента есть дочерний элемент и простой текст вне дочернего элемента. Одним из распространенных случаев является использование тегов форматирования (например, тегов жирного шрифта) для разметки данных внутри элемента. Хотя дочерний элемент (если его поддерживает Excel) может отображаться, текстовое содержимое будет потеряно при импорте данных, недоступно при экспорте данных и, следовательно, не может быть передано туда и обратно.

Карта XML не может быть экспортирована, если связь сопоставленного элемента с другими элементами не может быть сохранена. Эта связь не может быть сохранена по следующим причинам:

Определение схемы отображаемого элемента содержится в последовательности со следующими атрибутами:

Атрибут maxoccurs не равен 1.

Последовательность имеет более одного определенного прямого дочернего элемента или имеет другой компоновщик в качестве прямого дочернего элемента.

Неповторяющиеся одноуровневые элементы с одним и тем же повторяющимся родительским элементом сопоставляются с разными XML-таблицами.

Несколько повторяющихся элементов сопоставляются с одной и той же XML-таблицей, и повторение не определяется элементом-предком.

Дочерние элементы от разных родителей сопоставляются с одной и той же XML-таблицей.

Кроме того, карту XML нельзя экспортировать, если она имеет одну из следующих конструкций схемы XML:

Список списков У одного списка элементов есть второй список элементов.

Денормализованные данные В XML-таблице есть элемент, для которого в схеме определено однократное появление (для атрибута maxoccurs установлено значение 1). Когда вы добавляете такой элемент в таблицу XML, Excel заполняет столбец таблицы несколькими экземплярами элемента.

Вариант Сопоставленный элемент является частью конструкции схемы.

Excel не может сохранить вашу книгу в файле данных XML, пока вы не создадите один или несколько сопоставленных диапазонов.

Если вы добавили карту XML в книгу, выполните следующие действия, чтобы сопоставить элементы XML с таблицей:

Нажмите "Разработчик" > "Источник".

Если вы не видите вкладку "Разработчик", см. раздел Показать вкладку "Разработчик".

На панели задач "Источник XML" выберите элементы, которые нужно сопоставить с рабочим листом.

Чтобы выбрать несмежные элементы, щелкните один элемент, удерживая нажатой клавишу CTRL, а затем щелкните каждый элемент.

Перетащите выбранные элементы в нужное место на листе.

Если вы еще не добавили карту XML в свою книгу или ваши данные не находятся в таблице XML, сохраните книгу в формате файла книги Excel с поддержкой макросов (.xlsm).

Нужна дополнительная помощь?

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

Microsoft Excel позволяет легко импортировать данные расширяемого языка разметки (XML), созданные из других баз данных и приложений, сопоставлять элементы XML из схемы XML с ячейками рабочего листа, а также экспортировать измененные данные XML для взаимодействия с другими базами данных и Приложения. Думайте об этих функциях XML как о преобразовании Office Excel в генератор файлов данных XML со знакомым пользовательским интерфейсом.

В этой статье

Зачем использовать XML в Excel?

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

XML-файлы данных и схемы

Excel работает в основном с двумя типами XML-файлов:

Файлы данных XML (.xml), которые содержат пользовательские теги и структурированные данные.

Файлы схемы (.xsd), которые содержат теги схемы, обеспечивающие соблюдение правил, таких как тип данных и проверка.

Стандарт XML также определяет преобразование расширяемого языка таблиц стилей (XSLT) (.xslt), которые используются для применения стилей и преобразования данных XML в различные форматы представления. Эти преобразования можно применять перед импортом XML-файлов в Excel и после экспорта XML-файлов из Excel. Если файлы XSLT связаны с файлами данных XML, которые вы импортируете в Excel, у вас есть возможность применять или не применять форматирование до того, как данные будут добавлены на лист, но только при открытии файла XML с помощью команды «Открыть» из в Экселе. Выберите тип файла 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-данные в сопоставленные ячейки и из них.

Добавляя файл схемы XML (.xsd) в книгу, вы создаете карту XML. Как правило, 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 с типом данных определения схемы XML (XSD), который Excel интерпретирует как число, дату или время.

Работа с повторяющимися ячейками в XML-таблицах

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

Таблица XML создается, когда вы:

Используйте команду «Импорт» (в группе XML на вкладке «Разработчик»), чтобы импортировать файл данных XML.

Используйте команду "Открыть" в Excel, чтобы открыть файл данных XML, а затем выберите "Как таблицу XML" в диалоговом окне "Открыть XML".

Используйте команду «Импорт данных из XML» (от кнопки «Из других источников» в группе «Получить внешние данные» на вкладке «Данные»), чтобы импортировать файл данных XML, а затем выберите «Таблица XML на существующем листе» или «Новый лист в диалоговое окно "Импорт данных".

Перетащите один или несколько повторяющихся элементов из области задач "Источник XML" на лист.

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

Два параметра под кнопкой "Параметры" на панели задач "Источник XML" полезны при работе с таблицами XML:

Автоматически объединять элементы при сопоставлении Если этот флажок установлен, Excel создает одну XML-таблицу из нескольких полей, когда они перетаскиваются на рабочий лист. Этот параметр работает до тех пор, пока несколько полей перетаскиваются в одну и ту же строку, одно рядом с другим. Если этот флажок снят, каждый элемент отображается как отдельная таблица XML.

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

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

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

Вы не можете транспонировать XML-таблицу, чтобы новые записи добавлялись справа.

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

Вопросы безопасности карты XML

Карта XML и информация об источнике данных сохраняются вместе с рабочей книгой Excel, а не с конкретным рабочим листом. Злоумышленник может просмотреть эту информацию о карте с помощью макроса Microsoft Visual Basic для приложений (VBA). Кроме того, если вы сохраните свою книгу как файл формата Excel Office Open XML с поддержкой макросов, эту информацию о карте можно будет просмотреть в Блокноте Microsoft или другой программе редактирования текста.

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

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

Импорт XML-данных

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

Вы можете отобразить диалоговое окно "Свойства карты XML" (щелкните "Свойства карты" в группе XML на вкладке "Разработчик"), в котором есть три параметра, выбранных по умолчанию, которые можно установить или снять для управления поведением XML. привязка данных:

Проверять данные по схеме для импорта и экспорта Указывает, проверяет ли Excel данные по карте XML при импорте данных. Выберите этот параметр, если хотите убедиться, что импортируемые XML-данные соответствуют схеме XML.

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

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

При импорте XML-данных может потребоваться перезаписать некоторые сопоставленные ячейки, но не другие. Например, некоторые сопоставленные ячейки могут содержать формулы, и вы не хотите перезаписывать формулу при импорте XML-файла. Вы можете воспользоваться двумя подходами:

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

Создайте две карты XML из одной и той же схемы XML. Используйте одну карту XML для импорта данных XML. В этой XML-карте «Импорт» не сопоставляйте элементы с ячейками, содержащими формулы или другие данные, которые вы не хотите перезаписывать. Используйте другую карту XML для экспорта данных. На этой XML-карте «Экспорт» сопоставьте элементы, которые вы хотите экспортировать, в XML-файл.

Примечание. Возможность импортировать XML-данные из веб-службы с помощью файла подключения службы извлечения данных (.uxdc) для подключения к источнику данных больше не поддерживается в более поздних версиях, чем Excel 2003, через пользовательский интерфейс. Если вы откроете книгу, созданную в Excel 2003, вы по-прежнему сможете просматривать данные, но не сможете редактировать или обновлять исходные данные.

Работа с предполагаемой схемой

Если вы импортируете XML-данные без предварительного добавления соответствующей XML-схемы для создания XML-карты, Excel попытается вывести для вас схему на основе тегов, определенных в XML-файле данных. Выведенная схема хранится вместе с книгой, а выведенная схема позволяет работать с данными XML, если файл схемы XML не связан с книгой.

При работе с импортированными XML-данными с предполагаемой схемой можно также настроить область задач "Источник XML". Выберите параметр «Предварительный просмотр данных в области задач» на кнопке «Параметры», чтобы отобразить первую строку данных в качестве примера данных в списке элементов, если вы импортировали данные XML, связанные с картой XML, в текущем сеансе Excel.

Вы не можете экспортировать предполагаемую схему Excel в виде отдельного файла данных схемы XML (.xsd). Хотя существуют редакторы схемы XML и другие методы создания файла схемы XML, вы можете не иметь к ним удобного доступа или не знать, как их использовать. В качестве альтернативы можно использовать надстройку Excel 2003 XML Tools версии 1.1, которая может создать файл схемы из карты XML. Дополнительные сведения см. в разделе Использование надстройки Excel 2003 XML Tools версии 1.1.

Экспорт XML-данных

Вы экспортируете XML-данные, экспортируя содержимое сопоставленных ячеек на листе. При экспорте данных Excel применяет следующие правила, определяющие, какие данные сохранять и как их сохранять:

Пустые элементы не создаются, если для необязательного элемента существуют пустые ячейки, но пустые элементы создаются, если существуют пустые ячейки для обязательного элемента.

Для записи данных используется кодировка Unicode Transformation Format-8 (UTF-8).

Все пространства имен определяются в корневом XML-элементе.

Excel перезаписывает существующие префиксы пространств имен. Пространству имен по умолчанию назначается префикс ns0. Последующие пространства имен обозначаются от ns1, ns2 до ns, где — количество пространств имен, записанных в XML-файл.

Узлы комментариев не сохраняются.

Можно отобразить диалоговое окно "Свойства карты XML" (нажмите "Свойства карты" в группе "XML" на вкладке "Разработчик").), а затем используйте параметр Проверять данные по схеме для импорта и экспорта (активен по умолчанию), чтобы указать, проверяет ли Excel данные по карте XML при экспорте данных. Выберите этот параметр, если хотите убедиться, что экспортируемые XML-данные соответствуют схеме XML.

Использование файла формата Office Open XML с поддержкой макросов

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

Нужна дополнительная помощь?

Читайте также: