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

Nooo.'DocumentFormat.OpenXml.Spreadsheet.CellValues' does not contain a definition for 'Double' — Horrible Programmer, Jun 17 '14 at 05:26
Noo.... It told me (local variable)double? Error : The best overloaded method match for 'DocumentFormat.OpenXml.Spreadsheet.CellValue(string)' has some invalid arguments. — Horrible Programmer, Jun 17 '14 at 05:31
Only change `DataType` value and keep passing `string` -instead of `Double`- to `CellValue` constructor — har07, Jun 17 '14 at 05:55

score 5 · Accepted Answer · answered Jun 17 '14 at 05:52

5

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.

answered Jun 17 '14 at 05:52

har07

88,338
12
84
137

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? – D_Bester Jun 17 '14 at 05:56
1

@D_Bester Not really, [`CellValue`](http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.celltype.cellvalue%28v=office.14%29.aspx) property should be assigned a `CellValue` object. And `CellValue` class [doesn't have constructor that accept the number directly](http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.cellvalue.cellvalue%28v=office.14%29.aspx) – har07 Jun 17 '14 at 06:02
Thanks man. It works. I just took off my share string. I think it's the source where problem came. – Horrible Programmer Jun 17 '14 at 06:07
@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. – D_Bester Jun 17 '14 at 06:07

score 1 · Answer 2 · edited Jan 12 '17 at 19:39

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

I edited my answer that is now more complete and fully relevant. — Mister B., Feb 13 '15 at 00:19
The link to createCellFormat has changed: http://www.lateral8.com/articles/openxml-format-excel-values.html — Sebastian Widz, Oct 26 '19 at 17:34

score 0 · Answer 3 · answered Jun 17 '14 at 05:59

OpenXML always creates a cell as Inline Text, and relies upon formatting to determine the correct display format within Excel. Each number format in Excel corresponds to a unique code, and a style can be applied to the cell, the same way you might change the style from within Excel.

The OpenXML Standard defines a series of codes which can be assigned without creating a custom StyleSheet.

The implied format for a double is 4 #,##0.00

so first you create the styleId:

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

then, set the styleId on the cell:

cell.StyleIndex = _doubleStyleId;

score 0 · Answer 4 · answered Dec 15 '20 at 09:16

Super late to this party but I ran into this problem and to me the solution was in the default stringifying of my local language, presumably the use of ',' as a decimal delimiter

Cell cell = new Cell() {
    DataType = CellValues.Number,
    CellValue = new CellValue(value.ToString(new CultureInfo("en-US"))) };

solved it for me.

Changing DataType of cell to Double

4 Answers4

is OK

Linked