asp.net导出CSV逗号分隔值文件数字自动识别问题

 问题就是excel太聪明太智能了，网站也有很多人这样说了，

比如：自动识别数字和字符串，而且要把超过11位的数字自动变为科学计数法的格式，你试试输入“123456789012”，离开那个单元格，就成“123457E+11”了，够聪明吧，不过有时会让我们感觉不便，因为我输入的就是我自己的身份证号码，尾巴上没有“X”，本来好好的15位数字，得现在成这么个计数法了。那我就改改显示格式吧，改为把数字显示为文本，好了。可是国家的身份证升级了，号码变成18位，我把它输入到数据库，导出来时，用刚才的方法处理过，18位没错，可是最后三位怎么都是零了！

excel管的事实在太多了，有的时候我们只需要按照数据表里的数据准确的显示出来，我们就需要excel傻瓜化，只要按照字符串文本的形式显示出来，方法如下：

导出CSV逗号分隔值文件：

    如果不需要自动识别数字等格式，只要文件按照字符串文本显示，

    就在文本前面加上 /t ，这样就ok了，当excel打开它的时候就知道它不是数字列了；

导出excel文件：

    在文本前面加上单引号，就ok了

下面是导出csv文件的具体方法：

语言 ：C# 2.0

1.  /// <summary>
2.         /// 按照逗号分隔值文件格式整理GridView，转换、清理超文本标记
3.         /// </summary>
4.         /// <param name="dgdData">要导出的GridView</param>
5.         /// <param name="iFromCol">要导出的开始行索引</param>
6.         /// <param name="iToCol">要导出的结束行索引</param>
7.         /// <param name="sFileName">要生成的文件名</param>
8.         /// <param name="bOnlyVisuableCol">是否只显示Visuable为true的列</param>
9.         /// <param name="sTitle">输入到文件第一行的标题</param>
10.         /// <returns></returns>
11.         public static string ExportDvToCsvFile(GridView dgdData, int iFromCol, int iToCol, string sFileName, bool bOnlyVisuableCol, string sTitle)
12.         {
13.             bool flag;
14.             Type type;
15.             Control current;
16.             string str = "";
17.             if ((dgdData.Rows.Count == 0) | (dgdData.Rows.Count == 0))
18.             {
19.                 return "未找到任何可导出的数据，此时不能导出！";
20.             }
21.             bool allowPaging = dgdData.AllowPaging;
22.             if (allowPaging)
23.             {
24.                 dgdData.AllowPaging = false;
25.                 dgdData.DataBind();
26.             }
27.             StringBuilder writer = new StringBuilder();
28.             if (StringType.StrCmp(sTitle, "", false) != 0)
29.             {
30.                 writer.AppendLine(sTitle);
31.             }
32.             int num = iToCol;
33.             int num2 = iFromCol;
34.             while (num2 <= num)
35.             {
36.                 if (!(!bOnlyVisuableCol | (bOnlyVisuableCol & dgdData.Columns[num2].Visible)))
37.                 {
38.                     goto Label_027D;
39.                 }
40.                 flag = false;
41.                 type = dgdData.Columns[num2].GetType();
42.                 if (type == typeof(HyperLinkField))
43.                 {
44.                     flag = true;
45.                 }
46.                 if (type == typeof(TemplateField))
47.                 {
48.                     IEnumerator enumerator = dgdData.Rows[0].Cells[num2].Controls.GetEnumerator();
49.                     while (enumerator.MoveNext())
50.                     {
51.                         current = (Control)enumerator.Current;
52.                         if (current.GetType() == typeof(Label))
53.                         {
54.                             flag = true;
55.                             goto Label_01CD;
56.                         }
57.                     }
58.                 }
59.             Label_01CD:
60.                 if (type == typeof(BoundField))
61.                 {
62.                     flag = true;
63.                 }
64.                 if (type == typeof(TemplateField))
65.                 {
66.                     IEnumerator enumerator2 = dgdData.Rows[0].Cells[num2].Controls.GetEnumerator();
67.                     while (enumerator2.MoveNext())
68.                     {
69.                         current = (Control)enumerator2.Current;
70.                         if (current.GetType() == typeof(TextBox))
71.                         {
72.                             flag = true;
73.                             goto Label_0256;
74.                         }
75.                     }
76.                 }
77.             Label_0256:
78.                 if (flag)
79.                 {
80.                     str = str + "/t" + ClearHtmlCont(dgdData.Columns[num2].HeaderText) + ",";
81.                 }
82.             Label_027D:
83.                 num2++;
84.             }
85.             str = Strings.Left(str, str.Length - 1);
86.             writer.AppendLine(str);
87.             int num3 = dgdData.Rows.Count - 1;
88.             string sLeft = "";
89.             for (int i = 0; i <= num3; i++)
90.             {
91.                 str = "";
92.                 int num5 = iToCol;
93.                 num2 = iFromCol;
94.                 while (num2 <= num5)
95.                 {
96.                     if (!(!bOnlyVisuableCol | (bOnlyVisuableCol & dgdData.Columns[num2].Visible)))
97.                     {
98.                         goto Label_052E;
99.                     }
100.                     type = dgdData.Columns[num2].GetType();
101.                     flag = false;
102.                     if (type == typeof(HyperLinkField))
103.                     {
104.                         IEnumerator enumerator3 = dgdData.Rows[i].Cells[num2].Controls.GetEnumerator();
105.                         while (enumerator3.MoveNext())
106.                         {
107.                             current = (Control)enumerator3.Current;
108.                             if (current.GetType() == typeof(HyperLink))
109.                             {
110.                                 sLeft = ((HyperLink)current).Text;
111.                                 flag = true;
112.                                 goto Label_0391;
113.                             }
114.                         }
115.                     }
116.                 Label_0391:
117.                     if (type == typeof(TemplateField))
118.                     {
119.                         IEnumerator enumerator4 = dgdData.Rows[i].Cells[num2].Controls.GetEnumerator();
120.                         while (enumerator4.MoveNext())
121.                         {
122.                             current = (Control)enumerator4.Current;
123.                             if (current.GetType() == typeof(Label))
124.                             {
125.                                 sLeft = ((Label)current).Text;
126.                                 flag = true;
127.                                 goto Label_0419;
128.                             }
129.                         }
130.                     }
131.                 Label_0419:
132.                     if (type == typeof(BoundField))
133.                     {
134.                         sLeft = dgdData.Rows[i].Cells[num2].Text;
135.                         flag = true;
136.                     }
137.                     if (type == typeof(TemplateField))
138.                     {
139.                         IEnumerator enumerator5 = dgdData.Rows[i].Cells[num2].Controls.GetEnumerator();
140.                         while (enumerator5.MoveNext())
141.                         {
142.                             current = (Control)enumerator5.Current;
143.                             if (current.GetType() == typeof(TextBox))
144.                             {
145.                                 sLeft = ((TextBox)current).Text;
146.                                 flag = true;
147.                             }
148.                         }
149.                     }
150.                     if (flag)
151.                     {
152.                         if (StringType.StrCmp(sLeft, " ", false) == 0)
153.                         {
154.                             sLeft = "";
155.                         }
156.                         if (num2 == 0)
157.                         {
158.                             string str5 = "";
159.                             if (StringType.StrCmp(str5, sLeft, false) == 0)
160.                             {
161.                                 sLeft = "";
162.                             }
163.                             else
164.                             {
165.                                 str5 = sLeft;
166.                             }
167.                         }
168.                         str = str + "/t" + ClearHtmlCont(sLeft.Replace("/r", " ")) + ",";
169.                     }
170.                 Label_052E:
171.                     num2++;
172.                 }
173.                 str = Strings.Left(str, str.Length - 1);
174.                 writer.AppendLine(str);
175.             }
176.             if (dgdData.ShowFooter)
177.             {
178.                 str = "";
179.                 int num6 = iToCol;
180.                 for (num2 = iFromCol; num2 <= num6; num2++)
181.                 {
182.                     str = str + "/t" + dgdData.Columns[num2].FooterText + ",";
183.                 }
184.                 str = Strings.Left(str, str.Length - 1);
185.                 writer.AppendLine(str);
186.             }
187.             if (allowPaging)
188.             {
189.                 dgdData.AllowPaging = true;
190.                 dgdData.DataBind();
191.             }
192.             return writer.ToString();
193.         }
194.         /// <summary>
195.         /// 直接输出Csv文件
196.         /// </summary>
197.         /// <param name="pgeParent">当前页面page对象</param>
198.         /// <param name="dgdData">要导出的GridView</param>
199.         /// <param name="iFromCel">要导出的开始行索引</param>
200.         /// <param name="iToCol">要导出的结束行索引</param>
201.         /// <param name="sFileName">要生成的文件名</param>
202.         /// <param name="bOnlyVisuableCol">是否只显示Visuable为true的列</param>
203.         /// <param name="sTitle">输入到文件第一行的标题</param>
204.         public static void ExportToCsvFile(ref Page pgeParent, GridView dgdData, int iFromCel, int iToCol, string sFileName, bool bOnlyVisuableCol, string sTitle)
205.         {
206.             if ((dgdData.Rows.Count == 0) | (dgdData.Rows.Count == 0))
207.             {
208.                 RunScript(ref pgeParent, "<script language=javascript>alert('未找到任何可导出的数据，此时不能导出！')</script>");
209.             }
210.             else
211.             {
212.                 HttpContext.Current.Response.Clear();
213.                 HttpContext.Current.Response.Buffer = true;
214.                 HttpContext.Current.Response.ContentType = "text/csv";//application/ms-excel
215.                 HttpContext.Current.Response.AppendHeader("Content-Disposition",
216.                                                           "attachment;filename=" +
217.                                                           HttpUtility.UrlEncode(sFileName + ".csv", Encoding.UTF8));
218.                 HttpContext.Current.Response.ContentEncoding = Encoding.Default; //Encoding.GetEncoding("GB2312");
219.                 HttpContext.Current.Response.Write(
220.                     ExportDvToCsvFile(dgdData, iFromCel, iToCol, sFileName, bOnlyVisuableCol, sTitle));
221.                 HttpContext.Current.Response.End();
222.             }
223.         }
224.         /// <summary>
225.         /// 去除html超文本标记
226.         /// </summary>
227.         /// <param name="sCont"></param>
228.         /// <returns></returns>
229.         public static string ClearHtmlCont(string sCont)
230.         {
231.             int index = sCont.IndexOf("<");
232.             if (index >= 0)
233.             {
234.                 int num2 = sCont.IndexOf(">");
235.                 if (num2 > 0)
236.                 {
237.                     sCont = sCont.Remove(index, (num2 + 1) - index);
238.                     return ClearHtmlCont(sCont);
239.                 }
240.             }
241.             return sCont;
242.         }
243.         /// <summary>
244.         /// 清除指定html超文本标记
245.         /// 注：区分大小写
246.         /// </summary>
247.         /// <param name="sCont">要清除的字符串</param>
248.         /// <param name="name">指定清除的标签名，如超连接 a </param>
249.         /// <returns></returns>
250.         public static string ClearHtmlCont(string sCont,string name)
251.         {
252.             int index = sCont.IndexOf(string.Format("<{0}",name));
253.             if (index >= 0)
254.             {
255.                 int num2 = sCont.IndexOf(">",index);
256.                 if (num2 > 0)
257.                 {
258.                     sCont = sCont.Remove(index, (num2 + 1) - index);
259.                     return ClearHtmlCont(sCont, name);
260.                 }
261.             }
262.             index = sCont.IndexOf(string.Format("</{0}", name));
263.             if (index >= 0)
264.             {
265.                 int num2 = sCont.IndexOf(">", index);
266.                 if (num2 > 0)
267.                 {
268.                     sCont = sCont.Remove(index, (num2 + 1) - index);
269.                     return ClearHtmlCont(sCont, name);
270.                 }
271.             }
272.             return sCont;
273.         }

上面是导出csv文件，不存放到服务器，直接给浏览器输出文件流

优点： 

1、随时生成，不需要占用资源 

2、可以结合身份认证 

3、同样利于数据交换 

csv文件是逗号分隔值文件，这样的文件当然有它的优点：

1、csv文件是文本文件，逗号隔开字段，回车隔开行，当直接输出到浏览器的时候，减少了很大的输出量，易于数据导入导出。 

2、也是因为生成了文件，所以占用了服务器的空间，但是可以把文件名存放到数据库，再次给客户下载的时候不需要重复生成文件。 

3、可以进行身份认证后给客户下载，如果放到非web目录就没有对应的url，客户无法随时下载。

其实你可以把同样的数据分别导出到成csv文件和excel文件，然后用记事本打开看看就知道量的差距了！

所以这里我就不说导出excel文件的方法了，网上的方法也不少！