Bug 47262

Summary: POI getCellType() reads a numeric text value as CELL_TYPE_NUMERIC
Product: POI Reporter: David Wilson <david.wilson>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED INVALID    
Severity: normal    
Priority: P2    
Version: 3.2-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: SunOS   
Attachments: Input excel file

Description David Wilson 2009-05-25 12:04:19 UTC
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);

        }
    }
}
Comment 1 David Wilson 2009-05-25 12:05:38 UTC
Created attachment 23713 [details]
Input excel file
Comment 2 Nick Burch 2009-05-26 05:30:26 UTC
This is entirely to be expected. Please see the docs for more details. Excel really does store these cells as numeric.