C# как замена VBA в Excel

Я довольно много пишу на C#, и это мне нравится. Время от времени мне хочется, чтобы можно было использовать C# внутри других приложений.

Думаю, одним из таких приложений, в которых хорошо приживётся C#, был бы Excel, этим языком можно было бы заменить VBA. Язык VBA уже немного отстаёт от времени, а Microsoft не планирует его апгрейдить. Вместо него — компания предлагает среду-песочницу для JavaScript под названием Office Scripts.

Я понимаю привлекательность JavaScript для веб-версии Excel, но не в качестве хорошей замены VBA.

Так как этим не собирается заниматься Microsoft, я решил, что могу попробовать сам. В конце концов, если Microsoft так увлечена JavaScript, маловероятно, что она выдавит меня с обширного рынка использования C# в Excel.

Итак, вот что я создал:

QueryStorm IDE, выполняющая запрос LINQ для таблицы Excel

«Это что, IDE языка C#, выполняющая запросы LINQ для таблиц Excel?» Именно. Всё так и есть.

Я назвал это QueryStorm. Моя работа над этим проектом началась ещё в 2014 году. Изначально у него имелась только поддержка SQL, и с тех пор я продолжал над ним работать, постепенно добавляя поддержку скриптинга на C#, пользовательских функций C#/Excel, поддержку NuGet, отладчик и даже магазин приложений!

Позвольте мне провести экскурсию.

QueryStorm IDE

В качестве редактора кода в QueryStorm используется AvalonEdit. AvalonEdit — это текстовое поле с расширенными возможностями, способное отображать форматированный текст, но не знающее ничего о языке C# и его синтаксисе. Редактору необходимо сообщать, где в тексте расположены символы и ошибки (чтобы он мог их подсвечивать), какие опции автозавершения предлагать и так далее.

Чтобы понимать пользовательский код на C#, QueryStorm использует компилятор C# компании Microsoft под названием Roslyn.

Roslyn — это библиотека .NET, а не отдельная программа. Кроме возможности компилирования кода на C# в dll, Roslyn может рассказать вам всё о любом коде на C#, который вы ей передадите.

Например, если передать ей код на C#, она может сообщить, где могут быть ошибки, где определяется каждый символ и где на него ссылаются, что значит каждый символ и какие опции автозавершения предлагать для конкретной точки в коде.

Основная часть предлагаемой Roslyn функциональности выведена на поверхность в QueryStorm IDE, что предоставляет следующие возможности:

• Подсветка синтаксиса и ошибок
• Завершение кода
• Исправления и форматирование кода
• …и многое другое

Пишем код на C# в QueryStorm

Эти возможности позволяют писать код продуктивным и удобным образом. Может быть, они не сравнятся с Visual Studio или Rider, но определённо превосходят VBA IDE.

Скриптинг на C# в Excel

Проще всего исследовать C# в Excel, запустив создание скрипта на C#; это можно сделать, нажав на кнопку «C#» в ленте.

Создание нового скрипта на C#

Скриптовая разновидность C# позволяет исполнять фрагменты кода и вычислять выражения без церемоний определения пространств имён, классов и метода static void Main().

Пример выражения на C# и его результата

Запустить скрипт можно, нажав F5 на клавиатуре или щёлкнув кнопку «Run» в ленте.

Если скрипт возвращает значение явным образом (использует ключевое слово return) или косвенно (заканчивается выражением, которое не завершено точкой с запятой), то результат после исполнения будет показан в сетке результатов.

Можно использовать эту скриптовую функциональность в качестве блокнота C# (наподобие LinqPad), однако дополнительная выгода заключается во взаимодействии с книгой и Excel. Благодаря этому, мы получаем преимущества C# и .NET поверх нативной функциональности Excel.

Скрипты могут ссылаться на объект Application Excel или на объект Workbook при помощи метода Resolve<>.

Например, можно написать скрипт для создания новой таблицы Excel со списком файлов, содержащихся в указанной папке:

