Основные сценарии использования Excel Services

Алексей Федоров

Сценариииспользования Excel Services

   Просмотр электронныхтаблиц через браузер

   Создание компонентов дляанализа данных

   Использование вклиентских приложениях логики, встроенной в таблицы Excel

Расширениефункциональности Excel Services: пользовательские функции

Чем не является ExcelServices

Ограничения ExcelServices

Обзорархитектуры Excel Services

Microsoft Office 2007 будет включать новый продукт — Microsoft Office 2007 Excel Services. В задачу этого серверного компонента входит реализация ряда сценариев, которые мы рассмотрим в данном документе. Мы также расскажем о механизмах расширения Excel Services за счет создания пользовательских функций и об ограничениях в текущей версии Excel Services, а завершим наш обзор кратким рассмотрением его архитектуры, безопасности и масштабируемости.

Сценарии использования Excel Services

Три основных сценария применения сервисов Excel выглядят следующим образом:

• просмотр электронных таблиц через браузер;

• создание компонентов для анализа данных (Business Intelligence Dashboard);

• использование логики, встроенной в таблицы Excel, в клиентских приложениях.

Просмотр электронных таблиц через браузер

В настоящее время существует несколько способов обмена электронными таблицами для предоставления доступа к расположенным в них данным — отсылка таблиц по электронной почте, публикация в файловых каталогах общего доступа, сохранение таблицы в виде Web-страницы и т.п. Для некоторого класса электронных таблиц (таблиц, содержащих конфиденциальную бизнес-информацию, уникальную бизнес-логику, таблиц, основанных на большом количестве вычислений, а также для таблиц, которые предназначены для клиентов или партнеров) многие из перечисленных способов обмена не подходят. Например, внесенные в таблицы изменения порождают многочисленные версии приблизительно одних и тех же данных; возникают сложности с защитой информации, поставляемой в рамках электронной таблицы; помимо этого сложные таблицы, наполненные комплексными вычислениями, не всегда удобно пересылать по электронной почте.

Большинство из перечисленных проблем можно решить, используя Excel Services: при помощи Excel 2007 автор может опубликовать созданную им электронную таблицу в библиотеке документов SharePoint и затем разрешить доступ к серверной версии этой таблицы через Web-браузер. Когда пользователь обращается к такой таблице, службы Excel Services загружают таблицу, при необходимости обновляют внутренние данные, производят необходимые вычисления и генерируют HTML/DHTML-поток, отсылаемый в Web-браузер клиента. На клиентском компьютере для просмотра таблиц не требуется установки ни клиентской версии Excel 2007, ни даже каких-либо компонентов ActiveX. Пользователи всегда получают самую последнюю версию электронной таблицы, а у автора появляется возможность ограничить доступ к определенным книгам внутри таблицы, закрыть некоторые диапазоны и т.п. Более того, пользователи не видят формул, использовавшихся для вычисления значений ячеек. За счет того что Excel Services базируется на технологиях SharePoint, у авторов появляется возможность обеспечения безопасности, контроля за доступом, аудита, применения встроенных средств документооборота, версионности и т.п.

Существует два способа публикации электронных таблиц на сервере. Можно использовать команды Save или Save As для сохранения таблиц в библиотеке документов SharePoint: для этого необходимо указать имя сервера и имя файла — либо в UNC-, либо в HTTP-формате. Второй способ, появившийся в Excel 2007 впервые, представляет собой команду Publish To Office Server. Выполнение этой команды не только публикует электронную таблицу на сервере, но и позволяет задать ряд параметров, которые будут использоваться при обращении к серверной таблице.

Диалоговая панель Publish to Office Server выглядит так, как представлено на нижеследующем рисунке:

Публикация таблицы на сервере

По умолчанию опция Open the workbook in my browser включена — сразу же после публикации таблицы вы увидите ее содержимое в вашем браузере.

Как мы уже отмечали, при публикации на сервере с помощью команды Publish To Office Server можно задать несколько опций. К таким опциям, в частности, относятся область видимости и параметры, передаваемые электронной таблице при ее открытии.

