CODEWITHSUNDEEP
phpphpexcel
user2992220
user2992220

Reputation: 1112

PHPExcel taking format of empty cells

In an excel sheet where I previousely formatted the whole first colum as a date, I changed afterwards the format of the used cells to General (cells A1 to A11). So from cell A12, this first column still has the previous date format. When I try to use the values of the first column (A1, A2, ..., A11) it identifies their format as a date, and gives a wrong result.

For example, this retruns a date format (d/mm/yyyy), which is not the case (see picture)

$sheet->getStyle('A3')->getNumberFormat()->getFormatCode();

Also the whole column has this format :

$sheet->getStyle('A')->getNumberFormat()->getFormatCode();

Is there a way to make sure the format of the cell itself is considered? (when I indicated the format of this cells explicitely, the right format was used)

After some investigation, found that this problem occurs only for the .xlsx files (using reader PHPExcel_Reader_Excel2007) and not for the .xls files (reader PHPExcel_Reader_Excel5).

enter image description here

Upvotes: 2

Views: 1038

Answers (2)

user2992220
user2992220

Reputation: 1112

After some more invetigation found a possible cause in reader Excel2007.php.

There is a problem with the condition When setting the style (line 933):

if ($c["s"] && !$this->_readDataOnly) {
  $cell->setXfIndex(isset($styles[intval($c["s"])]) ? intval($c["s"]) : 0);
}

In case of General cell format, $c["s"] is not defined, and the _xfIndex of the cell is not set (not passing the if statement). This gives (I didn't found why) a problem with the stored _xfIndex of the cell.

When removing the $c["s"] condition, the right style is set.

if (/*$c["s"] &&*/ !$this->_readDataOnly) {
  $cell->setXfIndex(isset($styles[intval($c["s"])]) ? intval($c["s"]) : 0);
}

Hoping there are no hidden consequences.

Upvotes: 0

elpiel
elpiel

Reputation: 161

You can set the whole row/col format:

$sheet->getStyle('A')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

Because you have changed only the format of A1 to A11, the rest are still with the old format. So setting format of the Whole Column/Row will fix this problem

Upvotes: 2

Related Questions