Macros to help write Excel User defined functions easily in Rust
Fixed crash bug async function support and added support for single threaded functions
Populate f64::NAN when range has #NA or #DIV/0. Might break code that assumes 0.0
xladd would return a null value if a xlTypeMulti was returned from a range cell.
Some minor bug fixes in xladd integrated in
Update to use ndarray 0.14 Support for sref which allows use of index and offset ranges in excel Some minor bug fixes
enable_trace_logging that outputs to a file that is called on demand in your testing spreadsheetyour exports are renamed toprojectmyfunction`. If it's not specified it defaults to "xl".I find quickly knocking up a sample GUI in Excel by drag/dropping extremely good for prototyping. Writing a test function and being able to pass all sorts of data to it interactively is a useful feature
I found xladd from MarcusRainbow which enabled a raw api to create user defined functions for Excel. I wasn't totally happy with it and raised a couple of PRs against his project but didn't get any responses so I forked his project and contined to add functionality as I saw fit.
But it was still a pain and I wanted to learn about proc-macros so created this proc-macro crate to make it easier to write functions in Rust.
Add
[lib]
crate-type = ["cdylib"]
[dependencies]
xladd-derive= {"^0.4" }
xladd = {git="https://github.com/ronniec95/xladd" , features=["use_ndarray"] } # Needed to patch the old abandoned crateto your Cargo.toml
Write a Rust function and add the following annotation #[xl_func()] like
// These imports are needed from xladd
use xladd::registrator::Reg;
use xladd::variant::Variant;
use xladd::xlcall::LPXLOPER12;
use xladd_derive::xl_func;
use log::*; // Needed from 0.4.* onwards to give tracing
#[xl_func()]
fn add(arg1: f64, arg2: f64) -> Result<f64, Box<dyn std::error::Error>> {
// No comments in this method, defaults will be allocated
Ok(arg1 + arg2)
}
/// This function adds any number of values together
/// * v - array of f64
/// * ret - returns the sum0
#[xl_func()]
fn add_array(v: &[f64]) -> Result<f64, Box<dyn std::error::Error>> {
// Comments will be filled into the Excel function dialog box
Ok(v.iter().sum())
}
/// This function adds any number of values together
/// * v - array of f64
/// * ret - returns the sum
#[xl_func(category="MyCategory")] // Custom attribute to assign function to a particular category
fn add_array_v2(v: &[f64]) -> Result<(Vec<f64>, usize), Box<dyn std::error::Error>> {
// Comments will be filled into the Excel function dialog box
// This returns a 2d array to excel using a (vec,usize) tuple. Note that if v.len() / columns != 0 you will have missing values
Ok((v.to_vec(), 2))
}
use ndarray::Array2;
/// 2d arrays can now be accepted and returned opening up a lot more possibilities for interfacing with Excel
#[xl_func(category = "OptionPricing", prefix = "my", rename = "baz")]
fn add_f64_2(a: Array2<f64>) -> Result<Array2<f64>, Box<dyn std::error::Error>> {
Ok(Array2::from_elem([2, 2], 0.0f64))
}Right now there are a couple of restrictions which I hope to remove down the line
The return type of your function can be a Result<type,Box<dyn std::error::Error>> of any basic type:
or a tuple of
where the second parameter is the number of columns. This allows Excel to handle arrays of 2d data. The macro will calculate the rows from the size of the array.
I was thinking of making the input &[] arrays also be a tuple if there is demand for it.
Arguments are taken as LPXLOPER12 args which are then coerced to the Rust types. Errors in coercion are reported via a trace!() log. If you run Excel from the command line with env-logger or simplelog you could output these to a file for debugging.
The doc comments are interpreted in the following manner
/// This is a normal multiline comment that
/// will be used as a description of the function
/// * arg1 - This argument must be formatted with a `* <name> -` and will be used in the argument description
/// * ret - This is a special return type argument which will appended to the description of the functionExcel uses however many cores there are on the machine, but it relies on your UDFs being thread safe. Rust is multithread friendly, but watch out if you are reading/writing files.
If you want to control this aspect through an attribute, let me know.
Excel calls this function in your .dll when it starts. The macro generates the register_* functions for you so follow this template. If someone knows how to automatically determine these from a proc-macro, please get in touch, or raise a PR
// For excel to register this XLL ensure `no_mangle` is specified
#[no_mangle]
pub extern "stdcall" fn xlAutoOpen() -> i32 {
let reg = Reg::new();
register_add(®);
1 // Must return 1 to signal to excel SUCCESS
}As I cannot seem to be able to get in touch with MarcusRainbow, the original author of the xladd crate, I've created a fork of that, so in Cargo.toml you need to add a github dependency xladd = { git ="https://github.com/ronniec95/xladd"}. Let me know if that is a problem and I can see if there's a better way
As the xladd package calls into the Windows C api it's inherently unsafe.
This package uses the LPXLOPER12 api for excel so is compatible with 2007 onwards.
If the add-in is not working, check that your excel is the samme bit size (32 or 64bit) as your rustc compiler. Often Excel is installed as 32bit in a lot of organisations and your rustc compiler is probably 64bit. This will natually not work.
Asynchronous methods. I've not had the need for this especially as network and IO type work is much better done within Excel itself.
I also would like to add RTD support so you can subscribe to live data.
Within VSCode you can create a configuration for debugging and change the program to
"program": "C:/Program Files/Microsoft Office/root/Office16/EXCEL.EXE",This will launch excel but you can set breakpoints in your code.