通过上传excel导入数据到数据库

string savePath = Server.MapPath("~/upload/CLDR");

        try
        {
            //判断是否选择了文件
            if (fileExcel.HasFile)
            {
                //检查服务器上是否存在这个物理路径，如果不存在则创建
                if (!System.IO.Directory.Exists(savePath))
                {
                    System.IO.Directory.CreateDirectory(savePath);
                }

                string fileName = fileExcel.PostedFile.FileName; //得到上传文件的全路径 "C://材料信息表.xls"
                int index = fileName.LastIndexOf(".");
                if (index > 0)
                {
                    if (fileName.Substring(index) == ".xls")
                    {
                        ViewState["fileName"] = savePath + "//" + fileExcel.FileName;
                        fileExcel.SaveAs(ViewState["fileName"].ToString());//保存文件
                        DataSet ds = ImportExcel(ViewState["fileName"].ToString(), "Sheet1");
                        if (ds.Tables[0].Columns.Count < 2)
                        {
                            Response.Write("<script>alert('文件格式不正确！');</script>");
                            return;
                        }

                        try
                        {
                            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                            {
                                string strSSLB = ds.Tables[0].Rows[i]["所属类别"].ToString().Replace("&nbsp;", "");
                                string strCLMC = ds.Tables[0].Rows[i]["材料名称"].ToString().Replace("&nbsp;", "");
                                string strGGXH = ds.Tables[0].Rows[i]["规格型号"].ToString().Replace("&nbsp;", "");
                                string strJHJG = ds.Tables[0].Rows[i]["计划价格"].ToString().Replace("&nbsp;", "");
                                string strDW = ds.Tables[0].Rows[i]["单位"].ToString().Replace("&nbsp;", "");

                                //组合材料信息编码
                                string clxxId = "";
                                string idsql = "select * from wz_clxx where PID='" + pid + "'";
                                DataSet idds = general.Select(idsql);
                                if (idds.Tables[0].Rows.Count > 0)
                                {
                                    switch (Convert.ToString(idds.Tables[0].Rows.Count + 1).Length)
                                    {
                                        case 1:
                                            clxxId = pid + "00" + Convert.ToString(idds.Tables[0].Rows.Count + 1); //一级往后加00
                                            break;
                                        case 2:
                                            clxxId = pid + "0" + Convert.ToString(idds.Tables[0].Rows.Count + 1); //二级往后加0
                                            break;
                                        default:
                                            clxxId = pid + Convert.ToString(idds.Tables[0].Rows.Count + 1); //再多级直接加
                                            break;
                                    }
                                }
                                else
                                {
                                    clxxId = pid + "001";
                                }

                                //根据计量单位名称查询其编码
                                string jldwSql = "select * from XT_ZDK where codingClass='JLDW' and Name='" + strDW + "'";
                                DataSet jldwDS = general.Select(jldwSql);
                                //当该单位名称已经登记时，直接存储，否则，略过该条信息
                                if (jldwDS.Tables[0].Rows.Count > 0)
                                {
                                    string jldwID = jldwDS.Tables[0].Rows[0]["ID"].ToString();
                                    string sql = "";
                                    sql += "declare @count int ";
                                    sql += "select @count=count(*) from wz_clxx where name='" + strCLMC + "' and Materialmodel='" + strGGXH + "' and state=0 ";
                                    sql += "if @count=0 begin ";
                                    sql += "insert into wz_clxx (id,PID,name,Materialmodel,Price,Unit,Appenddate) values ('" + clxxId + "','" + pid + "','" + strCLMC + "','" + strGGXH + "'," + Convert.ToDouble(strJHJG) + ",'" + jldwID + "','" + DateTime.Now.Date + "') ";
                                    sql += "end";
                                    general.Update(true, sql);
                                }
                            }
                        }
                        catch
                        {
                            Response.Write("<script>alert('导入不成功：该EXCEL文档表头信息有错误，应为[所属类别]、[材料名称]、[规格型号]、[计划价格]、[单位]！');</script>");
                            return;
                        }
                    }
                    else
                    {
                        Response.Write("<script>alert('请选择上报.xls文件格式！');</script>");
                        return;
                    }
                }
                Page.ClientScript.RegisterClientScriptBlock(Page.GetType(), "filescript", "window.top.callBack('" + fileName + "');", true);
                File.Delete(ViewState["fileName"].ToString());
                Response.Write("<script>alert('数据导入成功！');</script>");
            }
        }
        catch (Exception ex)
        {
            //Response.Write("<script>alert('导入失败，上传目录属性为只读！');</script>");
            Response.Write(ex.ToString());
            return;
        }

 

public DataSet ImportExcel(string strFileName, string sheet)
    {
        DataSet ds = new DataSet();
        if (strFileName != "")
        {
            string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + ";Extended Properties=/"Excel 8.0;IMEX=1/" ";
            string sql = "select *  from [" + sheet + "$]";
            OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);

            try
            {
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                Response.Write("数据格式不对!" + ex.Message);
            }
            return ds;
        }
        else
        {
            return null;
        }
    }