Here is a loop that I used to process all the cells."ds" is an internally used
twodimentional array of sorts.. but you get the picture..
FileInputStream fis;
try {
fis = new FileInputStream( location + "/" + fileName );
try{
Workbook currentWb;
try {
// create a workbook out of the input stream
if ( fileName.endsWith( "xlsx" ) ) {
currentWb = new XSSFWorkbook( fis );
}
else {
currentWb = new HSSFWorkbook( fis );
}
}
catch ( IOException e ) {
// do what ever
}
// get a reference to the worksheet
Sheet sheet = currentWb.getSheet( worksheet );
if ( sheet == null ) {
// do what ever..
}
int actualRowCount = sheet.getLastRowNum() + 1;
try{
for ( int actualRowNum = 0; actualRowNum < actualRowCount;
actualRowNum++ ) {
Row row = sheet.getRow( actualRowNum );
if ( row != null ) {
int lastCell = row.getLastCellNum();
try{
for ( int physicalCellNum = 0; physicalCellNum
< lastCell; physicalCellNum++ ) {
Cell cell = row.getCell(
(short)physicalCellNum );
if ( cell != null ) {
short actualColNum = (short)(
physicalCellNum + 1 );//cell.getCellNum();
if ( cell.getCellType() ==
Cell.CELL_TYPE_STRING ) {
String val =
cell.getStringCellValue();
ds.setString( actualRowNum,
DataFileUtil.getColumnName( actualColNum ), val );
}
else if ( cell.getCellType() ==
Cell.CELL_TYPE_NUMERIC ) {
String val = "";
if ( DateUtil.isCellDateFormatted(
cell ) ) {
Date date =
DateUtil.getJavaDate( cell.getNumericCellValue() );
Calendar cal =
Calendar.getInstance();
cal.setTime( date );
val =
DateFormatter.formatDateTime( cal );
}
else {
val = "" +
cell.getNumericCellValue();
}
ds.setString( actualRowNum,
DataFileUtil.getColumnName( actualColNum ), "" + val );
}
else if ( cell.getCellType() ==
Cell.CELL_TYPE_BLANK ) {
ds.setString( actualRowNum,
DataFileUtil.getColumnName( actualColNum ), "" );
}
else if ( cell.getCellType() ==
Cell.CELL_TYPE_BOOLEAN ) {
String val = Boolean.toString(
cell.getBooleanCellValue() );
ds.setString( actualRowNum,
DataFileUtil.getColumnName( actualColNum ), val );
}
}
else {
ds.setString( actualRowNum,
DataFileUtil.getColumnName( physicalCellNum + 1 ), "" );
}
}
}
catch ( Exception e1 ) {
// what ever
}
}
}
}
catch ( Exception e1 ) {
// what ever
}
}
finally{
fis.close();
}
}
catch ( IOException e ) {
//what ever
}
-----Original Message-----
From: Patssay [mailto:[email protected]]
Sent: Tuesday, November 30, 2010 3:12 PM
To: [email protected]
Subject: Reading Excel sheet cell contents
Hi,
I have used Apache POI to read excel sheet and the cell contents
Now it may happen that either or more of the cells in a row are empty.
>From what I read the cell iterator returns the null cells but while
iterating over it cells.next points to the next non-null field.
I want to dump the contents as they are in the sheet into a database
So basically a null field has to be null.
So I wrote a routine which would check the cell.getColumnNumber and the
count that I maintained to check whether it is indeed the right cell.
But cell.hasNext() used in the loop makes the pointer move to the next cell
and due to which the entire loop goes haywire.
Could someone help me with this?
I think I am trying to complicate matters and there is an easy way out.
Bunch of Thanks
:)
Patssay
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/Reading-Excel-sheet-cell-contents-tp3286664p3286664.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
The information in this e-mail is confidential and may contain legally
privileged information. It is intended solely for the person or entity to which
it is addressed. Access to this e-mail by anyone else is unauthorized. If you
are not the intended recipient, any disclosure, copying, distribution, action
taken, or action omitted to be taken in reliance on it, is prohibited and may
be unlawful. If you received this e-mail in error, please contact the sender
and delete the material from any computer.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
