Как преобразовать xml в excel

Обновлено: 05.07.2024

Таблицы 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. Ниже приведен пример электронной таблицы с шестью столбцами и семью строками данных. В столбцах разделены фамилии, имена, возраст, специальность (предмет), средний балл (GPA) и учебный год каждого учащегося, указанного в электронной таблице.

Шаг 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-файл в Excel, чтобы вы могли легко работать с данными и анализировать их.

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

Это руководство охватывает:

Что такое файл XML?

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

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

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

XML довольно широко распространен в качестве формата файлов для хранения и передачи данных через Интернет. Многие популярные форматы файлов, такие как Microsoft Office Open XML, LibreOffice, OpenDocument, XHTML и SVG, также используют формат файлов XML.

Большинство популярных веб-сайтов в Интернете имеют карту сайта в формате XML. Это файл, содержащий подробную информацию обо всех важных страницах и категориях веб-сайта. Вот пример карты сайта от Forbes.

Теперь давайте посмотрим, как преобразовать XML-файл в Excel с помощью Power Query.

Импорт файла XML в Excel

Если у вас уже есть XML-файл (либо загруженный в вашу систему, либо ссылка на него в Интернете), вы можете легко преобразовать его в данные в файле Excel.

Благодаря Power Query (теперь называется Get & Transform)

Импорт XML-файла, сохраненного в вашей системе

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

После того как XML-файл появится в вашей системе, выполните следующие действия, чтобы передать данные XML-файла в Excel:

Вышеуказанные шаги вставят новый рабочий лист в файл Excel и загрузят все данные из XML-файла в новый рабочий лист.

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

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

Импорт XML-файла в Excel с помощью веб-URL

В приведенном выше примере мы сначала загрузили XML-файл в систему, а затем импортировали данные в Excel.

Ниже приведены шаги по подключению мощного запроса к веб-URL, содержащему XML-данные, и импорту этих данных в Excel:

И снова, если данные в этом URL-адресе обновляются, просто обновите запрос, чтобы получить новые данные в Excel.

Итак, это два простых способа преобразования XML-файла в Excel.

Если в вашей системе есть XML-файл, вы можете легко импортировать данные с помощью Power Query. И если у вас есть веб-URL файла XML, вы также можете извлечь эти данные в Excel.

Онлайн-конвертер: конвертируйте большие XML-файлы (до 100 ГБ) в формат Excel (XLSX, XLS)

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

Вы можете помочь другим найти этот веб-сайт — поделитесь своим опытом!

Как работает конвертер XML в Excel?

Зачем конвертировать XML в Excel?

Чтобы открыть XML-файл со сложной структурой в Excel, намного проще преобразовать этот XML-файл в формат таблицы Excel и открыть его непосредственно в Microsoft Office Excel.

Обратите внимание: если вы хотите преобразовать файл Excel XML (формат Microsoft Office XML) в формат Excel XLSX, используйте наш онлайн-конвертер Excel XML в Excel XLSX.

Проверка XML

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

Синтаксический анализ XML-данных

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

Преобразование данных

Каждый тег XML представляет собой отдельный столбец таблицы.
Каждый XML-атрибут представляет отдельный столбец таблицы
Данные объединяются на основе XML-тегов верхнего уровня.

Сохранение в файл Excel

После того как данные преобразованы в табличное представление и объединены на основе правил преобразования XML в Excel, они сохраняются в файл Excel.

Формат файла XML

Формат XML

XML — это аббревиатура от Extensible Markup Language.
XML — это и машиночитаемый, и человекочитаемый формат, который можно редактировать в любом текстовом редакторе.

Теги XML

Теги XML должны иметь правильно определенные имена, начинающиеся с символа, а не с цифры.
Например,
допустимо:

Начальный и конечный теги

и закрывающий тег (или закрывающий тег):

Корневой элемент XML

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

..xml содержимое здесь

Экранирование специальных символов

< представляет " "
& представляет "&"
' представляет "'"
" представляет '"'

Кодирование XML

Кодировка XML определяется в XML-файле первой строкой:

Мы поддерживаем все форматы кодировки. Самая популярная кодировка — «UTF-8».

Информация о формате файла

Формат файла Excel

Формат Excel

