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

来源：互联网发布：快手段子制作软件编辑：程序博客网时间：2024/05/21 08:42

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

public staticobject 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 staticboolAddDatasetToSQL(DataSet pds,int Cols)

{

intic, ir;

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

if(ic < Cols)

{

thrownewException("导入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

{

thrownewException("导入数据为空！");

}

returntrue;

}

public staticvoid 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 staticDataSetExcelDataSource(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;

}

0 0