В Excel 2007 можно задать три области видимости: вся таблица (значение по умолчанию), подмножество книг и набор именованных элементов (диапазоны, графики, таблицы, сводные таблицы, сводные графики).

Задание области видимости

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

Панель задач для редактирования ячеек

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

Для превращения ячейки в параметр используется диалоговая панель публикации таблицы. Вкладка Parameters служит для добавления, редактирования и удаления параметров.

Вкладка Parameters

Нажатие кнопки Add приводит к появлению списка доступных ячеек, соответствующих заданному критерию. Также существует объектная модель, поддерживающая превращение ячейки в параметр.

Завершая рассмотрение данной темы, следует отметить, что существует возможность установки значений ячеек, которые не видны пользователю, — это позволяет, например, представить в браузере пустую таблицу и предоставить клиенту возможность ее заполнения.

Создание компонентов для анализа данных

Вторым типовым сценарием использования Excel Services является предоставление данных, хранимых в электронных таблицах, для анализа. На следующем рисунке показана панель (dashboard), созданная средствами Excel 2007, Excel Services и SharePoint Portal Server без единой строки кода.

Панель анализа данных

Все интерактивные средства, ассоциируемые со сводными таблицами, доступны пользователям непосредственно из браузера. Отметим, что средства анализа, реализованные в Excel 2007/Excel Services, содержат возможность интеграции с SQL Server 2005 и доступ (через специальный набор формул) к средствам создания отчетов — SQL Server Reporting Services.

Использование в клиентских приложениях логики, встроенной в таблицы Excel

Третий типовой сценарий применения Excel Services — это повторное использование бизнес-логики, включенной в таблицы Excel из внешних приложений. Помимо интерфейса на уровне браузера, Excel Services поддерживает доступ к электронным таблицам через Web-сервисы. Это означает, что та же самая электронная таблица, опубликованная на сервере и доступная через браузер, может быть доступна и из приложения, использующего Web-сервисы, предоставляемые Excel Services. Такое приложение может изменять значения в таблице, производить вычисления, извлекать всю или часть таблицы и т.п.

Приведем несколько примеров функций, реализованных в Web-сервисах Excel Services.

Вот пример кода, использующего некоторые из перечисленных методов Web-сервиса Excel Services:

// Создать экземпляр прокси и аутентифицироваться

ExcelService ws = new ExcelService();

ws.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Открыть «книгу» Excel

Status[] status = null;

string sessionId = null;

sessionId = ws.OpenWorkbook(textBoxURL.Text, “en-US”, “en-US”, out status);

// Задать значения ячеек

status = ws.SetCell(sessionId, “Mortgage Calculator”, “CustomerName”, textBoxCustomerName.Text);

status = ws.SetCell(sessionId, “Mortgage Calculator”, “MortageAmount”, textBoxAmount.Text);

status = ws.SetCell(sessionId, “Mortgage Calculator”, “InterestRate”, textBoxInterest.Text);

status = ws.SetCell(sessionId, “Mortgage Calculator”, “MortgageLength”, textBoxLength.Text);

// Выполнить вычисления и получить результат

status = ws.CalculateWorkbook(sessionId, CalculateType.Recalculate);

object result = null;

result = ws.GetCell(sessionId, “Mortgage Calculator”, “Payment”, true, out status);

// Показать результат и закрыть «книгу»

if (result != null)

MessageBox.Show(“You pay” + result.ToString());

status = ws.CloseWorkbook(SessionId);

Таким образом, инвестиции в бизнес-логику, реализованную внутри электронных таблиц, могут быть сохранены и такая логика может быть повторно использована в других приложениях без необходимости переписывания уже существующего кода.

Расширение функциональности Excel Services: пользовательские функции

В Excel Services поддерживается возможность расширения функциональности за счет создания собственных функций (User Defined Functions, UDF). Такие функции могут использоваться в формулах для вычисления значений ячеек электронной таблицы и таким образом расширять набор функций, входящих в состав Excel Services.

UDF в Excel Services — это код, написанный на управляемом коде и сохраненный в виде стандартных сборок .NET 2.0. Сборка должна содержать как минимум один класс, помеченный атрибутом [UdfClass], и как минимум один метод, помеченный атрибутом [UdfMethod]. UDF может выполнять любые действия (в реальности это зависит от настроек Code Access Security), включая вызов внешнего неуправляемого кода, Web-сервисов и т.п.

При добавлении UDF к Excel Services вы расширяете набор существующих функций Excel — эта операция схожа с функциональностью XLL и Automation Addins в Excel.

Первый шаг при создании UDF — это добавление к проекту библиотеки классов ссылки на сборку Microsoft.Office.Excel.Server.Udfs.dll, которая, в частности, определяет необходимые для реализации UDF атрибуты.

Затем мы создаем класс, который будет содержать метод(ы) нашей функции, и указываем соответствующие атрибуты. После компиляции нашего проекта мы получаем сборку, содержащую UDF, которую мы должны подключить к Excel Services. Для этого мы переходим на страницу администрирования сервисов — User Defined Functions — и нажимаем кнопку Add User-Defined Function Assembly.

Диалоговая панель Add User-Defined Function Assembly

После этого в диалоговой панели заполняем соответствующие поля: местоположение сборки, статус сборки (локальная или относящаяся к глобальному кэшу сборок), активизация сборки и ее описание. После этих манипуляций сборка, содержащая UDF, готова к использованию.

Чем не является Excel Services

При рассмотрении Excel Services важно понимать, чем не являются серверные компоненты Excel 2007. Ниже приведен список функциональности, не реализованной в первой версии серверных компонентов:

• средство создания электронных таблиц — с помощью браузера нельзя создавать или редактировать электронные таблицы — для этих целей служит программный продукт Excel 2007, распространяемый как отдельно, так и в составе различных изданий Microsoft Office 2007;
• средство многопользовательской работы с таблицами — в данной версии Excel Services каждый пользователь работает с таблицей в своей собственной сессии. Сервер открывает таблицу в режиме «только чтение» и каждый пользователь получает экземпляр таблицы, сохраненный в памяти сессии. Таблица загружается в память сервера один раз, но все манипуляции, например установка фильтров, сохраняются в отдельной сессии и используются для перерисовки таблицы в браузере конкретного пользователя;
• распределение нагрузки по серверам при проведении вычислений — Excel Services поддерживает механизмы распределения нагрузки при вычислениях и позволяет производить вычисления в более чем одной таблице (каждый запрос выполняется в отдельном потоке), но в данной версии сервисов не поддерживается разбиение одной таблицы с комплексными вычислениями на несколько серверов. Текущий дизайн сервисов рассчитан на масштабирование при большом числе таблиц и увеличении числа запросов к серверу.

Ограничения Excel Services

В первой версии серверных компонентов  — Excel Services — существует ряд ограничений, которые мы кратко здесь рассмотрим.

Говоря о возможных ограничениях в Excel Services, следует понимать, что Excel 2007 является мощным, комплексным приложением, предназначенным для табличной обработки данных и их анализа, и в первой версии серверных компонентов вряд ли может быть в полном объеме реализована функциональность продукта класса Excel 2007. В первую очередь набор функциональности, реализованной в Excel Services, был направлен на поддержку рассмотренных выше сценариев. В следующих версиях серверных компонентов планируется расширить функциональность.

Рассмотрим некоторые аспекты поддержки функциональности Excel 2007 в Excel Services:

