public string Import_Excel()
{
string msg = "";
try
{
HttpPostedFileBase postedFile = Request.Files["import"];//获取上传信息对象
IWorkbook workbook = new XSSFWorkbook(postedFile.InputStream);
ISheet sheet = workbook.GetSheetAt(0);
if (checkImportExcelIsPass(sheet))
{
List <string> listSql = new List <string>();
//List<string> listCusno = new List<string>();
//List<string> listContractno = new List<string>();
string strSql = string.Empty;
List <string> excelField = getExcelField();
JObject json_user = Extension.Get_UserInfo(HttpContext.User.Identity.Name);
for (int i = 4; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i) as XSSFRow;
string code = Extension.getOrderCode();
string cusno = row.GetCell(1).ToStringSafe().Trim(); //业务编号
string contractno = row.GetCell(0).ToStringSafe().Trim(); //合同号
//listCusno.Add(cusno);
//listContractno.Add(contractno);
string field = string.Empty;
string values = string.Empty;
for (int j = 0; j < excelField.Count; j++)
{
field += "," + excelField[j];
string value = checkFieldisValid(excelField[j], row.GetCell(j).ToStringSafe().Trim());
values += ",'" + value + "'";
}
strSql = @"insert into RESIDENT_ORDER (code,RECEIVERUNITCODE,RECEIVERUNITNAME,SUBMITUSERID,SUBMITUSERNAME,status,SUBMITTIME,CREATETIME" + field + @")
select '" + code + "','" + json_user.Value <string>("CUSTOMERCODE") + "','" + json_user.Value <string>("CUSTOMERNAME") +
"','" + json_user.Value <string>("ID") + "','" + json_user.Value <string>("REALNAME") + "',10,sysdate,sysdate" + values + @" from dual ";
// where not exists (select code from RESIDENT_ORDER where CUSNO='" + cusno + "' or CONTRACTNO='" + contractno + "')";
if (cusno != string.Empty && contractno != string.Empty)
{
strSql += " where not exists (select code from RESIDENT_ORDER where CUSNO='" + cusno + "' or CONTRACTNO='" + contractno + "')";
}
else if (cusno != string.Empty)
{
strSql += " where not exists (select code from RESIDENT_ORDER where CUSNO='" + cusno + "')";
}
else if (contractno != string.Empty)
{
strSql += " where not exists (select code from RESIDENT_ORDER where CONTRACTNO='" + contractno + "')";
}
listSql.Add(strSql);
}
string result = "保存成功";
if (listSql.Count > 0)
{
List <int> listResult = DBMgr.ExecuteNonQueryBatch_ForStationedFileld(listSql);
if (listResult.Count > 0)
{
result = "部分保存成功";
}
}
msg = "{success:true,msg:'" + result + "'}";
}
else
{
msg = "{success:false,msg:'excel不正确，请下载正确的Excel模板'}";
}
}
catch (Exception ex)
{
msg = "{success:false,msg:'保存失败:" + ex.Message + "'}";
}
return(msg);
}