又要麻烦大家了，现在有个急需要解决的问题。
我在数据库建了一个产品信息表，包括产品名，产品序列号，mac号，ap号，现在我有mac和ap号的excel列表，我的目的就是想把excel中的mac何ap对应的导入到数据库中MAC和AP列中。那里有好的方法，继续解决，有图片或文字描述的就太好了，谢谢了。
我在数据库建了一个产品信息表，包括产品名，产品序列号，mac号，ap号，现在我有mac和ap号的excel列表，我的目的就是想把excel中的mac何ap对应的导入到数据库中MAC和AP列中。那里有好的方法，继续解决，有图片或文字描述的就太好了，谢谢了。
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
odda.Fill(ds, table);
return ds;
}
protected void Button8_Click(object sender, EventArgs e)
{
string strConn = "Data Source=.;DataBase=gongnuan;Uid=sa;Pwd=sa";
if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;//当无文件时,返回
}
string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
if (IsXls != ".xls")
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
SqlConnection cn = new SqlConnection(strConn);
cn.Open();
string filename = FileUpload1.FileName; //获取Execle文件名 DateTime日期函数
string savePath = Server.MapPath(("~\\upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
DataSet ds = ExecleDs(savePath, filename); //调用自定义方法
DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else
{
for (int i = 0; i < dr.Length; i++)
{
string pininputdate = dr[i]["品保输入日期"].ToString();//日期 excel列名【名称不能变,否则就会出错】
string productscode = dr[i]["产品编号"].ToString();//编号 列名 以下类似
string productsname = dr[i]["产品名称"].ToString();
string deliverydate = dr[i]["出货日期"].ToString();
string containercode = dr[i]["货柜号"].ToString();
string productsserial = dr[i]["产品序列号"].ToString();
string deliverynum = dr[i]["出货数量N"].ToString();
string n = dr[i]["n"].ToString();
string aql25ac = dr[i]["外观AQL取25AC"].ToString();
string aql25re = dr[i]["外观AQL取25RE"].ToString();
string aql10ac = dr[i]["功能AQL取10AC"].ToString();
string aql10re = dr[i]["功能AQL取10RE"].ToString();
string qualifiednum = dr[i]["合格数"].ToString();
string unqualifiednum = dr[i]["不合格数"].ToString();
string unqualifiednumreason = dr[i]["不合格原因"].ToString();
string unqualifiedresult = dr[i]["不合格品处置结果"].ToString();
string ifbatchqualified = dr[i]["此批是否合格"].ToString();
string finalsolutionprogram = dr[i]["FFI问题纠正/预防措施"].ToString();
string shenginputdate = dr[i]["生管输入日期"].ToString();
string firstchangecontent = dr[i]["首批改动内容"].ToString();
string changeproductdatetagcode = dr[i]["改动产品数据标签号"].ToString();
string changenum = dr[i]["改动产品数量"].ToString();
string cartontag = dr[i]["外箱标记"].ToString();
string cattonlastrecord = dr[i]["外箱参考号最后一次记录"].ToString();
string shengchangedate = dr[i]["技术最后修改日期"].ToString();
string productchangecode = dr[i]["产品更改单号(MOD)"].ToString();
string guochangedate = dr[i]["国贸修改日期"].ToString();
string Detectiondate = dr[i]["Valor检测日期"].ToString();
string Detectionresult = dr[i]["检测结果是否合格"].ToString();
string valorduty = dr[i]["Valor责任"].ToString();
string focusdutyandnum = dr[i]["Focus责任及数量"].ToString();
string Unqualified_Total = dr[i]["全检后的不合格总数"].ToString();
string insertstr = "insert into fires (pininputdate,productscode,productsname,deliverydate,containercode,productsserial,deliverynum,n,aql25ac,aql25re,aql10ac,aql10re,qualifiednum,unqualifiednum,unqualifiednumreason,unqualifiedresult,ifbatchqualified,finalsolutionprogram,shenginputdate,shengchangedate,guochangedate,firstchangecontent,changeproductdatetagcode,changenum,cartontag,cattonlastrecord,productchangecode,Detectiondate,Detectionresult,valorduty,focusdutyandnum,Unqualified_Total)values(convert(datetime,'" + pininputdate + "'),'" + productscode + "','" + productsname + "',convert(datetime,'" + deliverydate + "'),'" + containercode + "','" + productsserial + "','" + deliverynum + "','" + n + "','" + aql25ac + "','" + aql25re + "','" + aql10ac + "','" + aql10re + "','" + qualifiednum + "','" + unqualifiednum + "','" + unqualifiednumreason + "','" + unqualifiedresult + "','" + ifbatchqualified + "','" + finalsolutionprogram + "',convert(datetime,'" + shenginputdate + "'),convert(datetime,'" + shengchangedate + "'),convert(datetime,'" + guochangedate + "'),'" + firstchangecontent + "','" + changeproductdatetagcode + "','" + changenum + "','" + cartontag + "','" + cattonlastrecord + "','" + productchangecode + "',convert(datetime,'" + Detectiondate + "'),'" + Detectionresult + "','" + valorduty + "','" + focusdutyandnum + "','" + Unqualified_Total + "')"; SqlCommand cmd = new SqlCommand(insertstr, cn);
try
{
cmd.ExecuteNonQuery();
}
catch (MembershipCreateUserException ex) //捕捉异常
{
Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
}
}
Response.Write("<script>alert('Excle表导入成功!');location='default.aspx'</script>");
}
cn.Close();
SqlConnection sqlcon = new SqlConnection("server=localhost;database=gongnuan;uid=sa;pwd=sa");
SqlCommand sqlcom1 = new SqlCommand();
sqlcom1.CommandType = CommandType.StoredProcedure;
sqlcom1.CommandText = "a";
SqlDataAdapter adp = new SqlDataAdapter(sqlcom1.CommandText.ToString(), sqlcon);
sqlcon.Open();
DataSet dst = new DataSet();
adp.Fill(dst, "fires");
sqlcon.Close();
}
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=C:\Documents and Settings\Administrator\桌面\整理文件\汇总表.xls',sheet1$)
运行以上OK
我就是用这个的
using(OleDbConnection OleConn = new OleDbConnection(strConn))
{
OleConn.Open();
String sql = "SELECT * FROM [Sheet1$]";
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet ds= new DataSet();
OleDaExcel.Fill(ds);
OleConn.Close();
}