商业源码 .net ,如何网页实现客户端的excel的数据导入到服务器中sql数据库中？
之前做过一次，在网上找到的人家的源码，自己加强了一下健壮性，你试试看能不能用。下面的是后台的逻辑代码！没有分层，所有代码是混在一块的
using System;
using SystemConfiguration;
using SystemData;
using SystemWeb;
using SystemWebSecurity;
using SystemWebUI;
using SystemWebUIHtmlControls;
using SystemWebUIWebControls;
using SystemWebUIWebControlsWebParts;
using SystemDataOleDb;
using SystemDataSqlClient;
public partial class ImportSCI : SystemWebUIPage
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
//确保已经选择了待导入的文件，首先上传，然后在服务器端完成导入
if (thisfuOpenPostedFileFileName != "")
{
//确保文件是excel格式
//ResponseWrite(thisfuOpenFileNameSubstring(thisfuOpenFileNameLastIndexOf('')));
if (thisfuOpenFileNameSubstring(thisfuOpenFileNameLastIndexOf('') + 1) == "xls")
{
Random rd = new Random(1);
string filename = DateTimeNowDateToString("yyyymmdd") + DateTimeNowToLongTimeString()Replace(":", "") + rdNext(9999)ToString() + "xls";
try
{
thisfuOpenPostedFileSaveAs(@ServerMapPath("fileupload/") + filename);
}
catch (HttpException he)
{
ResponseWrite("文件上传不成功，请检查文件是否过大，是否有写权限！");
return;
}
#region --------读取文件内容到服务器内存----------
string conn = " Provider = MicrosoftJetOLEDB40 ; Data Source =" + ServerMapPath("fileupload") + "/" + filename + ";Extended Properties=Excel 80";
OleDbConnection thisconnection = new OleDbConnection(conn);
thisconnectionOpen();
//要保证字段名和excel表中的字段名相同
string Sql = "select from [Sheet1$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);
DataSet ds = new DataSet();
mycommandFill(ds, "[Sheet1$]");
thisconnectionClose();
thisGridView1DataSource = ds;
thisGridView1DataBind();
#endregion
#region --------插入到数据库中---------
string conn1 = "Server=(local);database=QK;Uid=sa1;Pwd=1234";
SqlConnection thisconnection1 = new SqlConnection(conn1);
thisconnection1Open();
int count = dsTables["[Sheet1$]"]RowsCount;
for (int i = 0; i < count; i++)
{
string ID,TITLE, ISSN, ei, DL, XL;
ID = dsTables["[Sheet1$]"]Rows[i]["ID"]ToString();
TITLE = dsTables["[Sheet1$]"]Rows[i]["TITLE"]ToString();
ISSN = dsTables["[Sheet1$]"]Rows[i]["ISSN"]ToString();
ei = dsTables["[Sheet1$]"]Rows[i]["e"]ToString();
DL = dsTables["[Sheet1$]"]Rows[i]["DL"]ToString();
//XL = dsTables["[Sheet1$]"]Rows[i]["XL"]ToString();
//string excelsql = "insert into SCI(ID, TITLE, ISSN, e, DL, XL) values ('" + ID + "','" + TITLE + "','" + ISSN + "','" + ei + "','"+DL+"','"+XL+"') ";
string excelsql = "insert into SCI(ID, TITLE, ISSN, e, DL) values ('" + ID + "','" + TITLE + "','" + ISSN + "','" + ei + "','" + DL + "'"+") ";
SqlCommand mycommand1 = new SqlCommand(excelsql, thisconnection1);
try
{
mycommand1ExecuteNonQuery();
}
catch (SqlException ode)
{
//ResponseWrite("<b>导入过程出现异常，请检查是否需要重新导入！</b>");
ResponseWrite(odeMessageToString());
return;
}
}
ResponseWrite("更新成功");
thisconnection1Close();
#endregion
}
else
{
ResponseWrite("导入文件的格式不正确！");
}
}
else
{
ResponseWrite("您还没有选择要导入的文件！");
}
}
}
你这上下两端代码没什么关系啊，上面那段代码粘全了么，如果想输出，在最后加上：
wwbwrite()；wwbclose();
就行了，不用下面那段代码。
哈哈
刚好我也在做这个案例
<%'sql 连接驱动
dim conn,connstr,time1,time2,mdb
time1=timer
dim aa
aa=requestform("EXCEL") '获取传递过来的值
Dim StrConnect,cc
Dim objConn
Dim rs
Dim Sql
cc=0
'Excel连接驱动
aaa="provider=MicrosoftJetOLEDB40; Data Source="&aa&";Extended Properties=Excel 80"
set StrConnect=CreateObject("ADODBConnection")
StrConnectOpen aaa
Set objConn=CreateObject("ADODBConnection")
objConnOpen StrConnect
'注意 表名一定要以下边这种格试"[表名$]"书写
Set rs = ServerCreateObject("ADODBRecordset")
Sql="select from [sheet1$]"
rsOpen Sql,StrConnect,2,2
IF rsEof And RsBof Then
responseWrite("<script>alert('没有资料可以导入！');historygo(-1)</script>")
else
Do While Not rsEOF
if rs(0)<>"" then
'插入SQL2000里
set rssql=serverCreateObject("adodbrecordset")
rssqlOpen "select from a_sell where PartNo='"&rs(0)&"'",conn,1,3
if not rssqlrecordcount=0 then
responsewrite ""
else
rssqlAddNew
rssql(1)=rs(0)
rssql(2)=rs(1)
rssql(3)=rs(2)
rssql(4)=rs(3)
rssql(5)=rs(4)
rssql(6)=rs(5)
rssql(7)=rs(6)
rssql(8)=rs(7)
rssqlUpdate
end if
end if
RsMoveNext
Loop
end if
responseWrite"<script language=javascript>alert('资料导入成功！\n重复数据未导入');historygo(-1)</script>"
%>
这个问题我也遇到过,是下载的server u版本汉化不好不支持部分中文命名引起的,解决方法:上传不了的文件就先改成英文或数字的名字,总之不要用中文命名,这样可以上传以后,再改回原来的名字
如果实在嫌麻烦,就换过一个软件,或看看其他版本会不会出现这个情况啊,我原先用的70出问题的,后来换60版本就没事了
0条评论