从excel中导入数据到table中,并保存到数据库
1.下载excel模板
<a target="_blank" style="margin-right: 100px;" href="/Files/xxxx.xls">[导入模板下载]</a>
2.上传excel数据文件
3.处理上传上来的excel文件
4.将excel中的数据保存到数据库中
protected void btnUp_Click(object sender, ImageClickEventArgs e) { double djid = 0; double.TryParse(Request.QueryString["djid"], out djid); HttpFileCollection files = Request.Files;//客服端上载文件的集合 var path = Server.MapPath(ConfigManager.GetWebConfigurationManager("ExceFilePath"));//ExcelFilePath 为文件存放位置在webconfig中进行配置 if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } int ifile;//单个文件,变量 for (ifile = 0; ifile < files.Count; ifile++)//循环取出每一张图片进行操作:大小?扩展名? { HttpPostedFile postedfile = files[ifile];//posted代表一个上载图片 if (postedfile.InputStream.Length > 0) { int index = postedfile.FileName.LastIndexOf('.'); var ext = postedfile.FileName.Substring(index + 1); if (CommonHelper.IsEquals(ext, new string[] { "xls", "xlsx" })) { path = path.Trim('/').Trim('\\') + "/" + Guid.NewGuid().ToString("N") + "." + ext; postedfile.SaveAs(path); var dt = GetTableData(path); SaveData(djid, dt);//用于保存数据 //CommonHelper.DelFile(path); } } } } private DataTable GetTableData(string sExcelFile) { DataSet ds = new DataSet(); try { string stro = (sExcelFile.Contains(".xlsx") ? "Provider=Microsoft.ACE.OLEDB.12.0;" : "Provider=Microsoft.Jet.OLEDB.4.0;") + "Data source=" + sExcelFile + ";" + "Extended Properties=Excel 8.0"; //Excel的连接 OleDbConnection objConn = new OleDbConnection(stro); objConn.Open(); DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); string tableName = schemaTable.Rows[0][2].ToString().Trim();//获取 Excel 的表名,默认值是sheet1 string strSql = "select * from [" + tableName + "]"; OleDbCommand objCmd = new OleDbCommand(strSql, objConn); OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn); myData.Fill(ds, tableName);//填充数据 } catch { } return ds != null && ds.Tables.Count > 0 ? ds.Tables[0] : null; }
private void SaveData(double djid, DataTable dt) { bool flag = false; if (dt == null || djid < 1) return; try { IList<BCgqdbBase> GoodsList = new List<BCgqdbBase>(); foreach (DataRow dr in dt.Rows) { if (dr["材料名称"] == DBNull.Value) continue; var cBase = new BCgqdbBase(); cBase.CPMC = dr["物资名称"].ToString(); cBase.CSJPZ = dr["规格型号"].ToString(); cBase.DW = dr["单位"].ToString(); cBase.SL = Convert.ToDouble(dr["数量"] == null || dr["数量"].ToString() == "" ? "0" : dr["数量"]); cBase.YSXJ = Convert.ToDouble(dr["不含税单价"] == null || dr["不含税单价"].ToString() == "" ? "0" : dr["不含税单价"]); cBase.ZCS = dr["品牌"].ToString(); cBase.BZ = dr["备注"].ToString(); cBase.CODE = dr["编码"].ToString(); cBase.SLZ = Convert.ToDouble(dr["税率"] == null || dr["税率"].ToString() == "" ? "0" : dr["税率"]); GoodsList.Add(cBase); } //数据保存到数据库 } catch { } JScript.ExecutionJS(this,"top.window.location=top.window.location.href;"); }
![](https://www.cnblogs.com/images/cnblogs_com/-hao/1352120/o_zfbAndWx.jpg)
您的资助是我最大的动力!
金额随意,欢迎来赏!