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 ImportEmployeeByExcel(HttpPostedFileBase files)
{
Common.ClientResult.Result result = new Common.ClientResult.Result();
#region 导入Excel
try
{
HttpPostedFileBase file = files;
string FileName;
string savePath = string.Empty;
if (file == null || file.ContentLength <= 0)
{
result.Code = Common.ClientCode.Fail;
result.Message = "文件不能为空";
//return result;
return(Json(new { code = 0, msg = result.Message }));
}
else
{
string filename = System.IO.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"; //定义上传文件的类型字符串
FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
if (!FileType.Contains(fileEx))
{
result.Code = Common.ClientCode.Fail;
result.Message = "文件类型不对，只能导入xls格式的文件";
//return result;
return(Json(new { code = 0, msg = result.Message }));
}
//if (filesize >= Maxsize)
//{
// result.Code = Common.ClientCode.Fail;
// result.Message = "上传文件超过4M，不能上传";
// //return result;
// return Json(new { code = 0, msg = result.Message });
//}
string path = AppDomain.CurrentDomain.BaseDirectory + "excel/";
savePath = System.IO.Path.Combine(path, FileName);
file.SaveAs(savePath);
}
//string filename = System.IO.Path.GetFileName(file.FileName);
string message = string.Empty;
DataTable table = Util_XLS.NpoiReadExcle2(savePath, "Sheet1", out message);
if (!string.IsNullOrWhiteSpace(message))
{
result.Code = Common.ClientCode.Fail;
result.Message = message;
return(Json(new { code = 0, msg = result.Message }));
}
if (table == null && table.Rows.Count <= 0)
{
result.Code = Common.ClientCode.Fail;
result.Message = "导入Excel中不存在有效员工信息请核实!";
return(Json(new { code = 0, msg = result.Message }));
}
int SuccessNum = 0;
if (ImportEmployeeAdd(table, ref message, out SuccessNum))
{
result.Code = Common.ClientCode.Succeed;
result.Message = string.Format("成功导入{0}人员工信息!", table.Rows.Count);
return(Json(new { code = 1, msg = result.Message }));
}
else
{
result.Code = Common.ClientCode.Fail;
result.Message = message;
return(Json(new { code = 0, msg = result.Message }));
}
}
catch (Exception ex)
{
result.Code = Common.ClientCode.Fail;
result.Message = ex.Message + "导入失败,请认真检查excel";
//return result;
return(Json(new { code = 0, msg = result.Message }));
}
#endregion
}
public ActionResult SetFailByExcel(HttpPostedFileBase files)
{
Common.ClientResult.Result result = new Common.ClientResult.Result();
result.Code = Common.ClientCode.Fail;
result.Message = "导入失败";
#region 导入Excel
try
{
HttpPostedFileBase file = files;
string FileName;
string savePath = string.Empty;
if (file == null || file.ContentLength <= 0)
{
result.Code = Common.ClientCode.Fail;
result.Message = "文件不能为空";
return(Json(new { code = result.Code, msg = result.Message }));
}
else
{
string filename = System.IO.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";//定义上传文件的类型字符串
string[] FileType = new string[] { ".xls", ".xlsx" };
FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
if (!FileType.Contains(fileEx))
{
result.Code = Common.ClientCode.Fail;
result.Message = "文件类型不对，只能导入Excel文件";
return(Json(new { code = result.Code, msg = result.Message }));
}
if (filesize >= Maxsize)
{
result.Code = Common.ClientCode.Fail;
result.Message = "上传文件超过4M，不能上传";
//return result;
return(Json(new { code = 0, msg = result.Message }));
}
string path = AppDomain.CurrentDomain.BaseDirectory + "excel/";
savePath = System.IO.Path.Combine(path, FileName);
file.SaveAs(savePath);
}
//string filename = System.IO.Path.GetFileName(file.FileName);
string message = string.Empty;
DataTable table = Util_XLS.NpoiReadExcle2(savePath, "Sheet1", out message);
if (!string.IsNullOrWhiteSpace(message))
{
result.Code = Common.ClientCode.Fail;
result.Message = message;
return(Json(new { code = result.Code, msg = result.Message }));
}
if (table == null && table.Rows.Count <= 0)
{
result.Code = Common.ClientCode.Fail;
result.Message = "导入Excel中不存在有效信息，请核实！";
return(Json(new { code = result.Code, msg = result.Message }));
}
IBLL.IEmployeeStopPaymentBLL mbll = new BLL.EmployeeStopPaymentBLL();
string errMsg = mbll.SetStopPaymentFailByExcel(table, LoginInfo.UserID, LoginInfo.UserName);
if (errMsg == "")
{
result.Code = Common.ClientCode.Succeed;
result.Message = "导入成功！";
}
else
{
result.Code = Common.ClientCode.Fail;
result.Message = errMsg;
}
return(Json(new { code = result.Code, msg = result.Message }));
}
catch (Exception ex)
{
result.Code = Common.ClientCode.Fail;
result.Message = ex.Message + "导入失败,请认真检查excel";
return(Json(new { code = result.Code, msg = result.Message }));
}
#endregion
}
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() }));
}
}
}
