feature request: Public IntoFilterData / new_string_and_criteria / new_number_and_criteria or alternative API

[maxmezzomo]

Feature Request

Hi, it seems currently there is no straightforward way, at least that I have come across, to allow implementing IntoFilterData on custom types. I understand the IntoFilterData behavior is perhaps not complete so the new_filter_data fn is private. In my case I have an enum representing many different data types and writing match statements can be get verbose quickly if we have to write

FilterCondition::new().add_custom_filter(<FilterCriteria>, <value>)..

in each arms of the match statement. Would like to hear any thoughts regarding this and whether the trait functions could either be made public or whether we could find some way to streamline this.

Thanks

[jmcnamara]

Could you show an example of how you are currently having to handle it, with the match statement etc.

[maxmezzomo]

Yes for sure, this is my current use case

 if let Some(ci) = ci {
            let filter_condition = FilterCondition::new();
            let filter_condition_with_filter = match &filter.value {
                SqlValue::I32(value) => {
                    filter_condition
                        .add_custom_filter(filter.operator.clone().into(), value.clone())
                    //value:i32
                }
                SqlValue::String(value) => {
                    filter_condition
                        .add_custom_filter(filter.operator.clone().into(), value.as_str())
                    //value:String
                }
                SqlValue::Date(value) => {
                    filter_condition.add_custom_filter(
                        filter.operator.clone().into(),
                        value.to_string().as_str(),
                        //value:NaiveDate
                    )
                }
                SqlValue::DateTime(value) => {
                    filter_condition.add_custom_filter(
                        filter.operator.clone().into(),
                        value.to_string().as_str(),
                        //value: NaiveDateTime
                    )
                }
                SqlValue::Numeric(value) => {
                    filter_condition
                        .add_custom_filter(filter.operator.clone().into(), value.to_f64().unwrap())
                    //value:BigDecimal
                }
                SqlValue::Bool(value) => {
                    filter_condition.add_custom_filter(
                        filter.operator.clone().into(),
                        value.to_string().as_str(),
                        //value:bool
                    )
                }
            };

            worksheet.filter_column(ci as u16, &filter_condition_with_filter)?;
        }

For some context:

• ci is Option and represents column index if any
• the filter var is a struct I use to represent filter operations for sql queries etc and display and contains operator and value fields.
• filter.value is SqlValue enum instance, and this is where I might like to try impl some trait for filter data.

Similarly, to go from my filter operator format to xlsxwriter I have

impl From<FilterOperator> for FilterCriteria {
    fn from(value: FilterOperator) -> Self {
        match value {
            FilterOperator::Gt => FilterCriteria::GreaterThan,
            FilterOperator::Gte => FilterCriteria::GreaterThanOrEqualTo,
            FilterOperator::Lt => FilterCriteria::LessThan,
            FilterOperator::Lte => FilterCriteria::LessThanOrEqualTo,
            FilterOperator::Like => FilterCriteria::Contains,
            _ => todo!(),
        }
    }
}

So am thinking maybe something similar could be done for values and FilterData. Thanks

[jmcnamara]

Thanks for the example. That seems reasonable.

I've made the required helper functions public on main. You should now have the tools to implement IntoFilterData for your type. Here is an example with a new type:

use rust_xlsxwriter::{
    FilterCondition, FilterCriteria, FilterData, Format, IntoFilterData, Workbook, Worksheet,
    XlsxError,
};

struct MyType {
    value: f64,
}

impl IntoFilterData for MyType {
    fn new_filter_data(&self, criteria: FilterCriteria) -> FilterData {
        FilterData::new_number_and_criteria(self.value, criteria)
    }
}

fn main() -> Result<(), XlsxError> {
    // Create a new Excel file object.
    let mut workbook = Workbook::new();

    // Add a worksheet  with some sample data to filter.
    let worksheet = workbook.add_worksheet();
    populate_autofilter_data(worksheet, false)?;

    // Set the autofilter area for numbers greater than 8000.
    worksheet.autofilter(0, 0, 50, 3)?;

    let my_instance = MyType { value: 8000.0 };

    // Set a custom number filter.
    let filter_condition =
        FilterCondition::new().add_custom_filter(FilterCriteria::GreaterThan, my_instance);
    worksheet.filter_column(2, &filter_condition)?;

    // Save the file to disk.
    workbook.save("gh116.xlsx")?;

    Ok(())
}

