我想在页面中实现一个功能,就是浏览一个本地的excel文件,然后点导入按钮,把excel文件中的数据导入sql2000,怎么做啊,请各位高手指教,谢谢
解决方案 »
- 有没有懂lightbox2的
- Itexsharp使用
- 在asp.net 中如何在英文操作系统下的英文版sql 中用varchar 字段保存中文汉字.
- _____问个初级问题,抱歉
- 麻烦问题:JMAIL添加邮件附件出现乱码以及不能重命名问题
- 急:怎样让页面不缓存js文件?
- 基于Exchange Server的企业应用系统设计和开发 ,请高手给于一点见意,和书籍参考
- 前后台传值取值的问题~~~~~~~~~~~~~~~
- asp.net中 HttpModel 与HttpModel
- 关于WebMatrixHosting.net提供的数据库空间的问题
- 到底是怎么回事呀??????我这3年在csdn上的问题怎么一下子给我删了!!!!!!!!!!!!!!!!!!!!!!111
- 求解DataGrid导入Excel问题! 急!!!!!!!!!
{
string mystring="Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = 'D:/ExportToExcel/excel/test.xls';Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection (mystring);
OleDbDataAdapter myDa =new OleDbDataAdapter("select * from [Sheet1$]",cnnxls);
DataSet myDs =new DataSet();
myDa.Fill(myDs);if(myDs.Tables[0].Rows.Count > 0)
{
string strSql = "";
string CnnString="Provider=SQLOLEDB;database=testnews;server=(local);uid=sa;pwd=";
OleDbConnection conn =new OleDbConnection(CnnString);
conn.Open ();
OleDbCommand myCmd =null;for(int i=0; i<myDs.Tables[0].Rows.Count; i++)
{
strSql="insert into news(title,body) values ('";
strSql += myDs.Tables[0].Rows[i].ItemArray[1].ToString() + "', '";
strSql += myDs.Tables[0].Rows[i].ItemArray[2].ToString() + "')";try
{
myCmd=new OleDbCommand(strSql,conn);
myCmd.ExecuteNonQuery();
Label8.Text = "<script language=javascript>alert('数据导入成功.');</script>";
}
catch
{
Label8.Text = "<script language=javascript>alert('数据导入失败.');</script>";
}
}
conn.Close();
}}
}
private DataSet GetCollection()
{
DataSet ds=new DataSet();
string strCon,strCmm;
//strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\irms\\tmp\\irsbd.xls;Extended Properties=Excel 8.0;";
strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath("tmp\\irsbd.xls")+";Extended Properties=Excel 8.0;";
strCmm="select distinct * from [Sheet1$]";
OleDbConnection oleCnn=new OleDbConnection(strCon);
OleDbCommand oleCmm=new OleDbCommand(strCmm,oleCnn);
OleDbDataAdapter oleDa=new OleDbDataAdapter(oleCmm);
oleDa.Fill(ds,"irsbd");return ds;}
private void PutData(DataSet ds)
{
string strCon=Application["strCon"].ToString();
string strSql="select top 1 * from irsbd";
DataSet myDs=new DataSet();
SqlDataAdapter da=new SqlDataAdapter(strSql,strCon);
da.Fill(myDs,"irsbd");
for(int i=0;i<ds.Tables[0].Rows.Count;i++)
if(ds.Tables[0].Rows[i]["sbdno"].ToString().Trim()!="")
{
DataRow dr=myDs.Tables[0].NewRow();
DataRow dr1=ds.Tables[0].Rows[i];
dr["sbdno"]=dr1["sbdno"];
dr["sbdnm"]=dr1["sbdnm"];
dr["sbdpd"]=dr1["sbdno"];
dr["sbdit"]=dr1["sbdit"];
dr["sbddt"]=dr1["sbddt"];
dr["sbdco"]=tbCo.Text.Trim();dr["sbdel"]=DateTime.Today;
dr["sbdcs"]=0;
dr["sbdas"]=0;
dr["sbdps"]=0;
dr["sbdcs1"]=0;
dr["sbdcs2"]=0;
dr["sbdcs3"]=0;
dr["sbdcs4"]=0;
dr["sbdas1"]=0;
dr["sbdas2"]=0;
dr["sbdas3"]=0;
dr["sbdas4"]=0;myDs.Tables[0].Rows.Add(dr);
}SqlCommandBuilder sqlCb=new SqlCommandBuilder(da);da.Update(myDs,"irsbd");
myDs.AcceptChanges();
}
private void ReadExcel()
{
DataTable rs=new DataTable();
rs=null;
DataSet rDs=null;
string strConn="";
int count=0;
DateTime createtime=DateTime.Now ;
string files=this.LoadfileDataGrid.SelectedItem.Cells[2].Text ;//获得文件名称 string userid=this.LoadfileDataGrid.SelectedItem.Cells[6].Text ;
string [] aa=files.Split(':');
string fileName=aa[1].ToString();
string path = Server.MapPath("../upparts/"+fileName); string filepath="..\\upparts\\"+fileName;
if (File.Exists (Server.MapPath(filepath)))//判断文件是否存在
{
strConn ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+path+";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"; //IMEX=1 用来转换文本
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]",strConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet,"[Sheet1$]");
string sqlstr;
foreach(DataRow row in myDataSet.Tables[0].Rows)
{
count++;
string classid="";
string name="";
string model="";
string price="";
string num="";
string mfg="";
string package="";
string re="";
classid=row["产品类型"].ToString();
name=row["产品名称"].ToString();
model=row["产品型号"].ToString();
price=row["产品价格"].ToString();
num=row["数 量"].ToString();
mfg=row["生产厂商"].ToString();
package=row["封 装"].ToString();
re=row["交易说明"].ToString();
if (name!="" && model!="")
{
sqlstr="insert into tab_part values('"+name+"','"+model+"','"+num+"','"+package+"','"+mfg+"','"+price+"','"+re+"','"+classid+"','"+createtime+"','"+userid+"','0')";
data1.moddata(sqlstr);
}
}
data1.Alert2 ("上传记录成功!","BatchAddParts.aspx",Page);
}
else
{
data1.Alert2("没有找到相应的模板文件","BatchAddParts.aspx",Page);
}
//return rs=rDs.Tables[0];
}
#endregion