Problem with Datatype: PHPExcel format cell as Date, not String

Topics: Developer Forum, User Forum
Jan 21, 2010 at 1:06 PM

We have the following Problem:

We generte a big Excel from a Database in a loop with setCellValueByColumnAndRow:
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $val);

If we have a string-value that starts with a single charachter (e.g. "A better tomorrow") then the cell is formated as date, not as String, and the value is the current date.
We can reproduce this with every single character. When the string-value start with 2 characters (e.g. "Aa better tomorrow") than it works.

We cant use "setValueExplicit" to declare the data-type, because of the DB-Loop.

We are grateful for any information or hint. Is this a know Problem?

Developer
Jan 21, 2010 at 1:31 PM

I can verify the bug. Work item created:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=11576

Jan 22, 2010 at 2:40 PM

We have still the same problem after installing the patch.

I leave a comment in  the issue tracker...

Developer
Jan 22, 2010 at 3:09 PM

Here is my test script.

require_once 'PHPExcel/Cell/AdvancedValueBinder.php';
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );

$excel = new PHPExcel();

$worksheet = $excel->getActiveSheet();
$worksheet->getCell('A1')->setValue('A better tommorrow');

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$writer->save('write.xlsx');



With PHPExcel 1.7.2 there is the date bug



With latest source code, after the above fix, it looks ok



I assumed that the bug was happening with AdvancedValueBinder.php. Can you test the above script?

Jan 22, 2010 at 4:05 PM
Edited Jan 22, 2010 at 4:06 PM

It seems that your script works. But maybe the Problem is that you fill the Cell with "setValue"

$worksheet->getCell('A1')->setValue('A better tommorrow');

 

And we use for the DB-Loop "setCellValueByColumnAndRow":

 

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $val);

Here is our script, i just removed the DB-Query...

 

require_once $_SERVER['DOCUMENT_ROOT'].'/../phpinc/classes/PHPExcel/PHPExcel/Cell/AdvancedValueBinder.php';
/** PHPExcel_IOFactory */
require_once $_SERVER['DOCUMENT_ROOT'].'/../phpinc/classes/PHPExcel/PHPExcel/IOFactory.php';

##set headers to redirect output to client browser as a file download
header('Content-Type: application/vnd.openXMLformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="test.xlsx"');
header('Cache-Control: max-age=0');

##Excel Object
//Set value binder
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );

$objPHPExcel = new PHPExcel();

##Set properties
$objPHPExcel->getProperties()->setCreator("Test")
->setTitle("Test")
->setSubject("Test")
->setDescription("Test")
->setKeywords("Test");

//get DB Data (here the data as array)
//eg. $output_data[1]['Real Name'] = 'A better tomorrow';


//set excel-data
foreach($output_data as $key=>$val) {
$col_count = 0;
//set key+1 for first row
$key++;

foreach($val as $key2=>$val2) {
//loop data
$val2 = trim($val2);
if(!empty($val2)) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col_count, $key, $val2);
}
$col_count++;
}
}

//Head-Description row
$objPHPExcel->getActiveSheet()->insertNewRowBefore(1, 1);
$col_count2 = 0;
foreach($output_data[0] as $key=>$val) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col_count2, 1, $key);
$col_count2++;
}

##Put an auto filter on the data
$objPHPExcel->getActiveSheet()->setAutoFilter('A1:' . $objPHPExcel->getActiveSheet()->getHighestColumn() . $objPHPExcel->getActiveSheet()->getHighestRow() );

##Header
$objPHPExcel->getActiveSheet()->insertNewRowBefore(1, 2);//Some empty rows for space
$objPHPExcel->getActiveSheet()->setCellValue('A1', "Headline");
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setRGB('A2007A');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);

##Printing
//Orientation
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);

##Set width of Cells
$highestColumn = $objPHPExcel->getActiveSheet()->getHighestColumn(); //e.g., 'G'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); //e.g., 6
for($column =1; $column < $highestColumnIndex; $column++) //start from 1 as columns are 0 indexed, but we don’t want to change the first row which we have already set explicitly
{
$objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($column))->setAutoSize(true);
}

//bold headline
$objPHPExcel->getActiveSheet()->getStyle('A3:' . $highestColumn.'3')->getFont()->setBold(true);

##set to top -> all fields
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
##linebreak -> all fields
$objPHPExcel->getDefaultStyle()->getAlignment()->setWrapText(true);

##Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Test Excel');

##Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

//-----Create a Writer and output the file to the browser-----
$objWriter2007 = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter2007->save('php://output'); //push out to the client browser

 

 

Developer
Jan 22, 2010 at 4:38 PM

Hmm.. I tried with setCellValueByColumnAndRow(). No difference.

require_once 'PHPExcel/Cell/AdvancedValueBinder.php';
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );

$excel = new PHPExcel();

$worksheet = $excel->getActiveSheet();
$worksheet->setCellValueByColumnAndRow(0, 1, 'A better tommorrow');

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$writer->save('write.xlsx');

I can't see how the bug can still occur. Is there a chance that you are somehow still running the old version?

Jan 22, 2010 at 4:58 PM

We installed the current Version 1.7.2

I will make some further Tests at the weekend and let u know.

And thx for your great support! Hope we find the Problem...

Jan 29, 2010 at 1:07 PM
Edited Jan 29, 2010 at 1:13 PM

Hi koyama,

sorry for my late reply, but we tested your snippet with 1.7.2 and your patch and still the same problem:
Just the current Date in the Field, no matter if we save the excel as file or output it directly to the browser.

