You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Alex Phung <al...@intercax.com> on 2010/03/30 16:20:38 UTC
How can I programmically clear an Excel cell?
What is the best way to programmically clear a cell? Below is code that
will set cell to an empty string ("") if a value is null, but it will not
work with existing formulas. A better solution is to clear out the cell -
equivalent to "Right Click on cell and Clear Contents" or "Click Backspace
on a selected cell."
for (int i = 0; i < cellRefs.length; i++) {
Sheet worksheet =
workbook.getSheet(cellRefs[i].getSheetName());
Row row =
worksheet.getRow(cellRefs[i].getRow());
if (row == null) {
row =
worksheet.createRow(cellRefs[i].getRow());
}
Cell cell =
row.getCell(cellRefs[i].getCol());
if (cell == null) {
cell =
row.createCell(cellRefs[i].getCol());
}
Object val = cellValues.get(i);
if (val != null) {
if (val instanceof Double) {
cell.setCellValue(((Double) val).doubleValue());
} else {
cell.setCellValue(String.valueOf(val));
}
} else {
cell.setCellValue("");
}
modified = true;
}
RE: How can I programmically clear an Excel cell?
Posted by MSB <ma...@tiscali.co.uk>.
Thanks Alex, always worth knowing what works just in case anyone else asks a
similar question in the future.
Yours
Mark B
Alex Phung wrote:
>
> cell.setCellValue((String) null);
> cell.setCellType(Cell.CELL_TYPE_BLANK);
> row.removeCell(cell);
>
> all 3 above gives me the result I wanted (unless there are unexpected side
> effects). I am leaning toward using
> cell.setCellType(Cell.CELL_TYPE_BLANK)
> because it really what I want - a blank cell.
>
> -----Original Message-----
> From: MSB [mailto:markbrdsly@tiscali.co.uk]
> Sent: Tuesday, March 30, 2010 11:40 AM
> To: user@poi.apache.org
> Subject: RE: How can I programmically clear an Excel cell?
>
>
> It will be interesting to see what happens if you do delete either cell C2
> or
> D2 Alex. I am going to guess that Excel will behave as if the cell has not
> yet been initialised with a value and so return -3 but I do not know this
> for a fact and the #VALUE error is just as likely a consequence.
>
> If deleting the cell does not work, try changing it's type. Make it a
> blank
> cell - call the setCellType(int) method on the cell object and pass the
> constant Cell.CELL_TYPE_BLANK - if this is possible as I know there have
> been some issues surrounding type conversions, and see what happens then.
>
> Yours
>
> Mark B
>
>
> Alex Phung wrote:
>>
>> Thank you. I'll try what you suggested. I guess I shouldn't have
>> created
>> the cell in the first place. To answer your question:
>>
>> I have a simple formula (=C2-D2) in cell E2
>> If C2 has a value of 3 and D2 has a value of 3, then the formula in E2
>> evaluates to 0. If C2 is blank and D2 has a value of 3, the formula in
>> E2
>> evaluates to -3.
>>
>> However, if I use cell.setValue(""), the cell appears to be blank, but
>> the
>> formula tries to evaluate it as "" - 3 and it gives me a "#VALUE!"
>> error.
>>
>> -----Original Message-----
>> From: MSB [mailto:markbrdsly@tiscali.co.uk]
>> Sent: Tuesday, March 30, 2010 10:49 AM
>> To: user@poi.apache.org
>> Subject: Re: How can I programmically clear an Excel cell?
>>
>>
>> Can I ask what you mean when you say "it will not work with existing
>> formulas" please?
>>
>> Have you thought about simply deleting the cell from the row. I think
>> there
>> is a method with the following signature - removeCell(Cell) - that is
>> avalable for both the HSSFRow and XSSFRow classes. If the record for the
>> cell is removed then, when the workbook is opened using Excel, the user
>> will
>> simply see a blank cell.
>>
>> Yours
>>
>> Mark B
>>
>>
>> Alex Phung wrote:
>>>
>>> What is the best way to programmically clear a cell? Below is code that
>>> will set cell to an empty string ("") if a value is null, but it will
>>> not
>>> work with existing formulas. A better solution is to clear out the cell
>>> -
>>> equivalent to "Right Click on cell and Clear Contents" or "Click
>>> Backspace
>>> on a selected cell."
>>>
>>>
>>>
>>>
>>>
>>> for (int i = 0; i < cellRefs.length; i++) {
>>>
>>> Sheet worksheet =
>>> workbook.getSheet(cellRefs[i].getSheetName());
>>>
>>> Row row =
>>> worksheet.getRow(cellRefs[i].getRow());
>>>
>>> if (row == null) {
>>>
>>> row =
>>> worksheet.createRow(cellRefs[i].getRow());
>>>
>>> }
>>>
>>> Cell cell =
>>> row.getCell(cellRefs[i].getCol());
>>>
>>> if (cell == null) {
>>>
>>> cell =
>>> row.createCell(cellRefs[i].getCol());
>>>
>>> }
>>>
>>> Object val = cellValues.get(i);
>>>
>>> if (val != null) {
>>>
>>> if (val instanceof
>>> Double)
>>> {
>>>
>>>
>>> cell.setCellValue(((Double) val).doubleValue());
>>>
>>> } else {
>>>
>>>
>>> cell.setCellValue(String.valueOf(val));
>>>
>>> }
>>>
>>>
>>> } else {
>>>
>>> cell.setCellValue("");
>>>
>>> }
>>>
>>> modified = true;
>>>
>>> }
>>>
>>>
>>>
>>
>> --
>> View this message in context:
>>
> http://old.nabble.com/How-can-I-programmically-clear-an-Excel-cell--tp280832
>> 73p28083663.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
>>
>>
>>
>
> --
> View this message in context:
> http://old.nabble.com/How-can-I-programmically-clear-an-Excel-cell--tp280832
> 73p28084388.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
>
>
>
--
View this message in context: http://old.nabble.com/How-can-I-programmically-clear-an-Excel-cell--tp28083273p28085242.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: How can I programmically clear an Excel cell?
Posted by Alex Phung <al...@intercax.com>.
cell.setCellValue((String) null);
cell.setCellType(Cell.CELL_TYPE_BLANK);
row.removeCell(cell);
all 3 above gives me the result I wanted (unless there are unexpected side
effects). I am leaning toward using cell.setCellType(Cell.CELL_TYPE_BLANK)
because it really what I want - a blank cell.
-----Original Message-----
From: MSB [mailto:markbrdsly@tiscali.co.uk]
Sent: Tuesday, March 30, 2010 11:40 AM
To: user@poi.apache.org
Subject: RE: How can I programmically clear an Excel cell?
It will be interesting to see what happens if you do delete either cell C2
or
D2 Alex. I am going to guess that Excel will behave as if the cell has not
yet been initialised with a value and so return -3 but I do not know this
for a fact and the #VALUE error is just as likely a consequence.
If deleting the cell does not work, try changing it's type. Make it a blank
cell - call the setCellType(int) method on the cell object and pass the
constant Cell.CELL_TYPE_BLANK - if this is possible as I know there have
been some issues surrounding type conversions, and see what happens then.
Yours
Mark B
Alex Phung wrote:
>
> Thank you. I'll try what you suggested. I guess I shouldn't have created
> the cell in the first place. To answer your question:
>
> I have a simple formula (=C2-D2) in cell E2
> If C2 has a value of 3 and D2 has a value of 3, then the formula in E2
> evaluates to 0. If C2 is blank and D2 has a value of 3, the formula in E2
> evaluates to -3.
>
> However, if I use cell.setValue(""), the cell appears to be blank, but the
> formula tries to evaluate it as "" - 3 and it gives me a "#VALUE!"
> error.
>
> -----Original Message-----
> From: MSB [mailto:markbrdsly@tiscali.co.uk]
> Sent: Tuesday, March 30, 2010 10:49 AM
> To: user@poi.apache.org
> Subject: Re: How can I programmically clear an Excel cell?
>
>
> Can I ask what you mean when you say "it will not work with existing
> formulas" please?
>
> Have you thought about simply deleting the cell from the row. I think
> there
> is a method with the following signature - removeCell(Cell) - that is
> avalable for both the HSSFRow and XSSFRow classes. If the record for the
> cell is removed then, when the workbook is opened using Excel, the user
> will
> simply see a blank cell.
>
> Yours
>
> Mark B
>
>
> Alex Phung wrote:
>>
>> What is the best way to programmically clear a cell? Below is code that
>> will set cell to an empty string ("") if a value is null, but it will not
>> work with existing formulas. A better solution is to clear out the cell
>> -
>> equivalent to "Right Click on cell and Clear Contents" or "Click
>> Backspace
>> on a selected cell."
>>
>>
>>
>>
>>
>> for (int i = 0; i < cellRefs.length; i++) {
>>
>> Sheet worksheet =
>> workbook.getSheet(cellRefs[i].getSheetName());
>>
>> Row row =
>> worksheet.getRow(cellRefs[i].getRow());
>>
>> if (row == null) {
>>
>> row =
>> worksheet.createRow(cellRefs[i].getRow());
>>
>> }
>>
>> Cell cell =
>> row.getCell(cellRefs[i].getCol());
>>
>> if (cell == null) {
>>
>> cell =
>> row.createCell(cellRefs[i].getCol());
>>
>> }
>>
>> Object val = cellValues.get(i);
>>
>> if (val != null) {
>>
>> if (val instanceof
>> Double)
>> {
>>
>>
>> cell.setCellValue(((Double) val).doubleValue());
>>
>> } else {
>>
>>
>> cell.setCellValue(String.valueOf(val));
>>
>> }
>>
>>
>> } else {
>>
>> cell.setCellValue("");
>>
>> }
>>
>> modified = true;
>>
>> }
>>
>>
>>
>
> --
> View this message in context:
>
http://old.nabble.com/How-can-I-programmically-clear-an-Excel-cell--tp280832
> 73p28083663.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
>
>
>
--
View this message in context:
http://old.nabble.com/How-can-I-programmically-clear-an-Excel-cell--tp280832
73p28084388.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: How can I programmically clear an Excel cell?
Posted by MSB <ma...@tiscali.co.uk>.
It will be interesting to see what happens if you do delete either cell C2 or
D2 Alex. I am going to guess that Excel will behave as if the cell has not
yet been initialised with a value and so return -3 but I do not know this
for a fact and the #VALUE error is just as likely a consequence.
If deleting the cell does not work, try changing it's type. Make it a blank
cell - call the setCellType(int) method on the cell object and pass the
constant Cell.CELL_TYPE_BLANK - if this is possible as I know there have
been some issues surrounding type conversions, and see what happens then.
Yours
Mark B
Alex Phung wrote:
>
> Thank you. I'll try what you suggested. I guess I shouldn't have created
> the cell in the first place. To answer your question:
>
> I have a simple formula (=C2-D2) in cell E2
> If C2 has a value of 3 and D2 has a value of 3, then the formula in E2
> evaluates to 0. If C2 is blank and D2 has a value of 3, the formula in E2
> evaluates to -3.
>
> However, if I use cell.setValue(""), the cell appears to be blank, but the
> formula tries to evaluate it as "" - 3 and it gives me a "#VALUE!"
> error.
>
> -----Original Message-----
> From: MSB [mailto:markbrdsly@tiscali.co.uk]
> Sent: Tuesday, March 30, 2010 10:49 AM
> To: user@poi.apache.org
> Subject: Re: How can I programmically clear an Excel cell?
>
>
> Can I ask what you mean when you say "it will not work with existing
> formulas" please?
>
> Have you thought about simply deleting the cell from the row. I think
> there
> is a method with the following signature - removeCell(Cell) - that is
> avalable for both the HSSFRow and XSSFRow classes. If the record for the
> cell is removed then, when the workbook is opened using Excel, the user
> will
> simply see a blank cell.
>
> Yours
>
> Mark B
>
>
> Alex Phung wrote:
>>
>> What is the best way to programmically clear a cell? Below is code that
>> will set cell to an empty string ("") if a value is null, but it will not
>> work with existing formulas. A better solution is to clear out the cell
>> -
>> equivalent to "Right Click on cell and Clear Contents" or "Click
>> Backspace
>> on a selected cell."
>>
>>
>>
>>
>>
>> for (int i = 0; i < cellRefs.length; i++) {
>>
>> Sheet worksheet =
>> workbook.getSheet(cellRefs[i].getSheetName());
>>
>> Row row =
>> worksheet.getRow(cellRefs[i].getRow());
>>
>> if (row == null) {
>>
>> row =
>> worksheet.createRow(cellRefs[i].getRow());
>>
>> }
>>
>> Cell cell =
>> row.getCell(cellRefs[i].getCol());
>>
>> if (cell == null) {
>>
>> cell =
>> row.createCell(cellRefs[i].getCol());
>>
>> }
>>
>> Object val = cellValues.get(i);
>>
>> if (val != null) {
>>
>> if (val instanceof
>> Double)
>> {
>>
>>
>> cell.setCellValue(((Double) val).doubleValue());
>>
>> } else {
>>
>>
>> cell.setCellValue(String.valueOf(val));
>>
>> }
>>
>>
>> } else {
>>
>> cell.setCellValue("");
>>
>> }
>>
>> modified = true;
>>
>> }
>>
>>
>>
>
> --
> View this message in context:
> http://old.nabble.com/How-can-I-programmically-clear-an-Excel-cell--tp280832
> 73p28083663.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
>
>
>
--
View this message in context: http://old.nabble.com/How-can-I-programmically-clear-an-Excel-cell--tp28083273p28084388.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: How can I programmically clear an Excel cell?
Posted by Alex Phung <al...@intercax.com>.
Thank you. I'll try what you suggested. I guess I shouldn't have created
the cell in the first place. To answer your question:
I have a simple formula (=C2-D2) in cell E2
If C2 has a value of 3 and D2 has a value of 3, then the formula in E2
evaluates to 0. If C2 is blank and D2 has a value of 3, the formula in E2
evaluates to -3.
However, if I use cell.setValue(""), the cell appears to be blank, but the
formula tries to evaluate it as "" - 3 and it gives me a "#VALUE!"
error.
-----Original Message-----
From: MSB [mailto:markbrdsly@tiscali.co.uk]
Sent: Tuesday, March 30, 2010 10:49 AM
To: user@poi.apache.org
Subject: Re: How can I programmically clear an Excel cell?
Can I ask what you mean when you say "it will not work with existing
formulas" please?
Have you thought about simply deleting the cell from the row. I think there
is a method with the following signature - removeCell(Cell) - that is
avalable for both the HSSFRow and XSSFRow classes. If the record for the
cell is removed then, when the workbook is opened using Excel, the user will
simply see a blank cell.
Yours
Mark B
Alex Phung wrote:
>
> What is the best way to programmically clear a cell? Below is code that
> will set cell to an empty string ("") if a value is null, but it will not
> work with existing formulas. A better solution is to clear out the cell -
> equivalent to "Right Click on cell and Clear Contents" or "Click
> Backspace
> on a selected cell."
>
>
>
>
>
> for (int i = 0; i < cellRefs.length; i++) {
>
> Sheet worksheet =
> workbook.getSheet(cellRefs[i].getSheetName());
>
> Row row =
> worksheet.getRow(cellRefs[i].getRow());
>
> if (row == null) {
>
> row =
> worksheet.createRow(cellRefs[i].getRow());
>
> }
>
> Cell cell =
> row.getCell(cellRefs[i].getCol());
>
> if (cell == null) {
>
> cell =
> row.createCell(cellRefs[i].getCol());
>
> }
>
> Object val = cellValues.get(i);
>
> if (val != null) {
>
> if (val instanceof Double)
> {
>
>
> cell.setCellValue(((Double) val).doubleValue());
>
> } else {
>
>
> cell.setCellValue(String.valueOf(val));
>
> }
>
>
> } else {
>
> cell.setCellValue("");
>
> }
>
> modified = true;
>
> }
>
>
>
--
View this message in context:
http://old.nabble.com/How-can-I-programmically-clear-an-Excel-cell--tp280832
73p28083663.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: How can I programmically clear an Excel cell?
Posted by MSB <ma...@tiscali.co.uk>.
Can I ask what you mean when you say "it will not work with existing
formulas" please?
Have you thought about simply deleting the cell from the row. I think there
is a method with the following signature - removeCell(Cell) - that is
avalable for both the HSSFRow and XSSFRow classes. If the record for the
cell is removed then, when the workbook is opened using Excel, the user will
simply see a blank cell.
Yours
Mark B
Alex Phung wrote:
>
> What is the best way to programmically clear a cell? Below is code that
> will set cell to an empty string ("") if a value is null, but it will not
> work with existing formulas. A better solution is to clear out the cell -
> equivalent to "Right Click on cell and Clear Contents" or "Click
> Backspace
> on a selected cell."
>
>
>
>
>
> for (int i = 0; i < cellRefs.length; i++) {
>
> Sheet worksheet =
> workbook.getSheet(cellRefs[i].getSheetName());
>
> Row row =
> worksheet.getRow(cellRefs[i].getRow());
>
> if (row == null) {
>
> row =
> worksheet.createRow(cellRefs[i].getRow());
>
> }
>
> Cell cell =
> row.getCell(cellRefs[i].getCol());
>
> if (cell == null) {
>
> cell =
> row.createCell(cellRefs[i].getCol());
>
> }
>
> Object val = cellValues.get(i);
>
> if (val != null) {
>
> if (val instanceof Double)
> {
>
>
> cell.setCellValue(((Double) val).doubleValue());
>
> } else {
>
>
> cell.setCellValue(String.valueOf(val));
>
> }
>
>
> } else {
>
> cell.setCellValue("");
>
> }
>
> modified = true;
>
> }
>
>
>
--
View this message in context: http://old.nabble.com/How-can-I-programmically-clear-an-Excel-cell--tp28083273p28083663.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