using System.IO;
using Microsoft.Office.Interop.Excel;

// получаем данные
var appDataFolder = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
var files = Directory
    .GetFiles(appDataFolder, "*", SearchOption.AllDirectories)
    .Take(1000)
    .Select(f =>
        new {
            File= Path.GetFileName(f),
            Size= new FileInfo(f).Length
        });

// записываем в Excel как новую таблицуц
Resolve<IExcelAccessor>().Run(excel =>
{
    (excel.Selection as Range).WriteTable(files, "myNewTable");
});

Записываем экземпляр List<T> как новую таблицу Excel

Доступный пользователю Excel API такой же, как в VBA, поэтому всё, что можно делать на VBA, можно делать и на C#.

Кроме того, имеется набор удобных методов расширения, например, показанный в примере выше WriteTable().

Из Linq в таблицы Excel

Однако вместо того, чтобы работать с концепциями Excel наподобие ячеек и листов, интереснее будет сосредоточиться на данных. В скриптах In QueryStorm на C# каждая таблица Excel отображается как коллекция со строгой типизацией, к которой можно выполнять запросы и которую можно обновлять.

Например, допустим, у нас есть таблица, содержащая список городов. Для каждого города есть его название, население и страна. Чтобы найти в каждой стране по три города с самым большим населением, мы можем выполнить следующий запрос LINQ:

cities.GroupBy(x => x.country)
    .SelectMany(g => g.OrderByDescending(x => x.population).Take(3))
    .OrderBy(x => x.country).ThenBy(x => x.population)
    .Select(x => new { x.city, x.country, x.population })

Пример запроса LINQ для таблицы Excel

▍ ORM для таблиц Excel

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

Строки имеют свойства со строгой типизацией, соответствующие столбцам таблицы

Откуда берётся этот класс? Дело в том, что он автоматически генерируется QueryStorm (при помощи Reflection.Emit).

Сгенерированный класс находится внутри dll, на которую автоматически ссылается скрипт. Каждый раз, когда пользователь изменяет таблицу, QueryStorm изучает изменение и при необходимости обновляет сгенерированные типы.

Динамически сгенерированная dll с типами для доступа к данным в таблицах Excel

Это работает в качестве слоя ORM поверх таблиц Excel. Однако для полного ORM нам нужна возможность указывать типы данных в столбцах и взаимосвязи в таблицах.

По умолчанию QueryStorm угадывает тип данных для каждого столбца таблицы на основании данных, которые он содержит, и не предполагает никаких взаимосвязей между таблицами.

Однако определение контекста произвольных данных позволяет нам указывать типы столбцов и взаимосвязи таблиц. Добавление взаимосвязей между таблицами вызывает добавление новых свойств навигации к сгенерированным типам.

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

public class WorkbookDataContext1 : WorkbookDataContext
{
    public WorkbookDataContext1(IWorkbookAccessor workbookAccessor, ISyncRunner syncRunner)
        : base(workbookAccessor, syncRunner, false)
    {
    }

    // Задаём типы данных столбцов
    protected override void Configure(ITablesConfiguration config)
    {
        config.ConfigureTable("countries", tableView => {
            tableView
                .ConfigureColumn<System.String>("Name")
                .ConfigureColumn<System.Int32>("Population (2020)");
        });

        config.ConfigureTable("cities", tableView => {
            tableView
                .ConfigureColumn<System.String>("city")
                .ConfigureColumn<System.Int32?>("population");
        });
    }

    // Задаём взаимосвязи
    protected override IEnumerable<RelationInfo> CreateRelationships()
    {
        // взаимосвязь city.Country (каждый город с одной страной)
        yield return new RelationInfo("cities", "countryName", To.One, "countries", "Name", "Country");
    
        // взаимосвязь country.Cities (каждая страна с N городами)
        yield return new RelationInfo("countries", "Name", To.Many, "cities", "countryName", "Cities");
    }
}

После обновления контекста данных мы увидим новые свойства навигации в скриптах на C#.

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

