We are attempting to read an XL spreadsheet (provided to us) with the MBS XL Plugin. We want to convert each cell to a string that looks identical to the cell value when displayed in Excel. Our first problem is dealing with cells containing percentages.
In Excel, the cell format is set to “percentage” with 4 decimal places. In Excel, it appears as 12.3456%. When using the XLSheetMBS.ReadNumber method, we get a double that is .123456 but we don’t know how many decimal places are shown in Excel. We can get an XLFormatMBS class of the cell and use the NumFormat method but it returns values that we cannot easily interpret.
Our tests indicate that cells formatted as percentages within Excel will return NumFormats of 9,168,10,165,164 and 166 when the decimal places within Excel are set to 0,1,2,3,4 and 5 for the cells. It seems that we will need to hard code all of the different NumFormats into our app to figure out how to display the numbers.
Is there a better way to get the value of a cell into a string that will look exactly as it looks in Excel?