客服电话
APP下载
迪恩网络APP
随时随地掌握行业动态
官方微信
扫描二维码
关注迪恩网络微信公众号
//创建一个数据链接
// 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" %> <asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server"> </asp:Content> <asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server"> <p> excle文件:<asp:FileUpload ID="FileUpload1" runat="server" /> <asp:Button ID="Button3" runat="server" Text="导入" onclick="Button3_Click" /> <br /> </p> <p> <asp:Button ID="Button1" runat="server" Text="预览数据库中的数据" onclick="Button1_Click" /> <asp:Button ID="Button2" runat="server" Text="导出" onclick="Button2_Click" /> </p> <p> <asp:GridView ID="GridView1" runat="server"> </asp:GridView> </p> <p> </p> </asp:Content> 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数据库中 } } }
|
请发表评论