Страницы: 1
RSS
Использование функций библиотеки на языке C в Excel
 
Недавно начал изучать язык программирования C и меня сразу заинтересовала такая тема, что можно написать для себя функции для использования в Excel'е. Вроде где-то читал, что функции, написанные на C/C++, быстро работают в Экселе по сравнению с VBA функциями, ну и решил написать простейшие функции для теста. На выходе мы получаем Win32 библиотеку (DLL), которую используем в Declare Function. Далее я узнал, что можно написать XLL надстройки, которые позволяют использовать функции С сразу в ячейках, а не в VBA, но это я не осилил, конечно.  С улыбкой Если кто-то знает, есть ли разница в скорости работы VBA и функций, буду очень благодарен.0
На ваш суд выношу библиотеку с двумя функциями. Первая из них (addOne) совсем простая - прибавляет единичку к аргументу Double и возвращает результат.
Вторая функция - очень интересная. Если мне не изменяет память, этот вопрос задавал nerv - можно ли использовать VBA-функцию в качестве callback-функции. Ответ - с помощью C DLL можно!
Библиотеки сделал в двух вариантах - 32 и 64-х битные. Предпроцессорная директива (#If Win64) сама выберет нужную библиотеку. От вас требуется скопировать куда-нибудь эти две библиотеки и указать к ним полный путь после Lib. Файл с кодом я приложу.
Итак, сначала код VBA.
Код
#If Win64 Then
    
    ' Excel 64-бит
    Private Declare PtrSafe Function addOne Lib "c:\DLLs\ExcelFuncs_x64.dll" _
        (ByVal x As Double) As Double
    
    Private Declare PtrSafe Function addOneWithCallback Lib "c:\DLLs\ExcelFuncs_x64.dll" _
        (ByVal funcPtr As LongPtr, ByVal x As Double) As Double

#Else

    ' Excel 32-бит
    Private Declare Function addOne Lib "c:\DLLs\ExcelFuncs_x86.dll" _
        (ByVal x As Double) As Double

    Private Declare Function addOneWithCallback Lib "c:\DLLs\ExcelFuncs_x86.dll" _
        (ByVal funcPtr As Long, ByVal x As Double) As Double

#End If

Sub AddOneTest()
    MsgBox "Сумма = " & addOne(5)
End Sub

Sub AddOneUsingCallback()
    
    ' C-фукнция получает указатель VBA функции "AddOneCallback" и передаёт ей пятёрку.
    ' VBA функция прибавляет 1 к аргументу и возвращает 6.
    ' С функция возвращает в VBA шестёрку.
    MsgBox "5 + 1 = " & addOneWithCallback(AddressOf AddOneCallback, 5)
    
End Sub

Function AddOneCallback(ByVal x As Double) As Double
    AddOneCallback = x + 1
End Function
 

Итак, разберём, что к чему. Как я и говорил выше, директива #If Win64 автоматически загрузит нужную библиотеку - свою для 32 и 64-битного Офиса. Если вы не программировали Win32 API для 64-битного Офиса, то для 64-битного Офиса поменялись некоторые типы данных, которые необходимо использовать. Например, вместо Long - LongPtr и т.д. На моём примере мы видим для 64-битного Экселя "PtrSafe" и вместо Long используется LongPtr (так как указатели имеют длину не 32 бита (Integer - 4 байта)).
После того, как вы подправили путь к библиотеке, далее идут две тестовые процедуры (AddOneTest и AddOneUsingCallback) и вспомогательная функция (callback) для AddOneUsingCallback.
Первая простейшая C-функция - addOne в AddOneTest. Как видно, ничего нет сложного - вызываем функцию обычно как будто это VBA-функция. В примере в неё передаётся пятёрка, к ней прибавляется единичка, на выходе - 6.
Вторая C-функция - addOneWithCallback. Она имеем два аргумента. Первый - это адрес callback-функции, которая будет вызываться в C-функции. Второй аргумент я сделал для наглядности - он будет передаваться в VBA-функцию в C-библиотеке.  :)  В комментариях я написал, что получается.
Итак, callback'и в VBA возможны. Думаю, Саша прочитает эту тему.  :D  
Исходный C код:
Код
#define WINAPI __stdcall

// Указатель функции
typedef double (WINAPI *fptrAddNum)(double);

double WINAPI addOne(double x)
{
    return x + 1;
}

double WINAPI addOneWithCallback(fptrAddNum callback, double x)
{
    return callback(x);
}
Ссылка на файл.
There is no knowledge that is not power
 
я то думаю, отчего икаю, а про меня тут пишуТь   :)  

Цитата
Если мне не изменяет память, этот вопрос задавал nerv - можно ли использовать VBA-функцию в качестве callback-функции. Ответ - с помощью C DLL можно!
с помощью application.run() тоже можно насколько помню
Цитата
Думаю, Саша прочитает эту тему
в это сложно поверить, но космические сигналы дошли   :D  

Цитата
Итак, callback'и в VBA возможны
только смотрится как порнография) Для сравнения замена подстроки в строке на VBA & js:
vba:

Код
r = replace(replace(replace(text,find,replacement),find,replacement),find,replacement)

