Если кто-либо пришлет файл XML, содержащий данные в таблицах, вам не придется читать весь текст и все угловые скобки с тегами. Можно загрузить этот документ напрямую в Excel, сообщить Excel, как следует отобразить этот документ, и работать с данными при помощи карт.
В последние несколько лет язык XML (Extensible Markup Language, букв, «расширяемый язык разметки») стал распространенным форматом обмена информацией, и нет ничего необычного в том, что люди и организации отправляют друг другу файлы XML. Простые структуры, лежащие в основе XML, делают обмен информацией чрезвычайно простым, причем неважно, используют ли все стороны одно и то же программное обеспечение и браузеры . Однако до недавних пор, хотя общие утилиты XML стали широко распространены, заполнить пробел между документами XML и пользовательским интерфейсом было еще достаточно трудно. Microsoft Excel упрощает эту задачу, по крайней мере, для данных в сетке таблицы.
В этом трюке используются возможности Excel, доступные только в Excel для Windows старше 2003 года. Более ранние версии Excel не поддерживают их; эти возможности не поддерживаются и в существующих, и планируемых версиях Excel для Macintosh.
Начнем с простого документа XML, приведенного в листинге 8.1.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
<
?xml version-
"1.0"
encoding-
"UTF-8"
?>
<
sales>
<
sale>
<
date>
2003
-
10
-
05
date>
<
isbn>
0596005385
isbn>
<
title>
Off1ce 2003
XML Essentia1s
title>
<
priceus>
34.95
<
quantity>
200
quantity>
<
customer IO=
"1025"
>
Zork"s Books
2003-10-05
0596002920
39.95
90
Zork"
s Books
customer>
title>
sale>
<
sale>
<
date>
2003
-
10
-
05
date>
<
isbn>
0596002378
isbn>
<
title>
SAX2
title>
<
priceus>
29.95
priceus>
<
quantity>
300
quantity>
<
customer ID=
"1025"
>
Zork"s Books
2003-10-05
0596005385
Office 2003 XML Essentials
34.95
10
Books of Glory
2003-10-05
0596002920
39.95
25
Books of Glory
2003-10-07
0596002378
SAX2
29.95
5
Books of Glory
2003-10-18
0596002378
SAX2
29.95
15
Title Wave
2003-10-21
0596002920
39.95
15
Books for You
|
// Листинг 8.1. Простой документ XML для анализа в Excel
< ?xml version-"1.0" encoding-"UTF-8"?>
2003-10-05
0596005385
Off1ce 2003 XML Essentia1s
34.95
200
Zork"s Books
2003-10-05
0596002920
XML in a Nutshell. 2nd Edition
39.95
90
Zork"s Books
2003-10-05
0596002378
SAX2
29.95
300
Zork"s Books
2003-10-05
0596005385
Office 2003 XML Essentials
34.95
10
Books of Glory
2003-10-05
0596002920
XML in a Nutshell, 2nd Edition
39.95
25
Books of Glory
2003-10-07
0596002378
SAX2
29.95
5
Books of Glory
2003-10-18
0596002378
SAX2
29.95
15
Title Wave
2003-10-21
0596002920
XML in a Nutshell. 2nd Edition
39.95
15
Books for You
Этот документ можно открыть непосредственно в Excel командой Файл → Открыть (File → Open). Откроется диалоговое окно (рис. 8.1).
Если вы выберете переключатель XML-список (As an XML list), то увидите предупреждение, что Excel создаст собственную схему для этого документа, не имеющего схемы (рис. 8.2).
Щелкнув кнопку ОК, вы увидите, какой способ Excel выбрал для представления информации в открываемом документе в виде электронной таблицы (рис. 8.3). Обратите внимание, что Excel ожидает встретить формат даты, который используется для элемента даты, поэтому даты, импортированные как 2003-10-05, будут отображаться как 10/5/2003.
Теперь, когда документ загружен в Excel, обрабатывать данные можно так же, как любые другие данные в Excel, - вставлять их в формулы, создавать именованные диапазоны, строить диаграммы на основе содержимого и т. д. Чтобы помочь вам, в Excel предусмотрено несколько встроенных возможностей анализа данных.
Раскрывающиеся списки в заголовках столбцов позволяют выбирать способ сортировки данных (по умолчанию данные выводятся в том порядке, в котором они записаны в исходном документе). Можно также включить отображение строки итогов Итог (Total); для этого можно воспользоваться панелью инструментов Список (List) или щелкнуть правой кнопкой мыши в любом месте списка и в контекстном меню выбрать команду Список → Строка итогов (List → Total Row). Когда строка итогов появится, выбрать вид итоговой информации можно будет в раскрывающемся меню (рис. 8.4).
Рис. 8.4. Выбор итогов для списка XML в Excel
Данные можно обновить, добавив при этом в обновляемую область информацию из документа XML с такой же структурой. Если у вас есть еще один документ с такой структурой, можете щелкнуть список правой кнопкой мыши, в контекстном меню выбрать команду XML → Импорт (XML → Import) и выбрать второй документ. Кроме того, после редактирования данные можно экспортировать обратно в файл XML, щелкнув список правой кнопкой мыши и выбрав в контекстном меню команду XML → Экспорт (XML → Export). Это превращает Excel в очень удобный инструмент редактирования простых XML-документов с табличной структурой.
Если данные достаточно простые, вы чаще всего можете доверить Excel выбор способа представления содержимого файла и использовать предусмотренные настройки по умолчанию. Если же данные усложняются, особенно если они содержат даты или текст, который выглядит как числа, то вы, возможно, захотите использовать схемы XML, чтобы указать Excel, как следует читать данные и какие данные подойдут к данной карте. Для нашего документа XML-схема может выглядеть, как в листинге 8.2.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
<
?xml version=
"1.0"
encoding=
"UTF-8"
?>
<
xs:
schema xmlns:
xs=
"http://www.w3.org/2001/XMLSchema"
elementFormDefault=
"qualified"
>
<
xs:
element name=
"sales"
>
<
xs:
complextype>
<
xs:
sequence>
<
xs:
element maxOccurs=
"unbounded"
ref=
"sale"
>
xs:
element>
xs:
sequence>
xs:
complextype>
xs:
element>
<
xs:
element name=
"sale"
>
<
xs:
complextype>
<
xs:
sequence>
<
xs:
element ref=
"date"
>
xs:
element>
<
xs:
element ref=
"ISBN"
>
xs:
element>
<
xs:
element ref=
"T1tle"
>
xs:
element>
<
xs:
element ref=
"PriceUS"
>
xs:
element>
<
xs:
element ref=
"quantity"
>
xs:
element>
<
xs:
element ref=
"customer"
>
xs:
element>
xs:
sequence>
xs:
complextype>
xs:
element>
<
xs:
element name=
"date"
type=
"xs:date"
>
xs:
element>
<
xs:
element name=
"ISBN"
type=
"xs:string"
>
xs:
element>
<
xs:
element name=
"Title"
type=
"xs:string"
>
xs:
element>
<
xs:
e1ement name=
"PriceUS"
type=
"xs:decimal"
>
xs:
e1ement>
<
xs:
element name=
"quant1ty"
type=
"xs:integer"
>
xs:
element>
<
xs:
element name=
"customer"
>
<
xs:
complextype mixed=
"true"
>
<
xs:
attribute name=
"ID"
use
=
"required"
type=
"xs:integer"
>
xs:
attribute>
xs:
complextype>
xs:
element>
xs:
schema>
|
// Листинг 8.2. Схема для данных о продаже книг
< ?xml version="1.0" encoding="UTF-8"?>
Обратите внимание, что элемент date определен как дата, а элемент ISBN определен как строка, а не как целое число. Если вы начнете с открытия этой схемы, а не документа, то заставите Excel загружать документ, сохранив ведущий нуль в ISBN.
На этот раз вы создадите список до загрузки документа XML, начав с пустого рабочего листа. Вам понадобится открыть область задач Источник XML (XML Source). Если она еще не открыта, нажмите сочетание клавиш Ctrl+Fl. Затем в раскрывающемся списке вверху области задач выберите Источник XML (XML Source) и вы увидите что-то похожее на рис. 8.6.
Чтобы загрузить схему, щелкните кнопку Карты XML (XML Maps). Откроется диалоговое окно Карты XML (XML Maps) (рис. 8.7).
Щелкните кнопку Добавить (Add), чтобы открыть схему, и выберите схему (рис. 8.8). Если схема не ограничивает документы одним начальным элементом, появится диалоговое окно с просьбой выбрать корневой элемент. Так как документы в этом примере начинаются с элемента sales, выберите «sales».
Когда вы щелкнете кнопку ОК, появится предупреждение о возможных сложностях с интерпретацией схем. XML-схема (XML Schema) - это огромная спецификация, поддерживающая чрезвычайно много структур, не отвечающих способу восприятия информации в Excel, поэтому в Excel есть некоторые ограничения.
В диалоговом окне Карты XML (XML Maps) Excel сообщит, что схема была добавлена к электронной таблице. Если вы щелкнете кнопку ОК, то вернетесь в главное окно Excel и в области задач Источник XML (XML Source) появится диаграмма, отображающая структуру схемы. Теперь, когда у вас есть структура, можно создать список. Самый простой способ сделать это, особенно с небольшими документами, как наш, - перетащить значок sales на ячейку А1.
Теперь, обустроив дом для данных, нужно заселить его. Можно щелкнуть кнопку Импорт XML-данных (Import XML Data) на панели инструментов Список (List) или щелкнуть правой кнопкой мыши список и в контекстном меню выбрать кнопку XML → Импорт (XML → Import). Если вы выберете файл, который уже открывали ранее (в листинге 8.1), то увидите результат, как на рис. 8.3. Обратите внимание на добавление ведущих нулей к значениям, которые теперь являются текстовыми, как и должно быть.
Элементы можно перетаскивать и по отдельности, если вы хотите поменять их местами, или помещать разные фрагменты информации в разные места электронной таблицы.
Поддержка XML-карт и списков в Excel означает, что можно создавать электронные таблицы, работающие с данными, которые поступают в отдельных файлах, с большей гибкостью, чем это было с предыдущими форматами, например CSV (с разделителями-запятыми) или форматом с разделительной табуляцией.
Вместо того чтобы подключаться к базе данных для интерактивного редактирования данных, пользователь сможет отредактировать файл XML, находясь в самолете, и передать его заказчику сразу же после приземления. Возможно, лучшее свойство новых возможностей XML Excel - это их гибкость. Пока данные организованы в структуру, соответствующую сетке таблицы, у Excel имеется совсем немного правил относительно того, какие виды XML можно передавать туда. Несколько раз щелкнув мышью и совершенно ничего не программируя, можно интегрировать данные XML в электронные таблицы.
Если необходимо создать файл XML-данных и файл схемы XML из диапазона ячеек на листе, можно использовать версию 1.1 надстройки "Средства XML для Excel 2003" для расширения существующих возможностей XML в Microsoft Excel 2007 и более поздних версиях.
Примечание:
Эта надстройка была разработана для Excel 2003. Документация и пользовательский интерфейс ссылаются на списки, которые в версиях приложения позднее Excel 2003 называются таблицами Excel.
Дополнительные сведения о работе с этой надстройкой см. в статье Использование надстройки "Средства XML" версии 1.1 для Excel 2003 .
Действие 2. Преобразование диапазона ячеек в XML-таблицу
Введите данные, для которых необходимо создать файл XML-данных и файл схемы XML. Данные должны быть представлены в табличном формате в виде столбцов и строк (так называемые обычные данные).
На вкладке Надстройки
в группе Команды меню
щелкните стрелку возле надписи Средства XML
и нажмите кнопку Преобразовать диапазон в список XML
.
Введите диапазон ячеек с данными, которые необходимо преобразовать, как абсолютную ссылку в текстовом поле.
В поле Первая строка содержит имена столбцов
выберите Нет
, если первая строка содержит данные, или Да
, если первая строка содержит заголовки столбцов, и нажмите кнопку ОК
.
Excel автоматически создаст схему XML, выполнит привязку ячеек к схеме и создаст XML-таблицу.
Важно:
Если откроется редактор Visual Basic и отобразится сообщение об ошибке Visual Basic for Applications (VBA), выполните следующие действия.
Нажмите кнопку ОК
.
В выделенной строке модуля кода VBA удалите из строки "50". Другими словами, измените:
XMLDoc в качестве MSXML
2
. DOMDocument50
Кому:
XMLDoc как Msxml2. Домдокумент
Нажмите клавишу F5 для поиска следующей строки, содержащей текст "XMLDoc As msxml2.DOMDocument50", нажмите кнопку ОК
и измените строку, как в предыдущем пункте.
Нажмите F5 еще раз, чтобы найти и изменить другие экземпляры строки.
Если после нажатия клавиши F5 сообщения об ошибке VBA больше не отображается, закройте редактор Visual Basic, чтобы вернуться в книгу. Диапазон ячеек будет преобразован в XML-таблицу.
Примечание:
Чтобы отобразить все карты XML в книге, на вкладке Разработчик
в группе XML
нажмите кнопку Источник
для отображения области задач "Источник XML". В нижней части области задач "Источник XML" нажмите кнопку Карты XML
.
Если вкладка Разработчик
не видна, выполните три первых действия, указанных в следующем разделе, чтобы добавить ее на ленту Excel.
Действие 3. Экспорт XML-таблицы в файл XML-данных (XML)
Примечание:
При создании карт XML и экспорте данных в Excel в XML-файлы существует ограничение на количество строк, которые можно экспортировать. При экспорте в XML-файл из Excel можно сохранить до 65 536 строк. Если файл содержит более 65 536 строк, Excel сможет экспортировать только первые строки (число строк mod 65 537). Например, если лист содержит 70 000 строк, Excel экспортирует 4464 строки (70 000 mod 65 537). Мы рекомендуем последовать одному из следующих советов: 1) используйте формат XLSX; 2) сохраните файл в формате "Таблица XML 2003 (*.xml)" (при этом будут потеряны сопоставления); 3) удалите все строки после 65 536 и затем снова выполните экспорт (при этом сопоставления сохранятся, но будут потеряны строки в конце файла).
XML является одним из самых распространенных форматов для хранения данных и обмена ими между различными приложениями. С данными работает и программа Microsoft Excel, поэтому очень актуальным является вопрос конвертации файлов из стандарта XML в форматы Excel. Выясним, как произвести эту процедуру различными способами.
XML-файлы написаны на специальном языке разметки чем-то похожим на HTML веб-страниц. Поэтому у этих форматов довольно сходная структура. В то же время, Эксель – это в первую очередь программа, имеющая несколько «родных» форматов. Самые известные из них: Книга Excel (XLSX) и Книга Excel 97 – 2003 (XLS). Выясним основные способы конвертации файлов XML в эти форматы.
Способ 1: встроенный функционал Excel
Программа Excel отлично работает с файлами формата XML. Она может открывать их, изменять, создавать, сохранять. Поэтому самый простой вариант поставленной перед нами задачи — это открыть данный объект и сохранить его через интерфейс приложения в виде документов XLSX или XLS.
На этом процедура конвертации файла XML в формат Excel через интерфейс программы окончена.
Способ 2: импорт данных
Вышеописанный способ подходит только для файлов XML с самой простой структурой. Более сложные таблицы при конвертации таким способом могут быть переведены некорректно. Но, существует ещё один встроенный инструмент Excel, который поможет корректно произвести импорт данных. Он расположен в «Меню разработчика»
, которое по умолчанию отключено. Поэтому, прежде всего, его нужно активировать.
Таким образом, конвертация в нужном для нас направлении будет совершена с максимально корректным преобразованием данных.
Способ 3: онлайн-конвертер
Тем пользователям, у которых по какой-то причине не установлена на компьютере программа Эксель, но нуждающимся в срочном преобразовании файла из формата XML в EXCEL, можно воспользоваться одним из множества специализированных онлайн-сервисов для конвертации. Одним из наиболее удобных сайтов подобного типа является Convertio.
Данный вариант может послужить хорошей подстраховкой на случай неимения доступа к стандартным инструментам для переформатирования по данному направлению.
Как видим, в самом Экселе есть встроенные инструменты, позволяющие преобразовать файл формата XML в один из «родных» форматов этой программы. Простейшие экземпляры можно легко конвертировать через обычную функцию «Сохранить как…». Для документов с более сложной структурой существует отдельная процедура преобразования через импорт. Те пользователи, которые по какой-либо причине не могут воспользоваться данными инструментами, имеют возможность выполнить задачу с помощью специализированных онлайн-сервисов для конвертации файлов.
Сообщить об опечатке
Текст, который будет отправлен нашим редакторам: