Постановка завдання, Web Services, ASP, статті

Уявіть собі менеджера з продажу, регулярно вивчає дані про продажах. Їй потрібно отримувати зведені дані про продажі за певний період. Зокрема, її цікавлять підсумкові дані про продажі за кожен із днів заданого періоду. Їй хотілося б, щоб дані виводилися і в числовому вигляді, і у вигляді діаграми, що показує, як змінювалися продажу протягом цього періоду.

Було б непрактично створювати спеціальний звіт по кожному її вимогу. Тоді комусь довелося б аналізувати вхідні дані і створювати окремі табличні звіти для безлічі сценаріїв, можливих в діяльності даної організації. Це стало б надто важким тягарем для розробників та інших фахівців з інформаційних технологій, особливо якщо врахувати, що менеджер по продажам з одного конкретного підрозділи – не єдиний співробітник організації, що потребує звітах такого роду. Краще створити електронну таблицю Excel, здатну самостійно налаштовуватися на формування різних звітів, запитуваних менеджером з продажу, а також співробітниками інших підрозділів, і дозволяє вирішувати аналогічні завдання.

У цій статті показується, як засобами Microsoft Office Excel 2003 імпортувати дані і встановити динамічний зв’язок діаграми з даними. Крім того, ми створимо зручний UI, що дозволяє задавати параметри звіту та імпортувати дані для звіту. Загальний вид рішення показаний на рис. 1.

Рис. 1. Графічне відображення бізнес-даних

XML-зіставлення в Excel 2003

В Excel 2003 удосконалено підтримка операцій з XML-даними. В Microsoft Excel 2002 з’явилася можливість відкривати і зберігати електронні таблиці у форматі “XML Spreadsheet”, використовуючи XML-словник, розроблений спеціально для Excel. Цей словник відповідає певною схемою, тому будь-яка система може формувати електронні таблиці в XML-форматі, з якими можна працювати в Excel, – головне, щоб вони відповідали схемі. Але було б ще краще, якби Excel вмів настільки ж інтелектуально працювати з іншими схемами. В Excel 2003 ця можливість реалізована – користувачі і розробники можуть додавати в робочі книги власні схеми. При цьому створюється XML-карта (XML map), яку можна застосовувати, щоб дані було зручніше інтерпретувати при імпорті або експорті. Це дозволяє імпортувати дані, що відповідають власному, специфічному для предметної області XML-словниками (або схемі), а потім записати дані назад, використовуючи той же XML-словник.

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

XML-карти

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

Як видно на рис. 2, Excel показує ієрархічне представлення того, яку структуру повинні мати дані, що містяться в електронній таблиці. Різні елементи цього подання можна пов’язати з певними осередками електронної таблиці. Схема може просто обмежувати дані деякої ієрархічної структурою або накладати на типи та інші аспекти даних додаткові обмеження, що враховуються при імпорті та експорті даних електронної таблиці.

Рис. 2. XML-карта для робочої книги

На рис. 3 показано, як XML-карта використовується в нашій робочій книзі. В XML-карті задані два ключових елементи – OrderDay і Sales. При виборі елементів карти в секції XML Structure Excel вибирає відповідний діапазон комірок робочої книги. Аналогічно при виборі комірки в діапазоні Excel вибирає відповідний елемент карти в
Task Pane
.

Рис 3. Зіставлення елементів і осередків

{
Малюнок:
XML Map – XML-карта
Mapped Cells – зіставляти осередку
}

Додавання XML-карти

Щоб скористатися XML-картою, її треба додати в робочу книгу.

Щоб додати XML-карту в робочу книгу через UI:

  1. У меню Data виберіть XML, Клацніть XML Source.
  2. У секції XML Source клацніть XML Maps…, Потім клацніть Add.
  3. Виберіть допустимий XML-, XSD-або UDC-файл, клацніть Open.
  4. Клацніть OK, Щоб додати XML-карту.

Щоб задати зіставлення осередків елементам XML-карти, потрібно виділити клітинку або діапазон клітинок і перетягнути елемент карти на обрані комірки. Крім того, можна виконати таке зіставлення програмно, навіть при імпорті даних із зовнішнього джерела, що і зроблено в нашому бізнес-рішення.

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

