You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by shankarrao <sh...@gmail.com> on 2011/02/02 09:29:19 UTC
Issue with retrieving the existing values in a cell
Hi All,
I have a scenario wherein i need to set the values in particular cells of a
workbook dynamically. The attached sample java program demonstrates the
scenario. I am calling a method sendData() passing different values to the
cell to add the values in the cell.
The final output i am expecting in the cells is: A1=34399, B1=34399 and
C1=34399. Currently i am getting the values in these cells as A1=34399, B1=0
and C1=0 respectively. Could you please let me know where am i doing wrong?
Also i am unable to retrieve the values from the existing cells to which a
value is set. I am also getting the cell value as an empty string.
Please find the attahed zip file
http://apache-poi.1045710.n5.nabble.com/file/n3367484/Files.zip Files.zip
for the java file, input and ouput workbooks.
Please let me know what am i doing wrong which is leading me to getting
incorrect results.
Regards,
Shankar
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Issue-with-retrieving-the-existing-values-in-a-cell-tp3367484p3367484.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: Issue with retrieving the existing values in a cell
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
You can rely on the getCell() method to do what it says - that is to recover
a reference to a cell for you, what you are doing is assuming that you know
the type of the cell and so can call the getNumericCellValue() method for
example. If you do always know the type of the cell, then this is fine, but
I always think it is better to test the cells type and call the appropriate
method to recover it's contents. If you have a look here -
http://poi.apache.org/spreadsheet/quick-guide.html#CellContents - you will
see the idiom that many users adopt in order to recover the contents of a
cell. This allows for the fact that they may not know the type of the cell
they have in advance.
Adopting this approach allows you to protect the code against unforeseen
circumstances - you may be processing a sheet where a user has incorrectly
entered a String value into a cell that you are expecting a numeric value
from. Testing the type will result in your code receiving a String to
process but it will prevent it from throwing an exception when it tries to
call a method that is unsupported for a cell of that specific type.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Issue-with-retrieving-the-existing-values-in-a-cell-tp3367484p3370912.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: Issue with retrieving the existing values in a cell
Posted by shankarrao <sh...@gmail.com>.
Thanks for your inputs, Mark. I have implemented the solution as you
suggested.
However on similar lines, i want to clarify on using the method
CellUtil.getCell(sheet.getRow(row),col) to retrieve the existing Cell value
from the worksheet.
Could you please let me know if i can use this method without any concerns
so as to retrieve the existing value from a cell.
I believe we have the methods to retrieve the desired values as they are
stored in the excel sheet.
I mean to say retrieving String values using
CellUtil.getCell(sheet.getRow(row),col).getStringCellValue() and retrieving
the numeric values using
CellUtil.getCell(sheet.getRow(row),col).getNumericCellValue().
Please clarify.
Thanks for your help!
Shankar
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Issue-with-retrieving-the-existing-values-in-a-cell-tp3367484p3370689.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: Issue with retrieving the existing values in a cell
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Had the chance to look at the code you posted this morning and wanted to
mention the use of the CellUtil.createCell() method. A couple of times you
use this method and encapsulate a numeric value into a String object so that
it can be passed to this method. Now, I am no expert, but I think that the
createCell() method is setting the cell's type to text (general). Owing to
this. Excel will flag an error when it opens the file telling you that it
has found a number formatted as text. Rather than rely on the createCell()
method, it might be better to use just a couple of additional lines of code
and do the job yourself;
Cell cell = row.createCell(index);
cell.setCellValue(34399);
cell.setCellFormat(dbFormat);
where index contains the number of the column the cell should occupy.
The setCellValue() method is overloaded to accept paratemers of many
different types and it will set the cells type for you. So, the call I made
above to setCellVlaue(34399) will have set the cells type as numeric and
it's value to 34399, avoiding the error flagging when the file is opened
using Excel.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Issue-with-retrieving-the-existing-values-in-a-cell-tp3367484p3369138.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: Issue with retrieving the existing values in a cell
Posted by shankarrao <sh...@gmail.com>.
Got it working.
Mark, Thank you so much for your inputs.
Thank you!
Shankar
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Issue-with-retrieving-the-existing-values-in-a-cell-tp3367484p3369096.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: Issue with retrieving the existing values in a cell
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
To answer your second post/question first, you will get null for the cell
because the previous line of code - Row rowParameter = sheet.createRow(0); -
just created a new row and added that to the workbook. In this case, there
will be no pre-existing record for any of the cells and you will see a null
value returned of you try to access one. If the row and cell already exist
and you want to recover the value from the latter, then you will need to do
something like;
Row rowParameter = sheet.getRow(0);
Cell cell = rowParameter.getCell(0);
You can change that if you want to make the code a little more defensive;
Row rowParameter = sheet.getRow(0);
if(rowParameter != null) {
Cell cell = rowParameter.getCell(0);
if(cell != null) {
// Go on to do something with the cell
}
else {
// May not need this but you could always add something like this in
cell = row.createCell(0);
}
// And then set the cells value here as you know this code should
// never throw an NPE
cell.setValue( );
// etc
}
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Issue-with-retrieving-the-existing-values-in-a-cell-tp3367484p3367975.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: Issue with retrieving the existing values in a cell
Posted by shankarrao <sh...@gmail.com>.
To add to above comment, basically when putting a value into one of the cells
and when accessing it the next time, i am unable to retrieve the older
value.
When i use the below code to retrieve a cell value, i am getting the value
as null for the cells where a value has been set.
Row rowParameter = sheet.createRow(0);
Cell cell = rowParameter.getCell(0);
Please provide your inputs on where am i doing wrong.
I would like to retain the already added values in the Cells.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Issue-with-retrieving-the-existing-values-in-a-cell-tp3367484p3367697.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