Databases
StackOverflow2013
Postsdbo
POOpenXml and Date format in Excel cell
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOpenXml and Date format in Excel cell
    primarykey
    data
    text
    <p>I am trying to create an Excel file in xlsx format using OpenXML because I need to use that on a web server.</p> <p>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.</p> <p>Below a quick test using <code>DocumentFormat.OpenXml</code> and WindowsBase references.</p> <pre><code>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&lt;WorksheetPart&gt;(); string relId = workbookPart.GetIdOfPart(worksheetPart); // File Version var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" }; // Style Part WorkbookStylesPart wbsp = workbookPart.AddNewPart&lt;WorkbookStylesPart&gt;(); 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&lt;OpenXmlElement&gt; CreateSheetData1() { List&lt;OpenXmlElement&gt; elements = new List&lt;OpenXmlElement&gt;(); 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; // &lt;= here I try to apply the style... cells[0] = cell3; row.Append(cells); elements.Add(row); return elements; } </code></pre> <p>The code executed creates the Excel document. However when I try to open the document, I receive this message: <em>“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.”</em></p> <p>If I remove the row:</p> <pre><code>cell3.StyleIndex = 1; </code></pre> <p>I can open the document but the date if not formatted, only the number of the date appears.</p> <p>Thank you for your help to format the date.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAlex
    1. USDan
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTDuplicate
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POOpenXml and Date format in Excel cell
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POOpenXml and Date format in Excel cell
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POOpenXml and Date format in Excel cell
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload