Hướng dẫn tạo XLL Add-in bằng Delphi với thư viện BSXLLAddin (1 người xem)

[Nguyễn Duy Tuân]

Nhiều năm trước tôi từng demo video thư viện BSXLLAddin nhưng chưa từng chia sẻ thư viện để cộng đồng dùng thử (bản demo cách đây 8 năm). Thư viện này tôi dùng để tạo Add-in A-Tools từ nhiều năm nay. Hôm nay tôi chia sẻ thư viện (dùng giới hạn) cùng video hướng dẫn. Mục đích chia sẻ
+ Một ý tưởng về thiết kế XLL add-in theo ngôn ngữ thuần Delphi (có lẽ chưa từng ai làm trên Delphi như vậy ?). Người lập trình chỉ cần kiến thức cơ bản về Delphi, không cần am hiểu sâu về "C API in Excel".
+ Khơi gợi cộng đồng hiểu hơn về Delphi với khả năng thiết kế ứng dụng nói chung và Excel nói riêng.
+ Những người thiết kế add-in với số lượng hàm UDF ít có thể dùng miễn phí.
+ Những người thiết kế add-in với số lượng hàm nhiều, làm quy mô hơn cần liên hệ tác giả để hỗ trợ gói nâng cao.

Tạo XLL add-in bằng BSXLLAddin bạn nhận được một file xll duy nhất không phụ thuộc vào thư viện nào, bạn mang file này sang các máy dùng Excel 2007 trở lên cài là chạy.
XLL add-in được biên dịch ra mã máy nên bảo mật mã nguồn, tốc độ nhanh.

GIỚI THIỆU VỀ "BSXLLAddin"
"BSXLLAddin" là thư viện hỗ trợ tạo XLL add-in cho Excel trong Delphi hoặc C++ Builder theo cách dễ dàng nhất. cho phép bạn tạo các hàm UDF theo hai cách:

