SharePoint Excel Services. Создаем кредитный калькулятор

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

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

Excel

Начнем с Excel-файла, в котором будут производится расчеты простого кредита. В качестве изначального файла я нашел на просторах сети кредитный калькулятор, немного его отредактировал, убрал лишнее и получил крайне простой кредитный калькулятор в виде Excel-файла.

К этому Excel-файлу мы ещё вернемся, а пока перейдем к Excel Services.

SharePoint Excel Services

Теперь необходимо пояснить как происходит работа с Excel-файлом. Любое такое взаимодействие с происходит через REST API: вызовы идут к /_vti_bin/ExcelRest.aspx, который в свою очередь обращается к Excel-файлу и возвращает данные в одном из форматов:

  • atom
  • workbook
  • image
  • html

Получается примерно вот так:

Для модификации или чтения данных ячеек в Excel-файле средствами Excel Services можно обращаться к ним по имени (если заданы именованные диапазоны) или в стандартной нотации вида A1:A2 (в URL-адресе вместо : необходимо указывать |), для получения таблиц, сводных таблиц или диаграмм обращаться к ним следует по соответствующему имени. В любом случае использование Excel Services REST API сводится к формированию URL-адреса.

Формируем URL для Excel Services REST API

У нас есть файл, который, например, расположен в библиотеке документов на корневом сайте по адресу: http://SPServer/ExcelDocLib/CreditCalc.xls. Для доступа к нему с помощью Excel Services формируем URL следующего вида:

http://SPServer/_vti_bin/ExcelRest.aspx/ExcelDocLib/CreditCalc.xls

Добавив в конец этого адреса /Model мы получим данные в формате ATOM, описывающие доступные диапазоны, диаграммы, таблицы и сводные таблицы:

SharePoint Excel Services ATOM feed

SharePoint Excel Services ATOM feed

Для получения именованного диапазона в формате HTML, что необходимо в нашем примере для вывода графика платежей, URL-адрес запроса будет выглядеть вот так:

http://SPServer/_vti_bin/ExcelRest.aspx/ExcelDocLib/CreditCalc.xls/Model/Ranges('ИмяДиапазона')?format=html

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

Динамический диапазон в Excel

В Excel-файле у нас есть таблица:

График платежей по кредиту в Excel

График платежей по кредиту в Excel

Ширина таблицы нам известна и изменяться не будет: выводим 5 столбцов. С высотой сложней: она равна [Кол-во платежей] + 1 (чтобы захватить заголовок таблицы). Информация о кол-ве платежей (срок кредита в месяцах) хранится в именованной ячейке Months на листе Settings. Используя функцию СМЕЩ (OFFSET) получаем определение необходимого нам диапазона:

=СМЕЩ(Payments!$A$1; 0; 0; Months + 1; 5)

Динамический диапазон в Excel

Динамический диапазон в Excel

URL-адрес запроса для получения этой таблицы в формате HTML получается следующий:

http://SPServer/_vti_bin/ExcelRest.aspx/ExcelDocLib/CreditCalc.xls/Model/Ranges('PaymentsGrid')?format=html

Изменение данных в Excel

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

http://SPServer/_vti_bin/ExcelRest.aspx/ExcelDocLib/CreditCalc.xls/Model/Ranges('PaymentsGrid')?Ranges('Months')=24&format=html

Аналогично поступает с остальными входными параметрами и получаем итоговый URL:

http://SPServer/_vti_bin/ExcelRest.aspx/ExcelDocLib/CreditCalc.xls/Model/Ranges('PaymentsGrid')?Ranges('CreditValue')=150000&Ranges('Percent')=.15&Ranges('Months')=24&format=html

Проценты указываем, исходя из того, что единица - это 100%. Получаем таблицу в формате HTML со стилями соответствующими Excel-файлу:

Excel таблица в формате HTML

Excel таблица в формате HTML

Осталось сделать интерфейс

Веб-часть

Для ввода данных создадим простую веб-часть, задача которой сводится к следующему: сформировать URL в зависимости от введенных значений, запросить HTML по данному URL-адресу и вывести результат на страницу.

Подробно я описывать создание интерфейса не буду. Я не реализовывал обработку ошибок, я не инкапсулировал вызов Excel Services, я не использовал серверный код. Час времени + jQuery UI и получаем кредитный калькулятор на базе SharePoint:

Кредитный калькулятор на SharePoint

Кредитный калькулятор на SharePoint

Данный код будет работать с SharePoint 2010, SharePoint 2013 и Office 365.

Виталий Жуков

Виталий Жуков

SharePoint архитектор, разработчик, тренер, Microsoft MVP (Office Development). Более 15 лет опыта работы с SharePoint, Dynamics CRM, Office 365, и другими продуктами и сервисами Microsoft.

Смотрите также

EntityFramework. Оптимистические блокировки

EntityFramework. Оптимистические блокировки

Linq to Sharepoint. Особенности

Linq to Sharepoint. Особенности

SharePoint 2010. Настройка входящей почты для кастомного списка

SharePoint 2010. Настройка входящей почты для кастомного списка

SharePoint 2010. PeopleEditor. Установка значения

SharePoint 2010. PeopleEditor. Установка значения

Linq to SharePoint. Особенности. Часть 2

Linq to SharePoint. Особенности. Часть 2