前几天做了关于winform对excel文件导入导出的小例子，今天继续关于页面实现excel文件导入导出的例子。excel导入依然是OLE的基础操作流程；excel导出GridView中查询的数据。
做一个简单的界面：
页面：
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ExcelOperate.aspx.cs" Inherits="TestWeb1.WebPage.ExcelOperate" EnableEventValidation="false" %><!DOCTYPE html><html xmlns="/1999/xhtml"><head runat="server"><meta http-equiv="Content-Type" content="text/html; charset=utf-8"/><title></title></head><body><form id="form1" runat="server"><div><span>导入Excel文件</span><span>---------------------------</span><table><tr><td width="300px">请选择导入数据表名：<asp:DropDownList runat="server" ID="ExcelTableName" AutoPostBack="true"><asp:ListItem Selected="True" Value="student">学生</asp:ListItem><asp:ListItem Value="teacher">教师</asp:ListItem></asp:DropDownList></td><td><span>上传Excel文件：    </span><asp:FileUpload ID="file_upload" runat="server" /><asp:Label ID="file_label" runat="server" class="file_text"></asp:Label></td></tr><tr><td><asp:Button runat="server" Text="导入" ID="uploadExcelButton" OnClick="uploadExcelButton_Click"/></td></tr></table></div><div><div><span>查询数据</span><span>---------------------------</span></div><div style="width:300px;height:60px; float:left ">请选择查询数据表名：<asp:DropDownList runat="server" ID="QueryTableName" AutoPostBack="true"><asp:ListItem Selected="True" Value="student">学生</asp:ListItem><asp:ListItem Value="teacher">教师</asp:ListItem></asp:DropDownList></div><div style="float:left; width:600px; height:60px">查询条件：<asp:TextBox runat="server" ID="QueryCondition" Text="姓名" onclick="JavaScript:this.value=''"></asp:TextBox><asp:Button runat="server" ID="QueryButton" OnClick="QueryButton_Click" Text="查询"/></div><div style="width:1200px"><asp:GridView ID="GridView2" runat="server" AllowPaging="true" AllowSorting="true" AutoGenerateColumns="False" ><Columns><asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="true" /><asp:BoundField DataField="studentID" HeaderText="studentID" InsertVisible="False" ReadOnly="True" SortExpression="studentID" /><asp:BoundField DataField="studentName" HeaderText="studentName" SortExpression="studentName" /><asp:BoundField DataField="studentClass" HeaderText="studentClass" SortExpression="studentClass" /><asp:BoundField DataField="studentAge" HeaderText="studentAge" SortExpression="studentAge" /><asp:BoundField DataField="studentSex" HeaderText="studentSex" SortExpression="studentSex" /></Columns></asp:GridView><asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="studentID" DataSourceID="SqlDataSource1"><Columns><asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="true" /><asp:BoundField DataField="studentID" HeaderText="studentID" InsertVisible="False" ReadOnly="True" SortExpression="studentID" /><asp:BoundField DataField="studentName" HeaderText="studentName" SortExpression="studentName" /><asp:BoundField DataField="studentClass" HeaderText="studentClass" SortExpression="studentClass" /><asp:BoundField DataField="studentAge" HeaderText="studentAge" SortExpression="studentAge" /><asp:BoundField DataField="studentSex" HeaderText="studentSex" SortExpression="studentSex" /></Columns></asp:GridView><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DispatchDB %>" DeleteCommand="DELETE FROM [student] WHERE [studentID] = @studentID" InsertCommand="INSERT INTO [student] ([studentName], [studentClass], [studentAge], [studentSex]) VALUES (@studentName, @studentClass, @studentAge, @studentSex)" SelectCommand="SELECT [studentID], [studentName], [studentClass], [studentAge], [studentSex] FROM [student]" UpdateCommand="UPDATE [student] SET [studentName] = @studentName, [studentClass] = @studentClass, [studentAge] = @studentAge, [studentSex] = @studentSex WHERE [studentID] = @studentID"><DeleteParameters><asp:Parameter Name="studentID" Type="String" /></DeleteParameters><UpdateParameters><asp:Parameter Name="studentName" Type="String" /><asp:Parameter Name="studentClass" Type="String" /><asp:Parameter Name="studentAge" Type="String" /><asp:Parameter Name="studentSex" Type="String" /><asp:Parameter Name="studentID" Type="String" /></UpdateParameters><InsertParameters><asp:Parameter Name="studentName" Type="String" /><asp:Parameter Name="studentClass" Type="String" /><asp:Parameter Name="studentAge" Type="String" /><asp:Parameter Name="studentSex" Type="String" /></InsertParameters></asp:SqlDataSource></div></div><div><span>导出Excel文件</span><span>---------------------------</span><table><tr><td>请选择导出后是否打开文件：<asp:RadioButton ID="radShow1" runat="server" Text="是" Checked="true" GroupName="IsShow"/><asp:RadioButton ID="radShow2" runat="server" Text="否" GroupName="IsShow" /></td><td><asp:Button runat="server" ID="downloadButton" Text="导出" OnClick="downloadButton_Click"/></td></tr></table> </div></form></body></html>
其中"GridView1"已绑定数据源"SqlDataSource1"
导入excel文件：
/// <summary>/// Excel文件导入点击事件/// </summary>/// <param name="sender"></param>/// <param name="e"></param>protected void uploadExcelButton_Click(object sender, EventArgs e){bool fileOk = false;if (file_upload.HasFile)//验证是否包含文件{//取得文件的扩展名,并转换成小写string fileExtension = Path.GetExtension(file_upload.FileName).ToLower();//验证上传文件是否图片格式fileOk = IsExcel(fileExtension);string tableName = this.ExcelTableName.SelectedValue.Trim();if (fileOk){//对上传文件的大小进行检测，限定文件最大不超过8Mif (file_upload.PostedFile.ContentLength < 8192000){string filepath = "/Excel/ExcelUpload/";if (Directory.Exists(Server.MapPath(filepath)) == false)//如果不存在就创建file文件夹{Directory.CreateDirectory(Server.MapPath(filepath));}string virpath = filepath + Common.DataTraUtil.CreatePasswordHash(file_upload.FileName, 4) + fileExtension;//这是存到服务器上的虚拟路径string mappath = Server.MapPath(virpath);//转换成服务器上的物理路径file_upload.PostedFile.SaveAs(mappath);//保存文件//清空提示file_label.Text = "";bool ret = DoOleSql(mappath, tableName);if (ret) file_label.Text = "上传文件成功！";}else{file_label.Text = "文件大小超出8M！请重新选择！";}}else{file_label.Text = "要上传的文件类型不对！请重新选择！";}}else{file_label.Text = "请选择要上传的文件！";}}/// <summary>/// 验证是否指定的文件格式/// </summary>/// <param name="str"></param>/// <returns></returns>private bool IsExcel(string str){bool isExcel = false;string thestr = str.ToLower();//限定只能上传xls和xlsx文件string[] allowExtension = {".xls", ".xlsx" };//对上传的文件的类型进行一个个匹对for (int i = 0; i < allowExtension.Length; i++){if (thestr == allowExtension[i]){isExcel = true;break;}}return isExcel;}/// <summary>/// 读取Excel文件并入库/// </summary>/// <param name="filePath"></param>/// <param name="tableName"></param>/// <returns></returns>public bool DoOleSql(string filePath, string tableName){OleDbConnection con = null;try{string strFileType = System.IO.Path.GetExtension(filePath);string strConn = "";//Excel就好比一个数据源一般使用if (strFileType == ".xls"){// Excel 版本连接字符串strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2;'";}else{// Excel 以上版本连接字符串//HDR：Yes 表示第一行包含列名,在计算行数时就不包含第一行。NO 则完全相反。//IMEX：0 写入模式；1 读取模式；2 读写模式。如果报错为“不能修改表 sheet1 的设计。它在只读数据库中”，那就去掉这个，问题解决。strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=2;'";}con = new OleDbConnection(strConn);con.Open();//获取sheet表名集合DataTable sheetdt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] {null, null, null, "TABLE" });;for (int i = 0; i < sheetdt.Rows.Count; i++){string sheetName = sheetdt.Rows[i][2].ToString().Trim();DataTable dt = new DataTable();OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + sheetName + "]", con);oda.Fill(dt);insertExcelData(dt, tableName);}con.Close();return true;}catch (Exception ex){file_label.Text = "异常："+ex.Message;return false;}finally{con.Close();}}/// <summary>/// 将数据入库/// </summary>/// <param name="dataTable"></param>/// <param name="tableName"></param>private void insertExcelData(DataTable dataTable, string tableName){string columnNames = "";int i = 0;int count = dataTable.Columns.Count;foreach (DataColumn col in dataTable.Columns){if (i == count - 1){columnNames += "[" + col.ColumnName.Trim() + "]";}else{columnNames += "[" + col.ColumnName.Trim() + "],";}i++;}foreach (DataRow row in dataTable.Rows){string valueData = "";for (int j = 0; j < count; j++){if (j == count - 1){valueData += "'" + row[j].ToString().Trim() + "'";}else{valueData += "'" + row[j].ToString().Trim() + "',";}}string sql = "INSERT INTO [dbo].[" + tableName + "] (" + columnNames + ") VALUES (" + valueData + ")";SqlHelper sqlHelper = new SqlHelper();sqlHelper.ExecuteNonQuery(sql, CommandType.Text, null);}}
查询数据：
由于GridView1的"DataSourceID"已绑定了sqlDataSourse1，无法再绑定新查询到的dataTable到"DataSource"。所以我为了偷懒新建了GridView2来存放查询到的数据。如果想要使用一个GridView控件，只能把sqlDataSource去掉，改成在Page_Load方法中进行初始的数据查询展示。
/// <summary>/// 查询数据按钮点击事件/// </summary>/// <param name="sender"></param>/// <param name="e"></param>protected void QueryButton_Click(object sender, EventArgs e){this.GridView1.Style.Add("display","none");string SelectCommand = "SELECT [studentID], [studentName], [studentClass], [studentAge], [studentSex] FROM [student]";string studentName = this.QueryCondition.Text.Trim();if (!"".Equals(studentName) && !(studentName == null))SelectCommand += " WHERE [studentName] = '" + studentName + "'";SqlHelper sqlHelper = new SqlHelper();DataTable dt = sqlHelper.ExecuteDataTable(SelectCommand, CommandType.Text, null);this.GridView2.DataSource = dt;this.GridView2.DataBind();}
导出Excel文件：
/// <summary>/// Excel文件导出点击事件/// </summary>/// <param name="sender"></param>/// <param name="e"></param>protected void downloadButton_Click(object sender, EventArgs e){string filename = this.QueryTableName.SelectedValue;HttpContext.Current.Response.Clear();HttpContext.Current.Response.Buffer = true;HttpContext.Current.Response.Charset = "GB2312";HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + ".xlsx");HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");HttpContext.Current.Response.ContentType = "application/ms-excel";CultureInfo cultureInfo = new CultureInfo("ZH-CN", true);StringWriter stringWriter = new StringWriter(cultureInfo);HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);//将GridView控件的内容输出到htmlTextWriter对象中if(this.GridView1.Style.Keys.Count == 0)GridView1.RenderControl(htmlTextWriter);elseGridView2.RenderControl(htmlTextWriter);HttpContext.Current.Response.Write(stringWriter.ToString());//GridView1.Dispose(); HttpContext.Current.Response.End();}public override void VerifyRenderingInServerForm(Control control) //重构一次{}
问题解决：
一、在文件导出实现过程中出现了两个小问题：
1、提示GridView必须放在runat="server"的窗体中，但是已经设置了runat="server"但还是报错，问题解决如下：
只需要重写VerifyRenderingInServerForm方法，将内容清空就可以了。
public override void VerifyRenderingInServerForm(Control control){}
2、第二个问题是提示：只能在 Render(); 期间呼叫 RegisterForEventValidation：
只需要在前台页面的@Page指令中加上一个属性：EnableEventValidation=“false”
二、在文件导入获取sheet表名集合的时候遇到Workbooks.open()方法报错"HRESULT:0xFFF40004"，未找到问题所在，也没有找到解决方法。所以改用其他获取表名集合的方法：
con = new OleDbConnection(strConn);con.Open();//获取sheet表名集合DataTable sheetdt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] {null, null, null, "TABLE" });string[] sheetNames = new string[sheetdt.Rows.Count];for (int i = 0; i < sheetdt.Rows.Count; i++){string sheetName = sheetdt.Rows[i][2].ToString().Trim();sheetNames[i] = sheetName;}con.Close();
如果大家有知道HRESULT:0xFFF40004这个错误的解决方法或者更好的获取表明集合的方法，请一定告诉我，谢谢。
![[转]Asp.Net下导出/导入规则的Excel(.xls)文件](https://700zi.400zi.cn/uploadfile/img/16/4/6733946b19011ad17d0208d2fb6f2c4d.jpg)
