C#导出GridView的数据到Excel以及Excel导入SQL Server

C#导出GridView的数据到Excel以及Excel导入SQL Server

2010年05月30日 22:41 | 作者 潘健 | 归类于 程序设计 |

根据这几天的经历，总结一下GridView导出到Excel以及把Excel导入SQL Server的有关问题。
1. GridView导出到Excel的简单方法：直接把GridView render成.xls的文件。这种方法，其实没有输出真正的excel文件。GridView输出的依旧是html code，只不过，最后这个文件的扩展名被保存成了.xls。由于excel能够识别并展现html的table，所以这种方法得到的xls文件可以被excel打开并编辑。
1.1 导出当前页数据
导出当前页数据最简单，示例代码如下（GridView1是当前页面上用的GridView）：

1. Response.Clear();
2. Response.Buffer = true;
3. Response.Charset = "UTF-8";
4. Response.AppendHeader("Content-Disposition", "attachment;filename=exportFile.xls");
5. Response.ContentEncoding = System.Text.Encoding.UTF8;
6. Response.ContentType = "application/ms-excel";
7. System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
8. System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
9. this.GridView1.RenderControl(oHtmlTextWriter);
10. Response.Output.Write(oStringWriter.ToString());
11. Response.Flush();
12. Response.End();

不过呢，这个导出有个缺点，会把当前页面上的style全部带进去。打开下载的xls文件，会发现，里面的东西，就和你看到的html页面基本类似。如果你不想要这个style，那么可以临时生成一个GridView的对象exportableView，并把当前页面的GridView的PageIndex和PageSize赋给这个exportableView对象。但是，这里有个注意点是，你一定要把这个exportableView加入到Control collection: this.Controls.Add(exportableView)。如果不这么做，那么在exportableView调用DataBind()的时候，会跑出null pointer的异常。
1.2 导出所有数据
这个稍微复杂一些。需要借助一个临时的GridView对象，并把它设置为不分页，然后再导出这个临时的GridView对象。网上说，要在网页上弄个隐藏的层来放置这个对象。这个其实不是必要的。示例代码如下(GridView2就是一个临时的GridView对象)：

1. DataTable sourceData = generateData(); //取得源数据
2. this.GridView2.DataSource = sourceData;
3. this.GridView2.DataBind();
4. Response.Clear();
5. Response.Buffer = true;
6. Response.Charset = "UTF-8";
7. Response.AppendHeader("Content-Disposition", "attachment;filename=ExcelFileName.xls");
8. Response.ContentEncoding = System.Text.Encoding.UTF8;
9. Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件
10. System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
11. System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
12. this.GridView2.RenderControl(oHtmlTextWriter);
13. Response.Output.Write(oStringWriter.ToString());
14. Response.Flush();
15. Response.End();

用以上两种方法导出数据时，都需要重载VerifyRenderingInServerForm方法：

1. public override void VerifyRenderingInServerForm(Control control)
2. {}

另外还需要修改aspx文件，加入EnableEventValidation = “false”：

1. <%@ Page Language="C#" EnableEventValidation = "false" AutoEventWireup="true"
2. CodeFile="ExportGridView.aspx.cs" Inherits="ExportGridView" %>

上述代码，存在的问题是，数据的前导0会被excel自动删除。如果要保留前导0，则需要在导出数据的代码中加入：

1. string style = @"<style> .text { mso-number-format:"@"} </style> ";
2. Response.Write(style);

同时还要在GridView的RowBound事件中加入：

1. if (e.Row.RowType == DataControlRowType.DataRow)
2. {
3. e.Row.Cells[index].Attributes.Add("class", "text");
4. }

上述code的原理其实就是给导出的html table加上excel用于格式化数据的css。excel的用于格式化数据的css如下：

