从浏览器将CSV数据下载到Excel [英] Downloading CSV data into Excel from a Browser

问题描述

因此，我在PHP中有一个脚本，用于创建制表符分隔 CSV输出.

So I have a script in PHP that creates tab separated CSV output.

我的HTML中有一个按钮，其工作方式如下:

I have a button in my HTML that works like so:

<a href="https://www.domain.co.uk/api.php?mode=csv" target="_blank" download="export.csv">Export Data</a>

理想情况下，我希望用户在Excel中打开此CSV文件.

Ideally I want the user to open this CSV file in Excel.

我在这里遇到的问题是制表符分隔的CSV，文件扩展名以及Excel如何处理所有这些问题.例如:

The issue I have here is with tab separated CSVs, the file extension, and how Excel handles all of this. For example:

download="export.csv"

浏览器中的结果要求我在Excel中打开它(此举是有害的行为)，但是一旦在Excel中，则所有列都不被尊重，因为它们是制表符分隔的(不是逗号分隔，这显然是Excel所期望的).

Results in the Browser asking me to open this in Excel (wanted behaviour), but then once in Excel none of the columns are respected as they are tab separated (not comma separated, which Excel is obviously expecting).

download="export.xls"

浏览器的结果是要求我在Excel中再次打开它(再次，是想要的行为)，但是Excel抱怨文件扩展名和内容不匹配，并向用户发出警告.如果用户超过此警告，则数据将按预期显示，但是我可以在没有警告的情况下进行操作.

Results in the Browser asking me to open this in Excel (again, wanted behaviour), but then Excel complains that the file extension and the contents do not match and gives the user a warning. If the user goes past this warning the data displays as expected, but I could do without the warning.

download="export.txt"

导致浏览器将文件下载为文本文件.一旦导入到Excel中，列就会受到尊重，但是我可以考虑将其视为类似于CSV文件的Excel文件.

Results in the Browser downloading the file as a text file. Once imported into Excel, the columns are respected, but I could do with this being thought of as an Excel file like CSV files are.

download="export.tsv"

导致浏览器下载文件，但是由于无法识别此扩展名，因此需要手动将其导入Excel，这不是我要的.实际上，即使TSV是制表符分隔节的最正确文件扩展名，TXT扩展名似乎也能更流畅地工作.

Results in the Browser downloading the file, but as this extension isnt recognized, it will need to be imported into Excel manually, which isn't what I am after. Infact, even though TSV is the most correct file extension for tab separated verse, the TXT extension seems to work more smoothly.

我无法在最终用户计算机上设置文件关联，并且我尽可能避免采用将数据导出为实际XLXS文件"路线.我宁愿使用制表符分隔的CSV而不是逗号分隔的CSV，因为导出的数据自然会包含很多逗号.

I am unable to set file associations on the end users machine, and I would like to avoid going down the "export your data as an actual XLXS file" route if at all possible. I would prefer to use tab separated CSVs over comma separated CSVs because the exported data contains lots of commas naturally.

因此，根据罗恩·罗森菲尔德(Ron Rosenfeld)的建议，我尝试输出一个用逗号分隔的CSV文件，并在数据前后加上引号-并将文件加载到Excel中，并保留了列-但是引号会出现在使用引号的每列数据中.

So as per Ron Rosenfeld suggested I tried outputting a comma separated CSV file with quotes around the data - and the file loads into Excel, with columns preserved - however the quotes appear on every piece of data in every column that uses quotes.

是否可能不出现引号?

理想情况下，我希望将内容选项卡分开，但是在此阶段，任何允许我从浏览器打开CSV文件到Excel的东西都很好.

Ideally I would prefer to have the content tab separated, but at this stage anything that allows me to open a CSV file from a browser into Excel would be great.

我想要一种方法，以尽可能少的麻烦从浏览器将制表符分隔的CSV文件下载到Excel.如何实现?

I want a way to download a tab separated CSV file from a browser to Excel with as little fuss as possible. How can this be achieved?

推荐答案

CSV表示逗号分隔的值"，因此默认分隔符为,.

CSV means "comma separated values", so the default separator is a ,.

要将该分隔符更改为标签，请放入

To change that separator to a tab, put

sep=\t

作为.csv文件中的第一行(是的，您仍然可以将其命名为.csv).这告诉excel分隔符应该是什么.

as the first line in your .csv-file (yes, you can still name it .csv). That tells excel what the delimiter character should be.

请注意，如果您使用实际的文本编辑器打开.csv，则其内容应类似于

Note, that if you open the .csv with an actual text editor, it should read like

sep=     (an actual tabulator character here, it's just not visible...)

.csv RFC 4180 中尚未正式定义此功能，因此，如果它起作用除Excel以外的任何软件都取决于该软件的实现.

This feature is not officially defined in the .csv RFC 4180, so if it works with any software other than Excel depends on that software's implementation.

这篇关于从浏览器将CSV数据下载到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！