asp.net access导入Excel

注：先上传，后导入。

/// <summary> /// 上传 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnUpload_Click(object sender, EventArgs e) { string dataUrl = ""; string message = WebUI.UpLoadFile(this.FileUpload1, "~/Excel/", out dataUrl); if (dataUrl == "") { WebUI.Alert(message, this.Page); } else { WebUI.Alert("上传成功！", this.Page); } }/// <summary> /// 导入 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnAdd_Click(object sender, EventArgs e) { DataTable dt = ExcelToDataTable("/Excel/中文.xls", this.txtName.Text.Trim().ToString()); for (int i = 0; i < dt.Rows.Count; i++) { string mfr = dt.Rows[i]["制造商"].ToString(); string pNumber = dt.Rows[i]["产品编号"].ToString(); string contents = dt.Rows[i]["产品描述"].ToString(); string pName = dt.Rows[i]["产品名称"].ToString(); string standard = dt.Rows[i]["产品规格"].ToString(); string price = dt.Rows[i]["产品单价"].ToString(); int twoTypeId = Convert.ToInt32(dt.Rows[i]["二级标题ID"].ToString()); string twoTitle = dt.Rows[i]["二级标题名称"].ToString(); int oneTypeId = Convert.ToInt32(dt.Rows[i]["一级标题ID"].ToString()); string oneTitle = dt.Rows[i]["一级标题名称"].ToString(); int sn = Convert.ToInt32(dt.Rows[i]["顺序号"].ToString()); string sql = string.Format("insert into T_Product(Mfr,PNumber,pName,Contents,Standard,Price,TwoTypeId,TwoTitle,OneTypeId,OneTitle,SN) values('{0}','{1}','{2}','{3}','{4}','{5}',{6},'{7}',{8},'{9}',{10})" , mfr, pNumber, pName, contents, standard, price, twoTypeId, twoTitle, oneTypeId, oneTitle, sn); DBHelper.IDUCommand(sql); } WebUI.Alert("导入完成！", this.Page); } public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName) { //HDR和IMEX也一定要配合使用,IMEX=1应该是将所有的列全部视为文本,HDR:YES第一行作为列名，NO表示第一行不为列名 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + urlconvertorlocal(strExcelFileName) + ";" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"; string strExcel = string.Format("select * from [{0}$]", strSheetName); //这是一种方法 //string strExcel = "select * from [sheet1$]"; DataSet ds = new DataSet(); OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn); adapter.Fill(ds, strSheetName); return ds.Tables[strSheetName]; }/// <summary> /// 转换为绝对路径 /// </summary> /// <param name="imagesurl1"></param> /// <returns></returns> private static string urlconvertorlocal(string imagesurl1) { string tmpRootDir = System.Web.HttpContext.Current.Server.MapPath(System.Web.HttpContext.Current.Request.ApplicationPath.ToString());//获取程序根目录 string imagesurl2 = tmpRootDir + imagesurl1.Replace(@"/", @"/"); //转换成绝对路径 return imagesurl2; }