为什么Microsoft Excel不能打开.csv文件？ [英] why cann't Microsoft Excel open .csv file?

问题描述

我在项目中用C＃代码创建一个.csv文件。它有一些用逗号填充的列。

Hi,
I create a .csv file by C# code in my project. It has some column saperated with comma.

StringBuilder sb = new StringBuilder();
                //Making columns!
                foreach (ColumnHeader ch in listView_Search.Columns)
                {
                    sb.Append(ch.Text + ",");
                }
                sb.AppendLine();

                //Looping through items and subitems
                foreach (ListViewItem lvi in listView_Search.SelectedItems)
                {
                    foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems)
                    {
                        if (lvs.Text.Trim() == string.Empty)
                            sb.Append(" ,");
                        else
                            sb.Append(lvs.Text + ",");
                    }
                    sb.AppendLine();
                }

                StreamWriter sw = new StreamWriter(filePath, false, Encoding.UTF8);
                sw.Write(sb);
                sw.Close();

在我的系统中（Windows 7，Microsoft Office 2010）我的文件可以正确地用Excel打开。每个列值都在正确的分隔列中。

但在其他系统（Windows XP，Microsoft Office 2010）中，该文件由Excel打开，但所有值都在同一列中！



如何解决这个问题？

In my system (windows 7, Microsoft Office 2010) my file can open with Excel correctly. every column values are in saperated columns, correctly.
But in other system (windows XP, Microsoft Office 2010) that file opens by Excel but all values are in same column!!!!

how do i resolve this problem?

推荐答案

这可能取决于设置的字符是什么列出两台机器上的分离器。



[ ^ ]请查看本页底部，了解有关如何更改的详细信息。

It's probably down to what character is set as the list seperator on the two machines in question.

[^] Check the bottom of this page for details on how to change it.

如果双击该文件并在Excel中自动打开，Excel将使用一组默认配置选项来解析文本。



如果先打开Excel，请转到文件 - >打开，您将获得一个向导窗口，允许您配置解析选项，包括分隔符。

If you double-click the file and it automatically opens in Excel, Excel will use a default set of configuration options to parse the text.

If you open Excel first, then go File -> Open, you'll get a wizard window that allows you to configure the parsing options, including the separator characters.

如果可能，请使用另一种不依赖于当前区域设置的格式。这是众所周知的CSV格式限制。



您可以手动打开任何CSV文件....但自动打开将始终使用系统定义的分隔符。这同样适用于日期格式（分隔符和顺序）。



一种选择是使用Open XML SDK创建真实的Excel文件。虽然它是免费的，但它的级别非常低，需要深入了解XLSX文件格式和大量测试。



否则，您可能会使用其他格式，如XML （甚至HTML），Office Interop（如果你不是在服务器上运行并且确定客户端已经安装了Excel）购买了一些支持Excel文件的组件。



如果您想使用CSV，则应该在导出时使用系统设置。如果相同（或系统本地在两个系统上都相同），它应该工作很久就有源计算机和目标计算机。或者您可以询问用户使用哪个分隔符。在这种情况下，您可以在用户手册中对这些问题进行一些解释，并可能对如何在Excel中手动导入数据进行一些解释。



实际上，CSV文件是一个糟糕的选择，因为它不是一个定义良好的格式，因为字段分隔符，日期排序，数字和日期分隔符都取决于按国家/语言更改的系统设置。

If possible use another format that does not depends on current locale. It is a well known limitation of CSV format.

You can open any CSV file manually.... but automatic opening will always use system defined separator. The same would apply to date format (separator and order).

One option is to use Open XML SDK to create real Excel file. Although it is free, it is very low level and require a deep understanding of XLSX file format and a lot of testing.

Otherwise, you might use other format like XML (or even HTML), Office Interop (if you are not runniong on a server and are sure that the client has Excel installed) of buy some component that do support Excel files.

If you want to stay with CSV, you should probably use system settings when exporting. It should work has long has the source and target computer if the same (or the system local is the same on both system). Or you can ask the user which separator to uses. In such case, you might put some explaination in the user manual about those issues and maybe some explaination on how to manually import data in Excel.

In reality, CSV files are a bad choice as it is not a well defined format as field separator, date ordering, number and date separator all depends on system settings which changes by country/language.

这篇关于为什么Microsoft Excel不能打开.csv文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！