+ Cách 1: Dùng ngôn ngữ Delphi cơ bản
Cách này bạn cần dùng ngôn ngữ Delphi cơ bản, kiểu dữ liệu Delphi đã cung cấp để tạo ra các hàm theo kiểu Delphi là được. Ví dụ
(Mã nguồn hàm băng Delphi tôi dùng format C# cho dễ nhìn)

function MySum(const v1, v2: OleVariant): OleVariant; stdcall;
begin
    VariantInit(Result);
    Result := v1 + v2;
end;

(*) Theo cách này yêu cầu như sau:
- Hàm trả về kiểu OleVariant và có khai báo "stdcall" ở cuối.
- Các tham số phải khai báo "const" và kiểu phải là OleVariant.

+ Cách 2: Tạo hàm theo kiểu "C API in Excel"
Với cách này phức tạp hơn rất nhiều, bạn cần có kiến thức tốt về quản bộ nhớ và kiểu con trỏ - pointer.

CÁCH TẠO XLL BẰNG "BSXLLAddin"

+ Vào menu File -> New -> "Other..." -> "Bluesofts XLL Add-in"
+ Khai báo thông tin xll add-in của bạn. Bấm "Create".
Hệ thống tạo cho bạn dữ án mẫu với các hàm ví dụ để bạn hiểu và làm theo.

Các thông tin khác hãy đọc "Readme.txt" trong file nén tải về.

VIDEO HƯỚNG DẪN

Download thư viện BSXLLAddin​

 

[Nguyễn Duy Tuân]

BSXLLAddin - "BLUESOFTS XLL ADD-IN BUILDER"

Gói hỗ trợ tạo XLL trong Delphi này cho phép người lập trình chỉ cần dùng chuột thực hiện các bước là có ngay một XLL add-in. Việc duy nhất của người lập trình là viết hàm theo công việc của mình. Bỏ qua tất cả các yêu tố kỹ thuật phức tạp về API, việc này đã có BSXLLAddin làm ngầm phía dưới.

Hướng dẫn bằng hình ảnh

Bước 01: Tạo XLL Project

Vào menu File->New->Other... -> "Bluesofts XLL Add-ins"

(Màn hình khai báo các thông tin cho XLL add-in của bạn. Thông tin này được nạp vào mã nguồn sinh ra bạn vẫn có thể chỉnh sửa trong code)​

Sau khi nhấn "Create" chương trình tạo dã XLL Project với code mẫu.

​

Mã nguồn mẫu tự động tạo ra ở hai file "MyXLLAddin" và "Unit1"

library MyXLLAddin;
(*
    This is an XLL project created by "Bluesofts XLL Add-in Builder".
    It allows you to create UDF functions for Excel that run quickly.
    The project's source code is built according to the Excel API standard published by Microsoft.
    Author: Nguyen Duy Tuan - tuanktcdcn@yahoo.com - Tel: (+84)904210337
*)

uses
  SysUtils,
  Classes,
  Unit1 in 'Unit1.pas';

{$R *.res}

begin
end.

Toàn bộ code quan trọng nằm ở Unit1. Tại đây cần bạn tùy chỉnh theo nhu cầu của mình.

​

Bước 02: Sửa mã nguồn - Tạo hàm UDF của bạn.

Toàn bộ mã nguồn Unit1:

unit Unit1;
(*
    This unit creates XLL add-ins, which are automatically
    generated by Bluesoft's XLL Add-ins.
    It performs the following tasks:
        + Installation
        + Uninstallation
        + Adding UDF functions
    Author: Nguyen Duy Tuan - tuanktcdcn@yahoo.com - Tel: (+84)904210337
*)
interface

uses Winapi.Windows, Winapi.ActiveX,
     BSXLLAPIs;

type
    TBSMyXLL = class(TBSXLLAddin)
    public
        procedure OnInstall; override;
        procedure OnUninstall; override;
        procedure OnAddFunctions(AFunctions: TBSFunctions); override;
    end;

implementation

(*
Delphi functions will be mapped to XLL UDF functions
+ The function always returns OleVariant type
+ Parameters are always declared with "const". The data type is always OleVariant
*)

function MyName(): OleVariant; stdcall;
begin
    VariantInit(Result); //important!
    Result := 'Nguyễn Duy Tuân';
end;

function MySum(const v1, v2: OleVariant): OleVariant; stdcall;
begin
    VariantInit(Result);
    Result := v1 + v2;
end;

function MyAny(const x: OleVariant): OleVariant; stdcall;
begin
    VariantInit(Result);
    Result := x;
end;

function MyAsync(const Value: OleVariant): OleVariant; stdcall;
begin
    VariantInit(Result);
    Result := Value;
end;

{ TBSMyXLL - INSTALL XLL ADD-IN }
procedure TBSMyXLL.OnInstall;
begin
    MessageBoxW(GetActiveWindow, 'Đăng ký Add-In XLL thành công!'#13#10 +
                                                                 'XLL tạo từ thư viện "BSXLLAddin" trên trang www.bluesofts.net',
                                                                 LPWSTR(AddinName),
                                                                 MB_OK + MB_ICONINFORMATION);
end;

procedure TBSMyXLL.OnUninstall;
begin
    MessageBoxW(GetActiveWindow, 'Đã gỡ bỏ Add-In XLL.',
                                                                 LPWSTR(AddinName),
                                                                 MB_OK + MB_ICONWARNING);
end;

//INSTALL XLL UDF FUNCTION - MAPPING DELPHI FUNCTIONS
procedure TBSMyXLL.OnAddFunctions(AFunctions: TBSFunctions);
var
    fn: TBSFunction;
begin
    AFunctions.Category := 'Delphi XLL Functions';
    //Adding UDF functions to XLL - Mapping with Delphi functions
    //1. Add UDF function MyName()
    fn := AFunctions.Add('MyName', @MyName);
    fn.Function_help := 'Hàm trả về chuỗi unicode.';
    //-----------------

    //2. Add UDF function GetAny()
    fn := AFunctions.Add('MyAny', @MyAny);
    fn.Function_help := 'Hàm trả về một hoặc mảng giá trị tùy thuộc tham số truyền vào.';
        //Add parameters to the function.
    fn.Params.Add('Value', 'là môt hoặc mảng giá trị bất kỳ');
    //-----------------

    //3. Add UDF function MySum()
    fn := AFunctions.Add('MySum', @MySum);
    fn.Function_help := 'Hàm trả về tổng của a + b.';
        //Add parameters to the function.
    fn.Params.Add('A', 'Nhập số > 0');
    fn.Params.Add('B', 'Nhập số > 0 và < 100');
    //-----------------

    //4. Add UDF function MyFunc()
    fn := AFunctions.Add('MyFunc', @MySum);
    fn.Function_help := 'Hàm trả về tổng của a + b.';
        //Add parameters to the function.
    fn.Params.Add('A', 'Nhập số > 0');
    fn.Params.Add('B', 'Nhập số > 0 và < 100');
    //-----------------

    //5. Add UDF function MyAsync()
    fn := AFunctions.Add('MyAsync', @MyAsync, True);
    fn.Function_help := 'Hàm chạy theo cơ chế bất đồng bộ.';
        //Add parameters to the function.
    fn.Params.Add('Value', 'là môt hoặc mảng giá trị bất kỳ');
    //-----------------
end;

initialization
    MyXLLAddin := TBSMyXLL.Create('My Excel XLL Add-in',
                                                                 'Author: Nguyễn Duy Tuân - https://bluesofts.net');

finalization

end.

Bạn có thể tự thêm hàm khác như :

    //6. Add UDF function MyMultip()
    fn := AFunctions.Add('MyMultip', @MyMultip);
    fn.Function_help := 'Hàm trả về phép nhân hai số a và b.';
        //Add parameters to the function.
    fn.Params.Add('A', 'Nhập con số bất kỳ');
    fn.Params.Add('B', 'Nhập con số bất kỳ');
    //-----------------

Hàm MyMultip viết thuần bằng Delphi như sau

function MyMultip(const a, b: OleVariant): OleVariant; stdcall;
begin
    VariantInit(Result);
    Result := a*b;
end;

(*) Nguyên tắc tạo hàm:
- Hàm trả về kiểu OleVariant và có khai báo "stdcall" ở cuối.
- Các tham số phải khai báo "const" và kiểu phải là OleVariant.

Bạn thấy việc tạo XLL add-in bằng thư viện BSXLLAddin rất dễ dàng đúng không? Bạn chỉ cần quan tâm tạo hàm theo công việc của bạn và khai báo UDF trong sự kiện OnAddFunctions là xong!

Bước 03: Biên dịch - Nhận kết quả sau khi biên dịch là file XLL.

Bạn chỉ cần mang duy nhất một file XLL sang các máy tính cài vào Mocrosoft Excel (tối thiểu từ Excel 2007) là được.



Hàm UDF trong XLL gọi trên bảng tính Excel.
​

Còn tiếp...

 

[nguyendang95]

Thư viện của bạn có hai cách tạo hàm, vậy hiệu năng giữa hai cách triển khai này có chênh lệch nhau lắm không?
Với lại, thư viện của bạn có hỗ trợ đầy đủ tính năng của Excel C API không? Ví dụ như, khai báo hàm an toàn thread để tận dụng tính năng Multithreaded Recalculation của Excel hoặc khai báo hàm sửa giá trị tại chỗ chẳng hạn.

 

[Kiều Mạnh]

Thư viện của bạn có hai cách tạo hàm, vậy hiệu năng giữa hai cách triển khai này có chênh lệch nhau lắm không?
Với lại, thư viện của bạn có hỗ trợ đầy đủ tính năng của Excel C API không? Ví dụ như, khai báo hàm an toàn thread để tận dụng tính năng Multithreaded Recalculation của Excel hoặc khai báo hàm sửa giá trị tại chỗ chẳng hạn.

dòm sơ qua Cái Class là biết thôi

type
    TBSMyXLL = class(TBSXLLAddin)
    public
        procedure OnInstall; override;
        procedure OnUninstall; override;
        procedure OnAddFunctions(AFunctions: TBSFunctions); override;
    end;

Trong đó có chức năng tương đương cơ bản như sau

1/ OnInstall = xlAutoOpen
2/ OnUninstall = xlAutoRemove
3/ OnAddFunctions = AddFunction

còn sử dụng const v1, v2: OleVariant thì trong Class TBSXLLAddin sử dụng hàm Map kiểu dữ liệu tương đương cho nó nhưng tiền ẩn nhiều lỗi có thể sau này viết nhiều mới thấy

4/ Viết procedure OnAddFunctions(AFunctions: TBSFunctions); override; nếu có 100 hàm thêm vào xoá đi quản lý rất cực vì mã sẽ dài nhiều mét
xong lại dò tìm xoá đi hay thêm vào

5/ Còn hiệu năng chắc chắn sẽ mất một ít chi phí Map chuyển đổi kiểu dữ liệu là chắc chắn còn nhiều hay ít thì phải thử mới biết

thay vì dùng trưc tiếp khác với qua hàm trung gian chuyển đổi

6/ Chốt lại viết C++/C++ builder là dễ nhất và code đơn giản nhất có 3 mục sau

- Không phí thời gian dịch gần hai ngàn dòng mã C++ sang Delphi
- khi Ms ra bộ mới tải về thay thế và sử dụng không cần viết gì thêm
- code ngắn hơn và hiệu xuất tốt hơn

7/ ẩn chứa trong cái class(TBSXLLAddin) là các hàm Map kiểu dữ liệu từ const v1, v2: OleVariant sang kiểu dữ liệu phù họp với c++ và xử lý các chức năng khác như quản lý và giải phóng vvv

cũng đơn giản thôi rảnh tôi thêm vào một unit sử dụng như vậy không quá khó

 

[Nguyễn Duy Tuân]

Thư viện của bạn có hai cách tạo hàm, vậy hiệu năng giữa hai cách triển khai này có chênh lệch nhau lắm không?
Với lại, thư viện của bạn có hỗ trợ đầy đủ tính năng của Excel C API không? Ví dụ như, khai báo hàm an toàn thread để tận dụng tính năng Multithreaded Recalculation của Excel hoặc khai báo hàm sửa giá trị tại chỗ chẳng hạn.

Câu hỏi tốc độ giữa hai cách tạo UDF trong BSXLLAddin?

+ Cách 1 dùng qua cơ chế mapping data type và, thư viện chỉ phải làm phần chuyển đổi để kiểu dữ liệu Delphi hiểu kiểu mà C API in Excel khai báo nên ở giai đoạn mapping thì cách này chậm hơn chút xíu về tốc độ so với Cách 2 nhưng không thực sự đáng kể. Dữ liệu truyền vào qua các tham số hàm đôi khi chỉ là phần nhỏ mà ta phải trả phí thời gian, khối lượng tính toán và các toán tử thực thi về chuỗi mới làm ảnh hưởng lớn đến hiệu suất tính toán và đây chính là nội dung chính công việc mà Cách 1 hay Cách 2 đều phải làm tương tự. Xét trên bài toán tổng thể, có những cái né không chuyển đổi trước thì phần sau có thể vẫn phải chuyển đổi để phù hợp cho hàng loạt những việc phân tích. Ứng dụng thực tế. Như đã nói, thư viện này tôi dùng trong Add-in A-Tools từ nhiều năm nay, triển khai các hàm giải quyết bài toán lớn của doanh nghiệp, tùy vào mỗi hàm mà tôi buộc phải dùng Cách 1 chứ không phải Cách 2 nếu không đáng để so sánh hơn thua tốc độ mapping, những hàm cần hoặc rất đáng giá về tốc độ thì sang Cách 2. Vì lý do đó thư viện này tôi cho phép người lập trình dùng cả hai cách.

Điều mà tôi rất thích thú và tâm huyết khi xây dựng thư viện này là muốn cho người lập trình Delphi dù kiến thức ở mức sơ đẳng, cơ bản đều có thể tạo được XLL Add-in. Đặc biệt thư viện hỗ trợ lập trình XLL trong Delphi hiện nay trên thế giới rất nghèo nàn. Cũng biết đâu trên thế giới vẫn có người làm XLL trên Delphi rất hoành tráng nhưng họ không công bố?

+ Cách 2: đây là cách mà thư viện làm việc trực tiếp với kiểu dữ liệu chuẩn C API in Excel. Cách gọi hàm cũng như vậy. Có thể nói hiệu suất C/C++ như thế nào thì bên Delphi cũng gần như tương đương. Cách đây tầm hơn 10 năm tôi từng có nhiều buổi làm việc và giao lưu trực tiếp với một trong những chuyên gia về an ninh mạng, dịch ngược mã máy, C/C++, C++ Builder tại Việt Nam để đánh giá tốc độ giữa C/C++ ( Visual C/C++ và C++ Builder) và Delphi khi tạo XLL. Cuối cùng tôi chính thức đầu tư công sức vào xây dựng bộ thư viện BSXLLAddin trên Delphi. Mặc dù cách đây 20 năm (từ năm 2006) tôi đã tạo XLL Add-in cho việc Đọc số thành chữ trong Excel là AccHelper tại đây. Thời gian đấy làm cho vui thôi chứ chưa chính thức làm vào sản phẩm thương mại vì thời gian dó bản thân chưa đủ năng lực để làm nghiêm túc. Nói vậy để các bạn hình dung nếu dùng Cách 2 thì hiệu suất có thể coi dùng trong Delphi hay C/C++ gần như nhau.

Câu hỏi về BSXLLAddin có hỗ trợ đầy đủ tính năng mà C API in Excel cung cấp không? Tạo hàm Thread Safe Function?
Tôi định chia sẻ phần ví dụ ứng dụng này ở một thời gian phù hợp trong chủ đề này. Nhân bạn hỏi thì xin cung cấp thông tin luôn. Thư viện BSXLLAddin cung cấp tất cả các tính chất mà trong "C API in Excel" cóng bố từ Microsoft. Trừ loại "Cluster safe functions" chưa ứng dụng trong phiên bản này. Toàn bộ tài liệu xây dựng XLL mà Microsoft công bố mới nhất đến thời điểm này tô đã cập nhật vào bộ thư viện này.

Để tạo các hàm với các tính chất khác nhau, Trong class TBSFunction khai báo thuộc tính có tên FunctionTypes, là một kiểu SET of TBSFunctionType (gồm các hằng số:
ftVolatile,
ftMacroSheet,
ftFunctionSheet,
ftThreadSafe,
ftClusterSafe)

​

Một thông tin khác mà bạn chưa hỏi đó là XLL cho phép tạo WorksheetFunction - UDF là các hàm chúng ta gọi trong công thức, trên bảng tính Excel; loại hai là Command, là dạng lệnh dùng trong Sheet Macro4.0 (công nghệ bây giờ ít người dùng nhưng Microsoft vẫn duy trì nó). Để tạo lệnh thì khai báo vào thuộc tinh Macro_Type

​

Gửi một phần hình ảnh mã nguồn khai báo từ "công nghệ lõi"

​

Bạn đang làm C/C++ để tạo XLL nhìn khai báo trên là biết công việc của tôi là đưa những gì Microsoft công bố về tạo XLL vào trong Delphi.

 

[Nguyễn Duy Tuân]

dòm sơ qua Cái Class là biết thôi

type
    TBSMyXLL = class(TBSXLLAddin)
    public
        procedure OnInstall; override;
        procedure OnUninstall; override;
        procedure OnAddFunctions(AFunctions: TBSFunctions); override;
    end;

Trong đó có chức năng tương đương cơ bản như sau

1/ OnInstall = xlAutoOpen
2/ OnUninstall = xlAutoRemove
3/ OnAddFunctions = AddFunction

còn sử dụng const v1, v2: OleVariant thì trong Class TBSXLLAddin sử dụng hàm Map kiểu dữ liệu tương đương cho nó nhưng tiền ẩn nhiều lỗi có thể sau này viết nhiều mới thấy

4/ Viết procedure OnAddFunctions(AFunctions: TBSFunctions); override; nếu có 100 hàm thêm vào xoá đi quản lý rất cực vì mã sẽ dài nhiều mét
xong lại dò tìm xoá đi hay thêm vào

5/ Còn hiệu năng chắc chắn sẽ mất một ít chi phí Map chuyển đổi kiểu dữ liệu là chắc chắn còn nhiều hay ít thì phải thử mới biết

thay vì dùng trưc tiếp khác với qua hàm trung gian chuyển đổi

6/ Chốt lại viết C++/C++ builder là dễ nhất và code đơn giản nhất có 3 mục sau

- Không phí thời gian dịch gần hai ngàn dòng mã C++ sang Delphi
- khi Ms ra bộ mới tải về thay thế và sử dụng không cần viết gì thêm
- code ngắn hơn và hiệu xuất tốt hơn

7/ ẩn chứa trong cái class(TBSXLLAddin) là các hàm Map kiểu dữ liệu từ const v1, v2: OleVariant sang kiểu dữ liệu phù họp với c++ và xử lý các chức năng khác như quản lý và giải phóng vvv

cũng đơn giản thôi rảnh tôi thêm vào một unit sử dụng như vậy không quá khó

Làm việc với thư viện gốc của C nói chung và C API in Excel phải có kiến thức về quản lý bộ nhớ, con trỏ trong Delphi mới chuyển đổi được, phải tìm cách làm được việc này nếu không thì dự án sẽ có ngày "phá sản". Nhiều năm tôi nghiên cứu về kiểu dữ liệu OLE và XLOPER/XLOPER12 của C API in Excel nên việc mapping data type tại thời điểm này tôi khẳng định không có lỗi, nó đang dùng trong Add-in A-Tools từ nhiều năm nay và liên tục được cải tiến và hoàn thiện. Đó là tuyên bố của nhà phát triển. Biết đâu lúc nào đó tòi ra lỗi? Win còn đang lỗi và update vá liên tục. Nên hy vọng một ngày nào đó nếu bạn hay bất kỳ ai phát hiện ra lỗi thì thông báo lên đây tôi rất vui để xử lý - Một trong những thứ làm công việc cuốn hút trong một ngày của tôi là fix bugs đấy.

Bạn quen và thích thú ngôn ngữ lập trình hay IDE nào thì cứ đầu tư làm thật tốt, cái gì cũng có cái giá của nó, có công sức học và đầu tư nghiêm túc thì có thành quả tốt. Các vấn đề khác về ngôn ngữ tôi không bàn luận thêm.

Vấn đề về khai báo hàm UDF trong thủ tục sự kiện OnAddFucntions(). Tôi đã tính trước cho phép nhà lập trình linh hoạt làm trong này như sau:

1. Khai báo qua interface thông qua class TBSFunction như ví dụ trong video ở trên.
2. Khai báo danh sách các hàm UDF cùng thông tin chi tiết qua cấu trúc JSON. Thư viện BSXLLAddin cung cấp các hàm chuyên đề làm việc này.

//INSTALL XLL UDF FUNCTION - MAPPING DELPHI FUNCTIONS
procedure TBSMyXLL.OnAddFunctions(AFunctions: TBSFunctions);
var
  JSONString: string;
  JSONFileName: string;
  UDFCount: Integer;
begin
  JSONString := 'Khai báo cấu trúc JSON thông tin về hàm UDF';
  UDFCount := GetFunctionFromJSON(JSONString, AFunctions);
  //Bạn có thể dùng tập tin khai báo cấu trúc JSON bên ngoài
  JSONFileName := '.\JSONDefFuns.json';
  UDFCount := GetFunctionFromJSONFile(JSONString, AFunctions);
end;

Nhiều cái đã xây dựng từ nhiều năm rồi. AI đó cứ nói cần gì tôi lại cung cấp thêm.

 

[Kiều Mạnh]

Làm việc với thư viện gốc của C nói chung và C API in Excel phải có kiến thức về quản lý bộ nhớ, con trỏ trong Delphi mới chuyển đổi được, phải tìm cách làm được việc này nếu không thì dự án sẽ có ngày "phá sản". Nhiều năm tôi nghiên cứu về kiểu dữ liệu OLE và XLOPER/XLOPER12 của C API in Excel nên việc mapping data type tại thời điểm này tôi khẳng định không có lỗi, nó đang dùng trong Add-in A-Tools từ nhiều năm nay và liên tục được cải tiến và hoàn thiện. Đó là tuyên bố của nhà phát triển. Biết đâu lúc nào đó tòi ra lỗi? Win còn đang lỗi và update vá liên tục. Nên hy vọng một ngày nào đó nếu bạn hay bất kỳ ai phát hiện ra lỗi thì thông báo lên đây tôi rất vui để xử lý - Một trong những thứ làm công việc cuốn hút trong một ngày của tôi là fix bugs đấy.

Bạn quen và thích thú ngôn ngữ lập trình hay IDE nào thì cứ đầu tư làm thật tốt, cái gì cũng có cái giá của nó, có công sức học và đầu tư nghiêm túc thì có thành quả tốt. Các vấn đề khác về ngôn ngữ tôi không bàn luận thêm.

Vấn đề về khai báo hàm UDF trong thủ tục sự kiện OnAddFucntions(). Tôi đã tính trước cho phép nhà lập trình linh hoạt làm trong này như sau:

1. Khai báo qua interface thông qua class TBSFunction như ví dụ trong video ở trên.
2. Khai báo danh sách các hàm UDF cùng thông tin chi tiết qua cấu trúc JSON. Thư viện BSXLLAddin cung cấp các hàm chuyên đề làm việc này.

//INSTALL XLL UDF FUNCTION - MAPPING DELPHI FUNCTIONS
procedure TBSMyXLL.OnAddFunctions(AFunctions: TBSFunctions);
var
  JSONString: string;
  JSONFileName: string;
  UDFCount: Integer;
begin
  JSONString := 'Khai báo cấu trúc JSON thông tin về hàm UDF';
  UDFCount := GetFunctionFromJSON(JSONString, AFunctions);
  //Bạn có thể dùng tập tin khai báo cấu trúc JSON bên ngoài
  JSONFileName := '.\JSONDefFuns.json';
  UDFCount := GetFunctionFromJSONFile(JSONString, AFunctions);
end;

Nhiều cái đã xây dựng từ nhiều năm rồi. AI đó cứ nói cần gì tôi lại cung cấp thêm.

chốt lại bài số 4 tôi suy đoán chính xác ... dừng chờ xem tiếp theo có gì mới

 

[Nguyễn Duy Tuân]

chốt lại bài số 4 tôi suy đoán chính xác ... dừng chờ xem tiếp theo có gì mới

Mục số 4) tôi đã trả lời rồi bạn đọc lại bài trên. Tôi viết lại mã nguồn để giải quyết.

