The value "120" in an Excel cell that is formatted as type text is being read as type CELL_TYPE_NUMERIC with a value of "120.0" when the contents are retrieved using the cell.toString() function. test output:java test_num SamplespreadSheet.xls Cell 0,0 Numeric: 120.0. Cell 1,0 String: abc. Cell 2,0 Numeric: 120.01. test code - test_num.java import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileWriter; import java.io.IOException; import java.io.OutputStreamWriter; import java.io.PrintWriter; import java.io.FilePermission; import java.util.Iterator; import java.security.AccessController; import org.apache.commons.cli.CommandLine; import org.apache.commons.cli.CommandLineParser; import org.apache.commons.cli.GnuParser; import org.apache.commons.cli.HelpFormatter; import org.apache.commons.cli.Option; import org.apache.commons.cli.Options; import org.apache.commons.cli.ParseException; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.xml.sax.SAXException; import javax.xml.parsers.ParserConfigurationException; import javax.xml.parsers.DocumentBuilderFactory; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.SAXParser; import javax.xml.parsers.SAXParserFactory; import org.xml.sax.Attributes; import org.xml.sax.ErrorHandler; import org.xml.sax.SAXParseException; import org.xml.sax.SAXException; import org.xml.sax.InputSource; import org.w3c.dom.Document; import org.w3c.dom.Attr; import org.w3c.dom.Element; import org.w3c.dom.Text; import java.io.IOException; import java.io.FileInputStream; import java.io.FileOutputStream; import javax.xml.transform.Transformer; import javax.xml.transform.TransformerConfigurationException; import javax.xml.transform.TransformerException; import javax.xml.transform.TransformerFactory; import javax.xml.transform.OutputKeys; import javax.xml.transform.stream.StreamResult; import javax.xml.transform.dom.DOMSource; import org.xml.sax.*; import org.xml.sax.helpers.DefaultHandler; import java.util.regex.*; public class test_num { static File xlsFile; public static String file_name; public static void main (String[] args) { file_name = args[0]; xlsFile = new File(file_name); try { POIFSFileSystem fs = new POIFSFileSystem( new FileInputStream( file_name)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); int firstRow; int lastRow; int numPhysicalRows; firstRow = sheet.getFirstRowNum(); lastRow = sheet.getLastRowNum(); numPhysicalRows = sheet.getPhysicalNumberOfRows() ; for (int i = firstRow; i<= lastRow; i++){ HSSFRow row = sheet.getRow(i); if(row != null) { short firstCell; short lastCell; int physicalNumberofCells; firstCell = row.getFirstCellNum(); lastCell = row.getLastCellNum(); physicalNumberofCells = row.getPhysicalNumberOfCells(); if(physicalNumberofCells > 0){ for (int j = firstCell; j < lastCell; j++ ){ HSSFCell cell = row.getCell(j); if (cell != null) { int cellType = cell.getCellType(); switch(cellType){ case org.apache.poi.hssf.usermodel.HSSFCell.CELL_TYPE_NUMERIC: System.out.println("Cell "+i+","+j+" Numeric: "+cell.toString()+"."); break; case org.apache.poi.hssf.usermodel.HSSFCell.CELL_TYPE_STRING: System.out.println("Cell "+i+","+j+" String: "+cell.toString()+"."); break; default: System.err.println("Invalid unknown cell type: "+i+","+j+"\n");//break; } // readCell(cell, i, docRow); } } } } } } catch (Exception e) { System.err.println("caught exception "+e.toString()); e.printStackTrace(); System.exit(1); } } }
Created attachment 23713 [details] Input excel file
This is entirely to be expected. Please see the docs for more details. Excel really does store these cells as numeric.