<%@ 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>
导入excel数据 - www.jb200.com</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>
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="http://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;//解决数字与字符混合时,识别不正常的情况.
