C# Open Xml SDK 2.0 Spreadsheet Setting Cell DateTime Format

Question

I'm having trouble finding this one.

my situation:

• SDK 2.0
• no template spreadsheet
• C# 4.0 in VS2010

my problem:
Certain data in the excel files I want to build exists in DateTime format. As I don't wan't to use just strings (stringed datetimes can't be sorted properly) I want to set the cells that contain DateTime to a format of my choosing as I would do in excel.
To my understanding I have to use a Stylesheet to get to that point. I've been browsing the web for a while now to find someone who has a simple explanation to this problem, but it 's seems that it is hard to find.

I already have a spreadsheet in mem, with the ability to add data, through SheetData. the only thing I'm missing is the formatting/styling of the cells.

this is how far I got:

DocumentFormat.OpenXml.Packaging.SpreadsheetDocument doc = SpreadsheetDocument.Create("test.xlsx", SpreadsheetDocumentType.Workbook);

WorkbookPart wbPart = doc.AddWorkbookPart();
wbPart.Workbook = new Workbook();

SheetData data = new SheetData(
            new Row(...etc));

WorksheetPart wsPart = wbPart.AddNewPart<WorksheetPart>();
wsPart.Worksheet = new Worksheet(data);

Sheets sheets = doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

Sheet sheet = new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(wsPart), SheetId = 1, Name = "TestSheet" };
sheets.Append(sheet);

wbPart.Workbook.Save();

doc.Close();

where and how can I add simple additions to style like date time (ex, "dd-MM-yyyy"), and maybe more advanced styling later on ?

I hope I was specific enough :) in the meanwhile I'll keep looking...

THX !!!

Answer 1

There's a lot involved in formatting numbers as dates.

You need to start with the number format. Either identify the built-in format that matches the pattern you want or create a custom one. The built-in formats are in ECMA-376, Second Edition, Part 1 - Fundamentals And Markup Language Reference section 18.8.30 (the reference for styles and <numFmt>. If you need to create a custom format, start with ID 164 and add them to the <numFmts> element within your styles.xml file. This is accessible in the SDK as:

doc.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats

Next you need to have a cell format that refers to a date format. You always need a cell format, there are no built-in ones. The cell style refers to the number format by numFmtId and are defined within styles.xml inside <cellXfs>. This is accessible in the sdk as:

doc.WorkbookPart.WorkbookStylesPart.Stylesheet.CellStyles

Cell styles themselves do not have an ID. They are referred to by zero-index position within the cell styles list. So when you create your cells, set their style index to the style you want for your dates.

For the value, you can store them in ISO 8601 format but Excel 2010 still uses date serial format to store its dates. If you use anything other than 1900-based date serial, you need to specify it in the workbook properties.

doc.WorkbookPart.Workbook.WorkbookProperties.DateCompatibility

There are two date compatibility settings for storing date serial values, they can be base 1900 or base 1904. 1900 is what Excel 2010 uses and 1904 is for backwards compatibility with old Excel for Mac.

In 1900 based date serials the number is the days since December 31, 1899 with the added complication that you have to treat February 29, 1900 as a valid date even though 1900 technically isn't a leap year.

Below is the method I wrote for converting from date serial values to DateTime. You need the reverse.

/// <summary>
/// Represents the formula used for converting date serial values stored within the workbook into DateTime instances.
/// </summary>
/// <remarks>
/// Information on date serial conversion is available here: http://www.documentinteropinitiative.com/implnotes/ISO-IEC29500-2008/001.018.017.004.001.000.000.aspx
/// </remarks>
public enum XlsxDateCompatibility
{
    /// <summary>
    /// Standard dates are based on December 30, 1899 and are considered "Standard 1900" dates.
    /// </summary>
    StandardBase1900,

    /// <summary>
    /// Excel for Windows backwards compatible dates are based on December 31, 1899 are are considered "Backwards compatible 1900" dates.
    /// </summary>
    BackwardsCompatibleBase1900,

    /// <summary>
    /// Excel for Macintos backwards compatible dates are based on January 1, 1904 and are considered "1904" dates.
    /// </summary>
    BackwardsCompatibleBase1904
}

    private static readonly IDictionary<XlsxDateCompatibility, DateTime> _dateSerialBaseDates
        = new Dictionary<XlsxDateCompatibility, DateTime>
            {
                {XlsxDateCompatibility.StandardBase1900, new DateTime(1899, 12, 30)},
                {XlsxDateCompatibility.BackwardsCompatibleBase1900, new DateTime(1899, 12, 31)},
                {XlsxDateCompatibility.BackwardsCompatibleBase1904, new DateTime(1904, 1, 1)}
            };

    public static DateTime DateSerialToDateTime(double dateSerial, XlsxDateCompatibility dateCompatibility)
    {

        // special case for dateCompaitility 1900, Excel thinks 1900 is a leap year
        // http://support.microsoft.com/kb/214019
        if (dateCompatibility == XlsxDateCompatibility.BackwardsCompatibleBase1900 && dateSerial >= 61.0)
        {
            dateSerial -= 1;
        }

        DateTime baseDate;          
        if (!_dateSerialBaseDates.TryGetValue(dateCompatibility, out baseDate))
        {
            baseDate = _dateSerialBaseDates[XlsxDateCompatibility.StandardBase1900];
        }
        return baseDate.AddDays(dateSerial);
    }