Changing DataType of cell to Double

Question

I'm using OpenXML to create Microsoft Excel file. I'm trying to insert a double type variable (Example : 4.987456789) into the Excel using

Cell cell = new Cell()
{
    CellReference = "A2",
    DataType = CellValues.String,
    CellValue = new CellValue(Convert.ToString(value))
};

But, when the cell is being made, it's in text form and Excel says "The number in this cell is formatted as text or preceded by an apostrophe." How can format the cell to insert double?

Edit : Sorry, It's double? type and I follow this tutorial

Solution 1

Using CellValues.Number works fine for me, for example :

double? value = 4.9874567891;
Cell cell2 = new Cell()
{
    CellReference = "A2",
    DataType = CellValues.Number,
    CellValue = new CellValue(Convert.ToString(value))
};

Double value printed to excel without the warning you got.

Solution 2

I had the same problem. I followed the advices from the various posts and answers applying a style on the cell... no success.

Finally I found the origin of the problem and so the solution :

In my loop, I inserted all data in the same way i.e. using the InsertSharedStringItem() function.

If you insert a number in your spreadsheet like that, the cell formating will be useless and your number will not be considered as a number.

What you should do is to insert it "directly".

---

index =InsertSharedStringItem(myStringNumber, shareStringPart);
cell = InsertCellInWorksheet("A", 1, worksheetPart);
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
cell.StyleIndex = _doubleStyleId;

will not work.

---

cell = InsertCellInWorksheet("A", 1, worksheetPart);
cell.CellValue = new CellValue(myStringNumber);
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
cell.StyleIndex = _doubleStyleId;

is OK

with as Claies wrote :

Stylesheet styleSheet = workbook.WorkbookStylesPart.Stylesheet; 
_doubleStyleId = createCellFormat(styleSheet, null,null, UInt32Value.FromUInt32(4));

the code for createCellFormat() can be found here

Comments

[D_Bester]

I don't know OpenXML or C# but it seems odd to me that you can't simply assign the number without converting to a string. Like: CellValue = value Doesn't OpenXML work that way?

[har07]

@D_Bester Not really, CellValue property should be assigned a CellValue object. And CellValue class doesn't have constructor that accept the number directly

[Horrible Programmer]

Thanks man. It works. I just took off my share string. I think it's the source where problem came.

[D_Bester]

@har07 Thanks. I'm seeing how that works. Essentially XML is text anyway then Excel interprets the text to build the spreadsheet for the user. Makes sense.

[Mister B.]

I edited my answer that is now more complete and fully relevant.

[Sebastian Widz]

The link to createCellFormat has changed: lateral8.com/articles/openxml-format-excel-values.html