Наше бізнес-рішення дозволяє багаторазово імпортувати дані з одного і того ж джерела, кожен раз задаючи різні параметри. Наприклад, спочатку користувачеві може знадобитися звіт з 1 липня 2000 р. по 15 липня 2000 р., а наступного разу – звіт за останні 16 днів місяці. Оскільки Excel багаторазово імпортує дані, немає сенсу кожен раз зіставляти осередку заново. Тому можна застосовувати існуючу карту і просто перезаписувати дані в зіставляються осередках.

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

Списки Excel

Списки (рис. 4) застосовуються для створення груп даних в електронних таблицях Excel і, таким чином, полегшують обробку даних. Якщо ви уважно подивіться на клітинки, то побачите навколо них синю рамку. Це не стиль форматування. Excel автоматично показує рамку навколо осередків, які ідентифікуються як список. (Якщо це збиває вас з толку, настройте Excel так, щоб він не показував рамку навколо неактивних списків). Оскільки список – це просто група даних електронної таблиці, в одній таблиці може бути кілька списків, і до кожному списку можна звертатися як до якоїсь одиниці. Крім того, в Excel 2003 існує тип списку, званий XML-списком. XML-список, як і більш загальний список, є списком або групою даних електронної таблиці, але з XML-списком зв’язується додаткова інформація (Метадані), яка надається в XML-форматі.

Рис. 4. Список в електронній таблиці Excel

Список містить стовпці, заголовки стовпців і рядок вставки для додавання даних. Якщо б більш уважно подивіться рис. 4, то побачите велику зірочку, яка вказує, де знаходиться рядок вставки. Крім того, ви побачите заголовки стовпців. В XML-списках стовпці зв’язуються з елементами XML-схеми. Для цього явно або неявно присвоюється значення властивості XPath стовпця. В нашому бізнес-рішення це властивість задається неявно, за допомогою XML-карти, яку ми додали в робочу книгу.

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

Щоб додати список в робочий лист:

  1. Виберіть клітинку, яка буде верхньою лівою клітинкою XML-списку.
  2. Виберіть кореневий елемент в секції XML Structure, Щоб виділити його в XML-карті.
  3. Перетягніть виділений кореневої елемент з секції XML
    Structure
    на виділену клітинку.

Вийде список виду, показаного на рис. 5.

Рис 5. Приклад нового XML-списку

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

Зчитування XML-даних в Excel

Вважати XML-дані в робочий лист Excel можна кількома способами. У класу Workbook три ключові методу імпорту XML-даних в Excel:

Для завантаження даних з файлу в робочу книгу використовується метод
OpenXML
. Це програмний еквівалент відкриття XML-файла в Excel вибором Open в меню File. У наступній таблиці показані різні варіанти відкриття XML-файлів в Excel та їх опис.

Варіант Опис
xlXmlLoadImportToList Завантаження даних з вихідного файлу в XML-список
xlXmlLoadMapXml Створення XML-карти відповідно з даними вихідного файлу
xlXmlLoadOpenXml Завантаження даних з вихідного файлу в нову робочу книгу
xlXmlLoadPromptUser Висновок діалогового вікна, пропонує вибрати один з трьох наведених вище варіантів

Імпорт XML-даних відрізняється від OpenXML тим, що XML-файл не відкривається в окремій робочій книзі. Дані поміщаються в існуючу робочу книгу, при цьому створюється або новий робочий аркуш, або XML-список в існуючому листі. Метод XMLImport дозволяє задати URL, з якого зчитуються XML-дані, що аналогічно використанню об’єкту QueryTable Web-запитами в Excel 2002 і в більш ранніх версіях. Метод приймає чотири параметри, описаних в такій таблиці.

Параметр Опис
URL Рядок посилання на URL, з якого зчитуються XML-дані (обов’язковий параметр)
XMLMap Примірник класу XMLMap (Обов’язковий параметр). Іншими словами, якщо в робочій книзі вже є XML-карта, ви можете використовувати цю карту. Якщо потрібно, щоб при імпорті Excel створив карту автоматично, передайте значення Nothing
Overwrite Логічне значення, яке вказує, чи повинні нові імпортовані дані перезаписувати існуючі (Необов’язковий параметр)
Destination Посилання на діапазон комірок, в який поміщаються імпортовані дані (необов’язковий параметр)

