I am trying to create an Excel file in xlsx format using OpenXML because I need to use that on a web server.
I don’t have any problem to fill the values in the sheets; however I am struggling to set the classic Date format in a cell.
Below a quick test using DocumentFormat.OpenXml
and WindowsBase references.
class Program
{
static void Main(string[] args)
{
BuildExel(@"C:\test.xlsx");
}
public static void BuildExel(string fileName)
{
using (SpreadsheetDocument myWorkbook =
SpreadsheetDocument.Create(fileName,
SpreadsheetDocumentType.Workbook))
{
// Workbook Part
WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
string relId = workbookPart.GetIdOfPart(worksheetPart);
// File Version
var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
// Style Part
WorkbookStylesPart wbsp = workbookPart.AddNewPart<WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
// Sheets
var sheets = new Sheets();
var sheet = new Sheet { Name = "sheetName", SheetId = 1, Id = relId };
sheets.Append(sheet);
// Data
SheetData sheetData = new SheetData(CreateSheetData1());
// Add the parts to the workbook and save
var workbook = new Workbook();
workbook.Append(fileVersion);
workbook.Append(sheets);
var worksheet = new Worksheet();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
private static Stylesheet CreateStylesheet()
{
Stylesheet ss = new Stylesheet();
var nfs = new NumberingFormats();
var nformatDateTime = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(1),
FormatCode = StringValue.FromString("dd/mm/yyyy")
};
nfs.Append(nformatDateTime);
ss.Append(nfs);
return ss;
}
private static List<OpenXmlElement> CreateSheetData1()
{
List<OpenXmlElement> elements = new List<OpenXmlElement>();
var row = new Row();
// Line 1
Cell[] cells = new Cell[2];
Cell cell1 = new Cell();
cell1.DataType = CellValues.InlineString;
cell1.InlineString = new InlineString { Text = new Text { Text = "Daniel" } };
cells[0] = cell1;
Cell cell2 = new Cell();
cell2.DataType = CellValues.Number;
cell2.CellValue = new CellValue((50.5).ToString());
cells[1] = cell2;
row.Append(cells);
elements.Add(row);
// Line 2
row = new Row();
cells = new Cell[1];
Cell cell3 = new Cell();
cell3.DataType = CellValues.Date;
cell3.CellValue = new CellValue(DateTime.Now.ToOADate().ToString());
cell3.StyleIndex = 1; // <= here I try to apply the style...
cells[0] = cell3;
row.Append(cells);
elements.Add(row);
return elements;
}
The code executed creates the Excel document. However when I try to open the document, I receive this message: “Excel found unreadable content in 'test.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.”
If I remove the row:
cell3.StyleIndex = 1;
I can open the document but the date if not formatted, only the number of the date appears.
Thank you for your help to format the date.
Best Answer
This blog helped me: http://polymathprogrammer.com/2009/11/09/how-to-create-stylesheet-in-excel-open-xml/
My problem was that I wanted to add NumberingFormats to the stylesheet rather than adding a new stylesheet altogether. If you want to to that, use
rather than
surprise, order counts..