Как включить режим программирования в excel

Обновлено: 04.07.2024

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

Историческая справка

Времена, когда доминировал проприетарный формат .xls(Excel Binary File Format) давно прошли и сейчас мы имеем только .xlsx(Excel Workbook), в рамках Office Open XML. Последний представляет собой обычный .zip архив с XML файлами. Не будем углубляться в его структуру, я искренне надеюсь что вам это никогда не понадобится.

На github, и не только, можно найти ряд библиотек, бесплатных и не только. Пожалуй самой популярной является EPPlus. До определенной степени, она довольно хорошо отражает концепцию Excel, именно по этому я всегда использую EPPlus. Версия 4 полностью бесплатна, начиная с 5‐й версии вам потребуется приобрести лицензию для коммерческого использования.

Задача

Итак, предположим, продукт-мэнеджеру ударила в голову идея того, что возможность выгружать некий отчет в формате Excel увеличит кол-во пользователей на 100500%. Проджет-менеджер решает выкатить эту киллер-фичу как хотфикс прямо сегодня — ведь работы всего на пару часов.

Сам по себе, отчет содержит краткое описание компании и историю изменения некоторых экономических показателей. Для простоты все свойства компании — строки. Экономические показатели — большие целые числа и числа с плавающей точкой, а также даты. Предположим, что где-то в недрах микросервисного backend-да есть сервис-генератор подобных отчетов, например по id компании. Однако, поскольку id нет смысла выводить пользователю, идентификатор отсутствует в самой модели отчета.

Аналитик, в свою очередь, выдает задачу с феноменально точным описанием - "Сгенерировать excel отчет на базе данных MarketReport". Что ж, для нашего примера, создадим заглушку — генератор фейковых данных:

Первый запуск

Подключим EPPlus версии 4.5.3.3 и создадим базовую обвязку для будущего генератора.

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

В методе main создается генератор отчетов, а также генератор Excel файлов. Далее полученный файл просто записывается на диск.

При попытке запустить приложение, получаем exception: InvalidOperationException: The workbook must contain at least one worksheet

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

Запускаем снова и. вот оно! Теперь наше приложение генерирует документ и, хотя там еще ничего нет, он уже весит 2,5KB - значит мы работаем с Excel правильно и все идет как надо.

Вывод данных

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

Полный код вывода шапки.

Для вывода исторических данных понадобится как минимум шапка таблицы и цикл по массиву History:

Предлагаю обратить внимание на метод LoadFromArrays, который заполняет диапазон ячеек рваным(зубчатым) массивом. Здесь мы можем видеть, что типизация теряется и передавая массив object мы ожидаем что EPPlus в конечном итоге использует ToString, чтобы записать переданное в ячейки.

Стилизация

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

Как это выглядит

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

Да, на все эти красивости у нас уйдет больше ~~года~~ кода, чем на сам вывод данных, и, в конечном тоге, получившаяся каша из логики вывода данных и разметки заставит некоторых усомниться в их компетентности. но, мы же backend разработчики, так давайте сверстаем Excel Sheet!

Размер ячеек

Из коробки у нас есть возможность сделать автофит а так же вручную выставить ширину в соответствии с нашей ситуацией. А ситуация у нас не самая хорошая — по задумке аналитика в шапке у ячеек должен быть автофит, а у ячеек таблицы — тоже автофит. Так в чем же подвох?

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

Формат данных

Как и большая часть стиля ячейки, он задается через одноименное свойство Style. Обратите внимание на вычисление 3-го аргумента индексатора. Это звоночек некачественного кода, но к этому мы вернемся в позже.

Выравнивание

Его можно задать как на ячейке, так и на диапазоне. На самом деле, для EPPlus, это одна и та же сущность — некий ExcelRange, описывающий диапазон ячеек, в том числе и со всего 1 ячейкой.

Стиль текста

Также легко задается, используя Style.Font, кстати, здесь, на 2-й строчке, мы впервые указываем диапазон так, как привыкли его видеть пользователи Excel:

Границы

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

График

"Ну что за отчет без графиков, верно, Карл?" - ловко подметит специалист по тестированию, и не важно, что этого не было в ТЗ а на часах уже половина 9-го.

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

Еще, может понадобиться защитить страницу от редактирования:

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

Заключение

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

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

После нажатия кнопки «Module» вы увидите ссылку на него, появившуюся в проводнике слева.

Первая программа на VBA Excel

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

Откройте стандартный модуль двойным кликом по его ссылке в проводнике. Поместите в него курсор и нажмите кнопку «Procedure…» во вкладке «Insert» главного меню. Та же ссылка будет доступна при нажатии на вторую кнопку после значка Excel на панели инструментов.