Примітка Якщо ви використовуєте існуючу XML-карту, не передавайте параметр Destination.

Останній метод, XMLImportXML, Аналогічний XMLImport за винятком того, що вказується не URL, що визначає місцезнаходження імпортованих даних, а рядок, що містить самі дані. Всі інші параметри точно такі ж.

Імпорт даних

Наше бізнес-рішення отримує вводяться користувачем дані, звертаючись до URL, за яким знаходяться дані. Коли користувач клацає кнопку на робочому аркуші, виконується процедура обробки події. Ця процедура імпортує дані. У методу ImportXML є обов’язковий параметр, що приймає посилання на об’єкт XMLMap. Ми отримуємо цю посилання наступним чином:

  Dim xmpCustomMap as XmlMap
  Set xmpCustomMap = ActiveWorkbook.XmlMaps("ROOT_Map")

Потім викликаємо метод ImportXML:

  ActiveWorkbook.XmlImport URL:= _
  & "http://localhost/NorthWindWeb/" _  
  & "sales.xml?BeginDate=07/01/2000&EndDate=07/14/2000, _
  ImportMap:=xmpCustomMap, Overwrite:=True

У цьому прикладі URL “зашитий” в код, так що метод цілком зрозумілий. Проте в бізнес-рішенні URL формується в окремій процедурі, щоб його можна було ставити відповідно до вводяться користувачем даними та параметрами робочої книги. Потім Excel повертає результуючий URL в вигляді рядка, тому його можна передавати в параметрі методу
ImportXML
. У бізнес-рішеннях, що застосовуються на практиці, слід використовувати обробку помилок та інші “просунуті” прийоми програмування.

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

Джерела даних динамічних діаграм

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

Щоб додати діаграму в робочий лист:

  1. У меню Insert виберіть Chart.
  2. Слідуйте вказівкам Chart Wizard і виберіть тип діаграми Line.
  3. Клацніть Next, Щоб задати джерело даних, або
    Finish
    , Щоб помістити на робочий лист порожню діаграму.

Після додавання діаграми потрібно вказати, що її джерелом даних є XML-список, але ми зробимо це не в діалоговому вікні Chart Wizard. Поступимо простіше. Відразу ж клацнемо Finish, тоді додасться порожня діаграма, яка поки що не відображає ніяких даних.

Щоб вказати XML-список в якості джерела даних діаграми

:

  1. Помістіть курсор на XML-список, щоб вибрати його.
  2. Клацніть рамку XML-списку, щоб перетягнути його на діаграму. Відкриється діалогове вікно Paste Special.
  3. Клацніть OK, Щоб прийняти параметри за замовчуванням і показати дані XML-списку в діаграмі.

Після виконання цих операцій з’явиться діаграма виду, показаного на рис. 6.

Рис. 6. Остаточний вигляд діаграми, пов’язаної з XML-списком

Можна змінити тип діаграми, вказавши замість Line, наприклад, Bar, Pie, Doughnut (це не випічка). Крім того, можна змінити формат діаграми на відмінний від використовуваного за замовчуванням. Проте джерело даних діаграми можна не змінювати. Якщо Excel заново заповнить XML-список даними, діаграма буде відповідати цим новим даними, оскільки її джерелом даних є динамічно завантажуваний XML-список.

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

Висновок

Удосконалення в Microsoft Office Excel 2003 полегшують роботу з “Рідними” вихідними даними у форматі XML. Ви можете імпортувати необроблені XML-дані в електронну таблицю, помістити їх в заданий місце і відобразити в динамічно оновлюваної діаграмі. XML-карти – могутній засіб, що дозволяє зіставляти заданим діапазонам електронної таблиці ієрархічні XML-схеми, що містять елементи і атрибути. Завдяки цьому діапазони стає зручніше обробляти в Відповідно до схеми XML-карти. Крім того, інтерпретацію даних полегшують XML-списки, що дозволяють створювати групи осередків в самій електронної таблиці. Ці групи (точніше, списки) можна обробляти незалежно від інших осередків і звертатися до них програмно.

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

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

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*