Support for data validations [feature-request]

[cocowalla]

Hi, first let me say thanks for ExcelHelper - I really like how simle the API is, and I was able to get started writing an Excel file in literally less than 2 minutes!

This is a feature request for the ability to add data validations when writing Excel files (this is supported by the underlying ClosedXML library).

My particular use case is restricting the length of text values, but of course ideally you could add any data validation.

[kendallb]

There is already support for writing your own custom type converters you can add via fluent syntax, and we use that all the time. So if it is something you reuse a lot, you could add something that way.

Adding support for fluent style validations the way ClosedXML does it is a nice idea. I will take a look at how hard it would be to do that. We personally use the C1 version of the library (not public on nuget) since it’s so much faster and uses way less memory than CloseXML (or more correctly the XML document library it uses), so if we added it we would need to make it compatible with both versions.

[cocowalla]

Ah right, I saw some projects in the repo with "C1", but had no idea what that meant, now I know!

I don't mind if data validation support uses fluent config or not, it's the function that's important 😄

I'm not sure if there are any docs (I just used the tests to see examples), but it'd be great if you had some pointers for how to use custom type converters?

[kendallb]

Yeah, C1.Excel is a commercial library from Component One (now Grape City). I originally wrote this library using C1 and later wanted to move it to the Open Source tools but found while they worked well, it's was quite a bit slower (2x!) and uses about 5x the memory (see the MemoryTest tool in GitHub). One day I hope to spend some time in the Open Source tools to try to figure that out, or find another Open Source Excel library to layer on that will be faster and use less memory. So for now, we use C1.Excel as it has some features we cannot yet properly support in the Open Source version (Date's for instance don't work right in ClosedXML for some things we need, like turning them into strings).

Anyway, yeah I do need to document this but it's super close to CsvHelper so the docs on that can help. For a sample of a custom type converter, here is a class we use internally that handles blank values and safely converts decimal values (our helper function CM.ToDecimal will handle all kinds of conversions, including removing $ signs etc). So you can do whatever you want in the conversion and throw exceptions for validation errors:

    public sealed class GiantGearExcelClassMap : ExcelClassMap<ExtendedGiantRecord>
    {
        /// <summary>
        /// Type converter for integers that will simply return 0 if the field is blank
        /// </summary>
        public class BlankIntConverter : Int32Converter
        {
            public override object ConvertFromExcel(
                TypeConverterOptions options,
                object excelValue)
            {
                return excelValue is int ? excelValue : CM.ToInt(excelValue?.ToString());
            }
        }

        /// <summary>
        /// Type converter for decimals that will simply return 0 if the field fails to parse.
        /// </summary>
        public class SafeDecimalConverter : DecimalConverter
        {
            public override object ConvertFromExcel(
                TypeConverterOptions options,
                object excelValue)
            {
                return excelValue is decimal ? excelValue : CM.ToDecimal(excelValue?.ToString());
            }
        }

        public GiantGearExcelClassMap()
        {
            Map(m => m.SupplierModel).Name("Giant Part Number");
            Map(m => m.Manufacturer).Name("Brand");
            Map(m => m.UPC).Name("UPC");
            Map(m => m.ManufacturerPartNumber).Name("Manufacturer Part Number");
            Map(m => m.Name).Name("Model Description");
            Map(m => m.Size).Name("Size");
            Map(m => m.Color).Name("Color");
            Map(m => m.Style).Name("Style");
            Map(m => m.Category).Name("Category");
            Map(m => m.ModelYear).Name("Model Year").TypeConverter<BlankIntConverter>();
            Map(m => m.Cost).Name("EachCost").TypeConverter<SafeDecimalConverter>();;
            Map(m => m.QtyBreak).Name("QuantityBreak").TypeConverter<BlankIntConverter>();
            Map(m => m.QtyPrice).Name("QuantityCost").TypeConverter<SafeDecimalConverter>();
            Map(m => m.Retail).Name("MSRP").TypeConverter<SafeDecimalConverter>();
            Map(m => m.MAPExempt).Name("UMAP Exempted").BooleanStyleYesBlank();
        }
    }

[cocowalla]

Ah, I think we have crossed wires about the type of validation 😅

The solution you have provided is for type mapping between Excel and classes, with a means to add validation during the mapping. Very useful, but unfortunately not what I'm looking for 😄 I'm reading dynamic tabular data from Excel files (so can't use a class map), with data types and validation rules decided based on column header names. I'm already doing validation C#-side (outwith a type mapper), and I'm happy with the way it works.

What I want to do is also add Excel data validation rules within the Excel worksheet - what you see as "Data Validation" in Excel, in the "Data" tab of the ribbon. The idea is to have basic validations within the Excel file (e.g. "Text Length", so users don't make easily avoidable mistakes, then when they upload the Excel file we can read it with ExcelHelper and perform much more rigorous and complex validations.

[kendallb]

Ahh so this is more for generating the excel files? I didn’t realize that existed, and sounds super interesting. So the goal would be to set it up on the type mappings and have the validations written to the excel file, so when the user downloads the file and edits it, they can’t put in bad data?

[cocowalla]

That's the end goal yes, though I'd like to have the ability to add these data validations in 2 ways, so those not using type mappings can also use it:

1. By configuring them on type mappings, as you describe
2. On a cell-by-cell, range or column basis - for example, ensuring only integers can be added in column 3