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 !!!
Best Answer
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 yourstyles.xml
file. This is accessible in the SDK as: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 withinstyles.xml
inside<cellXfs>
. This is accessible in the sdk as: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.
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.