1

Having tried all different solutions from this question, it doesn't seem to give me the wanted format (1.000.000 | 1.234).

I Have tried this:

dataBlock.ValueCell.Value = String.Format("{0:#.##0}", double.Parse(dataBlock.ValueCell.Value)); // 1 234
dataBlock.ValueCell.Value = String.Format("{0:N0}", double.Parse(dataBlock.ValueCell.Value)); // 1 234
dataBlock.ValueCell.Value = String.Format("{0}", double.Parse(dataBlock.ValueCell.Value.ToString("N0"))); // 1 234

//values as I read them from Excel = OLDVALUE
//values as I convert them to the wanted number format = NEWVALUE
//OLDVALUE == 2238,8
//NEWVALUE == 2 239
//OLDVALUE == -5372
//NEWVALUE == -5 372
//OLDVALUE == 3909,6
//NEWVALUE == 3 910

Any other solutions?

I could replace the space with a dot, but that does seem like bad code.

Edits

edit1: I tried this

dataBlock.ValueCell.Value = dataBlock.ValueCell.Value.ToString().Replace(' ', '.'); And it give me the same output, how come the string format and replace don't work?

edit2: added Nikita's answer [Works]

var doubleValue = double.Parse(dataBlock.ValueCell.Value);
                Console.WriteLine("doubleValue = " + doubleValue);

                var formattedValue = doubleValue.ToString("N0", new CultureInfo("is-IS"));
                Console.WriteLine("formattedValue = " + formattedValue);

                dataBlock.ValueCell.Value = formattedValue;

gave me this output:

doubleValue = 29300
formattedValue = 29.300
doubleValue = 20300
formattedValue = 20.300
doubleValue = 32360
formattedValue = 32.360
doubleValue = 28300
formattedValue = 28.300
doubleValue = 9000
formattedValue = 9.000
...

edit3: Here is more 'missing' data to get a better view of the situation

Community
  • 1
  • 1
Edito
  • 3,030
  • 13
  • 35
  • 67

1 Answers1

1

Add an extra step to verify how double.Parse() works. There might some surprises. One of them can be the separation char between integer value and decimals: , or .

// step 1: verify your string taken from excel
var cellString = dataBlock.ValueCell.Value.ToString();
// if you have a blank space there, remove it
cellString = cellString.Replace(' ', '');

// step 2: verify your double converted value
var doubleValue = double.Parse(cellString);
Console.WriteLine("doubleValue = " + doubleValue);

// step 3: verify the formatting
var formattedValue = doubleValue.ToString("#.##0");
Console.WriteLine("formattedValue = " + formattedValue);

dataBlock.ValueCell.Value = formattedValue;

Later edit after viewing actual data: You have a blank space in the string you take from the cell. You can verify this by adding another step

tomab
  • 2,061
  • 5
  • 27
  • 38
  • 1
    Exactly. It's culture dependent. From MSDN.: "Converts the string representation of a number in a specified culture-specific format" – BaBu Sep 15 '15 at 08:17
  • So this means that this won't work on every system, it depends what language, or regional settings you have, for example sometimes it will read it as 1234,56 and sometimes as 1234.56? also this solution gave me this output http://i.imgur.com/T8qIqZB.png – Edito Sep 15 '15 at 08:22
  • Typo in the format string, as there are no arguments, `0:` must be removed. – tomab Sep 15 '15 at 08:28
  • @tomab, still same output – Edito Sep 15 '15 at 10:25
  • 1
    Ok, so there are some missing information in your question. Please add values as you have them in cells. – tomab Sep 15 '15 at 10:37