CODEWITHSUNDEEP
c#excelcalendar
Sandeep Dhamale
Sandeep Dhamale

Reputation: 489

C# Read actual cell value from Excel where Date format is Long date and Calendar type is Western

In Excel, cell format has been set to LongDate format (Note Calendar Type is western. This calendar type is not available with C# class) enter image description here

Value Displayed in Excel cell - 15 December 2019

Code to Read and Assert cell value

cellValue = ExcelInteropHelper.ReadExcelCellValue(9, 1);
Assert.AreEqual("15 December 2019 00:00:00", cellValue);

But when trying to read value from excel the value is: 15-12-2019 00:00:00

Tried Code :

    var cellValue = (object)(xlWorksheet.Cells[rowNumber, columnNumber] as Excel.Range).Value;
    // output : 15-12-2019 00:00:00

The difference in both values (expected: "15 December 2019 00:00:00" and actual: "15-12-2019 00:00:00") is due to Calendar Types Western and Gregorian. C# provides support only for Gregorian Calendar.

I don't want to change calendar type in Excel as it provides range of Datetime formats. I just need to read actual value from Excel i.e. "15 December 2019 00:00:00"

Upvotes: 0

Views: 1793

Answers (2)

Sandeep Dhamale
Sandeep Dhamale

Reputation: 489

Used Text property instead of Value to read value as is:

var cellValue = (object)(xlWorksheet.Cells[rowNumber, columnNumber] as Excel.Range).Text;

Upvotes: 0

Noah Bridge
Noah Bridge

Reputation: 345

The cell range's Value property returns the cell's actual value, regardless of the formatting the cell may have. To get what it is actually displayed by Excel on the cell after the formatting has been applied, you would need to use the range's Text property, as explained in the example in this link:

https://learn.microsoft.com/en-us/office/vba/api/Excel.Range.Text

If you want to use Assert.AreEqual with the Value property (and not the Text property), you may want to compare against the actual date value (e.g. new Date(2019, 12, 15)), not a string. To confirm this, I would step through the code and check the type of the value being returned in the cellValue variable. It more than likely is not of type string.

Upvotes: 1

Related Questions