You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Rajesh Mikkilineni <Ra...@biogenidec.com> on 2006/05/25 14:16:01 UTC
Read Date Cell using HSSF Event model.
Hi All,
As my excel sheet have more than 40000 records I am using HSSF event
model. I am able to read test, numeric cell without any issue. The cell
with date values are notified as NumberRecord.sid, is there any way to
distinguish between numeric cell and date cell. Could any one help me
reading the date cell using HSSF event models.
Thanks for your time.
With Regards,
Rajesh MIkkilineni
617 679 3616
RE: Read Date Cell using HSSF Event model.
Posted by Rajesh Mikkilineni <Ra...@biogenidec.com>.
Thanks for you solution, but I am using even model and I not sure how to
get HSSFCell from NumberRecord. Is there any way to retrieve HSSFCell from
NumberRecord?
case NumberRecord.sid:
NumberRecord numrec = (NumberRecord) record;
System.out.println("Cell found with value " + numrec.getValue()
+ " at row " + numrec.getRow() + " and column "
+
numrec.getColumn() + " - " +
numrec.isInValueSection() + " - " +
numrec.isValue()
+ " - " +
org.apache.poi.hssf.usermodel.HSSFDateUtil.isValidExcelDate(numrec.getValue())
);
if(numrec.getColumn() == 4)
{
java.text.SimpleDateFormat formatter = new
java.text.SimpleDateFormat("dd-MMM-yyyy");
Date date = HSSFDateUtil.getJavaDate(numrec.getValue());
System.out.println("Date" + date );
}
break;
With Regards,
Rajesh MIkkilineni
617 679 3616
"Birendar Waldiya" <bw...@cisco.com>
25-May-2006 08:30 AM
Please respond to
"POI Users List" <po...@jakarta.apache.org>
Message Size: 11.4 KB
To
"'POI Users List'" <po...@jakarta.apache.org>
cc
Subject
RE: Read Date Cell using HSSF Event model.
Hope you are using :
if(HSSFDateUtil.isCellDateFormatted(cell)){
// if(HSSFDateUtil.isValidExcelDate(cell.getNumericCellValue())
){ //.isCellDateFormatted(cell)){
// if(HSSFDateUtil.isCellDateFormatted(cell)){
// HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
java.text.SimpleDateFormat formatter = new
java.text.SimpleDateFormat("dd-MMM-yyyy");
//result = formatter.format(cell.getDateCellValue());
result =
formatter.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}else{
result ="Number: "+
String.valueOf(cell.getNumericCellValue());
}
The date format that excel recognose are limited
Rest of the date format are intrepreted as nunber ie
Nuber of days from
1 jan 1900,
There are two solutions for this
One format your date in any one of the for mat that does not have @ or $
This can be view from format --> customs you will see what format your
date is
Chose nay of the date format that does not have @ $ in them
you will get it as date
2nd is :
Make a function that converts nuber to date
/*
* Created on May 18, 2006
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/
package excel;
/**
* @author bwaldiya
*
* TODO To change the template for this generated type comment go to
* Window - Preferences - Java - Code Style - Code Templates
*/
import java.util.*;
import java.text.*;
public class ConvertNumberToDate {
/**
Algorith 1
1. Identify the year as leap or not , Jan 1 ,1900 Leap
Year for
Excel
2. Pick up the devisor as 366 for leap year and 365 for non leap year
3. Find the number of years passed and store the number.
4. Find the number of days after the complete years
5. Identify Month if its 1,3
**/
public static void main(String[] args) throws Exception{
String dt =
ConvertNumberToDate.getDateFromNumber("3833");
System.out.println(dt);
}
public static String getDateFromNumber(String args)
throws Exception
{
int number = new Integer(args).intValue()
; // ;38353;
int newNumber = 0;
String dateFormat1 ="dd/mm/yyyy";
String dateFormat2 ="mm/dd/yyyy";
String dateFormat3 ="dd/mon/yyyy";
int day_years1 =366; //leap year
int day_years2 =365; //non leap year
int day_month31=31;
int day_month28=28;
int day_month29=29;
int day_month30=30;
int feb=0;
boolean check = true ;
int years = 0 ;
int months = 1 ;
int days = 1 ;
int local = 0 ;
String Fmt1Month [] ={
"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"};
String Fmt2Month [] ={
"0","1","2","3","4","5","6","7","8","9","10","11"};
/* CHECK HOW MANY YEARS ARE
* THERE AFTER JAN 1 1900, IS DAY 0
*/
while(check){
int counter = 0;
if( years%4==0){
if(number-day_years1>=0){
number =
number-day_years1 ;
years ++;
}
local = day_years1;
}
if( years%4!=0){
if(number-day_years2>=0){
number
= number-day_years2 ;
years ++;
}
local = day_years2;
}
//check the days left in a year
if(number <= local ){
days = number ;
check = false;
break;
}
}
/** Check the days left after
* the years have been extracted
* logic here
*/
if(years%4==0){
feb = day_month29;
for(int k = 1 ;k <13 ;k
++){
switch(k){
case 1 : ; //jan
case 3 : ; //mar
case 5 : ; //may
case 7 : ; //jul
case 8 : ; //aug
case 10 : ; //oct
case 12:
if(days - day_month31 >0) {
days
= days - day_month31;
months ++;
}
break
;
case 2 :
if( days
- feb >0) {
days= days - feb;
months ++;
}
break;
case 4 :;
case 6 :;
case 9 :;
case 11:
if(days
- day_month30>0){
days= days - day_month30;
months
++;
}
break
;
} //END SWITCH
}
}
else{
feb = day_month28;
System.out.println("is
NOT LEAP year :");
for(int k = 0 ;k <12 ;k
++){
switch(k){
case 1 : ; //jan
case 3 : ; //mar
case 5 : ; //may
case 7 : ; //jul
case 8 : ; //aug
case 10 : ; //oct
case 12:
if(days - day_month31>0){
days=
days - day_month31;
months
++;
}
break
;
case 2 :
if(days
- feb>0){
days= days - feb;
months ++;
}
break;
case 4 : ;
case 6 : ;
case 9 : ;
case 11:
if(days
- day_month30 >0){
days=
days - day_month30;
months
++;
}else
break
;
}
}
}
years = 1900 +years; //very important
System.out.println("YEAR :" +years);
System.out.println("MONTH :"+months);
System.out.println("DAY :"+days);
System.out.println(days+"-"+Fmt1Month[months-1]+"-"+years);
//Calendar rightNow =
Calendar.getInstance();
//rightNow.set(years,months,days);
// SimpleDateFormat formatter = new
SimpleDateFormat("dd-MMM-yyyy");
// String dt = formatter.format(new
Date(days+"-"+Fmt1Month[months-1]+"-"+years));
//DateFormat df =
DateFormat.getDateInstance();
// Date date = df.parse("07/10/96 4:5 PM,
PDT") ;
//Date date =
df.parse("\""+days+"/"+months+"/"+years+"\"")
;
//System.out.println(date);
return days+"-"+Fmt1Month[months-1]+"-"+years;
}
}
-----Original Message-----
From: Rajesh Mikkilineni [mailto:Rajesh.Mikkilineni@biogenidec.com]
Sent: Thursday, May 25, 2006 5:46 PM
To: POI Users List
Subject: Read Date Cell using HSSF Event model.
Hi All,
As my excel sheet have more than 40000 records I am using HSSF event
model.
I am able to read test, numeric cell without any issue. The cell with date
values are notified as NumberRecord.sid, is there any way to distinguish
between numeric cell and date cell. Could any one help me reading the
date
cell using HSSF event models.
Thanks for your time.
With Regards,
Rajesh MIkkilineni
617 679 3616
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: Read Date Cell using HSSF Event model.
Posted by Birendar Waldiya <bw...@cisco.com>.
Hope you are using :
if(HSSFDateUtil.isCellDateFormatted(cell)){
// if(HSSFDateUtil.isValidExcelDate(cell.getNumericCellValue())
){ //.isCellDateFormatted(cell)){
// if(HSSFDateUtil.isCellDateFormatted(cell)){
// HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
java.text.SimpleDateFormat formatter = new
java.text.SimpleDateFormat("dd-MMM-yyyy");
//result = formatter.format(cell.getDateCellValue());
result =
formatter.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}else{
result ="Number: "+
String.valueOf(cell.getNumericCellValue());
}
The date format that excel recognose are limited
Rest of the date format are intrepreted as nunber ie
Nuber of days from
1 jan 1900,
There are two solutions for this
One format your date in any one of the for mat that does not have @ or $
This can be view from format --> customs you will see what format your
date is
Chose nay of the date format that does not have @ $ in them
you will get it as date
2nd is :
Make a function that converts nuber to date
/*
* Created on May 18, 2006
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/
package excel;
/**
* @author bwaldiya
*
* TODO To change the template for this generated type comment go to
* Window - Preferences - Java - Code Style - Code Templates
*/
import java.util.*;
import java.text.*;
public class ConvertNumberToDate {
/**
Algorith 1
1. Identify the year as leap or not , Jan 1 ,1900 Leap Year for
Excel
2. Pick up the devisor as 366 for leap year and 365 for non leap year
3. Find the number of years passed and store the number.
4. Find the number of days after the complete years
5. Identify Month if its 1,3
**/
public static void main(String[] args) throws Exception{
String dt = ConvertNumberToDate.getDateFromNumber("3833");
System.out.println(dt);
}
public static String getDateFromNumber(String args) throws Exception
{
int number = new Integer(args).intValue() ; // ;38353;
int newNumber = 0;
String dateFormat1 ="dd/mm/yyyy";
String dateFormat2 ="mm/dd/yyyy";
String dateFormat3 ="dd/mon/yyyy";
int day_years1 =366; //leap year
int day_years2 =365; //non leap year
int day_month31=31;
int day_month28=28;
int day_month29=29;
int day_month30=30;
int feb=0;
boolean check = true ;
int years = 0 ;
int months = 1 ;
int days = 1 ;
int local = 0 ;
String Fmt1Month [] ={
"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"};
String Fmt2Month [] ={
"0","1","2","3","4","5","6","7","8","9","10","11"};
/* CHECK HOW MANY YEARS ARE
* THERE AFTER JAN 1 1900, IS DAY 0
*/
while(check){
int counter = 0;
if( years%4==0){
if(number-day_years1>=0){
number = number-day_years1 ;
years ++;
}
local = day_years1;
}
if( years%4!=0){
if(number-day_years2>=0){
number = number-day_years2 ;
years ++;
}
local = day_years2;
}
//check the days left in a year
if(number <= local ){
days = number ;
check = false;
break;
}
}
/** Check the days left after
* the years have been extracted
* logic here
*/
if(years%4==0){
feb = day_month29;
for(int k = 1 ;k <13 ;k ++){
switch(k){
case 1 : ; //jan
case 3 : ; //mar
case 5 : ; //may
case 7 : ; //jul
case 8 : ; //aug
case 10 : ; //oct
case 12:
if(days - day_month31 >0) {
days = days - day_month31;
months ++;
}
break ;
case 2 :
if( days - feb >0) {
days= days - feb;
months ++;
}
break;
case 4 :;
case 6 :;
case 9 :;
case 11:
if(days - day_month30>0){
days= days - day_month30;
months ++;
}
break ;
} //END SWITCH
}
}
else{
feb = day_month28;
System.out.println("is NOT LEAP year :");
for(int k = 0 ;k <12 ;k ++){
switch(k){
case 1 : ; //jan
case 3 : ; //mar
case 5 : ; //may
case 7 : ; //jul
case 8 : ; //aug
case 10 : ; //oct
case 12:
if(days - day_month31>0){
days= days - day_month31;
months ++;
}
break ;
case 2 :
if(days - feb>0){
days= days - feb;
months ++;
}
break;
case 4 : ;
case 6 : ;
case 9 : ;
case 11:
if(days - day_month30 >0){
days= days - day_month30;
months ++;
}else
break ;
}
}
}
years = 1900 +years; //very important
System.out.println("YEAR :" +years);
System.out.println("MONTH :"+months);
System.out.println("DAY :"+days);
System.out.println(days+"-"+Fmt1Month[months-1]+"-"+years);
//Calendar rightNow = Calendar.getInstance();
//rightNow.set(years,months,days);
// SimpleDateFormat formatter = new
SimpleDateFormat("dd-MMM-yyyy");
// String dt = formatter.format(new
Date(days+"-"+Fmt1Month[months-1]+"-"+years));
//DateFormat df = DateFormat.getDateInstance();
// Date date = df.parse("07/10/96 4:5 PM, PDT") ;
//Date date = df.parse("\""+days+"/"+months+"/"+years+"\"")
;
//System.out.println(date);
return days+"-"+Fmt1Month[months-1]+"-"+years;
}
}
-----Original Message-----
From: Rajesh Mikkilineni [mailto:Rajesh.Mikkilineni@biogenidec.com]
Sent: Thursday, May 25, 2006 5:46 PM
To: POI Users List
Subject: Read Date Cell using HSSF Event model.
Hi All,
As my excel sheet have more than 40000 records I am using HSSF event model.
I am able to read test, numeric cell without any issue. The cell with date
values are notified as NumberRecord.sid, is there any way to distinguish
between numeric cell and date cell. Could any one help me reading the date
cell using HSSF event models.
Thanks for your time.
With Regards,
Rajesh MIkkilineni
617 679 3616
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/