将Excel中数据导入到Access数据库中的方法

Default.aspx

复制代码代码如下:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

.style1

{

height: 16px;

}

.style3

{

height: 23px;

}

</style>

</head>

<body>

<div>

</div>

<table align="center" border="1" bordercolor="honeydew" cellpadding="0"

cellspacing="0">

<tr>

<td

</td>

<td colspan="2"

将Excel数据写入Access数据库中</td>

</tr>

<tr>

<td>

</td>

<td>

></iframe>

</td>

<td>

<asp:GridView ID="GridView1" runat="server" CellPadding="4" Font-Size="9pt"

ForeColor="#333333" GridLines="None" Width="228px">

</asp:GridView>

</td>

</tr>

<tr>

<td>

</td>

<td

valign="top">

<asp:Button ID="Button3" runat="server" Font-Size="9pt"

Text="Excel数据写入Access数据库中" />

<asp:Label ID="Label1" runat="server" Text="Label" Visible="False"

></asp:Label>

</td>

<td>

<asp:Button ID="Button2" runat="server" Font-Size="9pt"

Text="数据库中显示Excel数据" />

</td>

</tr>

<tr>

<td>

</td>

</tr>

</table>

</form>

</body>

</html>

Default.aspx.cs

复制代码代码如下:

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.OleDb;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

public OleDbConnection CreateCon()

{

string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + Server.MapPath("UserScore.mdb") + ";User Id=admin;Password=;";

OleDbConnection odbc = new OleDbConnection(strconn);

return odbc;

}

protected void Button1_Click(object sender, EventArgs e)

{

//定义Excel列表

string StyleSheet = "Sheet1";

//调用自定义LoadData方法，将Excel文件中数据读到ASPNET页面中

LoadData(StyleSheet);

//定义查询的SQL语句

string sql = "select ID,用户姓名,试卷,成绩,考试时间 from Score";

//创建Oledb数据库连接

OleDbConnection con = CreateCon();

con.Open();//打开数据库连接

OleDbCommand com = new OleDbCommand(sql, con);

//开始事务

OleDbTransaction tran = con.BeginTransaction();

com.Transaction = tran;

//创建适配器

OleDbDataAdapter da = new OleDbDataAdapter(com);

OleDbCommandBuilder cb = new OleDbCommandBuilder(da);

//创建DataSet数据集

DataSet ds = new DataSet();

//填充数据集

da.Fill(ds);

int curIndex = 0;

if (ds.Tables[0].Rows.Count > 0)

{

curIndex = Convert.ToInt32(ds.Tables[0].Rows[0][0]);

}

//创建一个内存表

DataTable tb = this.getExcelDate();

string selsql = "";

for (int i = 0; i < tb.Rows.Count; i++)

{

string UserName = tb.Rows[i][0].ToString();

selsql = "select count(*) from Score where 用户姓名='" + UserName + "'";

}

//判断Excel文件中是否已经导入到Access数据库中

if (ExScalar(selsql) > 0)

{

Label1.Visible = true;

Label1.Text = "<script language=javascript>alert('该Excle中的数据已经导入数据库中!');location='Default.aspx';</script>";

}

else

{

//循环读取Excel文件中数据，并添加到Access事先创建好的数据库表中

for (int i = 0; i < tb.Rows.Count; i++)

{

DataRow dr = ds.Tables[0].NewRow();

dr[0] = ++curIndex;

dr[1] = tb.Rows[i][0];

dr[2] = tb.Rows[i][1];

dr[3] = tb.Rows[i][2];

dr[4] = tb.Rows[i][3];

ds.Tables[0].Rows.Add(dr);

}

try

{

da.Update(ds);//执行插入操作

tran.Commit();//事务提交

Label1.Visible = true;

Label1.Text = "<script language=javascript>alert('数据导入成功！');location='Default.aspx';</script>";

}

catch

{

tran.Rollback();//事务回滚

Label1.Visible = true;

Label1.Text = "<script language=javascript>alert('数据导入失败！');location='Default.aspx';</script>";

}

finally

{

con.Close();//关闭数据库连接

}

protected void Button2_Click(object sender, EventArgs e)

{

string sqlstr = "select * from Score";

OleDbConnection conn = CreateCon();

conn.Open();

OleDbCommand mycom = new OleDbCommand(sqlstr, conn);

OleDbDataReader dr = mycom.ExecuteReader();

dr.Read();

if (dr.HasRows)

{

GetDataSet(sqlstr);

}

else

{

Label1.Visible = true;

Label1.Text = "<script language=javascript>alert('数据库中没有数据信息，请先导入再查询！');location='Default.aspx';</script>";

}

dr.Close();

conn.Close();

}

public DataSet GetDataSet(string sqlstr)

{

OleDbConnection conn = CreateCon();

OleDbDataAdapter myda = new OleDbDataAdapter(sqlstr, conn);

DataSet ds = new DataSet();

myda.Fill(ds);

GridView1.DataSource = ds;

GridView1.DataBind();

return ds;

}

public DataTable getExcelDate()

{

string strExcelFileName = Server.MapPath("学生成绩.xls");

string strcon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";

string sql = "select * from [Sheet1$]";

OleDbDataAdapter da = new OleDbDataAdapter(sql, strcon);

DataSet ds = new DataSet();

da.Fill(ds);

return ds.Tables[0];

}

public void LoadData(string StyleSheet)

{

//定义数据库连接字符串 m

string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + Server.MapPath("学生成绩.xls") + ";Extended Properties=Excel 8.0";

//创建数据库连接

OleDbConnection myConn = new OleDbConnection(strCon);

//打开数据链接，得到一个数据集

myConn.Open();

//创建DataSet对象

DataSet myDataSet = new DataSet();

//定义查询的SQL语句

string StrSql = "select * from [" + StyleSheet + "$]";

//创建数据库适配器

OleDbDataAdapter myCommand = new OleDbDataAdapter(StrSql, myConn);

//填充数据集中的数据

myCommand.Fill(myDataSet, "[" + StyleSheet + "$]");

//释放占有的资源

myCommand.Dispose();

//关闭数据库连接

myConn.Close();

}

public int ExScalar(string sql)

{

OleDbConnection conn = CreateCon();

conn.Open();

OleDbCommand com = new OleDbCommand(sql, conn);

return Convert.ToInt32(com.ExecuteScalar());

conn.Close();

}