Формат Excel — это формат файла, используемый в программе Microsoft Office Excel.
Файл в формате Excel имеет расширение: .XLS или .XLSX, в зависимости от версии Excel.
Наш конвертер XML в Excel сохраняет файл в формате XLSX, поддерживаемом Microsoft Office 2007 и более поздних версиях.

Ограничения Excel (XLSX — Excel 2007+)

Максимальное количество строк: 1 048 576 строк.
Максимальное количество столбцов: 16 384 столбца (последний столбец — "XFD").
Ширина столбца: 255 символов.
Максимальное количество символов в ячейке: 32 767 символов.
Максимальное количество гиперссылок на листе: 66 530 гиперссылок.

Ограничения Excel (XLS — Excel 97-2007)

Максимальное количество строк: 65 536 строк.
Максимальное количество столбцов: 256 столбцов (последний столбец – "IV").

Информация о формате файла

Примеры использования конвертера XML в Excel

Как конвертер XML в CSV используется для преобразования больших XML-файлов в формат CSV с дополнительной обработкой данных (фильтрация данных и сопоставление полей).

Обратное преобразование из формата Excel в формат XML

Есть возможность конвертировать данные обратно из формата Excel в формат XML.

Это можно сделать с помощью универсального конвертера Excel в XML или пользовательского конвертера.

(FAQ) Часто задаваемые вопросы, относящиеся к конвертеру XML в Excel

Как преобразовать XML-файл в Excel?

Используйте наш бесплатный онлайн-конвертер XML в Excel, чтобы преобразовать любой файл XML в формат Excel.
Пошаговая инструкция по использованию конвертера XML в Excel:

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

Сначала конвертируйте XML в формат Excel с помощью нашего бесплатного онлайн-конвертера XML в Excel.
Пошаговая инструкция по использованию конвертера XML в Excel:

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

Это отличная структура данных для чтения компьютером, но она немного менее доступна для чтения человеком.

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

Вы можете скачать примеры файлов, использованных в этом посте, здесь.

Видеоруководство

Примеры файлов XML

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

< /p>

Попробуйте сами: возьмите любой файл Excel и измените расширение с .xlsx на .zip, а затем изучите файлы в zip-папке. Вы увидите массу файлов XML. Вы можете открыть любой XML-файл в выбранном вами текстовом редакторе, чтобы просмотреть внутреннюю работу файла Excel.

Еще один распространенный пример можно найти в Интернете. На большинстве веб-сайтов есть файл sitemap.xml, который представляет собой XML-файл структуры ссылок веб-сайта. Это буквально карта веб-сайта, которую роботы, такие как Google, могут сканировать и индексировать.

< /p>

Создать XML-запрос

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

Мы можем быстро создать новый запрос, импортирующий файл XML в Excel.

Перейдите на вкладку "Данные" на ленте.
Выберите «Получить данные» в разделе «Получить и преобразовать данные».
Выбрать из файла.
Выбрать из XML.

< бр />

Откроется окно выбора файла, и мы сможем перейти к местоположению XML-файла и нажать кнопку «Импорт».

Развернуть поля в редакторе запросов

< бр />

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

Поскольку поля "Имя" и "Электронная почта" в XML-данных содержат подполя, мы видим таблицу в списке под заголовками столбцов в предварительном просмотре.Например, поле «Имя» содержит поля «Имя» и «Фамилия» для имени и фамилии человека. Если бы наши данные имели простую структуру без подполей, мы могли бы пропустить редактор запросов и нажать кнопку «Загрузить», чтобы напрямую загрузить данные в книгу.

Чтобы развернуть эти таблицы в редакторе расширенных запросов, нажмите кнопку "Преобразовать данные".

В редакторе запросов щелкните переключатель "Развернуть столбец" для столбца "Имя", содержащего элементы "Таблица". Выберите параметр «Развернуть» и нажмите кнопку «ОК».

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

Загрузить данные в таблицу рабочей книги

Теперь наши данные готовы для загрузки в таблицу рабочей книги.

< /p>

Перейдите на вкладку "Главная" в редакторе запросов и нажмите кнопку "Закрыть и загрузить", чтобы загрузить данные в таблицу рабочей книги.

Выводы

Данные XML – очень распространенный формат. Вы, вероятно, рано или поздно столкнетесь с этим, если будете работать с данными.

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

За несколько кликов мы можем использовать power query, чтобы преобразовать наши XML-данные в удобный формат таблицы.

Об авторе

Джон МакДугалл

Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

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