PHP生成Excel / CSV文件并以UTF-8发送

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

我正在使用UTF-8从Postgres DB中检索数据。 db和client_connection设置为UTF-8。

Then I send 2 headers to the visitor:

然后我向访问者发送2个标题:

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.

并开始以CSV方式输出纯文本数据。这将在访问者桌面上以简单的Excel文件打开。

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

我尝试在将数据从db检索到latin-1时更改我的client_encoding,这在大多数情况下都适用,但不适用于所有字符。所以这不是解决方案。

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

如何将输出的文件作为UTF-8发送?我不认为将数据从db转换为latin-1是可能的,因为latin-1中的char似乎未知...所以我需要Excel将文件视为UTF-8

6 个解决方案

#1

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

我会研究使用PHPExcel引擎。它使用UTF-8作为默认值,它可以生成电子表格文件类型的完整列表(Excel,OpenOffice,CSV等)。

#2

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.

我建议不要发送纯文本并将其伪装成Excel。 XLS文件通常是二进制文件,虽然不需要二进制文件,但使用非二进制数据的官方Excel方法是将其格式化为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.

您在标题中提到“CSV”,但您的问题不包括与CSV相关的任何内容。我提出这个问题是因为我认为你实际上应该将标签更改为逗号,然后你可以简单地输出一个标准的.csv文件,该文件仍由Excel读取,但不依赖于未记录或不稳定的功能。

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.

如果你真的想发送application / msexcel,那么你应该使用一个真正的Excel库,因为目前你没有创建一个真正的Excel文件。

#3

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

使用 ;应用后的charset = UTF-8 / xxxxxx我使用:

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

#4

Try mb_convert_encoding function.

尝试mb_convert_encoding函数。

#5

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

尝试使用iconv,将字符串转换为必需的字符集。

#6

Have you tried utf8_encode() the string?

你试过utf8_encode()字符串吗?

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

所以类似:echo implode(“\ t”,utf8_encode($ cols)。“\ r \ n”)

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

不确定这是否有效,但试一试

#1