VSTO-автоматизация: офисные микросервисы в помощь кредитному аналитику

В ИД «Регламент» в пятом номере журнала «Банковское кредитование» вышла статья Владимира Козлова о технологиях Visual Studio Tools for Office (VSTO). Они позволяют создавать собственный функционал прямо внутри приложения Microsoft и экономить время аналитика на обработку кредитной заявки. Рассмотрим два кейса работы с заявками на кредит и покажем, как можно радикально снизить временные затраты, не входя в долгий и сложный ИТ-проект.

Несмотря на обилие прикладных программных решений, большую часть рабочего времени сотрудник негосударственного банка среднего размера все еще тратит в приложениях Microsoft — таких как Microsoft Word, Microsoft Excel, Microsoft PowerPoint. В первом пишутся отчеты, во втором ведутся реестры и делаются расчеты, в третьем готовятся слайды для выступлений. Экономия рабочего времени и повышение эффективности в этом аспекте зачастую сводятся к тренингам по освоению приемов работы в этих программах, крайне редко — к изучению скриптового языка VBA, доступного для всех этих продуктов «из коробки».

Мы же, не претендуя на ИТ-глубину и новизну, хотели бы предложить банковским топ-менеджерам радикальный способ повысить эффективность рабочего времени таких сотрудников, основанный на технологиях VSTO, — а именно создание собственного функционала в приложении Microsoft.

Функционал может быть написан на самом современном языке, например C# версии 7.3, что выгодно отличает его от устаревшего Visual Basic For Applications (VBA), и интегрирован в ИТ-контур организации малыми усилиями.

Мы остановимся на создании микросервисов — не с точки зрения архитектуры, а с точки зрения задач, решаемых с помощью VSTO.

На диаграмме Венна (рис. 1) VSTO вполне могут претендовать на класс 12345.

Рисунок 1

Диаграмма Венна: соотношения требований к ИТ-продуктам

Действительно, работе с продуктом не надо обучать, он недорог в разработке и поддерживать его несложно. При этом его довольно легко согласовать с ИТ-службами, так как по сути это доработанный продукт компании Microsoft. А потенциал и технический арсенал огромны, так как язык C# отвечает самым современным требованиям к производительности и быстродействию и технологии VSTO созданы для .Net Framework (4.6).

 

Кейс 1: заявка на получение финансового продукта в MS Word

Попробуем решить задачу-вызов для микросервисного продукта: прочитать структуру схемы базы данных, записать в базу данных определенные поля из заявки и даже применить приемы AutoML, не выходя из MS Word и не пользуясь VBA, архаичность которого известна многим.

Команда из нескольких специалистов, если она ранее работала с продуктами Microsoft, может создать проект, пользуясь сравнительно небольшими ресурсами. Архитектура проекта показана на рис. 2.

Рисунок 2

Архитектура проекта

Ключевые блоки и особенности системы:

— Мы рекомендуем создавать приложения на уровне документа. Такой подход позволяет наиболее эффективно решать задачу пользователя и не загружать исходный код в приложение при работе с иными задачами.

— Globals — это VSTO-специфичные глобальные переменные, такие как «Документ», «Range», «Приложение». Так как под капотом у этих переменных Microsoft.Office.Interop.Word и каждый вызов его довольно дорогой, рекомендуем использовать некий вариант MyGlobals — статический класс с хранением необходимых переменных.

— VSTO позволяют перехватить практически все события приложения, не залезая в Component Object Model (открытие, закрытие, сохранение), и построить практически любые интерфейсы, привычные для пользователей Microsoft.

— CRUD-операции поддерживаются с помощью большого количества ОDBC-драйверов с открытым исходным кодом.

— Асинхронные операции оптимально поддерживать через класс backgroundWorker, а расчеты — с помощью любой ML-библиотеки, которыми так богат современный github.

— В примере мы будем использовать ContentControl как некий UI-элемент работы с данными из БД.

Что мы получим, собрав всю систему:

— При открытии документа пользователь вводит логин и пароль для входа в базу данных (документ не откроется, если они будут неверными).

— Прямо из Word видна вся структура баз данных (рис. 3). Более того, аналитик может сохранить в базу или взять из базы те сведения, которые обычно банки не хранят или «перебивают» во внутренних системах.

Для примера использовано ПО YesSystems компании Raisk, в котором treeview в правой части экрана совпадает по структуре и содержанию с таблицами базы данных, что позволяет пользователю напрямую работать в БД.

 Рисунок 3

Интеграция документа в MS Word с базой данных

— Пользователь может создавать внутри документа собственную разметку, которая позволит записывать данные «на лету» или распознавать их (рис. 4).

Рисунок 4

Пример создания пользовательской разметки

Что это дает? Хранение всех параметров каждой заявки в базе без дополнительной нагрузки на банковских сотрудников.

Кто из руководителей не мечтал бы построить, например, такую аналитику:

— срок рассмотрения заявки в разрезе регионов;

— увеличение залоговой массы в процессе движения заявки;

— соотношение суммы кредита и процентной ставки из заявок с выданной суммой кредита по месяцам в группировке по отделениям?

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

Однако такая гибкость требует знания объектной модели Word, что тяжело дается ИТ-службам «с нуля». Вот пример поиска и замены слов в тексте:

object matchCase = false;

object matchWholeWord = true;

object matchWildCards = false;

object matchSoundsLike = false;

object matchAllWordForms = false;

object forward = true;

object format = false;

object matchKashida = false;

object matchDiacritics = false;

object matchAlefHamza = false;

object matchControl = false;

object read_only = false;

object visible = true;

object replace = 2;

object wrap = 1;

