OpenXML Excel Date Format

Question

User-1317883028 posted

I have an Excel template, where cells are formatted to what i need. It is an xlsx file.
<br>
I need to fill in the values in the appropriate cells.<br>
But when I enter date, it is not formatting correctly. I can see in debug that the cell value is correct, but the number comes as 72000.
<br>
<br>
Can someone please help? Thanks much.<br>
<br>
I am using SDK 2.0. I tried other values for style index, but the result is the same. I am not adding styles, as I assume that it should be there since the template was correctly created.<br>
<br>
<br>
Cell cl = getCell(ws.Worksheet, getColLetter(col), row);<br>
cl.CellValue = new CellValue(newVal);<br>
cl.DataType =<br>
new EnumValue&lt;CellValues&gt;(CellValues.Date);<br>
cl.StyleIndex = (UInt32Value)14U;

Answer 1

User-986267747 posted

Hi tinausa,

According to code snippet, it seems that the CellValue object cause the issue. I don’t know What is the type of the variable named newVal. If the type of the variable is DateTime, you could try to modify your code like below and check if it works for you.

cl.CellValue = new CellValue(newVal.ToOADate().ToString());

for more information, please refer to:

http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2012/02/16/dates-in-spreadsheetml.aspx

I hope it is helpful to you.

Best regards,

Klein Zhang

Answer 2

User-1317883028 posted

Thanks Klein but the date now comes as 55000.<br>
The value is correct in both cl and Val.
<br>
public void setCellValueDate(WorksheetPart ws, int row, int col, DateTime newVal)<br>
{<br>
Cell cl = getCell(ws.Worksheet, getColLetter(col), row);<br>
var val = new CellValue(newVal.ToOADate().ToString());<br>
cl.DataType =<br>
new EnumValue&lt;CellValues&gt;(CellValues.Date);<br>
cl.StyleIndex = (UInt32Value)14U;<br>
cl.CellValue = val;<br>
<br>
}<br>