public ActionResult InputEmployeeStopPaymentByExcel(HttpPostedFileBase files) { //int companyID = Convert.ToInt32(Request.QueryString["CompanyID"]); Common.ClientResult.Result result = new Common.ClientResult.Result(); try { #region 文件上传 HttpPostedFileBase file = files; string uploadFilePath = AppDomain.CurrentDomain.BaseDirectory + "DataExport/"; string returnMsg; string savePath = Util_XLS.UpLoadXls(file, uploadFilePath, out returnMsg); #endregion #region 读取Execl数据,验证,插入数据库 if (string.IsNullOrEmpty(returnMsg)) { //DataSet myDataSet = Util_XLS.SelectFromXLS(savePath, "select * from [Sheet1$]", out returnMsg); DataTable table = Util_XLS.NpoiReadExcle(savePath, "Sheet1", true, out returnMsg); if (string.IsNullOrEmpty(returnMsg)) { //DataTable table = myDataSet.Tables["SelectResult"].DefaultView.ToTable(); string message = string.Empty; if (ImportEmployeeAdd(table, ref message)) { result.Code = Common.ClientCode.Succeed; result.Message = "导入成功"; } else { result.Code = Common.ClientCode.Fail; result.Message = message; } } else { result.Code = Common.ClientCode.Fail; result.Message = returnMsg; } } else { result.Code = Common.ClientCode.Fail; result.Message = returnMsg; } #endregion } catch (Exception ex) { result.Code = Common.ClientCode.Fail; result.Message = ex.Message + "导入失败,请认真检查excel"; } return(Json(result)); }
public ActionResult ImportExcel(string yearM, string suppliersid, string CreateFrom) { using (SysEntities db = new SysEntities()) { try { System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch(); sw.Start(); string outMsg = "", errorMsg = ""; //获取要导入的年月 int yearMonth = Convert.ToInt32(yearM); int suppliersId = Convert.ToInt32(suppliersid); //获取文件 HttpPostedFileBase file = Request.Files["files"]; string path = Server.MapPath("../Uploads/Excel/"); string savePath = Util_XLS.UpLoadXls(file, path, out outMsg); if (outMsg != "") { return(Json(new { Code = 0, Message = outMsg })); } string SheetName = Util_XLS.ConvertToSQLSheetName(yearMonth.ToString()); // string strSQL = string.Format("select * from {0}", SheetName); DataTable dt = Util_XLS.NpoiReadExcle(savePath, SheetName, true, out outMsg); string Msg = m_BLL.ImportExcelForGYS(dt, Convert.ToInt32(CreateFrom), yearMonth, suppliersId, LoginInfo.UserID, LoginInfo.BranchID, LoginInfo.UserName); if (Msg != "") { return(Json(new { Code = 0, Message = Msg })); } else { return(Json(new { Code = 1, Message = "导入成功" })); } } catch (Exception ex) { return(Json(new { Code = 1, Message = "导入失败" + ex.ToString() })); } } }
public ActionResult ImportExcel(string yearM, string suppliersid) { using (SysEntities db = new SysEntities()) { try { System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch(); sw.Start(); string outMsg = "", errorMsg = ""; //获取要导入的年月 int yearMonth = Convert.ToInt32(yearM); int suppliersId = Convert.ToInt32(suppliersid); //获取文件 HttpPostedFileBase file = Request.Files["files"]; string path = Server.MapPath("../Uploads/Excel/"); string savePath = Util_XLS.UpLoadXls(file, path, out outMsg); if (outMsg != "") { return(Json(new { Code = 0, Message = outMsg })); } #region 弃用 //if (file == null || file.ContentLength <= 0) //{ // ViewBag.error = "文件不能为空"; // return View(); //} //else //{ // string filename = Path.GetFileName(file.FileName); // int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte // string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名 // string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名 // int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M // string FileType = ".xls,.xlsx";//定义上传文件的类型字符串 // FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx; // if (!FileType.Contains(fileEx)) // { // ViewBag.error = "文件类型不对,只能导入xls和xlsx格式的文件"; // return View(); // } // if (filesize >= Maxsize) // { // ViewBag.error = "上传文件超过4M,不能上传"; // return View(); // } // string path = AppDomain.CurrentDomain.BaseDirectory + "Uploads/Excel/"; // savePath = Path.Combine(path, FileName); // file.SaveAs(savePath); //} // string strConn; //// strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savePath + ";" + "Extended Properties=Excel 8.0"; // strConn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + savePath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; // OleDbConnection conn = new OleDbConnection(strConn); // conn.Open(); // string SheetName = yearMonth + "$"; // string strSQL = string.Format("select * from [{0}]", SheetName); // OleDbDataAdapter myCommand = new OleDbDataAdapter(strSQL, strConn); // DataSet myDataSet = new DataSet(); // // myCommand.Fill(myDataSet, "SelectResult"); // try // { // myCommand.Fill(myDataSet, "SelectResult"); // } // catch (Exception ex) // { // ViewBag.error = ex.Message + "导入失败,请认真检查excel"; // return Json(ViewBag.error); // } // DataTable table = myDataSet.Tables["SelectResult"].DefaultView.ToTable(); #endregion string SheetName = Util_XLS.ConvertToSQLSheetName(yearMonth.ToString()); // string strSQL = string.Format("select * from {0}", SheetName); DataTable dt = Util_XLS.NpoiReadExcle(savePath, SheetName, true, out outMsg); if (outMsg != "") { return(Json(new { Code = 0, Message = outMsg })); } //验证Excel信息 List <COST_PayTemporary> list = new List <COST_PayTemporary>(); //验证 string guid = Common.Result.GetNewId(); string str = verification(db, dt, yearMonth, guid, out list); if (str != "") { return(Json(new { Code = 0, Message = str })); } else { //批量插入数据库 string connString = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ToString(); string[] st = { "ID", "YearMonth", "CreateTime", "CreateUserID", "CreateUserName", "BranchID", "PersonName", "CardId", "PersonId", "CompanyId", "CompanyName", "Suppliers", "CityId", "PaymentSocialMonthYL", "RadixYL", "CompanyCosYL", "PersonCostYL", "PaymentSocialMonthSY", "RadixSY", "CompanyCostSY", "PersonCostSY", "PaymentSocialMonthGS", "RadixGS", "CompanyCostGS", "PaymentSocialMonthYiL", "RadixYil", "CompanyCostYil", "PersonCostYil", "CompanyCostYilMax", "PersonCostYilMax", "CompanyCostShengY", "PaymentSocialMonthGJJ", "RadixGJJ", "CompanyCostGJJ", "PersonCostGJJ", "PaymentSocialMonthBCGJJ", "RadixBCGJJ", "CompanyCostBCGJJ", "PersonCostBCGJJ", "PayOtherSocial", "PayOther", "YanglaoPayFee", "YiliaoPayFee", "ShiyePayFee","GongshangPayFee","GongjijinPayFee", "ShengYuPayFee", "BatchGuid", "Remark", "SuppliersId", "CardIdSB" }; Util_XLS.BulkInsert(connString, "COST_PayTemporary", list, st); //把数据分到不同的表中 Database dbEntity = EnterpriseLibraryContainer.Current.GetInstance <Database>("connStr"); DbCommand cmd = dbEntity.GetStoredProcCommand("FillPayTable"); dbEntity.AddInParameter(cmd, "@yearMonth", DbType.Int32, yearMonth); dbEntity.AddInParameter(cmd, "@batchGuid", DbType.String, guid); dbEntity.AddInParameter(cmd, "@suppliersId", DbType.Int32, suppliersId); dbEntity.AddInParameter(cmd, "@userId", DbType.Int32, LoginInfo.UserID); dbEntity.AddInParameter(cmd, "@userName", DbType.String, LoginInfo.UserName); dbEntity.AddInParameter(cmd, "@branchId", DbType.String, LoginInfo.BranchID); dbEntity.AddParameter(cmd, "@return", DbType.String, ParameterDirection.ReturnValue, "return", DataRowVersion.Default, ""); cmd.CommandTimeout = 0; dbEntity.ExecuteNonQuery(cmd); string result = dbEntity.GetParameterValue(cmd, "@return").ToString(); if (result == "1") { return(Json(new { Code = 1, Message = "导入成功" })); } else { return(Json(new { Code = 1, Message = "导入失败" })); } } } catch (Exception ex) { return(Json(new { Code = 1, Message = "导入失败" + ex.ToString() })); } } }