How to define data type?

.net, c#, openxml, openxml-sdk

Solution

For use number type set `DataType` property of `Cell` object.

 Cell cell = new Cell();
 cell.DataType = CellValues.Number; 
 cell.CellValue = new CellValue("100");

Also you need comment next row:

 oxa.Add(new OpenXmlAttribute("t", null, "str"));

Also i take newCell.StyleIndex = numberIndexStyle;

For correct display and formatting number you need describe cellformat and numberformat:

  uint iExcelIndex = 164;
  NumberingFormat nformatForcedText = new NumberingFormat
  {
       NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
       FormatCode = StringValue.FromString("0.00E+00 ")
  };
  CellFormat cellFormat1 = new CellFormat()
  {
       NumberFormatId = 165U,
       ApplyNumberFormat = true
  };
  NumberingFormats numberingFormats = new NumberingFormats();
  numberingFormats.Append(nformatForcedText);
  numberingFormats.Count = UInt32Value.FromUInt32( (uint)numberingFormats.ChildElements.Count);
 CellFormats cellFormats1 = new CellFormats() {};     
 cellFormats1.Append(cellFormat1 );
 cellFormats1.Count = UInt32Value.ToUInt32((uint) cellFormats1.ChildElements.Count);
 Stylesheet StyleSheet = new Stylesheet();
 StyleSheet.Append(cellFormats1);
 StyleSheet.Append(numberingFormats);

WorkbookStylesPart stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
stylesPart.Stylesheet = StyleSheet;
stylesPart.Stylesheet.Save();

Problem

I have to create a big XLSX file. I use OpenXmlWriter to do the task as fast as possible. The file is properly create, but i can't change the data type of the field. It's always standard type, and i would like to use number format for some of them. I have tried many way but no one worked (Cell DataType, with the attribute,...). Here is an example : ``` SpreadsheetDocument fichier_excel = SpreadsheetDocument.Create(chemin + NomFichier, SpreadsheetDocumentType.Workbook); fichier_excel.AddWorkbookPart(); WorksheetPart wsp = fichier_excel.WorkbookPart.AddNewPart<WorksheetPart>(); OpenXmlWriter writer = OpenXmlWriter.Create(wsp); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); oxa = new List<OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("r", null, "1")); writer.WriteStartElement(new Row(), oxa); oxa = new List<OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("t", null, "str")); writer.WriteStartElement(new Cell(), oxa); writer.WriteElement(new CellValue("10001")); writer.WriteEndElement(); writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); writer = OpenXmlWriter.Create(fichier_excel.WorkbookPart); writer.WriteStartElement(new Workbook()); writer.WriteStartElement(new Sheets()); writer.WriteElement(new Sheet() { Name = "Inventaire", SheetId = 1, Id = fichier_excel.WorkbookPart.GetIdOfPart(wsp) }); writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); writer.Dispose(); fichier_excel.Close(); fichier_excel.Dispose(); ```

Original source