ExcelController.cs 42 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.Mvc;
  6. using System.Data;
  7. using System.Data.SqlClient;
  8. using System.IO;
  9. using System.Data.OleDb;
  10. using MES.Production.Entity;
  11. using Ant.ORM;
  12. using Ant.Data;
  13. using Ant.Common;
  14. using System.Text.RegularExpressions;
  15. using MES.Production.Entity.Enum;
  16. using System.Transactions;
  17. using ChangFa.Machinery.WebPage.Controllers;
  18. using Central.Control.Domain;
  19. using MES.Production.Entity.Entity;
  20. using Ant.Service.Utility;
  21. using System.Configuration;
  22. using ChangFa.Machinery.WebPage.App_Start;
  23. namespace ChangFa.Machinery.WebPage.Areas.SysManage.Controllers
  24. {
  25. public class ExcelController : BaseController
  26. {
  27. /// <summary>
  28. ///
  29. /// </summary>
  30. /// <param name="file"></param>
  31. /// <returns></returns>
  32. [HttpPost]
  33. public ActionResult UpLoad(HttpPostedFileBase filebase)
  34. {
  35. HttpPostedFileBase file = Request.Files["file"];//获取上传的文件
  36. string result = string.Empty;
  37. string strConn;
  38. strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + file + "; " + "Extended Properties=Excel 8.0;";
  39. OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
  40. DataSet myDataSet = new DataSet();
  41. myCommand.Fill(myDataSet, "ExcelInfo");
  42. System.Data.DataTable tab = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
  43. // ...用foreach把tab中数据添加到数据库 省略了如果是多表插入,可以调用存储过程.呵呵
  44. result = "导入成功!";
  45. JsonResult json = new JsonResult();
  46. json.Data = result;
  47. return json;
  48. }
  49. /// <summary>
  50. /// 导入经销商
  51. /// </summary>
  52. /// <param name="filebase"></param>
  53. /// <returns></returns>
  54. [HttpPost]
  55. public JsonResult DataImportDistributor(HttpPostedFileBase filebase)
  56. {
  57. HttpPostedFileBase file = Request.Files["file"];//获取上传的文件
  58. string FileName;
  59. string savePath;
  60. var json = new Ant.Service.Common.JsonHelper() { Msg = "导入成功", Status = "y", ReUrl = "/Sys/Home/Index" };
  61. if (file == null || file.ContentLength <= 0)
  62. {
  63. json.Status = "n";
  64. json.Msg = "文件不能为空!";
  65. return Json(json);
  66. //ViewBag.error = "文件不能为空";
  67. //return View();
  68. }
  69. else
  70. {
  71. string filename = Path.GetFileName(file.FileName);
  72. int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte
  73. string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
  74. string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
  75. int Maxsize = 10000 * 1024;//定义上传文件的最大空间大小为10M
  76. string FileType = ".xls,.xlsx";//定义上传文件的类型字符串
  77. FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
  78. if (!FileType.Contains(fileEx))
  79. {
  80. json.Status = "n";
  81. json.Msg = "文件类型不对,只能导入xls和xlsx格式的文件";
  82. return Json(json);
  83. }
  84. if (filesize >= Maxsize)
  85. {
  86. json.Status = "n";
  87. json.Msg = "上传文件超过10M,不能上传";
  88. return Json(json);
  89. }
  90. string path = AppDomain.CurrentDomain.BaseDirectory + "upload\\excel\\";
  91. savePath = Path.Combine(path, FileName);
  92. file.SaveAs(savePath);
  93. string strConn;
  94. //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + savePath + "; " + "Extended Properties=Excel 8.0;";
  95. strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + savePath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
  96. //备注: "HDR=yes;"是说Excel文件的第一行是列名而不是数据,"HDR=No;"正好与前面的相反。
  97. // "IMEX=1 "如果列中的数据类型不一致,使用"IMEX=1"可必免数据类型冲突。
  98. //OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
  99. DataSet myDataSet = new DataSet();
  100. try
  101. {
  102. //连接串
  103. //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "DataSource=" + Path + ";" + "ExtendedProperties=Excel8.0;";
  104. OleDbConnection conn = new OleDbConnection(strConn);
  105. conn.Open();
  106. //返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等 
  107. DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
  108. //包含excel中表名的字符串数组
  109. string[] strTableNames = new string[dtSheetName.Rows.Count];
  110. for (int k = 0; k < dtSheetName.Rows.Count; k++)
  111. {
  112. strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
  113. }
  114. OleDbDataAdapter myCommand = null;
  115. DataTable dt = new DataTable();
  116. //从指定的表明查询数据,可先把所有表明列出来供用户选择
  117. string strExcel = "select * from [" + strTableNames[0] + "]";
  118. myCommand = new OleDbDataAdapter(strExcel, strConn);
  119. //myCommand.Fill(dt);
  120. myCommand.Fill(myDataSet, "ExcelInfo");
  121. }
  122. catch (Exception ex)
  123. {
  124. json.Status = "n";
  125. json.Msg = ex.Message.ToString();
  126. return Json(json);
  127. //return View();
  128. }
  129. DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
  130. if (table.Rows.Count > 1000)
  131. {
  132. json.Status = "n";
  133. json.Msg = "导入数据不能大于1000条!";
  134. return Json(json);
  135. }
  136. using (TransactionScope transaction = new TransactionScope())
  137. {
  138. for (int i = 0; i < table.Rows.Count; i++)
  139. {
  140. var _entity = new EntSYS_DISTRIBUTORS(); var _userenty = new En_SYS_USER(); //当前经销商和用户
  141. string distridutorid = Guid.NewGuid().ToString();
  142. using (AntORM orm = new AntORM())
  143. {
  144. orm.db = DataAccessFactory.CreateDataConnection("CyclingItem");
  145. RequestModel res = new RequestModel
  146. {
  147. newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "获取公告信息" }
  148. };
  149. string name = table.Rows[i]["经销商名称"].ToString();
  150. var distype = table.Rows[i]["经销商类型"].ToString();
  151. string discode = table.Rows[i]["经销商编号"].ToString();
  152. var proudctline1 = table.Rows[i]["轮拖轮式收条线联系人"].ToString();// 601615
  153. var proudctline2 = table.Rows[i]["履带插秧条线联系人"].ToString();//612611
  154. var proudctline3 = table.Rows[i]["玉米收花生机条线联系人"].ToString();//622628
  155. var phone1 = table.Rows[i]["轮拖轮式收条线手机号"].ToString();
  156. var phone2 = table.Rows[i]["履带插秧条线手机号"].ToString();
  157. var phone3 = table.Rows[i]["玉米收花生机条线手机号"].ToString();
  158. var saleproduct = table.Rows[i]["销售产品"].ToString();
  159. var resulut = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.NAME == name).FirstOrDefault();
  160. if (resulut.IsSuccess)//修改
  161. {
  162. EntSYS_DISTRIBUTORS dismod = resulut.ResultModel;
  163. distridutorid = dismod.ID;
  164. _entity.NAME = table.Rows[i]["经销商名称"].ToString();
  165. _entity.CODE = table.Rows[i]["经销商编号"].ToString();
  166. _entity.Address = table.Rows[i]["地址"].ToString();
  167. string pro = table.Rows[i]["所在省"].ToString();
  168. var resluts = orm.Queryable<MES.Production.Entity.SYS_CODE_AREA>().Where(p => p.ID == pro).FirstOrDefault(res);
  169. if (resluts.IsSuccess)
  170. {
  171. MES.Production.Entity.SYS_CODE_AREA mod = resluts.ResultModel;
  172. _entity.Province = mod.ID.ToInt32();
  173. }
  174. string city = table.Rows[i]["所在市"].ToString();
  175. resluts = orm.Queryable<MES.Production.Entity.SYS_CODE_AREA>().Where(p => p.ID == city).FirstOrDefault(res);
  176. if (resluts.IsSuccess)
  177. {
  178. MES.Production.Entity.SYS_CODE_AREA mod = resluts.ResultModel;
  179. _entity.City = mod.ID.ToInt32();
  180. }
  181. string area = table.Rows[i]["所在市"].ToString();
  182. resluts = orm.Queryable<MES.Production.Entity.SYS_CODE_AREA>().Where(p => p.ID == area).FirstOrDefault(res);
  183. if (resluts.IsSuccess)
  184. {
  185. MES.Production.Entity.SYS_CODE_AREA mod = resluts.ResultModel;
  186. _entity.Area = mod.ID.ToInt32();
  187. }
  188. _entity.FenceRadius = table.Rows[i]["电子围栏范围"].ToInt32();
  189. _entity.Theodolite = table.Rows[i]["经纬度"].ToString().Replace(",", ",");
  190. _entity.SaleCar = table.Rows[i]["销售产品"].ToString();
  191. //var reslutdevice = orm.UpdateByLambda<EntSYS_DISTRIBUTORS>(p => _entity, a => a.ID == dismod.ID, res);
  192. }
  193. else//新增一级经销商和仓库
  194. {
  195. if (distype == "一级经销商")
  196. {
  197. _entity.BUSINESSLEVEL = 2;
  198. int sumcount = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.BUSINESSLEVEL == 2).Count() + 1;
  199. string num = sumcount.ToString("D5");
  200. _entity.CODE = "003" + num;
  201. _entity.NAME = table.Rows[i]["经销商名称"].ToString();
  202. _entity.DealerNumber = table.Rows[i]["经销商编号"].ToString();
  203. _entity.PARENTID = "32f7a4bd-84de-4587-be29-734d65ad6f70";
  204. _entity.ParentName = "常发农机定位锁车";
  205. _entity.PARENTCODE = "003";
  206. Ent_SYS_USER usermod = new Ent_SYS_USER();
  207. usermod.NAME = table.Rows[i]["经销商联系人"].ToString();
  208. usermod.ACCOUNT = table.Rows[i]["经销商手机号"].ToString();
  209. usermod.DPTID = distridutorid;
  210. usermod.PHONE = table.Rows[i]["经销商手机号"].ToString();
  211. var pwd = PasswordUtils.MD5_Encrypt("111111", 32);
  212. usermod.PASSWORD = pwd;
  213. usermod.USERTYPE = 2;
  214. usermod.CREATEPER = "系统导入";
  215. usermod.CREATEDATE = DateTime.Now;
  216. var reslut1 = orm.Save(usermod, res);
  217. }
  218. else //仓库
  219. {
  220. var reslut = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.DealerNumber == discode && p.BUSINESSLEVEL == 2).FirstOrDefault(res);
  221. if (reslut.IsSuccess)
  222. {
  223. EntSYS_DISTRIBUTORS entSYS_DISTRIBUTORS = reslut.ResultModel;//父级经销商数据;
  224. int sumcount = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.BUSINESSLEVEL == 3 && p.PARENTCODE == entSYS_DISTRIBUTORS.CODE).Count() + 1;
  225. string num = sumcount.ToString("D3");
  226. _entity.CODE = entSYS_DISTRIBUTORS.CODE + num;
  227. _entity.PARENTID = entSYS_DISTRIBUTORS.ID;
  228. _entity.ParentName = entSYS_DISTRIBUTORS.NAME;
  229. _entity.PARENTCODE = entSYS_DISTRIBUTORS.CODE;
  230. }
  231. _entity.BUSINESSLEVEL = 3;
  232. _entity.NAME = table.Rows[i]["经销商名称"].ToString();
  233. _entity.DealerNumber = table.Rows[i]["经销商编号"].ToString();
  234. }
  235. _entity.Address = table.Rows[i]["地址"].ToString();
  236. //_entity.ProvinceCity = table.Rows[i]["所在省"].ToString() +","+ table.Rows[i]["所在市"].ToString();
  237. string pro = table.Rows[i]["所在省"].ToString();
  238. var resluts = orm.Queryable<MES.Production.Entity.SYS_CODE_AREA>().Where(p => p.ID == pro).FirstOrDefault(res);
  239. if (resluts.IsSuccess)
  240. {
  241. MES.Production.Entity.SYS_CODE_AREA mod = resluts.ResultModel;
  242. _entity.Province = mod.ID.ToInt32();
  243. }
  244. string city = table.Rows[i]["所在市"].ToString();
  245. resluts = orm.Queryable<MES.Production.Entity.SYS_CODE_AREA>().Where(p => p.ID == city).FirstOrDefault(res);
  246. if (resluts.IsSuccess)
  247. {
  248. MES.Production.Entity.SYS_CODE_AREA mod = resluts.ResultModel;
  249. _entity.City = mod.ID.ToInt32();
  250. }
  251. string area = table.Rows[i]["所在市"].ToString();
  252. resluts = orm.Queryable<MES.Production.Entity.SYS_CODE_AREA>().Where(p => p.ID == area).FirstOrDefault(res);
  253. if (resluts.IsSuccess)
  254. {
  255. MES.Production.Entity.SYS_CODE_AREA mod = resluts.ResultModel;
  256. _entity.Area = mod.ID.ToInt32();
  257. }
  258. _entity.FenceRadius = table.Rows[i]["电子围栏范围"].ToInt32();
  259. _entity.Theodolite = table.Rows[i]["经纬度"].ToString().ToString().Replace(",", ",");
  260. _entity.SaleCar = table.Rows[i]["销售产品"].ToString();
  261. _entity.CREATEDATE = DateTime.Now;
  262. orm.Save(_entity, res); //保存经销商信息
  263. }
  264. if (!proudctline1.IsEmpty())
  265. {
  266. var userreslut = orm.Queryable<En_SYS_USER>().Where(p => p.PHONE == phone1).FirstOrDefault(res);
  267. if (userreslut.IsSuccess)
  268. {
  269. En_SYS_USER user = userreslut.ResultModel;
  270. int struserid = user.ID.ToInt32();
  271. var count = orm.Queryable<EntYW_UserDistributor>().Where(p => p.distributorId == distridutorid && p.userId == struserid).Count();
  272. if (count == 0)
  273. {
  274. EntYW_UserDistributor userdismod = new EntYW_UserDistributor();
  275. userdismod.ID = Guid.NewGuid();
  276. userdismod.userId = struserid;
  277. userdismod.distributorId = distridutorid;
  278. userdismod.ProductLine = 601;
  279. userdismod.ProductLine1 = 615;
  280. userdismod.add_name = "系统导入";
  281. var reslut1 = orm.Save(userdismod, res);
  282. }
  283. else
  284. {
  285. var updatereslut = orm.UpdateByLambda<EntYW_UserDistributor>(p => new EntYW_UserDistributor { ProductLine = 601, ProductLine1 = 615 }, f => f.distributorId == distridutorid && f.userId == struserid, res);
  286. }
  287. }
  288. else
  289. {
  290. En_SYS_USER usermod = new En_SYS_USER();
  291. usermod.NAME = proudctline1;
  292. usermod.ACCOUNT = phone1;
  293. usermod.PHONE = phone1;
  294. var pwd = PasswordUtils.MD5_Encrypt("111111", 32);
  295. usermod.PASSWORD = pwd;
  296. usermod.USERTYPE = 2;
  297. usermod.CREATEPER = "系统导入";
  298. usermod.CREATEDATE = DateTime.Now;
  299. var reslut = orm.SaveHaveNull(usermod, res);
  300. if (reslut.IsSuccess)
  301. {
  302. int struserid = reslut.RecordNum;
  303. var count = orm.Queryable<EntYW_UserDistributor>().Where(p => p.distributorId == distridutorid && p.userId == struserid).Count();
  304. if (count == 0)
  305. {
  306. EntYW_UserDistributor userdismod = new EntYW_UserDistributor();
  307. userdismod.ID = Guid.NewGuid();
  308. userdismod.userId = struserid;
  309. userdismod.distributorId = distridutorid;
  310. userdismod.ProductLine = 601;
  311. userdismod.ProductLine1 = 615;
  312. userdismod.add_name = "系统导入";
  313. var reslut1 = orm.Save(userdismod, res);
  314. }
  315. else
  316. {
  317. var updatereslut = orm.UpdateByLambda<EntYW_UserDistributor>(p => new EntYW_UserDistributor { ProductLine = 601, ProductLine1 = 615 }, f => f.distributorId == distridutorid && f.userId == struserid, res);
  318. }
  319. }
  320. }
  321. }
  322. if (!proudctline2.IsEmpty())
  323. {
  324. var userreslut = orm.Queryable<En_SYS_USER>().Where(p => p.PHONE == phone2).FirstOrDefault(res);
  325. if (userreslut.IsSuccess)
  326. {
  327. En_SYS_USER user = userreslut.ResultModel;
  328. int struserid = user.ID.ToInt32();
  329. var count = orm.Queryable<EntYW_UserDistributor>().Where(p => p.distributorId == distridutorid && p.userId == struserid).Count();
  330. if (count == 0)
  331. {
  332. EntYW_UserDistributor userdismod = new EntYW_UserDistributor();
  333. userdismod.ID = Guid.NewGuid();
  334. userdismod.userId = struserid;
  335. userdismod.distributorId = distridutorid;
  336. userdismod.ProductLine2 = 612;
  337. userdismod.ProductLine3 = 611;
  338. userdismod.add_name = "系统导入";
  339. var reslut1 = orm.Save(userdismod, res);
  340. }
  341. else
  342. {
  343. var updatereslut = orm.UpdateByLambda<EntYW_UserDistributor>(p => new EntYW_UserDistributor { ProductLine2 = 612, ProductLine3 = 611 }, f => f.distributorId == distridutorid && f.userId == struserid, res);
  344. }
  345. }
  346. else
  347. {
  348. En_SYS_USER usermod = new En_SYS_USER();
  349. usermod.NAME = proudctline1;
  350. usermod.ACCOUNT = phone1;
  351. usermod.PHONE = phone1;
  352. var pwd = PasswordUtils.MD5_Encrypt("111111", 32);
  353. usermod.PASSWORD = pwd;
  354. usermod.USERTYPE = 2;
  355. usermod.CREATEPER = "系统导入";
  356. usermod.CREATEDATE = DateTime.Now;
  357. var reslut = orm.SaveHaveNull(usermod, res);
  358. if (reslut.IsSuccess)
  359. {
  360. int struserid = reslut.RecordNum;
  361. var count = orm.Queryable<EntYW_UserDistributor>().Where(p => p.distributorId == distridutorid && p.userId == struserid).Count();
  362. if (count == 0)
  363. {
  364. EntYW_UserDistributor userdismod = new EntYW_UserDistributor();
  365. userdismod.ID = Guid.NewGuid();
  366. userdismod.userId = struserid;
  367. userdismod.distributorId = distridutorid;
  368. userdismod.ProductLine2 = 612;
  369. userdismod.ProductLine3 = 611;
  370. userdismod.add_name = "系统导入";
  371. var reslut1 = orm.Save(userdismod, res);
  372. }
  373. else
  374. {
  375. var updatereslut = orm.UpdateByLambda<EntYW_UserDistributor>(p => new EntYW_UserDistributor { ProductLine2 = 612, ProductLine3 = 611 }, f => f.distributorId == distridutorid && f.userId == struserid, res);
  376. }
  377. }
  378. }
  379. }
  380. if (!proudctline3.IsEmpty())
  381. {
  382. var userreslut = orm.Queryable<En_SYS_USER>().Where(p => p.PHONE == phone3).FirstOrDefault(res);
  383. if (userreslut.IsSuccess)
  384. {
  385. En_SYS_USER user = userreslut.ResultModel;
  386. int struserid = user.ID.ToInt32();
  387. var count = orm.Queryable<EntYW_UserDistributor>().Where(p => p.distributorId == distridutorid && p.userId == struserid).Count();
  388. if (count == 0)
  389. {
  390. EntYW_UserDistributor userdismod = new EntYW_UserDistributor();
  391. userdismod.ID = Guid.NewGuid();
  392. userdismod.userId = struserid;
  393. userdismod.distributorId = distridutorid;
  394. userdismod.ProductLine4 = 622;
  395. userdismod.ProductLine5 = 628;
  396. userdismod.add_name = "系统导入";
  397. var reslut1 = orm.Save(userdismod, res);
  398. }
  399. else
  400. {
  401. var updatereslut = orm.UpdateByLambda<EntYW_UserDistributor>(p => new EntYW_UserDistributor { ProductLine4 = 622, ProductLine5 = 628 }, f => f.distributorId == distridutorid && f.userId == struserid, res);
  402. }
  403. }
  404. else
  405. {
  406. En_SYS_USER usermod = new En_SYS_USER();
  407. usermod.NAME = proudctline1;
  408. usermod.ACCOUNT = phone1;
  409. usermod.PHONE = phone1;
  410. var pwd = PasswordUtils.MD5_Encrypt("111111", 32);
  411. usermod.PASSWORD = pwd;
  412. usermod.USERTYPE = 2;
  413. usermod.CREATEPER = "系统导入";
  414. usermod.CREATEDATE = DateTime.Now;
  415. var reslut = orm.SaveHaveNull(usermod, res);
  416. if (reslut.IsSuccess)
  417. {
  418. int struserid = reslut.RecordNum;
  419. var count = orm.Queryable<EntYW_UserDistributor>().Where(p => p.distributorId == distridutorid && p.userId == struserid).Count();
  420. if (count == 0)
  421. {
  422. EntYW_UserDistributor userdismod = new EntYW_UserDistributor();
  423. userdismod.ID = Guid.NewGuid();
  424. userdismod.userId = struserid;
  425. userdismod.distributorId = distridutorid;
  426. userdismod.ProductLine4 = 622;
  427. userdismod.ProductLine5 = 628;
  428. userdismod.add_name = "系统导入";
  429. var reslut1 = orm.Save(userdismod, res);
  430. }
  431. else
  432. {
  433. var updatereslut = orm.UpdateByLambda<EntYW_UserDistributor>(p => new EntYW_UserDistributor { ProductLine4 = 622, ProductLine5 = 628 }, f => f.distributorId == distridutorid && f.userId == struserid, res);
  434. }
  435. }
  436. }
  437. }
  438. }
  439. }
  440. transaction.Complete();
  441. json.Status = "y";
  442. json.Msg = string.Format("成功导入{0}条数据", table.Rows.Count);
  443. }
  444. }
  445. return Json(json);
  446. }
  447. public FileResult ExportSchoolExcel2()
  448. {
  449. var schoolInfo = new { RaceName = "2021年武进区迎新春中长跑比赛", SchoolName = "少体校", Leader = "蒋建良", Coacher = "谢川,张晓娜", Contact = "15961169089" };
  450. List<dynamic> sporterInfo = new List<dynamic>();
  451. sporterInfo.Add(new
  452. {
  453. RaceNo = "2211",
  454. Name = "徐文彬",
  455. Sex = "男",
  456. RegNo = "20192026051",
  457. IDCard = "32048320090412131X",
  458. Grade = "小五",
  459. Group = "小学甲组",
  460. Project1 = "1500米",
  461. Project2 = "",
  462. Project3 = "",
  463. Project4 = "",
  464. QRCode = "测试",
  465. SchoolName = "测试"
  466. });
  467. var ms = ExportExcelHelper.ExportSchoolExcel(schoolInfo, sporterInfo);
  468. return File(ms, "application/vnd.ms-excel", "武进区学校体育教练员统计表.xls");
  469. }
  470. /// <summary>
  471. /// 将Excel导入绑定农机数据库
  472. /// </summary>
  473. /// <param name="filebase"></param>
  474. /// <returns></returns>
  475. [HttpPost]
  476. public JsonResult DataImportAll(HttpPostedFileBase filebase)
  477. {
  478. HttpPostedFileBase file = Request.Files["file"];//获取上传的文件
  479. string result = string.Empty;
  480. string distributorsid = Request["distributorsid"];
  481. string distributorName = Request["distributorName"];
  482. string saleid = Request["saleid"];
  483. string FileName;
  484. string savePath;
  485. var json = new Ant.Service.Common.JsonHelper() { Msg = "导入成功", Status = "y", ReUrl = "/Sys/Home/Index" };
  486. if (file == null || file.ContentLength <= 0)
  487. {
  488. json.Status = "n";
  489. json.Msg = "文件不能为空!";
  490. return Json(json);
  491. //ViewBag.error = "文件不能为空";
  492. //return View();
  493. }
  494. else
  495. {
  496. string filename = Path.GetFileName(file.FileName);
  497. int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte
  498. string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
  499. string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
  500. int Maxsize = 10000 * 1024;//定义上传文件的最大空间大小为10M
  501. string FileType = ".xls,.xlsx";//定义上传文件的类型字符串
  502. FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
  503. if (!FileType.Contains(fileEx))
  504. {
  505. json.Status = "n";
  506. json.Msg = "文件类型不对,只能导入xls和xlsx格式的文件";
  507. return Json(json);
  508. }
  509. if (filesize >= Maxsize)
  510. {
  511. json.Status = "n";
  512. json.Msg = "上传文件超过10M,不能上传";
  513. return Json(json);
  514. }
  515. string path = AppDomain.CurrentDomain.BaseDirectory + "upload\\excel\\";
  516. savePath = Path.Combine(path, FileName);
  517. file.SaveAs(savePath);
  518. string strConn;
  519. strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + savePath + "; " + "Extended Properties=Excel 8.0;";
  520. //strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + savePath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
  521. //备注: "HDR=yes;"是说Excel文件的第一行是列名而不是数据,"HDR=No;"正好与前面的相反。
  522. // "IMEX=1 "如果列中的数据类型不一致,使用"IMEX=1"可必免数据类型冲突。
  523. //OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
  524. DataSet myDataSet = new DataSet();
  525. try
  526. {
  527. //连接串
  528. //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "DataSource=" + Path + ";" + "ExtendedProperties=Excel8.0;";
  529. OleDbConnection conn = new OleDbConnection(strConn);
  530. conn.Open();
  531. //返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等 
  532. DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
  533. //包含excel中表名的字符串数组
  534. string[] strTableNames = new string[dtSheetName.Rows.Count];
  535. for (int k = 0; k < dtSheetName.Rows.Count; k++)
  536. {
  537. strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
  538. }
  539. OleDbDataAdapter myCommand = null;
  540. DataTable dt = new DataTable();
  541. //从指定的表明查询数据,可先把所有表明列出来供用户选择
  542. string strExcel = "select * from [" + strTableNames[0] + "]";
  543. myCommand = new OleDbDataAdapter(strExcel, strConn);
  544. //myCommand.Fill(dt);
  545. myCommand.Fill(myDataSet, "ExcelInfo");
  546. }
  547. catch (Exception ex)
  548. {
  549. json.Status = "n";
  550. json.Msg = ex.Message.ToString();
  551. return Json(json);
  552. //return View();
  553. }
  554. DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
  555. //if (table.Rows.Count > 1000)
  556. //{
  557. // json.Status = "n";
  558. // json.Msg = "导入数据不能大于1000条!";
  559. // return Json(json);
  560. //}
  561. EntSYS_DISTRIBUTORS entSYS_DISTRIBUTORS = new EntSYS_DISTRIBUTORS();
  562. using (AntORM orm = new AntORM())
  563. {
  564. orm.db = DataAccessFactory.CreateDataConnection("CyclingItem");
  565. var res = new RequestModel
  566. {
  567. newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "获取经销商信息" }
  568. };
  569. var reslutdis = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.ID == distributorsid).FirstOrDefault(res);
  570. if (reslutdis.IsSuccess)
  571. {
  572. entSYS_DISTRIBUTORS = reslutdis.ResultModel;
  573. }
  574. }
  575. int n = 0; int m = 0;
  576. foreach (DataRow dr in table.Rows)
  577. {
  578. if (dr["企业编号"].IfNotNull())
  579. {
  580. string dealercode = dr["企业编号"].ToString().Trim();
  581. if (!dealercode.IsEmpty())
  582. {
  583. using (AntORM orm = new AntORM())
  584. {
  585. orm.db = DataAccessFactory.CreateDataConnection("CyclingItem");
  586. RequestModel res = new RequestModel();
  587. res.newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "新增经销商数据" };
  588. List<string> userlist = new List<string>();
  589. string barcode = dr["条码"].ToString().Trim(); string carid = dr["卫星定位模块"].ToString().Trim(); string dealername = dr["经销单位"].ToString().Trim();
  590. var num = orm.Queryable<EntYW_Bicycle>().Where(p => p.carBar == barcode).FirstOrDefault(res);
  591. if (num.IsSuccess)
  592. {
  593. EntYW_Bicycle carmod = num.ResultModel;
  594. if (carmod.DistributorsID.IsEmpty())
  595. {
  596. var reslut = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.DealerNumber == dealercode && p.PARENTID == "32f7a4bd-84de-4587-be29-734d65ad6f70").FirstOrDefault(res);
  597. if (reslut.IsSuccess)
  598. {
  599. EntSYS_DISTRIBUTORS entdid = reslut.ResultModel;
  600. var resluts = orm.UpdateByLambda<EntYW_Bicycle>(p => new EntYW_Bicycle() { DistributorsID = entdid.ID, DistributorsName = dealername, CustomerCode = dealercode, CustomerName = entdid.NAME }, a => a.carBar == barcode, res);
  601. n++;
  602. }
  603. else
  604. {
  605. var reslutss = orm.UpdateByLambda<EntYW_Bicycle>(p => new EntYW_Bicycle() { CustomerCode = dealercode, CustomerName = dealername }, a => a.carBar == barcode, res);
  606. }
  607. }
  608. }
  609. else
  610. {
  611. var reslut = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.DealerNumber == dealercode && p.PARENTID == "32f7a4bd-84de-4587-be29-734d65ad6f70").FirstOrDefault(res);
  612. if (reslut.IsSuccess)
  613. {
  614. EntSYS_DISTRIBUTORS dismod = reslut.ResultModel;
  615. string codebar = barcode.Substring(0, 3);
  616. if (codebar == "628")
  617. {
  618. EntYW_Bicycle mod = new EntYW_Bicycle();
  619. mod.ID = Guid.NewGuid();
  620. mod.carBar = barcode.ToString();
  621. //mod.CarName = dr["productName"].ToString().Trim();
  622. int cartype = 2; string productName = "花生机";
  623. mod.DistributorsID = dismod.ID;
  624. mod.DistributorsName = dismod.NAME;
  625. mod.WarehouseID = dismod.ID;
  626. mod.WarehouseName = dismod.NAME;
  627. mod.ProductLine = 628;
  628. mod.CustomerCode = dealercode;
  629. mod.CustomerName = dealername;
  630. mod.CarID = carid.ToString();
  631. mod.CarName = productName;
  632. mod.CarModel = "CF326";
  633. if (dr["发货日期"] != null)
  634. mod.productionDate = dr["发货日期"].ToDateTime();
  635. mod.FactoryNumber = dr["出厂编号"].ToString().Trim();
  636. mod.OutDate = dr["发货日期"].ToDateTime();
  637. mod.LeaseStatus = 0;
  638. mod.CarSate = CarState.OutStock.GetHashCode();
  639. mod.LockState = false;
  640. mod.carType = cartype;
  641. mod.CreateDate = DateTime.Now;
  642. mod.Createper = "系统导入";
  643. mod.FenceState = 0;
  644. var flag = orm.Save(mod, res);
  645. if (flag.IsSuccess)
  646. m++;
  647. }
  648. else
  649. {
  650. }
  651. }
  652. }
  653. }
  654. }
  655. }
  656. }
  657. json.Status = "y";
  658. json.Msg = string.Format("成功导入{0}条数据,修改数据{1}条数据", m, n);
  659. }
  660. return Json(json);
  661. }
  662. }
  663. }