Подключить dll к 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 отчет, поработали со стилями и даже решили пару попутных проблем.
Здравствуйте, жители RSDN'на!
Здравствуйте, Аноним, Вы писали:
А>Здравствуйте, жители RSDN'на!
Возможно что в DLL имена декорированы.
Убедись что для проекта DLL есть файл *.def с опредлением EXPORT в котором указано недекорированое имя экспртируемой функции.
Shores of solar sea
Oh how i wish to go down with the sun.
Здравствуйте, hermit., Вы писали:
H>Здравствуйте, Аноним, Вы писали:
А>>Здравствуйте, жители RSDN'на!
H>Возможно что в DLL имена декорированы.
H>Убедись что для проекта DLL есть файл *.def с опредлением EXPORT в котором указано недекорированое имя экспртируемой функции.
def файл заполнен, но ноль имоций! Когда начинаю дебагить скрипт на VB, то во время вызова функции из библиотеки и после неё, безуспешно пытаюсь найти её загруженную в памяти. Поиск проводил при помощи ProccessExplorer. Вначале VB даже ни какую ошибку не выдовал, когда пытался выполнить функцию из библиотеки. Вчера выдал ошибку, но она ни как не связана с библиотекой: "Expression too coplex". Так же пытался написать в библиотеки функцию с бесконечным циклом, но при попытки её вызова ничего не произошло, и дебаггер пошёл дальше по алгоритму, хотя по идее должен был зависнуть в этом месте. У меня просто слов уже нет, одни эмоции. Читаю везде, что подключить библиотеку, это как два байта написать, но ни где не могу найти рабочий пример.
Да ещё в эту библиотеку нужно будет массивы передавать, я думаю, что тут тоже будут свои подводные камни.
Здравствуйте, Аноним, Вы писали:
А>Здравствуйте, hermit., Вы писали:
H>>Здравствуйте, Аноним, Вы писали:
А>>>Здравствуйте, жители RSDN'на!
H>>Возможно что в DLL имена декорированы.
H>>Убедись что для проекта DLL есть файл *.def с опредлением EXPORT в котором указано недекорированое имя экспртируемой функции.
А>def файл заполнен, но ноль имоций! Когда начинаю дебагить скрипт на VB, то во время вызова функции из библиотеки и после неё, безуспешно пытаюсь найти её загруженную в памяти. Поиск проводил при помощи ProccessExplorer. Вначале VB даже ни какую ошибку не выдовал, когда пытался выполнить функцию из библиотеки. Вчера выдал ошибку, но она ни как не связана с библиотекой: "Expression too coplex". Так же пытался написать в библиотеки функцию с бесконечным циклом, но при попытки её вызова ничего не произошло, и дебаггер пошёл дальше по алгоритму, хотя по идее должен был зависнуть в этом месте. У меня просто слов уже нет, одни эмоции. Читаю везде, что подключить библиотеку, это как два байта написать, но ни где не могу найти рабочий пример.
Это что-то из области фантастики. Жаль С на работе не установлен. VB загружает библиотеку в момент вызова функции. Что-то странное у тебя происходит.
А>Да ещё в эту библиотеку нужно будет массивы передавать, я думаю, что тут тоже будут свои подводные камни.
Угу. Обязательно будут Правда смотря чего массив.
Shores of solar sea
Oh how i wish to go down with the sun.
Здравствуйте, hermit., Вы писали:
H>Здравствуйте, Аноним, Вы писали:
А>>Здравствуйте, hermit., Вы писали:
H>>>Здравствуйте, Аноним, Вы писали:
А>>>>Здравствуйте, жители RSDN'на!
H>>>Возможно что в DLL имена декорированы.
H>>>Убедись что для проекта DLL есть файл *.def с опредлением EXPORT в котором указано недекорированое имя экспртируемой функции.
А>>def файл заполнен, но ноль имоций! Когда начинаю дебагить скрипт на VB, то во время вызова функции из библиотеки и после неё, безуспешно пытаюсь найти её загруженную в памяти. Поиск проводил при помощи ProccessExplorer. Вначале VB даже ни какую ошибку не выдовал, когда пытался выполнить функцию из библиотеки. Вчера выдал ошибку, но она ни как не связана с библиотекой: "Expression too coplex". Так же пытался написать в библиотеки функцию с бесконечным циклом, но при попытки её вызова ничего не произошло, и дебаггер пошёл дальше по алгоритму, хотя по идее должен был зависнуть в этом месте. У меня просто слов уже нет, одни эмоции. Читаю везде, что подключить библиотеку, это как два байта написать, но ни где не могу найти рабочий пример.
H>Это что-то из области фантастики. Жаль С на работе не установлен. VB загружает библиотеку в момент вызова функции. Что-то странное у тебя происходит.
Вот и я про тоже самое, только я это проверян на трёх компах и везде было одно и тоже ну не находит он её не находи и хоть ты тресни. И самое интересное даже пытался специально нарваться на ошибку, объявив несуществующую функцию из не существующей внешней библиотеки. Он и её проглатил без ошибки. По этому прошу народ что-бы выслали точно рабочий пример, хочется всё таки узнать это меня так перед дипломом клинит или что-то не доустановлено на моих компах.
А>>Да ещё в эту библиотеку нужно будет массивы передавать, я думаю, что тут тоже будут свои подводные камни.
H>Угу. Обязательно будут Правда смотря чего массив.
Создаем обычную динамическую библиотеку. Выбираем файл, создать, потом Win32 Dynamik-Link Library. Не забудьте указать имя проекта (DllExcel).
И три файла нужно создать в проекте. Как мы это делали в "Шаг 1 - Минимальный проект".
Создаем код
Описание и макрос Excel
Вот так работает наша DLL.
Описание
Первым шагом мы реализовали саму функцию в CPP файле. В общем всё тривиально, за исключением модификатора WINAPI. Существует несколько моделей вызовов функций. Для использования в Windows модель вызовов функций PASCAL. Для соответствия требованиям WIN32 API и добавлен модификатор. Дальше в exceldll.h мы описали данную функцию. Но применили опять модификатор для приведения имен функций к стандарту C. Применение этого стандарта гарантирует поддержку Ваших имен функций другими программами. Если DLL создается именно для вашего приложения, то это не важно, но если Вы собрались поделиться ей с миром, то должны обеспечить совместимость.
И последним шагом заполнили def файл, описав функцию MyTest как функцию экспорта, то есть что она будет извлекаться из DLL. Теперь собирайте проект. Полученную DLL нужно поместить в каталог Windows/System или просто Windows или WinNT, если Windows NT. При вызове функции из DLL необходимо чтобы DLL была найдена. В описанных каталогах операционная система обязательно посмотрит.
Теперь запустите Excel, создайте макрос, опишите функцию и код макроса. Пояснений я сюда писать не буду, т.к. это касается программирования VBA. А если вы этого не умеете, зачем вам этот шаг, хотя на Бейсике программировать может каждый. Запускайте макрос. Если Вы всё сделали правильно, то увидите диалоговое окно созданное функцией DLL.
Это очень хорошая возможность. Применив данную технику Вы можете расширить возможности любой программы, которая имеет макроязык и функции работы с DLL. Перечислю на взлет программы Microsoft Office, Corel Draw (Corel Script), AutoCad (VBA), ArcView (Avenue) и т.д.
Те, кто программирует на VBA для Excel, в определенный момент задумываются над распространением своих приложений в качестве независимых файлов. Лучшая возможность для организации распространяемых файлов – это создание специальных дополнений или надстроек Excel. За время развития программы появилось несколько типов надстроек. Мы попытались собрать и систематизировать информацию для разработчиков обо всех видах, назначениях, достоинствах и недостатках надстроек Excel.
Персональная книга макросов
| Описание: | хранение общих функций и процедур для персонального использования |
| Требования: | нет |
| Достоинства: | возможность хранения пользовательских функций для работы с данными (UDF) |
| Недостатки: | сложности с тиражированием |
Для общих программ и макросов можно использовать, так называемую, личную книгу макросов – по умолчанию это файл PERSONAL.XLS (в Excel 2007-2010 PERSONAL.XLSB). Файл с данным именем создается с при записи макроса средствами Excel. Этот файл будет автоматически загружаться каждый раз при запуске Excel. Местонахождение данного файла в каталоге Windows: ПОЛЬЗОВАТЕЛЬ\Application Data\Microsoft\Excel\XLSTART. На самом деле Excel будет запускать автоматически все файлы из данного каталога, независимо от имен файлов.
В принципе, личные книги макросов можно считать надстройками Excel. В файле PERSONAL.XLS (или любом другом из стартового каталога) можно хранить общие функции и макросы, автоматизирующие часто повторяемые операции. Для выполнения каких-либо операций при старте Excel можно использовать событие Worbook_Open этого файла, либо процедуру Auto_Open в модуле кода.
XLA/XLAM
| Описание: | стандартная надстройка |
| Требования: | нет |
| Достоинства: | возможность создания тиражируемых продуктов |
| Недостатки: | слабая защита исходного кода программ. |
Надстройки с расширением xla (в версии 2007-2010 xlam) представляют собой стандартный xls-файл, который может быть открыт как невидимая в списке открытых файлов рабочая книга.
Файл надстройки обычно содержит программный код, который управляет какими-либо оперциями активной рабочей книги Excel. Теоретически, кроме модулей кода, в состав xla-файла могут быть таблицы и даже листы диаграмм, но надо понимать, что особого смысла в этих данных нет, так как нет возможности отобразить эти данные на экране стандартными средствами. На рабочих листах надстройки можно хранить, например, константы для работы кода, если по каким-либо соображениям не хочется использовать константы VBA. Данные в ячейках файла надстройки можно даже изменять во время работы, но сохранять открытый и исполняемый в данный момент файл xla в общем случае недопустимо.
Для доступа к данным рабочей книги надстройки используется объект ThisWorkbook, а для работы с активной рабочей книгой – объект ActiveWorkbook.
В надстройках имеется возможность хранить пользовательские функции для работы с данными рабочих листов (user defined function - UDF). Только надо учитывать, что в этом случае рабочий файл сохраняет ссылку (связь) на файл надстройки по абсолютному пути. Эта особенность может затруднить распространение программных продуктов.
Отладку надстроек лучше производить через xls-файл (не забывая различия между ThisWorkbook и ActiveWorkbook), а впоследствии сохранять этот файл как xla. Нектороые сложности могут в этом случае возникнуть при использовании событий Workbook_AddinInstall и Workbook_AddinUninstall, так как их невозможно эмулировать в простом файле рабочей книги. К счастью, эти события требуются крайне редко; честно говоря, мы не знаем ни одного примера их полезного использования на практике.
Подключать готовую XLA-надстройку можно через соответствующий диалог (Excel 2000-2003 Сервис \ Надстройки, Excel 2007 Кнопка Office \ Параметры Excel\ Надстройки \ Перейти).
Подключаемые надстройки прописываются в реестре Windows по адресу HKEY_CURRENT_USER \ Software \ Microsoft \ Office \ НОМЕР_ВЕРСИИ.0 \ Excel \ Options в строковых параметрах с префиксом OPEN и порядковым номером надстройки (причем первая по порядку надстройка номера не имеет). Управляя этим ключом реестра, можно подключить / отключить надстройку через инсталляционные программы. Это, кстати, еще одна причина никогда не использовать событие Workbook_AddinInstall – очевидно, что оно не будет вызвано при изменении ключей реестра внешними программами.
Имя и описание надстройки можно задать в свойствах файла перед его сохранением в форматие xla/xlam (Excel 2000-2003 Файл \ Свойства, Excel 2007 Кнопка Office \ Подготовить \ Свойства).
Важным преимуществом надстройки XLA/XLAM вместо использования кода внутри рабочих файлов является возможность повторного использования кода VBA. Кроме того, установленные надстройки не спрашивают о наличии макросов при запуске Excel на любом уровне безопасности.
Интересная, но скорее всего бесполезная в работе информация. Надстройки XLA в Excel версии 5.0-8.0 (95-97) хранятся в скомпилированном виде. В связи с этим, там просто нет исходного текста программ в открытом или зашифрованном виде. Эта особенность приводит к невозможности декомпиляции или вскрытию кода специальными средствами. К сожалению, в последующих версиях Excel надстройки хранятся в нескомпилированном виде и легко преобразуются в доступный для изменения файл, даже будучи защищенными паролями. Microsoft рекомендует использовать для разработчиков возможности COM-DLL и средства VSTO (см. далее).
В качестве примера XLA-надстройки, можно ознакомиться с исходным кодом надстройки ExcelFin в разделе Программы.
XLS/XLA как ссылка
| Описание: | библиотека кода VBA |
| Требования: | нет |
| Достоинства: | не требует компиляции во внешних программных продуктах |
| Недостатки: | сложность распространения из-за абсолютных ссылок на файлы |
Если вы создали набор общеупотребительных функций, но не имеете возможность скомпилировать библиотеку в виде XLL или DLL-файла (см.ниже), то можно подключить любой VBA-код XLS-файла в качестве ссылки в проекте. Делается это в окне редактора кода через меню Tools \ References.
В этом случае вы также можете избавиться от повторного использования кода, но такой вариант вызывает дополнительные сложности при распространении рабочего файла, так как в проекте сохраняются абсолютные пути к ссылкам.
При использовании общего кода XLA-надстроек и ссылок к XLS-файлам важную роль играет определение методов и свойств класса Friend. Эти методы и свойства видны внутри проекта, но не во внешнем файле, использующем ссылку. Внутри одного файла определение Friend соответствует Public.
| Описание: | библиотека функций рабочего листа |
| Требования: | компилятор C/C++ и API-библиотека |
| Достоинства: | создание наиболее производительных функций с полным встраиванием в интерфейс Excel |
| Недостатки: | нет |
Наиболее производительные пользовательские функций для работы с таблицами реализуются при помощи XLL-надстроек.
XLL-надстройка представляет собой скомпилированный на C/C++ файл динамической библиотеки Windows, специально предназначенной для использования в Excel. Разработчикам предоставляется специальная библиотека C API (Excel XLL SDK). Использование языка C с возможностью управления памятью позволяют разработать самые мощные и производительные функции для работы с данными Excel. Сравните производительность встроенных функций и UDF, разработанных на VBA.
Мы не имеем практического опыта разработки XLL-надстроек, поэтому желающих разрабатывать библиотеки такого типа отсылаем к MSDN.
Имеется не очень стандартный способ регистрации UDF-функций для вывода пользовательской функции на VBA в интерфейсе Excel через Application.ExecuteExcel4Macro(“REGISTER(…)”) . Метод проверен и работает. Но не дает никаких преимуществ в производительности функций, поэтому в практической работе его ценность сомнительна. Подробнее читайте:
COM DLL (Office Developer Tools – ODT)
| Описание: | полнофункциональная надстройка Excel |
| Требования: | ODE 97 или ODT XP |
| Достоинства: | возможность создания интерфейса любой сложности; полностью закрытый исходный код. |
| Недостатки: | невозможно создание пользовательских функций для работы с данными (UDF) |
Главной особенность Excel ODT является наличие возможности создания проекта VBA независимого от xls-файла, с возможностью последующей компиляции данного проекта в файл формата DLL (ActiveX DLL).
Исходный VBA-проект содержит специальный дизайнер с методами обработки событий Excel. В проекте, кроме этого, доступны для создания обычные модули кода, классы и формы. Причем формы могут быть открыты немодально. Надстройки данного типа могут быть подключены к различным версиям Excel одновременно. Файл DLL, скомпилированный с помощью ODE/ODT не требует никаких дополнительных исполняемых модулей для своей работы, кроме, собственно говоря, Excel.
Здесь важное замечание, что, даже если вас заинтересовали возможности ODE/ODT, вам вряд ли удастся приобрести лицензионные версии этих продуктов – Microsoft с 2003го года прекратил их распространение. Вообще же вероятно, что в России, официальным способом было приобретено совсем небольшое количество копий пакетов для разработчиков, поэтому искать пиратскую версию этих продуктов тоже особого смысла не имеет.
Настройка ленты Office 2007:
В отличие от панелей инструментов, новый пользовательский интерфейс Excel 2007 в виде ленты (ribbons) не имеет встроенных механизмов настройки напрямую через VBA.
Имеется довольно странный механизм настройки ленты через редактирование xml-файла. Формат xlsx представляет из себя zip-архив нескольких файлов и папок, в одном из которых доступна настройка на ленте пользовательских функций. Странно, но разработчики Excel почему-то не предоставили интерфейс для настройки ленты иным способом. Подробнее см. MSDN:
Вызов процедур COM-DLL из VBA
В тех случаях, когда основной алгоритм закрыт в COM-надстройке, вызов функций и процедур (например, из обработчиков пунктов меню) осуществляется через механизм позднего связывания (Late-bound). Для получения доступа к объекту надстройки используется функция COMAddIns объекта Application с указанием имени COM-надстройки. Например:
COM DLL (Visual Studio Tools for Office – VSTO)
Комментарии
. Workbook_AddinInstall и Workbook_AddinUninstall, так как их невозможно эмулировать в простом файле рабочей книги. К счастью, эти события требуются крайне редко; честно говоря, мы не знаем ни одного примера их полезного использования на практике.
Хорошее замечание.Действительно, хранить udf в xla можно. Только в этом случае формула использует xla как ссылку на файл (можно посмотреть в меню 2000-2003 Правка\Связи или 2007 Подготовить\Использовать ссылки на файлы).
В обычной работе это скорее всего никак не сказывается, но проблемы могут возникнуть при распространении надстройки из-за абсолютных путей в ссылках.
Исправил текст статьи. Спасибо.
. В надстройках нет возможности хранить пользовательские функции для работы с данными рабочих листов (user defined function - UDF).
Как это нет. я храню и использую пользовательские функции которые хранятся в .xla файле, и никаких проблем.
Читайте также: