将excel数据导入到GridView与将GridView数据导出到excel，这个功能应该是OA或ERP系统中经常会用到的功能吧，虽然具体到项目中，使用的控件不一定是GridView，不过我想，无论什么样的富控件，应该都支持以DataTable为数据源吧，这样，我们解决列表控件与Excel数据交换，就有很多共同之处。
一、从Execl中导入数据到GridView.
这个比较oledb来实现相当的简单：
private DataSet CreateDataSource()
{
string strCon;
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("excel.xls") + "; Extended Properties=Excel 8.0;";
OleDbConnection olecon = new OleDbConnection(strCon);
OleDbDataAdapter myda = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strCon);
DataSet myds = new DataSet();
myda.Fill(myds);
return myds;
}
protected void Button1_Click(object sender, EventArgs e)
{
GridView1.DataSource = CreateDataSource();
GridView1.DataBind();
}
使用Oledb连接对Excel的全部操作，请参考本站以下文章：
使用OleDb，让Excel操作与数据库一样简单二、然后是将GridView数据导出到Excel中。
在页面增加一个按钮，在单击事件中添加如下方法：
protected void Button1_Click(object sender, EventArgs e)
{
Export("application/ms-excel", "excel地址");
}
private void Export(string FileType, string FileName)
{
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF7;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
Response.ContentType = FileType;
this.EnableViewState = false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
GridView1.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
}
//如果没有下面方法会报错类型“GridView”的控件“GridView1”必须放在具有 runat=server 的窗体标记内
public override void VerifyRenderingInServerForm(Control control)
{
}
还有因为对Excel操作，属于文件操作，所以要引入名称空间IO和Text
全部后台代码如下：
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Text;
public partial class Default1 : System.Web.UI.Page
{
SqlConnection sqlcon;
SqlCommand sqlcom;
string strCon = "Data Source=(local);Database=数据库;Uid=sa;Pwd=sa";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bind();
}
}
public void bind()
{
string sqlstr = "select * from 数据库";
sqlcon = new SqlConnection(strCon);
SqlDataAdapter myda = new SqlDataAdapter(sqlstr, sqlcon);
DataSet myds = new DataSet();
sqlcon.Open();
myda.Fill(myds, "数据库");
GridView1.DataSource = myds;
GridView1.DataKeyNames = new string[] { "姓名" };
GridView1.DataBind();
sqlcon.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
Export("application/ms-excel", "要导出的excel地址");
}
private void Export(string FileType, string FileName)
{
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF7;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
Response.ContentType = FileType;
this.EnableViewState = false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
GridView1.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
}
}
前台主要代码如下：
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" >
<Columns >
<asp:BoundField DataField="姓名" HeaderText="姓名" / >
<asp:BoundField DataField="家庭住址" HeaderText="家庭住址" / >
<asp:BoundField DataField="邮政编码" HeaderText="邮政编码" / >
<asp:BoundField DataField="出生日期" HeaderText="邮政编码" / >
</Columns >
</asp:GridView >
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="导出" / >
以上代码来自互联网，并没有经过本人测试，不保证源码的正确性，但我想从以上的源码中，足以看出此类功能的解题思路，如果提供的源码有纰漏，大家稍微修正即可。