Problem with version 1.7.5

Topics: Developer Forum
Dec 12, 2010 at 3:27 PM

Hello,

Version 1.7.4 works fine.
Since I have been updated to version 1.7.5. It adds me dates in column E randomly, then they should be the name. And this, nothing changed in my PHP code.

Congratulations on your beautiful work.

Have a good day
,

Steph258

Coordinator
Dec 12, 2010 at 6:26 PM

Is this when you're reading a file, or writing a file? What formats? Is PHPExcel replacing existing cell values? (if so, what should the cells contain?) or arbitrarily adding values to empty cells? Do the cells have any number masking? Is it always column E? or does it happen in other columns?

Dec 12, 2010 at 7:01 PM

While writing the file format (CSV, Excel 97, Excel 2003, Excel 2007).
PHPExcel replace the cell value by the day 2010-04-12 (40280) or 2010-12-12 (40,524). The remaining cells in the column have a standard format, but these cells are transformed into a date format. These cells should contain the name. And has only column E that is affected.

Coordinator
Dec 12, 2010 at 7:20 PM

How are you setting the cell values? What is "the name"? I assume "standard format" means that you're not setting any specific format? I also assume that you've not changed the value binder.

Dec 12, 2010 at 7:26 PM

I search in a database.  "The name" is the family name.  Yes, I do not specify format.

Coordinator
Dec 13, 2010 at 9:06 AM

It's very strange that PHPExcel should arbitrarily decide that some values (especially strings) should be dates, and change the format of it's own volition. Even stranger, that it should convert these strings to a valid date value.

Can you give me some idea of the string (name) values that you're reading from the database, and the specific dates that they're being converted into.

Dec 18, 2010 at 9:14 PM

I'm back.
The name issue are: "April" and "L. Martin".
And only those two.
If it can help.

Dec 18, 2010 at 9:18 PM

Sorry, I forgot to answer the last question.
"April" is replaced by 2010-04-18.
"
L. Martin" is replaced by 2010-12-18.

Dec 19, 2010 at 2:16 PM

I have a problem using the caching method discISAM. The caching method seems to be overwritten to 'Memory' in cachedObjectStorageFactory:

my test-Code:

        $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
        $cacheSettings = array(); // 'memoryCacheSize'  => '256MB');

        if (! PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings))
        {
            echo "error setting caching method in " . __METHOD__ . " using caching method $cacheMethod";
            die;

        } else {
           
            echo PHPExcel_Settings::getCacheStorageMethod();
            die;
        }
-> returns 'Memory'
While $memory contains 'DiscISAM' in PHPExcel_Settings::setCacheStorageMethod(), it changes to 'Memory' in  PHPExcel_CachedObjectStorageFactory::initialize() for any reason. I could change the default value in initialize() - but this does not sounds right to me.

I have seen, that PHPExcel_Settings contains a static wrapper function 'getCacheStorageMethod' which works if $_cacheStorageMethod has a public scope in class PHPExcel_CachedObjectStorageFactory.

This method returns also 'Memory'.


Coordinator
Dec 21, 2010 at 11:08 PM

@steph258

I can unserstand why "April" would be replaced with 2010-04-18 if you were using the Advanced Value Binder, although I can't see why this should happen if you were using the Default Value Binder. Please confirm that you aren't using the Advanced Value Binder, otherwise I could spend months searching for an error.

 In the interim, can you try using the setCellValueExplicit() method rather than setCellValue() to force the datatype to PHPExcel_Cell_DataType::TYPE_STRING

 


@trophy

In future, please don't hijack other threads about totally unrelated issues. Create a new thread for a new topic.

Are you instantiating a PHPExcel object or loading a file before setting the cacheing method?

Dec 23, 2010 at 12:22 AM

@MarkBaker: well - i tought: "Problem with version 1.7.5" could be a good thread - anyway - that was my first flame ever received in a discussion board you made your point perfectly clear :) - so sorry for that!

I am instantiating a PHPExcel object to create an xlsx.

Thanks in advance.

Coordinator
Dec 23, 2010 at 8:41 AM

@Trophy

That wasn't a flame, simply a mild reprimand... you'll know when you've been flamed. It just makes it a lot harder to keep track of what's being said if there are two active (and totally unrelated) conversations going on in the same thread.

Back to the problem you're having... you need to ensure that you set the cache storage method before you instantiate the PHPExcel object, otherwise PHPExcel will set it for you as the default method, which is memory. Once it's set, it can't be changed within the execution time of the script.

Dec 24, 2010 at 5:24 PM

Here are some of the code:

$objPHPExcel->setActiveSheetIndex(0)
	->setCellValue('B'.$c, mb_convert_encoding ($data[$i]['Nom'], "UTF-8", "ISO-8859-1" ))
	->setCellValue('C'.$c, mb_convert_encoding ($data[$i]['Prenom'], "UTF-8", "ISO-8859-1" ));

//Date
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() ); $objPHPExcel->getActiveSheet()->setCellValue('A'.$c, $data[$i]['Date']); $objPHPExcel->getActiveSheet()->getStyle('A'.$c)->getNumberFormat()->applyFromArray(array('code' => 'yyyy/mm/dd')); // Centrage des colonnes $objPHPExcel->getActiveSheet()->getStyle('A'.$c.':'.'C'.$c) ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

Dec 24, 2010 at 7:53 PM

And with this code, there is no problem:

PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );
$objPHPExcel->getActiveSheet()->getCell('B'.$c)->setValueExplicit(mb_convert_encoding ($data[$i]['Nom'], "UTF-8", "ISO-8859-1" ), PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->getCell('C'.$c)->setValueExplicit(mb_convert_encoding ($data[$i]['Prenom'], "UTF-8", "ISO-8859-1" ), PHPExcel_Cell_DataType::TYPE_STRING);

//Date
$objPHPExcel->getActiveSheet()->setCellValue('A'.$c, $data[$i]['Date']);
$objPHPExcel->getActiveSheet()->getStyle('A'.$c)->getNumberFormat()->applyFromArray(array('code' => 'yyyy/mm/dd'));

Thank you, even if does not understand why it did not.

Coordinator
Dec 26, 2010 at 4:32 PM

When you set a cell value, you also set a value datatype (null, numeric, string, boolean, formula, error, etc).

If you use setValueExplicit(), you tell PHPExcel what datatype to set, as well as what value (the default is PHPExcel_Cell_DataType::TYPE_STRING). If you use setCellValue(), then PHPExcel attempts to determine the datatype itself, and the dataTypeForValue() method of the active Value Binder provides that rules that it uses for this. The Default Value Binder is fairly simple, principally based on the PHP datatype; but the Advanced Value Binder is more sophisticated. It will, for example, identify a string containing '2 1/2' as a numeric 2.5; or a string containing '75%' as 0.75... and in addition to setting datatype, it can also set number format masks, so the format masking for my previous examples is set to display a fraction and a percentage. It will also try to identify strings that are formatted date and/or time values, and convert them to an Excel date value, setting a basic date mask ('yyyy-mm-dd') if it finds something that looks like a date.

This is what is happening to your names. You're using the Advanced Value Binder, so the string 'April' is being interpreted as a date, and the PHP date logic is filling in the day and year using the current time, so 'April' will be treated as '26-April-2010', and converted to an Excel date accordingly. Once you set a Value Binder, it is applied for every subsequent SetCellValue() call. I'm guessing that you're setting it in a loop, expecting it to be applied only to the next call to SetCellValue() for the value in column A; but in the next iteration of the loop it will be applied for the setting of name values in cells B and C as well.