Excel 导入到数据库中的两种方法(.net)

 

此方法是将excel上传到服务器中，在服务器中读取内容放到dataset 中，最后把excel文件删除。

        public static object ToSql()

        {

            stringerror = "";

            stringfilename = string.Empty;

       

            try

            {

                //获取文件路径

                stringuserName=System.Web.HttpContext.Current.Session["UserName"].ToString();

                stringfilePath = dbop.GetDataSet("select * from  UPLOADIMG where  addUser='" + userName + "' order by id desc").Tables[0].Rows[0]["name"].ToString();

 

                //导入到dataset

                DataSetds= ExcelDataSource(filePath, "导入格式");

             

                //插入到数据库

                AddDatasetToSQL(ds, 8);

                //删除上传的XLS文件

                if(filePath != string.Empty && System.IO.File.Exists(HttpContext.Current.Server.MapPath(filePath)))

                {

                    System.IO.File.Delete(HttpContext.Current.Server.MapPath(filePath));

                }

                returnerror;

            }

            catch(Exception exp)

            {

                returnerror = exp.Message;

            }

        }

 

        #region 批量导入到数据库 私有方法

        ///<summary>

        ///将Dataset的数据导入数据库

        ///</summary>

        ///<paramname="pds">数据集</param>

        ///<paramname="Cols">数据集列数</param>

        ///<returns></returns>

        public static boolAddDatasetToSQL(DataSet pds, int Cols)

        {

            intic, ir;

            ic = pds.Tables[0].Columns.Count;

            if(ic < Cols)

            {

                thrownew Exception("导入Excel格式错误！Excel只有" + ic.ToString() + "列");

            }

            ir = pds.Tables[0].Rows.Count;

            if(pds != null && ir > 0)

            {

                List<string> sqlProduct = newList<string>();

                List<string> sqlCK = newList<string>();

                List<string> sqlOther = newList<string>();

                for(int i = 0; i < pds.Tables[0].Rows.Count;i++)

                {

                   sqlProduct.Add(pds.Tables[0].Rows[i][0].ToString());

                   sqlProduct.Add(pds.Tables[0].Rows[i][1].ToString());

                   sqlProduct.Add(pds.Tables[0].Rows[i][2].ToString());

 

                   sqlOther.Add(pds.Tables[0].Rows[i][3].ToString());

                    sqlOther.Add(pds.Tables[0].Rows[i][4].ToString());

                   sqlOther.Add(pds.Tables[0].Rows[i][5].ToString());

                   sqlOther.Add(pds.Tables[0].Rows[i][6].ToString());

                   sqlOther.Add(pds.Tables[0].Rows[i][7].ToString());

                   sqlOther.Add(pds.Tables[0].Rows[i][8].ToString());

 

                    Add(sqlProduct, sqlCK,sqlOther);//执行SQL

                }

            }

            else

            {

                thrownew Exception("导入数据为空！");

            }

            returntrue;

        }

 

        public static void Add(List<string>sqlProduct, List<string>sqlCK, List<string>sqlOther)

        {

            //得到sql 并执行插入

            stringsqlP = string.Format("insertinto S_PRODUCT(p_col_1,p_col_2,p_col_3) values('{0}','{1}','{2}')",sqlProduct[0], sqlProduct[1], sqlProduct[2]);

            dbop.ExecNonQuery(sqlP);

 

            stringsqlproID = string.Format("select P_id from S_PRODUCT where p_col_1='{0}' andp_col_2='{1}' and p_col_3='{2}' order by P_id desc", sqlProduct[0],sqlProduct[1], sqlProduct[2]);

            stringidtemp = dbop.GetSingleValue(sqlproID).ToString();

 

            stringsqlO = string.Format("insertinto P_BeginOperation(ProductID,WarehouseNameID,WarehouseAreaID,WarehousePlaceID,Number,UnitNum,UnitNumRU)values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", idtemp,sqlOther[0], sqlOther[1], sqlOther[2], sqlOther[3], sqlOther[4], sqlOther[5]);

            dbop.ExecNonQuery(sqlO);

        }

 

//将excel转化成dataset

        public static DataSetExcelDataSource(string filepath, string sheetname)

        {

            stringstrConn;

            stringpath = HttpContext.Current.Server.MapPath(filepath);

            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=" + path + ";ExtendedProperties=Excel 8.0;";

            OleDbConnectionconn = new OleDbConnection(strConn);

            OleDbDataAdapteroada = new OleDbDataAdapter("select * from [" + sheetname + "$]", strConn);

            DataSetds = new DataSet();

            oada.Fill(ds);

            returnds;

        }

        #endregion

 

 

另一种方法是对上传的文件操作，获取excel的某行某列，循环插入到数据库：

        privatebool toSql(HttpPostedFilef, out stringsMsg)

        {

            string sql ="";

            sMsg = "";

            inti = 0;

            introwCount = 0, rowSuccessCount = 0;

            db.BeginTrans();

            try

            {

                ISheetsheet;

                try

                {

                    sheet = (new HSSFWorkbook(f.InputStream)).GetSheetAt(0);

                }

                catch

                {

                    sheet = (new HSSFWorkbook(f.InputStream)).GetSheetAt(0);

                }

                IRowheaderRow = sheet.GetRow(0);

                rowCount = sheet.LastRowNum;

           

                #endregion

                for(i = 1; i <= rowCount; i++)

                {

                    IRowrow = sheet.GetRow(i);

                    if(row == null) continue;

                    //将其他信息转换成ID  excel中有些字段需要转化成ID 保存到数据库

                    stringckmc = row.GetCell(3).ToString();

                    sql = "select ddi_value from S_DROPDOWNLIST_ITEMS whereddi_name='" + ckmc + "' andddi_ddID='ckmc'";

                    ckmc =db.GetSingleValue(sql).ToString();

 

                    stringkqmc = row.GetCell(4).ToString();

                    sql = "select ddi_value from S_DROPDOWNLIST_ITEMS whereddi_name='" + kqmc + "' andddi_ddID='cqmc'";

                    kqmc =db.GetSingleValue(sql).ToString();

 

                    stringkwmc = row.GetCell(5).ToString();

                    sql = "select ddi_value from S_DROPDOWNLIST_ITEMS whereddi_name='" + kwmc + "' andddi_ddID='kwmc'";

                    kwmc =db.GetSingleValue(sql).ToString();

                

              

 

                    //插入到期初表

 

                    stringsqlO = string.Format("insertintoP_BeginOperation(ProductID,WarehouseNameID,WarehouseAreaID,WarehousePlaceID,Number,UnitNum,UnitNumRU,CreateDate,isDeleted)values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')",productId, ckmc, kqmc, kwmc, row.GetCell(6), row.GetCell(7), row.GetCell(8), DateTime.Now, "1");

                    if(db.ExecNonQuery(sqlO) != 1)

                    {

                        sMsg = "插入货品信息失败！";

                        goto lbl_error;

                    }

                    rowSuccessCount++;

                }

                sheet = null;

 

                sMsg = "共导入" + rowSuccessCount.ToString()+ "条记录。";

            }

            catch(Exception ex)

            {

                sMsg = ex.Message;

                gotolbl_error;

            }

            db.CommitTrans();

            returntrue;

 

        lbl_error:

            db.RollbackTrans();

            returnfalse;

        }