//INSTALL XLL UDF FUNCTION - MAPPING DELPHI FUNCTIONS
procedure TBSMyXLL.OnAddFunctions(AFunctions: TBSFunctions);
var
  JSONString: string;
  JSONFileName: string;
  UDFCount: Integer;
begin
  JSONString := 'Khai báo cấu trúc JSON thông tin về hàm UDF';
  UDFCount := GetFunctionFromJSON(JSONString, AFunctions);
  //Bạn có thể dùng tập tin khai báo cấu trúc JSON bên ngoài
  JSONFileName := '.\JSONDefFuns.json';
  UDFCount := GetFunctionFromJSONFile(JSONString, AFunctions);
end;

Dù có hàng ngàn hàm cũ chỉ mấy dòng trên thôi, hàm trong thư viện làm cho rồi.

 

[Kiều Mạnh]

Mục số 4) tôi đã trả lời rồi bạn đọc lại bài trên. Tôi viết lại mã nguồn để giải quyết.

//INSTALL XLL UDF FUNCTION - MAPPING DELPHI FUNCTIONS
procedure TBSMyXLL.OnAddFunctions(AFunctions: TBSFunctions);
var
  JSONString: string;
  JSONFileName: string;
  UDFCount: Integer;
begin
  JSONString := 'Khai báo cấu trúc JSON thông tin về hàm UDF';
  UDFCount := GetFunctionFromJSON(JSONString, AFunctions);
  //Bạn có thể dùng tập tin khai báo cấu trúc JSON bên ngoài
  JSONFileName := '.\JSONDefFuns.json';
  UDFCount := GetFunctionFromJSONFile(JSONString, AFunctions);
