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