123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714 |
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Web;
- using System.Web.Mvc;
- using System.Data;
- using System.Data.SqlClient;
- using System.IO;
- using System.Data.OleDb;
- using MES.Production.Entity;
- using Ant.ORM;
- using Ant.Data;
- using Ant.Common;
- using System.Text.RegularExpressions;
- using MES.Production.Entity.Enum;
- using System.Transactions;
- using ChangFa.Machinery.WebPage.Controllers;
- using Central.Control.Domain;
- using MES.Production.Entity.Entity;
- using Ant.Service.Utility;
- using System.Configuration;
- using ChangFa.Machinery.WebPage.App_Start;
- namespace ChangFa.Machinery.WebPage.Areas.SysManage.Controllers
- {
- public class ExcelController : BaseController
- {
- /// <summary>
- ///
- /// </summary>
- /// <param name="file"></param>
- /// <returns></returns>
- [HttpPost]
- public ActionResult UpLoad(HttpPostedFileBase filebase)
- {
- HttpPostedFileBase file = Request.Files["file"];//获取上传的文件
- string result = string.Empty;
- string strConn;
- strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + file + "; " + "Extended Properties=Excel 8.0;";
- OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
- DataSet myDataSet = new DataSet();
- myCommand.Fill(myDataSet, "ExcelInfo");
- System.Data.DataTable tab = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
- // ...用foreach把tab中数据添加到数据库 省略了如果是多表插入,可以调用存储过程.呵呵
- result = "导入成功!";
- JsonResult json = new JsonResult();
- json.Data = result;
- return json;
- }
- /// <summary>
- /// 导入经销商
- /// </summary>
- /// <param name="filebase"></param>
- /// <returns></returns>
- [HttpPost]
- public JsonResult DataImportDistributor(HttpPostedFileBase filebase)
- {
- HttpPostedFileBase file = Request.Files["file"];//获取上传的文件
- string FileName;
- string savePath;
- var json = new Ant.Service.Common.JsonHelper() { Msg = "导入成功", Status = "y", ReUrl = "/Sys/Home/Index" };
- if (file == null || file.ContentLength <= 0)
- {
- json.Status = "n";
- json.Msg = "文件不能为空!";
- return Json(json);
- //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 = 10000 * 1024;//定义上传文件的最大空间大小为10M
- string FileType = ".xls,.xlsx";//定义上传文件的类型字符串
- FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
- if (!FileType.Contains(fileEx))
- {
- json.Status = "n";
- json.Msg = "文件类型不对,只能导入xls和xlsx格式的文件";
- return Json(json);
- }
- if (filesize >= Maxsize)
- {
- json.Status = "n";
- json.Msg = "上传文件超过10M,不能上传";
- return Json(json);
- }
- string path = AppDomain.CurrentDomain.BaseDirectory + "upload\\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'"; //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
- //备注: "HDR=yes;"是说Excel文件的第一行是列名而不是数据,"HDR=No;"正好与前面的相反。
- // "IMEX=1 "如果列中的数据类型不一致,使用"IMEX=1"可必免数据类型冲突。
- //OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
- DataSet myDataSet = new DataSet();
- try
- {
- //连接串
- //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "DataSource=" + Path + ";" + "ExtendedProperties=Excel8.0;";
- OleDbConnection conn = new OleDbConnection(strConn);
- conn.Open();
- //返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
- DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
- //包含excel中表名的字符串数组
- string[] strTableNames = new string[dtSheetName.Rows.Count];
- for (int k = 0; k < dtSheetName.Rows.Count; k++)
- {
- strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
- }
- OleDbDataAdapter myCommand = null;
- DataTable dt = new DataTable();
- //从指定的表明查询数据,可先把所有表明列出来供用户选择
- string strExcel = "select * from [" + strTableNames[0] + "]";
- myCommand = new OleDbDataAdapter(strExcel, strConn);
- //myCommand.Fill(dt);
- myCommand.Fill(myDataSet, "ExcelInfo");
- }
- catch (Exception ex)
- {
- json.Status = "n";
- json.Msg = ex.Message.ToString();
- return Json(json);
- //return View();
- }
- DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
- if (table.Rows.Count > 1000)
- {
- json.Status = "n";
- json.Msg = "导入数据不能大于1000条!";
- return Json(json);
- }
- using (TransactionScope transaction = new TransactionScope())
- {
- for (int i = 0; i < table.Rows.Count; i++)
- {
- var _entity = new EntSYS_DISTRIBUTORS(); var _userenty = new En_SYS_USER(); //当前经销商和用户
- string distridutorid = Guid.NewGuid().ToString();
- using (AntORM orm = new AntORM())
- {
- orm.db = DataAccessFactory.CreateDataConnection("CyclingItem");
- RequestModel res = new RequestModel
- {
- newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "获取公告信息" }
- };
- string name = table.Rows[i]["经销商名称"].ToString();
- var distype = table.Rows[i]["经销商类型"].ToString();
- string discode = table.Rows[i]["经销商编号"].ToString();
- var proudctline1 = table.Rows[i]["轮拖轮式收条线联系人"].ToString();// 601615
- var proudctline2 = table.Rows[i]["履带插秧条线联系人"].ToString();//612611
- var proudctline3 = table.Rows[i]["玉米收花生机条线联系人"].ToString();//622628
- var phone1 = table.Rows[i]["轮拖轮式收条线手机号"].ToString();
- var phone2 = table.Rows[i]["履带插秧条线手机号"].ToString();
- var phone3 = table.Rows[i]["玉米收花生机条线手机号"].ToString();
- var saleproduct = table.Rows[i]["销售产品"].ToString();
- var resulut = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.NAME == name).FirstOrDefault();
- if (resulut.IsSuccess)//修改
- {
- EntSYS_DISTRIBUTORS dismod = resulut.ResultModel;
- distridutorid = dismod.ID;
- _entity.NAME = table.Rows[i]["经销商名称"].ToString();
- _entity.CODE = table.Rows[i]["经销商编号"].ToString();
- _entity.Address = table.Rows[i]["地址"].ToString();
- string pro = table.Rows[i]["所在省"].ToString();
- var resluts = orm.Queryable<MES.Production.Entity.SYS_CODE_AREA>().Where(p => p.ID == pro).FirstOrDefault(res);
- if (resluts.IsSuccess)
- {
- MES.Production.Entity.SYS_CODE_AREA mod = resluts.ResultModel;
- _entity.Province = mod.ID.ToInt32();
- }
- string city = table.Rows[i]["所在市"].ToString();
- resluts = orm.Queryable<MES.Production.Entity.SYS_CODE_AREA>().Where(p => p.ID == city).FirstOrDefault(res);
- if (resluts.IsSuccess)
- {
- MES.Production.Entity.SYS_CODE_AREA mod = resluts.ResultModel;
- _entity.City = mod.ID.ToInt32();
- }
- string area = table.Rows[i]["所在市"].ToString();
- resluts = orm.Queryable<MES.Production.Entity.SYS_CODE_AREA>().Where(p => p.ID == area).FirstOrDefault(res);
- if (resluts.IsSuccess)
- {
- MES.Production.Entity.SYS_CODE_AREA mod = resluts.ResultModel;
- _entity.Area = mod.ID.ToInt32();
- }
- _entity.FenceRadius = table.Rows[i]["电子围栏范围"].ToInt32();
- _entity.Theodolite = table.Rows[i]["经纬度"].ToString().Replace(",", ",");
- _entity.SaleCar = table.Rows[i]["销售产品"].ToString();
- //var reslutdevice = orm.UpdateByLambda<EntSYS_DISTRIBUTORS>(p => _entity, a => a.ID == dismod.ID, res);
- }
- else//新增一级经销商和仓库
- {
- if (distype == "一级经销商")
- {
- _entity.BUSINESSLEVEL = 2;
- int sumcount = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.BUSINESSLEVEL == 2).Count() + 1;
- string num = sumcount.ToString("D5");
- _entity.CODE = "003" + num;
- _entity.NAME = table.Rows[i]["经销商名称"].ToString();
- _entity.DealerNumber = table.Rows[i]["经销商编号"].ToString();
- _entity.PARENTID = "32f7a4bd-84de-4587-be29-734d65ad6f70";
- _entity.ParentName = "常发农机定位锁车";
- _entity.PARENTCODE = "003";
- Ent_SYS_USER usermod = new Ent_SYS_USER();
- usermod.NAME = table.Rows[i]["经销商联系人"].ToString();
- usermod.ACCOUNT = table.Rows[i]["经销商手机号"].ToString();
- usermod.DPTID = distridutorid;
- usermod.PHONE = table.Rows[i]["经销商手机号"].ToString();
- var pwd = PasswordUtils.MD5_Encrypt("111111", 32);
- usermod.PASSWORD = pwd;
- usermod.USERTYPE = 2;
- usermod.CREATEPER = "系统导入";
- usermod.CREATEDATE = DateTime.Now;
- var reslut1 = orm.Save(usermod, res);
- }
- else //仓库
- {
- var reslut = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.DealerNumber == discode && p.BUSINESSLEVEL == 2).FirstOrDefault(res);
- if (reslut.IsSuccess)
- {
- EntSYS_DISTRIBUTORS entSYS_DISTRIBUTORS = reslut.ResultModel;//父级经销商数据;
- int sumcount = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.BUSINESSLEVEL == 3 && p.PARENTCODE == entSYS_DISTRIBUTORS.CODE).Count() + 1;
- string num = sumcount.ToString("D3");
- _entity.CODE = entSYS_DISTRIBUTORS.CODE + num;
- _entity.PARENTID = entSYS_DISTRIBUTORS.ID;
- _entity.ParentName = entSYS_DISTRIBUTORS.NAME;
- _entity.PARENTCODE = entSYS_DISTRIBUTORS.CODE;
- }
- _entity.BUSINESSLEVEL = 3;
- _entity.NAME = table.Rows[i]["经销商名称"].ToString();
- _entity.DealerNumber = table.Rows[i]["经销商编号"].ToString();
- }
- _entity.Address = table.Rows[i]["地址"].ToString();
- //_entity.ProvinceCity = table.Rows[i]["所在省"].ToString() +","+ table.Rows[i]["所在市"].ToString();
- string pro = table.Rows[i]["所在省"].ToString();
- var resluts = orm.Queryable<MES.Production.Entity.SYS_CODE_AREA>().Where(p => p.ID == pro).FirstOrDefault(res);
- if (resluts.IsSuccess)
- {
- MES.Production.Entity.SYS_CODE_AREA mod = resluts.ResultModel;
- _entity.Province = mod.ID.ToInt32();
- }
- string city = table.Rows[i]["所在市"].ToString();
- resluts = orm.Queryable<MES.Production.Entity.SYS_CODE_AREA>().Where(p => p.ID == city).FirstOrDefault(res);
- if (resluts.IsSuccess)
- {
- MES.Production.Entity.SYS_CODE_AREA mod = resluts.ResultModel;
- _entity.City = mod.ID.ToInt32();
- }
- string area = table.Rows[i]["所在市"].ToString();
- resluts = orm.Queryable<MES.Production.Entity.SYS_CODE_AREA>().Where(p => p.ID == area).FirstOrDefault(res);
- if (resluts.IsSuccess)
- {
- MES.Production.Entity.SYS_CODE_AREA mod = resluts.ResultModel;
- _entity.Area = mod.ID.ToInt32();
- }
- _entity.FenceRadius = table.Rows[i]["电子围栏范围"].ToInt32();
- _entity.Theodolite = table.Rows[i]["经纬度"].ToString().ToString().Replace(",", ",");
- _entity.SaleCar = table.Rows[i]["销售产品"].ToString();
- _entity.CREATEDATE = DateTime.Now;
- orm.Save(_entity, res); //保存经销商信息
- }
- if (!proudctline1.IsEmpty())
- {
- var userreslut = orm.Queryable<En_SYS_USER>().Where(p => p.PHONE == phone1).FirstOrDefault(res);
- if (userreslut.IsSuccess)
- {
- En_SYS_USER user = userreslut.ResultModel;
- int struserid = user.ID.ToInt32();
- var count = orm.Queryable<EntYW_UserDistributor>().Where(p => p.distributorId == distridutorid && p.userId == struserid).Count();
- if (count == 0)
- {
- EntYW_UserDistributor userdismod = new EntYW_UserDistributor();
- userdismod.ID = Guid.NewGuid();
- userdismod.userId = struserid;
- userdismod.distributorId = distridutorid;
- userdismod.ProductLine = 601;
- userdismod.ProductLine1 = 615;
- userdismod.add_name = "系统导入";
- var reslut1 = orm.Save(userdismod, res);
- }
- else
- {
- var updatereslut = orm.UpdateByLambda<EntYW_UserDistributor>(p => new EntYW_UserDistributor { ProductLine = 601, ProductLine1 = 615 }, f => f.distributorId == distridutorid && f.userId == struserid, res);
- }
- }
- else
- {
- En_SYS_USER usermod = new En_SYS_USER();
- usermod.NAME = proudctline1;
- usermod.ACCOUNT = phone1;
- usermod.PHONE = phone1;
- var pwd = PasswordUtils.MD5_Encrypt("111111", 32);
- usermod.PASSWORD = pwd;
- usermod.USERTYPE = 2;
- usermod.CREATEPER = "系统导入";
- usermod.CREATEDATE = DateTime.Now;
- var reslut = orm.SaveHaveNull(usermod, res);
- if (reslut.IsSuccess)
- {
- int struserid = reslut.RecordNum;
- var count = orm.Queryable<EntYW_UserDistributor>().Where(p => p.distributorId == distridutorid && p.userId == struserid).Count();
- if (count == 0)
- {
- EntYW_UserDistributor userdismod = new EntYW_UserDistributor();
- userdismod.ID = Guid.NewGuid();
- userdismod.userId = struserid;
- userdismod.distributorId = distridutorid;
- userdismod.ProductLine = 601;
- userdismod.ProductLine1 = 615;
- userdismod.add_name = "系统导入";
- var reslut1 = orm.Save(userdismod, res);
- }
- else
- {
- var updatereslut = orm.UpdateByLambda<EntYW_UserDistributor>(p => new EntYW_UserDistributor { ProductLine = 601, ProductLine1 = 615 }, f => f.distributorId == distridutorid && f.userId == struserid, res);
- }
- }
- }
- }
- if (!proudctline2.IsEmpty())
- {
- var userreslut = orm.Queryable<En_SYS_USER>().Where(p => p.PHONE == phone2).FirstOrDefault(res);
- if (userreslut.IsSuccess)
- {
- En_SYS_USER user = userreslut.ResultModel;
- int struserid = user.ID.ToInt32();
- var count = orm.Queryable<EntYW_UserDistributor>().Where(p => p.distributorId == distridutorid && p.userId == struserid).Count();
- if (count == 0)
- {
- EntYW_UserDistributor userdismod = new EntYW_UserDistributor();
- userdismod.ID = Guid.NewGuid();
- userdismod.userId = struserid;
- userdismod.distributorId = distridutorid;
- userdismod.ProductLine2 = 612;
- userdismod.ProductLine3 = 611;
- userdismod.add_name = "系统导入";
- var reslut1 = orm.Save(userdismod, res);
- }
- else
- {
- var updatereslut = orm.UpdateByLambda<EntYW_UserDistributor>(p => new EntYW_UserDistributor { ProductLine2 = 612, ProductLine3 = 611 }, f => f.distributorId == distridutorid && f.userId == struserid, res);
- }
- }
- else
- {
- En_SYS_USER usermod = new En_SYS_USER();
- usermod.NAME = proudctline1;
- usermod.ACCOUNT = phone1;
- usermod.PHONE = phone1;
- var pwd = PasswordUtils.MD5_Encrypt("111111", 32);
- usermod.PASSWORD = pwd;
- usermod.USERTYPE = 2;
- usermod.CREATEPER = "系统导入";
- usermod.CREATEDATE = DateTime.Now;
- var reslut = orm.SaveHaveNull(usermod, res);
- if (reslut.IsSuccess)
- {
- int struserid = reslut.RecordNum;
- var count = orm.Queryable<EntYW_UserDistributor>().Where(p => p.distributorId == distridutorid && p.userId == struserid).Count();
- if (count == 0)
- {
- EntYW_UserDistributor userdismod = new EntYW_UserDistributor();
- userdismod.ID = Guid.NewGuid();
- userdismod.userId = struserid;
- userdismod.distributorId = distridutorid;
- userdismod.ProductLine2 = 612;
- userdismod.ProductLine3 = 611;
- userdismod.add_name = "系统导入";
- var reslut1 = orm.Save(userdismod, res);
- }
- else
- {
- var updatereslut = orm.UpdateByLambda<EntYW_UserDistributor>(p => new EntYW_UserDistributor { ProductLine2 = 612, ProductLine3 = 611 }, f => f.distributorId == distridutorid && f.userId == struserid, res);
- }
- }
- }
- }
- if (!proudctline3.IsEmpty())
- {
- var userreslut = orm.Queryable<En_SYS_USER>().Where(p => p.PHONE == phone3).FirstOrDefault(res);
- if (userreslut.IsSuccess)
- {
- En_SYS_USER user = userreslut.ResultModel;
- int struserid = user.ID.ToInt32();
- var count = orm.Queryable<EntYW_UserDistributor>().Where(p => p.distributorId == distridutorid && p.userId == struserid).Count();
- if (count == 0)
- {
- EntYW_UserDistributor userdismod = new EntYW_UserDistributor();
- userdismod.ID = Guid.NewGuid();
- userdismod.userId = struserid;
- userdismod.distributorId = distridutorid;
- userdismod.ProductLine4 = 622;
- userdismod.ProductLine5 = 628;
- userdismod.add_name = "系统导入";
- var reslut1 = orm.Save(userdismod, res);
- }
- else
- {
- var updatereslut = orm.UpdateByLambda<EntYW_UserDistributor>(p => new EntYW_UserDistributor { ProductLine4 = 622, ProductLine5 = 628 }, f => f.distributorId == distridutorid && f.userId == struserid, res);
- }
- }
- else
- {
- En_SYS_USER usermod = new En_SYS_USER();
- usermod.NAME = proudctline1;
- usermod.ACCOUNT = phone1;
- usermod.PHONE = phone1;
- var pwd = PasswordUtils.MD5_Encrypt("111111", 32);
- usermod.PASSWORD = pwd;
- usermod.USERTYPE = 2;
- usermod.CREATEPER = "系统导入";
- usermod.CREATEDATE = DateTime.Now;
- var reslut = orm.SaveHaveNull(usermod, res);
- if (reslut.IsSuccess)
- {
- int struserid = reslut.RecordNum;
- var count = orm.Queryable<EntYW_UserDistributor>().Where(p => p.distributorId == distridutorid && p.userId == struserid).Count();
- if (count == 0)
- {
- EntYW_UserDistributor userdismod = new EntYW_UserDistributor();
- userdismod.ID = Guid.NewGuid();
- userdismod.userId = struserid;
- userdismod.distributorId = distridutorid;
- userdismod.ProductLine4 = 622;
- userdismod.ProductLine5 = 628;
- userdismod.add_name = "系统导入";
- var reslut1 = orm.Save(userdismod, res);
- }
- else
- {
- var updatereslut = orm.UpdateByLambda<EntYW_UserDistributor>(p => new EntYW_UserDistributor { ProductLine4 = 622, ProductLine5 = 628 }, f => f.distributorId == distridutorid && f.userId == struserid, res);
- }
- }
- }
- }
- }
- }
- transaction.Complete();
- json.Status = "y";
- json.Msg = string.Format("成功导入{0}条数据", table.Rows.Count);
- }
- }
- return Json(json);
- }
- public FileResult ExportSchoolExcel2()
- {
- var schoolInfo = new { RaceName = "2021年武进区迎新春中长跑比赛", SchoolName = "少体校", Leader = "蒋建良", Coacher = "谢川,张晓娜", Contact = "15961169089" };
- List<dynamic> sporterInfo = new List<dynamic>();
- sporterInfo.Add(new
- {
- RaceNo = "2211",
- Name = "徐文彬",
- Sex = "男",
- RegNo = "20192026051",
- IDCard = "32048320090412131X",
- Grade = "小五",
- Group = "小学甲组",
- Project1 = "1500米",
- Project2 = "",
- Project3 = "",
- Project4 = "",
- QRCode = "测试",
- SchoolName = "测试"
- });
- var ms = ExportExcelHelper.ExportSchoolExcel(schoolInfo, sporterInfo);
- return File(ms, "application/vnd.ms-excel", "武进区学校体育教练员统计表.xls");
- }
- /// <summary>
- /// 将Excel导入绑定农机数据库
- /// </summary>
- /// <param name="filebase"></param>
- /// <returns></returns>
- [HttpPost]
- public JsonResult DataImportAll(HttpPostedFileBase filebase)
- {
- HttpPostedFileBase file = Request.Files["file"];//获取上传的文件
- string result = string.Empty;
- string distributorsid = Request["distributorsid"];
- string distributorName = Request["distributorName"];
- string saleid = Request["saleid"];
- string FileName;
- string savePath;
- var json = new Ant.Service.Common.JsonHelper() { Msg = "导入成功", Status = "y", ReUrl = "/Sys/Home/Index" };
- if (file == null || file.ContentLength <= 0)
- {
- json.Status = "n";
- json.Msg = "文件不能为空!";
- return Json(json);
- //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 = 10000 * 1024;//定义上传文件的最大空间大小为10M
- string FileType = ".xls,.xlsx";//定义上传文件的类型字符串
- FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
- if (!FileType.Contains(fileEx))
- {
- json.Status = "n";
- json.Msg = "文件类型不对,只能导入xls和xlsx格式的文件";
- return Json(json);
- }
- if (filesize >= Maxsize)
- {
- json.Status = "n";
- json.Msg = "上传文件超过10M,不能上传";
- return Json(json);
- }
- string path = AppDomain.CurrentDomain.BaseDirectory + "upload\\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'"; //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
- //备注: "HDR=yes;"是说Excel文件的第一行是列名而不是数据,"HDR=No;"正好与前面的相反。
- // "IMEX=1 "如果列中的数据类型不一致,使用"IMEX=1"可必免数据类型冲突。
- //OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
- DataSet myDataSet = new DataSet();
- try
- {
- //连接串
- //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "DataSource=" + Path + ";" + "ExtendedProperties=Excel8.0;";
- OleDbConnection conn = new OleDbConnection(strConn);
- conn.Open();
- //返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
- DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
- //包含excel中表名的字符串数组
- string[] strTableNames = new string[dtSheetName.Rows.Count];
- for (int k = 0; k < dtSheetName.Rows.Count; k++)
- {
- strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
- }
- OleDbDataAdapter myCommand = null;
- DataTable dt = new DataTable();
- //从指定的表明查询数据,可先把所有表明列出来供用户选择
- string strExcel = "select * from [" + strTableNames[0] + "]";
- myCommand = new OleDbDataAdapter(strExcel, strConn);
- //myCommand.Fill(dt);
- myCommand.Fill(myDataSet, "ExcelInfo");
- }
- catch (Exception ex)
- {
- json.Status = "n";
- json.Msg = ex.Message.ToString();
- return Json(json);
- //return View();
- }
- DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
- //if (table.Rows.Count > 1000)
- //{
- // json.Status = "n";
- // json.Msg = "导入数据不能大于1000条!";
- // return Json(json);
- //}
- EntSYS_DISTRIBUTORS entSYS_DISTRIBUTORS = new EntSYS_DISTRIBUTORS();
- using (AntORM orm = new AntORM())
- {
- orm.db = DataAccessFactory.CreateDataConnection("CyclingItem");
- var res = new RequestModel
- {
- newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "获取经销商信息" }
- };
- var reslutdis = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.ID == distributorsid).FirstOrDefault(res);
- if (reslutdis.IsSuccess)
- {
- entSYS_DISTRIBUTORS = reslutdis.ResultModel;
- }
- }
- int n = 0; int m = 0;
- foreach (DataRow dr in table.Rows)
- {
- if (dr["企业编号"].IfNotNull())
- {
- string dealercode = dr["企业编号"].ToString().Trim();
- if (!dealercode.IsEmpty())
- {
- using (AntORM orm = new AntORM())
- {
- orm.db = DataAccessFactory.CreateDataConnection("CyclingItem");
- RequestModel res = new RequestModel();
- res.newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "新增经销商数据" };
- List<string> userlist = new List<string>();
- string barcode = dr["条码"].ToString().Trim(); string carid = dr["卫星定位模块"].ToString().Trim(); string dealername = dr["经销单位"].ToString().Trim();
- var num = orm.Queryable<EntYW_Bicycle>().Where(p => p.carBar == barcode).FirstOrDefault(res);
- if (num.IsSuccess)
- {
- EntYW_Bicycle carmod = num.ResultModel;
- if (carmod.DistributorsID.IsEmpty())
- {
- var reslut = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.DealerNumber == dealercode && p.PARENTID == "32f7a4bd-84de-4587-be29-734d65ad6f70").FirstOrDefault(res);
- if (reslut.IsSuccess)
- {
- EntSYS_DISTRIBUTORS entdid = reslut.ResultModel;
- 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);
- n++;
- }
- else
- {
- var reslutss = orm.UpdateByLambda<EntYW_Bicycle>(p => new EntYW_Bicycle() { CustomerCode = dealercode, CustomerName = dealername }, a => a.carBar == barcode, res);
- }
- }
- }
- else
- {
- var reslut = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.DealerNumber == dealercode && p.PARENTID == "32f7a4bd-84de-4587-be29-734d65ad6f70").FirstOrDefault(res);
- if (reslut.IsSuccess)
- {
- EntSYS_DISTRIBUTORS dismod = reslut.ResultModel;
- string codebar = barcode.Substring(0, 3);
- if (codebar == "628")
- {
- EntYW_Bicycle mod = new EntYW_Bicycle();
- mod.ID = Guid.NewGuid();
- mod.carBar = barcode.ToString();
- //mod.CarName = dr["productName"].ToString().Trim();
- int cartype = 2; string productName = "花生机";
- mod.DistributorsID = dismod.ID;
- mod.DistributorsName = dismod.NAME;
- mod.WarehouseID = dismod.ID;
- mod.WarehouseName = dismod.NAME;
- mod.ProductLine = 628;
- mod.CustomerCode = dealercode;
- mod.CustomerName = dealername;
- mod.CarID = carid.ToString();
- mod.CarName = productName;
- mod.CarModel = "CF326";
- if (dr["发货日期"] != null)
- mod.productionDate = dr["发货日期"].ToDateTime();
- mod.FactoryNumber = dr["出厂编号"].ToString().Trim();
- mod.OutDate = dr["发货日期"].ToDateTime();
- mod.LeaseStatus = 0;
- mod.CarSate = CarState.OutStock.GetHashCode();
- mod.LockState = false;
- mod.carType = cartype;
- mod.CreateDate = DateTime.Now;
- mod.Createper = "系统导入";
- mod.FenceState = 0;
- var flag = orm.Save(mod, res);
- if (flag.IsSuccess)
- m++;
- }
- else
- {
-
- }
- }
- }
- }
- }
- }
- }
- json.Status = "y";
- json.Msg = string.Format("成功导入{0}条数据,修改数据{1}条数据", m, n);
- }
- return Json(json);
- }
- }
- }
|