• загрузка файлов — Excel Services не загружает все файлы Excel. В некоторых случаях, когда Excel Services не поддерживает определенную функциональность Excel 2007, не загружается тот или иной файл. Так, Excel Services не поддерживает выполнение VBA-кода;
• просмотр файлов — в ряде случаев Excel Services не может отобразить объект из электронной таблицы таким образом, как Excel 2007. К указанным объектам, в частности, могут относиться расширения деловой графики в Excel 2007: при отображении через Excel Services графики могут не иметь всех новых визуальных эффектов;
• взаимодействие с файлами — некоторые интерактивные функции не поддерживаются на уровне браузера. Например, пользователи лишены возможности добавления или перераспределения полей в сводных таблицах при работе с электронными таблицами, отображаемыми в браузере;
• создание файлов — как уже было отмечено, этот сценарий не входит в число поддерживаемых в данной версии Excel Services. Например, нельзя вставить график или изменить формулу в электронной таблице, открытой средствами Excel Services.

После перечисления основных ограничений рассмотрим их более подробно. Ниже показаны характеристики электронных таблиц, которые не могут быть загружены средствами Excel Services:

• таблицы с кодом — к таким таблицам относятся таблицы с VBA-макросами, компонентами форм, компонентами инструментальных панелей, панелями MS 5.0 Dialogs и XML-таблицы;
• таблицы, защищенные средствами IRM;
• таблицы, содержащие компоненты ActiveX;
• таблицы со встроенными SmartTags;
• сводные таблицы, основанные на диапазонах множественной консолидации (multiple consolidation);
• таблицы с внешними ссылками (ссылки на другие таблицы);
• таблицы, сохраненные в режиме Formula View;
• таблицы с расширенным использованием XML (XML Expansion Pack);
• таблицы, содержащие XML Maps;
• таблицы со средствами визуализации данных;
• таблицы, содержащие Query Tables, SharePoint Lists, Web Queries и Text Queries;
• таблицы со ссылочными компонентами (reference add-ins);
• таблицы, использующие функцию выполнения в реальном времени (RTD);
• таблицы с защитой самих таблиц и «книг»;
• таблицы со встроенной графикой, имеющие фоновые изображения на уровне ячеек или таблиц, использующие AutoShapes и WordArt;
• таблицы, содержащие рукописные аннотации (Ink Annotations);
• таблицы с диаграммами;
• таблицы с DDE-связями.

Самый простой способ определить, будет ли таблица открываться средствами Excel Services, — это использовать описанную функцию просмотра в браузере непосредственно после публикации таблицы на сервере. В том случае, если таблица содержит неподдерживаемые данной версией Excel Services функции, вы получите сообщение об ошибке, представленной на рисунке ниже.

Сообщение об ошибке при попытке открыть таблицу средствами Excel Services

Некоторые элементы таблиц не могут быть отображены средствами Excel Services. К таким элементам, в частности, относятся:

• панели — Split and Freeze Panes;
• верхние и нижние заголовки;
• просмотр в режиме Page Layout;
• шаблоны заполнения ячеек (Cell patterns);
• средства масштабирования (Zoom);
• свойства членов Analysis Services, отображаемые в подсказках (tooltips);
• некоторые варианты форматирования ячеек, а именно диагональные рамки, а также типы рамок, не поддерживаемые на уровне HTML.

К следующей группе относится функциональность Excel 2007 с ограниченными элементами интерактивности при отображении средствами Excel Services. К такой функциональности относятся:

• графики по сводным таблицам (PivotCharts) — такие графики не будут интерактивными при отображении в браузере, то есть у вас не будет возможности непосредственно задать фильтр для графика, но если обратиться к сводной таблице, служащей источником данных для графика, последний изменится соответствующим образом;
• сводные таблицы — поддерживается сортировка, фильтрация, раскрытие и закрытие регионов данных для сводных таблиц, отображаемых в браузере, но пользователям будут недоступны функции drill-through, использование служб SQL Server Analysis Services, добавление и удаление полей или их перемещение;
• функция GoTo — в браузере пользователи могут использовать либо режим Named Object View, либо предоставляемые элементы навигации для перемещения по электронной таблице. Функции GoTo не поддерживаются;
• увеличение и уменьшение изображения — функции Minimize и Maximize не поддерживаются;
• переключение в режим Page Layout View, служащий для упрощения печати таблиц в Excel 2007, не поддерживается в браузере;
• Goal Seek, Scenario Manager, аудит формул, изменение режима вычислений и окно Watch не поддерживаются.