В результате откроется окно добавления шаблона процедуры (Sub).

Имя процедуры может быть написано как на латинице, так и на кириллице, может содержать цифры и знак подчеркивания. Оно обязательно должно начинаться с буквы и не содержать пробелы, вместо которых следует использовать знак подчеркивания.

Вставьте внутрь шаблона процедуры следующую строку: MsgBox "Привет" .

Проверьте, что курсор находится внутри процедуры, и запустите ее, нажав клавишу «F5». А также, запустить процедуру на выполнение можно, нажав на треугольник (на изображении под пунктом меню «Debug») или на кнопку «Run Sub/UserForm» во вкладке «Run» главного меню редактора VBA Excel.

Работа с переменными

Чтобы использовать в процедуре переменные, их необходимо объявить с помощью ключевого слова «Dim». Если при объявлении переменных не указать типы данных, они смогут принимать любые доступные в VBA Excel значения. Комментарии в тексте процедур начинаются со знака «'» (апостроф).

Вкладка с инструментами, связанными с VBA Excel, называется «Разработчик». Если этой вкладки нет на вашей ленте, добавьте ее через окно «Параметры»:

Откройте окно «Параметры» через меню «Файл».
Выберите вкладку «Настроить ленту».
В правой колонке установите галочку у пункта «Разработчик» и нажмите «OK».

Кнопка – элемент управления формы

Вставка кнопки на лист

Выберите вкладку «Разработчик» и нажмите на кнопку «Вставить».
Нажмите на значок кнопки в коллекции «Элементы управления формы».

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

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

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

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

Создание процедуры для кнопки

Кнопке из коллекции «Элементы управления формы» можно назначить макрос (процедуру), размещенную в стандартном программном модуле.

Создайте или откройте файл Excel с расширением .xlsm (Книга Excel с поддержкой макросов) и перейдите в редактор VBA, нажав сочетание клавиш «Левая_клавиша_Alt+F11».

Если вы не создавали ранее в этом проекте VBA стандартный программный модуль, нажмите кнопку «Module» во вкладке «Insert» главного меню. То же подменю откроется при нажатии на вторую кнопку (после значка Excel) на панели инструментов.

Ссылка на модуль появится в проводнике слева. Если модуль создан ранее, дважды кликните по его ссылке в проводнике, и он откроется справа для редактирования.

В открывшемся окне добавления шаблона процедуры оставьте выбранным переключатель «Sub», вставьте в поле «Name» название процедуры «NovayaProtsedura» и нажмите «OK».

В стандартный программный модуль будет вставлен шаблон процедуры «NovayaProtsedura».

На своём веку я занимался многими странными вещами, о некоторых из которых не могу рассказать, однако использование Excel вместо кодинга — одно из тех постыдных удовольствий, которые я не буду ни от кого скрывать.

Забудьте о тесте Тьюринга — проходит ли ваша гениальная идея программы тест Excel? Например, все пользовались простыми табличными формулами для генерации отчётов, но знаете ли вы что Excel может запросто выполнять замены регулярными выражениями, применять операторы if, и даже можно вызывать онлайн-функции, чтобы подгружать актуальные цены онлайн-сервисов?

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

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

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

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

После этого я даже мог использовать вот такую формулу:

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

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

Разумеется, писать их вручную я не хотел, поэтому создал один скрипт, а затем сопоставил значения со столбцами базы данных с помощью сравнения строк и операторов if. Примерно так:

="union select id from tb_test test where test.customer_property "&IF(A2="larger";">";"<")&"5;"

Особенно обратите внимание на оператор union , поскольку он позволил мне запустить скрипт один раз, затем скопировать все получившиеся id из результата и вставить их обратно в электронную таблицу.

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

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

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

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

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

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

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

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

Чем старше я становлюсь, тем больше меня раздражают люди, ненавидящие то, что они не понимают.

То же самое относится и к Excel: это невероятно мощный инструмент, если вы сможете освоить его возможности, но достаточно всего одного человека в команде, который воскликнет «боже, только не Excel», чтобы мы потратили час на его объяснения преимуществ того современного инструмента, который он предпочитает. Другие инструменты приходят и уходят, а Excel всегда рядом, когда вам нужно крепкое плечо, в которое можно поплакать.

На правах рекламы

Эпичные серверы для разработчиков и не только! Недорогие VDS на базе новейших процессоров AMD EPYC и NVMe хранилища для размещения проектов любой сложности, от корпоративных сетей и игровых проектов до лендингов и VPN.

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