//创建一个数据链接
// 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数据库中
}
}
}