Огляд xml в excel
Причини використання XML в Excel
XML є технологією, розробленою для управління структурованими даними та відображення цих даних у вигляді удобочитаем текстового файлу. Мова XML відповідає галузевим стандартам і може бути оброблений багатьма базами даних і додатками. За допомогою XML багато розробників можуть створювати власні налаштовані теги, структури даних і схеми. В цілому XML істотно полегшує визначення, передачу, перевірку та інтерпретацію даних в різних базах даних, додатках і організаціях.
Дані XML і файли схеми
Excel працює переважно з двома типами XML-файлів:
файлами даних XML (XML), які містять настроюються теги і структуровані дані;
файлами схеми (XSD), які містять теги схеми, які встановлюють певні правила (наприклад, тип даних і перевірку).
Ключові сценарії XML і Excel
Ось ключові сценарії, для роботи з якими призначені можливості XML:
розширення функціональних можливостей існуючих шаблонів Excel шляхом зіставлення XML-елементів з існуючими осередками. Це полегшує видалення і додавання даних XML в шаблонах, не змінюючи самі шаблони;
використання даних XML в якості вхідних даних для існуючих моделей обчислень шляхом зіставлення XML-елементів з існуючими листами;
імпорт файлів даних XML в нову книгу;
імпорт даних XML з веб-служби на лист Excel;
експорт даних в зіставлених осередках в файли даних XML незалежно від інших даних у книзі.
Основний процес використання даних XML в Excel
На таку схему показано, як взаємодіють різні файли і операції при використанні XML в Excel. Фактично цей процес складається з п'яти етапів.
Додавання в книгу файлу схеми XML (XSD)
Зіставлення елементів схеми XML з окремими осередками або XML-таблицями
Імпорт XML-файла даних і прив'язка XML-елементів до співставленим осередкам
Введення даних, переміщення зіставлених осередків і використання функціональних можливостей Excel при збереженні структури і визначень XML.
Експорт оброблених даних з зіставлених осередків в XML-файл даних
Робота з картами XML
Ви можете створити або відкрити книгу в Excel, вкласти файл схеми XML (XSD) в книгу і використовувати область завдань Джерело XML для зіставлення XML-елементів схеми з окремими осередками або таблицями. Виконавши таке зіставлення, ви можете імпортувати і експортувати дані XML в елементи таблиці і з них відповідно.
При додаванні файлу схеми XML (XSD) в книгу створюється карта XML. Як правило, карти XML використовуються для створення зіставлених осередків і управління взаємозв'язком між такими осередками та окремими елементами схеми XML. Крім того, ці карти використовуються для прив'язки вмісту зіставлених осередків до елементів схеми при імпорті або експорті файлів даних XML (XML).
Ви можете створювати зіставлені осередки двох видів: осередки з точковим зіставленням і повторювані осередки (які відображаються у вигляді XML-таблиць). Для більш гнучкої розробки книги ви можете переміщати зіставлені осередки в будь-яке місце на аркуші і в будь-якій послідовності, навіть відрізняється від послідовності, заданої в схемі XML. Ви також можете вибрати, які елементи слід зіставляти.
Важливо знати такі правила використання карт XML:
книга може містити одну або кілька карт XML;
одночасно можна зіставити тільки один елемент з одним розташуванням в книзі;
кожна карта XML незалежна від інших, навіть якщо кілька карт XML в одній книзі відносяться до однієї і тієї ж схемою;
карта XML може містити тільки один кореневий елемент. При додаванні схеми, що визначає більш одного кореневого елемента, з'явиться запит на вибір кореневого елемента, який буде використовуватися в новій карті XML.
Використання області завдань джерела XML
Використовуйте область завдань Джерело XML для управління картами XML. Щоб її відкрити, на вкладці Розробник у групі XML натисніть Джерело. Наступного схемі показані основні функціональні можливості цієї області завдань.
1. Відображення карт XML, доданих до книги.
2. Відображення ієрархічного списку XML-елементів в поточному карті XML.
4. Відкриття діалогового вікна Карти XML. яке служить для додавання, видалення або перейменування карт XML.
5. Перевірка можливості експорту даних XML через поточну карту XML.
Типи елементів і їх значки
У наступній таблиці наведено всі типи XML-елементів, з якими може працювати Excel, і значки, які використовуються для подання кожного типу елементів.
Робота з осередками з точковим зіставленням
Осередок з точковим зіставленням - це осередок, зіставлена з неповторним 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-елементами, які мають тип даних визначення схеми XML (XSD), що інтерпретується Excel як число, дата або час. У XML-таблиці, як і в таблиці Excel, формули копіюються вниз по стовпчику в міру додавання нових рядків.
Забезпечення безпеки карт XML
Якщо ви хочете як і раніше використовувати відомості про карту, але при цьому приховати конфіденційні дані, ви можете видалити з книги визначення джерела даних схеми 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 Excel намагається отримати схему, грунтуючись на тегах, визначених у файлі даних XML. Отримана схема зберігається в книзі і дозволяє працювати з даними XML, навіть якщо файл схеми XML не пов'язаний з книгою.
Експорт даних XML
Експорт даних XML здійснюється шляхом експорту вмісту зіставлених осередків листа. При експорті даних в Excel застосовуються такі правила для визначення даних і способів їх збереження:
порожні елементи не створюються при наявності порожніх клітинок для додаткового елемента, але вони створюються при наявності порожніх клітинок для обов'язкового елемента;
запис даних проводиться в кодуванні UTF-8;
всі простори імен визначаються в кореневому XML-елементі;
існуючі префікси простору імен перезаписувати. За замовчуванням простору імен призначається префікс ns0. Наступним просторів імен присвоюються префікси ns1, ns2 і т. Д. До ns
У діалоговому вікні Властивості карти XML (натисніть Властивості карти в групі XML на вкладці Розробник) виберіть параметр Перевіряти дані на відповідність схемі при імпорті та експорті (використовується за умовчанням), щоб вказати, чи слід Excel перевіряти дані по карті XML при експорті. Увімкніть цей параметр, щоб все експортовані дані XML точно відповідали схемі XML.
Використання файлу Excel формату Office Open XML з підтримкою макросів
Книгу Excel можна зберігати в різних форматах файлу, включаючи файли Excel формату Office Open XML (XLSM) з підтримкою макросів. Excel містить схему XML, що визначає вміст книги Excel, в тому числі теги XML, в яких зберігаються всі відомості книги (наприклад, дані і властивості), а також всю структуру книги. Файл Excel формату Office XML з підтримкою макросів може застосовуватися в призначених для користувача додатках. Наприклад, розробники можуть створити додаток, яке виконує пошук даних в декількох книгах, які зберігаються в цьому форматі, і створює систему звітів на підставі отриманих даних.