Time format Validation in ClosedXML

Question

I'm Creating Excel file using ClosedXMl, and want to set alert message on entering Time format other than "HH:MM". I have tried below code but after entering "1" also it's not showing error message but same is working fine for Date format.

       ws.Cell("D7").Style.NumberFormat.Format = "hh-mm";
       ws.Cell("D7").DataValidation.Time.EqualOrGreaterThan(new TimeSpan(0, 0, 0));
       ws.Cell("D7").SetDataValidation().ErrorStyle = XLErrorStyle.Stop;
       ws.Cell("D7").SetDataValidation().ErrorMessage = "Date Format HH MM";

code which is working for Date(Alert message get popup aftering entering 1)

      ws.Cell("T5").Style.NumberFormat.Format = "dd-mmm-yyyy";
      ws.Cell("T5").SetDataValidation().Date.EqualOrGreaterThan(new DateTime(2000, 1, 1));
      ws.Cell("T5").SetDataValidation().ErrorStyle = XLErrorStyle.Stop;
      ws.Cell("T5").SetDataValidation().ErrorMessage = "Date Format DD MMM YYYY";

yes,it is working for "Date" format but i want for "Time" format also. — Teja, Aug 26 '19 at 05:29
@Tejashree can you try using DateTime, like `SetDataValidation().DateTime` for validating time. — prisar, Aug 26 '19 at 05:53

score 3 · Accepted Answer · answered Aug 26 '19 at 06:05

3

Probably there is no Time property for EqualOrGreaterThan method of Time in DataValidation.Time.EqualOrGreaterThan(new TimeSpan(0, 0, 0));. But try the below method.

One thing you can do is that you can put time along with date in the date validation.

  ws.Cell("T5").Style.NumberFormat.Format = "dd-MM-yyyy HH:mm:ss";
  ws.Cell("T5").SetDataValidation().Date.EqualOrGreaterThan(new DateTime(2008, 5, 1, 8, 30, 52)); // first 
  ws.Cell("T5").SetDataValidation().ErrorStyle = XLErrorStyle.Stop;
  ws.Cell("T5").SetDataValidation().ErrorMessage = "Date Format DD MMM YYYY HH mm ss";

answered Aug 26 '19 at 06:05

prisar

3,041
2
26
27

Thank you so much for your answer but my requirement is Cell will contain time only. After entering Character it shows Error message that only HH:MM format is allowed but is doesn't work for integers value and it replace integers with 00:00. – Teja Aug 26 '19 at 07:15
can you use cell style and NumberFormat, eg `sheet.Column(1).Style.Numberformat.Format = "hh:mm";` – prisar Aug 28 '19 at 07:00

Time format Validation in ClosedXML

1 Answers1