Привет!
В данной статье расскажем, какое применение мы нашли для технологии Excel Services, входящей в состав сервера SharePoint, для построения калькуляторов банковских продуктов.
В 2011 году мы разрабатывали сайт банка ВТБ24, одной из важных задач, которую должен решить новый сайт, было предоставление удобного инструмента для создания и обслуживания калькуляторов и заявок, связанных с продуктами банка. Например, для продукта кредитования «АвтоСтандарт» банк предоставляет пользователю сайта возможность самостоятельно рассчитать с помощью калькулятора, сумму выплат, и в случае, если все устраивает, можно отправить заявку.
Как обычно происходит процесс обновления калькулятора
В большинстве случаев логику расчета калькуляторов сотрудникам банка удобно оформлять и согласовывать в виде Excel файлов, поэтому процесс внесения изменений в калькулятор на сайте может выглядеть так:
Если необходимо внести какие-то изменения или добавить новый продукт запускается процесс с множеством участников:
- специалисты заказчика согласовывают и описывают задачу (формируется Excel файл с расчетами и некоторое ТЗ);
- ставят задачу исполнителю;
- исполнитель ставит задачу команде разработки на внедрение новой логики расчетов;
- команда добавляет новый калькулятор, либо меняет логику старого.
В зависимости от «масштаба трагедии» этот процесс может занимать продолжительное время. В нашем случает такая ситуация не устраивала клиента, поскольку информация должна появляться на сайте в соответствии с определенным регламентом. Например, новые ставки уже действуют, а калькулятор все еще считает по старым. Команде разработки исполнителя такие однотипные задачи, как правило, тоже не приносят удовольствия, куда лучше создать какой-то новый интересный сервис, чем каждый день менять ставки продуктов и логику расчетов.
К чему стремимся
Идеальный вариант решения данной задачи — предоставить клиенту возможность самостоятельно менять параметры расчетов и создавать новые калькуляторы продуктов.
С точки зрения технической реализации это может выглядеть так:
- Все считает сам Excel, никакого переноса формул на другие языки (JS, C# и пр.).
- используем технологию Excel Services или библиотеки для работы с Excel;
- все данные с веб-формы, которые вводит пользователь, записываются на лист ввода данных;
- выполняется расчет книги;
- результаты вычислений считываются и отображаются на веб-форме.
Выгода: Клиент теперь сам может поменять файл и загрузить новую логику на сайт через веб-интерфейс.
- Создаем конструктор веб-форм с мэппингом на поля Excel.
- определяем все поля, которые необходимы для автоматизации имеющихся калькуляторов;
- на основании списка полей получаем элементы управления, с помощью которых можно автоматизировать все калькуляторы (чекбокс, радигруппа, слайдер и прочее);
- формируем лист настроек и указываем элементы управления веб-формы, их значения при инициализации, граничные значения для валидации и любые другие параметры, на которые хватит фантазии;
- также на листе настроек указываем, из каких ячеек будет считываться результат расчета, который увидит пользователь на странице.
Выгода: Клиент теперь сам может достаточно оперативно создать калькулятор нового типового продукта без привлечения программиста.
Вот так, например, выглядит лист ввода данных и результатов расчетов для калькулятора кредита «АвтоСтандарт» сайта ВТБ24:
Рассмотрим подход к созданию конструктора калькуляторов.
Техническая реализация
Так как в качестве платформы для публичного сайта ВТБ24 в нашем распоряжении был SharePoint 2010, то «движком» для калькуляторов была выбрана служба Excel Services.
В задачи службы не входит визуализация данных, она лишь предоставляет программные интерфейсы для работы с Excel файлом, загруженным в память, и позволяет загрузить/сохранить файл, обращаться к его ячейкам для чтения и записи, произвести вычисления, а также предоставляет REST API и веб-сервис API для этих функций. Более подробно ознакомиться со всеми возможностями можно в этой статье.
Из важных для нашей задачи особенностей службы является то, что файл-источник загружается для сессии пользователя, который работает с порталом, и расчеты на книге одного пользователя не отражаются на результатах расчетов другого пользователя.
Еще мы должны были учесть возможность горизонтального масштабирования на несколько серверов, если мощности одного сервера не хватает для обслуживания большого количества пользователей. Из существенных недостатков можно выделить высокие базовые требования со стороны программного обеспечения (требуется Enterprise редакция сервера SharePoint), а из ограничений – отсутствие поддержки исполнения макросов.
Пришло время собрать свой пример простого калькулятора
Что считаем?
Для того чтобы сосредоточиться на решении, а не на тонкостях банковских продуктов, мы не стали брать сложные реальные формулы, на основании которых, например, работают калькуляторы сайта ВТБ24. Для примера мы выбрали достаточно простую формулу, которая позволяет посчитать ежемесячный платеж:
где
A — ежемесячный платеж,
S — сумма кредита,
p — процентная ставка в месяц,
n — количество месяцев погашения кредита.
Начнем с подготовки файла
Создаем лист, который будет принимать входные данные для расчетов.
Итак, для ввода пользователю доступны поля: сумма кредита, количество месяцев и выбор валюты. Процентная ставка задана константой. В результате мы получаем размер ежемесячного платежа по формуле, которую выбрали выше.
Теперь нужно определить, какие элементы управления будут доступны пользователю для ввода этих значений на веб-форме. Для интереса примера возьмем разные элементы управления. В результате дополнительно получим следующие листы:
Лист с настройками. Содержит все необходимое для того, чтобы веб-форма смогла нарисовать и проинициализировать элементы управления. Например, строка номер 3 указывает на то, что на форме необходимо создать выпадающий список, заполнить его значениями из другого листа Excel (столбец RangeSet), выбрать значение по умолчанию и записывать результат изменения этого поля пользователем в ячейку, указанную в колонке PutCell. Полет фантазии ничем не ограничен, и вы можете придумать любые метаданные, которые нужны для решения задачи (например, в некоторых калькуляторах ВТБ24 используется динамический пересчет минимальных и максимальных значений некоторых полей в зависимости от введенных пользователем данных или используются переключатели режимов – в зависимости от выбранного значения радиогруппы используются разные листы для расчетов).
Лист со справочниками (в нашем случае это курсы валют).
Теперь файл содержит всю необходимую информацию и администратору портала будет достаточно загрузить его на сервер SharePoint и указать в настройках всего два параметра: путь к файлу и название листа с диапазоном ячеек для настроек.
Перейдем к созданию веб-интерфейса и кода конструктора калькуляторов.
Читаем настройки из файла и генерируем классы для будущих контролов
using Microsoft.Office.Excel.Server.WebServices;
…………
/// <summary>
/// Формируем массив настроек на основании информации листа с настройками в Excel
/// </summary>
public List<CalculateControl> GetControlsInfo(List<CalculateControl> controls)
{
List<CalculateControl> controlInfoList = new List<CalculateControl>();
SessionId = ExcelService.OpenWorkbook(ExcelFileUrl, "ru-RU", "ru-RU", out status);
string sheetName = SettingsPath.Split('!')[0];
string settingRange = SettingsPath.Split('!')[1];
// если нам что-то передали, то сначала пересчитываем книгу
if (controls != null)
Recalc(controls);
/* Считываем диапазон с настройками, для нашего примера он задан А3:Н8
* Для упрощения примера:
* - перебираем только строки, столбцы "зашьем" в коде
* - считаем, что у столбца только одна буква (в реальном Excel может быть 2е и больше)
*/
int rowStart = int.Parse(settingRange.Split(':')[0].ToString().Substring(1));
int rowEnd = int.Parse(settingRange.Split(':')[1].ToString().Substring(1));
while (rowStart <= rowEnd)
{
string curRange = "A" + rowStart.ToString();
string cell = ExcelService.GetCellA1(SessionId, sheetName, curRange, false, out status).ToString();
if (cell == string.Empty)
break;
switch (cell)
{
case "DropDown":
controlInfoList.Add(new CalculateControl
{
Type = CalculatorControlType.Dropdown,
Name = GetCellValue(sheetName, "B", rowStart),
DefaultVal = GetSingleVal(GetCellValue(sheetName, "G", rowStart)),
Title = GetSingleVal(GetCellValue(sheetName, "C", rowStart)),
ValueList = GetDropDownVal(GetSingleVal(GetCellValue(sheetName, "D", rowStart))),
PutCell = GetCellValue(sheetName, "H", rowStart)
});
break;
case "Range":
controlInfoList.Add(new CalculateControl
{
Type = CalculatorControlType.Range,
Name = GetCellValue(sheetName, "B", rowStart),
Title = GetCellValue(sheetName, "C", rowStart),
DefaultVal = GetSingleVal(GetCellValue(sheetName, "G", rowStart)),
MinVal = GetSingleVal(GetCellValue(sheetName, "E", rowStart)),
MaxVal = GetSingleVal(GetCellValue(sheetName, "F", rowStart)),
PutCell = GetCellValue(sheetName, "H", rowStart)
});
break;
case "SingleVal":
controlInfoList.Add(new CalculateControl
{
Type = CalculatorControlType.SingleVal,
Name = GetCellValue(sheetName, "B", rowStart),
Title = GetCellValue(sheetName, "C", rowStart),
DefaultVal = GetSingleVal(GetCellValue(sheetName, "G", rowStart)),
});
break;
}
rowStart++;
}
ExcelService.CloseWorkbook(SessionId, out status);
return controlInfoList;
}
private string GetCellValue(string sheetName, string colName, int rowIndex)
{
return GetCellValue(sheetName, string.Concat(colName, rowIndex));
}
private string GetCellValue(string sheetName, string cell)
{
return ExcelService.GetCellA1(SessionId, sheetName, cell, false, out status).ToString();
}
private List<string> GetDropDownVal(string rangeInf)
{
// В этом методе выбираем значения для выпадающего списка из диапазона – в нашем примере это «Валюта’!B2:B4»
// ……………..
}
private string GetSingleVal(string rangeInf)
{
var sheetName = rangeInf.Split('!')[0].ToString().Trim();
var cell = rangeInf.Split('!')[1].ToString().Trim();
return GetCellValue(sheetName, cell);
}
/// <summary>
/// пересчет книги
/// </summary>
/// <param name="controls"></param>
public void Recalc(List<CalculateControl> controls)
{
foreach (CalculateControl control in controls.Where(w => w.Type != CalculatorControlType.SingleVal))
{
ExcelService.SetCellA1(SessionId, control.PutCell.Split('!')[0], control.PutCell.Split('!')[1], control.DefaultVal, out status);
}
ExcelService.CalculateWorkbook(SessionId, CalculateType.Recalculate, out status);
}
Создаем элементы управления и логику инициализации
/// <summary>
/// Рендеринг элементов управления веб-формы калькулятора
/// </summary>
private void DrawCalculator()
{
pnlInput.Controls.Clear();
pnlOut.Controls.Clear();
foreach (CalculateControl control in controlsInfoList)
{
Label lbl = new Label {Text = control.Title };
if (control.Type.Equals(CalculatorControlType.Dropdown))
{
DropDownList dd = new DropDownList {
ClientIDMode = ClientIDMode.Static;
EnableViewState = true;
ID = control.Name;
DataSource = control.ValueList;
SelectedValue = control.DefaultVal;
}
dd.DataBind();
pnlInput.Controls.Add(lbl);
pnlInput.Controls.Add(dd);
pnlInput.Controls.Add(new HtmlGenericControl("br"));
}
if (control.Type.Equals(CalculatorControlType.Range))
{
TextBox tbx = new TextBox {
ClientIDMode = ClientIDMode.Static;
EnableViewState = true;
ID = control.Name;
Text = control.DefaultVal;
}
pnlInput.Controls.Add(lbl);
pnlInput.Controls.Add(tbx);
pnlInput.Controls.Add(new HtmlGenericControl("br"));
}
if (control.Type.Equals(CalculatorControlType.SingleVal))
{
HtmlGenericControl tbx = new HtmlGenericControl("input");
tbx.Attributes["id"] = control.Name;
tbx.Attributes["Value"] = control.DefaultVal;
pnlOut.Controls.Add(lbl);
pnlOut.Controls.Add(new HtmlGenericControl("br"));
pnlOut.Controls.Add(tbx);
pnlOut.Controls.Add(new HtmlGenericControl("br"));
}
}
}
Получаем на странице готовую форму
В результате получаем на странице готовую форму, при изменении входных параметров которой происходит запись в указанные ячейки Excel, пересчет результатов и отображение новых результатов на странице.
Замечание: для упрощения примера мы не стали делать специальные контролы для отображения «диапазонных» значений для суммы и срока кредита. В нашем случае они будут нужны только для клиентской (javaScript) проверки введенных значений.
Конечно, до enterprise-решения данный пример следует существенно доработать:
- вынести логику в отдельный веб-сервис;
- добавить асинхронные ajax запросы на обновление страниц;
- написать логику на js для более дружественного пользователю поведения визуальных элементов;
- сделать более красивый (удобный) дизайн (к примеру, для ввода суммы кредита);
- и т.д.
В данном упрощенном примере мы ставили перед собой другую задачу — показать саму концепцию.
Полезные ссылки
1. Общие сведения о службах Excel
2. Архитектура служб Excel
3. Веб-службы Excel (раздел разработчика).
Никита Клычников, руководитель отдела разработки SharePoint ADV/web-engineering
Рустам Кафеев, разработчик SharePoint ADV/web-engineering
Автор: tourte
