Excel converting numbers as text to exponential form

Question

Am reading some cells from an excel file using apache poi , the cells values are pure numbers e.g. 6944460000 ,even though i formatted the cells as text ,the program keeps reading them as 6.9+exxx

if (line.get(0) != null) {
                    int mpnCellType = line.get(0).getCellType();
                    switch (mpnCellType) {

                        case HSSFCell.CELL_TYPE_NUMERIC:
                            mpn = (String.valueOf(line.get(0).getNumericCellValue()));
                            break;
                        case HSSFCell.CELL_TYPE_STRING:
                            mpn = (line.get(0).getStringCellValue());
                            break;
                    }

                }

eventhough am checking the cell type, i still get the exponential form , which means the source file was in that form before i read it , how i can stop this conversion ?

I'm not familiar with apache poi. But the manner in which excel stores values in cells is as "pure numbers". If you can access the .Text property of the source Excel cell with apache poi, that should return the value as you have formatted it. Changing the formatting of the Excel cell to Text AFTER you have entered the numeric value will NOT affect how it is stored. — Ron Rosenfeld, Aug 31 '14 at 11:12
possible duplicate of [Values of excel field is getting 3.0E9 where I need 3000000000 when trying to parse](http://stackoverflow.com/questions/25397377/values-of-excel-field-is-getting-3-0e9-where-i-need-3000000000-when-trying-to-pa) — Gagravarr, Aug 31 '14 at 14:51
You are not correctly converting the number to a string. See one of about a hundred duplicate questions which all provide the correct way to do it — Gagravarr, Aug 31 '14 at 14:52

score 0 · Answer 1 · answered Aug 31 '14 at 11:43

I believe that Rosenfeld is correct. If the worksheet is like:

.

then poi is picking up the displayed value rather than the intrinsic value shown in the formula bar. One approach is to fix the cell formatting prior to running poi. Try this small macro:

Sub FixFormats()
    Dim r As Range
    For Each r In ActiveSheet.UsedRange
        If CStr(r.Value) <> r.Text Then
            r.NumberFormat = 0
        End If
    Next r
End Sub

Excel converting numbers as text to exponential form

1 Answers1