Using Apache POI to manipulate excel files

Question

I'm trying to write a test using the Marathon testing tool with Jython. I'm using the Apache POI in order to read/write with Excel files. I'm very new to Jython and the Apache POI so this question may seem very simple to some, but I can't get past it. I'm using the getCell() function in the Cell interface and it grabs the cell just fine, but the contents that it prints for me are not what I want. I want the integer value, but it returns a floating point.

for r in range(1, rowsBusiness):
    row = sheetBusiness.getRow(r)
    idNum = row.getCell(0) # it is returning double values here
    print idNum
    print idNum.getStringCellValue()

I'm okay with it returning double values so long as I can convert them to a string or integer because the application that I'm testing converts from string to integer or spits out an error, but I can't figure out how to convert from double and get rid of the decimal point. The getStringCellValue() function doesn't work on idNum. It just leaves it blank and the test gets stuck. I also formatted the Excel file so that it only takes integer values in the cells that I'm referring to. So, for example, in the excel file I have the value 1(formatted to not contain any decimal points), but the print idNum returns 1.0

Any helpful hints on how to get this to a string or integer? Or any other ideas that might contribute to a successful workaround?

Gagravarr Gagravarr · Accepted Answer · 2013-12-18T00:01:58

The Excel file format only has a few basic types, like Numeric and String. Most numbers get stored as a floating point value + formatting rules. That's why you're getting back a double not an int - that's simply what lives in the file!

Apache POI provides the DataFormatter class, which takes a floating point value and the format string, and does its best to return a string of the number as shown in Excel. For your case, calling that is likely to yield what you're after.

Using Apache POI to manipulate excel files

1 Answers