Skip to content
mwmiPublic

About

πŸš€ Modern C++20 framework for developing Excel/WPS XLL add-ins with UDF and RTD support

Topics

Resources

Stars

3 stars

Watchers

1 watching

Forks

Latest commit

Β 

History

5 Commits

Folders and files

NameName
Last commit message
Last commit date
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 

Repository files navigation

πŸš€ ExcelXLL - Modern Excel Add-in Development Framework

πŸ€– AI Generated Documentation πŸ€–

✨ This documentation is generated by artificial intelligence ✨


C++20 License Platform Excel WPS GitHub Stars GitHub Forks

πŸ“ˆ ExcelXLL is a powerful Excel/WPS XLL add-in development framework built with modern C++20 standards. This project aims to simplify the development process of Excel custom functions, providing C++ developers with efficient, easy-to-use, and memory-safe add-in development solutions.

✨ Core Features

πŸ’» Development Convenience

  • ⚑ Modern C++20 Standard: Leverages modern C++ features to enhance development efficiency
  • 🎯 Concise Macro Definitions: Significantly simplifies function definitions through UDF and RTD macros
  • πŸ”„ Intelligent Type System: Automatically handles data type conversion between Excel and C++
  • πŸ“š Rich Example Code: Provides complete function examples and best practices

πŸ›‘οΈ Memory Safety

  • πŸ”’ RAII Pattern: Automatically manages memory resources to prevent memory leaks
  • 🧠 Smart Pointer Management: Uses std::unique_ptr to manage dynamic memory
  • ⚠️ Exception Safety Guarantee: Provides strong exception safety guarantees

πŸš€ High Performance

  • ⚑ Zero-Copy Design: Supports move semantics for efficient large data processing
  • πŸ“¦ Static Linking Optimization: Reduces dependencies and file size in Release mode
  • πŸ”§ Compile-Time Optimization: Template metaprogramming and compile-time computation

🌐 Wide Compatibility

  • πŸ”¨ Dual Compiler Support: MinGW-w64 and Microsoft Visual C++
  • πŸ’Ύ Multi-Architecture Support: 32-bit WPS and 64-bit Excel
  • πŸ“… Multi-Version Compatibility: Excel 2016+ and WPS Office

πŸ”„ Real-Time Data Support

  • πŸ“‘ RTD Functions: Supports real-time data updates
  • ⚑ Asynchronous Processing: Non-blocking data acquisition and updates
  • πŸ”— COM Components: Complete RTD server implementation

πŸ“ Project Structure

ExcelXLL/
β”œβ”€β”€ include/                 # Header files directory
β”‚   β”œβ”€β”€ XLCALL.H            # Excel C API interface
β”‚   β”œβ”€β”€ xllType.h           # Core data type wrapper
β”‚   β”œβ”€β”€ xllUDF.h            # UDF function management
β”‚   β”œβ”€β”€ xllManager.h        # XLL manager
β”‚   β”œβ”€β”€ xllRTD.h            # RTD function management
β”‚   β”œβ”€β”€ xllTools.h          # Utility function library
β”‚   β”œβ”€β”€ xllMacros.h         # Macro definitions
β”‚   β”œβ”€β”€ RtdServer.h         # RTD server
β”‚   β”œβ”€β”€ RTDTopic.h          # RTD topic management
β”‚   β”œβ”€β”€ IRTDServer.h        # RTD server interface
β”‚   └── dll.h               # DLL export definitions
β”œβ”€β”€ src/                    # Source files directory
β”‚   β”œβ”€β”€ XLCALL.CPP          # Excel API implementation
β”‚   β”œβ”€β”€ xllType.cpp         # Data type implementation
β”‚   β”œβ”€β”€ xllUDF.cpp          # UDF framework implementation
β”‚   β”œβ”€β”€ xllManager.cpp      # Manager implementation
β”‚   β”œβ”€β”€ xllRTD.cpp          # RTD implementation
β”‚   β”œβ”€β”€ xllTools.cpp        # Utility function implementation
β”‚   β”œβ”€β”€ RtdServer.cpp       # RTD server implementation
β”‚   β”œβ”€β”€ RTDTopic.cpp        # RTD topic implementation
β”‚   └── dll.cpp             # DLL entry implementation
β”œβ”€β”€ lib/                    # Library files directory
β”‚   β”œβ”€β”€ XLCALL32.LIB        # 32-bit Excel library
β”‚   └── x64/
β”‚       └── XLCALL32.LIB    # 64-bit Excel library
β”œβ”€β”€ functions.cpp           # Example function implementation
β”œβ”€β”€ dll.def                 # DLL export definitions
β”œβ”€β”€ CMakeLists.txt          # CMake build configuration
└── LICENSE                 # MIT license