Can't understand that it is working in your case, but not in ours. Here is our Script:

 

require_once $_SERVER['DOCUMENT_ROOT'].'/../phpinc/classes/PHPExcel/PHPExcel/Cell/AdvancedValueBinder.php';
/** PHPExcel_IOFactory */
require_once $_SERVER['DOCUMENT_ROOT'].'/../phpinc/classes/PHPExcel/PHPExcel/IOFactory.php';

##set headers to redirect output to client browser as a file download
header('Content-Type: application/vnd.openXMLformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="test.xlsx"');
header('Cache-Control: max-age=0');

PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );

$excel = new PHPExcel();

$worksheet = $excel->getActiveSheet();
$worksheet->setCellValueByColumnAndRow(0, 1, 'A better tommorrow');

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$writer->save('php://output'); //push out to the client browser

Can u please check it again?

EDIT:
It looks like the function "stringToExcel" isnt called. I made an output (send a mail) in this Function, but nothing happens.

Are u shure the function is called? I think the problem must be in another place...

 

Developer
Jan 30, 2010 at 8:52 PM

I realize now what the problem is. Since PHPExcel 1.7.2 some changes were made in AdvancedValueBinder.php:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=11483

I was testing with AdvancedValueBinder.php from latest source code.

Please download latest source code where things should be working:

http://phpexcel.codeplex.com/SourceControl/list/changesets

 

Feb 1, 2010 at 10:10 AM

Ok, with the Version from SVN it seems to work... THX!

The only thing is that i have to include the file "PHPExcel.php" before "/PHPExcel/Cell/AdvancedValueBinder.php".
This wasn't necessary in the last Version, the offical 1.7.2 Build.

Coordinator
Feb 1, 2010 at 10:38 AM
muck wrote:

Ok, with the Version from SVN it seems to work... THX!

The only thing is that i have to include the file "PHPExcel.php" before "/PHPExcel/Cell/AdvancedValueBinder.php".
This wasn't necessary in the last Version, the offical 1.7.2 Build.

 I think I can be blamed for that problem.

One of the changes implemented in SVN is a "lazy loader", so that classes are only required when they're actually needed, and user scripts only need to include/require a single PHPExcel file and everything else will just be pulled in as needed (no further includes necessary). The benefits of this are reduced memory overhead (anything between .5MB and 2.5MB), and a performance increase (about 10%) in loading or saving workbooks as well (require_once is an expensive function call).

I've only configured a handful of files as "entry points" that automatically initialise the "lazy loader" (PHPExcel.php itself, the IO Factory and each individual reader, and the calculation engine). If user scripts include any of these files, there is no need to include any other file such as the AdvancedValueBinder; and I still need to document this change.

I probably need to check the directory paths in the "entry points" as well, I've noticed a couple of places where they're not set up correctly for the "lazy loader".

Sep 27, 2010 at 10:51 PM
Edited Sep 27, 2010 at 10:55 PM

Thanks! I tried to experiment with some of the code within this thread but the code 'koyama' and it worked :)

muck wrote:

Ok, with the Version from SVN it seems to work... THX!

The only thing is that i have to include the file "PHPExcel.php" before "/PHPExcel/Cell/AdvancedValueBinder.php".
This wasn't necessary in the last Version, the offical 1.7.2 Build.

This fixed the issue:

Fatal error: Class 'PHPExcel_Cell_DefaultValueBinder' not found in ..\PHPExcel\Cell\AdvancedValueBinder.php on line 36

 

Coordinator
Sep 28, 2010 at 10:14 PM

I've added the entry points to load the "lazy loader" to the DefaultValueBinder and AdvancedValueBinder

Jan 18, 2011 at 2:23 PM

Sorry to re-open this one but I have a similar bug in 1.7.5.

I'm not sure if the test-case that was fixed was based on particular characters but in our sample sheets the string 'T-Mobile' (single character with a hyphen) is being transformed into a date (2011-01-18) when using PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder()).

Same issue?

Coordinator
Jan 18, 2011 at 4:16 PM
seuj wrote:

Sorry to re-open this one but I have a similar bug in 1.7.5.

I'm not sure if the test-case that was fixed was based on particular characters but in our sample sheets the string 'T-Mobile' (single character with a hyphen) is being transformed into a date (2011-01-18) when using PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder()).

Same issue?

 I need to double check this. I thought that I'd fixed it recently (since the 1.7.5 release), but can't find a reference here on the discussion board, or on the issue list.

Jan 18, 2011 at 4:31 PM

Many thanks Mark - let me know if I need to pull SVN to test.

Jan 26, 2011 at 7:46 PM

Anything I can do to help this issue along Mark? I have test files if needed. Many thanks.

Coordinator
Jan 29, 2011 at 2:07 PM

@seuj

I can recreate the issue... that's not a problem.

What I'm trying to do is come up with a regexp (or at least a rule that I can implement) to filter out strings that cannot possibly be considered dates from those that are potential dates before calling the DATEVALUE() function.

I have to come up with something that will allow variants such as '29-Jan-2011', '2011/01/29', '02-29-11', 'Jan 2011', 'Jan-2011', 'Jan 29', etc. while rejecting values such as 'T-Mobile' and '50-cent'.

I have a basic solution, but one that will reject a lot of perfectly valid dates/times... I still need to refine it so that I get fewer false negatives while still avoiding any false positives.

I'm targetting the next release (1.7.6) for about 2 weeks time. If I haven't come up with anything better by then, I'll go with my current filter "as is".