OpenXML - Writing a date into Excel spreadsheet results in unreadable content

Question

I am using the following code to add a DateTime to a column in my spreadsheet:

var dt = DateTime.Now;
r.AppendChild<Cell>(new Cell()
    { 
        CellValue = new CellValue(dt.ToOADate().ToString()),
        DataType = new EnumValue<CellValues>(CellValues.Date), 
        StyleIndex = 1,
        CellReference = header[6] + index
    });

When I try to open the file in Excel 2010, I get the error

Excel found unreadable content in file.xlsx

All is fine if I comment out the line.

I have referred to similar questions on StackOverflow, but they basically have the same code as I do.

Solution 1

Late to the party as usual but I have to post an answer because ALL of the previous ones are completely wrong except for Oleh's down voted answer which was sadly incomplete.

As the question is related to Excel, the easiest thing to do is create an Excel spread sheet with the data and style you want, then open it as parts and look at the raw XML.

Adding the date 01/01/2015 into cell A1 results in the following:

<row r="1">
  <c r="A1" s="0">
    <v>42005</v>
  </c>
</row>

Note that the type attribute is not there. However there is a style attribute referencing the following style:

<xf numFmtId="14" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1" />

That is the most basic style that you must add.

So code to generate the above:

1. You need to create a style as follows:

var CellFormats = new CellFormats();
CellFormats.Append(new CellFormat()
{
    BorderId = 0,
    FillId = 0,
    FontId = 0,
    NumberFormatId = 14,
    FormatId = 0,
    ApplyNumberFormat = true
});
CellFormats.Count = (uint)CellFormats.ChildElements.Count;
var StyleSheet = new Stylesheet();
StyleSheet.Append(CellFormats);

The NumberFormatId = 14 refers to the built-in format mm-dd-yy, here's a list of some other formats.

Unfortunately it seems that adding just the above style is not quite enough and if you do it actually causes Excel to crash. Note that BorderId, FillId, FontId need to correspond to an item in the style sheet which means you need to provide them. The GetStyleSheet() method in the complete code listing provides the minimum default stylesheet required for Excel to work without errors.

2. And add a cell as follows:

SheetData.AppendChild(new Row(
    new Cell() 
    { 
        // CellValue is set to OADate because that's what Excel expects.
        CellValue = new CellValue(date.ToOADate().ToString(CultureInfo.InvariantCulture)), 
        // Style index set to style (0 based).
        StyleIndex = 0
    }));

Note: Office 2010 and 2013 can handle dates differently but by default it seems they do not.

They provide support for dates in ISO 8601 format i.e. yyyy-MM-ddTHH:mm:ss just so happens that this is also standard format sortable ("s") so you can do:

SheetData.AppendChild(new Row(
    new Cell() 
    { 
        CellValue = new CellValue(date.ToString("s")), 
        // This time we do add the DataType attribute but ONLY for Office 2010+.
        DataType = CellValues.Date
        StyleIndex = 1
    }));

The result:

<row>
  <c s="0" t="d">
    <v>2015-08-05T11:13:57</v>
  </c>
</row>

Complete Code Listing

Below is an example of the minimum code required to add a cell with date format.

private static void TestExcel()
{
    using (var Spreadsheet = SpreadsheetDocument.Create("C:\\Example.xlsx", SpreadsheetDocumentType.Workbook))
    {
        // Create workbook.
        var WorkbookPart = Spreadsheet.AddWorkbookPart();
        var Workbook = WorkbookPart.Workbook = new Workbook();

        // Add Stylesheet.
        var WorkbookStylesPart = WorkbookPart.AddNewPart<WorkbookStylesPart>();
        WorkbookStylesPart.Stylesheet = GetStylesheet();
        WorkbookStylesPart.Stylesheet.Save();

        // Create worksheet.
        var WorksheetPart = Spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
        var Worksheet = WorksheetPart.Worksheet = new Worksheet();

        // Add data to worksheet.
        var SheetData = Worksheet.AppendChild(new SheetData());
        SheetData.AppendChild(new Row(
            new Cell() { CellValue = new CellValue(DateTime.Today.ToOADate().ToString(CultureInfo.InvariantCulture)), StyleIndex = 1 },
            // Only works for Office 2010+.
            new Cell() { CellValue = new CellValue(DateTime.Today.ToString("s")), DataType = CellValues.Date, StyleIndex = 1 }));

        // Link worksheet to workbook.
        var Sheets = Workbook.AppendChild(new Sheets());
        Sheets.AppendChild(new Sheet()
        {
            Id = WorkbookPart.GetIdOfPart(WorksheetPart),
            SheetId = (uint)(Sheets.Count() + 1),
            Name = "Example"
        });

        Workbook.Save();
    }
}

