将EXCel数据导入到数据库里

时间:2022-11-17 12:15:51

   public DataSet ImportDataBase(string path)

{

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        conn.Open();
        System.Data.DataTable SheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
        string[] sheet = new string[SheetName.Rows.Count];
        for (int i = 0; i < SheetName.Rows.Count; i++)
        {
            sheet[i] = SheetName.Rows[i]["TABLE_NAME"].ToString();
        }

        OleDbDataAdapter myCommand = null;
        //DataTable dt = new DataTable();
        DataSet ds = new DataSet();
        string strExcel = "select * from [" + sheet[0] + "]";
        myCommand = new OleDbDataAdapter(strExcel, strConn);
        myCommand.Fill(ds);
        conn.Close();
        return ds;

}

 

 string ssjg = ds.Tables[0].Rows[i]["所属机构"].ToString().Trim();
                    string ssbm = ds.Tables[0].Rows[i]["所属部门"].ToString().Trim();
                    string khmc = ds.Tables[0].Rows[i]["客户姓名"].ToString().Trim();
                    string jbsj = ds.Tables[0].Rows[i]["接报时间"].ToString().Trim();
                    string bxdz = ds.Tables[0].Rows[i]["报修地址"].ToString().Trim();
                    string lxdh = ds.Tables[0].Rows[i]["联系电话"].ToString().Trim();
                    string bxlb = ds.Tables[0].Rows[i]["报修类别"].ToString().Trim();
                    string yysj = ds.Tables[0].Rows[i]["预约时间"].ToString().Trim();
                    string pgsj = ds.Tables[0].Rows[i]["派工时间"].ToString().Trim();
                    string bxzt = ds.Tables[0].Rows[i]["报修状态"].ToString().Trim();
                    string xxsx = ds.Tables[0].Rows[i]["详细事项"].ToString().Trim();
                    string pgdbh = ds.Tables[0].Rows[i]["派工单编号"].ToString().Trim();
                    string wxr = ds.Tables[0].Rows[i]["维修人"].ToString().Trim();
                    string kgsj = ds.Tables[0].Rows[i]["开工时间"].ToString().Trim();
                    string wcsj = ds.Tables[0].Rows[i]["完成时间"].ToString().Trim();
                    string gznr = ds.Tables[0].Rows[i]["工作内容"].ToString().Trim();
                    string yhyj = ds.Tables[0].Rows[i]["用户意见"].ToString().Trim();
                    string gzl = ds.Tables[0].Rows[i]["工作量"].ToString().Trim();
                    string sjfsfy = ds.Tables[0].Rows[i]["实际发生费用(元)"].ToString().Trim();
                    string pgdzt = ds.Tables[0].Rows[i]["派工单状态"].ToString().Trim();
                    string bz = ds.Tables[0].Rows[i]["备注"].ToString().Trim();
                    string hfkhxm = ds.Tables[0].Rows[i]["回访客户姓名"].ToString().Trim();
                    string hflxdh = ds.Tables[0].Rows[i]["回访联系电话"].ToString().Trim();
                    string fhdz = ds.Tables[0].Rows[i]["房号/地址"].ToString().Trim();
                    string hffs = ds.Tables[0].Rows[i]["回访方式"].ToString().Trim();
                    string hfsj = ds.Tables[0].Rows[i]["回访时间"].ToString().Trim();
                    string hfnr = ds.Tables[0].Rows[i]["回访内容"].ToString().Trim();
                    string yzpj = ds.Tables[0].Rows[i]["业主评价"].ToString().Trim();
                    string hfr = ds.Tables[0].Rows[i]["回访人"].ToString().Trim();
                    string yjjy = ds.Tables[0].Rows[i]["意见或建议"].ToString().Trim();
                    string hfbz = ds.Tables[0].Rows[i]["回访备注"].ToString().Trim();
                    model.ssjg = ssjg;
                    model.ssbm = ssbm;
                    model.khxm = khmc;
                    model.jbsj = jbsj;
                    model.bxdz = bxdz;
                    model.lxdh = lxdh;
                    model.bxlb = bxlb;
                    model.yysj = yysj;
                    model.pgsj = pgsj;
                    model.bxzt = bxzt;
                    model.xxsx = xxsx;
                    model.pgdbh = pgdbh;
                    model.wxr = wxr;
                    model.kgsj = kgsj;
                    model.wcsj = wcsj;
                    model.gznr = gznr;
                    model.yhyj = yhyj;
                    model.gzl = gzl;
                    model.sjfsfy = sjfsfy;
                    model.pgdzt = pgdzt;
                    model.bz = bz;
                    model.hfkhxm = hfkhxm;
                    model.hflxdh = hflxdh;
                    model.fhdz = fhdz;
                    model.hffs = hffs;
                    model.hfsj = hfsj;
                    model.hfnr = hfnr;
                    model.yzpj = yzpj;
                    model.hfr = hfr;
                    model.yjjy = yjjy;
                    model.hfnr = hfnr;
                    model.hfbz = hfbz;
                    model.sjbh = "";//预留的字段
                    model.zdbh = "";//预留的字段
                    business.Add(model);
                    Response.Write("<script language='javascript'>alert('数据成功导入到数据库!')</script>");
                    Response.Write("<script>window.location.href='bxAdd.aspx'</script>");
                }
            }
        }