end;

Dù có hàng ngàn hàm cũ chỉ mấy dòng trên thôi, hàm trong thư viện làm cho rồi.

vấn đề là người dùng thêm vào xoá đi ... xong dò tìm mà thêm và xoá trong cái SONFileName := '.\JSONDefFuns.json'; khi có cả ngàn dòng

 

[Nguyễn Duy Tuân]

vấn đề là người dùng thêm vào xoá đi ... xong dò tìm mà thêm và xoá trong cái SONFileName := '.\JSONDefFuns.json'; khi có cả ngàn dòng

Có đủ phương thức để làm với danh sách hàm đã có. Lại chia sẻ thêm một phần hình ảnh về "công nghệ lõi" của thư viện BSXLLAddin.

​

 

[nguyendang95]

Câu hỏi tốc độ giữa hai cách tạo UDF trong BSXLLAddin?

+ Cách 1 dùng qua cơ chế mapping data type và, thư viện phải làm phần chuyển đổi để kiểu dữ liệu Delphi hiểu kiểu mà C API in Excel khai báo nên nếu chỉ xét về giai đoạn mapping thì cách này chậm hơn chút xíu về tốc độ so với Cách 2 nhưng không thực sự đáng kể. Dữ liệu truyền vào qua các tham số hàm đôi khi chỉ là phần nhỏ mà ta phải trả phí thời gian, khối lượng tính toán và các toán tử thực thi về chuỗi mới làm ảnh hưởng lớn đến hiệu suất tính toán. Xét trên bài toán tổng thể, có những cái né không chuyển đổi trước thì phần sau có thể vẫn phải chuyển đổi để phù hợp cho hàng loạt những việc phân tích. Ưng dụng thực tế. Như đã nói, thư viện này tôi dùng trong Add-in A-Tools từ nhiều năm nay, triển khai các hàm giải quyết bài toán lớn của doanh nghiệp, tùy vào mỗi hàm mà tôi buộc phải dùng Cách 1 nếu không đáng để so sánh hơn thua tốc độ mapping, những hàm cần hoặc rất đáng giá về tốc độ thì sang Cách 2. Vì lý do đó thư viện này tôi cho phép người lập trình dùng cả hai cách.

