Excel javascript вместо vba

Обновлено: 06.07.2024

Всем привет, в этой статье я покажу, как с помощью Excel можно формировать JS массивы на основе таблиц. Звучит как бред, однако в дальнейшем можно сохранить массив в js файл, изменить расширение файла на DB и полученную "базу данных" подключать к простой веб странице, можно использовать в Android приложении или расширении для браузера. Меняя содержимое файла можно изменять данные не затрагивая html код, и тут пригодится Excel, который возьмет на себя перевод данных из таблицы в массив. Разбор из файла в JSON будет происходить в веб странице.

Смотрите видео: База данных из JavaScript для веб страницы из Excel на VBA модуле.

Итак, сохраним книгу Excel с поддержкой макросов, работать будем с двумя листами, tab - таблица и test - лист с кнопкой и готовым результатом. В редакторе VBA добавим новый модуль, и внесем в него следующий код:

Привяжем кнопку к модулю и нажмем на нее. Полученные данные вставим в текстовый файл с текстом

между кавычками. Файл сохраняем как JS, но с расширением db.

В секции head веб страницы(я ипользовал bootstrap шаблон), добавим "базу данных" как если бы это был скрипт. Прим: при использовании уберите пробел перед левой кавычкой!

В секции body опишем таблицу для вывода данных и script , который будет формировать строки и столбцы на странице. Прим: при использовании уберите пробел перед левой кавычкой!

При запуске html страницы будет отображена таблица: заголовок Имя и Возраст и данные name и age .

Первый VBA модуль обладает одним недостатком: имена полей приходится указывать в коде и самих полей только два, данные начинаются с первой строки. Вторая версия модуля сама определяет имена полей и их количество, "шапка" таблицы в первой строке, данные начинаются со второй строки. Сделайте копии листов tab , test сохранив как tab2 , test2 , добавьте новый модуль, вставьте в него следующий код и присвойте кнопке Module2.

Это позволяет мне выполнять произвольный JavaScript, однако у меня нет доступа к электронной таблице Excel из среды Javascript. Есть ли способ, которым я могу установить и получить значения листа в активном листе из JavaScript?

Это зависит от того, какого рода «программирование в Excel» вы хотите сделать.

Если вы хотите манипулировать файлами в Excel, вы можете сделать это с помощью автоматизации COM или, в некоторой степени, через ODBC, используя JavaScript, работающий в среде сценариев Windows.

Но если вы хотите, чтобы ваш код выполнялся в активном сеансе Excel и делал то, что увидит посетитель, у вас будет меньше вариантов.

Посмотрите на этот вопрос, который я опубликовал несколько лет назад, когда у меня был JavaScript, который я хотел запустить в Excel, и у меня не было бюджета для преобразования в VBA:

Попробуйте Excel-DNA для программного интерфейса для Excel. Два других приятных инструмента, на которые можно посмотреть: xlwings и Excel-REPL, которые поддерживают интерфейсы Python и Clojure соответственно.

Для людей, использующих Excel 2016 или более позднюю версию, в надстройке есть надстройка Excel под названием Funfun. -в магазине, который на самом деле позволяет писать и запускать код JavaScript прямо в Excel. И, конечно, ваш код JavaScript также имеет доступ к данным, хранящимся в электронной таблице. Вот скриншот того, как это выглядит в Excel 2016 .

Хорошо в середине интерфейса у вас есть раздел, в котором вы можете написать JavaScript, CSS и HTML-код. Это очень похоже на игровую площадку, встроенную в Excel. Но у Funfun также есть онлайн-редактор, в котором вы можете протестировать свой код. Вы можете увидеть это на картинке ниже. Я также разместил ссылку на пример на первом рисунке, чтобы вы могли поиграть.

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

Чтобы использовать данные, хранящиеся в электронной таблице, все, что вам нужно сделать, это записать некоторую конфигурацию в файл short.io Funfun, чтобы сообщить JavaScript, какая область в электронной таблице содержит ваши данные. Например, в примере, который я разместил, все, что вам нужно написать, это

А в коде JavaScript для чтения данных используется объект $ internal . Таким образом, чтобы прочитать данные, которые хранятся в A2: B9, необходимо написать

И это сделано. Вы можете обратиться к документации Funfu n, если хотите узнать больше.

Если вы удовлетворены результатом, которого вы достигли в онлайн-редакторе, вы можете легко загрузить результат в Excel, используя приведенный выше URL. Прежде всего, вам нужно вставить надстройку Funfun из Вставка - Мои надстройки . Вот несколько скриншотов, показывающих, как это можно сделать.

Раскрытие информации: я разработчик Funfun

WSTools от WizardSoft позволяет вам смешивать VBA и JScript, включая объекты и возвращаемые значения (варианты). Он также имеет много других полезных функций. Вид пакета утилит.

