Date validation for OpenXml Spreadsheet

Question

I am trying to create date validation on an xlsx column so far I have:

        DataValidation dataValidation = new DataValidation
        {
            Type = DataValidationValues.Date,
            AllowBlank = false,
            ShowErrorMessage = true,
            ErrorTitle = "Invalid value entered",
            Error = "Please enter a valid date in dd/mm/yyyy format",
            SequenceOfReferences = new ListValue<StringValue> { InnerText = "A2:A10000" }
        };

if I open an xlsx document and select the date validation type there are fields for the following:

Data: Between, Greater than, less than Minimum Maximum

How can I set these programmatically?

johnstaveley johnstaveley · Accepted Answer · 2017-04-24T08:48:47

This gives you a date time validation for column A rows 2 to 10,000, set your start date with formula1 (the number of days past 1900) and end date with formula2 which is the number of days after the start date (in this case 01/01/2021)

    DataValidation dataValidation = new DataValidation
    {
        Type = DataValidationValues.Date,
        AllowBlank = false,
        ShowErrorMessage = true,
        ShowInputMessage = true,
        ErrorTitle = "Invalid value entered",
        Error = "Please enter a valid date in dd/mm/yyyy format",
        SequenceOfReferences = new ListValue<StringValue> { InnerText = "A2:A10000" },
        Formula1 = new Formula1("1");
        Formula2 = new Formula2("44196");
    };

Date validation for OpenXml Spreadsheet

1 Answers