ASP.NET 实现Excel文件的导入与导出

来源：互联网发布：手机数据恢复精灵微信编辑：程序博客网时间：2024/05/16 06:40

今天，写了一个关于ASP.NET 实现Excel文件的导入与导出，希望能给同学们带来一定的帮助。

页面设计代码如下：

<%@ Page Language="C#" AutoEventWireup="true" EnableEventValidation="false" CodeFile="Default.aspx.cs" Inherits="_Default" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head id="Head1" runat="server"> <title>ASP.NET 实现Excel文件的导入与导出</title> <mce:script language="javascript" type="text/javascript"></mce:script></head><body> <form id="formMain" runat="server" enctype="multipart/form-data"> <div> 要求导入的Excel文件位置： <asp:FileUpload ID="fuMainOne" runat="server" Height="20px" Width="450px" onchange="getFileUpLoadFileName(this,'hiddenContent1')" /> <input type="hidden" id="hiddenContent1" runat="server" /> <input type="hidden" id="hiddenContent2" runat="server" /> <br /> <br /> <asp:Button ID="btnGetTableName" runat="server" Text="获取该文件中所有的工作表名" Width="186px" OnClick="btnGetTableName_Click" /> <br /> <br /> 工作表的名称列表： <asp:DropDownList ID="ddlTables" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlTables_SelectedIndexChanged"> </asp:DropDownList> <br /> <br /> <asp:GridView ID="gvTemp" runat="server" BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None" Width="931px"> <FooterStyle BackColor="Tan" /> <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" /> <HeaderStyle BackColor="Tan" Font-Bold="True" /> <AlternatingRowStyle BackColor="PaleGoldenrod" /> </asp:GridView> <br /> 要求导出的数据表的名称列表：<asp:DropDownList ID="ddlOutTables" AutoPostBack="true" runat="server" OnSelectedIndexChanged="ddlOutTables_SelectedIndexChanged"> </asp:DropDownList> <br /> 要求导出的Excel文件位置： <asp:FileUpload ID="fuMainTwo" runat="server" Height="20px" Width="450px" onchange="getFileUpLoadFileName(this,'hiddenContent2')" /> <br /> <br /> <br /> <asp:Button ID="btnOut" runat="server" Text="导出数据" OnClick="btnOut_Click" /> <br /> <br /> <asp:GridView ID="gvOutTemp" runat="server" BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None" Width="932px"> <FooterStyle BackColor="Tan" /> <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" /> <HeaderStyle BackColor="Tan" Font-Bold="True" /> <AlternatingRowStyle BackColor="PaleGoldenrod" /> </asp:GridView> <br /> </div> </form></body></html>

后置代码如下：

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data.OleDb;using System.Data;using System.Data.SqlClient;public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { using (SqlConnection sqlConn = new SqlConnection("server=.;database=MyBookShop;integrated security=sspi;")) { using (SqlDataAdapter sqlAda = new SqlDataAdapter("SELECT [name] FROM sys.objects WHERE [type] = 'u'", sqlConn)) { using (DataSet dsDataSet = new DataSet()) { sqlAda.Fill(dsDataSet, "sysobjects"); if (dsDataSet.Tables.Count != 0) { this.ddlOutTables.DataSource = dsDataSet.Tables[0]; this.ddlOutTables.DataTextField = "Name"; this.ddlOutTables.DataBind(); } } } } } } protected void btnGetTableName_Click(object sender, EventArgs e) { this.ddlTables.Items.Clear(); string sFileName = this.fuMainOne.FileName; if (sFileName.Length == 0) { ClientScript.RegisterStartupScript(this.GetType(), "", "<mce:script type="text/javascript"></mce:script>"); return; } sFileName = this.hiddenContent1.Value; using (OleDbConnection oledbConn = new OleDbConnection()) { try { oledbConn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;data source=" + sFileName + ";Extended Properties=Excel 8.0;"; oledbConn.Open(); using (DataTable dtOledb = oledbConn.GetSchema("Tables")) { using (DataTableReader dtReader = new DataTableReader(dtOledb)) { while (dtReader.Read()) { this.ddlTables.Items.Add(dtReader["Table_Name"].ToString()); } } } } catch { ClientScript.RegisterStartupScript(this.GetType(), "", "<mce:script type="text/javascript"></mce:script>"); return; } } } protected void ddlTables_SelectedIndexChanged(object sender, EventArgs e) { string sFileName = this.hiddenContent1.Value; if (sFileName.Length == 0) { ClientScript.RegisterStartupScript(this.GetType(), "", "<mce:script type="text/javascript"></mce:script>"); return; } DataSet dsDataSet = ExcelDataSource(sFileName, this.ddlTables.SelectedItem.Text); if (dsDataSet != null) { this.gvTemp.DataSource = dsDataSet.Tables[0].DefaultView; this.gvTemp.DataBind(); } } public DataSet ExcelDataSource(string sFilePath, string sSheetName) { DataSet dsDataSet = null; string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFilePath + ";Extended Properties=Excel 8.0;"; using (OleDbConnection oledbConn = new OleDbConnection(strConn)) { try { using (OleDbDataAdapter oledbAda = new OleDbDataAdapter("select * from [" + sSheetName + "$]", oledbConn)) { dsDataSet = new DataSet(); oledbAda.Fill(dsDataSet); } } catch { ClientScript.RegisterStartupScript(this.GetType(), "", "<mce:script type="text/javascript"></mce:script>"); return null; } } return dsDataSet; } protected void ddlOutTables_SelectedIndexChanged(object sender, EventArgs e) { using (SqlConnection sqlConn = new SqlConnection("server=.;database=MyBookShop;integrated security=sspi;")) { using (SqlDataAdapter sqlAda = new SqlDataAdapter("SELECT * FROM " + this.ddlOutTables.SelectedItem.Text, sqlConn)) { using (DataSet dsDataSet = new DataSet()) { sqlAda.Fill(dsDataSet, this.ddlOutTables.SelectedItem.Text); if (dsDataSet.Tables.Count != 0) { this.gvOutTemp.DataSource = dsDataSet.Tables[0]; this.gvOutTemp.DataBind(); } } } } } protected void btnOut_Click(object sender, EventArgs e) { string sFileName = this.hiddenContent2.Value; if (sFileName.Length == 0) { ClientScript.RegisterStartupScript(this.GetType(), "", "<mce:script type="text/javascript"></mce:script>"); return; } ExportExcelFromGridView(sFileName, this.gvOutTemp); ClientScript.RegisterStartupScript(this.GetType(), "", "<mce:script type="text/javascript"></mce:script>"); } private void ExportExcelFromGridView(string sFilename, GridView gvToExcelGrid) { Response.Clear(); Response.Buffer = true; Response.Charset = "utf-8"; Response.AppendHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode(sFilename)); Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");//设置输出流为简体中文 Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 this.EnableViewState = false; System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true); System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); gvToExcelGrid.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.End(); } public override void VerifyRenderingInServerForm(Control control) { }}