How to format a double to string and get the literal value

[wallstr33t]

The cell values in excel are all formatted as text. Each value in the source array is not a String but to a proper data type based upon its value but I want the cell formatted value as it would appear on the screen in excel. Some of the cells contain values of a data type Double -0.000023 but when I do a ToString on the value I get "-2.3E-05". I do not want to use Cell.Value as that would be a lot of overhead and kill performance.

        Object[,] source = (Object[,])Data.get_Range("C" + row, Data.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell)).Value;


        for (X = 0; X < MaxRows; X++) 
        { 
           for (Y = 0; Y < MaxCols; Y++) 
           { 
              mGridData[X, Y] = Convert.ToString(source[X + 2, Y + 2]); 
           } 
        } 

[Momerath]

I'm not sure why you are concerned about using Cell.Value, since you are using Cell.Value (it's in the first line of your block of code).

And you use the expensive Convert.ToString instead of using the objects ToString method. And if you want data formated a specific way, use format specifiers in the ToString method.

[wallstr33t]

If you notice I am not using Cell.value but using the get_range.SpecialCells.value which returns a 2 dimensional Object array of all the data in one shot, not a single cell value. And even though the cells are specfied as text Excel fills the array with values and their respective data types other than string if they qualify. The .ToString is better for performace but will still generate the same result

[TnTinMN]

I don't have the Microsoft.Office.Tools.Excel.dll so I can not test the get_Range function, but according to the documentation it returns a Microsoft.Office.Interop.Excel.Range.

The Range object has the "Text" property that returns the as-displayed (formatted) text.

[wallstr33t]

One would think the .Text property instead of .Value would return the 2 dim array with the as-displayed text but alas it does not, in fact it does not return a 2 dim array at all, it is a single string value

[Momerath]

I think you misunderstood when I said to use ToString(). Did you try it with a format specifier, i.e. ToString("F3")?

[ddanbe]

Also have a loo at this: http://www.csharp-examples.net/string-format-double/

[wallstr33t]

I think the title of this post is misleading. The problem isn't so much with c# as the problem is with excel and the data extraction through the com object. Using the string formatter will not work because that would mean I would have to know more information about the data that I am reading other than it's original value. The crux of it is in excel the user has specified a cell to be an explicit string ("-0.000023") when the value is extracted through range.value the excel is automatically converting it to a double -0.000023 not keeping the string data type. As far as I can tell the only efficient way to grab data from excel is through range.value, to get what I need requires much more overhead. Also prefixing a cell with a single quote does force the data type to be a string and the value miraculously removes the single quote when it is stored in the 2 dimensional object array through range.value.