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

Question

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

Mark Northrop · Accepted Answer · 2011-10-04 14:43:10Z

2

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.).

answered Oct 4, 2011 at 14:43

Mark Northrop

2,6331 gold badge21 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ken · Accepted Answer · 2011-10-04 14:47:32Z

1

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.

answered Oct 4, 2011 at 14:47

ken

3,7281 gold badge32 silver badges44 bronze badges

3 Comments

DarkMantis Over a year ago

That's true, and there is a library to deal with Excel files, such as code.google.com/p/php-excel and infact I'm sure there's probably hundreds out there. There is one native to PHP, however it only runs on Windows servers, I can't remember the name of it though :/

user410932 Over a year ago

I know, I'm generating TSV files instead of CSV files, but the result is the same: it's a plain text file with the data seperated by a separator. I'm maskerading as XLS so the visitor's desktop will open the file appropriately instead of in a Txt-editor or something alike. That does work fine, untill your output contains some special characters.

ken Over a year ago

That is not a valid reason to masquerade as .xls; Excel sets itself as the default application for .csv files.

Saic Siquot · Accepted Answer · 2011-10-04 15:16:42Z

1

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"');

answered Oct 4, 2011 at 15:16

Saic Siquot

6,5135 gold badges37 silver badges56 bronze badges

1 Comment

Benjamin Over a year ago

Doesn't work for me.

Roman Snitko · Accepted Answer · 2011-10-04 14:38:04Z

0

Try mb_convert_encoding function.

answered Oct 4, 2011 at 14:38

Roman Snitko

3,64527 silver badges29 bronze badges

2 Comments

user410932 Over a year ago

WebFlakeStudio: isn't the other way around possible: send a certain header so Excel will know it's to open the file as UTF-8 ?

Piskvor left the building Over a year ago

@user410932: No. The file is transferred using HTTP, and then opened through normal means - Excel isn't downloading the file, and the HTTP headers won't be accessible to it.

Roman Snitko · Accepted Answer · 2011-10-04 14:41:49Z

0

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

edited Oct 4, 2011 at 14:41

Roman Snitko

3,64527 silver badges29 bronze badges

answered Oct 4, 2011 at 14:37

shox

1,1605 gold badges18 silver badges32 bronze badges

Comments

DarkMantis · Accepted Answer · 2011-10-04 14:44:54Z

0

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

answered Oct 4, 2011 at 14:44

DarkMantis

1,5165 gold badges19 silver badges40 bronze badges

1 Comment

DarkMantis Over a year ago

Unless your trying to encode the entire file which then it wouldn't work

Collectives™ on Stack Overflow

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

6 Answers 6

Comments

3 Comments

1 Comment

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

3 Comments

1 Comment

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related