insert into sendaccount(send_tel,send_name) SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="+strdirc+";User ID=;Password=;Extended properties=Excel 5.0')...Sheet1$ 注意:EXCEL和数据库必须在同一服务器,否则会报错
用oledb吧可以像操作普通数据库一样操作excel。接下来你爱干什么就干什么。
ExcelConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileName & ";Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""" strSql = "SELECT distinct trim(F1),trim(F2) FROM [Sheet1$A2:B65535] where F1 is not null and F2 is not null and F1<>'#N/A' and F2<>'#N/A' and trim(F1)<>'' and trim(F2)<>''" cnExcel = New OleDbConnection(ExcelConnString) daExcel = New OleDbDataAdapter(strSql, cnExcel) Try daExcel.Fill(dsExcel, "list") Catch ex As OleDbException Response.Write("<script>alert('Can not open the Sheet1!')</script>") Exit Sub Catch ex As Exception '如果文件已经被打开时,不能获取文件中数据 Response.Write("<script>alert('Failure!Please check if the file is open!')</script>") Exit Sub End Try
strSql = "SELECT distinct trim(F1),trim(F2) FROM [Sheet1$A2:B65535] where F1 is not null and F2 is not null and F1<>'#N/A' and F2<>'#N/A' and trim(F1)<>'' and trim(F2)<>''"
cnExcel = New OleDbConnection(ExcelConnString)
daExcel = New OleDbDataAdapter(strSql, cnExcel) Try
daExcel.Fill(dsExcel, "list")
Catch ex As OleDbException Response.Write("<script>alert('Can not open the Sheet1!')</script>")
Exit Sub
Catch ex As Exception '如果文件已经被打开时,不能获取文件中数据
Response.Write("<script>alert('Failure!Please check if the file is open!')</script>")
Exit Sub End Try
楼上abszhanghe(贺) 说的用oledb吧可以像操作普通数据库一样操作excel,请问如何实现?能否给出一部分资料?
erwanfan(teddyxiong) 上的服务端是否必须安装excle软件?SELECT distinct trim(F1),trim(F2) 这条SQL 语句中的F1和F2 具体指的什么?能否解释一下?
谢谢!
我已经实现用oledb 将excel 文件读到dataset 中,但是从dataset读出再写到Sql数据表中,该如何实现呢?是重新建立一个连接,建立一个datareader ?如何读取呢?
#region"实施数据导入"
public bool ImportToSql(DataSet ds,string strTb,string[] arrValues,string[] arrText,bool sure,string areaId)
{
bool blnResult=false;
string strAddId="";
string strSql="";
string strSqlAddid="";
int intRent=0;
try
{
DataTable dt=ds.Tables[0];
this.DB.Open();
this.BeginTransaction();
for(int i=0;i<dt.Rows.Count;i++)
{
for(int j=0;j<dt.Columns.Count;j++)
{
arrText[j]=dt.Rows[i][j].ToString().Trim();
}
if (dt.Rows[i][4].ToString().Trim().Length>0)
{
strSql=GetStrSql(arrValues,arrText,strTb,sure);
this.SetSQL(strSql);
this.Insert();
strSql="if not exists(select * from BASE_TOOLS_ADDRESS where AddressId in(select AddressId from TEMP_TOOLS_ADDRESS "+
" where (Ltrim(Rtrim(addr1))+Ltrim(Rtrim(addr2))+Ltrim(Rtrim(addr3))+Ltrim(Rtrim(addr4))+Ltrim(Rtrim(addr5))+Ltrim(Rtrim(addr6)))="+
"'"+arrText[0].Trim()+arrText[4].Trim()+arrText[5].Trim()+arrText[6].Trim()+arrText[7].Trim()+arrText[8].Trim()+"'"+
" )) insert BASE_TOOLS_ADDRESS select AddressId,addr1,addr2,addr3,addr4,addr5,addr6 from TEMP_TOOLS_ADDRESS "+
" where (Ltrim(Rtrim(addr1))+Ltrim(Rtrim(addr2))+Ltrim(Rtrim(addr3))+Ltrim(Rtrim(addr4))+Ltrim(Rtrim(addr5))+Ltrim(Rtrim(addr6)))="+
"'"+arrText[0].Trim()+arrText[4].Trim()+arrText[5].Trim()+arrText[6].Trim()+arrText[7].Trim()+arrText[8].Trim()+"'";
this.SetSQL(strSql);
this.Insert();
}
_successfulRows=i+1;
}
strSql="drop table TEMP_TOOLS_ADDRESS";
this.SetSQL(strSql);
this.Delete();
this.CommitTransaction();
blnResult=true;
}
catch(Exception je)
{
this.RollbackTransaction();
throw je;
}
finally
{
this.DB.Close();
}
return blnResult;
}
#endregion
this.DB.Open();this.BeginTransaction();this.SetSQL(strSql);this.Delete();
this.CommitTransaction();是数据层的方法。GetStrSql(arrValues,arrText,strTb,sure)是构造字符串的一个方法:根据从dataset中读取的数据(在arrText变量中)
#region"构造插入字符串"
public string GetStrSql(string[] arrValue,string[] arrText,string strTable,bool sure)
{
string strText="";
string strSql=" Insert "+strTable+" (";
string strSelect=" select ";
//产生SQL添加数据语句
for (int i=0;i<arrValue.Length;i++)
{
if (arrValue[i].Length>0)
{
if (strText.Length==0)
{
strText+=arrValue[i].Trim();
strSelect+="'"+arrText[i].Trim()+"'";
}
else if (strText.Length>0)
{
strText+=","+arrValue[i].Trim();
if (i==(arrText.Length-1)&&sure)
{
strSelect+=",(select AddressId from TEMP_TOOLS_ADDRESS where (Ltrim(Rtrim(addr1))+Ltrim(Rtrim(addr2))+ "+
"Ltrim(Rtrim(addr3))+Ltrim(Rtrim(addr4))+Ltrim(Rtrim(addr5))+Ltrim(Rtrim(addr6)))="+
"'"+arrText[0].Trim()+arrText[4].Trim()+arrText[5].Trim()+arrText[6].Trim()+arrText[7].Trim()+arrText[8].Trim()+"')";
}
else
{
strSelect+=","+"'"+arrText[i].Trim()+"'";
}
}
}
}
if (strText.Length>0)
{
strSql+=strText+") "+strSelect;
}
return strSql;
} #endregion