Điều mà tôi rất thích thú và tâm huyết khi xây dựng thư viện này là muốn cho người lập trình Delphi dù kiến thức ở mức sơ đẳng, cơ bản đều có thể tạo được XLL Add-in. Đặc biệt thư viện hỗ trợ lập trình XLL trong Delphi hiện nay trên thế giới rất nghèo nàn. Cũng biết đâu trên thế giới vẫn có người làm XLL trên Delphi rất hoành tráng nhưng họ không công bố?

+ Cách 2: đây là cách mà thư viện làm việc trực tiếp với kiểu dữ liệu chuẩn C API in Excel. Cách gọi hàm cũng như vậy. Có thể nói hiệu suất C/C++ như thế nào thì bên Delphi cũng gần như tương đương. Cách đây tầm hơn 10 năm tôi từng có nhiều buổi làm việc và giao lưu trực tiếp với một trong những chuyên gia về an ninh mạng, dịch ngược mã máy, C/C++, C++ Builder tại Việt Nam để đánh giá tốc độ giữa C/C++ ( Visual C/C++ và C++ Builder) và Delphi khi tạo XLL. Cuối cùng tôi chính thức đầu tư công sức vào xây dựng bộ thư viện BSXLLAddin trên Delphi. Mặc dừ cách đây 20 năm (từ năm 2006) tôi đã tạo XLL Add-in cho việc Đọc số thành chữ trong Excel là AccHelper tại đây. Thời gian đấy làm cho vui thôi chứ chưa chính thức làm vào sản phẩm thương mại vì thời gian dó bản thân chưa đủ năng lực để làm nghiêm túc. Nói vậy để các bạn hình dung nếu dùng cách 2 thì hiệu suất có thể coi dùng trong Delphi hay C/C++ gần như nhau.

Câu hỏi về BSXLLAddin có hỗ trợ đầy đủ tính năng mà C API in Excel cung cấp không? Tạo hàm Thread Safe Function?
Tôi định chia sẻ phần ví dụ ứng dụng này ở một thời gian phù hợp trong chủ đề này. Nhân bạn hỏi thì xin cung cấp thông tin luôn. Thư viện BSXLLAddin cung cấp tất cả các tính chất mà trong "C API in Excel" cóng bố từ Microsoft. Trừ loại "Cluster safe functions" chưa ứng dụng trong phiên bản này. Toàn bộ tài liệu xây dựng XLL mà Microsoft công bố mới nhất đến thời điểm này tô đã cập nhật vào bộ thư viện này.

Để tạo các hàm với các tính chất khác nhau, Trong class TBSFunction khai báo thuộc tính có tên FunctionTypes, là một kiểu SET of TBSFunctionType (gồm các hằng số:
ftVolatile,
ftMacroSheet,
ftFunctionSheet,
ftThreadSafe,
ftClusterSafe)

View attachment 312200​

Một thông tin khác mà bạn chưa hỏi đó là XLL cho phép tạo WorksheetFunction - UDF là các hàm chúng ta gọi trong công thức, trên bảng tính Excel; loại hai là Command, là dạng lệnh dùng trong Sheet Macro4.0 (công nghệ bây giờ ít người dùng nhưng Microsoft vẫn duy trì nó). Để tạo lệnh thì khai bóa vào thuộc tinh Macro_Type

View attachment 312201​

Gửi một phần hình ảnh mã nguồn khai báo từ "công nghệ lõi"

View attachment 312202​

Bạn đang làm C/C++ để tạo XLL nhìn khai báo trên là biết công việc của tôi là đưa những gì Microsoft công bố về tạo XLL vào trong Delphi.

Vậy thì ổn rồi, dù sao thì cũng nên làm rõ những điểm này để người dùng cân nhắc xem dùng cách nào là tốt nhất, nếu có thêm ví dụ nữa thì càng tốt.
Mà hình như tôi thấy thư viện của bạn không hỗ trợ viết hàm bất đồng bộ, đúng không nhỉ?

 

[Nguyễn Duy Tuân]

Vậy thì ổn rồi, dù sao thì cũng nên làm rõ những điểm này để người dùng cân nhắc xem dùng cách nào là tốt nhất, nếu có thêm ví dụ nữa thì càng tốt.
Mà hình như tôi thấy thư viện của bạn không hỗ trợ viết hàm bất đồng bộ, đúng không nhỉ?

