//导入
private void ImportExcelData()
{
//获取yearid
string yearid = Request["id"];
Base_Company_Method bcm = new Base_Company_Method();
string formdata = Request["formdata"]; string action = Request["action"];
JObject json_formdata = (JObject)JsonConvert.DeserializeObject(formdata);
string reponseresult = "";
HttpPostedFile postedFile = Request.Files["UPLOADFILE"];//获取上传信息对象
string fileName = Path.GetFileName(postedFile.FileName);
if (!Directory.Exists("/FileUpload/PreData"))
{
Directory.CreateDirectory("/FileUpload/PreData");
}
string newfile = @"/FileUpload/PreData/" + DateTime.Now.ToString("yyyyMMddhhmmss") + "_" + fileName;
postedFile.SaveAs(Server.MapPath(newfile));
Dictionary <int, List <int> > result = upload_base_company(newfile, fileName, action, json_formdata, yearid);
List <int> succInts = result[1];
List <int> errorInts = result[2];
string errorStr = "";
for (int i = 0; i < errorInts.Count; i++)
{
errorStr = errorStr + errorInts[i] + ",";
}
//返回失败信息
string responseerrorlist = "";
//返回成功信息
string responsesuccesslist = "";
if (errorInts.Count > 0)
{
responseerrorlist = "插入失败的行数为：" + errorStr;
}
if (succInts.Count > 0)
{
responsesuccesslist = "成功插入" + succInts[0] + "行!";
}
reponseresult = responsesuccesslist + responseerrorlist;
string response = "{\"success\":\"" + reponseresult + "\"}";
Response.Write(response);
Response.End();
}
public Dictionary <int, List <int> > upload_base_company(string newfile, string fileName, string action,
JObject json_formdata, string table)
{
Base_Company_Method bcm = new Base_Company_Method();
Switch_helper_Base_blend_web sw = new Switch_helper_Base_blend_web();
DataTable dtExcel = bcm.GetExcelData_Table(Server.MapPath(newfile), 0);
List <string> stringList = new List <string>();
//记住发生错误的行数
List <int> errorlines = new List <int>();
//记住插入成功的个数
int count = 0;
Sql.Base_blend_web bc = new Sql.Base_blend_web();
//插入成功的个数(返回放入dictionary)
List <int> successinsert = new List <int>();
//返回值
Dictionary <int, List <int> > returndic = new Dictionary <int, List <int> >();
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
for (int j = 0; j < dtExcel.Columns.Count; j++)
{
stringList.Add(dtExcel.Rows[i][j].ToString());
}
string formdata = sw.importValue(json_formdata, table, stringList);
JObject json = (JObject)JsonConvert.DeserializeObject(formdata);
if (bc.check_repeat(table, json) > 0 || string.IsNullOrEmpty(json.Value <string>("CODE")))
{
errorlines.Add(i + 2);
}
else
{
bc.insertTable(table, json);
count = count + 1;
}
stringList.Clear();
}
successinsert.Add(count);
returndic.Add(1, successinsert);
returndic.Add(2, errorlines);
return(returndic);
}
public Dictionary <int, List <int> > upload_base_company(string newfile, string fileName, string action, JObject json_formdata, string yearid)
{
Base_Company_Method bcm = new Base_Company_Method();
DataTable dtExcel = bcm.GetExcelData_Table(Server.MapPath(newfile), 0);
Sql.decl_HS by = new Sql.decl_HS();
//判断是否有重复的ciq代码
//excel中得到的数据是：CIQ代码，CIQ中文名，启用情况，备注
List <string> insert_base_hscode = new List <string>();
//记住成功插入的条数
int countsuccess = 0;
//记住失败的行数
List <int> errorlines = new List <int>();
//记住成功的个数
List <int> successInts = new List <int>();
//返回值
Dictionary <int, List <int> > retundDictionary = new Dictionary <int, List <int> >();
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
for (int j = 0; j < dtExcel.Columns.Count; j++)
{
insert_base_hscode.Add(dtExcel.Rows[i][j].ToString());
}
//hs编码 //附加码
string HSCODE = insert_base_hscode[0]; string EXTRACODE = insert_base_hscode[1];
//商品名称 //申报要素
string NAME = insert_base_hscode[2]; string ELEMENTS = insert_base_hscode[3];
//法定单位 //第二单位
string LEGALUNIT = insert_base_hscode[4]; string SECONDUNIT = insert_base_hscode[5];
//海关监管
string CUSTOMREGULATORY = insert_base_hscode[6];
//商检监管
string INSPECTIONREGULATORY = insert_base_hscode[7];
//一般税率
string GENERALRATE = insert_base_hscode[8];
//最惠税率
string FAVORABLERATE = insert_base_hscode[9];
//增值税率
string VATRATE = insert_base_hscode[10];
//出口退税率
string EXPORTREBATRATE = insert_base_hscode[11];
//暂定税率
string TEMPRATE = insert_base_hscode[12];
//消费税率
string CONSUMERATE = insert_base_hscode[13];
//出口税率
string EXPORTRATE = insert_base_hscode[14];
//最高价格
string TOPPRICE = insert_base_hscode[15];
//最低价格
string LOWPRICE = insert_base_hscode[16];
//特殊标志
string SPECIALMARK = insert_base_hscode[17];
//启用情况
string ENABLED = insert_base_hscode[18] == "是" ? "1" : "0";
//备注
string REMARK = insert_base_hscode[19];
//启用日期
string startdate = json_formdata.Value <string>("STARTDATE");
//停用日期
string enddate = json_formdata.Value <string>("ENDDATE");
string formdata = "{\"HSCODE\":\"" + HSCODE + "\",\"NAME\":\"" + NAME + "\",\"LEGALUNIT\":\"" + LEGALUNIT + "\",\"EXTRACODE\":\"" + EXTRACODE + "\",\"ELEMENTS\":\"" + ELEMENTS + "\",\"CUSTOMREGULATORY\":\"" + CUSTOMREGULATORY + "\",\"INSPECTIONREGULATORY\":\"" + INSPECTIONREGULATORY + "\",\"ENABLED\":\"" + ENABLED + "\"," +
"\"REMARK\":\"" + REMARK + "\",\"STARTDATE\":\"" + startdate + "\",\"ENDDATE\":\"" + enddate + "\"," +
"\"SECONDUNIT\":\"" + SECONDUNIT + "\",\"GENERALRATE\":\"" + GENERALRATE + "\",\"FAVORABLERATE\":\"" + FAVORABLERATE + "\"," +
"\"VATRATE\":\"" + VATRATE + "\",\"EXPORTREBATRATE\":\"" + EXPORTREBATRATE + "\",\"TEMPRATE\":\"" + TEMPRATE + "\"," +
"\"CONSUMERATE\":\"" + CONSUMERATE + "\",\"EXPORTRATE\":\"" + EXPORTRATE + "\",\"TOPPRICE\":\"" + TOPPRICE + "\"," +
"\"LOWPRICE\":\"" + LOWPRICE + "\",\"SPECIALMARK\":\"" + SPECIALMARK + "\"}";
JObject json = (JObject)JsonConvert.DeserializeObject(formdata);
if (by.check_repeat_base_insphs(json, yearid).Rows.Count > 0 || string.IsNullOrEmpty(HSCODE) || string.IsNullOrEmpty(NAME))
{
errorlines.Add(i + 2);
}
else
{
by.insert_base_insphs(json, yearid);
countsuccess = countsuccess + 1;
}
insert_base_hscode.Clear();
}
successInts.Add(countsuccess);
retundDictionary.Add(1, successInts);
retundDictionary.Add(2, errorlines);
return(retundDictionary);
}
private void save(string formdata)
{
JObject json = (JObject)JsonConvert.DeserializeObject(formdata);
Base_Company_Method bcm = new Base_Company_Method();
Sql.Base_Company bcsql = new Sql.Base_Company();
//禁用人
string stopman = "";
//返回重复结果
string repeat = "";
//返回前端的值
string response = "";
if (json.Value <string>("ENABLED") == "1")
{
stopman = "";
}
else
{
FormsIdentity identity = HttpContext.Current.User.Identity as FormsIdentity;
string userName = identity.Name;
JObject json_user = Extension.Get_UserInfo(userName);
stopman = (string)json_user.GetValue("ID");
}
if (String.IsNullOrEmpty(json.Value <string>("ID")))
{
//插入数据库
if (json.Value <string>("ENABLED") == "1")
{
List <int> retunRepeat = bcm.CheckRepeat(json.Value <string>("ID"), json.Value <string>("INCODE"), json.Value <string>("CODE"),
json.Value <string>("SOCIALCREDITNO"));
repeat = bcm.Check_Repeat(retunRepeat);
if (repeat == "")
{
//insert数据向表base_company当是5时插入成功
bcsql.insert_base_company(json, stopman);
repeat = "5";
}
}
else
{
bcsql.insert_base_company(json, stopman);
repeat = "5";
}
}
else
{
if (json.Value <string>("ENABLED") == "1")
{
//更新数据库
List <int> retunRepeat = bcm.CheckRepeat(json.Value <string>("ID"), json.Value <string>("INCODE"),
json.Value <string>("CODE"),
json.Value <string>("SOCIALCREDITNO"));
repeat = bcm.Check_Repeat(retunRepeat);
if (repeat == "")
{
DataTable dt = bcsql.LoadDataById(json.Value <string>("ID"));
int i = bcsql.update_base_company(json, stopman);
if (i > 0)
{
bcsql.insert_base_alterrecord(json, dt);
}
repeat = "5";
}
}
else
{
DataTable dt = bcsql.LoadDataById(json.Value <string>("ID"));
int i = bcsql.update_base_company(json, stopman);
if (i > 0)
{
bcsql.insert_base_alterrecord(json, dt);
}
repeat = "5";
}
}
response = "{\"success\":\"" + repeat + "\"}";
Response.Write(response);
Response.End();
}
public Dictionary <int, List <int> > upload_base_company(string newfile, string fileName, string action, JObject json_formdata)
{
Base_Company_Method bcm = new Base_Company_Method();
Sql.Base_Company bc = new Sql.Base_Company();
DataTable dtExcel = bcm.GetExcelData_Table(Server.MapPath(newfile), 0);
List <string> stringList = new List <string>();
//停用人
string stopman = "";
//存放成功信息
List <int> repeatListsuccess = new List <int>();
//存放失败条数
List <int> repeatListerror = new List <int>();
//记住insert成功的条数
int count = 0;
//返回信息
Dictionary <int, List <int> > dcInts = new Dictionary <int, List <int> >();
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
for (int j = 0; j < dtExcel.Columns.Count; j++)
{
stringList.Add(dtExcel.Rows[i][j].ToString());
}
string DeclNature = ""; //海关企业性质
string InspNature = ""; //商检企业性质
//内部编码 //海关编码
string incode = stringList[0]; string CODE = stringList[1];
//商检编码 //企业名称
string INSPCODE = stringList[2]; string NAME = stringList[3];
//企业英文名称 //货物存放地
string ENGLSHNAME = stringList[4]; string GOODSLOCAL = stringList[5];
//收货人类型 //启用情况
string RECEIVERTYPE = stringList[6]; string ENABLED = stringList[7] == "是"?"1":"0";
//备注
string REMARK = stringList[8];
//维护人
string CREATEMANNAME = json_formdata.Value <string>("CREATEMANNAME");
//启用时间
string STARTDATE = json_formdata.Value <string>("STARTDATE") == "" ? DateTime.MinValue.ToShortDateString() : json_formdata.Value <string>("STARTDATE");
//停用日期
string ENDDATE = json_formdata.Value <string>("ENDDATE") == ""
? DateTime.MaxValue.ToShortDateString()
: json_formdata.Value <string>("ENDDATE");
//社会信用代码
string SOCIALCREDITNO = stringList[9];
if (CODE.Length > 6)
{
DeclNature = CODE.Substring(5, 1);
InspNature = CODE.Substring(5, 1);
}
if (ENABLED == "1")
{
stopman = "";
}
else
{
FormsIdentity identity = HttpContext.Current.User.Identity as FormsIdentity;
string userName = identity.Name;
JObject json_user = Extension.Get_UserInfo(userName);
stopman = (string)json_user.GetValue("ID");
}
//导入判断条件
List <int> inlist = bcm.CheckRepeat("", incode, CODE, "");
string check_repeat = bcm.Check_Repeat(inlist);
if (check_repeat == "")
{
bc.export_insert_base_company(incode, CODE, INSPCODE, NAME, ENGLSHNAME, GOODSLOCAL, RECEIVERTYPE,
ENABLED, REMARK, STARTDATE, ENDDATE, DeclNature, InspNature, stopman, SOCIALCREDITNO);
count = count + 1;
}
else
{
repeatListerror.Add(i + 2);
}
//清除
stringList.Clear();
}
repeatListsuccess.Add(count);
dcInts.Add(1, repeatListsuccess);
dcInts.Add(2, repeatListerror);
return(dcInts);
}
public void ImportExcelData()
{
Base_Company_Method bcm = new Base_Company_Method();
string formdata = Request["formdata"]; string action = Request["action"];
JObject json_formdata = (JObject)JsonConvert.DeserializeObject(formdata);
string reponseresult = "";
HttpPostedFile postedFile = Request.Files["UPLOADFILE"];//获取上传信息对象
string fileName = Path.GetFileName(postedFile.FileName);
if (!Directory.Exists("/FileUpload/PreData"))
{
Directory.CreateDirectory("/FileUpload/PreData");
}
string newfile = @"/FileUpload/PreData/" + DateTime.Now.ToString("yyyyMMddhhmmss") + "_" + fileName;
postedFile.SaveAs(Server.MapPath(newfile));
//本机不加Server.MapPath
//postedFile.SaveAs(newfile);
//npoi的方法
//DataTable dt = NPOIHelper.RenderDataTableFromExcel(newfile, ".xls", 0, 0);
//int bCount = dt.Rows.Count;
//string a = dt.Rows[0][0].ToString();
Dictionary <int, List <int> > result = upload_base_company(newfile, fileName, action, json_formdata);
//成功的条数
List <int> success = result[1];
//失败列
List <int> error = result[2];
string errorcount = "";
for (int i = 0; i < error.Count; i++)
{
errorcount = errorcount + error[i] + ",";
}
//返回失败信息
string responseerrorlist = "";
//返回成功信息
string responsesuccesslist = "";
if (error.Count > 0)
{
responseerrorlist = "插入失败的行数为：" + errorcount;
}
if (success.Count > 0)
{
responsesuccesslist = "成功插入" + success[0] + "行!";
}
reponseresult = responsesuccesslist + responseerrorlist;
//失败的条数
//********************************************************************
string response = "{\"success\":\"" + reponseresult + "\"}";
Response.Write(response);
Response.End();
}