Контекст произвольных данных меняет классы со строгой типизацией, описывающие строки таблицы

Как мы указали в определении контекста данных, свойства населения теперь имеют тип «int?», а cities теперь имеет свойство навигации "Country", что позволяет с лёгкостью перейти к соответствующей строке в таблице countries.

▍ Обновление таблиц Excel

Мы не ограничены одними только запросами данных из таблиц Excel. Можно также выполнять обновления в таблицах.

Например, давайте добавим новый столбец в таблицу cities для доли населения страны, живущей в городе. Для начала нужно добавить столбец вручную в Excel, а затем обновить его через скрипт на C#.

cities.ForEach(c =>
{
    int? cityPopulation = c.population;
    int? countryPopulation = c.Country?.Population__2020_;

    var shareOfCountrysPopulation =
        1.0 * cityPopulation/ countryPopulation;
    
    c.share_of_country_s_popupation =
            $"{100.0 * shareOfCountrysPopulation:0.00}%";
})

Обновляем содержимое таблицы Excel при помощи LINQ C#

▍ Форматирование строк таблицы

Наконец, мы можем обновлять форматирование строк при помощи метода расширения Format(), доступного для объектов IEnumerable.

Например, давайте выделим все города, в которых живёт более 20% населения соответствующей страны.

cities.Where(c => c.share_of_country_s_popupation > 0.2)
    .Format(r => r.Interior.ColorIndex = 35);

Обновление форматирования строк таблицы Excel при помощи C#

▍ Производительность

Данные из таблиц Excel кэшируются, поэтому производительность считывания примерно такая же, как при работе со списками объектов в памяти, то есть очень высокая.

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

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

Расширение функциональности Excel с помощью C#

Скрипты в основном используются для выполнения одноразовых запросов данных в таблицах. Но мы также можем использовать C# для добавления постоянной функциональности самому Excel! И можно даже публиковать свои расширения, чтобы ими пользовались другие люди.

Расширить Excel можно при помощи следующих видов элементов:

• Собственные функции Excel
• Новые контекстные меню
• Новые вкладки и элементы управления в ленте
• Горячие клавиши

▍ Собственные функции Excel на основе C#

На VBA можно с лёгкостью создавать собственные функции для использования в формулах Excel. В QueryStorm это столь же просто (но с гораздо более мощными возможностями).

Чтобы задать одну или несколько собственных функций Excel, нужно создать проект в QueryStorm, написать функцию на C#, декорировать её атрибутом [ExcelFunction] и собрать проект. После этого функция станет доступной в Excel.

Создание и использование функции C# в формулах Excel

Например, давайте создадим функцию "IsMatch", которая будет сообщать нам, соответствует ли вводимая строка паттерну регулярного выражения:

using System;
using System.ComponentModel;
using System.Text.RegularExpressions;
using QueryStorm.Apps;
 
namespace DemoProject;

public class ExcelFunctions1
{
    [ExcelFunction(Description="check if input matches rx pattern")]
    public static bool IsMatch(string inputText, string regexPattern)
        => Regex.IsMatch(inputText, regexPattern);
}

Вот как эта функция выглядит в Excel:

Валидация IP-адресов при помощи нового метода IsMatch, созданного на основе регулярных выражений

Библиотека базовых классов .NET содержит всевозможную функциональность, которая может быть полезна в Excel (например, регулярные выражения), и это удобный способ сделать её доступной в Excel.

И как мы увидим позже, в расширениях также можно использовать пакеты NuGet.

Функции C#, раскрываемые как функции Excel, могут:

• быть синхронными или асинхронными
• возвращать одно значение или массив значений, распространяющийся на диапазон ячеек
• возвращать одно значение или поток значений, меняющихся со временем (например, биржевые котировки)

Подробнее см. в разделе документации об определении функций Excel при помощи C#.

▍ Добавление собственных команд в Excel

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

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

Вот как будет выглядеть код для этой команды:

