C# Excel Percentages Converted to Decimals
c#, excel, formatting
Solution
Check the Excel worksheet - if the cells are formatted as `Percentage` then the data is only displayed as, eg: `90.0%`, but is stored as `0.9`.
If you check the cell format before importing the number then you can handle any conversions required.
Assuming you are using Interop.Excel :
The `Range.NumberFormat` property returns the same format strings you would use in the `Format Cells` dialog box in Excel. `Percentage` is just a shortcut to a format string of the type `0.00%` where the `decimal places` selected in the `Percentage` type simply alters the number of zeroes after the decimal point in the format string.
MSDN - Range.NumberFormat
EDIT
Example of how to find the format of a range of cells :
Worksheet wks = new Worksheet();
String nfmt = (string)((Range)wks.Cells[1,1]).NumberFormat;
Where `nfmt` will contain a format string which you can check for `%` to determine whether the cell (or range) is formatted to display a fraction as a percentage. Note that `nfmt` will be `NULL` if the range spans cells with different format strings!
Problem
I am reading data from an Excel worksheet. Some of the data is percent values and these values get converted to decimals automatically -- ie 90% --> 0.90. I can't seem to find anyway to keep this from happening. Also, aren't all the data on the Excel sheet strings? So why would number formatting be happening at all? Any advice is appreciated. Regards.