js

Код
r = text.replace(find,replacement).replace(find,replacement).replace(find,replacement);

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


Аналогично и с коллбеками (тоже форум покалечил):
Код
r = text.replace(find,function(a,b,c) {
Код
   return '...';
Код
});

---

Я сейчас изучаю Node.js. Данная программная платформа кроссплатформенная =), что позволяет писать на js в т.ч. десктопные приложения для любой ОС.
http://www.youtube.com/watch?v=N-4p2_NEr9w
Чебурашка стал символом олимпийских игр. А чего достиг ты?
Тишина - самый громкий звук


https://github.com/nervgh
 
ну, а в целом C/C++ - хорошо, одобрямс  :)

На правах новостей:
[quote]
Проект Mozilla и компания Epic Games продемонстрировали возможность использования Web-бразуера Firefox в качестве платформы для запуска современных 3D-игр в Web
[/quote]

http://www.opennet.ru/opennews/art.shtml?num=39294
---

ляськи-масяськи, какой глючный форум)))
Изменено: nerv - 14.03.2014 01:13:59
Чебурашка стал символом олимпийских игр. А чего достиг ты?
Тишина - самый громкий звук


https://github.com/nervgh
 
Цитата
Я сейчас изучаю Node.js.
Подскажите, в Node.js - это тот же javascript? А как в нем реализуется UI ? Строгая типизация отсутствует?

А так да, согласен, в сравнении с java подобными языками синтаксис VBA похож на порнографию
 
Цитата
Цитата
Итак, callback'и в VBA возможны
только смотрится как порнография) Для сравнения замена подстроки в строке на VBA & js:
vba:
Код
 r = replace(replace(replace(text,find,replacement),find,replacement),find,replacement) 
Саша, это НЕ callback'и.... Уж этого от тебя НЕ ожидал!  :(
There is no knowledge that is not power
 
Цитата
Подскажите, в Node.js - это тот же javascript?
да

Цитата
А как в нем реализуется UI ?
не понял вопроса. Возможно, ты про это.

Кто не ходил в википедию, могу первый абзац сюда запостить:
Цитата
Node или Node.js — программная платформа, основанная на движке V8 (транслирующем JavaScript в машинный код) превращающая JavaScript из узко специализированного языка в язык общего назначения. Node.js добавляет возможность JavaScript взаимодействовать с устройствами ввода/вывода через свой API (написанном на C++), подключать другие внешние библиотеки, написанные на разных языках, обеспечивая вызовы к ним из JavaScript кода. Node.js применяется преимущественно на сервере, выполняя роль веб-сервера, но есть возможность разрабатывать на Node.js и десктопные оконные приложения (при помощи node-webkit и AppJS для Linux, Windows и MacOS) и даже программировать микроконтроллеры (например, tessel и espruino). В основе Node.js лежит событийно-ориентированное и асинхронное (или реактивное) программирование снеблокирующим вводом/выводом.
---

Цитата
Саша, это НЕ callback'и.... Уж этого от тебя НЕ ожидал!
Читай внимательней мой пост.
Чебурашка стал символом олимпийских игр. А чего достиг ты?
Тишина - самый громкий звук


https://github.com/nervgh
 
Цитата
Johny пишет:
Если кто-то знает, есть ли разница в скорости работы VBA и функций, буду очень благодарен
Добрый день, Евгений.

Скомпилированные функции, вызываемые из DLL, могут выполняться значительно быстрее, чем пользовательские функции VBA в тех случаях, когда производятся сложная обработка данных. На вызов DLL-функции из VBA тратится некоторое фиксированное время, а сам код функция отрабатывается обычно быстрее, чем аналогичный код UDF. Поэтому, чем более сложная обработка в самой функции, тем больший выигрыш в использовании DLL вместо VBA можно получить.  

Чаще всего в С-ных DLL производят математические вычисления

Особенно затратным является обмен текстовыми (String) данными, так как VBA при этом производит преобразование из Unicode в ASCII с добавление нулевого байта в конце строки при передаче текстового параметра в DLL-функцию, а  при возврате текста из DLL производится обратное преобразование. Значительно быстрее в таких случаях декларировать для DLL текстовый аргумент как As Long (As LongPtr для 64-бит ) с передачей в DLL параметра StrPrt(strVariable), и для удобства передавать еще и длину строки. Но при этом на стороне C-кода требуется учитывать, что текст будет в Unicode, а не ASCII.
 
А насчет обратного вызова - это стандартный прием.
Здесь на форуме приводились примеры такого вызова, например,  при использовании API-функции SetTimer
Изменено: ZVI - 16.03.2014 22:03:12
 
Цитата
ZVI пишет:
Чаще всего в С-ных DLL производят математические вычисления
Владимир, как всегда - большое спасибо за пояснения!  :)
А ведь ещё на C/C++ можно создавать XLL-надстройки. Я нашёл одну замечательную книгу "Financial Applications Using Excel Add-in Development in C and C++" (второе издание, включает Офис 2007), но дюже там всего много. А где бы достать информацию, где бы описывалось всё попроще? :) Ведь каждый формулист хотел бы иметь в загашнике свою быструю формулу, написанную на C/C++. :)
There is no knowledge that is not power
 