πŸš€ Quick Start

πŸ“‹ System Requirements

  • πŸ’Ώ Operating System: Windows 10/11
  • πŸ”¨ Compiler:
    • 🟒 MinGW-w64 GCC 11+ (Recommended)
    • πŸ”΅ Microsoft Visual C++ 2019+
  • βš™οΈ Build Tools: CMake 3.20+, Ninja
  • 🎯 Target Platform:
    • πŸ“Š Excel 2016+ (64-bit)
    • πŸ“ WPS Office (32-bit)

βš™οΈ Environment Setup

🟒 Option 1: MinGW-w64 (Recommended)

  1. Install MSYS2
# Update package manager
pacman -Syu

# Install toolchain
pacman -S mingw-w64-i686-toolchain mingw-w64-x86_64-toolchain
pacman -S mingw-w64-x86_64-cmake mingw-w64-x86_64-ninja
  1. Configure Environment Variables
C:\msys64\mingw64\bin
C:\msys64\mingw32\bin
C:\msys64\usr\bin

πŸ”΅ Option 2: Microsoft Visual C++

Install Visual Studio 2019/2022, ensuring the following components are included:

  • C++ core features
  • MSVC v143 compiler toolset
  • Windows 10/11 SDK
  • CMake tools

πŸ”¨ Building the Project

MinGW-w64 Build

# 32-bit build (for WPS)
cmake -B build32 -G Ninja -DCMAKE_CXX_STANDARD=20 -DCMAKE_SIZEOF_VOID_P=4
cmake --build build32

# 64-bit build (for Excel)
cmake -B build64 -G Ninja -DCMAKE_CXX_STANDARD=20 -DCMAKE_SIZEOF_VOID_P=8
cmake --build build64

MSVC Build

# Initialize MSVC environment
call "C:\Program Files\Microsoft Visual Studio\2022\Community\VC\Auxiliary\Build\vcvars64.bat"

# 32-bit build
cmake -B build32 -G Ninja -DCMAKE_C_COMPILER=cl -DCMAKE_CXX_COMPILER=cl -DCMAKE_CXX_STANDARD=20 -DCMAKE_SIZEOF_VOID_P=4
cmake --build build32

# 64-bit build
cmake -B build64 -G Ninja -DCMAKE_C_COMPILER=cl -DCMAKE_CXX_COMPILER=cl -DCMAKE_CXX_STANDARD=20 -DCMAKE_SIZEOF_VOID_P=8
cmake --build build64

πŸ“₯ Installation and Usage

  1. Copy Plugin Files:

    • Excel: Copy build64/EXCELXLL_x64.xll to target location
    • WPS: Copy build32/EXCELXLL_x86.xll to target location
  2. Load Plugin:

    • Open Excel/WPS
    • File β†’ Options β†’ Add-ins β†’ Manage Excel Add-ins β†’ Browse
    • Select the corresponding .xll file
  3. Using Functions:

    =HelloWorld()           # Display "Hello World"
    =Add(10, 20)           # Calculate sum of two numbers
    =MySum(A1:A10)         # Sum a range
    =RTDClock()            # Display real-time clock
    

πŸ“– Development Guide

🎯 Creating UDF Functions

UDF (User Defined Function) is used to create Excel custom functions:

#include "xllManager.h"

// Simple function example
UDF(HelloWorld, L"Greeting function") {
    xllType result;
    result = L"Hello World";
    return result.get_return();
}

// Function with parameters
UDF(Add, L"Add two numbers", Param a, Param b) {
    xllType result;
    xllType a_ = a;
    xllType b_ = b;
    
    if (a_.is_num() && b_.is_num()) {
        result = a_.get_num() + b_.get_num();
    } else {
        result = L"Type error!";
    }
    return result.get_return();
}

