Как скрестить Excel c интерактивным веб-приложением

Не секрет, что Excel довольно мощный инструмент для работы с числовыми табличными данными. Однако средства, которые предоставляет Microsoft для интеграции с ним, далеки от идеала. В частности, сложно интегрировать в Excel современные пользовательские интерфейсы. Нам нужно было дать пользователям Excel возможность работать с довольно насыщенным и функциональным интерфейсом. Мы пошли несколько другим путем, который в итоге показал хороший результат. В этой статье я расскажу, как можно организовать интерактивное взаимодействие Excel c веб-приложением на Angular и расшить Excel практически любым функционалом, который реализуем в современном веб-приложении.



Итак, меня зовут Михаил и я CTO в Exerica. Одна из проблем которые мы решаем — облегчение работы финансовых аналитиков с числовыми данными. Обычно они работают как с исходными документами финансовой и статистической отчетности, так и каким-либо инструментом для создания и поддержания аналитических моделей. Так сложилось, что 99% аналитиков работают в Microsoft Excel и делают там довольно сложные вещи. Поэтому перевести их с Excel на другие решения не эффективно и практически невозможно. Объективно, «облачные» сервисы электронных таблиц до функционала Excel пока не дотягивают. Но в современном мире инструменты должны быть удобны и соответствовать ожиданиям пользователей: открываться по клику мышки, иметь удобный поиск. А реализация в виде разных несвязанных приложений будет довольно далека от ожиданий пользователя.

То с чем работает аналитик выглядит примерно так:



Основные данные тут — это числовые «финансовые показатели», например, доход за 1 квартал 2020 года. Для простоты буду называть их дальше просто «числами». Как видно, почти никакой связи между числами в документе и в аналитической модели нет, все только в голове у аналитика. И работа с заполнением и поддержанием модели занимает часы поиска и перебивания чисел из документа в таблицы, а потом поиск ошибок ввода. Вместе этого мы хотели бы предложить пользователю привычные инструменты: «перетаскивание», вставка через клипборд и так далее, а также быстрый просмотр исходных данных.

Что у нас уже было


К моменту, когда мы начали реализацию интерактивного взаимодействия с Excel в виде, изложенном в этой статье, у нас уже была база данных на MongoDB, бэкэнд в виде REST API на .NET Core, фронтовое SPA на Angular и некоторые другие сервисы. Мы к этому моменту уже пробовали разные варианты интеграции в приложения электронных таблиц, в том числе и в Excel, и все они не пошли дальше MVP, но это тема отдельной статьи.



Связываем данные


В Excel существует два распространенных инструмента, с помощью которых можно решить задачу связывания данных в таблице с данными в системе: RTD (RealTimeData) и UDF (User-Defined Functions). Чистый RTD менее удобен для пользователя в плане синтаксиса и ограничивает гибкость решения. С помощью UDF можно создать кастомную функцию, которая будет работать привычным для Excel-пользователя образом. Ее можно использовать в других функциях, она понимает ссылки типа A1 или R1C1 и вообще ведет себя как надо. При этом никто не мешает использовать механизм RTD для обновления значения функции (что мы и сделали). UDF мы разрабатывали в виде Excel addin с использованием привычного нам C# и .NET Framework. Для ускорения разработки мы использовали библиотеку Excel DNA

Кроме UDF наш addin реализует ribbon (панель инструментов) с настройками и некоторыми полезными функциями по работе с данными.

Добавляем интерактивность


Для передачи данных в Excel и налаживания интерактива мы разработали отдельный сервис, который предоставляет подключение по Websocket при помощи библиотеки SignalR и фактически является брокером для сообщений о событиях, которыми должны обмениваться «фронтовые» части системы в реальном времени. Он у нас называется Notification Service.



Вставляем данные в Excel


В нашем SPA мы подсвечиваем все числа, которые обнаружила система. Пользователь может выделять их, навигировать по ним и т.п. Для вставки данных мы реализовали 3 механизма, чтобы закрыть различные варианты использования:

  • Перетаскивание (drag-and-drop)
  • Автоматическая вставка по клику в SPA
  • Копирование и вставка через клипборд

