Microsoft Excel mangles Diacritics in .csv files?-第3页回答

I am programmatically exporting data (using PHP 5.2) into a .csv test file.
Example data: Numéro 1 (note the accented e). The data is utf-8 (no prepended BOM).

When I open this file in MS Excel is displays as NumÃ©ro 1.

I am able to open this in a text editor (UltraEdit) which displays it correctly. UE reports the character is decimal 233.

How can I export text data in a .csv file so that MS Excel will correctly render it, preferably without forcing the use of the import wizard, or non-default wizard settings?

标签： excel encoding csv diacritics

22条回答

像晚风撩人

2楼-- · 2018-12-31 08:00

You can save an html file with the extension 'xls' and accents will work (pre 2007 at least).

Example: save this (using Save As utf8 in Notepad) as test.xls:

<html>
<meta http-equiv="Content-Type" content="text/html" charset="utf-8" />
<table>
<tr>
  <th>id</th>
  <th>name</th>
</tr>
<tr>
 <td>4</td>
 <td>Hélène</td>
</tr>
</table>
</html>

0人赞添加讨论(0) 举报

萌妹纸的霸气范

3楼-- · 2018-12-31 08:00

Note that including the UTF-8 BOM is not necessarily a good idea - Mac versions of Excel ignore it and will actually display the BOM as ASCII… three nasty characters at the start of the first field in your spreadsheet…

0人赞添加讨论(0) 举报

伤终究还是伤i

4楼-- · 2018-12-31 08:00

I can only get CSV to parse properly in Excel 2007 as tab-separated little-endian UTF-16 starting with the proper byte order mark.

0人赞添加讨论(0) 举报

素衣白纱

5楼-- · 2018-12-31 08:04

I've found a way to solve the problem. This is a nasty hack but it works: open the doc with Open Office, then save it into any excel format; the resulting .xls or .xlsx will display the accentuated characters.

0人赞添加讨论(0) 举报

刘海飞了

6楼-- · 2018-12-31 08:06

As Fregal said \uFEFF is the way to go.

<%@LANGUAGE="JAVASCRIPT" CODEPAGE="65001"%>
<%
Response.Clear();
Response.ContentType = "text/csv";
Response.Charset = "utf-8";
Response.AddHeader("Content-Disposition", "attachment; filename=excelTest.csv");
Response.Write("\uFEFF");
// csv text here
%>

0人赞添加讨论(0) 举报

栀子花@的思念

7楼-- · 2018-12-31 08:06

open the file csv with notepad++ clic on Encode, select convert to UTF-8 (not convert to UTF-8(without BOM)) Save open by double clic with excel Hope that help Christophe GRISON

0人赞添加讨论(0) 举报

Microsoft Excel mangles Diacritics in .csv files?

采纳回答

编辑标签

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

付费偷看金额在0.1-10元之间