reading locked cell

Topics: Developer Forum, User Forum
Dec 20, 2012 at 12:45 PM


     I'm running PHPExcel 1.7.7 from a Linux server which has a drive mounted through Samba on a windows file server. I normally don't have any sort of problems reading values from the excel spreadsheets, but one particular cell (and as fate would have it, it's a necessary information) refuses to be read, no matter what I do. The reason for that is that it's protection is set to locked, which makes the getValue and/or getFormattedValue always return null (even though worksheet and spreadsheet protection is turned off).

I've tried this:

echo $feuilleStub->getCellByColumnAndRow(4,5)->getValue();

Given that I have well over 300 files to process and that a spot-check revealed all of the files have that specific cell set to locked, I can't very well open each file up and remove the attribute by hand. So what am I supposed to do here?

Thanks in advance,


Dec 20, 2012 at 3:27 PM

Strange, because I do not believe PHPExcel checks that a cell is protected before to return or not the value.
A point that might explain why: (4.5) is not the cell E6 but the E5 (excerpt from the documentation: In PHPExcel column index is 0-based while row index is 1-based.) That means 'A1' ~ (0.1))
Dec 20, 2012 at 5:30 PM

Actually, I made a mistake when pasting the preceding code and forgot to include a line (sorry about that). Here's a more complete version of what I'm doing:

$fichier = $reader->load($dossier2->getPath() . "/" . $dossier2->getFilename());
$feuilleStub = $fichier->getSheetByName("Stub");
$feuilleStub->getStyle('E6')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED); echo $feuilleStub->getCellByColumnAndRow(4,5)->getValue(); //returns "Billing date", which is the title of the column echo $feuilleStub->getCellByColumnAndRow(4,6)->getValue(); //always null because cell is set to locked
The first line merely displays the title of the column (which works and merely served to confirm I'm actually referencing the right cell). The one that doesn't display anything is the second echo, the one with coordinates (4,6)... which does reference cell E6. Here's the beginning of the resulting var_dump I took when trying to access the cell (I'll spare you the 30000 lines following).

object(PHPExcel_Cell)#2095 (8) {
  string(1) "E"
  string(4) "null"
  object(PHPExcel_Worksheet)#2047 (39) {

........ snip .........

As you can see, the value is indeed null. So anyway, any idea what could be causing this problem?

Thanks in advance,


Dec 21, 2012 at 7:52 AM

Hmm, wait, you're saying that the workbook, the worksheet is not protected, but that the cell is locked... What exactly is a default workbook (all cells are locked). As a result, focus on this point is unlikely to bring the solution.
What does the cell in the original workbook? a simple number? a particular format? A formula? The workbook is made how (I had problems with workbooks created by server software)? xls? xlsx?
The reading of the workbook as PHPExcel is made by "load" as you write in the piece of code? (no ReadDataOnly, no filter on the sheets or cells,...).
If you open the workbook in Excel then you save it, does the workbook has the same problem?
I know that you have many workbooks, but already identify the problem.

("$fichier", "$dossier" ? Si mon anglais est incompréhensible nous avons probablement une autre langue commune)

Dec 21, 2012 at 10:54 AM

Faut croire qu'on est tous les deux francophones he he he ;). Anyway, pour répondre aux questions (je vais le faire en anglais pour en faire bénéficier le maximum de monde):

1) Your documentation specify three levels of protection. One on the file, one on the sheet and one on the cell. The only one that's defined is the one on the cell (the dreaded 'locked' attribute. Of course, in the software itself, it says that if sheet protection is activated, this should have no effect whatsoever, but apparently it has for PHPExcel.

2) The cell itself contains a date in custom format that displays them this way: 1 august 2009. Though I tried accessing different cells on the same line which are all String format and I can't get the value for the exact same reason.

3) I do believe the workbooks are automatically generated, but I don't know how exactly since I get them from a client website and they're rather protective of their infrastructure.

4) The file is in XLS format (which is correctly identified with Excel5 by PHPExcel).

5) In addition, I have specified a filter to avoid reading the entire file. I'm not currently at work though (and probably won't be today for holiday reasons he he he), so I can't remember what I set... but if I set the number of lines to be read to 5, I swear I'm gonna jump off a bridge ;). I'll keep you posted as soon as I get back to work.

6) I haven't tried to open and save the workbook to see if the problem persisted.

Anyway, that's about all I can say right now, considering I don't have my source code at home. Still, there's a chance I might get into work today. If I do, you can be certain that the first thing I'll check is that damn filter on the number of lines I'm actually reading! I swear, the more I think about it, the more I think that's where the problem lies.

Anyway, Joyeux Noel mister francophone ;).


Dec 21, 2012 at 12:50 PM

1) If you create a new workbook with Excel, you have exactly this pattern: the workbook is not protected, the sheets are not protected, all cells are locked. As a result, if this would create a problem for PHPExcel, lot of users complain of inability to read a workbook.
(2) If you have not already play with dates in Excel, this maybe sports (see another discussion on this topic), but at least, you should be able to get a number, and not the value null.
(5) It is tempting to call into question the filter, indeed ;-)

Joyeux Noël à toi, Merry Christmas to all, with a special thought to Mark that does a great job.
Dec 21, 2012 at 3:59 PM

Okay, you're officially talking to my ghost right now as I jumped off a bridge like I said I would, the reason being this line here:

$filterSubset = new MyReadFilter(1, 5, range('A', 'E'));

I don't think I need to tell what exactly this does, other than make me feel like bumping my head against the wall. Curiously, when changed to this:

$filterSubset = new MyReadFilter(1, 10, range('A', 'Z'));

The desired value started appearing like it should have from the start.

On se sent tellement intelligents dans ces situations là ;).

Noyeux Joël et désolé du dérangement he he he.