C# Excel Percentages Converted to Decimals

Question

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.

Answer 1

ws.Cells[1, 1].NumberFormatLocal = XlConditionValueTypes.xlConditionValuePercent;
ws.Cells[1, 1].NumberFormat = "0,00%";

Answer 2

Percentage is a formatting artifact in Excel. The actual number is always a decimal fraction (0.9 in your case). Percentage is only a way to display your number on the worksheet.

If you know that a column represents percentage, you should multiply it by 100.

Answer 3

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!