将excel导入sql数据库

[lvxiangqun86]

又要麻烦大家了，现在有个急需要解决的问题。
我在数据库建了一个产品信息表，包括产品名，产品序列号，mac号，ap号，现在我有mac和ap号的excel列表，我的目的就是想把excel中的mac何ap对应的导入到数据库中MAC和AP列中。那里有好的方法，继续解决，有图片或文字描述的就太好了，谢谢了。

[bdmh]

有啥好方法，操作excel吗，方法自己去查吗

[MSDNXGH]

应该不能吧。。

[generhappy]

全部源代码 你自己看吧

public DataSet ExecleDs(string filenameurl, string table)
    {       
        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();
    }

[lsy51213]

insert into 表 select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=C:\Documents and Settings\Administrator\桌面\整理文件\汇总表.xls',sheet1$)
运行以上OK
我就是用这个的

[porschev]

http://www.dotblogs.com.tw/dislin/archive/2010/07/02/16331.aspx

这种问题可以自己百度、google很多人的博客里都有例子

[qwh1998]

不错，修改一下，还是能借用的，谢了

[wuyq11]

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
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();
}

[神码浮云]

不错不错..

[shine_fly]

[Quote=引用 7 楼 qwh1998 的回复:]

不错，修改一下，还是能借用的，谢了
[/Quote]

直接搞这导.

[liweifeng1986]

收藏待以后用到

[happy664618843]

http://blog.csdn.net/happy664618843/archive/2010/06/23/5689478.aspx最近做的 一个例子 可将100个 sheet数据导入到sqlserver中

[lw402365015]

好像这个也有版本的区分吧？