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 2011/02/01 08:53:49 UTC
DO NOT REPLY [Bug 50697] New: Time reading is not working properly
https://issues.apache.org/bugzilla/show_bug.cgi?id=50697
Summary: Time reading is not working properly
Product: POI
Version: 3.7
Platform: PC
OS/Version: Windows XP
Status: NEW
Keywords: APIBug
Severity: normal
Priority: P2
Component: POI Overall
AssignedTo: dev@poi.apache.org
ReportedBy: trainees@tecgemini.com
when iam trying to read time data from xls file using poi excel reader i get
the value something like "31-dec-1899".This is the problem what iam
facing.Expecting reply soon.Thanks in advance.
From
Renjith.R
--
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 50697] Problem with reading time field from excel
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=50697
trainees <tr...@tecgemini.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Summary|Time reading is not working |Problem with reading time
|properly |field from excel
--
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 50697] Time reading is not working properly
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=50697
--- Comment #2 from trainees <tr...@tecgemini.com> 2011-02-01 05:18:26 EST ---
Created an attachment (id=26586)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=26586)
sample data
There is field named as Time .The datas of that field we are trying to read
--
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 50697] Time reading is not working properly
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=50697
trainees <tr...@tecgemini.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|RESOLVED |REOPENED
Resolution|INVALID |
--- Comment #4 from trainees <tr...@tecgemini.com> 2011-02-01 05:26:41 EST ---
This is the function i am using. When it reads the 'time' field in the attached
excel, the data obtained is '31-Dec-1899' instead of actual data. How can we
get the actual data?
public void displayFromExcel(String xlsPath, String sheetName) {
System.out.println("In POIExcelReader :: method
===========displayFromExcel");
InputStream inputStream = null;
try {
inputStream = new FileInputStream(xlsPath);
} catch (FileNotFoundException e) {
System.out.println("File not found in the specified path.");
e.printStackTrace();
}
POIFSFileSystem fileSystem = null;
try {
fileSystem = new POIFSFileSystem(inputStream);
this.workBook = new HSSFWorkbook(fileSystem);
this. sheet = workBook.getSheetAt(0);
if (!sheetName.equals("")) {
sheet = workBook.getSheet(sheetName);
}
if(sheet != null){
Iterator<Row> rows = sheet.rowIterator();
//Iterator<HSSFRow> rows = sheet.rowIterator ();
this.excelData = new ArrayList<ArrayList>();
this.excelRows = sheet.getPhysicalNumberOfRows();
///
// System.out.println("the total number of rows are" + rows);
int cols = 0; // No of columns
int tmp = 0;
// This trick ensures that we get the data properly even if it
doesn't start from first few rows
for (int i = 0; i < 10 || i < this.excelRows; i++) {
Row erow = sheet.getRow(i);
// System.out.println("get cell "+erow.getCell(0));
if (erow != null) {
tmp = sheet.getRow(i).getPhysicalNumberOfCells();
//out.println("tmp value"+tmp);
if (tmp > cols) {
cols = tmp;
}
}
}
this.excelCols = cols;
this.excelColDataType = new ArrayList();
this.excelColDataSize = new ArrayList();
for (int c = 0; c < cols; c++) {
this.excelColDataType.add("Varchar");
this.excelColDataSize.add("0");
}
CommonFunctions commonFunctions = new CommonFunctions();
int testrow=0;
while (rows.hasNext()) {
//HSSFRow row = rows.next ();
Row row = rows.next();
ArrayList rowData = new ArrayList(cols);
// display row number in the console.
// System.out.println("Row No.: " + row.getRowNum());
// once get a row its time to iterate through cells.
//Iterator<HSSFCell> cells = row.cellIterator ();
Iterator<Cell> cells = row.cellIterator();
int c = 0;
while (cells.hasNext()) {
//HSSFCell cell = cells.next ();
Cell cell = cells.next();
// System.out.println("Cell No.: " +
cell.getColumnIndex());//.getCellNum ());
if (cell != null && cell.toString() !="")
{
if(cell.getCellType() ==Cell.CELL_TYPE_NUMERIC )//Lilu
added on 2010 Dec 9
{
rowData.add(String.valueOf((long)cell.getNumericCellValue()));
}
else
{
rowData.add(cell.toString());
}
//
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
break;
}
case Cell.CELL_TYPE_STRING: {
RichTextString richTextString =
cell.getRichStringCellValue();
// System.out.println("String value: " +
richTextString.getString());
break;
}
default: {
// types other than String and Numeric.
System.out.println("Type not supported.");
break;
}
}
} else {
rowData.add("");
}
c++;
}
// System.out.println("rowData "+rowData);
this.excelData.add(rowData);
}
}
// System.out.println("Excel Data Size read: " +
this.excelData.size() + "~~~~" + this.excelData);
// System.out.println("Excel Data Type size: " +
this.excelColDataType.size());
// System.out.println("Excel Data Type read: " +
this.excelColDataType);
// System.out.println("Excel Data Length read: " +
this.excelColDataSize);
} catch (IOException e) {
e.printStackTrace();
System.out.println("Error in POIExcelReader :: method
==============displayFromExcel=====>"+e.getMessage()+e.getCause());
}
}
--
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 50697] Problem with reading time field from excel
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=50697
--- Comment #6 from David Fisher <df...@jmlafferty.com> 2011-02-01 10:29:57 EST ---
Additionally Bugzilla is NOT the place to have a discussion about using the
API. The user list is the place.
--
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 50697] Time reading is not working properly
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=50697
--- Comment #3 from trainees <tr...@tecgemini.com> 2011-02-01 05:20:33 EST ---
Created an attachment (id=26587)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=26587)
Excel file which i am trying to read
please check the column titled 'time' in the attached 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
DO NOT REPLY [Bug 50697] Time reading is not working properly
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=50697
trainees <tr...@tecgemini.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |trainees@tecgemini.com
--
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 50697] Problem with reading time field from excel
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=50697
Nick Burch <ni...@alfresco.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|REOPENED |RESOLVED
Resolution| |INVALID
--- Comment #5 from Nick Burch <ni...@alfresco.com> 2011-02-01 06:37:43 EST ---
Just read the "time" cell as a number - it's fractions of a day so 12 hours is
stored as 0.5
Or, read it as a date, and just look at the time part
--
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 50697] Time reading is not working properly
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=50697
Nick Burch <ni...@alfresco.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution| |INVALID
--- Comment #1 from Nick Burch <ni...@alfresco.com> 2011-02-01 04:46:20 EST ---
Times and dates are stored the same, which is an offset from either 1900 or
1904. Either use time specific methods, or just fetch the number which is a
floating point number of days
--
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