How to define data type?

Question

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();

Emmerich

The main problem is that this line: oxa.Add(new OpenXmlAttribute("t", null, "str")); is defining the cell type to be a string As far as I can tell (and this is only a partial list) these are the values needed for various cell types: "b" == boolean "d" == DateTime "n" == Number (also the default if you don't provide one) "str" == String (what you are incorrectly using if your intent is to write a number) There are a couple of others that I don't know off the top of my head. so changing "str" to "n" and it will be stored as a number. I hope that helps. – Boole

Answer 1

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();

Chlamydospore

Hi, actually i already tried that way. And it didn't work. Do you have an example ? – Emmerich

First try to use without styles, Cell cell = new Cell(); cell.DataType = CellValues.Number; cell.CellValue = new CellValue("100"); – Chlamydospore

The following code didn't work : writer.WriteStartElement(new Cell() { DataType = CellValues.Number }, oxa); writer.WriteElement(new CellValue(ua.Ordre.ToString())); writer.WriteEndElement(); – Emmerich

oxa.Add(new OpenXmlAttribute("t", null, "str")); Comment it. – Chlamydospore

Already did, without success. I did the mistake only on the example i provided here. Thx anyway. – Emmerich

Also, to prove that i did properly the change here is the XML generated in the XLSX file : <x:c t="n"> <x:v>1001</x:v> </x:c> But it's not number... – Emmerich

Check xml, are you any place - t="str" ? – Chlamydospore

There arn't. So i added a "0" to the data "01001". And it actually convert it to number "1001". It's actually working even if excel doesnt show it as number in the option. I still have an issue with the big number, for example 220104192221 become 2,20104E+11. Any idea ? – Emmerich

Commenting the row: oxa.Add(new OpenXmlAttribute("t", null, "str")); won't help, you need to change it to oxa.Add(new OpenXmlAttribute("t", null, "n")); – Boole