Which version of POI are you using? I used the 2.5 release.
If you can send me a sample sheet I can check it out. There doesn't seem to be
anything in the code that should throw an NPE.
-Rahul
--- ORIGINAL MESSAGE ---
From:[EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject:Re: Reading excel files (was RE:RE:Different dates Formats and Decimals)
Date: 04/15/2004 06:23:36 AM
Rahul,
Thanks for sending the code. Went through it. I am also doing the same as you.
But in my Excel Sheet the data is as follows.
|----------+------------+----------+--------+---------+------+----------+-----+--------+--------+--------------+-----+----------+------|
|TRADE_DATE|SETTLE_DATE |MAT_DATE |PAR_AMT
|AE_NUMBER|CREDIT|COMMISSION|PRICE|CURRENCY|ACCT_NUM|TRADE_ACCT_NUM|CUSIP|TRADE_TYPE|SOURCE|
|----------+------------+----------+--------+---------+------+----------+-----+--------+--------+--------------+-----+----------+------|
|14-04-2004| 14-04-2004|14-04-2004| 1000|BALAJI | 0| 1000| 10|EUR
|ABCD |TRADE |XYZ | |EE |
|----------+------------+----------+--------+---------+------+----------+-----+--------+--------+--------------+-----+----------+------|
|14-04-2004| 14-04-2004|14-04-2004| 1000|BALAJI | 0| 1000| 10|EUR
|ABCD |TRADE |XYZ | |EE |
|----------+------------+----------+--------+---------+------+----------+-----+--------+--------+--------------+-----+----------+------|
The Trade Type column is of General Type. When i try to find the no of columns
in the Second Rows using the function getLastCellNum() , it throws a null pointer
exception and comes out of the Program. Initially i was using the
getPhysicalNumberOfCells(); function.That also was returning the same error. The work
around is change the General Type to Text then it works fine. Any clue about this.
When you find time , please checkout the link, it says there is a bug in the function
http://www.mail-archive.com/[EMAIL PROTECTED]/msg03882.html
My Code snippet is as follows.
for (int k = 0; k < wb.getNumberOfSheets(); k++)
{
System.out.println("Sheet " + k);
HSSFSheet sheet = wb.getSheetAt(k);
int rows = sheet.getPhysicalNumberOfRows();
for (int r = 1; r < rows; r++)
{
HSSFRow row = sheet.getRow(r);
//int cells = row.getPhysicalNumberOfCells();
int cells = row.getLastCellNum();
System.out.println("ROW " + row.getRowNum());
System.out.println("Cells " + cells);
for (int c = 0; c < 13; c++)
{
HSSFCell cell = row.getCell((short) c);
String value = null;
switch (cell.getCellType())
{
case HSSFCell.CELL_TYPE_FORMULA :
value = "FORMULA ";
break;
case HSSFCell.CELL_TYPE_STRING :
value = "STRING value=" +
cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC :
value = "NUMERIC value=" +
cell.getNumericCellValue();
break;
} //cell
System.out.println("CELL col="+ cell.getCellNum()+
" VALUE=" + value);
pstmt.setDate(1,new
java.sql.Date(row.getCell((short) 0).getDateCellValue().getTime())); //trade_date
pstmt.setDate(2,new
java.sql.Date(row.getCell((short) 1).getDateCellValue().getTime())); //settle_date
pstmt.setDate(3,new
java.sql.Date(row.getCell((short) 2).getDateCellValue().getTime())); //mat_date
pstmt.setDouble(4, row.getCell((short)
3).getNumericCellValue()); //par_amt
pstmt.setString(5,row.getCell((short)
4).getStringCellValue()); //ae_number
pstmt.setDouble(6,(int) row.getCell((short)
5).getNumericCellValue()); //credit
pstmt.setDouble(7,(int) row.getCell((short)
6).getNumericCellValue()); //commission
pstmt.setDouble(8,(int) row.getCell((short)
7).getNumericCellValue()); //price
pstmt.setString(9,row.getCell((short)
8).getStringCellValue()); //currency
pstmt.setString(10,row.getCell((short)
9).getStringCellValue()); //acct_num
pstmt.setString(11,row.getCell((short)
10).getStringCellValue()); //trade_acct_num
pstmt.setString(12,row.getCell((short)
11).getStringCellValue());//cusip
//pstmt.setString(13,row.getCell((short)
12).getStringCellValue());//trade_type
pstmt.setString(13," ");
pstmt.setString(14,row.getCell((short)
13).getStringCellValue());//source
pstmt.setString(15,"B");//process_flag
pstmt.setString(16,"A");//status_flag
pstmt.setString(17,"B");//credit_status
pstmt.setDate(18,new
java.sql.Date(System.currentTimeMillis()));//in_z
} //Columns
pstmt.executeUpdate();
con.commit();
}//Rows
}}// Sheets
I have tried the cell iterator method also. It scans the columns reverse. Just wanted
to know how were you doing.
Thanks for spending the time for me.
Regards,
Balaji.M,
Institutional Client Group(IT),
Deutsche Bank [/], London,
Office : +44(0)20754 55426
[EMAIL PROTECTED]
om To: Balaji Muniraja/DMGGM/DMG
UK/[EMAIL PROTECTED] UK
cc: [EMAIL PROTECTED]
04/14/2004 08:06 Subject: Reading excel files (was
RE:RE:Different dates Formats and Decimals)
PM
Please respond to
"POI Users List"
Hello Balaji,
Attached is a sample java programs that reads an excel file and echoes the value to
the System.out. I am attaching a sample excel file also that you can use to test this
class.
Please change the location of the excel file before compiling and runing it.
You would obviously need to compile this and run it after setting the classpath etc.
This works with version 2.5 of POI. The distribution I used is downloaded from
http://apache.mirrors.pair.com/jakarta/poi/release/bin/
Hope this helps you to do what you are trying.
POI developers,if you think it helps people to understand reading of excel files using
the POI library please consider making this part of the POI documentation. If I can
help in this regard I would be glad to do that.
Best Regards,
Rahul Phadnis
--- ORIGINAL MESSAGE ---
From:[EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject:RE:Different dates Formats and Decimals
Date: 04/14/2004 09:10:03 AM
Hi,
Do you have any sample code for reading like that and let me know how do i read
a empty cell as a text value ?
Regards,
Balaji.M,
Institutional Client Group(IT),
Deutsche Bank [/], London,
Office : +44(0)20754 55426
[EMAIL PROTECTED]
om To: Balaji Muniraja/DMGGM/DMG
UK/[EMAIL PROTECTED] UK
cc: [EMAIL PROTECTED]
04/14/2004 04:56 Subject: RE:Different dates Formats
and Decimals
PM
Hello,
You can extract the values from excel sheet. Then based on the column type apply
formating to the value viz converting dates to dd-mm-yyyy etc. The formatting can be
done using the java.text api. After applying necessary formatting these values can
then be inserted into the database.
-Rahul Phadnis
--- ORIGINAL MESSAGE ---
From:[EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject:Different dates Formats and Decimals
Date: 04/14/2004 08:37:53 AM
Hi ,
I am newbie to POI.
I have a requirement, where i need to read each row in the Excel File and upload
those rows in to the Oracle Database. I am using the following code to do that. But
before reading the Excel file using the Java Programs, i need to 1) Format the Date
Columns to (CUSTOM-> dd-mm-yyyy) , number colums to Number and no decimal places and
there are cases where columns in the Excel will be empty (it will be in General
Format). If i convert it to Text and run the program then it will work properly.
What i wanted to know is , Is there any way
1) To read without modifying the format for the Date values in the Excel Sheet.
2) If the Excel Column is of General Type and try to read a String Value
successfully.
Many thanks in Advance.
Regards,
Balaji.M,
Institutional Client Group(IT),
Deutsche Bank [/], London,
Office : +44(0)20754 55426
--
This e-mail may contain confidential and/or privileged information. If you are not the
intended recipient (or have received this e-mail in error) please notify the sender
immediately and destroy this e-mail. Any unauthorized copying, disclosure or
distribution of the material in this e-mail is strictly forbidden.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
All the kung fu in the world isn't any help when it comes to women. - Jet Li
--
This e-mail may contain confidential and/or privileged information. If you are not the
intended recipient (or have received this e-mail in error) please notify the sender
immediately and destroy this e-mail. Any unauthorized copying, disclosure or
distribution of the material in this e-mail is strictly forbidden.
It's a recession when your neighbor loses his job; it's a depression when you lose
yours - Harry S. Truman
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--
This e-mail may contain confidential and/or privileged information. If you are not the
intended recipient (or have received this e-mail in error) please notify the sender
immediately and destroy this e-mail. Any unauthorized copying, disclosure or
distribution of the material in this e-mail is strictly forbidden.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
I believe in God, only I spell it Nature. - Frank Lloyd Wright
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
