//创建一个数据链接           // string strCon =" Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0} ;Extended Properties='Excel 8.0;HDR=yes'"; //@"Provider=Microsoft.ACE.OleDb.12.0;Data Source="+path+";Extended Properties='Excel 12.0;HDR=YES'";            //  HDR=NO　即无字段             //   HDR=yes　即有字段，一般默认excel表中第1行的列标题为字段名，如姓名、年龄等             //如果您在连接字符串中指定 HDR=NO，Jet OLE DB 提供程序将自动为您命名字段（F1 表示第一个字段，F2 表示第二个字段，依此类推）；             // IMEX　表示是否强制转换为文本             //   Excel 驱动程序读取指定源中一定数量的行（默认情况下为 8 行）以推测每列的数据类型。             //如果推测出列可能包含混合数据类型（尤其是混合了文本数据的数值数据时），             //驱动程序将决定采用占多数的数据类型，并对包含其他类型数据的单元返回空值。             //（如果各种数据类型的数量相当，则采用数值类型。）             //Excel 工作表中大部分单元格格式设置选项不会影响此数据类型判断。             //可以通过指定导入模式来修改 Excel 驱动程序的此行为。             //若要指定导入模式，请在“属性”窗口中将 IMEX=1 添加到 Excel             //连接管理器的连接字符串内的扩展属性值中。         //打开连接后，查询语句//" SELECT * FROM [Sheet1$] ";      1.excel数据显示页面 2.excel数据导入到数据库中  3.excel中数据修改,新增,删除

 

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="StudentMS.aspx.cs" Inherits="WebApplication1.StudentMS" %>
     
         
      
         excle文件:
               
               
    
    
      
        
               
           
    
      
        
               
           
    
      
        
     

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data;using System.IO;using System.Data.OleDb;using System.Data.SqlClient;namespace WebApplication1{    public partial class StudentMS : System.Web.UI.Page    {        string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=yes'";        string connS = "server=STER-PC;uid=sa;pwd=123;database=t2";        protected void Page_Load(object sender, EventArgs e)        {            string fileName = "content/student.xls";            fileName = Server.MapPath(fileName);            connStr = string.Format(connStr, fileName);//连接字符串        }        private void BindList()        {            string sql = "select * from [Sheet1$]";            OleDbConnection conn = new OleDbConnection(connStr);            conn.Open();            OleDbCommand cmd = new OleDbCommand(sql, conn);            OleDbDataAdapter da = new OleDbDataAdapter(cmd);            DataTable dt = new DataTable();            da.Fill(dt);            conn.Close();            GridView1.DataSource = dt;            GridView1.DataBind();        }              //导出        protected void Button2_Click(object sender, EventArgs e)        {            //1.复制一份模板,将temp复制一份            string oldpath = Server.MapPath("content/student.xls");            string npath = Server.MapPath("content/temp.xls");            if (File.Exists(npath))            {                File.Delete(npath);            }            File.Copy(oldpath,npath );            //2.查询数据表            string sql = "select * from sheet1";            SqlConnection conn = new SqlConnection(connS);            conn.Open();            SqlCommand cmd = new SqlCommand(sql,conn);            SqlDataAdapter da = new SqlDataAdapter(cmd);            DataSet ds = new DataSet();            da.Fill(ds,"bb");            DataTable dt = ds.Tables["bb"];            //3、将数据插入到表格中            string sqlOleDb = "insert into [Sheet1$] values(@a,@b,@c)";            OleDbParameter[] pms = new OleDbParameter[3];            OleDbConnection oconn = new OleDbConnection(connStr);            oconn.Open();            OleDbCommand ocmd = new OleDbCommand(sqlOleDb,oconn);            foreach (DataRow item in dt.Rows)            {                string id = item["编号"].ToString();                string name = item["姓名"].ToString();                string sex = item["性别"].ToString();                pms[0] = new OleDbParameter("@a",id);                pms[1] = new OleDbParameter("@b",name);                pms[2] = new OleDbParameter("@c",sex);                foreach (OleDbParameter itemo in pms)                {                    ocmd.Parameters.Add(itemo);                }                int i = ocmd.ExecuteNonQuery();                            }            conn.Close();            oconn.Close();            Response.ContentType = "application/vnd.ms-excel";            Response.AddHeader("content-disposition", "attchment;filename=aaa.xls");            FileStream fs = new FileStream(Server.MapPath("content/temp.xls"), FileMode.Open, FileAccess.Read, FileShare.Read);            Stream st = Response.OutputStream;            byte[] bt = new byte[102400];            while (true)            {                int len = fs.Read(bt, 0, bt.Length);                if (len == 0) break;                st.Write(bt, 0, len);                Response.Flush();            } fs.Close();            Response.End();            Response.Write("导出成功");            #region            //int oi = 1;            //string cid = "";            //string cname = "";            //string csex = "";            ////1.把temp.xls复制一份(data.xls) File.Copy()            //File.Copy(MapPath("content/temp.xls"), MapPath("content/student55.xls"));            ////2.把数据库中的student数据查询出来            //string sqlc = "select * from sheet1";            //SqlConnection connc = new SqlConnection(connS);            //connc.Open();            //SqlCommand cmdc = new SqlCommand(sqlc, connc);            //SqlDataAdapter dac = new SqlDataAdapter(cmdc);            //DataSet dsc = new DataSet();            //DataTable dtc = dsc.Tables["aa"];            ////把查询出来的数据一条条插入到data.xls,            //foreach (DataRow item in dtc.Rows)            //{            //    cid = item["id"].ToString();            //    cname = item["name"].ToString();            //    csex = item["sex"].ToString();            //    string ofileName = "content/temp.xls";            //    ofileName = Server.MapPath(ofileName);            //    connStr = string.Format(connStr, ofileName);//连接字符串                         //    string osql = "select * from [Sheet1$]";            //    OleDbConnection oconn = new OleDbConnection(connStr);            //    oconn.Open();            //    OleDbCommand ocmd = new OleDbCommand(osql, oconn);            //    OleDbDataAdapter oda = new OleDbDataAdapter(ocmd);            //    OleDbParameter[] pm = new OleDbParameter[3];            //    pm[0] = new OleDbParameter("@a", cid);            //    pm[1] = new OleDbParameter("@b", cname);            //    pm[2] = new OleDbParameter("@c", csex);            //    DataSet ods = new DataSet();            //    oda.Fill(ods, "bb");            //    DataTable odt = ods.Tables["bb"];            //    foreach (OleDbParameter oitem in pm)            //    {            //        ocmd.Parameters.Add(oitem);            //    }            //    oi += ocmd.ExecuteNonQuery();            //    oconn.Close();            //}            //if (oi > 1)            //{            //    Response.Write("写入xls成功");            //}            //else            //{            //    Response.Write("写入xls失败");            //}            //connc.Close();            //Response.ContentType = "application/vnd.ms-excel";            //Response.AddHeader("content-disposition", "attchment;filename=aaa.zip");            //FileStream fs = new FileStream(Server.MapPath("content/temp.xls"), FileMode.Open, FileAccess.Read, FileShare.Read);            //Stream st = Response.OutputStream;            //byte[] bt = new byte[102400];            //while (true)            //{            //    int len = fs.Read(bt, 0, bt.Length);            //    if (len == 0) break;            //    st.Write(bt, 0, len);            //    Response.Flush();            //} fs.Close();            //Response.End();            ////3把data.xls发送出去            #endregion        }        //预览        protected void Button1_Click(object sender, EventArgs e)        {            BindList();        }        //导入        protected void Button3_Click(object sender, EventArgs e)        {           //1.选择的文件上传到服务器的文件夹            string type = Path.GetExtension(FileUpload1.FileName);            string fileNamae = "content/" +FileUpload1.FileName;            //保存            FileUpload1.SaveAs(Server.MapPath(fileNamae));                            //2.把刚上传的这个excel文件中的内容查询出来            string id = "";            string name = "";            string sex = "";            int i = 0;            string sql = "select * from [Sheet1$]";            OleDbConnection conn = new OleDbConnection(connStr);            conn.Open();            OleDbCommand cmd = new OleDbCommand(sql,conn);            OleDbDataAdapter da = new OleDbDataAdapter(cmd);            DataSet ds=new DataSet();            da.Fill(ds, "aa");            DataTable dt = ds.Tables["aa"];                       foreach (DataRow item in dt.Rows)            {                id = item["编号"].ToString();                name = item["姓名"].ToString();                sex = item["性别"].ToString();                                string sqli = "insert into sheet1 values(@a,@b,@c)";                SqlConnection conni = new SqlConnection(connS);                conni.Open();                SqlCommand cmdi = new SqlCommand(sqli, conni);                SqlParameter[] pm = new SqlParameter[3];                pm[0] = new SqlParameter("@a", id);                pm[1] = new SqlParameter("@b", name);                pm[2] = new SqlParameter("@c", sex);                foreach (SqlParameter item1 in pm)                {                    cmdi.Parameters.Add(item1);                }               i+= cmdi.ExecuteNonQuery();               conni.Close();            }            if (i>1)            {                Response.Write("导入成功");            }            else            {                Response.Write("导入失败");            }                        conn.Close();            //一条条的插入列sqlserver数据库中                                          }    }}