This project has moved. For the latest updates, please go here.

Text like "1.0.0.0" converted to "1000" ?

Jul 25, 2011 at 10:14 PM

Hi, i write a version string to a text cell like this

wb.NamedRanges.NamedRange("ParamProgramVersion").Range.Value = "1.0.0.0"  #ParamProgramVersion is a text cell

The cell contains the string "1000". All dots are removed. If i change to first . to a , then cell value is corret "1,0.0.0"

Do i miss something?

thx, Peter
Coordinator
Jul 25, 2011 at 10:24 PM

What's your locale? It is most likely that in your locale .NET can translate "1.0.0.0" to a number. If that's the case refer to Text and Numbers.

Jul 25, 2011 at 10:30 PM

German.

But my Cell's data type is already TEXT. Before and after? i would understand if 1.000 will be converted to 1000, but not 1.0.0.0 ??

Coordinator
Jul 25, 2011 at 10:35 PM

I'll check...

Thank you for your patience =)

Coordinator
Jul 27, 2011 at 9:29 PM

I can't reproduce it. Here's the code I'm using:

            var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("Sheet1");
            ws.Cell(1, 1).AddToNamed("ParamProgramVersion");
            wb.NamedRanges.NamedRange("ParamProgramVersion").Range.Value = "1.0.0.0";

Does this gives you the error?

Jul 28, 2011 at 12:04 PM

Yes, it gives me the error too. That is in my sheet.xml file:

- <x:sheetData>
-   <x:row r="1" spans="1:1">
-     <x:c r="A1" s="0" t="n">
         <x:v>1000</x:v>
  </x:c>
 </x:row> 
 </x:sheetData>
Coordinator
Jul 29, 2011 at 6:04 AM

I'm able to reproduce the error now but there's nothing I can do. With DE locale .NET recognizes 1.0.0.0 as a number. I suggest you use one of the methods described on Text and Numbers.

Jul 29, 2011 at 7:08 AM

I changed it from

wb.NamedRanges.NamedRange("ParamProgramVersion").Range.Value = "1.0.0.0";

to
wb.NamedRanges.NamedRange("ParamProgramVersion").Range.SetValue("1.0.0.0");


and it works now. THX.
But is it a .NET error which needs to be reported to MS?

Thanks a lot. Peter
Coordinator
Jul 29, 2011 at 5:16 PM

It's not a bug because DE uses dots as thousands separators. The same would happen with a US locale and using "1,0,0,0". The thousands separator is discarded when converting to number.

Btw, later on I'll add a flag for you to specify if you want it to be "smart" (try to interpret the values) or not.

Jul 29, 2011 at 11:09 PM

Great. Smart is always good ;). I can live with the current solution, but i still think that 1.0.0.0 is not a valid number in DE locale like 1,0,0,0 is not a valid number in US locale.

Aug 6, 2011 at 8:00 AM

Is the flag already in the latest build?