Аннотация

В статье Web Queries and Dynamic Chart Data in Microsoft Excel 2002 на сайте MSDN показывалось, как с помощью Web-запросов импортировать данные в Microsoft Excel 2002, а затем создавать диаграммы, динамически отражающие изменения в исходных данных. В этой статье я расскажу, как добиться тех же результатов в Microsoft Office Excel 2003, написав меньше кода, потратив меньше усилий, но обеспечив больше возможностей. Прочитав статью, вы расширите свои познания в области поддержки XML в Excel 2003: познакомитесь с XML-картами, XML-списками, новыми процедурами импорта XML-данных и с применением динамически заполняемых XML-списков как источников данных диаграмм.

Содержание

Постановка задачи
XML-сопоставление в Excel 2003
Списки Excel
Считывание XML-данных в Excel
Источники данных динамических диаграмм
Заключение

Постановка задачи

Представьте себе менеджера по продажам, регулярно изучающую данные о продажах. Ей нужно получать сводные данные о продажах за определенный период. В частности, ее интересуют итоговые данные о продажах за каждый из дней заданного периода. Ей хотелось бы, чтобы данные выводились и в числовом виде, и в виде диаграммы, показывающей, как изменялись продажи в течение этого периода.

Было бы непрактично создавать специальный отчет по каждому ее требованию. Тогда кому-то пришлось бы анализировать входные данные и создавать отдельные табличные отчеты для множества сценариев, возможных в деятельности данной организации. Это стало бы слишком тяжелым бременем для разработчиков и других специалистов по информационным технологиям, особенно если учесть, что менеджер по продажам из одного конкретного подразделения - не единственный сотрудник организации, нуждающийся в отчетах такого рода. Лучше создать электронную таблицу 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;
• XMLImport;
• XMLImportXML.

Для загрузки данных из файла в рабочую книгу используется метод OpenXML. Это программный эквивалент открытия XML-файла в Excel выбором Open в меню File. В следующей таблице показаны различные варианты открытия XML-файлов в Excel и их описание.

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

Примечание Если вы используете существующую 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 в автоматизации предприятия.