asp.net中EXCEL数据导入到数据库的方法
程序员文章站
2024-02-22 14:50:16
本文实例讲述了asp.net中excel数据导入到数据库的方法。分享给大家供大家参考。具体分析如下：
excel是办公中非常常用的一个办公表格了，但我们在开发中通常会需要...
本文实例讲述了asp.net中excel数据导入到数据库的方法。分享给大家供大家参考。具体分析如下：
excel是办公中非常常用的一个办公表格了，但我们在开发中通常会需要直接把excel数据快速导入到数据库中了，这里整理了一个asp.net中excel数据导入到数据库的例子供各位参考学习。
注意：excel中的第一行不能导入。
下面是源码：intoexcel.aspx：
复制代码 代码如下:
<%@ page autoeventwireup="true" codefile="intoexcel.aspx.cs" inherits="study_intoexcel" %>
<!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>
<script language="javascript" type="text/javascript"><!--
// <!cdata[
function check() {
var k=//s+/.[xls]/;
if(!k.test(document.getelementbyid("fileid").value))
{
alert("只能上次xls格式的文件");
return false;
}
return true;
}
// --></script>
</head>
<body>
<form id="form1" runat="server">
<div>
<p>
<asp:fileupload id="fileid" runat="server" />
<asp:button id="button1" runat="server" text="上传" onclientclick="return check()" onclick="button1_click" /></p>
</div>
</form>
</body>
</html>
<!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>
<script language="javascript" type="text/javascript"><!--
// <!cdata[
function check() {
var k=//s+/.[xls]/;
if(!k.test(document.getelementbyid("fileid").value))
{
alert("只能上次xls格式的文件");
return false;
}
return true;
}
// --></script>
</head>
<body>
<form id="form1" runat="server">
<div>
<p>
<asp:fileupload id="fileid" runat="server" />
<asp:button id="button1" runat="server" text="上传" onclientclick="return check()" onclick="button1_click" /></p>
</div>
</form>
</body>
</html>
intoexcel.aspx.cs
复制代码 代码如下:
using system;
using system.collections.generic;
using system.linq;
using system.web;
using system.web.ui;
using system.collections;
using system.configuration;
using system.data;
using system.web.security;
using system.web.ui.htmlcontrols;
using system.web.ui.webcontrols.webparts;
using system.io;
using system.data.oledb;
using system.data.sqlclient;
using system.web.ui.webcontrols;
public partial class study_intoexcel : system.web.ui.page
{
protected void page_load(object sender, eventargs e)
{
}
/// <summary>
/// 上传文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void button1_click(object sender, eventargs e)
{
string filename = fileid.filename;
string savepath = server.mappath("~/file/");
fileoperatpr(filename, savepath);
fileid.saveas(savepath + filename);
dataoperator(filename, savepath);
}
/// <summary>
/// 数据操作
/// </summary>
/// <param name="filename"></param>
/// <param name="savepath"></param>
private void dataoperator(string filename, string savepath)
{
string mystring = "provider = microsoft.jet.oledb.4.0 ; data source = " + savepath + filename + ";extended properties=excel 8.0";
oledbconnection oconn = new oledbconnection(mystring);
oconn.open();
dataset ds = new dataset();
oledbdataadapter oda = new oledbdataadapter("select * from [sheet1$]", oconn);
oda.fill(ds);
oconn.close();
datasetoperator(ds,savepath+filename);
}
/// <summary>
/// 数据集操作
/// </summary>
/// <param name="ds"></param>
private void datasetoperator(dataset ds,string filepath)
{
sqlconnection conn = new sqlconnection("data source=sonysvr;initial catalog=iar_factory_811;user id=sa;password=p@ssword");
conn.open();
sqltransaction str = conn.begintransaction();//利用事务处理 防止中断
int k = 0;
if (ds.tables[0].rows.count < 1)
{
response.write("<script>alert('没有数据！')</script>");
return;
}
try
{
for (int i = 0; i < ds.tables[0].rows.count; i++)
{
string <strong><a href="//www.jb51.net" title="sql" target="_blank">sql</a></strong>str = "insert into intoexcel(tname,tage,taddress)values";
sqlstr +="('"+ ds.tables[0].rows[i][0].tostring()+"',";
sqlstr += ds.tables[0].rows[i][1].tostring()+",";
sqlstr +="'" +ds.tables[0].rows[i][2].tostring()+"')";
sqlcommand cmd = new sqlcommand(sqlstr, conn, str);
cmd.transaction = str;
k += cmd.executenonquery();
}
str.commit();
}
catch (exception ex)
{
response.write("发生异常,数据已回滚/n信息/n" + ex.message);
str.rollback();
}
finally
{
response.write("上传成功" + k + "条");
file.delete(filepath);
}
}
/// <summary>
/// 文件操作
/// </summary>
/// <param name="filename"></param>
/// <param name="savepath"></param>
private void fileoperatpr(string filename, string savepath)
{
if (!directory.exists(savepath))
{
directory.createdirectory(savepath);
}
if (file.exists(savepath + filename))
{
file.delete(savepath + filename);
}
}
}
provider=microsoft.jet.oledb.4.0;data source=" + savepath + ";extended properties='excel 8.0;hdr=yes
provider=microsoft.jet.oledb.4.0;;//连接驱动
data source=" + savepath + "; // 数据库地址
extended properties='excel 8.0; // 连接的是excel8.0
hdr=yes;// 有两个值:yes/ no, 这2个值，说了你是否能直接读列名，no，只可以读下标
imex=1;//解决数字与字符混合时,识别不正常的情况.
using system.collections.generic;
using system.linq;
using system.web;
using system.web.ui;
using system.collections;
using system.configuration;
using system.data;
using system.web.security;
using system.web.ui.htmlcontrols;
using system.web.ui.webcontrols.webparts;
using system.io;
using system.data.oledb;
using system.data.sqlclient;
using system.web.ui.webcontrols;
public partial class study_intoexcel : system.web.ui.page
{
protected void page_load(object sender, eventargs e)
{
}
/// <summary>
/// 上传文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void button1_click(object sender, eventargs e)
{
string filename = fileid.filename;
string savepath = server.mappath("~/file/");
fileoperatpr(filename, savepath);
fileid.saveas(savepath + filename);
dataoperator(filename, savepath);
}
/// <summary>
/// 数据操作
/// </summary>
/// <param name="filename"></param>
/// <param name="savepath"></param>
private void dataoperator(string filename, string savepath)
{
string mystring = "provider = microsoft.jet.oledb.4.0 ; data source = " + savepath + filename + ";extended properties=excel 8.0";
oledbconnection oconn = new oledbconnection(mystring);
oconn.open();
dataset ds = new dataset();
oledbdataadapter oda = new oledbdataadapter("select * from [sheet1$]", oconn);
oda.fill(ds);
oconn.close();
datasetoperator(ds,savepath+filename);
}
/// <summary>
/// 数据集操作
/// </summary>
/// <param name="ds"></param>
private void datasetoperator(dataset ds,string filepath)
{
sqlconnection conn = new sqlconnection("data source=sonysvr;initial catalog=iar_factory_811;user id=sa;password=p@ssword");
conn.open();
sqltransaction str = conn.begintransaction();//利用事务处理 防止中断
int k = 0;
if (ds.tables[0].rows.count < 1)
{
response.write("<script>alert('没有数据！')</script>");
return;
}
try
{
for (int i = 0; i < ds.tables[0].rows.count; i++)
{
string <strong><a href="//www.jb51.net" title="sql" target="_blank">sql</a></strong>str = "insert into intoexcel(tname,tage,taddress)values";
sqlstr +="('"+ ds.tables[0].rows[i][0].tostring()+"',";
sqlstr += ds.tables[0].rows[i][1].tostring()+",";
sqlstr +="'" +ds.tables[0].rows[i][2].tostring()+"')";
sqlcommand cmd = new sqlcommand(sqlstr, conn, str);
cmd.transaction = str;
k += cmd.executenonquery();
}
str.commit();
}
catch (exception ex)
{
response.write("发生异常,数据已回滚/n信息/n" + ex.message);
str.rollback();
}
finally
{
response.write("上传成功" + k + "条");
file.delete(filepath);
}
}
/// <summary>
/// 文件操作
/// </summary>
/// <param name="filename"></param>
/// <param name="savepath"></param>
private void fileoperatpr(string filename, string savepath)
{
if (!directory.exists(savepath))
{
directory.createdirectory(savepath);
}
if (file.exists(savepath + filename))
{
file.delete(savepath + filename);
}
}
}
provider=microsoft.jet.oledb.4.0;data source=" + savepath + ";extended properties='excel 8.0;hdr=yes
provider=microsoft.jet.oledb.4.0;;//连接驱动
data source=" + savepath + "; // 数据库地址
extended properties='excel 8.0; // 连接的是excel8.0
hdr=yes;// 有两个值:yes/ no, 这2个值，说了你是否能直接读列名，no，只可以读下标
imex=1;//解决数字与字符混合时,识别不正常的情况.
这个读入数据库的方式不是最佳的，应该用office组件
select * from [sheet1$] //引用excle文件中sheet1工作表的内容
oledb控件用的是oledb的驱动程序，可以访问各种数据库
数据库中的字段：
复制代码 代码如下:
create table intoexcel
(
tid int identity(1,1) primary key,
tname varchar(50),
tage int,
taddress varchar(200),
)
(
tid int identity(1,1) primary key,
tname varchar(50),
tage int,
taddress varchar(200),
)
sql控件用的是专用的驱动程序，能高效的访问sql server数据库
sqlconnection只能访问sql server，而oledbconnection则可以访问所有数据库。
如果只是访问sql server的话，sql比oledb更快。
希望本文所述对大家的asp.net程序设计有所帮助。