Когда пользователь инициирует drag’n’drop некоторого числа из SPA, для перетаскивания формируется ссылка с идентификатором этого числа из нашей системы (.../unifiedId/005F5549CDD04F8000010405FF06009EB57C0D985CD001). При вставке в Excel наш addin перехватывает событие вставки и парсит регэкспом вставляемый текст. При обнаружении валидной ссылки на лету подменяет ее на соответствующую формулу =ExrcP(...).

При клике на числе в SPA через Notification Service отправляется сообщение в addin, содержащее все необходимые данные для вставки формулы. Далее формула просто вставляется в текущую выделенную ячейку.

Эти способы хороши, когда пользователю нужно вставлять в свою модель по одному числу, но если надо перенести целую таблицу или ее часть, необходим другой механизм. Наиболее привычным для пользователей представляется копирование через клипборд. Однако этот способ оказался сложнее первых двух. Дело в том, что для удобства вставляемые данные должны быть представлены в нативном для Excel формате — OpenXML Spreadsheet. Наиболее просто это реализуется используя объектную модель Excel, то есть из addin’а. Поэтому процесс формирования клипборда у нас выглядит так:

  • Пользователь выделяет область с числами в SPA
  • Массив выделенных чисел передается на Notification Service
  • Notification Service передает его в addin
  • Addin формирует OpenXML и вставляет его в клипборд
  • Пользователь может вставить данные из клипборда в любое место любой Excel-таблицы.



Несмотря на то, что данные проделывают довольно долгий путь, благодаря SignalR и RTD происходит это довольно быстро и абстрагированно от пользователя. 

Распространяем данные


После того, как пользователь выбрал начальные данные для своей модели, их надо «распространить» все периоды (года, полугодия и кварталы), которые представляют интерес. Для этих целей одним из параметров нашей UDF является дата (период) данного числа (вспоминаем: «доход за 1 квартал 2020 года»). В Excel существует нативный механизм «распространения» формул, который позволяет заполнить ячейки той же формулой с учетом ссылок, заданных в параметрах. То есть вместо конкретной даты в формулу вставлена ссылка на нее, а далее пользователь «распространяет» ее на другие периоды, при этом в таблицу автоматически загружаются «те же» числа из других периодов. 

А что это там за число?


Теперь у пользователя есть модель на несколько сотен строк и несколько десятков столбцов. И у него может возникнуть вопрос, что же там за число в ячейке L123? Чтобы получить ответ, у нас ему достаточно кликнуть на эту ячейку и в нашем SPA откроется тот самый отчет, на той самой странице, где записано кликнутое число, а число в отчете будет выделено. Вот так:



А если это не просто одно число из отчета, а результат некоторых вычислений на числах, взятых из отчета, то мы подсветим все числа, входящие в вычисляемое в Excel выражение. При этом не происходит загрузки всего приложения и подгрузки всех необходимых данных, как в случае перехода по ссылке.

В качестве заключения


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

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

Similar posts

Ads
AdBlock has stolen the banner, but banners are not teeth — they will be back

More