Эти функции специально предназначены для переплетения VBA / VBScript / JavaScript

Excel широко распространен практически на всех рабочих местах. Люди от крупных инвестиционных компаний и крупных инжиниринговых компаний до индивидуальных предпринимателей работают с помощью Excel.

В этой статье мы рассмотрим некоторые проблемыа такжепреимущества использования Excel и какJava встроена в Excelэти проблемы могут быть преодолены.

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

Excel snafu стоит фирме 24 миллиона долларов

Простая ошибка в электронной таблице стоила фирме колоссальные 24 миллиона долларов США. Ошибка привела к TransAlta, большой канадский генератор энергии .

Как лондонская китовая катастрофа отчасти является результатом ошибки при использовании Excel

Это то, о чем люди начинают говорить в блогосфере, что должно дать паузу всей Уолл-стрит. Над…

С такими рисками, почему компании все еще используют Excel, и что они могут сделать, чтобы предотвратить подобные ситуации?

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

Разработчики программного обеспечения часто утверждают, что того же можно достичь на их любимом языке программирования. Технически они могут быть правильными, но это предполагает, что у каждого есть время и желание учиться на разработчика (на что у большинства из нас уходит много-много лет).

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

Проблемы с Excel

Что в Excel делает его подверженным ошибкам? Само по себе ничего, но разработчики привыкли к этомууменьшитьтакие же риски в решениях, не основанных на Excel. Ниже приведен список некоторых слабых мест в том, как электронные таблицы Excel обычно разрабатываются.

Как мы можем решить эти проблемы?

Все эти проблемы возникают из-за того, что на поверхности что-то довольно простое (сетка связанных чисел), и толкают его до тех пор, пока его поведение и правильность не станут чрезвычайно сложными.

По сути, Excel - это способ выражения отношений между вещами.A1это суммаB1а такжеС1,например,Где это начинает идти не так, когда эти отношения становятся все более и более сложными

Если бы вы хотели вычислитьA1дисперсия ежедневных возвратов временных рядовИксКак это будет выглядеть в Excel? Если вы опытный пользователь Excel, вы, возможно, представляете таблицу, представляющую временной ряд B, с дополнительными столбцами для вычисления возвращаемых значений и формулой для вычисления дисперсии. Но что, если теперь мы хотим вычислить отдачу для другогоNвременная последовательность? Скопировать и вставить формулы для каждого нового временного ряда? Вот так начинают появляться ошибки!

Гораздо лучше заключить в капсулуалгоритмвычисления дисперсии ежедневных возвратов временного ряда в функцию. Затем мы можем вызывать это повторно столько раз, сколько мы хотим, без риска того, что одна из промежуточных ячеек будет отредактирована или неправильно скопирована.

Теперь представьте, что вместо таблицы данных временной ряд может быть представлен одной ячейкой в Excel. Если этого можно достичь, мы вернемся к простым отношениям между двумя клетками: «A1 = daily_returns_variance_of (B1)». Внезапно наша электронная таблица начинает выглядеть намного менее сложной!

У нас все еще есть проблема, что данные временного ряда должны откуда-то приходить. Вместо копирования и вставки из другой системы или базы данных, что если бы у нас была функция, которая загружала временные ряды из этой системы или базы данных напрямую? Таким образом, каждый раз, когда мы вычисляли электронную таблицу, мы знали, что данные были актуальными и полными! Чтобы продолжить предыдущий пример, мы могли бы иметьB1 = load_time_series (тикер, start_date, end_date)». Далее мы рассмотрим, как именно мы можем хранить весь набор данных в одной ячейке.

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

Как Java помогает нам?

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

Если бы мы решили написать все наши функции в VBA (и поверьте мне, это делают многие!), Мы бы не воспользовались каким-либо прогрессом в разработке программного обеспечения, достигнутым за последние 20 лет!

Java идет в ногу с современной разработкой программного обеспечения и может многое предложить по сравнению с VBA.

