XML в Excel и обратно

Какие XML приходят чаще всего и что из них получается

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

Как Excel сам открывает XML и где он ошибается

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

Три способа открыть

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

Второй путь: Данные → Получить данные → Из файла → Из XML. Это Power Query: он показывает таблицы файла, даёт раскрыть вложенные ветки кнопкой в заголовке колонки и запоминает шаги, так что следующий такой же файл обновится по кнопке. Для регулярных выгрузок из 1С или прайсов поставщика это удобный вариант, для разового файла настройка занимает дольше, чем сама работа.

Сохранить таблицу в XML

Файл → Сохранить как → Таблица XML 2003 отдаёт книгу целиком в формате SpreadsheetML: листы, ячейки, стили. Такой файл поймёт другой Excel и конвертер выше, а программа, которая ждёт свой формат вроде «rows → row → поле», его не примет. Второй вариант в том же меню, «XML-данные», работает только после сопоставления столбцов со схемой в области задач XML-источник, без схемы Excel откажется сохранять.

Именно такой XML собирает режим «Excel → XML»: имена корня и записи задаются в полях, заголовки таблицы становятся тегами, а спецсимволы &, < и > экранируются по правилам XML. Если получатель ждёт значения в атрибутах, переключатель «колонки становятся атрибутами» соберёт запись вида <row Артикул="00123" Цена="12500"/>.

Как дерево тегов становится таблицей

XML описывает дерево, а таблица плоская, поэтому главный вопрос конвертера: какой элемент считать строкой. Он перебирает все теги документа и считает, сколько раз каждый повторяется среди соседей с общим родителем; тег с наибольшим числом повторов и с полями внутри становится записью. В прайсе для Яндекс Маркета это offer, в выгрузке 1С Товар, в RSS item, в карте сайта url. Список кандидатов показан справа, и запись можно сменить: тогда из того же файла получится другая таблица, например цены вместо товаров.

Каждая запись раскладывается в строку: атрибуты становятся колонками со знаком @ впереди, вложенные теги дают колонки с путём через точку, повторяющиеся теги внутри записи склеиваются через точку с запятой. Префиксы пространств имён убираются, чтобы вместо ss:Data в шапке стояло Data. Отдельная ветка разбирает книги «Таблица XML 2003» из самого Excel: листы, строки, ячейки и пропуски колонок по атрибуту ss:Index. Обратный режим собирает XML из книги или вставленных клеток: имена корня и записи задаются полями, заголовки становятся тегами, спецсимволы экранируются, а колонки по выбору идут элементами или атрибутами. Для JSON рядом есть такой же конвертер.

История: от SGML до выгрузок 1С

XML вырос из SGML, языка разметки документов, который стал стандартом ISO 8879 в 1986 году и использовался в издательствах и оборонной документации. SGML был слишком сложен для веба, и рабочая группа W3C во главе с Джоном Босаком упростила его до десятка страниц правил; рекомендация XML 1.0 вышла 10 февраля 1998 года, редакторами были Тим Брей, Жан Паоли и Майкл Сперберг-Маккуин. Идея была в обмене данными между любыми программами: строгий синтаксис, обязательное закрытие тегов, кодировка в заголовке файла.

Excel получил поддержку XML в версии 2002 и расширил её в 2003: появился формат «Таблица XML 2003», он же SpreadsheetML, где каждая ячейка описана тегом Cell с вложенным Data. Через четыре года на его основе Microsoft выпустила Office Open XML, и привычный .xlsx с 2007 года устроен как zip-архив с XML-файлами внутри; в 2008 году формат стал стандартом ISO/IEC 29500. Так XML оказался и снаружи Excel, как формат обмена, и внутри, как способ хранения.

В России XML стал языком выгрузок: формат CommerceML для обмена каталогами между 1С и интернет-магазинами, YML для прайсов Яндекс Маркета, отчётность в налоговую и фонды, банковские выписки. Всё это файлы с глубокой вложенностью, которые Excel открывает с трудом, поэтому вопрос «как открыть XML в Excel» задают тысячи раз в месяц.

Как разобрать файл и собрать XML под программу