前台拖一个Gridview，在拖一个导出excel的按钮，给这个按钮添加事件

后台代码：

using BLL;using Model;namespace Web{    public partial class ExcelOperate : System.Web.UI.Page    {        private StudentBLL bll = new StudentBLL();        protected void Page_Load(object sender, EventArgs e)        {            if (!IsPostBack)            {                Bind();            }        }        //绑定数据        private void Bind()        {            GridView1.DataSource = bll.GetAllStu(null);            GridView1.DataBind();        }        #region 导出到excel        //导出excel        protected void btnExcelout_Click(object sender, EventArgs e)        {            string style = @"<style> .text { mso-number-format:\@; } </script> "; //设置格式            Response.ClearContent();            Response.ContentEncoding = Encoding.GetEncoding("gbk");            Response.AddHeader("content-disposition", "attachment;filename=ouput.xls");            Response.ContentType = "application/excel";            StringWriter sw = new StringWriter();            HtmlTextWriter htw = new HtmlTextWriter(sw);            GridView1.RenderControl(htw);            Response.Write(style);//注意            Response.Write(sw.ToString());            Response.End();        }        //注意：必须覆盖此方法        public override void VerifyRenderingInServerForm(Control control)        {            //base.VerifyRenderingInServerForm(control);        }        //解决数字字符串显示不完全        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)        {            // e.Row.Cells[3].Attributes.Add("class", "text");//在数据绑定中设置格式            //哪一列需要显示文本的，加上下面这句话即可            e.Row.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat:@");        }         #endregion    }}

页面效果：

导入Excel并保存到数据库：

前台需要拖一个FileUpload上传控件，一个导入excel按钮，给其添加事件：

//导入excel数据        protected void btnExcelIn_Click(object sender, EventArgs e)        {            string filepath = string.Empty;            string getErrormg = string.Empty;            DataTable dt=new DataTable();            if (!fuFile.HasFile)            {                Response.Write("<script>alert('请选择你要导入的Excel文件');</script>");                return;            }            //获取文件的后缀名            string fileExt = System.IO.Path.GetExtension(fuFile.FileName);            if (fileExt != ".xls")            {                Response.Write("<script>alert('文件类型错误！');</script>");                return;            }            //获取绝对路径            filepath = fuFile.PostedFile.FileName;            string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";Data Source=" + filepath;            OleDbConnection excelCon = new OleDbConnection(conn);            //Excel文件里面工作表名 默认为Sheet1,后面需要加上$符号[工作表名称$]切记，不然会报错            OleDbDataAdapter odda = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelCon);            try            {                odda.Fill(dt);            }            catch (Exception ex)            {                Response.Write(ex.Message);                Response.Write("<script>alert('" + ex.Message + "！')</script>");            }            finally            {                excelCon.Close();                excelCon.Dispose();            }            //将数据写到数据库里面            try            {                              for (int i = 0; i < dt.Rows.Count; i++)                {                    Studnet stu=new Studnet();                    stu.C_id = Convert.ToInt32(dt.Rows[i]["c_id"]);                    stu.No = dt.Rows[i]["no"].ToString();                    stu.Name = dt.Rows[i]["name"].ToString();                    stu.Gender = dt.Rows[i]["gender"].ToString() == "男" ? true : false;                    stu.Age = Convert.ToInt32(dt.Rows[i]["age"].ToString());                    bll.InsertStu(stu);                }            }            catch (Exception ex)            {                getErrormg = ex.Message;                Response.Write(ex.Message);            }            if (getErrormg == "")            {                Response.Write("<script>alert('导入Excel文件成功！')</script>");                Bind();            }            else { Response.Write("<script>alert('导入Excel文件失败！')</script>"); }        }

Excel和导入后的页面效果：

数据库在导入excel数据之前和时候的效果：

这里要注意几个地方，一般导出excel的时候，数字文本会把前面的0都省略掉了，这里需要注意：红色代码片段，导入的时候，也有个***红红红色***标记码块要注意

以下是前台完整代码：

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ExcelOperate.aspx.cs" Inherits="Web.ExcelOperate" %><!DOCTYPE html><html xmlns=";><head runat="server"><meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>    <title></title></head><body>    <form id="form1" runat="server">    <div>        <asp:GridView ID="GridView1" runat="server" OnRowDataBound="GridView1_RowDataBound"></asp:GridView>        <br />        <asp:Button ID="btnExcelout" runat="server" OnClick="btnExcelout_Click" Text="导出到Excel" />        <br />        <asp:FileUpload ID="fuFile" runat="server" />        <asp:Button ID="btnExcelIn" runat="server" OnClick="btnExcelIn_Click" Text="导入Excel数据" />    </div>    </form></body></html>

以下是后台完整代码：

using System;using System.Collections.Generic;using System.Data;using System.Data.OleDb;using System.IO;using System.Linq;using System.Text;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using BLL;using Model;namespace Web{    public partial class ExcelOperate : System.Web.UI.Page    {        private StudentBLL bll = new StudentBLL();        protected void Page_Load(object sender, EventArgs e)        {            if (!IsPostBack)            {                Bind();            }        }        //绑定数据        private void Bind()        {            GridView1.DataSource = bll.GetAllStu(null);            GridView1.DataBind();        }        #region 导出到excel        //导出excel        protected void btnExcelout_Click(object sender, EventArgs e)        {            string style = @"<style> .text { mso-number-format:\@; } </script> "; //设置格式            Response.ClearContent();            Response.ContentEncoding = Encoding.GetEncoding("gbk");            Response.AddHeader("content-disposition", "attachment;filename=ouput.xls");            Response.ContentType = "application/excel";            StringWriter sw = new StringWriter();            HtmlTextWriter htw = new HtmlTextWriter(sw);            GridView1.RenderControl(htw);            Response.Write(style);//注意            Response.Write(sw.ToString());            Response.End();        }        //注意：必须覆盖此方法  ***红红红色***        public override void VerifyRenderingInServerForm(Control control)        {            //base.VerifyRenderingInServerForm(control);        }        //解决数字字符串显示不完全 ***红红红色******红红红色******红红红色***        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)        {            //在数据绑定中设置格式            //哪一列需要显示文本的，加上下面这句话即可            e.Row.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat:@");        }         #endregion        //导入excel数据        protected void btnExcelIn_Click(object sender, EventArgs e)        {            string filepath = string.Empty;            string getErrormg = string.Empty;            DataTable dt=new DataTable();            if (!fuFile.HasFile)            {                Response.Write("<script>alert('请选择你要导入的Excel文件');</script>");                return;            }            //获取文件的后缀名            string fileExt = System.IO.Path.GetExtension(fuFile.FileName);            if (fileExt != ".xls")            {                Response.Write("<script>alert('文件类型错误！');</script>");                return;            }            //获取绝对路径            filepath = fuFile.PostedFile.FileName;            string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";Data Source=" + filepath;            OleDbConnection excelCon = new OleDbConnection(conn);            //默认为Sheet1,后面需要加上$符号表面为什么名称，[表名称$]，切记不然会报错            OleDbDataAdapter odda = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelCon);            try            {                odda.Fill(dt);            }            catch (Exception ex)            {                Response.Write(ex.Message);                Response.Write("<script>alert('" + ex.Message + "！')</script>");            }            finally            {                excelCon.Close();                excelCon.Dispose();            }            //将数据写到数据库里面            try            {                              for (int i = 0; i < dt.Rows.Count; i++)                {                    Studnet stu=new Studnet();                    stu.C_id = Convert.ToInt32(dt.Rows[i]["c_id"]);                    stu.No = dt.Rows[i]["no"].ToString();                    stu.Name = dt.Rows[i]["name"].ToString();                    stu.Gender = dt.Rows[i]["gender"].ToString() == "男" ? true : false;                    stu.Age = Convert.ToInt32(dt.Rows[i]["age"].ToString());                    bll.InsertStu(stu);                }            }            catch (Exception ex)            {                getErrormg = ex.Message;                Response.Write(ex.Message);            }            if (getErrormg == "")            {                Response.Write("<script>alert('导入Excel文件成功！')</script>");                Bind();            }            else { Response.Write("<script>alert('导入Excel文件失败！')</script>"); }        }           }}