Comments 19

    0

    "Облегчение работуы"? И как, получается?
    Извините, тут бесполезно по одной ошибке вам в личку таскать, вы бы свой текст перед публикацией перечитали хоть раз.


    насышенным и функциональным интерфесом
    0

    А может изучить функционал Excel? И как в нем делать интерфейсы?
    Каким образом вот это вот облегчит визуализацию 255 колонок и 65000 строк?
    У Excel такой багаж наработок в плане анализа.
    А уж в плане получения данных из гетерогенных источников…
    Хорош уже заниматься велосипедостроением!

      +1
      Как я понимаю, это решение для переноса данных из неструктурированных источников «Для чайников». Это реально бывает сложно, Эксель даже csv файлы не всегда корректно обрабатывает, я уже не говорю о случаях, когда на входе html. У меня кое-какая IT-подготовка есть и то бывает сложно с ним справиться. Иногда проще оказывается вообще написать скрипт или сначала загрузить в Таблицы Гугл и сохранить оттуда.
        0

        У меня, как у человека, который в этом "велосипеде" делал почти всё от колёс до руля есть ответы на ваши вопросы (надеюсь):


        1. А как правильно делать интерфейсы в Excel так, чтобы можно было показать пользователю конкретную страницу PDF файла из которого изначально был получен показатель, содержащийся в текущей ячейке? Неужели на связке VBA/MS Forms что-то писать в 2020 году? Может TaskPane со встроенным браузером? Или внешний процесс для отображения PDF файлов… Ну так у нас он есть — браузер называется. Если есть идеи как это сделать лучше/правильней — я с удовольствием выслушаю.
        2. Это решение никаким образом визуализацию не облегчит, потому что оно не для этого предназначенно. Наверное, тут проблема в том, что изначальная задача не очень понятно описана. Она не в том, чтобы получить табличные данные из базы и отобразить их в виде отчёта, она о том, чтобы из данные из разных неструктурированных внешних источников (отсканированные PDF отчёты) подготовить к загрузке в базу и дальнейшему анализу другими средствами.

        Так что я двумя руками за существующие решения, но иногда приходится и велосипеды собирать из подручных материалов :)

        0

        Внешние источники данных вас спасут. Нет необходимости в этом зоопарке.

          0
          К сожалению, не спасут. Из источника данные можно загрузить, но реализовать, например, просмотр исходного документа без дополнительных инструментов тут уже нельзя. Поддержание и визуализация связей с исходным документом, было одной из основных задач.
            0
            Вообще, это задача комплексная. Не «подходят» внешние источники потому, что вы решаете задачу несистемно.
            • Доступ к данным должен быть ограничен. Это должно решаться через централизованное управление правами. А это AD. И если взять за основу оперирование данными через внешние источники, эта задача бы решалась на уровне, например, MS SQL.
            • Excel можно рассматривать как формат входящих данных и как движок для отчетов. Но не как БД для построения других отчетов. Теоретически это возможно. Практически, это ад кромешный для сисадминов и поддержки. Потому, что за формулами и связями стоят конкретные люди не ограниченные своей фантазией. Поддержание консистентности такой системы невозможно. Т.е. совсем. Это высокий операционный риск компании. В любой момент времени она может потерять ценные данные. И просто так из бэкапа их не восстановить. Потому, что многие из документов становятся «критическими» и актуализируются в реальном времени.
            • Нужно разделять шаблон отчета и отчет. Управление шаблонами необходимо организовать через системы управления версиями. Частные отчеты необходимо фиксировать «как есть». Вне зависимости от изменяющегося шаблона. В противном случае, когда задача будет — «а соберите мне данные за последние 3 года» — это будет очередной ад.


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

            P.S. Вообще, я два раза перечитал статью и оба раза не совсем понял целевую задачу.
              0
              1. Разграничение доступа конечно же есть. У нас реализована ролевая модель и существует «подсистема» управления правами. В статье я этого не касался, т.к. предмет был немного другой.
              2. В некотором смысле так и есть — с помощью этого инструмента пользователи создают и поддерживают свои модели привычным им способом. Вопросы версионности моделей мы не решаем (это не задача нашего инструмента). Версионность и контроль целостности самих данных, которые они загружают реализуется в нашей системе, но это таже несколько выходит за рамки статьи.
              3. Хранение готовых моделей и их дальнейшее использование не является областью применения нашей системы. Клиенты реализуют их по-разному. Некоторые могут хранить, например Excel-файлы в Sharepoint, где есть какое-никакое управление версиями. А для некоторых эта модель промежуточная, как вы упоминали — способ получения входных данных для других аналитических инструментов. И то что они сделали при помощи нашей системы, они выгружают куда-то еще.

              Наверное, со стороны решаемая задача, для которой пришлось все это сделать, выглядит несколько непонятной. Но у нас взгляд замылен, мы в ней давно варимся :) Задача достаточно «отраслевая» и связана с повышением удобства переноса числовых данных из неструктурированых документов с сохранением привязки к источнику. Т.е. надо:
              • быстро перенести данные в Excel
              • иметь возможность увидеть число из ячейки в исходном документе (например, финасовом отчете)
              • прeдоставить некоторые инструменты по работе с данными в виде функций Excel
          0

          А мы пошли обратным путем (https://m.habr.com/ru/company/arcadia/blog/498032/) и позволили пользователям продолжать работу в экселе по редактированию данных и настройке бизнес рассчетов. А вот визуализацию, анализ данных, сложные рассчеты оптимизировали на бекэнде и выдавали в более удобном виде для клиента, которому интересен лишь результат и красивая картинка.

            0
            А чем аналитиков не устроили Гугл Таблицы? Помниться, я делал в Ексел расчет методом конечных элементов. Все с нуля, начиная от формирования матриц жесткости для каждого треугольника до составления результирующей системы уравнений. В общем, это была жесть чистой воды. Затем импортировал это дело в Гугл Таблицы и все заработало.
              0
              Гугл Таблицы:
              1. Немного, но ощутимо подтормаживают.
              2. Выглядят неряшливо (когда работаешь с таблицами по 5-6 часов в день такие вещи начинают бесить).
              3. Во многих случаях их просто не принимает заказчик/целевой потребитель. Скажем, в требованиях банка к финмодели может прямо в первой строчке быть написано: «Модель представляется в виде файла Microsoft Excel версии не ниже ___».

              Ну и вообще, Эксель это «наше все», устоявшийся отраслевой стандарт. Я сейчас начинаю очередной заказ, который прямо просится в Jupyter Notebook, но вместо этого делаю в Экселе, просто потому, что никто в команде кроме меня не пользуется ничем другим.
                0

                Может банально нельзя, просто интернет не положен?

                  0
                  Некоторым клиентам действительно нельзя
                  0
                  Почему мы не делали это в Google-таблицах — есть несколько причин:
                  1. Клиентам в основном нужно работать с Excel.
                  2. Для некоторых клиентов решение должно работать в сети, изолированной от Интернета или с сильно ограниченным доступом в Интренет.
                  3. Как уже упоминали тут в комментах, на больших таблицах Google-таблицы тормозят сильнее, чем Excel.
                  4. Возможностей по подключению к внешним источникам и загрузки данных в Google-таблицы меньше, чем предоставляет Microsoft.
                  0
                  А вот мы зашли в другую тему — Excel JS Addin. Это страничка прямо в документе, js-код работает с документом через js-api, с бэкендом через rest-api.
                  Там возможностей хватает, но сильно проще разрабатывать и обновлять аддины.
                    0
                    Да, JS API для Excel мы тоже смотрели. Для многих задач он намного удобнее старого нативного API для addin'ов. Но некоторые вещи через него мы бы не смогли сделать, например перехват вставок. И полтора года назад, когда мы принимали решение каким путем пойти, JS API было беднее, чем сейчас. Если я е ошибаюсь, тогда там нельзя было сделать асинхронные UDF. При этом ExcelDNA очень помогает и облегчает разработку. Установка и обновление у нас сделаны через MSI, проблем с этим нет.
                    +1
                    Похоже, сама постановка задачи вызывает много вопросов. Поясню немного. Задача состояла в том, чтобы сохранить Excel, как инструмент, в котором работает пользователь. При этом дать возможность удобно заполнять таблицы числовыми данными из исходны документов. Для этого Пользователю надо видеть одновмеменно свою таблицуи размеченный нашей системой исходный документ. Также необходимо поддерживать связанность между ячейкой, в которую пользователь вставил «число» и исходным документом. Для этого при перемещении по ячейкам ему надо показывать исходный документ и подсвечивать числа в нем. И все это должно работать быстро.
                    Мы ерепробовали разные варианты решения этой задачи: и «чистый» RTD, и JS API. Но результат, удобный для пользователя предстказуемо работающий без сайд-эффектов, связанных с особенностями выполенения кода в Excel, у нас получился именно в том виде, который описан в статье.
                      0

                      Я всё собираюсь написать статью, которая расскажет про трудный путь к текущему решению через асинхронные UDF (которые оказались "иногда синхронными", причём в внезапно) к связке RTD + SignalR. Про некоторые неприятные сюрпризы от ExcelDNA и почему иногда мне хочется написать свою версию этой библиотеки (конечно с преферансом и институтками), а не использовать то, что есть. Ну а про то, что COM Interop в .NET вообще и в Excel в частности — это бесконечный источник боли, наверное, только ленивый не писал ещё :)

                      Only users with full accounts can post comments. Log in, please.