将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">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="head1" runat="server">
<title>无标题页</title>
<style type="text/css">
.style1
{
height: 16px;
}
.style3
{
height: 23px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
<table align="center" border="1" bordercolor="honeydew" cellpadding="0"
cellspacing="0">
<tr>
<td style="font-size: 9pt; color: #ff0000; background-color: #ff9933; text-align: center"
class="style1">
</td>
<td colspan="2"
style="font-size: 9pt; color: #ffffff; height: 16px; background-color: #ff9933; text-align: center">
将excel数据写入access数据库中</td>
</tr>
<tr>
<td style="background-color: #ffffcc; text-align: center">
</td>
<td style="background-color: #ffffcc; text-align: center">
<iframe id="i1" name="i1" scrolling="yes" src="学生成绩.xls"
style="width: 407px; height: 280px"></iframe>
</td>
<td style="width: 190px; background-color: #ffffcc; text-align: center">
<asp:gridview id="gridview1" runat="server" cellpadding="4" font-size="9pt"
forecolor="#333333" gridlines="none" width="228px">
<footerstyle backcolor="#990000" font-bold="true" forecolor="white" />
<rowstyle backcolor="#fffbd6" forecolor="#333333" />
<selectedrowstyle backcolor="#ffcc66" font-bold="true" forecolor="navy" />
<pagerstyle backcolor="#ffcc66" forecolor="#333333" horizontalalign="center" />
<headerstyle backcolor="#990000" font-bold="true" forecolor="white" />
<alternatingrowstyle backcolor="white" />
</asp:gridview>
</td>
</tr>
<tr>
<td style="background-color: #ff9900; text-align: center" class="style3">
</td>
<td style="height: 23px; background-color: #ff9900; text-align: center"
valign="top">
<asp:button id="button3" runat="server" font-size="9pt" onclick="button1_click"
text="excel数据写入access数据库中" />
<asp:label id="label1" runat="server" text="label" visible="false"
style="font-size: x-small"></asp:label>
</td>
<td style="width: 190px; height: 23px; background-color: #ff9900; text-align: center">
<asp:button id="button2" runat="server" font-size="9pt" onclick="button2_click"
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();
}
}
推荐阅读
-
将Excel中数据导入到Access数据库中的方法
-
将Access数据库中数据导入到SQL Server中的详细方法实例
-
.net实现oracle数据库中获取新插入数据的id的方法
-
Java从数据库中读取Blob对象图片并显示的方法
-
ASP.NET web.config中数据库连接字符串connectionStrings节的配置方法
-
将Excel中数据导入到Access数据库中的方法
-
将Access数据库中数据导入到SQL Server中的详细方法实例
-
.net实现oracle数据库中获取新插入数据的id的方法
-
Asp.net中把Excel数据存储至SQL Server中的具体实现方法
-
Java从数据库中读取Blob对象图片并显示的方法