Protecting cells sets empty numeric cells to zero

Topics: Developer Forum, User Forum
Nov 6, 2009 at 8:59 AM

I have a worksheet where I only want the user to be able to modify certain columns.

I protect the worksheet using;

	$sheet->getProtection()->setPassword('PHPExcel');
	$sheet->getProtection()->setSheet(true);

.. and then un-protect the 2 columns using;

	$highestRow = $sheet->getHighestRow();	
	$sheet->getStyle('I2:J' .$highestRow)->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
Any blank numeric cell types in the worksheet become zero.

I do not want them to be zero I need the cells to be blank / empty unless the user enters data in the cell like when the worksheet is not protected.

Is this a bug? I have done a manual test in EXCEL and when a numeric cell type is blank it remains blank, EXCEL does not convert it to zero.

Nov 6, 2009 at 9:07 AM

I am now not sure if this is related to the protecting of the cells or is something that has changed in the latest release??

Nov 6, 2009 at 9:51 AM

I have just confirmed it is not to do with the protection but is something that has changed between version 1.7 and 1.7.1

I tested using 1.7 and the cells are blank, with 1.7.1 the cells are zero.

 

Developer
Nov 6, 2009 at 9:52 PM

I am wondering what you mean by a "blank numeric cell type".

Do you have a complete example showing the problem?

Nov 9, 2009 at 11:51 AM

I meant a blank or empty cell which is formatted to numeric style.

With version 1.7 it works as expected, in 1.7.1 the cell is populated with zero.

Unfortunately I do not have an example, format a cell to numeric style and leave it blank, when opening in Excel the cell will have zero in it.

This was using the Excel 2007 writer.

Developer
Nov 10, 2009 at 2:39 AM

The only situation I can think of where there could be a change from PHPExcel 1.7.0 to 1.7.1 is in a situation like this.

Consider this line:

$worksheet->getCell('A1')->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NUMERIC);

In PHPExcel 1.7.0 you will probably see a blank cell when you open the generated Excel file, but in 1.7.1 you will see a 0.

What is happening here? When you use PHPExcel_Cell_DataType::TYPE_NUMERIC, the first argument is really expected to be a number. But '' is not a number, it is an empty string.

In PHPExcel 1.7.0 such code could result in corrupt Excel files (xlsx). Try this with PHPExcel 1.7.0 and you will see that you get a corrupt xlsx file:

$worksheet->getCell('A1')->setValueExplicit('abc', PHPExcel_Cell_DataType::TYPE_NUMERIC);

In PHPExcel 1.7.1 a mechanism was added to protect against such errors
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10600

That could explain why you get different results, but the above should explain why the approach of PHPExcel 1.7.1 is the correct.

To summarize, you should only use PHPExcel_Cell_DataType::TYPE_NUMERIC when you really want a number. A number cannot be blank. If you for some reason want a blank cell, use an empty string:

$worksheet->getCell('A1')->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING);

Note: Do not confuse data types and number formats. The above has to do with data types and not number formats.

Nov 10, 2009 at 8:16 AM

Hi Koyama,

Is there any way that I can use 1.7.1 to export like version 1.7 did when it comes across an empty TYPE:NUMERIC?

Thanks

 

Developer
Nov 10, 2009 at 8:30 AM
ale1981 wrote:

Hi Koyama,

Is there any way that I can use 1.7.1 to export like version 1.7 did when it comes across an empty TYPE:NUMERIC?

Thanks

 

No, because as soon as you set the value PHPExcel converts the empty string to a zero internally.

But why are you using TYPE:NUMERIC for the blank cells? If you can explain why this is necessary then we can better guide you to the correct solution.

Nov 10, 2009 at 8:39 AM

I have used PHPExcel to re-create an old program and it is basically to keep the spreadsheets looking the same, but if this is not possible then I would rather use the speed benefits of 1.7.1 over 1.7

Will the reader read the cells as blank or as zero, is there a difference when reading the cell?

 

 

Developer
Nov 10, 2009 at 8:58 AM
ale1981 wrote:

I have used PHPExcel to re-create an old program and it is basically to keep the spreadsheets looking the same, but if this is not possible then I would rather use the speed benefits of 1.7.1 over 1.7

Will the reader read the cells as blank or as zero, is there a difference when reading the cell?

There are no differences with the readers in PHPExcel 1.7.0 and 1.7.1. A plain read/write must never convert blank cells to numeric zeros.

Note that this issue only has to do with setValueExplicit(). Using setValue() may be more suitable in your case and you should not have the problem you describe.

Nov 10, 2009 at 9:01 AM

Hi Koyama,

I will try and use SetValue() and let you know the outcome.

Nov 10, 2009 at 10:21 AM
koyama wrote:
ale1981 wrote:

I have used PHPExcel to re-create an old program and it is basically to keep the spreadsheets looking the same, but if this is not possible then I would rather use the speed benefits of 1.7.1 over 1.7

Will the reader read the cells as blank or as zero, is there a difference when reading the cell?

There are no differences with the readers in PHPExcel 1.7.0 and 1.7.1. A plain read/write must never convert blank cells to numeric zeros.

Note that this issue only has to do with setValueExplicit(). Using setValue() may be more suitable in your case and you should not have the problem you describe.

 

I used setValue() only on the cells which were of type numeric and this worked, thanks for your help.