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):
- Response.Clear();
- Response.Buffer = true;
- Response.Charset = "UTF-8";
- Response.AppendHeader("Content-Disposition", "attachment;filename=exportFile.xls");
- Response.ContentEncoding = System.Text.Encoding.UTF8;
- Response.ContentType = "application/ms-excel";
- System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
- System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
- this.GridView1.RenderControl(oHtmlTextWriter);
- Response.Output.Write(oStringWriter.ToString());
- Response.Flush();
- 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对象):
- DataTable sourceData = generateData(); //取得源数据
- this.GridView2.DataSource = sourceData;
- this.GridView2.DataBind();
- Response.Clear();
- Response.Buffer = true;
- Response.Charset = "UTF-8";
- Response.AppendHeader("Content-Disposition", "attachment;filename=ExcelFileName.xls");
- Response.ContentEncoding = System.Text.Encoding.UTF8;
- Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件
- System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
- System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
- this.GridView2.RenderControl(oHtmlTextWriter);
- Response.Output.Write(oStringWriter.ToString());
- Response.Flush();
- Response.End();
用以上两种方法导出数据时,都需要重载VerifyRenderingInServerForm方法:
- public override void VerifyRenderingInServerForm(Control control)
- {}
另外还需要修改aspx文件,加入EnableEventValidation = “false”:
- <%@ Page Language="C#" EnableEventValidation = "false" AutoEventWireup="true"
- CodeFile="ExportGridView.aspx.cs" Inherits="ExportGridView" %>
上述代码,存在的问题是,数据的前导0会被excel自动删除。如果要保留前导0,则需要在导出数据的代码中加入:
- string style = @"<style> .text { mso-number-format:"@"} </style> ";
- Response.Write(style);
同时还要在GridView的RowBound事件中加入:
- if (e.Row.RowType == DataControlRowType.DataRow)
- {
- e.Row.Cells[index].Attributes.Add("class", "text");
- }
上述code的原理其实就是给导出的html table加上excel用于格式化数据的css。excel的用于格式化数据的css如下:
- mso-number-format:"0" NO Decimals
- mso-number-format:"0\.000" 3 Decimals
- mso-number-format:"\#\,\#\#0\.000" Comma with 3 dec
- mso-number-format:"mm\/dd\/yy" Date7
- mso-number-format:"mmmm\ d\,\ yyyy" Date9
- mso-number-format:"m\/d\/yy\ h\:mm\ AM\/PM" D -T AMPM
- mso-number-format:"Short Date" 01/03/1998
- mso-number-format:"Medium Date" 01-mar-98
- mso-number-format:"d\-mmm\-yyyy" 01-mar-1998
- mso-number-format:"Short Time" 5:16
- mso-number-format:"Medium Time" 5:16 am
- mso-number-format:"Long Time" 5:16:21:00
- mso-number-format:"Percent" Percent - two decimals
- mso-number-format:"0%" Percent - no decimals
- mso-number-format:"0\.E+00" Scientific Notation
- mso-number-format:"\@" Text
- mso-number-format:"\#\ ???\/???" Fractions - up to 3 digits (312/943)
- mso-number-format:"\0022£\0022\#\,\#\#0\.00" £12.76
- 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来做。下面是这样做的一个例子:
- using Excel = Microsoft.Office.Interop.Excel;
- ...
- DataTable viewData = generateData(); //取得要导出的数据
- object missing = System.Reflection.Missing.Value;
- Excel.Application ExcelApp = new Excel.ApplicationClass();
- int iRow = 0, iCol = 1;
- Excel.Workbook objBook = ExcelApp.Workbooks.Add(missing);
- Excel.Worksheet objSheet = (Excel.Worksheet)objBook.Sheets["Sheet1"];
- objSheet.Name = "Sheet1";
- //数字用文本形式存储,这样一些字符串的前导0不会被excel自动过滤
- objSheet.Cells.NumberFormatLocal = "@";
- iRow = 1;
- List<String> keyList = new List<string>();
- //下面是viewPrivilege 里面存的是一些key-value对,key是DataTable里面的index名字,value是显示给用户看的名字。下面的代码是在写excel文件的表头
- ViewPrivilege viewPrivilege = getUser().ViewPrivileges[viewId];
- foreach (string internalName in viewPrivilege.Columns.Keys)
- {
- objSheet.Cells[iRow, iCol++] = viewPrivilege.Columns[internalName];
- keyList.Add(internalName);
- }
- //下面开始写数据,excel中,行和列的第一个是从1开始的,不是从0开始
- foreach (DataRow row in viewData.Rows)
- {
- iCol = 1;
- iRow++;
- foreach (string key in keyList)
- {
- objSheet.Cells[iRow, iCol++] = row[key];
- }
- }
- //让数字恢复数字的格式。否则,如果用户编辑了那些用文本形式存储的数字,并仍旧选择用文本形式存储,那么最终OleDb降无法读到这些数字,只会读到空值
- objSheet.get_Range("H2", "O" + iRow).NumberFormatLocal = "0.000";
- //把excel文件保存到数据库的临时文件夹
- string fileName = "exported_" + WebUtil.GenerateStringForCurrentTime() + ".xls";
- objBook.SaveAs( Server.MapPath("./UploadedFiles/" + fileName),
- missing,
- missing,
- missing,
- missing,
- missing,
- Excel.XlSaveAsAccessMode.xlExclusive,
- missing,
- missing,
- missing,
- missing,
- missing);
- objBook.Close(missing, missing, missing);
- //跳转,让用户下载
- Response.Redirect("./UploadedFiles/" + fileName);
如果希望在开新窗口让用户下载,那么可以在导出按钮上加入如下代码:
- button.Attributes.Add("onclick", "this.form.target='_newName'");