// Complex configuration function
UDF(Concat2, 
    ({udf::help, L"Concatenate two strings"},
     {udf::category, L"Text Functions"},
     {udf::arguments, L"Text1,Text2"}),
    Param a, Param b) {
    xllType result;
    xllType a_ = a;
    xllType b_ = b;
    std::wstring str = a_.get_str() + b_.get_str();
    result = str;
    return result.get_return();
}

// Array processing function
UDF(MySum, L"Array summation", Param a) {
    xllType result;
    xllType a_ = a;
    double sum = 0;
    
    if (a_.is_array()) {
        // Use iterator to traverse
        for (auto item : a_) {
            if (item->is_num()) {
                sum += item->get_num();
            }
        }
    }
    result = sum;
    return result.get_return();
}

// Function returning array
UDF(RetArray, L"Return test array") {
    xllType result;
    xllType a = L"Text";
    xllType b = 20;
    xllType c = 30;
    xllType d = 40;
    
    // Create 2D array
    result = {{a, b}, {c, d}};
    return result.get_return();
}

πŸ“‘ Creating RTD Functions

RTD (Real-Time Data) is used to create real-time data functions:

// Simple RTD function
RTD(RTDHelloWorld, L"RTD greeting function", 
    ([](xllptrlist args, Topic* topic) {
        topic->setValue(L"Hello World");
        return 0;
    }, L"Loading...")) {
    
    xllType ret;
    CALLRTD(ret);
    return ret.get_return();
}

// Asynchronous RTD clock
RTD(RTDClock, L"Real-time clock", 
    ([](xllptrlist args, Topic* topic) {
        SYSTEMTIME st;
        while (true) {
            GetLocalTime(&st);
            wchar_t buffer[100];
            swprintf(buffer, 100, L"πŸ•’ %04d-%02d-%02d %02d:%02d:%02d", 
                    st.wYear, st.wMonth, st.wDay, 
                    st.wHour, st.wMinute, st.wSecond);
            topic->setValue(buffer);
            Sleep(1000);
        }
        return 0;
    }, L"Clock starting...", true)) {
    
    xllType ret;
    CALLRTD(ret);
    return ret.get_return();
}

// RTD function with parameters
RTD(RTDParam, L"Parameter test", 
    ([](xllptrlist args, Topic* topic) {
        xllType p;
        if (args.size() > 0) {
            p = *args[0].get();
        }
        topic->setValue(p);
        return 0;
    }, L"Processing parameters..."), Param a) {
    
    xllType ret;
    CALLRTD(ret, a);
    return ret.get_return();
}

βš™οΈ Global Configuration

// Plugin configuration
SET() {
    // Set plugin name
    xll::xllName = L"My Excel Plugin";
    
    // Set default function category
    xll::defaultCategory = L"Custom Functions";
    
    // Set whether to enable RTD (enabled by default)
    xll::enableRTD = true;
    
    // Plugin load callback
    xll::open = []() {
        // Configure specific functions
        UDFCONFIG(HelloWorld)->set_funchelp(L"Display greeting message");
        
        // Show welcome message
        xll::alert(L"Plugin loaded successfully!");
        return 1;
    };
    
    // Plugin unload callback
    xll::close = []() {
        return 1;
    };
    
    return 0;
}

πŸ”„ Data Type Processing

The xllType class provides powerful data type encapsulation:

// Type checking
if (param.is_num()) { /* Numeric type */ }
if (param.is_str()) { /* String type */ }
if (param.is_array()) { /* Array type */ }

// Data retrieval
double number = param.get_num();
std::wstring text = param.get_str();

// Array operations
for (auto item : param) {
    if (item->is_num()) {
        double val = item->get_num();
    }
}

// Index access
auto item = param[0];      // Linear index
auto item = param.at(0, 1); // 2D index

// Data setting
xllType result;
result = 3.14;           // Numeric
result = L"Text";        // String
result = {1, 2, 3};      // 1D array
result = {{1, 2}, {3, 4}}; // 2D array

πŸ”§ Advanced Features

πŸ“ž Calling Excel Built-in Functions

UDF(CallExcelFunc, L"Excel function call example", Param range) {
    xllType result;
    xllType range_ = range;
    
    // Call Excel's SUM function
    xll::callExcelFunction(result, xlfSum, range_);
    
    // Call Excel's ABS function
    // xll::callExcelFunction(result, xlfAbs, -100);
    
    return result.get_return();
}

⚠️ Error Handling Mechanism

