XlsIO date cells

[Greg]

I'm attempting to add some days to a date and then read the result.  Attempting to read the value of the cell shows that it is not a date.  Any guidance on how to read the results as a date?

using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Instantiate the Excel application object
                IApplication application = excelEngine.Excel;
 
                //Assigns default application version
                application.DefaultVersion = ExcelVersion.Excel2016;
 
                //A new workbook is created equivalent to creating a new workbook in Excel
                //Create a workbook with 1 worksheet
                IWorkbook workbook = application.Workbooks.Create(1);
 
                //Access first worksheet from the workbook
                IWorksheet worksheet = workbook.Worksheets[0];
 
                worksheet.EnableSheetCalculations();
 
                worksheet.Range["A1"].DateTime = DateTime.UtcNow;
                worksheet.Range["B1"].Formula = "=A1 + 7";
 
                Console.WriteLine(worksheet.Range["B1"].DateTime);
                Console.WriteLine(worksheet.Range["B1"].FormulaDateTime);
 
            }

I'm new to XlsIO, so apologies for such a simple question!

[Shamini Kiruba Sobers]

Hi Greg, 

Greetings from Syncfusion. 

You can use the highlighted workaround in the below code to add some days to a date and read the result. 

Code snippet: 

using (ExcelEngine excelEngine = new ExcelEngine()) 

{ 

    //Instantiate the Excel application object 

    IApplication application = excelEngine.Excel; 

 

    //Assigns default application version 

    application.DefaultVersion = ExcelVersion.Excel2016; 

 

    //A new workbook is created equivalent to creating a new workbook in Excel 

    //Create a workbook with 1 worksheet 

    IWorkbook workbook = application.Workbooks.Create(1); 

 

    //Access first worksheet from the workbook 

    IWorksheet worksheet = workbook.Worksheets[0]; 

 

    worksheet.EnableSheetCalculations(); 

 

    worksheet.Range["A1"].DateTime = DateTime.UtcNow; 

   worksheet.Range["B1"].DateTime = worksheet.Range["A1"].DateTime.AddDays(7); 

 

    Console.WriteLine(worksheet.Range["B1"].DateTime); 

    Console.WriteLine(worksheet.Range["B1"].CalculatedValue); 

    Console.WriteLine(worksheet.Range["B1"].DisplayText); 

 

} 

You can also check whether an Excel cell contains DateTime value using HasDateTime property. 

However, we have an issue with the formula like below. 

worksheet.Range["B1"].Formula = "=A1+7"; 

We will validate the issue and get back to you with the validation details on May 29^th,2020. 

Kindly let us know if the workaround helps. 

Regards, 

Shamini

[Greg]

Hi Shamini.  I really appreciate the quick response.

Unfortunately in this case the workaround doesn't help.  The formula, "=A1+7" is supplied by the user.  I created the code to replicate the problem.  As the formula works in Excel, the hope is that it will work in XlsIO as well.

thanks...

[Shamini Kiruba Sobers]

Hi Greg, 

Thanks for the update. 

We are validating the issue in formula with DateTime values. We will share the validation details on May 29^th,2020 as promised earlier. 

Regards, 

Shamini

[Shamini Kiruba Sobers]

Hi Greg, 

MS Excel detects the number format automatically if a formula is added to a cell. But, XlsIO will not detect its number format when you set a formula. This is the behaviour of XlsIO. 

To achieve your requirement, we suggest you set the number format to the cell. Please refer the following code snippet for this. 

using (ExcelEngine excelEngine = new ExcelEngine())  

{  

    //Instantiate the Excel application object  

    IApplication application = excelEngine.Excel;  

  

    //Assigns default application version  

    application.DefaultVersion = ExcelVersion.Excel2016;  

  

    //A new workbook is created equivalent to creating a new workbook in Excel  

    //Create a workbook with 1 worksheet  

    IWorkbook workbook = application.Workbooks.Create(1);  

  

    //Access first worksheet from the workbook  

    IWorksheet worksheet = workbook.Worksheets[0];  

  

    worksheet.EnableSheetCalculations();  

  

    worksheet.Range["A1"].DateTime = DateTime.UtcNow;  

    worksheet.Range["B1"].Value = "=A1+7"; 

 

    worksheet.Range["B1"].NumberFormat = worksheet.Range["A1"].NumberFormat; 

     

    Console.WriteLine(worksheet.Range["B1"].DateTime);  

    Console.WriteLine(worksheet.Range["B1"].CalculatedValue);  

    Console.WriteLine(worksheet.Range["B1"].DisplayText);  

  

}  

 

You can also refer to the UG link to apply number formats from the following link. 

https://help.syncfusion.com/file-formats/xlsio/working-with-cell-or-range-formatting#apply-number-formats 

 

Regards, 

Shamini 

[Greg]

Thanks, Shamini.  This code allowed me to correctly get the date and time from the cell:

worksheet.Range["B1"].NumberFormat = "yyyy-mm-dd hh:mm:ss";
DateTimeResult = worksheet.Range["B1"].FormulaDateTime;

[Shamini Kiruba Sobers]

Hi Greg, 

We are glad that the provided solution helped you. 

Regards, 

Shamini 

[Hruday]

Hi Team ,

is there conditional formula to Check the Column Date Match with Current Date and Highlight its mismatched In date .In Attached Screenshot Current Date is Considered as 4th Oct 2022 ,so other than current date is consider as mismatch .

I have tried below code but its not working .can you pls help me ...

            IDataValidation validation = worksheet.Range["K1"].DataValidation; //K1 is Column
            validation.AllowType = ExcelDataType.User;

            //Compare the Date in K1 column with Current Date 
            validation.CompareOperator = ExcelDataValidationComparisonOperator.Equal;
            validation.FirstDateTime =DateTime.Parse(DateTime.Now.ToString("MM/dd/yyyy")) ;
            validation.ShowErrorBox = true; 

[Mohan Chandran]

Hi Hruday,

Greetings from Syncfusion.

To highlight cells with a certain rule, we suggest using conditional formatting in XlsIO. We have attached a sample to achieve your requirement.

Kindly try this and let us know whether it helps.

Regards,

Mohan.

Attachment: HighlightDate_458cf4da.zip