using Microsoft.Office.Interop.Excel;
using System;
using QueryStorm.Apps;
using QueryStorm.Apps.Contract;
using QueryStorm.Tools.Excel;
using QueryStorm.Tools;

namespace Project;

public class ContextCommand1 : ContextMenuCommand
{
    private readonly IWorkbookAccessor workbookAccessor;

    public ContextCommand1(IWorkbookAccessor workbookAccessor)
        : base(
            caption: "To UPPPER case",
            faceId: 100,
            allowedLocations: new[] { KnownContextMenuLocations.Cell, KnownContextMenuLocations.Table })
    {
        this.workbookAccessor = workbookAccessor;
    }

    public override void Execute()
    {
        workbookAccessor.Run(wb =>
        {
            var range = wb.Application.Selection as Range;
            object [,] data = range.GetData();
            for (int i = data.GetLowerBound(0); i <= data.GetUpperBound(0); i++)
            {
                for (int j = data.GetLowerBound(1); j <= data.GetUpperBound(1); j++)
                {
                    var val = data[i,j];
                    if(val is string s)
                        data[i, j] = s.ToUpper();
                }
            }
            range.Value = data;
        });
    }
}

Вот как новая команда контекстного меню будет выглядеть в Excel:

Собственная команда для преобразования текста в верхний регистр

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

▍ Публикация пакетов расширений

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

После публикации расширения в репозиторий пользователи, имеющие среду исполнения QueryStorm и URL репозитория, смогут скачать и использовать его. Подробнее об этом см. в разделе «Среда исполнения» этой статьи.

Автоматизация книг

Ещё одна возможность, реализуемая в Excel при помощи VBA — это реагирование на событие в книге. Это мы тоже можем с лёгкостью реализовать на C# в QueryStorm.

