You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2009/05/25 21:04:22 UTC
DO NOT REPLY [Bug 47262] New: POI getCellType() reads a numeric text
value as CELL_TYPE_NUMERIC
https://issues.apache.org/bugzilla/show_bug.cgi?id=47262
Summary: POI getCellType() reads a numeric text value as
CELL_TYPE_NUMERIC
Product: POI
Version: 3.2-FINAL
Platform: PC
OS/Version: SunOS
Status: NEW
Severity: normal
Priority: P2
Component: HSSF
AssignedTo: dev@poi.apache.org
ReportedBy: david.wilson@cgi.com
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);
}
}
}
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 47262] POI getCellType() reads a numeric text
value as CELL_TYPE_NUMERIC
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=47262
Nick Burch <ni...@torchbox.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution| |INVALID
--- Comment #2 from Nick Burch <ni...@torchbox.com> 2009-05-26 05:30:26 PST ---
This is entirely to be expected. Please see the docs for more details. Excel
really does store these cells as numeric.
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 47262] POI getCellType() reads a numeric text
value as CELL_TYPE_NUMERIC
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=47262
--- Comment #1 from David Wilson <da...@cgi.com> 2009-05-25 12:05:38 PST ---
Created an attachment (id=23713)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=23713)
Input excel file
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org