How to insert a date to an Open XML worksheet?
Solution 1
You have to convert DateTime
to double
using function ToOADate
i.e.:
DateTime dtValue = DateTime.Now;
string strValue = dtValue.ToOADate().ToString(CultureInfo.InvariantCulture);
then set it as CellValue
Cell cell;
cell.DataType = new EnumValue<CellValues>(CellValues.Date);
cell.CellValue = new CellValue(strValue);
Remember to format cell using DateTime
formatting, otherwise you will see double
value, not date.
Solution 2
I used the code provided by Andrew J, but the DataType
CellValues.Date
produced a corrupted xlsx-file for me.
The DataType
CellValues.Number
worked fine for me (Don't forget to set NumberFormatId
):
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
My whole code:
DateTime valueDate = DateTime.Now;
string valueString = valueDate.ToOADate().ToString();
CellValue cellValue = new CellValue(valueString);
Cell cell = new Cell();
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
cell.StyleIndex = yourStyle; //StyleIndex of CellFormat cfBaseDate -> See below
cell.Append(cellValue);
My CellFormat for this cell in the Stylesheet looks like:
CellFormat cfBaseDate = new CellFormat() {
ApplyNumberFormat = true,
NumberFormatId = 14, //14 is a localized short Date (d/m/yyyy) -> See list below
//Some further styling parameters
};
If you'd like to format your date another way, here is a list of all default Excel NumberFormatId
's
ID FORMAT CODE 0 General 1 0 2 0.00 3 #,##0 4 #,##0.00 9 0% 10 0.00% 11 0.00E+00 12 # ?/? 13 # ??/?? 14 d/m/yyyy 15 d-mmm-yy 16 d-mmm 17 mmm-yy 18 h:mm tt 19 h:mm:ss tt 20 H:mm 21 H:mm:ss 22 m/d/yyyy H:mm 37 #,##0 ;(#,##0) 38 #,##0 ;[Red](#,##0) 39 #,##0.00;(#,##0.00) 40 #,##0.00;[Red](#,##0.00) 45 mm:ss 46 [h]:mm:ss 47 mmss.0 48 ##0.0E+0 49 @
Source of list: https://github.com/ClosedXML/ClosedXML/wiki/NumberFormatId-Lookup-Table
I know this list is from ClosedXML, but it's the same in OpenXML.
Solution 3
When creating new SpreadsheetDocument
from scratch, for Date
formatting to work, minimal Stylesheet
has to be created.
Critical are those few lines:
new CellFormat
{
NumberFormatId = 14,
ApplyNumberFormat = true
})
Full Stylesheet
class:
using (var spreadSheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
// Workbook
var workbookPart = spreadSheet.AddWorkbookPart();
workbookPart.Workbook =
new Workbook(new Sheets(new Sheet { Name = "Sheet1", SheetId = (UInt32Value) 1U, Id = "rId1" }));
// Add minimal Stylesheet
var stylesPart = spreadSheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
stylesPart.Stylesheet = new Stylesheet
{
Fonts = new Fonts(new Font()),
Fills = new Fills(new Fill()),
Borders = new Borders(new Border()),
CellStyleFormats = new CellStyleFormats(new CellFormat()),
CellFormats =
new CellFormats(
new CellFormat(),
new CellFormat
{
NumberFormatId = 14,
ApplyNumberFormat = true
})
};
// Continue creating `WorksheetPart`...
After Stylesheet
is added, DateTime
can be formatted:
if (valueType == typeof(DateTime))
{
DateTime date = (DateTime)value;
cell.CellValue = new CellValue(date.ToOADate().ToString(CultureInfo.InvariantCulture));
// "StyleIndex" is "1", because "NumberFormatId=14"
// is in the 2nd item of `CellFormats` array.
cell.StyleIndex = 1;
}
Note that StyleIndex
value depends on the order of CellFormat
items in the CellFormats
array or the Stylesheet
object. In this example NumberFormatId = 14
item on the 2nd item in the array.
Solution 4
There are 2 ways to store dates in OpenXml; by writing a number (using ToOADate
) and setting the DataType
to Number
or by writing an ISO 8601 formatted date and setting the DataType
to Date
. Note that the default DataType
is Number
so if you go with the first option you don't have to set the DataType
.
Whichever method you choose, you'll need to set the style as Excel displays both methods identically. The following code shows an example of writing a date using the Number
format (with and without explicitly setting the DataType
) and using the ISO 8601 format.
using (SpreadsheetDocument document = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
//fluff to generate the workbook etc
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet" };
sheets.Append(sheet);
workbookPart.Workbook.Save();
var sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
//add the style
Stylesheet styleSheet = new Stylesheet();
CellFormat cf = new CellFormat();
cf.NumberFormatId = 14;
cf.ApplyNumberFormat = true;
CellFormats cfs = new CellFormats();
cfs.Append(cf);
styleSheet.CellFormats = cfs;
styleSheet.Borders = new Borders();
styleSheet.Borders.Append(new Border());
styleSheet.Fills = new Fills();
styleSheet.Fills.Append(new Fill());
styleSheet.Fonts = new Fonts();
styleSheet.Fonts.Append(new Font());
workbookPart.AddNewPart<WorkbookStylesPart>();
workbookPart.WorkbookStylesPart.Stylesheet = styleSheet;
CellStyles css = new CellStyles();
CellStyle cs = new CellStyle();
cs.FormatId = 0;
cs.BuiltinId = 0;
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
styleSheet.Append(css);
Row row = new Row();
DateTime date = new DateTime(2017, 6, 24);
/*** Date code here ***/
//write an OADate with type of Number
Cell cell1 = new Cell();
cell1.CellReference = "A1";
cell1.CellValue = new CellValue(date.ToOADate().ToString());
cell1.DataType = new EnumValue<CellValues>(CellValues.Number);
cell1.StyleIndex = 0;
row.Append(cell1);
//write an OADate with no type (defaults to Number)
Cell cell2 = new Cell();
cell2.CellReference = "B1";
cell2.CellValue = new CellValue(date.ToOADate().ToString());
cell1.StyleIndex = 0;
row.Append(cell2);
//write an ISO 8601 date with type of Date
Cell cell3 = new Cell();
cell3.CellReference = "C1";
cell3.CellValue = new CellValue(date.ToString("yyyy-MM-dd"));
cell3.DataType = new EnumValue<CellValues>(CellValues.Date);
cell1.StyleIndex = 0;
row.Append(cell3);
sheetData.AppendChild(row);
worksheetPart.Worksheet.Save();
}
Related videos on Youtube
Manuel
Updated on July 09, 2022Comments
-
Manuel almost 2 years
I'm using Microsoft Open XML SDK 2 and I'm having a really hard time inserting a date into a cell. I can insert numbers without a problem by setting
Cell.DataType = CellValues.Number
, but when I do the same with a date (Cell.DataType = CellValues.Date
) Excel 2010 crashes (2007 too).I tried setting the
Cell.Text
value to many date formats as well as Excel's date/numeric format to no avail. I also tried to use styles, removing the type attribute, plus many other pizzas I threw at the wall…Can anyone point me to an example inserting a date to a worksheet?
-
David Thielen over 12 yearsAnd watch out for the 1904 flag - dates can be set to be from 1-1-1900 or 1-1-1904 (because of a bug in how it calculates leap years).
-
DVA over 12 yearsStrangely that did not work for me. Even on Excel 2010.(CellValues.Date should not be supported on 2007)
-
user236215 over 11 yearsHow do you look up StyleDate??
-
Panagiotis Kanavos over 11 yearsDON'T just call ToString() and try to replace ",". Call ToString(CultureInfo.InvariantCulture) to ensure proper formatting. The compiler and Resharper even have a warning for this thing
-
World Wide DBA over 8 yearsThis worked great for me, thanks! The accepted solution did not work for me as you say, the Date type isn't actually supported.
-
IamSierraCharlie about 4 yearsUnfortunately this doe not work - maybe something has changed from the time the answer was posted. Excel 365 doesn't recognise the formatting and your spreadsheet is corrupted