У програмі Microsoft Excel можна легко імпортувати дані розширюваною мовою розмітки (XML), створені в інших базах даних і програмах, зіставляти елементи XML з XML-схеми з клітинками аркуша, а також експортувати переглянуті XML-дані, щоб обмінюватися відомостями з іншими базами даних і програмами. Завдяки всім цим можливостям XML програму Office Excel можна розглядати як генератор файлів XML-даних зі знайомим інтерфейсом користувача.
У цій статті
Роль XML у програмі Excel
XML – це технологія, розроблена для керування структурованими даними та їх спільного використання в зручному для сприйняття текстовому файлі. Формат XML відповідає вимогам галузевих стандартів і може оброблятися в різних базах даних і програмах. За допомогою мови XML розробники можуть створювати власні користувацькі позначки, структури даних і схеми. Іншими словами, мова XML значно полегшує процес визначення, передавання, перевірки й інтерпретації даних між базами даних, програмами й організаціями.
Файли XML-даних і XML-схем
Програма Excel головним чином працює із двома типами файлів XML.
-
Файли XML-даних (XML), які містять настроювані позначки та структуровані дані.
-
Файли схеми (XSD), які містять теги схеми, які забезпечують застосування правил, наприклад тип даних і перевірка.
Стандарт XML також визначає файли XSLT (XSLT) (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
Ви можете створити або відкрити книгу в програмі 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-даних, а потім у діалоговому вікні Open XML (Відкрити XML) виберіть Як XML-таблицю.
-
Скористайтеся командою З імпортованих даних XML (кнопка З інших джерел у групі Отримання зовнішніх даних на вкладці Дані ), щоб імпортувати файл XML-даних, а потім виберіть пункт XML-список у наявній книзі або Новий аркуш у діалоговому вікні Імпорт даних.
-
Перетягніть на аркуш один або кілька повторюваних елементів з області завдань Джерело XML.
Під час створення XML-таблиці імена елементів XML автоматично використовуються як заголовки стовпців. Їх можна змінити на будь-які інші заголовки стовпців. Утім, під час експорту даних із зіставлених клітинок завжди використовуються вихідні імена елементів XML.
Під час роботи з XML-таблицями в області завдань Джерело XML, натиснувши кнопку Параметри, можна скористатися двома зручними функціями:
-
<c0>Автоматично об’єднувати елементи під час зіставлення</c0>. Якщо вибрано цей параметр, програма Excel створює одну XML-таблицю з кількох полів у порядку їх перетягування на аркуш. Ця функція доступна, якщо кілька полів перетягнуто в один рядок і розміщено поруч. Якщо зняти прапорець поруч із цим параметром, кожен елемент відображатиметься у власній XML-таблиці.
-
<c0>Дані з заголовками</c0>. Якщо вибрано цей параметр, наявні дані заголовка використовуються як заголовки стовпців для повторюваних елементів, зіставлених в аркуші. Якщо зняти прапорець поруч із цим параметром, імена елементів використовуватимуться як заголовки стовпців.
За допомогою XML-таблиць можна легко імпортувати, експортувати, сортувати, фільтрувати та роздруковувати дані на основі джерела XML-даних. Утім, XML-таблиці мають певні обмеження залежно від способу їх упорядкування на аркуші.
-
XML-таблиці мають рядковий формат, тобто вони ростуть із рядка заголовка вниз. Не можна додавати нові записи над наявними рядками.
-
Транспонувати XML-таблицю, щоб нові записи додавалися праворуч, не можна.
Ви можете використовувати формули у стовпцях, зіставлених з елементами XML, з типом даних визначення XML-схеми (XSD), які програма Excel інтерпретує як число, дату або час. Подібно до таблиці Excel, формули в XML-таблиці заповнюють стовпець униз, коли до таблиці додаються нові рядки.
Вимоги до безпеки під час роботи з картами XML
Карта XML і відомості її джерела даних зберігаються із книгою Excel (не як окремий аркуш). За допомогою макросу Microsoft Visual Basic for Applications (VBA) зловмисники можуть переглянути ці відомості карти. Крім того, якщо зберегти книгу у форматі файлу Excel Office Open XML із підтримкою макросів, ці відомості також можна переглянути у блокноті Microsoft або іншому текстовому редакторі.
Якщо вам потрібно використати відомості карти, але при цьому видалити потенційно важливі відомості джерела даних, можна видалити з аркуша визначення джерела даних 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, починаючи від версії Excel 2003, більше не підтримується. Якщо відкрити книгу, створену у програмі Excel 2003, ви все ще зможете переглядати дані, але не зможете змінювати або оновлювати вихідні дані.
Робота з виведеними схемами
Під час імпорту XML-даних без попереднього додавання відповідної XML-схеми для створення карти XML програма Excel намагатиметься вивести схему на основі позначок, визначених у файлі XML-даних. Така виведена схема, яка зберігається у книзі, дає змогу працювати з XML-даними, якщо файл XML-схеми не зв’язано із книгою.
Під час роботи з імпортованими XML-даними, які містять виведену схему, також можна настроїти область завдань Джерело XML. Виберіть параметр Попередній перегляд даних в області завдань на кнопці Параметри , щоб відобразити перший рядок даних як зразок даних у списку елементів, якщо ви імпортували XML-дані, пов'язані з картою XML у поточному сеансі Excel.
Не можна експортувати схему Excel як окремий файл даних XML-схеми (XSD). Хоча існують деякі редактори XML-схем та інші способи створювати файли XML-схем, імовірно, ви не маєте до них доступу або не знаєте, як їх використовувати. Крім того, ви можете використовувати надбудову знарядь XML Excel 2003 версії 1.1, яка дає змогу створити файл схеми на карті XML. Докладні відомості див. в статті Використання надбудови знарядь XML для Excel 2003 версії 1.1.
Експорт XML-даних
Експорт XML-даних – це експорт вмісту зіставлених клітинок в аркуш. Під час експорту даних програма Excel застосовує наведені нижче правила визначення того, які дані потрібно зберегти та як це зробити.
-
Пусті елементи не створюються, коли пусті клітинки створено для додаткових елементів. Але пусті елементи створюються, якщо пусті клітинки доступні для обов’язкових елементів.
-
Для записування даних використовується формат кодування UTF-8.
-
Усі простори імен визначено в кореневому елементі XML.
-
Програма Excel перезаписує наявні префікси простору імен. Простору імен за замовчуванням призначено префікс ns0. Послідовні простори імен – ns1, ns2–ns<count> де <кількість> – кількість просторів імен, записаних у файл XML.
-
Вузли приміток не зберігаються.
Ви можете відобразити діалогове вікно Властивості карти XML (на вкладці Розробник у групі XML у групі XML натисніть кнопку Властивості карти). а потім скористайтеся параметром Перевірити дані на відповідність схемі для імпорту й експорту (активні за замовчуванням), щоб указати, чи перевіряє програма Excel дані на відповідність карті XML під час експорту даних. Виберіть цей параметр, якщо потрібно переконатися, що експортовані XML-дані відповідають XML-схемі.
Використання файлу формату Excel Office Open XML із підтримкою макросів
Книгу Excel можна зберегти в кількох форматах, зокрема у форматі Excel Office Open XML із підтримкою макросів (XLSM). Програма Excel має визначену XML-схему, що визначає вміст книги Excel, зокрема XML-позначки, які зберігають усі відомості про книгу (наприклад, дані та властивості), а також визначають загальну структуру документа. Настроювані програми можуть використовувати такий формат файлу Excel Office XML із підтримкою макросів. Наприклад, розробники можуть створити настроювану програму для пошуку даних у кількох книгах, збережених у цьому форматі, а також створити систему створення звітів на основі знайдених даних.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.
