How to get formatted display cell value in excel using closedXML?

Question

I would like to get the displayed value in excel, not the rich text, but the formatted display value.

For example, if the value is "7/1/2015", and this cell is with number format:cell.Style.NumberFormat.Format="d", then in excel this number will be displayed as 1.

I would like to get the "1" by using closedXML but with no success. Below are some value I tried:

cell.Value = "7/1/2015";
cell.RichText.Text = "7/1/2015";
cell.GetString() = "7/1/2015";
cell.GetFormattedString() = "7/1/2015";
cell.GetValue<string>() = "7/1/2015";

Does any one know how to achieve this? Many thanks!

Answer 1

Have you tried using NumberFormat.Format?

ex. worksheet.Cell(rowCount, 2).Style.NumberFormat.Format = "mm/dd/yyyy";

Let me know if this is whatyou're looking for.

Answer 2

After some searching, I found this: https://github.com/ClosedXML/ClosedXML/issues/270

which indicates that closedXML formattedstring is different from Excel's and there won't be a fix. So I ended up adding my own custom handler for date time values.

Answer 3

To get the display value for an Excel cell, i used this below RichText property rather than using the Cell.Value property (which gives the actual value of the cell without formatting).

using cXl = ClosedXML.Excel;

string cellValue, col="A";
int row=1;
cXl.IXLWorksheet ws;

cellValue = ws.Cell(row, col)
              .RichText
              .ToString();