3

I have a Json, decode it, write it in a CSV file with PHP, convert the CSV to .xls with the aid of PHPexcel

The goal is to export from a JSON into an excel file correctly. I am having trouble with encoding and mainly with Excel for Mac 2011 which does not support UTF-8 CSV files: link If I preview it (spacebar) on my mac it looks well encoded. The trouble is the excel.

First the code for the CSV:

$file_csv = fopen('files/file.csv', 'w');

then the JSON:

$response_data = json_decode($connection->response['response'], true);
foreach ($response_data as $value)
  {
  //take the data I have 10 strings here in reality
  $text_lang = $value['lang'];
  $date = $value['date'];
  //insert into the csv
  $details = array($text_lang, $date );
  fputcsv($file_csv, $details);
};
fclose($file_csv);

Now if I import this CSV with the excel I have trouble with UTF-8. If I convert it with a text editor e.g. Textmate to UTF-16LE, and then import it to Excel everything is smooth.

And the PHPExcel code to create the .xls from here:

$objReader = PHPExcel_IOFactory::createReader('CSV');
$objPHPExcel = $objReader->load('files/file.csv');  
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('files/summary.xls');

The summary.xls is broken on UTF-8 and when opening it requires to be repaired.

My first attempt was to convert the above file.csv into UTF-16LE in PHP to see if that will be ok with the excel. I used iconv before fputcsv and the excel showed Chinese words...

Now I want to encode the csv file to UTF-16LE and load it well with PHPExcel. I used the following lines and didn't work. Probably because the .csv I had created with the incov was partially (?) UTF-8 and UTF-16LE.

$objReader->setInputEncoding('UTF-16LE');
$objPHPExcel = $objReader->load('file.csv');

The goal is to render the data from the JSON into a .xls (or .xlxs) document without encoding problems on excel.

Community
  • 1
  • 1
Diolor
  • 13,181
  • 30
  • 111
  • 179
  • 1
    You're right that mixing encodings will cause problems: The CSV Reader will always execute try to convert from the input encoding to UTF-8 before inseerting a value into the PHPExcel cell object... if the CSV value is UTF-16LE, then this should work correctly, but converting a UTF-8 value to UTF-8 as though it were UTF16-LE will cause problems.... the csv file does need to be internally consistent in it sencoding – Mark Baker May 03 '13 at 13:44
  • @MarkBaker Thanks for the comment and thanks for your commitment on PHPExcel. I am not sure but I think that there is some problems with special character like: or ♑ (readable on safari/not in chrome) When excel founds on of these characters it breaks and needs repair while data gets lost. Theoretically "Apple Color Emoji" should not be a problem since they are unicode or UTF-8 readable. I am searching for more right now – Diolor May 03 '13 at 22:12
  • @MarkBaker Yes. I had to sanitize strings from emoji. I added this function: http://stackoverflow.com/a/12824140/1447885 – Diolor May 03 '13 at 22:48
  • Glad you found a solution – Mark Baker May 03 '13 at 22:57

1 Answers1

3
$objReader->setInputEncoding('');

$objPHPExcel = $objReader->load('file.csv');

this worked well for me.

devlin carnate
  • 8,309
  • 7
  • 48
  • 82
Kooker Sam
  • 31
  • 2