Изменение калькуляторов расчета на сайте без программистов


    Привет, Хабр!

    В данной статье расскажем, какое применение мы нашли для технологии Excel Services, входящей в состав сервера SharePoint, для построения калькуляторов банковских продуктов.

    В 2011 году мы разрабатывали сайт банка ВТБ24, одной из важных задач, которую должен решить новый сайт, было предоставление удобного инструмента для создания и обслуживания калькуляторов и заявок, связанных с продуктами банка. Например, для продукта кредитования «АвтоСтандарт» банк предоставляет пользователю сайта возможность самостоятельно рассчитать с помощью калькулятора сумму выплат и в случае, если все устраивает, можно отправить заявку.

    Как обычно происходит процесс обновления калькулятора


    В большинстве случаев логику расчета калькуляторов сотрудникам банка удобно оформлять и согласовывать в виде Excel файлов, поэтому процесс внесения изменений в калькулятор на сайте может выглядеть так:


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

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

    К чему стремимся


    Идеальный вариант решения данной задачи — предоставить клиенту возможность самостоятельно менять параметры расчетов и создавать новые калькуляторы продуктов.


    С точки зрения технической реализации это может выглядеть так:
    • Все считает сам Excel, никакого переноса формул на другие языки (JS, C# и пр.).

    1. используем технологию Excel Services или библиотеки для работы с Excel;
    2. все данные с веб-формы, которые вводит пользователь, записываются на лист ввода данных;
    3. выполняется расчет книги;
    4. результаты вычислений считываются и отображаются на веб-форме.

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

    • Создаем конструктор веб-форм с мэппингом на поля Excel.

    1. определяем все поля, которые необходимы для автоматизации имеющихся калькуляторов;
    2. на основании списка полей получаем элементы управления, с помощью которых можно автоматизировать все калькуляторы (чекбокс, радигруппа, слайдер и прочее);
    3. формируем лист настроек и указываем элементы управления веб-формы, их значения при инициализации, граничные значения для валидации и любые другие параметры, на которые хватит фантазии;
    4. также на листе настроек указываем, из каких ячеек будет считываться результат расчета, который увидит пользователь на странице.

    Выгода: Клиент теперь сам может достаточно оперативно создать калькулятор нового типового продукта без привлечения программиста.

    Вот так, например, выглядит лист ввода данных и результатов расчетов для калькулятора кредита «АвтоСтандарт» сайта ВТБ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

    ADV/web-engineering co.

    46,00

    Компания

    Поделиться публикацией

    Похожие публикации

    Комментарии 16
      +16
      Там на одном из скриншотов виден обрывочек функции ЕСЛИ, который сразу напомнил это:

      Скрытый текст
      ZsaZsaZu: экселевская функция ЧИСТРАБДНИ мейкс ми край. звучит как глагол.
      ZsaZsaZu: пойду чистрабдну
      ZsaZsaZu: а еще есть функция РАЗНДАТ.
      ZsaZsaZu: «он был красив и разндат»
      ZsaZsaZu: КУБЧИСЛОЭЛМНОЖ СУКА!!!
      ZsaZsaZu: есть еще древнегреческий ученый ДСТАНДОТКЛ
      ZsaZsaZu: ДАТАКУПОНДО — это боевое искусство такое.
      ZsaZsaZu: ПОИСКПОЗ — функция для тех, кому нехватает разнообразия
      ZsaZsaZu: вот еще греческого философа нашла: СРОТКЛ
      ZsaZsaZu: я прнс ЛГРФПРИБЛ
        0
        Я понимаю, что статья совсем не о том, но сразу бросается в глаза:
        1) Ползунки сроков и сумм — при клике уезжают из под курсора. UI-дизайнеру незачёт.
        2) То ли уже хабраэффект, то ли общее качество Шарепойнта — но расчёт при изменении параметров не производится, Ajax, видимо, не получает ответ. Если в это время попробовать обновить страницу — алерт error:error (отладочный код на боевом сервере?).
        3) После обновления во втором пункте — к странице не догружаются скрипты и стили (хотя, возможно, это прокси на моей стороне чудит).
          0
          1) да есть такое: там стрелочка влево-вправо, при клике происходит смещение, что действительно не очень удобно.
          2, 3) похоже у вас действительно что-то с прокси-сервером. Попробовали большинство калькуляторов — все работает без ошибок. На каком у вас не получается посчитать?
            0
            Пункт 3 — чёрт с ним, наверняка прокси.
            По второму — сейчас считает, но не моментально, а пару секунд. Ну и обновление страницы, пока крутится прелоадер — error:error. Калькулятор — тот, на который первая ссылка в посте. Скриншот.
          0
          У вас на последнем скриншоте что-то страшное: 100000р под 15% в год в итоге вытекают в сумму 179726,29р за год, немножко не сходится.
            +1
            Спасибо, исправили скриншот.
            –13
            Привет, Никита! Помнишь холодную зиму 2011? :-)
            Как твой ниисан-жук? Извини, я не хотел про него говорить плохо :-)
              +1
              А не проще было написать админку для этого? о_0
                +3
                Интересно, у меня слегка другой подход для этого — тулза для автоматической кодогенерации на основе Excel таблиц.
                  0
                  Да, тоже хороший подход. Единственное, что для обновления диапазонов входных данных и результатов придется снова сгенерировать классы — а это уже рядовой сотрудник не сделает. В нашем случае требуется только загрузить файл в правильном формате.
                    0
                    На самом деле у меня чуточку другой юз-кейс, но иметь возможность автоматического обновления тоже хочется. Самое сложное — это проблема поддержки всех встроенных в Excel функций.
                  0
                  шарик-то 13-й… а в 10-м все также?
                    0
                    Сайт втб24 сделан на sharepoint 2010 (судя по докладам ADV на конференциях :) ).

                    А собственно что там может быть не так же? Используются простейшие операции: положить значения в ячейку, пересчитать, получить значения из ячейки. По-моему даже в 2007 это всё уже можно было сделать.
                      0
                      угу. но на картинке шарик 13-й.
                        0
                        Все верно — сайт ВТБ24 на SharePoint 2010, а пример на 2013 версии. Из отличий Excel Servcies для этих двух версий мы нашли только то, что в 2013 версии они менее требовательны к формату файлов. Например, сложный файл с макросами может не загрузиться в Excel Services 2010, но загрузиться в 2013 (при этом ни там ни там макросы не исполняются, но все расчеты работают). У Microsoft указаны некоторые ограничения. К сожалению, подробной документации по этой теме не так много и проще всего для сложных Excel делать небольшие пилотные проекты, на которых проверить что все работает как ожидается
                      0
                      У нас на сайте ценообразование и создание заказов (с резервированием, контролем остатков и прочим) онлайн в 1с через 1сные веб-сервисы.

                      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                      Самое читаемое