тестирование, В Java есть множество различных сред тестирования, каждый из которых имеет свои сильные и слабые стороны. Однако, какой бы вы ни выбрали, возможность запуска автоматических тестовых наборов для всей базы кода дает вам уверенность в том, что все происходит правильно. Это просто невозможно с VBA.
Обширная библиотека поддержки.Написание кода на VBA часто является случаем написания стандартных алгоритмов, которые можно найти в Интернете, и преобразования их в VBA. Хотите сделать что-то тривиальное, как сортировать массив данных? В Java это не проблема, но в VBA вы будете нести ответственность за то, чтобы убедиться, что ваш алгоритм сортировки работает, и без какого-либо тестирования. Теперь представьте, что пишете сложную модель ценообразования!
Храните код за пределами Excel.Код VBA обычно сохраняется в рабочей книге, поэтому при совместном использовании рабочих книг ошибки так сложно отследить. Если ваша электронная таблица вместо этого ссылается на скомпилированную библиотеку Java (JAR), то она является внешней по отношению ко всем электронным таблицам, которые ссылаются на нее, и может быть легко обновлена.
Контроль версий.Исходный код Java - это просто текст, поэтому его легко можно проверить в системе контроля версий. Большинство Java IDE имеют отличную поддержку для этого, поскольку это стандартная часть современной разработки программного обеспечения.
Среда разработки.Редактор VBA (VBE) не менялся годами. Он предлагает чуть больше, чем очень простой текстовый редактор с простейшими возможностями отладки. Java, с другой стороны, предлагает широкий выбор отличных IDE.

Но Java не является частью Excel!

Это правда, но в Excel есть концепция «надстроек», которая позволяет разработчикам расширять функциональные возможности Excel. Одна такая надстройкаJinx, надстройка Excel для Java,

С помощьюдурной глазВы можете полностью отказаться от VBA и написать функции листа, макросы и меню полностью на Java.

Написание функции рабочего листа в Java так же просто, как добавление аннотации JExx @ExcelFunction в метод Java:

Вы можете вернуть все основные типы, которые вы ожидаете, а также 1d и 2d массивы. Для более сложных типов вы можете написать свои собственные преобразователи типов или вернуть объекты Java непосредственно в Excel в качестве дескрипторов объектов для передачи другому методу Java.

Джинкс этобесплатно скачать, УвидетьРуководство пользователя Jinxдля получения дополнительной информации о том, как вы можете использовать Java в качестве замены для VBA

Что это было за возвращение временного ряда в виде одной ячейки?

Функции Jinx могут возвращать все стандартные типы, которые вы ожидаете (целые, двойные, массивы и т. Д.), Но также могут возвращать объекты Java! Когда возвращается сложный объект Java (например, класс, представляющий временной ряд, загруженный из базы данных), он возвращается в Excel как дескриптор объекта. Этот дескриптор объекта затем может быть передан другим функциям Jinx, а метод Java будет передан исходному объекту Java, возвращенному из первой функции.

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

Вы можете прочитать больше об этих дескрипторах объекта вJinx Object Cacheраздел руководства пользователя.

Другие языки

Эти методы не являются уникальными для Java.

То же самое можно сказать и о других языках JVM, таких какScalaилиКотлин, Jinx работает со всеми языками JVM, не только с Java.

Другой популярный язык для написания надстроек Excel - это Python. Это может быть достигнуто с помощьюНадстройка PyXLL для Excel,

это позволяет мне выполнить произвольный javascript, однако у меня нет доступа к электронной таблице excel из среды javascript. Есть ли способ, который я могу установить и получить значения листа в активном листе из javascript?

Если вы хотите управлять файлами Excel, вы можете сделать это через автоматизацию COM или даже ODBC в некоторой степени, используя JavaScript, запущенный в среде сценариев Windows.

Но если вы хотите, чтобы ваш код работал в активном сеансе Excel, что делает посетитель, у вас будет меньше вариантов.

См. этот вопрос. Я опубликовал несколько лет назад, когда у меня был JavaScript, который я хотел запустить в Excel, и у меня не было бюджета для преобразования в VBA:

Для пользователей, использующих Excel 2016 или более позднюю версию, в надстройке есть надстройка Excel Funfun, которая фактически позволяет вам писать и запускать код JavaScript непосредственно в Excel. И, конечно, ваш код JavaScript также имеет доступ к данным, хранящимся в электронной таблице. Вот скриншот, как это выглядит в Excel
2016.

В середине интерфейса у вас есть раздел, в котором вы можете писать JavaScript, CSS и HTML-код. Это похоже на детскую площадку, встроенную в Excel. Но у Funfun также есть онлайн-редактор, в котором вы можете протестировать свой код. Вы можете увидеть это на рис. Ниже. Я также разместил ссылку примера на первой картинке, чтобы вы могли играть.

Чтобы использовать данные, хранящиеся в электронной таблице, все, что вам нужно сделать, это написать некоторую конфигурацию в файле short.io Funfun, чтобы рассказать JavaScript о том, какая область в электронной таблице содержит ваши данные. Например, в приведенном выше примере все, что вам нужно написать, это

И в коде JavaScript для чтения данных используется объект с именем $internal. Поэтому, чтобы прочитать данные, хранящиеся в A2: B9, вам нужно написать

И все сделано. Вы можете перейти к документации Funfu, если вы хотите узнать больше.

Раскрытие информации: Я разработчик Funfun

Читайте также: