Огляд мови XML у програмі Excel

Діаграма XML

У програмі Microsoft Excel можна легко імпортувати дані розширюваною мовою розмітки (XML), створені в інших базах даних і програмах, зіставляти елементи XML з XML-схеми з клітинками аркуша, а також експортувати переглянуті XML-дані, щоб обмінюватися відомостями з іншими базами даних і програмами. Завдяки всім цим можливостям XML програму Office Excel можна розглядати як генератор файлів XML-даних зі знайомим інтерфейсом користувача.

У цій статті

Роль XML у програмі Excel

Файли XML-даних і XML-схем

Основні сценарії використання мови XML у програмі Excel

Простий процес використання XML-даних у програмі 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 також визначено файли Extensible Stylesheet Language Transformation (XSLT), які зазвичай використовуються, щоб застосовувати стилі та перетворювати XML-дані на різні формати презентацій. Ці перетворення можна застосовувати, перш ніж імпортувати файли XML у програму Excel, або експортувавши файли XML із програми Excel. Якщо з файлами XML-даних, які імпортуються у програму Excel, зв’язано файли XSLT, можна застосувати або не застосовувати форматування, перш ніж додавати дані до аркуша. Утім, це можливо, тільки якщо використано команду Відкрити в програмі Excel, щоб відкрити файл XML. Щоб побачити в папці файли XML, виберіть тип файлу XML-файли (*.xml), перш ніж натиснути кнопку Відкрити.

Основні сценарії використання мови XML у програмі Excel

За допомогою форматів XML і Excel можна керувати книгами та даними в такий спосіб, який раніше був неможливий або дуже складний. За допомогою карт XML можна легко додавати, визначати й видобувати певні частини бізнес-даних із документів Excel. Наприклад, рахунок, який містить ім’я та адресу клієнта, або звіт, що містить фінансові відомості за останній квартал, більше не являють собою прості статичні звіти. Ви можете легко імпортувати ці відомості з баз даних і програм, перевіряти й експортувати їх у ті самі або інші бази даних і програми.

Нижче наведено основні сценарії використання функцій XML.

  • Розширення функціональності наявних шаблонів Excel завдяки зіставленню елементів XML із наявними клітинками. Це значно спрощує отримання XML-даних у шаблонах і поза ними без необхідності їх змінення.

  • Використання XML-даних як вихідних для наявних моделей обчислення під час зіставлення елементів XML із наявними аркушами.

  • Імпорт XML-даних у новий аркуш.

  • Імпорт XML-даних із веб-служби в аркуш Excel.

  • Експорт даних у зіставлених клітинках із файлами XML-даних незалежно від інших даних в аркуші.

На початок сторінки

Простий процес використання XML-даних у програмі Excel

Наведена нижче діаграма ілюструє особливості спільної роботи різних файлів і операцій під час використання функцій XML із програмою Excel. Власне, ідеться про п’ять етапів цього процесу.

Огляд роботи програми Excel із даними XML

Виноска 1 Додавання файлу XML-схеми (XSD) до книги

Виноска 2 Зіставлення елементів XML-схеми з окремими клітинками або XML-таблицями

Виноска 3 Імпорт файлу XML-даних (XML) і зв’язування елементів XML із зіставленими клітинками

Етап 4 Введення даних, переміщення зіставлених клітинок і поліпшення функціональності програми Excel під час збереження структури та визначень XML

Етап 5 Експорт переглянутих даних із зіставлених клітинок у файл даних 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 натисніть кнопку Джерело. На схемі нижче показано основні функції цієї області завдань.

Область завдань «Джерело 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-даних, а потім виберіть пункт 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. Під час імпорту відбувається зв’язування даних із файлу з картою 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-дані з веб-служби за допомогою файлу підключення служби видобування даних (UXDC) для підключення до джерела даних через інтерфейс користувача у програмі Excel, починаючи від версії Excel 2003, більше не підтримується. Якщо відкрити книгу, створену у програмі Excel 2003, ви все ще зможете переглядати дані, але не зможете змінювати або оновлювати вихідні дані.

Робота з виведеними схемами

Під час імпорту XML-даних без попереднього додавання відповідної XML-схеми для створення карти XML програма Excel намагатиметься вивести схему на основі позначок, визначених у файлі XML-даних. Така виведена схема, яка зберігається у книзі, дає змогу працювати з XML-даними, якщо файл XML-схеми не зв’язано із книгою.

Під час роботи з імпортованими XML-даними, які містять виведену схему, також можна настроїти область завдань Джерело XML. Натисніть кнопку Параметри та виберіть пункт Переглянути дані в області завдань, щоб відобразити перший рядок даних як зразок у списку елементів, якщо ви імпортували XML-дані, зв’язані з картою XML, у поточному сеансі роботи із програмою Excel.

Експортувати виведену схему Excel як окремий файл даних XML-схеми (XSD) не можна. Хоча наявні кілька редакторів XML-схем, а також інші методи створення файлу XML-схеми, у вас може не бути доступу до них або ви можете не знати, як ними користуватися. Натомість можна скористатися надбудовою знарядь для програми Excel 2003 версії 1.1, за допомогою якої можна створити файл схеми з карти XML. Докладні відомості див. у статті Використання версії 1.1 надбудови знарядь XML для програми Excel 2003.

Експорт XML-даних

Експорт XML-даних – це експорт вмісту зіставлених клітинок в аркуш. Під час експорту даних програма Excel застосовує наведені нижче правила визначення того, які дані потрібно зберегти та як це зробити.

  • Пусті елементи не створюються, коли пусті клітинки створено для додаткових елементів. Але пусті елементи створюються, якщо пусті клітинки доступні для обов’язкових елементів.

  • Для записування даних використовується формат кодування UTF-8.

  • Усі простори імен визначено в кореневому елементі XML.

  • Програма Excel перезаписує наявні префікси простору імен. Простору імен за замовчуванням призначено префікс ns0. Успішним просторам імен призначено префікси ns1, ns2 і ns<кількість>, де <кількість> це число просторів імен, записаних у файл XML.

  • Вузли приміток не зберігаються.

У діалоговому вікні Властивості карти XML (вкладка Розробник, група XML, кнопка Властивості карти) можна вибрати пункт Перевіряти дані на відповідність схемі під час імпортування та експортування (прапорець установлено за замовчуванням), щоб визначити, чи програма Excel перевіряє дані на відповідність карті XML під час їх експорту. Установіть цей прапорець, якщо потрібно забезпечити відповідність експортованих XML-даних XML-схемі.

Використання файлу формату Excel Office Open XML із підтримкою макросів

Книгу Excel можна зберегти в кількох форматах, зокрема у форматі Excel Office Open XML із підтримкою макросів (XLSM). Програма Excel має визначену XML-схему, що визначає вміст книги Excel, зокрема XML-позначки, які зберігають усі відомості про книгу (наприклад, дані та властивості), а також визначають загальну структуру документа. Настроювані програми можуть використовувати такий формат файлу Excel Office XML із підтримкою макросів. Наприклад, розробники можуть створити настроювану програму для пошуку даних у кількох книгах, збережених у цьому форматі, а також створити систему створення звітів на основі знайдених даних.

На початок сторінки

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×