// Generate worksheet data to filter on.
pub fn populate_autofilter_data(
    worksheet: &mut Worksheet,
    add_blanks: bool,
) -> Result<(), XlsxError> {
    // The sample data to add to the worksheet.
    let mut data = vec![
        ("East", "Apple", 9000, "July"),
        ("East", "Apple", 5000, "April"),
        ("South", "Orange", 9000, "September"),
        ("North", "Apple", 2000, "November"),
        ("West", "Apple", 9000, "November"),
        ("South", "Pear", 7000, "October"),
        ("North", "Pear", 9000, "August"),
        ("West", "Orange", 1000, "December"),
        ("West", "Grape", 1000, "November"),
        ("South", "Pear", 10000, "April"),
        ("West", "Grape", 6000, "January"),
        ("South", "Orange", 3000, "May"),
        ("North", "Apple", 3000, "December"),
        ("South", "Apple", 7000, "February"),
        ("West", "Grape", 1000, "December"),
        ("East", "Grape", 8000, "February"),
        ("South", "Grape", 10000, "June"),
        ("West", "Pear", 7000, "December"),
        ("South", "Apple", 2000, "October"),
        ("East", "Grape", 7000, "December"),
        ("North", "Grape", 6000, "July"),
        ("East", "Pear", 8000, "February"),
        ("North", "Apple", 7000, "August"),
        ("North", "Orange", 7000, "July"),
        ("North", "Apple", 6000, "June"),
        ("South", "Grape", 8000, "September"),
        ("West", "Apple", 3000, "October"),
        ("South", "Orange", 10000, "November"),
        ("West", "Grape", 4000, "December"),
        ("North", "Orange", 5000, "August"),
        ("East", "Orange", 1000, "November"),
        ("East", "Orange", 4000, "October"),
        ("North", "Grape", 5000, "August"),
        ("East", "Apple", 1000, "July"),
        ("South", "Apple", 10000, "March"),
        ("East", "Grape", 7000, "October"),
        ("West", "Grape", 1000, "September"),
        ("East", "Grape", 10000, "October"),
        ("South", "Orange", 8000, "March"),
        ("North", "Apple", 4000, "July"),
        ("South", "Orange", 5000, "July"),
        ("West", "Apple", 4000, "June"),
        ("East", "Apple", 5000, "April"),
        ("North", "Pear", 3000, "August"),
        ("East", "Grape", 9000, "November"),
        ("North", "Orange", 8000, "October"),
        ("East", "Apple", 10000, "June"),
        ("South", "Pear", 1000, "December"),
        ("North", "Grape", 10000, "July"),
        ("East", "Grape", 6000, "February"),
    ];

    // Introduce blanks cells for some of the examples.
    if add_blanks {
        data[5].0 = "";
        data[18].0 = "";
        data[30].0 = "";
        data[40].0 = "";
    }

    // Widen the columns for clarity.
    worksheet.set_column_width(0, 12)?;
    worksheet.set_column_width(1, 12)?;
    worksheet.set_column_width(2, 12)?;
    worksheet.set_column_width(3, 12)?;

    // Write the header titles.
    let header_format = Format::new().set_bold();
    worksheet.write_string_with_format(0, 0, "Region", &header_format)?;
    worksheet.write_string_with_format(0, 1, "Item", &header_format)?;
    worksheet.write_string_with_format(0, 2, "Volume", &header_format)?;
    worksheet.write_string_with_format(0, 3, "Month", &header_format)?;

    // Write the other worksheet data.
    for (row, data) in data.iter().enumerate() {
        let row = 1 + row as u32;
        worksheet.write_string(row, 0, data.0)?;
        worksheet.write_string(row, 1, data.1)?;
        worksheet.write_number(row, 2, data.2)?;
        worksheet.write_string(row, 3, data.3)?;
    }

    Ok(())
}

Output:

You can test it by adding the GitHub repo to your Cargo.toml file:

cargo add --git https://github.com/jmcnamara/rust_xlsxwriter.git rust_xlsxwriter

Let me know how you get on and I'll roll it up into a release.

[maxmezzomo]

Fantastic, this look great, I'll give it a go and let you know how it goes. Thanks!

[maxmezzomo]

Hi, I tried it out and seems to work well, thanks for the quick update!

[jmcnamara]

Added in rust_xlsxwriter v0.79.3.