Чтобы добавить в книгу поведение (в виде кода на C#), нужно создать проект внутри книги и добавить класс компонента:

Создание проекта и компонента внутри книги

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

using System;
using System.Collections.Generic;
using System.Linq;
using QueryStorm.Apps;
using QueryStorm.Apps.Contract;
using QueryStorm.Data;
using static QueryStorm.Tools.DebugHelpers;

namespace Project;

public class Component1 : ComponentBase
{
    private double _number;
        
    // привязка данных к таблице "People"
    [BindTable]
    public PeopleTable People { get; set; }
        
    // обработка нажатия на кнопку "Sheet1!CommandButton1"
    [EventHandler("Sheet1!CommandButton1")]
    public void Test()
    {
        var oldestPersonsName = People
            .OrderByDescending(x => x.Age)
            .First()
            .Name;
            
        System.Windows.Forms.MessageBox.Show(
            $"Oldest person is {oldestPersonsName}");
    }
}

Компонент использует привязку данных для доступа к данным в таблице Excel и определяет метод для обработки события нажатия мышью на кнопку.

После сборки проекта скомпилированное приложение книги встраивается в книгу в виде одного или нескольких файлов dll. Затем среда исполнения QueryStorm обнаруживает приложение книги и запускает его.

Демо простого приложения книги

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

Система проектов

В QueryStorm есть своя система проектов, схожая с системой в Visual Studio.

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

Система проектов QueryStorm

Контекстное меню содержит команды для генерации различных видов файлов с добавлением базовой кодогенерации.

▍ Поддержка VB.NET

Стоит заметить, что C# — это не единственный поддерживаемый язык; VB.NET тоже поддерживается. При создании проекта можно выбрать используемый язык.

Выбор между языками C# и VB.NET при создании проекта

Этот выбор сохраняется в файле project.config.

Язык проекта хранится внутри файла project.config

Поддержка VB.NET может оказаться привлекательной для пользователей, имеющих опыт работы с VBA. Так как Roslyn имеет поддержку VB.NET, логично поддерживать его в QueryStorm.

Код на VB.NET в QueryStorm

Стоит заметить, что можно использовать VB.NET для сборки приложений расширений и приложений книг, но в Roslyn не поддерживается скриптовый VB.NET, поэтому нет скриптов на VB.NET, только на C#.

Пакеты NuGet

Одно из основных преимуществ C# и .NET — это возможность использования готовых библиотек и пакетов из экосистемы .NET, поэтому поддержка NuGet была обязательным требованием для QueryStorm.

Вот как выглядит добавление пакета в проект:

Добавление пакета NuGet в проект и его использование

Среда исполнения QueryStorm

Итак, как же использовать расширение книги или Excel, собранное при помощи QueryStorm?

Главное, что для этого понадобится — среда исполнения QueryStorm. Среда исполнения позволяет запускать книги со скомпилированным кодом на C#, а также скачивать и запускать расширения Excel, созданные QueryStorm.

Среда исполнения — это свободное дополнение на 4 МБ, которое конечные пользователи устанавливают в Excel. Скачать его можно с веб-сайта QueryStorm.

Скачивание установщика среды исполнения

Установщик выполняет установку для пользователя, не требующую прав администратора.

Приложения книг и расширений очень похожи, но отличаются по способу распространения.

▍ Запуск и распространение приложений книг

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

Обратите внимание, что отправка книги в виде приложения к письму не всегда срабатывает, потому что книга содержит встроенную dll, которую некоторые фильтры электронной почты обнаруживают и блокируют из соображений безопасности. Однако передача при помощи OneDrive, Dropbox или Google Drive работает вполне хорошо.

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

Запрос безопасности, позволяющий запустить приложение книги

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

Подробнее о безопасности приложений книг можно прочитать в разделе документации.

▍ Запуск и распространение приложений расширений

Расширения Excel распространяются через магазин приложений QueryStorm. Автор публикует свой пакет расширения на сервере (NuGet).

Конечные пользователи скачивают расширение оттуда, но они сначала должны зарегистрировать URL репозитория при помощи диалогового окна «Extensions» во вкладке ленты «QueryStorm Runtime»:

Регистрация репозитория расширений

После этого они смогут просматривать и устанавливать все расширения из этого репозитория.

Среда исполнения QueryStorm поставляется с одним стандартным репозиторием, где публикуются расширения (в основном мной) общего назначения.

Например, я собрал и опубликовал расширение, добавляющее множество функций для работы с телефонными номерами.

Вот как установить его и использовать:

Установка и использование пакета Windy.Phone с функциями для работы с телефонными номерами

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

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

Если бы вы хотели делиться своими расширениями с другими людьми вне своей сети, то лучше всего использовать облачный репозиторий наподобие Azure Artifacts. Azure Artifacts имеет бесплатный тариф с 2 ГБ хранилища, чего хватит на сотни расширений. Настройка репозитория Azure Artifacts займёт всего несколько минут; инструкции см. в этом видео.

Подробнее о настройке репозиториев можно почитать в этом разделе документации.

Отладка кода на C# в Excel

Если вы пишете достаточно объёмный код на C#, то вам понадобится возможность отладки. Существует множество способов отладки кода на C# в QueryStorm; простейший из них — это встроенный отладчик .NET.

Чтобы начать отлаживать проект, нажмите F5.

Демо отладки кода на C#

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

Просмотр и измерение горячих клавиш

Отладчик имеет ограничение: в нём можно отлаживать только обычный код на C#. Отладка скриптов на C# (пока) не поддерживается.

Для отладки скриптов на C# можно или воспользоваться логированием (с помощью метода Log()), или прикрепить Visual Studio к процессу Excel и использовать метод Debug() в качестве контрольной точки.

В заключение

Excel имеет огромную функциональность. Однако благодаря возможности обрабатывать данные и логику при помощи C# он может стать в руках опытного пользователя чрезвычайно мощным инструментом.

В этом посте рассмотрены лишь самые основы использования C# внутри Excel.

Если вы разработчик на C#, на VBA, аналитик данных или дата-саентист, то я надеюсь, QueryStorm найдёт своё место в вашем инструментарии.

Telegram-канал с розыгрышами призов, новостями IT и постами о ретроиграх