Microsoft.Office.Interop.Word.Application doc = Globals.ThisDocument.Application;

doc.Selection.Find.Execute(ref findText, ref matchCase, ref matchWholeWord,

ref matchWildCards, ref matchSoundsLike, ref matchAllWordForms, ref forward, ref wrap, ref format, ref replaceWithText, ref replace,

ref matchKashida, ref matchDiacritics, ref matchAlefHamza, ref matchControl);  

Здесь много вещей, которые надо знать изначально. Вот таким нетривиальным образом можно собрать все ContentControls (те самые «рамочки» со значениями из базы):

List<ContentControl> ccList = new List<ContentControl>();

List<ContentControl> ccListfin = new List<ContentControl>();

Range rangeStory;

foreach (Range range in wordRng.Document.StoryRanges)

    rangeStory = range;

do

     try

        {

         foreach (ContentControl cc in range.ContentControls)

            ccList.Add(cc);

         foreach (Shape shape in range.ShapeRange)

             foreach (ContentControl cc in shape.TextFrame.TextRange.ContentControls)

                ccList.Add(cc);

    }

        catch (COMException) { }

        rangeStory = rangeStory.NextStoryRange;

     }

while (rangeStory != null);

foreach (var cc in ccList)

if (cc.Range == wordRng || wordRng.InRange(cc.Range))//equal or intersect

        ccListfin.Add(cc);

return ccListfin;

Мы рекомендуем использовать именно ContentControls для такого рода взаимодействия с пользователем. Различные «бонусы» в виде склонения-спряжения, справочников имен, фамилий, адресов, datetimePicker’ов мы даже не рассматриваем — они очевидны и довольно просты в реализации.

Огромный плюс использования VSTO — возможность применения AutoML-библиотек или современного языка программирования в расчетах. Так, например, можно дообучить прямо внутри Word-документа AutoML-модель следующим псевдокодом (библиотека):

MLContext mlContext = new MLContext();

// Run AutoML binary classification experiment

ExperimentResult<RegressionMetrics> experimentResult = mlContext.Auto()

.CreateRegressionExperiment(ExperimentTime)

.Execute(trainingDataView, LabelColumnName, progressHandler: new RegressionExperimentProgressHandler());

var creditApplicationSample = new Application()

{

//params

};

// Create prediction engine

var predEngine = mlContext.Model.CreatePredictionEngine< Application, ApplicationPrediction>(model);

// Score

var predictedResult = predEngine.Predict(creditApplicationSample);

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

 

Кейс 2: просмотр содержимого баз данных прямо в MS Excel

Популярность такого нишевого ИТ-продукта, как CRUD-клиент в Excel-интерфейсе, растет. Почему — показано на рис. 5.

Рисунок 5

Интеграция документа в MS Excel с базой данных

Мы видим прямую навигацию в базе данных с возможностью «подсказок», всплытия дополнительных окон и прямого редактирования данных базы прямо из Microsoft Excel.

В отличие от предыдущего кейса, данный кейс написан на Visual Basic 15.0, физически данные хранятся в PostgreSQL, используется библиотека npgsql.

Отметим важные технологические аспекты, без которых решение не могло быть реализовано:

— Здесь VSTO также используются на уровне документа и выгодно отличаются от приложений, например, Bloomberg: их так просто не выгрузить из ленты, а пользоваться ими нужно не каждый день.

— Без этой строки в ThisWorkbook_Startup() всплывающие панели создать крайне проблематично:

AllAppPanes = Globals.Factory.CreateActionsPane(Nothing, Nothing, "Panes", "Panes", me)

ActiveWindow.Zoom должен быть равен 100, иначе они не сработают.

— В данном решении мы реализовали довольно сложную объектную модель, работающую параллельно с «родной» моделью Excel. Это нужно прежде всего для скорости работы с данными, ведь известно, что в Excel в десятки раз быстрее вставлять массив в Range определенного размера, чем менять значение каждой ячейки.

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

With wsact.Worksheets(SheetNumber)

             rowsUsed = .Cells.Find(What:="*", _

                                           After:=.Range("A1"), _

                                           LookAt:=Excel.XlLookAt.xlPart, _

                                           LookIn:=Excel.XlFindLookIn.xlFormulas, _

                                        SearchOrder:=Excel.XlSearchOrder.xlByRows, _

                                        SearchDirection:=Excel.XlSearchDirection.xlPrevious, _

                                           MatchCase:=False).Row

Поиск и отладка параметров для такой простой, казалось бы, функции в документации Microsoft заняли несколько десятков часов. Таких функций пришлось создать около 30.

Отдельная сложность — маппинг типов данных: Excel по умолчанию возвращает массив типа object, под который средства npgsql не адаптированы.

Однако наши усилия окупаются. Из рис. 6 видно, что аналитик работает в привычном ему Excel-интерфейсе с большими данными — открытыми данными Росстата по финансовым результатам компаний.

Рисунок 6

Пример работы с данными Росстата в интерфейсе Excel

Крайне удобный вариант своего хранилища, особенно при наличии полного контроля за тем, что «внутри» ваших больших данных.

Вместе с тем прототип подобной системы ваши ИТ-специалисты могут собрать за несколько дней. И это лишь базовые возможности VSTO-приложений.

Один высокопоставленный сотрудник крупнейшего государственного банка в свое время обронил фразу «наш банк управляется прямо из Power Point». У внимательного читателя данной статьи есть полный арсенал средств, которые позволят на деле показать, что это возможно. Надо только немного переориентировать свои ИТ-службы с модных контейнеров, облаков и веб-интерфейсов на богатый и все еще недооцененный мир .NET.