1. mso-number-format:"0" NO Decimals
2. mso-number-format:"0\.000" 3 Decimals
3. mso-number-format:"\#\,\#\#0\.000" Comma with 3 dec
4. mso-number-format:"mm\/dd\/yy" Date7
5. mso-number-format:"mmmm\ d\,\ yyyy" Date9
6. mso-number-format:"m\/d\/yy\ h\:mm\ AM\/PM" D -T AMPM
7. mso-number-format:"Short Date" 01/03/1998
8. mso-number-format:"Medium Date" 01-mar-98
9. mso-number-format:"d\-mmm\-yyyy" 01-mar-1998
10. mso-number-format:"Short Time" 5:16
11. mso-number-format:"Medium Time" 5:16 am
12. mso-number-format:"Long Time" 5:16:21:00
13. mso-number-format:"Percent" Percent - two decimals
14. mso-number-format:"0%" Percent - no decimals
15. mso-number-format:"0\.E+00" Scientific Notation
16. mso-number-format:"\@" Text
17. mso-number-format:"\#\ ???\/???" Fractions - up to 3 digits (312/943)
18. mso-number-format:"\0022&pound;\0022\#\,\#\#0\.00" &pound;12.76
19. mso-number-format:"\#\,\#\#0\.00_ \;\[Red\]\-\#\,\#\#0\.00\ " 2 decimals, negative numbers in red and signed

如果只是想导出数据来看看，并不会把导出的数据在通过OleDb的方式读取的话，那上面的方法够用了。但是，如果想让用户编辑导出的excel文件，然后上传回系统数据库，那就必须采用导出真实的excel的方法了。

2. 导出真实的excel
这个相对来说复杂一些，网上也有一些需要付费的lib可用。当然，也可用直接借用Microsoft.Office.Interop.Excel来做。下面是这样做的一个例子：

1. using Excel = Microsoft.Office.Interop.Excel;
2. ...
3. DataTable viewData = generateData(); //取得要导出的数据
4. object missing = System.Reflection.Missing.Value;
5. Excel.Application ExcelApp = new Excel.ApplicationClass();
6. int iRow = 0, iCol = 1;
7. Excel.Workbook objBook = ExcelApp.Workbooks.Add(missing);
8. Excel.Worksheet objSheet = (Excel.Worksheet)objBook.Sheets["Sheet1"];
9. objSheet.Name = "Sheet1";
10. //数字用文本形式存储，这样一些字符串的前导0不会被excel自动过滤
11. objSheet.Cells.NumberFormatLocal = "@";
12. iRow = 1;
13. List<String> keyList = new List<string>();
14. //下面是viewPrivilege 里面存的是一些key-value对，key是DataTable里面的index名字，value是显示给用户看的名字。下面的代码是在写excel文件的表头
15. ViewPrivilege viewPrivilege = getUser().ViewPrivileges[viewId];
16. foreach (string internalName in viewPrivilege.Columns.Keys)
17. {
18. objSheet.Cells[iRow, iCol++] = viewPrivilege.Columns[internalName];
19. keyList.Add(internalName);
20. }
21. //下面开始写数据，excel中，行和列的第一个是从1开始的，不是从0开始
22. foreach (DataRow row in viewData.Rows)
23. {
24. iCol = 1;
25. iRow++;
26. foreach (string key in keyList)
27. {
28. objSheet.Cells[iRow, iCol++] = row[key];
29. }
30. }
31. //让数字恢复数字的格式。否则，如果用户编辑了那些用文本形式存储的数字，并仍旧选择用文本形式存储，那么最终OleDb降无法读到这些数字，只会读到空值
32. objSheet.get_Range("H2", "O" + iRow).NumberFormatLocal = "0.000";
33. //把excel文件保存到数据库的临时文件夹
34. string fileName = "exported_" + WebUtil.GenerateStringForCurrentTime() + ".xls";
35. objBook.SaveAs( Server.MapPath("./UploadedFiles/" + fileName),
36. missing,
37. missing,
38. missing,
39. missing,
40. missing,
41. Excel.XlSaveAsAccessMode.xlExclusive,
42. missing,
43. missing,
44. missing,
45. missing,
46. missing);
47. objBook.Close(missing, missing, missing);
48. //跳转，让用户下载
49. Response.Redirect("./UploadedFiles/" + fileName);

如果希望在开新窗口让用户下载，那么可以在导出按钮上加入如下代码：

1. button.Attributes.Add("onclick", "this.form.target='_newName'");