Eric White's Blog

A few weeks ago I made an article evolving around the intricate details of how to handle dates in SpreadsheetML. This is a follow-up on to that article.

Introduction

SpreadsheetML (and OpenXml as such) is divided in two chunks – “the Transitional chunk” and “the Strict chunk”. In short – the transitional chunk contains functionality aimed at enabling compatibility with documents created by the Office applications by made by Microsoft prior to 2007 – and compatibility with the applications themselves in addition to the other “legacy” productivity suites out there like iWork, OpenOffice.org, Lotus Symphony and others. “The Strict chunk” is stripped for all that legacy functionality and is more aimed at “the future”, so to speak.

ISO-dates in spreadsheets

In my previous post I outlined how dates in cells in spreadsheets are really just styled numbers. But in Strict files this is not the case anymore. Dates are here simply what we know as “dates”.

In short – when you want to use a date in a spreadsheet cell, you simply put in a standard ISO Date.

What is supported?

ISO-dates is a comprehensive thing – defined in a standard spanning some 40 pages in total. ISO dates cover literally everything regarding dates throughout all time in the past and in the future – including time zones etc. The support for ISO-dates in Strict SpreadsheetML is a limited subset of this functionality.

The support of ISO dates is defined in the standard of OpenXml in Part 1, Section 18.17.4 Dates and times. In short the limitations are:

• Minimum date is 0001-01-01, 00:00, i.e. January 1st at midnight of the year 0
• Maximum date is 9999-12-31, 23:59:59.999
• Dates are represented as “local dates” which means that no time zone is allowed and UTC-dates are not allowed
• Fractions of seconds are limited to 3 digits of accuracy

The format for persisting dates in a Cell is this

The code

At the end of the day we’ll want to use this in our code using e.g. the OpenXml SDK.

Caveat

There is one note however regarding the usage of the ISO dates, that you need to consider. Microsoft Excel has had read capability of Strict spreadsheets since Microsoft Office 2010. But previous versions of Microsoft Office does not support this and applications like LibreOffice or OpenOffice.org does not support this either … yet.

OpenXml Markup

Remember that the markup for a date in a Cell used to be like this

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

But in strict files the markup is now this

    <row r="1">
      <c r="A1" t="d" s="1">
        <v>2012-03-01T14:30:00</v>
      </c>
    </row>

So two things have changed here:

1. The cell now uses the “t-” attribute with the value “d” indicating that the cell is of type “Date”.
2. The date is persisted in ISO date format

So what has essentially changed is only the persisting format and the type of the date. Note also that everything with styling of the ISO date is as it has always been, so you’ll still be able to style your ISO date above to be displayed as

• 2012-03-01
• 2012-01-01 14:30
• March 1st 2012 2:30PM
• …

… all according to your style settings.

C# and the SDK

When creating a date cell using the OpenXml SDK you’ll need to add a new property on the cell, the DataType-property.

The code is this

    //Create a new cell
    var c = new Cell { CellReference = "A1" };

    // Create a Date CellValue
    var typeValue = new EnumValue<CellValues> { Value = CellValues.Date };

    // Set the Cell value type to the cell
    c.DataType = typeValue;

    //Add an ISOO-date to the cell
    var value = new CellValue { Text = DateTime.Now.ToString("yyyy-MM-dd") };

    // Append the value to the cell
    c.AppendChild(value);

And that’s is really all there is to it.

Happy coding!