CÓ rồi đấy. Trong bài #! ví dụ số 5. Tôi viết lại ở đây

    //5. Add UDF function MyAsync()
    fn := AFunctions.Add('MyAsync', @MyAsync, True);
    fn.Function_help := 'Hàm chạy theo cơ chế bất đồng bộ.';
        //Add parameters to the function.
    fn.Params.Add('Value', 'là môt hoặc mảng giá trị bất kỳ');
    //-----------------

Tham số thứ 3 của hàm Add là True.
fn := AFunctions.Add('MyAsync', @MyAsync, True);

Cấu trúc viết hàm Delphi như mẫu chung. Lưu ý hàm này đang chạy trong thread riêng.

function MyAsync(const Value: OleVariant): OleVariant; stdcall;
begin
    VariantInit(Result);
    Result := Value;
end;

 

[nguyendang95]

CÓ rồi đấy. Trong bài #! ví dụ số 5. Tôi viết lại ở đây

    //5. Add UDF function MyAsync()
    fn := AFunctions.Add('MyAsync', @MyAsync, True);
    fn.Function_help := 'Hàm chạy theo cơ chế bất đồng bộ.';
        //Add parameters to the function.
    fn.Params.Add('Value', 'là môt hoặc mảng giá trị bất kỳ');
    //-----------------

Tham số thứ 3 của hàm Add là True.
fn := AFunctions.Add('MyAsync', @MyAsync, True);

Cấu trúc viết hàm Delphi như mẫu chung. Lưu ý hàm này đang chạy trong thread riêng.

function MyAsync(const Value: OleVariant): OleVariant; stdcall;
begin
    VariantInit(Result);
    Result := Value;
end;

Bạn có thể trình bày thêm cách thức triển khai hàm bất đồng bộ được không, với thêm một số ví dụ về trường hợp tính toán mất nhiều thời gian (ví dụ giả lập thời gian tính toán kéo dài bằng cách gọi Sleep(5000) chẳng hạn)?

 

[Nguyễn Duy Tuân]

Bạn có thể trình bày thêm cách thức triển khai hàm bất đồng bộ được không, với thêm một số ví dụ về trường hợp tính toán mất nhiều thời gian (ví dụ giả lập thời gian tính toán kéo dài bằng cách gọi Sleep(5000) chẳng hạn)?

Ví dụ về tạo hàm UDF theo tính chất BẤT ĐỒNG BỘ TRONG TRONG BSXLLAddin
Trong ví dụ số 5 chỉ cần giả định thêm một lượng thời gian chờ là thấy nó hoạt động như thế nào.

function MyAsync(const Value: OleVariant): OleVariant; stdcall;
begin
    VariantInit(Result);
    Result := Value;
    Sleep(10000); //Giả định khối lượng tính toán mất 10000 mili giây.
end;

Sau khi nhập công thức có hàm MyAsync trên bảng tính, người dùng có thể làm việc khác trong khi hàm MyAsync đang làm gì đó. Khi nào xong nó trả về kết quả. Người dùng không bị ép phải chờ hàm, cứ làm việc khác, khi hàm chưa trả về kết quả thì tại nơi công thức có hàm MyAssync hiển thị là #CALC!.

Các bạn lưu ý loại hàm chạy cơ chế bất đồng bộ này chỉ chạy từ Excel 2013 trở lên.

​

 

[nguyendang95]

Trong ví dụ số 5 chỉ cần giả định thêm một lượng thời gian chờ là thấy nó hoạt động như thế nào.

function MyAsync(const Value: OleVariant): OleVariant; stdcall;
begin
    VariantInit(Result);
    Result := Value;
    Sleep(10000); //Giả định khối lượng tính toán mất 10000 mili giây.
end;

Sau khi nhập công thức có hàm MyAsync trên bảng tính, người dùng có thể làm việc khác trong khi hàm MyAsync đang làm gì đó. Khi bào xong nó trả về kết quả. Người dùng không bị ép phải chờ hàm, cứ làm việc khác, khi hàm chưa trả về kết quả thì tại nơi công thức có hàmMyAssync hiển thị là #CALC!.

Các bạn lưu ý loại hàm chạy cơ chế bất đồng bộ này chỉ chạy từ Excel 2013 trở lên.

View attachment 312211​

OK như vậy là đầy đủ rồi, có thêm ví dụ thế này mới dễ hiểu được.

 

[nguyendang95]

dòm sơ qua Cái Class là biết thôi

type
    TBSMyXLL = class(TBSXLLAddin)
    public
        procedure OnInstall; override;
        procedure OnUninstall; override;
        procedure OnAddFunctions(AFunctions: TBSFunctions); override;
    end;

Trong đó có chức năng tương đương cơ bản như sau

1/ OnInstall = xlAutoOpen
2/ OnUninstall = xlAutoRemove
3/ OnAddFunctions = AddFunction

còn sử dụng const v1, v2: OleVariant thì trong Class TBSXLLAddin sử dụng hàm Map kiểu dữ liệu tương đương cho nó nhưng tiền ẩn nhiều lỗi có thể sau này viết nhiều mới thấy

4/ Viết procedure OnAddFunctions(AFunctions: TBSFunctions); override; nếu có 100 hàm thêm vào xoá đi quản lý rất cực vì mã sẽ dài nhiều mét
xong lại dò tìm xoá đi hay thêm vào

5/ Còn hiệu năng chắc chắn sẽ mất một ít chi phí Map chuyển đổi kiểu dữ liệu là chắc chắn còn nhiều hay ít thì phải thử mới biết

thay vì dùng trưc tiếp khác với qua hàm trung gian chuyển đổi

6/ Chốt lại viết C++/C++ builder là dễ nhất và code đơn giản nhất có 3 mục sau

- Không phí thời gian dịch gần hai ngàn dòng mã C++ sang Delphi
- khi Ms ra bộ mới tải về thay thế và sử dụng không cần viết gì thêm
- code ngắn hơn và hiệu xuất tốt hơn

7/ ẩn chứa trong cái class(TBSXLLAddin) là các hàm Map kiểu dữ liệu từ const v1, v2: OleVariant sang kiểu dữ liệu phù họp với c++ và xử lý các chức năng khác như quản lý và giải phóng vvv

cũng đơn giản thôi rảnh tôi thêm vào một unit sử dụng như vậy không quá khó

Nếu dùng cách 1 ở trên thì nghĩa là dùng kiểu của OLE (I1, I2, I4, BSTR, R4, R8, v.v...) để làm kiểu dữ liệu trung gian, nói như vậy thì trình tự chuyển đổi sẽ như thế này:

