C # Open XML SDK 2.0 table Setting date format in cell

Question

I am having trouble finding this.

my situation:

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

my problem:
Some of the data in the excel files I want to build exists in DateTime format. Since I don't want to use only strings (string dates cannot be sorted properly), I want to set the cells that contain the DateTime to the format of my choice, as I would do in excel.
As far as I understand, I need to use the Style Sheet to get to this point. For some time I was browsing the web to find someone who has a simple explanation for this problem, but seems hard to find.

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

here 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 style additions such as a time date (for example, "dd-MM-yyyy") and, possibly, a more advanced style later?

Hope I was specific enough :) at that time I will continue to search ...

thanks!!!

Answer 1

There are many dates in formatting digits.

You need to start with the number format. Either specify the embedded format that matches the template you want, or create your own. Built-in formats are given in ECMA-376, second edition, part 1 - Basics and description of the markup language section 18.8.30 (link to styles and <numFmt> . If you need to create your own format, start with ID 164 and add them to the <numFmts> element <numFmts> in your styles.xml . This is available in the SDK as:

 doc.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats 

Then you need to have a cell format that relates to the date format. You always need a cell format, no built-in. The cell style refers to the format of the number numFmtId and is defined inside styles.xml inside <cellXfs> . This is available in sdk as:

 doc.WorkbookPart.WorkbookStylesPart.Stylesheet.CellStyles 

Cell styles themselves do not have an identifier. They are referred to by the zero position of the index in the cell style list. Therefore, when you create your cells, set their style index in the style that you want for your dates.

For a value, you can save them in ISO 8601 format , but Excel 2010 still uses the date format to save its dates. If you use anything other than the 1900s, you must specify it in the properties of the book.

 doc.WorkbookPart.Workbook.WorkbookProperties.DateCompatibility 

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

In 1900, based on the serial data, the number is the days from December 31, 1899 with an additional complication, which you should consider February 29, 1900 as a valid date, although 1900 is not technically a leap year.

Below is the method I wrote to convert from sequential date values ​​to DateTime. You need the opposite.

 /// <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); } 

Answer 2

Others suggested using a library, such as Closed XML

from this discussion of Open XML and date formats