CODEWITHSUNDEEP
php
user410932
user410932

Reputation: 3015

PHP generate Excel/CSV file and send as UTF-8

I'm retrieving data from my Postgres DB in UTF-8. The db and the client_connection settings are in UTF-8.

Then I send 2 headers to the visitor: 

header("Content-Type: application/msexcel");
header("Content-Disposition: $mode; filename=export.xls");

and start outputting plain text data in a CSV-manner. This will open as a simple Excel file on the visitors desktop.

$cols = array ("col1", "col2", "col3");
echo implode("\t", $cols)."\r\n";

Works fine, untill special characters like é, è etc are encountered.

I tried changing my client_encoding while retrieving the data from the db to latin-1, which works in most cases but not for all chars. So that is not a solution.

How could I send the outputted file as UTF-8? I don't think converting the data from the db to latin-1 is possible, since the char seems unknown in latin-1 ... so I need Excel to treat the file as UTF-8

Upvotes: 2

Views: 5381

Answers (6)

Saic Siquot
Saic Siquot

Reputation: 6513

use ; charset=UTF-8 after aplication/xxxxxx I do use:

    header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
//  header("Content-Length: " . strlen($thecontent));     // this is not mandatory
    header('Content-Disposition: attachment; filename="file.xls"');

Upvotes: 1

ken
ken

Reputation: 3709

I would recommend not sending plain-text and masquerading it as Excel. XLS files are typically binary, and while binary isn't required, the official Excel method of using non-binary data is to format it as XML.

You mention "CSV" in the title, but nothing about your problem includes anything related to CSV. I bring this up because I believe that you should actually change your tabs to commas, and then you could simply output a standard .csv file, which is read by Excel still but doesn't rely on undocumented or unstable functionality.

If you truly want to send application/msexcel, then you should use a real Excel library, because currently, you are not creating a real Excel file.

Upvotes: 1

DarkMantis
DarkMantis

Reputation: 1516

Have you tried utf8_encode() the string?

So something like: echo implode("\t", utf8_encode($cols)."\r\n")

Not sure if that would work, but give it a go

Upvotes: 0

Mark Northrop
Mark Northrop

Reputation: 2623

I'd look into using the PHPExcel engine. It uses UTF-8 as default and it can generate a whole list of spreadsheet file types (Excel, OpenOffice, CSV, etc.).

Upvotes: 2

shox
shox

Reputation: 1160

Try to use iconv, for converting string into required charset.

Upvotes: 0

Roman Snitko
Roman Snitko

Reputation: 3655

Try mb_convert_encoding function.

Upvotes: 0

Related Questions