1. Excel cung cấp tham số (nếu có) dưới dạng XLOPER/XLOPER12, tuỳ thuộc vào kiểu XLOPER/XLOPER12 mang theo là gì (chuỗi, số hay mảng) mà từ đó chuyển đổi sang kiểu OLE cho phù hợp, dùng VARIANT (OleVariant trong Delphi) là phương tiện chứa đựng.
2. Tính toán, xử lý những tham số này, xác định kết quả.
3. Chuyển đổi kết quả sau khi tính toán dưới dạng VARIANT, sau đó lại chuyển đổi tiếp thành dạng mà Excel yêu cầu dưới dạng XLOPER/XLOPER12.
4. Dọn dẹp bộ nhớ.
5. Trả về kết quả cho Excel, kích hoạt hàm gọi lại xlAutoFree/xlAutoFree12 nếu cần để dọn dẹp vùng nhớ cấp phát động.

Nhìn chung là điểm nghẽn hiệu năng nằm ở 1, 3 và 4 thôi, có thể không đáng kể nếu hàm không quá phức tạp, nhưng nếu muốn viết hàm yêu cầu khắt khe về hiệu năng thì không ổn. Lúc này phải sử dụng cách 2 theo ý kiến của chủ thớt.

 

[Kiều Mạnh]

Nếu dùng cách 1 ở trên thì nghĩa là dùng kiểu của OLE (I1, I2, I4, BSTR, R4, R8, v.v...) để làm kiểu dữ liệu trung gian, nói như vậy thì trình tự chuyển đổi sẽ như thế này:

1. Excel cung cấp tham số (nếu có) dưới dạng XLOPER/XLOPER12, tuỳ thuộc vào kiểu XLOPER/XLOPER12 mang theo là gì (chuỗi, số hay mảng) mà từ đó chuyển đổi sang kiểu OLE cho phù hợp, dùng VARIANT (OleVariant trong Delphi) là phương tiện chứa đựng.
2. Tính toán, xử lý những tham số này, xác định kết quả.
3. Chuyển đổi kết quả sau khi tính toán dưới dạng VARIANT, sau đó lại chuyển đổi tiếp thành dạng mà Excel yêu cầu dưới dạng XLOPER/XLOPER12.
4. Dọn dẹp bộ nhớ.
5. Trả về kết quả cho Excel, kích hoạt hàm gọi lại xlAutoFree/xlAutoFree12 nếu cần để dọn dẹp vùng nhớ cấp phát động.

Nhìn chung là điểm nghẽn hiệu năng nằm ở 1, 3 và 4 thôi, có thể không đáng kể nếu hàm không quá phức tạp, nhưng nếu muốn viết hàm yêu cầu khắt khe về hiệu năng thì không ổn. Lúc này phải sử dụng cách 2 theo ý kiến của chủ thớt.

Chính xác là như vậy ... Xong tôi Copy bài của Ông cho ChatGPT phân tích câu từ, thuật ngữ chính xác hơn dễ hiểu hơn

Vì tôi chỉ là tay ngang code két nên câu từ sẽ sai còn hiểu và làm thực tế lại khác ... dòm qua cái Class là cơ bản hình dung ra thôi

Nội dung sau do ChatGPT phân tích

Theo mình hiểu, nếu framework sử dụng OleVariant làm kiểu dữ liệu trung gian thì mỗi lần Excel gọi một UDF sẽ phải trải qua ít nhất hai lần chuyển đổi dữ liệu (marshalling), chưa tính thời gian xử lý của chính hàm người dùng.

Bước 1: Excel truyền tham số dưới dạng XLOPER/XLOPER12. Framework sẽ chuyển các tham số này sang OleVariant để gọi hàm Delphi/C++ Builder.

Bước 2: Hàm do người dùng tự định nghĩa thực hiện xử lý dữ liệu.

Bước 3: Kết quả trả về dưới dạng OleVariant lại được framework chuyển ngược thành XLOPER/XLOPER12 để Excel nhận và hiển thị.

Như vậy, ngoài thời gian xử lý của hàm, luôn tồn tại chi phí của hai lần chuyển đổi dữ liệu.

Với các hàm đơn giản như cộng, trừ, nhân, chia hoặc chỉ có vài tham số thì chi phí này thường không đáng kể.

Tuy nhiên, khi viết các hàm xử lý dữ liệu thực tế thì câu chuyện khác hẳn. Ví dụ:

=MyFunc(A1:K100)

Framework phải chuyển cả vùng A1:K100 từ XLOPER/XLOPER12 sang cấu trúc trung gian (thường là Variant Array hoặc tương đương). Trong vùng dữ liệu này có thể chứa rất nhiều kiểu dữ liệu khác nhau do Excel quản lý:

