C#导出数据到CSV和EXCEL文件，过长的数值被转义的解决方法(3)



EXCEL里面，默认的情况下，对于字段内容长度超过15位的数字，后面的内容会全部被搞成0！

例如：常见的，我们输入18位的身份证号码：111111111111111111，就会变成 111111111111111000

而且，显示也用科学计数方式显示，真是太恶心了

最近有个需求，导出一些人的个人资料到 CSV 或者 EXCEL，在导出身份证时，就遇到了这个垃圾问题。

经过一番查找资料，找到了解决方法：

1.对于导出 CSV 文件，在禁止转义的内容前，加上一个TAB符号，C# 里面是 /t

2.对于EXCEL文件， 在禁止转义的内容前，加上一个半角单引号，' 符号

如上就OK了，希望其他人少走弯路吧。

   public static void ExportCSV(System.Data.DataTable table, string path)

        {

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            if (xlApp == null)

            {

                MessageBox.Show("导出表格失败，请检查电脑是否已安装Excel。", "提示");

            }

            StreamWriter writer;

            bool comma = false;

            int columns = table.Columns.Count;

            using (writer = new StreamWriter(path, false))

            {

                int iColCount = 0;

                foreach (DataColumn col in table.Columns)

                {

                    iColCount++;

                    if (!comma)

                        comma = true;

                    else

                        writer.Write(',');

                    writer.Write(col.ColumnName);

                }

                writer.WriteLine();

                int rowColCount = 0;

                string field = string.Empty;

                foreach (DataRow row in table.Rows)

                {

                    rowColCount = 0;

                    //comma = false;

                    for (int c = 0; c < columns; c++)

                    {

                        field = "";

                        field = "\t" + row[c].ToString();

                        if (field.Contains("\""))

                        {

                            field = field.Replace("\"", "\"\"");

                        }

                        if (field.Contains(",")

                            || field.Contains(System.Environment.NewLine)

                            || field.Contains("\n")

                            || field.Contains("\r")

                            || field.Contains("\t"))

                        {

                            field = String.Format("\"{0}\"", field);

                        }

                        writer.Write(field + "");

                        rowColCount++;

                        if (rowColCount < iColCount)

                        {

                            writer.Write(",");

                        }

                    }

                    writer.WriteLine();

                }

            }

//            MyHelper.RunPath(path);

        }