Looking at the list here, you can see:
You can see most cases seem to be covered, 22 gets very close, but what I really need is m/d/yyyy h:mm tt - does anyone know of a way to set that in OpenXml? Thank you.
There isn't a built in format to achieve what you're after within OpenXml but you can easily add your own format. The format string you need is m/d/yyyy\ h:mm\ AM/PM.
In order to apply a format you need to create a NumberingFormats object, add a NumberingFormat to it and assign it to the NumberingFormats property on a Stylesheet. The following method will create a Stylesheet with the m/d/yyyy\ h:mm\ AM/PM format:
private static Stylesheet CreateStyles()
{
Stylesheet styleSheet = new Stylesheet();
NumberingFormats nfs = new NumberingFormats();
NumberingFormat nf;
nf = new NumberingFormat();
nf.NumberFormatId = 165;
nf.FormatCode = "m/d/yyyy\\ h:mm\\ AM/PM";
nfs.Append(nf);
CellFormat cf = new CellFormat();
cf.NumberFormatId = nf.NumberFormatId;
cf.ApplyNumberFormat = true;
CellFormats cfs = new CellFormats();
cfs.Append(cf);
styleSheet.CellFormats = cfs;
styleSheet.NumberingFormats = nfs;
styleSheet.Borders = new Borders();
styleSheet.Borders.Append(new Border());
styleSheet.Fills = new Fills();
styleSheet.Fills.Append(new Fill());
styleSheet.Fonts = new Fonts();
styleSheet.Fonts.Append(new Font());
CellStyles css = new CellStyles();
CellStyle cs = new CellStyle();
cs.FormatId = 0;
cs.BuiltinId = 0;
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
styleSheet.Append(css);
return styleSheet;
}
The following code will create a new file from scratch with 2 dates (in A1 and B1) using the format added above:
public static void CreateFile(string filename)
{
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
workbookpart.AddNewPart<WorkbookStylesPart>();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Sheet 1"
};
sheets.Append(sheet);
Worksheet worksheet = new Worksheet();
SheetData sheetData = new SheetData();
Stylesheet styleSheet = CreateStyles();
Row row = CreateRow();
sheetData.Append(row);
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
workbookpart.WorkbookStylesPart.Stylesheet = styleSheet;
// Close the document.
spreadsheetDocument.Close();
}
}
private static Row CreateRow()
{
Row row = new Row();
DateTime now = DateTime.UtcNow;
//add a date cell using the number data type
Cell cell = new Cell();
cell.StyleIndex = 0;
cell.DataType = CellValues.Number;
string columnValue = now.ToOADate().ToString();
cell.CellValue = new CellValue(columnValue);
row.Append(cell);
//add a date cell using the date data type
Cell cell2 = new Cell();
cell2.StyleIndex = 0;
cell2.DataType = CellValues.Date;
columnValue = now.ToString("o");
cell2.CellValue = new CellValue(columnValue);
row.Append(cell2);
return row;
}
Original answer (based on the 24hr time with the AM/PM specifier)
Unfortunately I don't think this is possible.
You can define custom formats but section 18.8.31 numFmts of the spec states:
If the format contains AM or PM, the hour is based on the 12-hour clock, where "AM" or "A" indicates times from midnight until noon and "PM" or "P" indicates times from noon until midnight. Otherwise, the hour is based on the 24-hour clock.
So, mixing the 24hr format with an AM/PM suffix isn't possible.