Excel на JavaScript!Viktor Turskyi

CTO at WebbyLab

Kyiv.js 2013

Бизнес задача

Запускать XLS файлы со сложными математическими моделями в браузере и чтобы без сервера

Требования к производительности:

1. Возможность запуска крупных моделей(до 2-х миллионов ячеек, 400тыс формул, 1млн Excel функций, 50 листов)

2. Поддержка цепей вычисления на сотни тысяч ячеек

3. Высокая производительность4. Небольшой размер файла

Требования к окружению

● Офлайн работа в браузере● Работа на сервере● Работа на планшетах(iOS, Android)

=IF($F$36 + $AF128 <= 101; SUMPRODUCT( ($S128:OFFSET($S128;$F$36-1;0)) * ($AG$55:OFFSET($AG$55;$F$36-1;0)) * ('Sheet25'!BY84:OFFSET('Sheet25'!BY84;$F$36-1;0) + 'Sheet25'!BY194:OFFSET('Sheet25'!BY194; $F$36-1; 0) ) ); SUMPRODUCT( ($S128:$S$155) * ($AG$55:OFFSET($AG$55;100-$AF128;0)) * ('Sheet25'!BY84:BY$111 + 'Sheet25'!BY194:BY$221) ) )

1. Дизайн интерфейса2. 12 мб xls файл c формулами вида:

От заказчика получили

Почему не Google Docs API:

1. Работает только в онлайн2. Очень медленный3. Проблемы при конвертации некоторых

xls 4. Максимум 40 тыс формул в файле

Вопрос №0: это возможно? :)

С помощью JavaScript все возможно ;)

Вопрос №1: Достаточно ли у JS производительности?

1. Определяем целевые платформы(движки)a. Браузеры и их версииb. iOS устройстваc. Android устройства

2. Пишем синтетический мини бенчмаркa. Математический вычисленияb. Длинные цепи вычислений

Вопрос №2: Как вычитать данные с XLS файла?

● Необходимо вычитать значения● Необходимо вычитать формулы● Необходимо вычитать называние

листов● Необходимо вычитать имена

диапазонов и ячеек

Варианты:

● Nodejs модули - не способны даже вычитать значения, сваливаются на огромных файлах.

● Ruby/Python/Perl/PHP - нет возможности получить формулы или имена ячеек

Что сработало?

Perl (100 строк) + Windows + Win32::OLE + Excel + документация VBA = тупой дамп в JSON сырых данных.

Вопрос №3: Вычитали и что?

1. Нужно разобрать гору сырых данных2. Нужно проанализировать все формулы3. Нужно отслеживать завимости между

формулами4. Нужно это как-то хранить5. Нужно реализовать множество функций

c Excel6. Нужен движок, который все выполнит

Вопрос №4: Как разобрать формулу в JS?

1. Учет приоритета операторов2. Инфиксные/префиксные операторы3. Функции4. Ссылки на ячейки5. Ссылки на диапазоны6. Именованные адреса

Неправильное решение

Самописный лексер и парсер:1. Сложно2. Долго3. Дорого

Отказались с первыми существенными проблемами приоритета операторов.

Правильное решение - ANTLR

1. Генератор парсеров (включая JS)2. Лексический и синтаксический анализ3. На выходе AST (Abstract Syntax Tree) 4. Лучшее из всего, что есть под JS (и не

только)Мы используем версию 3.3 (в версии 3.4 баг в JS генераторе)

http://www.antlr.org/

Примеры формул

Formula: '=1+2*3'JS AST: [ '+', 1, [ '*', 2, 3] ]

Formula: '=A1+B1'JS AST: [‘+’, ['=', 0, 0, 0], ['=', 0, 1, 0] ]

Formula: ‘=SUM(B5:B100, 42)'JS AST: [ 'SUM', [ 'RANGE', 0, 1, 4, 1, 99 ], 42 ]

Компоненты движка

● LocalRunner - работает с файлом и определяет порядок вычислений

● Formula Evaluator - вычисляет формулу● Address Parser - парсит адреса

введенные пользователем● Functions - реализация Excel функций

Реализация EXCEL функций

● Одна функция - один класс● Все функции без побочных эффектов ● Используется принцип внедрения

завимостей для подключения● node-qunit для тестирования

Пример вызова: SQRT([ 9 ]) вернет 3SUM([2, [5, 6, 7, 9], 1 ]) вернет 30

Вопрос №5: зависимости ячеек

A1=1A2=A1+1A3=A1+A2

Ячейка А1 влияет на A2 и A3 Ячейка A2 влияет на А3

Что представляют собой зависимости?

По сути, мы имеемнаправленныйациклическийграф

(храним в JSONвместе с AST)

При изменении ячейки пересчитывать зависимые

На тестовых файлах работает, а в реальной жизни - нет.

Причина - множественные пересчеты однихи тех же ячеек.

Топологическая сортировка

Позволяет нам вычислять ячейку один раз.

На тестовых файлах работает, в реальной жизни - нет.

Причина - переполнение стека вызовов.

Что делать?

Не используйте рекурсию, сами управляйте стеком и обходите граф.

Результат на реальной модели:Без сортировки - 1часС сортировкой - 6 секунд

Как работать с диапазонами ячеек?

SUM( [ [ 21, 22, 23, 31, 32, 33 ] ] );SUM( [ new ArrayRange([21, 22, 23, 31, 32, 33]) ] );SUM( [ new ModelRange(model, ‘B2:C4’ ) ] );

Оптимизация

Обращайте внимание на:1. Рекурсивные алгоритмы2. Большие JSON файлы(40мб повалит

ваш браузер)3. Лимиты по помяти в NodeJS (иногда

невозможно обойти)4. Копирования данных в памяти

Инструменты

1. ANTLR для синтаксического анализа2. Web Workers для повышения

отзывчивости интерфейса3. Browserify для использования

CommonJS модулей в браузере4. Qunit для тестирования

Поддержка разных окружений

Для сервера - nodejsДля браузера - browserifyДля планшетов - phonegap

Живая демонстрация

Viktor Turskyiviktor@webbylab.com

http://koorchik.blogspot.comhttp://search.cpan.org/~koorchik/

https://github.com/koorchik

WebbyLabhttp://webbylab.com

WebbyLabищет

Junior Frontend Developer

http://www.work.ua/jobs/1433919/