UDF(SafeDiv, L"Safe division", Param a, Param b) {
    xllType result;
    xllType a_ = a;
    xllType b_ = b;
    
    if (!a_.is_num() || !b_.is_num()) {
        result.set_err(xlerrValue);  // #VALUE! error
        return result.get_return();
    }
    
    double divisor = b_.get_num();
    if (divisor == 0) {
        result.set_err(xlerrDiv0);   // #DIV/0! error
        return result.get_return();
    }
    
    result = a_.get_num() / divisor;
    return result.get_return();
}

⚑ Performance Optimization Tips

  1. Avoid unnecessary copying:
// Good practice
UDF(ProcessArray, L"Process array", Param arr) {
    xllType result;
    xllType& arr_ref = arr;  // Reference, avoid copying
    // ... processing logic
    return result.get_return();
}
  1. Use move semantics:
xllType createLargeArray() {
    xllType arr;
    // ... create large array
    return arr;  // Automatically use move semantics
}
  1. Pre-allocate memory:
UDF(CreateArray, L"Create array", Param size) {
    xllType result;
    int n = static_cast<int>(xllType(size).get_num());
    
    result.reserve(n);  // Pre-allocate memory
    for (int i = 0; i < n; ++i) {
        result.push_back(i);
    }
    
    return result.get_return();
}

πŸ› Troubleshooting Guide

❓ Common Issues

  1. Compilation error: XLCALL32.LIB not found

    • Ensure the lib/ directory contains the correct library files
    • Check link_directories in CMake configuration
  2. Excel cannot load XLL file

    • Check architecture matching (32-bit vs 64-bit)
    • Ensure all dependency libraries are correctly linked
    • Check Windows security settings
  3. Function returns #VALUE! error

    • Check if parameter types are correct
    • Ensure proper use of get_return() method
    • Check if memory management is correct
  4. RTD functions do not update

    • Ensure RTD server is correctly registered
    • Check if asynchronous functions are running normally
    • Verify COM component registration

πŸ” Debugging Tips

  1. Use breakpoint debugging:
UDF(DebugFunc, L"Debug function", Param input) {
    xllType result;
    
    // Set breakpoint here
    xllType input_ = input;
    
    if (input_.is_num()) {
        result = input_.get_num() * 2;
    }
    
    return result.get_return();
}
  1. Output debug information:
UDF(LogFunc, L"Log function", Param input) {
    xllType result;
    
    // Use Excel alert box to display debug information
    std::wstring debug_info = L"Input type: " + 
        (xllType(input).is_num() ? L"Numeric" : L"Non-numeric");
    xll::alert(debug_info.c_str());
    
    result = L"Debug completed";
    return result.get_return();
}

πŸ“Š Performance Analysis

  1. Use Release mode build for optimal performance
  2. Avoid frequent string operations
  3. Use RTD update frequency reasonably
  4. Consider using thread pools for complex computations

🀝 Contributing Guidelines

We welcome any form of contribution! Please follow these steps:

  1. Fork the project
  2. Create feature branch (git checkout -b feature/AmazingFeature)
  3. Commit changes (git commit -m 'Add some AmazingFeature')
  4. Push to branch (git push origin feature/AmazingFeature)
  5. Open Pull Request

πŸ“ Code Standards

  • Use C++20 standard
  • Follow existing code style
  • Add appropriate comments and documentation
  • Write unit tests
  • Ensure all tests pass

πŸ“„ License

This project is licensed under the MIT License - see the LICENSE file for details.

πŸ™ Special Thanks

  • Microsoft Excel SDK Team
  • MinGW-w64 Project
  • CMake Project
  • All contributors and users

πŸ“ž Contact Information


🌟 If this project helps you, please give us a Star!

GitHub stars GitHub forks GitHub watchers

πŸ“š For more documentation and examples, please check πŸ“– Wiki

πŸš€ Quick Start | πŸ“– Detailed Documentation | πŸ› Issue Feedback | πŸ’‘ Feature Suggestions

Get Started β€’ View Documentation β€’ Submit Issues β€’ Feature Requests

Thank you for the support from all contributors! πŸŽ‰

About

πŸš€ Modern C++20 framework for developing Excel/WPS XLL add-ins with UDF and RTD support

Topics

Resources

Stars

3 stars

Watchers

1 watching

Forks

Contributors

Languages