• Số
• Chuỗi
• Date/Time
• Boolean
• Ô trống
• Giá trị lỗi (#N/A, #VALUE!, #DIV/0!, ...)

Sau khi hàm xử lý xong, kết quả lại phải chuyển ngược về XLOPER/XLOPER12 để trả cho Excel.

Vì vậy, theo mình, muốn đánh giá một XLL thì không nên chỉ nhìn vào các ví dụ như MySum, MyMultiply hay MyName. Những ví dụ đó chỉ chứng minh được cơ chế đăng ký và gọi UDF hoạt động.

Điều mình quan tâm hơn là framework xử lý như thế nào với các hàm nhận Range, mảng hai chiều, Reference, Error, Dynamic Array... vì đó mới là những trường hợp phản ánh chất lượng của lớp chuyển đổi dữ liệu (marshaller/type mapper) và hiệu năng thực tế của framework.

 

[nguyendang95]

Chính xác là như vậy ... Xong tôi Copy bài của Ông cho ChatGPT phân tích câu từ, thuật ngữ chính xác hơn dễ hiểu hơn

Vì tôi chỉ là tay ngang code két nên câu từ sẽ sai còn hiểu và làm thực tế lại khác ... dòm qua cái Class là cơ bản hình dung ra thôi

Nội dung sau do ChatGPT phân tích

Để tôi viết thế này cho ông dễ hình dung, giả sử đang áp dụng cho các phiên bản Excel từ 2007 trở đi.
Chuyển đổi tham số do Excel cung cấp từ XLOPER12 mang kiểu chuỗi, hàm tính toán và trả về kiểu chuỗi:

1. Chuyển đổi kiểu chuỗi đặc thù của Excel (kiểu chuỗi chứa thông tin độ dài chuỗi ở phần tử đầu tiên của mảng chuỗi) thành dạng wide string thông thường (wchar_t).
2. Chuyển đổi chuỗi ở bước 1 thành chuỗi BSTR để đưa vào VARIANT (OleVariant). Từ đây người dùng mới có thể tương tác được với các tham số mà Excel gửi đến.
3. Tính toán dựa trên các tham số đã chuyển đổi và cho ra kết quả.
4. Hàm tính toán đòi hỏi trả về kiểu chuỗi, cho nên chuyển kết quả thành dạng BSTR rồi đưa vào kiểu VARIANT.
5. Lấy chuỗi BSTR từ kiểu VARIANT ra, chuyển đổi thành dạng chuỗi mà Excel yêu cầu, giải phóng chuỗi BSTR ở trong VARIANT, sau đó đưa vào kiểu XLOPER12, bật bit cờ xlbitDLLFree rồi trả về cho Excel. Excel căn cứ vào cờ bit để gọi hàm gọi lại xlAutoFree12 và giải phóng bộ nhớ mà XLOPER12 chứa đựng.

Với tham số là mảng với cột và dòng số lượng lớn thì bước 1-5 sẽ lặp lại tương đương cột x dòng, hiệu năng tiêu tốn tương đối đáng kể.
P/s: Với cách triển khai thứ hai mà chủ thớt đề cập thì rất phức tạp so với cách 1, không rõ thư viện này có thể hỗ trợ người dùng đến mức nào để giúp người dùng đỡ "đau đầu".

 

[Nguyễn Duy Tuân]

Sử dụng thư viện BSXLLAddin: Tạo XLL Add-in với hàm MyVlookup Tim kiếm và trả về một hoặc mảng kết quả

XLL add-in này là một ví dụ rất quan trọng giúp các bạn tạo các hàm UDF cho Excel làm việc với mảng. Qua đây các bạn cũng hiểu hơn về cách lập trình mảng với kiểu dữ liệu OleVariant trong Delphi. Sẽ là kiến thức nền từ đó bạn tạo các hàm mảng khác với thư viện BSXLLAddin.
Đây là một ví dụ rất quan trọng giúp các bạn tạo các hàm UDF làm việc với mảng. Qua đây các bạn cũng hiểu hơn về cách lập trình mảng với kiểu dữ liệu OleVariant trong Delphi. Sẽ là kiến thức nền từ đó bạn tạo các hàm mảng khác.

VIDEO hướng dẫn

Mã nguồn đính kèm trong bài này cùng thư viện đã build XLL

unit Unit1;
//
//    This unit creates XLL add-ins, which are automatically
//    generated by Bluesofts XLL Add-ins.
//    It performs the following tasks:
//        + Installation
//        + Uninstallation
//        + Adding UDF functions
//    Author: Nguyen Duy Tuan - tuanktcdcn@yahoo.com - Tel: (+84)904210337
//
interface

uses Winapi.Windows, Winapi.ActiveX,
     BSXLLAPIs, Variants, StrUtils;

type
    TBSMyXLL = class(TBSXLLAddin)
    public
        procedure OnInstall; override;
        procedure OnUninstall; override;
        procedure OnAddFunctions(AFunctions: TBSFunctions); override;
    end;

implementation

//
//Delphi functions will be mapped to XLL UDF functions
//+ The function always returns OleVariant type
//+ Parameters are always declared with "const". The data type is always OleVariant
//

//DELPHI FUNCTION

function MyVlookup(const findValue, array2D, ColIndex: OleVariant): OleVariant; stdcall;
const
  errNA = -2146826246; // #N/A
var I, FirstCol, LastCol: Integer;
  vtmp: Variant;
  resCount: Integer;
  resInts: array of Integer;
begin
  VariantInit(Result); //Important!!!
  //
  //  Check the validity of the parameters
  //
  resCount := 0;
  for I := VarArrayLowBound(array2D, 1) to VarArrayHighBound(array2D, 1) do //each row in ...
  begin
    vtmp := array2D[I, 1];
    if not VarIsError(vtmp) then
    begin
      if not VarIsStr(vtmp) then
        vtmp := VarAsType(vtmp, varOleStr);
      if ContainsText(vtmp, findValue) then
      begin
        resCount := resCount + 1;
        SetLength(resInts, resCount);
        resInts[resCount - 1] := I; //save found row index
      end;
    end;
  end;
  //Get Result
  if resCount = 0 then //not found
  begin
    TVarData(Result).VType := varError;
    TVarData(Result).VError := errNA;
  end
  else
  if resCount = 1 then //Single value
    Result := array2D[resInts[resCount - 1], ColIndex]
  else
  begin
    //Result is 2D array
    //Create array:
    Result := VarArrayCreate([1, resCount, 1, 1], varVariant);
    resCount := 0;
    for I := Low(resInts) to High(resInts) do
    begin
      resCount := resCount + 1;
      Result[resCount, 1] := array2D[resInts[I], ColIndex];
    end;
  end;
end;

//INSTALL XLL UDF FUNCTION - MAPPING DELPHI FUNCTIONS
procedure TBSMyXLL.OnAddFunctions(AFunctions: TBSFunctions);
var
    fn: TBSFunction;
begin
    AFunctions.Category := 'Delphi XLL Functions';
  fn := AFunctions.Add('MyVlookup', @MyVlookup);
  fn.Function_help := 'The function returns an array of the found results.';
  //fn.FunctionTypes := [ftThreadSafe];
  //Add params
  fn.Params.Add('FindValue', 'This is the search value');
  fn.Params.Add('Array2D', 'It is a 2D array for searching and returning results');
  fn.Params.Add('ColumnIndex', 'Result column position');
end;

// TBSMyXLL - INSTALL XLL ADD-IN
procedure TBSMyXLL.OnInstall;
begin
    MessageBoxW(GetActiveWindow, 'Đăng ký Add-In XLL thành công!'#13#10 +
                                                                 'XLL tạo từ thư viện "BSXLLAddin" trên trang www.bluesofts.net',
                                                                 LPWSTR(AddinName),
                                                                 MB_OK + MB_ICONINFORMATION);
end;

procedure TBSMyXLL.OnUninstall;
begin
    MessageBoxW(GetActiveWindow, 'Đã gỡ bỏ Add-In XLL.',
                                                                 LPWSTR(AddinName),
                                                                 MB_OK + MB_ICONWARNING);
end;

initialization
    MyXLLAddin := TBSMyXLL.Create('My Excel XLL Add-in',
                                                                 'Author: Nguyễn Duy Tuân - https://bluesofts.net');

finalization

end.

Toàn bộ mã mnguồn dưới đây bạn sẽ thấy lập trình Delphi với thư viện BSXLLAddin với Cách 1 rất ngắn gọn.
Dù trình độ lập trình Delphi của bạn ở mức nào vẫn có thể tạo ra được XLL rất dễ dàng. Code ngắn gọn ! Tiện dụng! Không phải nghiên cứu sâu về kỹ thuật (việc của chuyên gia cao cấp). Dành 100% thời gian cho nghiệp vụ và hàm UDF của bạn mà thôi!

Toàn bộ mã nguồn và XLL đã build để các bạn dùng & test tại bài này.
Download BSXLLAddin & Examples Source

 

[Nguyễn Duy Tuân]

Bản chất XLL là một DLL chuẩn mà tôi hướng dẫn trong chủ đề này. Nên với các hàm mà tôi hướng dẫn dùng theo Cách 1 nếu bạn muốn xuất ra để dùng trong môi trường VBA, .NET, C/C++ thì chỉ cần thêm khai báo exports ở cuối Unit1 là được.

​

exports
MyName,
MySum,
MyAny,
MyFunc,
MyVlookup;

Như vậy khi lập trình theo Cách 1 chúng ta làm một công được hai việc. Vừa dùng cho hàm UDF trên bảng tính Excel, vừa dùng chúng trong môi trường lập trình VBA nói riêng và các môi trường lập trình khác.