You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Lo...@log-net.com on 2010/12/01 01:38:18 UTC
Re: Reading Excel sheet cell contents
Here is a snippet of what I use to process our spreadsheets (I think I got
it from a sample on here or the web site):
for (Row row : sheet) { //all rows in the sheet
if(row.getRowNum()==0)continue;
short minColIx = 0;
short maxColIx = row.getLastCellNum();
List<String> cells = new ArrayList<String>();
for(short colIx=minColIx; colIx<maxColIx; colIx++)
{
Cell cell = row.getCell(colIx);
cells.add(getCellContentsAsString(cell));
...
And the called function to get the contents (this is optional):
protected String getCellContentsAsString(Cell cell) throws
Exception {
String rc = "";
Object cellValue = getCellContents(cell);
if(cellValue==null)return "";
if (cellValue instanceof Integer) {
rc = ((Integer)cellValue).toString();
} else if (cellValue instanceof Double) {
rc = ((Double)cellValue).toString();
} else if (cellValue instanceof String) {
rc = (String)cellValue;
} else if (cellValue instanceof Boolean) {
rc = ((Boolean)cellValue).toString();
} else if (cellValue instanceof java.util.Date) {
rc = ((java.util.Date)cellValue).toString();
}
return rc;
}
And the function to get the contents (this is from one of the examples):
protected Object getCellContents(Cell cell) throws Exception {
Object cellValue = null;
switch(cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
cellValue =
cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(cell)) {
cellValue = cell.getDateCellValue();
} else {
cellValue = cell.getNumericCellValue();
}
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
cellValue = cell.getCellFormula();
break;
default:
cellValue = null;
}
return cellValue;
}
-Lou
Patssay <sa...@gmail.com> wrote on 11/30/2010 03:12:19 PM:
>
> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
Re: Reading Excel sheet cell contents
Posted by Lo...@log-net.com.
Craig:
Thanks - glad I could help. Currently, I have two processes - one that
creates a spreadsheet for a user and another that takes the spreadsheet
and loads it into our DB - so I don't do what you need. I see an example
of what you want at http://poi.apache.org/spreadsheet/how-to.html and
http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/hssf/usermodel/examples/HSSFReadWrite.java.
They are for hssf, but can be changed pretty easily.
-Lou
XSSF Mail <cz...@bellsouth.net> wrote on 11/30/2010 07:59:50 PM:
> Lou,
>
> Great example. If I wanted to manipulate the data and then update
> the existing spreadsheet, what approach would be necessary? Would I
> need to build a new sheet, read the sheet and all the necessary
> styles, indexes etc. or is there a facility available in poi for xssf.
>
> Regards,
> -Craig
> Sent from my iPhone
>
> On Nov 30, 2010, at 19:38, Louis.Masters@log-net.com wrote:
>
> > Here is a snippet of what I use to process our spreadsheets (I think I
got
> > it from a sample on here or the web site):
> >
> > for (Row row : sheet) { //all rows in the sheet
> > if(row.getRowNum()==0)continue;
> > short minColIx = 0;
> > short maxColIx = row.getLastCellNum();
> > List<String> cells = new ArrayList<String>();
> > for(short colIx=minColIx; colIx<maxColIx;
colIx++)
> > {
> > Cell cell = row.getCell(colIx);
> > cells.add(getCellContentsAsString(cell));
> > ...
> >
> >
> >
> >
> >
> > And the called function to get the contents (this is optional):
> >
> > protected String getCellContentsAsString(Cell cell) throws
> > Exception {
> > String rc = "";
> > Object cellValue = getCellContents(cell);
> > if(cellValue==null)return "";
> > if (cellValue instanceof Integer) {
> > rc = ((Integer)cellValue).toString();
> > } else if (cellValue instanceof Double) {
> > rc = ((Double)cellValue).toString();
> > } else if (cellValue instanceof String) {
> > rc = (String)cellValue;
> > } else if (cellValue instanceof Boolean) {
> > rc = ((Boolean)cellValue).toString();
> > } else if (cellValue instanceof java.util.Date) {
> > rc = ((java.util.Date)cellValue).toString();
> > }
> > return rc;
> > }
> >
> >
> > And the function to get the contents (this is from one of the
examples):
> >
> > protected Object getCellContents(Cell cell) throws Exception {
> > Object cellValue = null;
> >
> > switch(cell.getCellType()) {
> > case Cell.CELL_TYPE_STRING:
> > cellValue =
> > cell.getRichStringCellValue().getString();
> > break;
> > case Cell.CELL_TYPE_NUMERIC:
> > if(DateUtil.isCellDateFormatted(cell)) {
> > cellValue = cell.getDateCellValue();
> > } else {
> > cellValue = cell.getNumericCellValue();
> > }
> > break;
> > case Cell.CELL_TYPE_BOOLEAN:
> > cellValue = cell.getBooleanCellValue();
> > break;
> > case Cell.CELL_TYPE_FORMULA:
> > cellValue = cell.getCellFormula();
> > break;
> > default:
> > cellValue = null;
> > }
> >
> > return cellValue;
> >
> > }
> >
> >
> > -Lou
> >
> >
> >
> > Patssay <sa...@gmail.com> wrote on 11/30/2010 03:12:19 PM:
> >
> >>
> >> 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: user-unsubscribe@poi.apache.org
> >> For additional commands, e-mail: user-help@poi.apache.org
> >>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
Re: Reading Excel sheet cell contents
Posted by XSSF Mail <cz...@bellsouth.net>.
Lou,
Great example. If I wanted to manipulate the data and then update the existing spreadsheet, what approach would be necessary? Would I need to build a new sheet, read the sheet and all the necessary styles, indexes etc. or is there a facility available in poi for xssf.
Regards,
-Craig
Sent from my iPhone
On Nov 30, 2010, at 19:38, Louis.Masters@log-net.com wrote:
> Here is a snippet of what I use to process our spreadsheets (I think I got
> it from a sample on here or the web site):
>
> for (Row row : sheet) { //all rows in the sheet
> if(row.getRowNum()==0)continue;
> short minColIx = 0;
> short maxColIx = row.getLastCellNum();
> List<String> cells = new ArrayList<String>();
> for(short colIx=minColIx; colIx<maxColIx; colIx++)
> {
> Cell cell = row.getCell(colIx);
> cells.add(getCellContentsAsString(cell));
> ...
>
>
>
>
>
> And the called function to get the contents (this is optional):
>
> protected String getCellContentsAsString(Cell cell) throws
> Exception {
> String rc = "";
> Object cellValue = getCellContents(cell);
> if(cellValue==null)return "";
> if (cellValue instanceof Integer) {
> rc = ((Integer)cellValue).toString();
> } else if (cellValue instanceof Double) {
> rc = ((Double)cellValue).toString();
> } else if (cellValue instanceof String) {
> rc = (String)cellValue;
> } else if (cellValue instanceof Boolean) {
> rc = ((Boolean)cellValue).toString();
> } else if (cellValue instanceof java.util.Date) {
> rc = ((java.util.Date)cellValue).toString();
> }
> return rc;
> }
>
>
> And the function to get the contents (this is from one of the examples):
>
> protected Object getCellContents(Cell cell) throws Exception {
> Object cellValue = null;
>
> switch(cell.getCellType()) {
> case Cell.CELL_TYPE_STRING:
> cellValue =
> cell.getRichStringCellValue().getString();
> break;
> case Cell.CELL_TYPE_NUMERIC:
> if(DateUtil.isCellDateFormatted(cell)) {
> cellValue = cell.getDateCellValue();
> } else {
> cellValue = cell.getNumericCellValue();
> }
> break;
> case Cell.CELL_TYPE_BOOLEAN:
> cellValue = cell.getBooleanCellValue();
> break;
> case Cell.CELL_TYPE_FORMULA:
> cellValue = cell.getCellFormula();
> break;
> default:
> cellValue = null;
> }
>
> return cellValue;
>
> }
>
>
> -Lou
>
>
>
> Patssay <sa...@gmail.com> wrote on 11/30/2010 03:12:19 PM:
>
>>
>> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org