Что касается информационных материалов по XLL, то хороших книг мне не попадалось.
Впрочем, может быть сейчас уже и есть такие книги.

XLL изучал по Excel XLL Software Development Kit (SDK), в котором есть примеры кода как пользовательских функций, так и интерфейсных процедур.

В MSDN есть достаточно подробное описание, аналогичное тому, что в SDK:
http://msdn.microsoft.com/en-us/library/office/bb687883(v=office.15).aspx

Сам SKD можно скачать отсюда: http://www.microsoft.com/en-us/download/details.aspx?id=35567

Несколько соображений по быстродействию.

У XLL-функций очень быстрый доступ к объектной модели Excel, потому что эти функции обращаются напрямую к структурам памяти ячеек, листов и других объектов Excel.

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

И C-ные функции из DLL все равно связываются с объектной моделью Excel через VBA и поэтому имеют те же фиксированные задержки времени доступа.

Отмеченную медлительность VBA в некоторых случаях можно значительно уменьшить, используя VBA-функции, возвращающие массив значений (UDF используются в формулах массива). Так, например,  если использовать VBA–функцию, которая  выдает массив из 1000 значений вместо использования 1000 вызовов одной и той же UDF, то выигрыш в фиксированной задержке обмена данными между VBA и Excel будет примерно в 1000 раз. При этом быстродействие VBA (по данному критерию) приближается к быстродействию  XLL.

Удачные примеры XLL-надстроек:
1. Некоммерческая надстройка MOREFUNC from Laurent Longre (translated)
2. Коммерческая надстройка FastExcel from Charles Williams
Изменено: ZVI - 17.03.2014 00:02:48
 
Цитата
ZVI пишет:
У XLL-функций очень быстрый доступ к объектной модели Excel, потому что эти функции обращаются напрямую к структурам памяти ячеек, листов и других объектов Excel.
Цитата
ZVI пишет:
И C-ные функции из DLL все равно связываются с объектной моделью Excel через VBA и поэтому имеют те же фиксированные задержки времени доступа.
Владимир, хотелось бы получить уточнение по поводу второго комментария - имеется ввиду C-ные функции, которые не в XLL DLL?  :)  То есть если я пишу XLL надстройку, то скорость будет феноменальной, а если просто пишу Win DLL, то скорость падает. Так?  :)
Изменено: Johny - 17.03.2014 15:58:02
There is no knowledge that is not power
 
Цитата
Johny пишет:
То есть если я пишу XLL надстройку, то скорость будет феноменальной, а если просто пишу Win DLL, то скорость падает. Так?
Евгений, да, именно так. Но скорость падает только за счет медленного интерфейса между объектами VBA и Excel.
Этот интерфейс проверяет корректность обмена данными, автоматически корректирует типы данных и т.п.
XLL не использует этот  интерфейс, а работает напрямую с памятью объектной модели Excel, корректность обращений к памяти Excel при этом не контролируется, т.е. больший риск подвесить excel, если в коде что-то не так.
 
Мои результаты тестирования:

Обработка 100 тыс. строк
Циклом в VBA (модуль RUS)
1.Функция VBA - 110 секунд, размер - в файле Excel.
2.xll созданная с помощью С# + Excel-DNA запуск через Application.Run - 19,3 сек., размер 543 КБ.
3.Библиотека dll написанная на С (Си) и задекларированная в VBA (Declare) - запуск просто в функции 11,69 сек. (лучший результат, т.к. работает напрямую минуя Application.Run), размер 84 КБ.
3. Собственно сам сабж - xll написанная на С (Си) через WINAPI (Thread-Safe) запуск через Application.Run -один поток - 18,16 сек., размер 15 КБ.

Переходим к работе напрямую с листом (через функции) копируем на 100 тыс. строк (Модуль TEST).
1.Функция VBA - 111,23 секунды, почти так же.
2.xll созданная с помощью С# + Excel-DNA 14,43 секунды - уже быстрее, все же это xll созданный именно для работы с листом.
3.Библиотека dll написанная на С (Си) и задекларированная в VBA (Declare) - 14,84 сек. (результат хуже, обертка Excel кушает)
3. Собственно сам сабж - xll написанная на С (Си) через WINAPI (Thread-Safe) - 8 потоков - 1,77!!! секунды (работа на 8 ядрах).

Ноги здесь и здесь. Там же и файл-тест.
Изменено: bedvit - 17.10.2017 21:45:14
«Бритва Оккама» или «Принцип Калашникова»?
 
Для объективности:
Вариант "xll созданная с помощью С# + Excel-DNA" - многопоточный режим - [ExcelFunction(IsThreadSafe = true)]
RELEASE 2-е тестирование
VBA
Excel_xll-AddIn64-packed.xll (C#+Excel-DNA) 17,75

Лист-функция
Excel_xll-AddIn64-packed.xll (C#+Excel-DNA) (Thread-Safe) -3,035156 - 8 потоков
«Бритва Оккама» или «Принцип Калашникова»?
Страницы: 1
Наверх