Problem with setCellValueByColumnAndRow

Topics: Developer Forum, Project Management Forum, User Forum
May 15, 2013 at 3:00 PM
Hello,

i have require in my file: require_once("_libraries/PHPExcel_1.7.8/Classes/PHPExcel.php");

So I would use: $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 8, 'Some value');

But i get a error, then i open the excelfile:

In English translate: "Excel was unreadable content in ... found."

What is it Problem?
Coordinator
May 15, 2013 at 3:08 PM
So what does your error log say? Or open the file in a text editor and see if there are any error messages in it?

Please folks, learn to do some basic PHP debugging yourselves: my telepathy is on a low at the moment, and the crystal ball is in the repair shop this week.
May 15, 2013 at 3:16 PM
Edited May 15, 2013 at 3:24 PM
ID: 1, Application Name: Microsoft Office Excel, Application Version: 12.0.6665.5003, Microsoft Office Version: 12.0.6612.1000. This session lasted 7 seconds with 0 seconds of active time. This session ended normally.


Protokollname: OSession
Quelle: Microsoft Office 12 Sessions
Datum: 15.05.2013 17:24:11
Ereignis-ID: 7000
Aufgabenkategorie:Keine
Ebene: Informationen
Schlüsselwörter:Klassisch
Benutzer: Nicht zutreffend
Computer: process-windows
Beschreibung:
ID: 1, Application Name: Microsoft Office Excel, Application Version: 12.0.6665.5003, Microsoft Office Version: 12.0.6612.1000. This session lasted 7 seconds with 0 seconds of active time. This session ended normally.
Ereignis-XML:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="Microsoft Office 12 Sessions" />
<EventID Qualifiers="0">7000</EventID>
<Level>4</Level>
<Task>0</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2013-05-15T15:24:11.000000000Z" />
<EventRecordID>106</EventRecordID>
<Channel>OSession</Channel>
<Computer>process-windows</Computer>
<Security />
</System>
<EventData>
<Data>1</Data>
<Data>Microsoft Office Excel</Data>
<Data>12.0.6665.5003</Data>
<Data>12.0.6612.1000</Data>
<Data>7</Data>
<Data>0</Data>
</EventData>
</Event>
May 15, 2013 at 3:57 PM
OK, i have a Problem limited.

When the Worksheet-Name have a umlaut (ä, ü, ö, ...), then come this Problem.

But i convert the Name with html_entity_decode, but still the problem persists.
Coordinator
May 15, 2013 at 4:14 PM
UTF-8 is the answer:

Use either iconv() or mb_convert_encoding() to ensure your strings are all UTF-8 before setting them as cell values
May 15, 2013 at 4:24 PM
But,

in the database the data are utf8_general_ci and
in the Site-Header is <meta http-equiv="content-type" content="text/html; charset=UTF-8">
Coordinator
May 15, 2013 at 4:30 PM
The site header is irrelevant because you're not saving the file into the site.... are you setting names UTF-8 for your database connection?
May 15, 2013 at 4:42 PM
my data in database all in utf-8
Coordinator
May 15, 2013 at 4:45 PM
Edited May 15, 2013 at 4:46 PM
It may well all be UTF-8 in the database, but unless you're using SET NAMES UTF-8 in your database connection, then what you're getting back isn't guaranteed to be UTF-8.... if your PHPExcel problem is non-ASCII characters, then the data you're setting in the cells isn't UTF-8 so something is getting lost in translation between the database and the call to set the worksheet name.
May 15, 2013 at 6:38 PM
if your PHPExcel problem is non-ASCII characters, then the data you're setting in the cells isn't UTF-8 so something is getting lost in translation between the database and the call to set the worksheet name.
We can this repair?
Coordinator
May 15, 2013 at 9:11 PM
Data Storage:
Specify the utf8 character set on all tables and text columns in your database. This makes MySQL physically store and retrieve values encoded natively in UTF-8. Note that MySQL will implicitly use utf8 encoding if a utf8_* collation is specified (without any explicit character set).

Data Access:
In your application code, in whatever DB access method you use, you'll need to set the connection charset to utf8. This way, MySQL does no conversion from its native UTF-8 when it hands data off to your application and vice versa.

Some drivers provide their own mechanism for configuring the connection character set, which both updates its own internal state and informs MySQL of the encoding to be used on the connection—this is usually the preferred approach. In PHP:

If you're using the PDO abstraction layer with PHP ≥ 5.3.6, you can specify charset in the DSN:
$dbh = new PDO('mysql:charset=utf8');
If you're using mysqli, you can call set_charset():
$mysqli->set_charset('utf8');       // object oriented style
mysqli_set_charset($link, 'utf8');  // procedural style
If the driver does not provide its own mechanism for setting the connection character set, you may have to issue a query to tell MySQL how your application expects data on the connection to be encoded: SET NAMES 'utf8'.