Что касается создания и редактирования таблиц, то, как уже отмечалось, этот сценарий не являлся ключевым в первой версии Excel Services. Перечисленные здесь функции не поддерживаются в серверных компонентах Excel 2007:

• вставка новой книги;
• вставка объекта;
• создание таблицы;
• вставка сводной таблицы;
• вставка сводного графика;
• редактирование формул;
• вставка данных в ячейки (кроме рассмотренных выше параметризованных ячеек);
• задание имен;
• изменение форматирования ячеек;
• изменение правил и условий форматирования;
• группировка и отмена группировки — имеется возможность использования групп, заданных средствами Excel 2007;
• задание консолидированных диапазонов;
• преобразование текста в колонки.

В Excel Services поддерживается рендеринг графиков, созданных средствами Excel 2007, но следует ожидать некоторых визуальных различий в ряде типов графиков — например не поддерживаются отсечки, тени, отражения и некоторые другие новые расширенные графические функции, появившиеся в Excel 2007.

Выше мы упоминали, что в Excel Services не поддерживается загрузка электронных таблиц, содержащих VBA-код. В Excel Services поддерживаются таблицы, содержащие пользовательские функции на управляемом коде. Вместо дополнений, которые не поддерживаются, можно использовать функции-переходники.

Обзор архитектуры Excel Services

Завершим наше обсуждение сервисов Excel Services кратким рассмотрением архитектуры серверных компонентов. Как уже отмечалось, Excel Services построен на технологиях SharePoint Portal Server. Excel Services состоит из трех основных компонентов:

• сервис вычислений — Excel Calculation Service;
• сервис доступа через Web — Excel Web Access;
• сервис поддержки Web-сервисов — Excel Web Service.

Взаимодействие этих сервисов отражено на представленной диаграмме.

Кратко опишем назначение этих компонентов.

Excel Web Access — представляет собой компонент Web-part в SharePoint, задачей которого является отрисовка (генерация HTML) книги Excel на Web-страницу. Для конечных пользователей, обращающихся к Excel Services, это самый видимый компонент из всего набора сервисов.

Excel Web Services — обеспечивает программный доступ к Excel Services. Представляет собой Web-сервис, расположенный в SharePoint. Методы этого Web-сервиса могут использоваться для разработки приложений, применяющих вычисления, реализованные на основе Excel Services, и для автоматизации обновлений электронных таблиц.

Архитектура Excel Services

Excel Calculation Service — компонент, загружающий электронную таблицу, выполняющий вычисления, обновляющий внешние данные и отвечающий за поддержание состояния сессии.

Помимо перечисленных компонентов, в Excel Services входит прокси-уровень, обеспечивающий коммуникации между компонентами, доступными через Web, и сервером приложений в многосерверных конфигурациях. Также к задачам данного компонента относится обеспечение балансирования нагрузки в тех случаях, когда задействуется более одного сервера приложений.

Модель безопасности, используемая в Excel Services, базируется на инфраструктуре, предоставляемой SharePoint. Excel Services применяет SharePoint для аутентификации, авторизации, а также для аудита выполняемых операций.

Excel Services оптимизирован для сценариев, в которых ряд пользователей обращается к одной и той же электронной таблице. Оптимизация достигается за счет использования различных механизмов кэширования — как для электронных таблиц, так и для применяемых ими источников данных.

Масштабирование Excel Services достигается за счет добавления дополнительных процессоров или памяти на сервере, где выполняются соответствующие сервисы. Excel Services поддерживает конфигурации с несколькими процессорами, равномерно распределяя запросы между ними, а также конфигурации с 64-битными процессорами. В ряде случаев возможен различный подход к вопросам масштабирования сервера приложений и Web-компонентов. Например, если снижается производительность при отрисовке электронных таблиц средствами Excel Web Access, то необходимо увеличить мощность сервера, где выполняются Web-компоненты, а в случае потери производительности при вычислениях следует повысить мощность сервера приложений.

КомпьютерПресс 8'2006