Tạo, sử dụng Automation Add-in trong Excel bằng Visual C#

[nguyendang95]

Automation Add-in là một trong nhiều phương pháp bổ sung tính năng mới cho Excel. Thông qua nó, người dùng có thể bổ sung thêm công thức tính toán trên bảng tính, triển khai hàm RTD của Excel lấy dữ liệu theo thời gian thực hay (tùy chọn) triển khai thêm giao diện (interface) IDTExtensibility2 để tùy biến Excel sâu hơn. Phạm vi bài viết này chỉ đề cập đến chức năng chính mà Automation Add-in hướng đến: Tạo hàm do người dùng định nghĩa (user-defined functions).

Để tạo Automation Add-in, người dùng cài đặt Visual Studio (khuyến nghị nên cài đặt phiên bản mới nhất là Visual Studio 2022 Community Edition miễn phí), sau đó tạo một dự án (project) mới là Class Library (.NET Framework) như hình dưới.

Để đăng ký với hệ thống, người dùng cần tạo hai phương thức dưới đây, lần lượt gắn thuộc tính (attribute) là [ComRegisterFunction] và [ComUnregisterFunction]. Đây chính là cơ sở để Excel có thể xác định danh tính của Automation Add-in mà người dùng muốn sử dụng trong Excel.

using Microsoft.Win32;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;

namespace ExcelUserDefinedFunctions
{
    [Guid("6353A1BB-3DBD-4B0D-A55B-19C0490E4911")]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    [ComVisible(true)]
    public class Class1
    {
        public Class1() { }
        [ComRegisterFunction]
        public static void RegisterFunction(Type type)
        {
            Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, "Programmable"));
            RegistryKey Key = Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, "InprocServer32"), true);
            Key.SetValue("", Environment.SystemDirectory + @"\mscoree.dll", RegistryValueKind.String);
        }
        [ComUnregisterFunction]
        public static void UnregisterFunction(Type type)
        {
            Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type, "Programmable"), false);
        }
        private static string GetSubKeyName(Type type, string SubKeyName)
        {
            StringBuilder Sb = new StringBuilder();
            Sb.Append(@"CLSID\{");
            Sb.Append(type.GUID.ToString().ToUpper());
            Sb.Append(@"}\");
            Sb.Append(SubKeyName);
            return Sb.ToString();
        }
    }
}

Sau khi đăng ký với tiện ích Regasm, thông tin của add-in sẽ được ghi nhận trên Registry Editor.

Chúng ta hãy thử viết hai hàm đơn giản dưới đây, hàm Hello lấy một đối số kiểu chuỗi và trả về giá trị "Hello " + Name, và hàm còn lại SumTwoNumbers trả về kết quả là phép cộng hai số.

public string Hello(string Name)
{
    return $"Hello {Name}";
}
public double SumTwoNumbers(double A, double B)
{
    return A + B;
}

Sau khi viết xong, người dùng cần chọn Project - <tên_dự_án> Properties. Trong tab Build, chọn Register for COM interop, sau đó lưu lại và tiến hành Build ra DLL. Lưu ý: Cần phải chạy Visual Studio với quyền quản trị viên (Run as Administrator), ngoài ra cần Build với Platform cụ thể (vd: Excel bản 32 bit thì chọn x86 và Excel bản 64 bit thì chọn x64).

Để sử dụng Automation Add-in mà người dùng vừa viết, từ Excel người dùng mở tab Developer - Excel Add-ins. Mở ra hộp thoại Add-ins, người dùng chọn tiếp Automation và tìm tên của add-in, sau đó nhấn OK để đóng hộp thoại lại.

Sử dụng công thức trong sheet:

Nhược điểm của Automation add-in so với xll add-in truyền thống:

• Không hỗ trợ gợi ý hàm, đối số của hàm.
• Hiệu năng kém hơn.

(Bổ sung) Bên cạnh việc sử dụng Automation add-in để tạo công thức tính toán cho sheet, người dùng còn có thể thiết lập và sử dụng hàm RTD thông qua add-in này nhằm cập nhật dữ liệu lên sheet theo thời gian thực.
Để thiết lập hàm RTD, người dùng cần triển khai giao diện (interface) IRtdServer của Excel. Chi tiết cách thực hiện người dùng có thể tham khảo bài viết dưới đây của Microsoft.
How to set up and use the RTD function in Excel
How to create a RealTimeData server for Excel
IRtdServer Interface
Hoặc, người dùng có thể tra cứu và tìm một vài code mẫu trên internet ở diễn đàn nổi tiếng như Stack Overflow.
Code đính kèm theo bài viết này tạo một automation server cho phép Excel cập nhật dữ liệu theo thời gian thực về bảng giá mã chứng khoán, nguồn dữ liệu lấy từ trang SSI, cứ năm giây cập nhật một lần.

Video thực tế:

 

[Mr.hieudoanxd]

Tuyệt vời. Có khi em chuyển từ VBNet sang C# luôn.