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

28 августа, 2015

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, описывающие доступные диапазоны, диаграммы, таблицы и сводные таблицы:

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

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

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

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

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

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

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

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-файлу:

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

Веб-часть

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

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