private static Stylesheet GetStylesheet()
{
    var StyleSheet = new Stylesheet();

     // Create "fonts" node.
    var Fonts = new Fonts();
    Fonts.Append(new Font()
    {
        FontName = new FontName() { Val = "Calibri" },
        FontSize = new FontSize() { Val = 11 },
        FontFamilyNumbering = new FontFamilyNumbering() { Val = 2 },
    });

    Fonts.Count = (uint)Fonts.ChildElements.Count;

    // Create "fills" node.
    var Fills = new Fills();
    Fills.Append(new Fill()
    {
        PatternFill = new PatternFill() { PatternType = PatternValues.None }
        });
        Fills.Append(new Fill()
        {
            PatternFill = new PatternFill() { PatternType = PatternValues.Gray125 }
        });

    Fills.Count = (uint)Fills.ChildElements.Count;

    // Create "borders" node.
    var Borders = new Borders();
    Borders.Append(new Border()
    {
        LeftBorder = new LeftBorder(),
        RightBorder = new RightBorder(),
        TopBorder = new TopBorder(),
        BottomBorder = new BottomBorder(),
        DiagonalBorder = new DiagonalBorder()
    });

    Borders.Count = (uint)Borders.ChildElements.Count;

    // Create "cellStyleXfs" node.
    var CellStyleFormats = new CellStyleFormats();
    CellStyleFormats.Append(new CellFormat()
    {
        NumberFormatId = 0,
        FontId = 0,
        FillId = 0,
        BorderId = 0
    });

    CellStyleFormats.Count = (uint)CellStyleFormats.ChildElements.Count;

    // Create "cellXfs" node.
    var CellFormats = new CellFormats();

    // A default style that works for everything but DateTime
    CellFormats.Append(new CellFormat()
    {
        BorderId = 0,
        FillId = 0,
        FontId = 0,
        NumberFormatId = 0,
        FormatId = 0,
        ApplyNumberFormat = true
    });

   // A style that works for DateTime (just the date)
   CellFormats.Append(new CellFormat()
    {
        BorderId = 0,
        FillId = 0,
        FontId = 0,
        NumberFormatId = 14, // or 22 to include the time
        FormatId = 0,
        ApplyNumberFormat = true
    });

    CellFormats.Count = (uint)CellFormats.ChildElements.Count;

    // Create "cellStyles" node.
    var CellStyles = new CellStyles();
    CellStyles.Append(new CellStyle()
    {
        Name = "Normal",
        FormatId = 0,
        BuiltinId = 0
    });
    CellStyles.Count = (uint)CellStyles.ChildElements.Count;

    // Append all nodes in order.
    StyleSheet.Append(Fonts);
    StyleSheet.Append(Fills);
    StyleSheet.Append(Borders);
    StyleSheet.Append(CellStyleFormats);
    StyleSheet.Append(CellFormats);
    StyleSheet.Append(CellStyles);

    return StyleSheet;
}

Solution 2

Try indicating it is a CellValues.String type, instead of a CellValues.Date type.

Use

DataType = new EnumValue<CellValues>(CellValues.String)   // good

instead of

DataType = new EnumValue<CellValues>(CellValues.Date)     // bad

Now, it would make sense to add it as date, without the ToString()conversion, and use the CellValues.Date DataType -- but CellValue() only takes a string as a parameter.

[Why, OpenXmlSDK, WHY??? You're a wrapper. Wrap things nicely. Make them invisible, and make my life easier. :::sigh:::]

Additionally, if the target cell expects to be formatting a date, we should be indicating it is a date.

But I've found that while CellValues.String and CellValues.Date both get formatted as expected (identically), only the CellValues.Date throws up the "unreadable content" on-load.

I've had utterly no luck with any variation on dt.ToOADate().ToString(new CultureInfo("en-US")); method -- I end up with a five-digit number that gets displayed in the spreadsheet as five-digit number, when it should be a formatted date.

I was receiving the same error message when adding a string value, but using the CellValues.Number DataType.

Solution 3

try dt.ToOADate().ToString().Replace (",", ".") instead of dt.ToOADate().ToString()

For some working code samples see http://www.codeproject.com/KB/office/ExcelOpenXMLSDK.aspx

EDIT:

please change your code to this:

dt.ToOADate().ToString(new CultureInfo("en-US"));

Comments

[Extrakun]

I try changing the output string and the style index, but I am still getting the error.

[Kev]

Be careful when posting copy and paste boilerplate/verbatim answers to multiple questions, these tend to be flagged as "spammy" by the community. If you're doing this then it usually means the questions are duplicates so flag them as such instead: stackoverflow.com/a/12680793 | stackoverflow.com/a/12679713

[user236215]

doesnt work. I see a floating point number in my sheet. Not one answer on this subject.

[Michael Paulukonis]

yo! drive-by downvoter! If you find this to be inaccurate, please explain why. Because it worked for me. I would LOVE a better solution.

[Jeremy Thompson]

+1 for originally posting the other answer: stackoverflow.com/a/8170894/495455

[renathy]

Please, give code snippet to your answer as it is not clear.

[renathy]

This doesn't compile.

[Michael Paulukonis]

@renathy there IS a code-snippet in the answer. See the top few lines; you need to use "CellValues.String" instead of "CellValues.Date". The rest of the answer is about the answer seems like a bit of voodoo coding.

[Ludovic Feltz]

You can replace new CultureInfo("en-US") by CultureInfo.InvariantCulture

[Robert Jørgensgaard Engdahl]

Regarding the CellValue it is worth noting that your choices are international Excel compatibility (123,456 vs 123.456) and backwards compatibility with legacy Offices (yyyy-MM-ddTHH:mm:ssK vs ToOADate()).

[John]

Man that is way too complicated. I just gave up and added the date value as a string.

[Jeremy Buentello]

Something that isn't explicitly stated that messed me up for a little while is that when you are using the date.ToOADate().ToString(CultureInfo.InvariantCulture) you MUST have the datetype set to number (this is the default and is therefore left out in the example. If you are setting it yourself, be aware of that.