Custom Format and Data Validation for date type

Aspose.Cells Product Family
1

Hi,
I have excel with date validation. and my date is in MM-dd-yyyy format. But after applying date type validation using aspose.Cells, It is not letting me update the date in cell.DateUpdate.zip (7.0 KB)
I have also attached excel file to this. Try updating column value it will through error.
I also tried adding custom styling but as soon as i add validation custom style is not being applied

2

@Amrinder_Singh,

Thanks for the template XLSX file.

I checked your file and the cell “B5” has Date data validation applied but it has “General” style/formatting. To accomplish your task you need to specify your custom DateTime formatting “mm-dd-yyyy” to the cell. I applied the style/formatting to B5 after loading your file via Aspose.Cells APIs and re-saved your file (attached in the zipped archive), it works fine and as expected.
e.g.
Sample code:


Workbook workbook = new Workbook("g:\\test2\\DateUpdate.xlsx");

// Obtaining the reference of first worksheet
Worksheet worksheet = workbook.Worksheets[0];

// Getting the Style of the B5 cell
Style style = worksheet.Cells["B5"].GetStyle();

// Setting the format to custom DateTime format
style.Custom = "mm-dd-yyyy";

// Applying the style to the B5 cell
worksheet.Cells["B5"].SetStyle(style);

// Saving the Excel file
workbook.Save("g:\\test2\\out1.xlsx");

out1.zip (7.4 KB)

I opened the output Excel file(attached) into MS Excel and enters some date, it works (if it is valid after 1900) and formatting “mm-dd-yyyy” is applied properly to the cell at the end.

Hope, this helps a bit.

3

@amjad.sahi
Hi,
I see you applied the custom date formatting. But can you do the same(apply custom formatting) and apply date validation using Aspose only?
You can create new excel sheet using workbook instead of using mine and add put any dummy date to cell and do the same + validation using Aspose only and check if it still works the same way.
Note - Date format should be mm-dd-yyyy

4

@Amrinder_Singh,

I have done what you proposed using the following sample code, it works absolutely fine and custom formatting is applied properly to the cell having Date validation. I have created an new Workbook and added Date validation with all the settings to a cell. Then I get/instantiated Style object for the cell and applied your desired custom formatting to it.
e.g.
Sample code:

            // Create a workbook.
            Workbook workbook = new Workbook();

            // Obtain the cells of the first worksheet.
            Cells cells = workbook.Worksheets[0].Cells;

            // Put a string value into the A1 cell.
            cells["A1"].PutValue("Please enter Date after 1/1/1900");

            // Set row height and column width for the cells.
            cells.SetRowHeight(0, 31);
            cells.SetColumnWidth(0, 27);

            // Get the validations collection.
            ValidationCollection validations = workbook.Worksheets[0].Validations;

            // Create Cell Area
            CellArea ca = new CellArea();
            ca.StartRow = 0;
            ca.EndRow = 0;
            ca.StartColumn = 1;
            ca.EndColumn = 1;

            // Add a new validation.
            Validation validation = validations[validations.Add(ca)];

            // Set the data validation type.
            validation.Type = ValidationType.Date;

            // Set the operator for the data validation
            validation.Operator = OperatorType.GreaterThan;

            // Set the value or expression associated with the data validation.
            validation.Formula1 = "1/1/1900";

            // Enable the error.
            validation.ShowError = true;

            // Set the validation alert style.
            validation.AlertStyle = ValidationAlertType.Stop;

            // Set the title of the data-validation error dialog box
            validation.ErrorTitle = "Date Error";

            // Set the data validation error message.
            validation.ErrorMessage = "Enter a Valid Date";

            // Set and enable the data validation input message.
            validation.InputMessage = "Date Validation Type";
            validation.IgnoreBlank = true;
            validation.ShowInput = true;

            // input some Date into B1 cell
            cells["B1"].PutValue(DateTime.Now);

            // Getting the Style of the B5 cell
            Style style = cells["B1"].GetStyle();

            // Setting the format to custom DateTime format
            style.Custom = "mm-dd-yyyy";

            // Applying the style to the B5 cell
            cells["B1"].SetStyle(style);

            // Autofit second column
            workbook.Worksheets[0].AutoFitColumn(1);

            // Saving the Excel file
            workbook.Save("g:\\test2\\out1_new.xlsx");

Please find attached the output file for your reference.
out1_new.zip (6.2 KB)

Let us know if you still have any issue or confusion.

5

@amjad.sahi
Hi,
When I try to update the date(from your output file attached) see the format changes from mm-dd-yyyy to dd-mm-yyyy, after updating it coming to same required format mm-dd-yyyy.
Is this the default behaviour with using validation and custom style through aspose only or we can change this behaviour.

6

@Amrinder_Singh,

This is MS Excel’s behavior for validation feature. You may implement your task (as per the suggested code segment) in MS Excel manually and you will notice the same behavior. If you find anything different when implementing using MS Excel (manually), let us know with details and sample file, we will check it soon.