Cell format corrupted after insert

Topics: Developer Forum, User Forum
Jun 27, 2014 at 8:40 AM
Edited Jun 27, 2014 at 9:00 AM
Hi guys.

I have to say that Im very new to phpexcel.
First of all here is my simple code:
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("exl/temp.xlsx");

$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Juni.2014');
B1 gets "Juni.2014".
A1 is saved as "=B1" in my template with format "date.month".
A2 =A1+1
A3 =A1+2 and so on.

What should happen: The script inserts "Juni.2014" in B1 and the list under column A will get filled with every day of the inserted month (1.Jun. / 2.Jun. / 3.Jun).

But what really happens: When I insert Juni.2014 into B1, the cell A1 (=B1) shows up "Juni.2014" too. The saved format "day.month." gets corrupted.

When I open the result.xlsx excel shows up the right format for the cell A1, but its not working.
Even if I change the format to something else it wont change anything.

The script isnt even touching the cell A1, so why the hell isnt this working?
I hope this was clear enough.

Looking forward for your help.
Coordinator
Jun 27, 2014 at 9:47 AM
"Juni.2014" is nothing more than a string to PHPExcel, so it won't do anything clever at all.... what you say should happen requires a degree of intelligence, it need PHPExcel to recognise "Juni.2014" as a month and year with a dot separating them, and to automatically convert that to a MS Excel serialized datetime stamp..... it requires me as a developer to program that intelligence into PHPExcel, in 14 different languages..... and I'm afraid that neither PHPExcel nor I are that smart.

But PHPExcel is an open source project.... you're clearly familiar with languages that I don't use, so there's nothing to stop you recoding PHPExcel's date recognition rules so that it will recognise "Juni.2014" as a date and convert it to a MS Excel serialized datetime stamp and set a number format mask accordingly.

However, Neither PHPExcel nor myself are totally stupid, and we've provided you with a hook that will allow PHPExcel to do this for you, by creating a custom cell value binder. Looking at the code of the advanced value binder might give you an idea of how to achieve this.
Jun 27, 2014 at 10:24 AM
Edited Jun 27, 2014 at 10:53 AM
Wow. Looks like you feel offended by my question. Theres no need to notice phpexcel isnt a 'totally' stupid thing.
I already said Im new to this. So please come down a bit.

Back to topic:
Alright. Applying a straight date format to the cell should fix this shouldnt it?
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("exl/temp.xlsx");

echo date('H:i:s') . " Add new data to the template<br>";

$objPHPExcel->getActiveSheet()->setCellValue('B1', '01.06.2014');
$objPHPExcel->getActiveSheet()->getStyle('B1')->getNumberFormat()->setFormatCode('MMM. JJ');

echo date('H:i:s') . " Write to Excel2007 format<br>";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('exl/test.xlsx');
I also tried to set the cell format before inserting the value. Both wont work. In excel there is no format applied to the cell.
Coordinator
Jun 27, 2014 at 11:10 AM
'01.06.2014' is a string, and will be treated no differently to any other string in PHPExcel: simply setting a number format mask won't magically change the datatype to a number. A string with a numeric format mask is still a string, whether in MS Excel or in PHPExcel.

If you want to work with dates, you need to convert them to MS Excel serialized date/time values, ie a floating point number representing the number of days since 1st January 1900 or 1st January 1904 (depending on whether the workbook is using the Windows 1900 or the Mac 1904 calendar).

You have two options here:
  • either you convert '01.06.2014' yourself (perhaps using the PHPExcel_Shared_Date::PHPToExcel() method) and then write the resulting float value to the cell and setting the number format mask
  • or you set a cell value binder that will do this for you automatically if it recognises that a string is a date format
Marked as answer by C4udex on 6/27/2014 at 6:41 AM
Jun 27, 2014 at 2:41 PM
Edited Jun 27, 2014 at 2:49 PM
MarkBaker wrote:
[...]
You have two options here:
  • either you convert '01.06.2014' yourself (perhaps using the PHPExcel_Shared_Date::PHPToExcel() method) and then write the resulting float value to the cell and setting the number format mask
  • or you set a cell value binder that will do this for you automatically if it recognises that a string is a date format
$objPHPExcel->getActiveSheet()->setCellValue('B1', '41791');
$objPHPExcel->getActiveSheet()->getStyle('B1')->getNumberFormat()->setFormatCode('MMM. YY');
Thanks a lot for the detailed explanation. Problem is found. :)