Example #1
0
        public void GetDateTest()
        {
            ExcelStream xlsxfile = new ExcelStream(@"C:\Users\maxgr\OneDrive\Documents\Fiverr\C#\Drag n chart\Sample data\Data Template 2b.xlsx");

            xlsxfile.Select(4);
            var test = (string)xlsxfile.GetCellData(3, 2, true);
        }
Example #2
0
        public ExcelStreamReader_Tests()
        {
            _excelStreamReader = new ExcelStreamReader();
            _excelPath         = Path.GetTempFileName();

            var excelStream = new ExcelStream();

            if (File.Exists(_excelPath))
            {
                File.Delete(_excelPath);
            }
            using (var x = excelStream.Create(_excelPath))
            {
                using (var sheet = x.LoadSheet(1))
                {
                    sheet.Add(new Pomelo.Data.Excel.Infrastructure.Row
                    {
                        "Name", "Sex", "Age"
                    });
                    sheet.Add(new Pomelo.Data.Excel.Infrastructure.Row
                    {
                        "Tor", "Male", "42"
                    });
                    sheet.SaveChanges();
                }
            }
        }
Example #3
0
        public void GetAllSheetDataTest()
        {
            ExcelStream xlsxfile = new ExcelStream(@"C:\Users\maxgr\OneDrive\Documents\Fiverr\C#\Drag n chart\Sample data\Data Template 2b.xlsx");

            xlsxfile.Select(4);
            var test = xlsxfile.GetAllSheetData();
            xlsxfile.Close();
        }
 public Excel_Tests()
 {
     _ExcelStream = new ExcelStream();
     _ExcelPath   = @"c:\excel\test.xlsx";
     if (File.Exists(_ExcelPath))
     {
         File.Delete(_ExcelPath);
     }
 }
        public SheetHDR(ulong Id, string XmlSource, ExcelStream Excel, SharedStrings stringDictionary)
            : base(Id, Excel, stringDictionary)
        {
            var xd = new XmlDocument();
            xd.LoadXml(XmlSource);
            var rows = xd.GetElementsByTagName("row");
            // 遍历row标签
            var flag = false;
            Header = new Header();
            foreach (XmlNode x in rows)
            {
                var cols = x.ChildNodes;
                var objs = new Row(Header);
                // 遍历c标签
                foreach (XmlNode y in cols)
                {
                    string value = null;
                    // 如果是字符串类型,则需要从字典中查询
                    if (y.Attributes["t"]?.Value == "s")
                    {
                        var index = Convert.ToUInt64(y.FirstChild.InnerText);
                        value = StringDictionary[index];
                    }
                    else if (y.Attributes["t"]?.Value == "inlineStr")
                    {
                        value = y.FirstChild.FirstChild.InnerText;
                    }
                    // 否则其中的v标签值即为单元格内容
                    else
                    {
                        value = y.InnerText;
                    }

                    if (!flag)
                    {
                        Header.Add(value, y.Attributes["r"].Value);
                        continue;
                    }
                    objs.Add(value, y.Attributes["r"].Value);
                }
                if (!flag)
                {
                    while (Header.LastOrDefault() == null)
                        Header.RemoveAt(Header.Count - 1);
                    flag = true;
                    continue;
                }
                // 去掉末尾的null
                while (objs.LastOrDefault() == null)
                    objs.RemoveAt(objs.Count - 1);
                if (objs.Count > 0)
                    this.Add(objs);
            }
            while (this.Count > 0 && this.Last().Count == 0)
                this.RemoveAt(this.Count - 1);
            GC.Collect();
        }
 public ExcelStream_Tests()
 {
     _excelStream = new ExcelStream();
     //_excelPath = @"c:\excel\test.xlsx";
     _excelPath = Path.GetTempFileName();
     if (File.Exists(_excelPath))
     {
         File.Delete(_excelPath);
     }
 }
Example #7
0
 public CouponController(ICouponQueryService queryService,
                         ICouponService service,
                         IAuthorizeValidator authorizeValidator,
                         IHostingEnvironment env)
 {
     _queryService       = queryService;
     _service            = service;
     _authorizeValidator = authorizeValidator;
     _hostingEnv         = env;
     _excelStream        = new ExcelStream();
 }
        public async Task <IActionResult> Upload(IFormFile file, Guid projectId)
        {
            var fileStream = file.OpenReadStream();
            var excel      = new ExcelStream();

            excel.Load(fileStream);
            var sheet1 = excel.LoadSheetHDR(excel.WorkBook.First().Name);
            var slots  = new List <OnCallSlot>(sheet1.Count);

            foreach (var x in sheet1)
            {
                var primary = await User.Manager.FindByNameAsync(x[2]);

                var backup = await User.Manager.FindByNameAsync(x[3]);

                var incidentManager = await User.Manager.FindByNameAsync(x[4]);

                slots.Add(new OnCallSlot
                {
                    Begin     = Convert.ToDateTime(x[0]),
                    End       = Convert.ToDateTime(x[1]),
                    ProjectId = projectId,
                    Role      = SlotRole.Primary,
                    UserId    = primary.Id
                });
                slots.Add(new OnCallSlot
                {
                    Begin     = Convert.ToDateTime(x[0]),
                    End       = Convert.ToDateTime(x[1]),
                    ProjectId = projectId,
                    Role      = SlotRole.Backup,
                    UserId    = backup.Id
                });
                slots.Add(new OnCallSlot
                {
                    Begin     = Convert.ToDateTime(x[0]),
                    End       = Convert.ToDateTime(x[1]),
                    ProjectId = projectId,
                    Role      = SlotRole.IncidentManager,
                    UserId    = incidentManager.Id
                });
            }
            DB.AddRange(slots);
            await DB.SaveChangesAsync();

            return(Prompt(x =>
            {
                x.Title = "导入成功";
                x.Details = "On-Call日程表已经成功导入";
                x.HideBack = true;
                x.RedirectUrl = Url.Action("Index", "OnCall", new { project = projectId });
                x.RedirectText = "查看On-Call时间表";
            }));
        }
Example #9
0
        public void OpeningAndShowing()
        {
            ExcelStream xlsxfile = new ExcelStream(@"C:\Users\maxgr\OneDrive\Documents\Fiverr\C#\Drag n chart\Sample data\Data Template 2b.xlsx");

            xlsxfile.Select(4);

            string actual = xlsxfile.GetCellData(5, 4).ToString().Trim();
            string expected = "2.26";
            Assert.AreEqual(expected, actual);
            xlsxfile.Close();

        }
Example #10
0
        public void SavingProjects()
        {
            ExcelStream excelStream = new ExcelStream(@"C:\Users\maxgr\OneDrive\Documents\Fiverr\C#\Drag n chart\Sample data\Data Template 2b.xlsx");

            excelStream.Select(4);
            Project testProj = new Project()
            {
                ExcelStream = excelStream, ReadingsData = excelStream.GetAllSheetData()
            };

            testProj.Save(@"C:\Users\maxgr\Desktop\test file.xml");
        }
        public SheetWithoutHDR(ulong Id, string XmlSource, ExcelStream Excel, SharedStrings stringDictionary)
            : base(Id, Excel, stringDictionary)
        {
            var xd = new XmlDocument();

            xd.LoadXml(XmlSource);
            var rows = xd.GetElementsByTagName("row");

            // 遍历row标签
            foreach (XmlNode x in rows)
            {
                var cols = x.ChildNodes;
                var objs = new Row();
                // 遍历c标签
                foreach (XmlNode y in cols)
                {
                    string value = null;
                    // 如果是字符串类型,则需要从字典中查询
                    if (y.Attributes["t"]?.Value == "s")
                    {
                        var index = Convert.ToUInt64(y.FirstChild.InnerText);
                        value = StringDictionary[index];
                    }
                    else if (y.Attributes["t"]?.Value == "inlineStr")
                    {
                        value = y.FirstChild.FirstChild.InnerText;
                    }
                    // 否则其中的v标签值即为单元格内容
                    else
                    {
                        value = y.InnerText;
                    }

                    objs.Add(value, y.Attributes["r"].Value);
                }

                // 去掉末尾的null
                while (objs.LastOrDefault() == null)
                {
                    objs.RemoveAt(objs.Count - 1);
                }
                if (objs.Count > 0)
                {
                    this.Add(objs);
                }
            }
            while (this.Count > 0 && this.Last().Count == 0)
            {
                this.RemoveAt(this.Count - 1);
            }
            GC.Collect();
        }
Example #12
0
        public SheetHDR(ulong Id, string XmlSource, ExcelStream Excel, SharedStrings stringDictionary)
            :base(Id, Excel, stringDictionary)
        {
            var xd = new XmlDocument();
            xd.LoadXml(XmlSource);
            var rows = xd.GetElementsByTagName("row");
            // 遍历row标签
            var flag = false;
            Header = new Header();
            foreach (XmlNode x in rows)
            {
                var cols = x.ChildNodes;
                var objs = new Row(Header);
                // 遍历c标签
                foreach (XmlNode y in cols)
                {
                    string value = null;
                    // 如果是字符串类型,则需要从字典中查询
                    if (y.Attributes["t"].Value == "s")
                    {
                        var index = Convert.ToUInt64(y.FirstChild.InnerText);
                        value = StringDictionary[index];
                    }
                    // 否则其中的v标签值即为单元格内容
                    else
                    {
                        value = y.InnerText;
                    }

                    if (!flag)
                    {
                        Header.Add(value, y.Attributes["r"].Value);
                        continue;
                    }
                    objs.Add(value, y.Attributes["r"].Value);
                }
                if (!flag)
                {
                    while (Header.LastOrDefault() == null)
                        Header.RemoveAt(Header.Count - 1);
                    flag = true;
                    continue;
                }
                // 去掉末尾的null
                while (objs.LastOrDefault() == null)
                    objs.RemoveAt(objs.Count - 1);
            }
            while (this.Count > 0 && this.Last().Count == 0)
                this.RemoveAt(this.Count - 1);
            GC.Collect();
        }
Example #13
0
        public IActionResult Export(Guid id)
        {
            Activity activity;

            if (User.IsInRole("Root"))
            {
                activity = DB.Activities.Single(x => x.Id == id);
            }
            else
            {
                activity = DB.Activities.Single(x => x.Id == id && x.OwnerId == User.Current.Id);
            }

            var src = DB.Briberies
                      .Where(x => x.ActivityId == id && x.ReceivedTime.HasValue)
                      .OrderBy(x => x.ReceivedTime)
                      .ToList();

            var nonawarded = DB.Briberies
                             .Count(x => x.ActivityId == id && !x.ReceivedTime.HasValue);

            var tmp  = Guid.NewGuid().ToString();
            var path = Path.Combine(Directory.GetCurrentDirectory(), tmp + ".xlsx");

            using (var excel = ExcelStream.Create(path))
                using (var sheet1 = excel.LoadSheet(1))
                {
                    // Headers
                    sheet1.Add(new Pomelo.Data.Excel.Infrastructure.Row {
                        "Open Id", "昵称", "金额", "领取时间"
                    });
                    foreach (var x in src)
                    {
                        sheet1.Add(new Pomelo.Data.Excel.Infrastructure.Row {
                            x.OpenId ?? "", x.NickName ?? "", (x.Price / 100.0).ToString("0.00"), x.ReceivedTime.Value.ToString("yyyy-MM-dd HH:mm:ss")
                        });
                    }
                    sheet1.Add(new Pomelo.Data.Excel.Infrastructure.Row());
                    sheet1.Add(new Pomelo.Data.Excel.Infrastructure.Row {
                        "未领取金额(元)", "未领取红包(个)", "总参与人数"
                    });
                    sheet1.Add(new Pomelo.Data.Excel.Infrastructure.Row {
                        ((activity.Price - src.Sum(x => x.Price)) / 100.0).ToString("0.00"), nonawarded.ToString(), activity.Attend.ToString()
                    });
                    sheet1.SaveChanges();
                }
            var ret = System.IO.File.ReadAllBytes(path);

            System.IO.File.Delete(path);
            return(File(ret, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", activity.Title + ".xlsx"));
        }
Example #14
0
 public void OpeningTest2()
 {
     ExcelStream file = new ExcelStream();
     try
     {
         file = new ExcelStream(@"C:\Users\maxgr\OneDrive\Documents\Fiverr\C#\Drag n chart\Sample data\New Microsoft Word Document.docx");
         Assert.Fail(); //It should not reach this point if the method works successfully.
     }
     catch (ExtensionException)
     {
         MessageBox.Show("Success!");
         file.Close();
     }
 }
Example #15
0
        protected virtual void Dispose(bool disposing)
        {
            if (!disposedValue)
            {
                if (disposing)
                {
                    ExcelStream.Dispose();
                    ExcelDoc.Dispose();
                }

                ExcelStream   = null;
                ExcelDoc      = null;
                disposedValue = true;
            }
        }
Example #16
0
        /// <summary>
        /// Opens the specified filepath for reading
        /// </summary>
        /// <returns></returns>
        public void OpenExcelReader()
        {
            if (ExcelDoc != null)
            {
                ExcelDoc.Close();
                ExcelDoc.Dispose();
            }

            if (ExcelStream != null)
            {
                ExcelStream.Close();
                ExcelStream.Dispose();
            }

            ExcelStream = System.IO.File.Open(ExcelFilePath, FileMode.Open, FileAccess.Read);

            ExcelDoc = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(ExcelStream, false);
        }
Example #17
0
        public static void Main(string[] args)
        {
            using (var x = ExcelStream.Create(@"c:\excel\try.xlsx"))
                using (var sheet = x.LoadSheet(1))
                {
                    sheet.Add(new Infrastructure.Row
                    {
                        "Create test"
                    });
                    sheet.SaveChanges();
                }
            using (var x = new ExcelStream(@"c:\excel\1.xlsx")) // Open excel file
                using (var sheet = x.LoadSheet("Sheet1"))       // var sheet = x.LoadSheet(1)
                {
                    // Removing sheet2
                    x.RemoveSheet(2);

                    // Creating sheet
                    var s = x.CreateSheet("Hello");
                    s.Add(new Infrastructure.Row
                    {
                        "Code Comb"
                    });
                    s.SaveChanges();

                    // Writing data into sheet
                    sheet.Add(new Infrastructure.Row
                    {
                        "Hello world!"
                    });
                    sheet.SaveChanges();

                    // Reading the data from sheet
                    foreach (var a in sheet)
                    {
                        foreach (var b in a)
                        {
                            Console.Write(b + '\t');
                        }
                        Console.Write("\r\n");
                    }
                }
            Console.ReadKey();
        }
Example #18
0
 public Sheet(ulong id, ExcelStream excel, SharedStrings stringDictionary)
 {
     _id              = id;
     _excel           = excel;
     StringDictionary = stringDictionary;
 }
Example #19
0
        private List <DType> CreateBeanFields(DefBean bean, Sheet.NamedRow row)
        {
            var list = new List <DType>();

            foreach (DefField f in bean.HierarchyFields)
            {
                string      fname = f.Name;
                Sheet.Title title = row.GetTitle(fname);
                if (title == null)
                {
                    throw new Exception($"bean:{bean.FullName} 缺失 列:{fname},请检查是否写错或者遗漏");
                }
                // 多级标题
                if (title.SubTitles.Count > 0)
                {
                    try
                    {
                        if (f.IsMultiRow)
                        {
                            list.Add(f.CType.Apply(this, row.GetSubTitleNamedRowOfMultiRows(fname), f.RawIsMultiRow, f.IsNullable));
                        }
                        else
                        {
                            list.Add(f.CType.Apply(this, row.GetSubTitleNamedRow(fname), f.RawIsMultiRow /* 肯定是 false */, f.IsNullable));
                        }
                    }
                    catch (Exception e)
                    {
                        throw new Exception($"读取结构:{bean.FullName} 字段:{fname} 读取 出错 ==> {e.Message}", e);
                    }
                }
                else
                {
                    string sep = f.ActualSep;
                    if (string.IsNullOrWhiteSpace(sep) && IsContainerAndElementNotSepType(f.CType))
                    {
                        sep = ";,";
                    }

                    if (f.IsMultiRow)
                    {
                        try
                        {
                            list.Add(f.CType.Apply(MultiRowExcelDataCreator.Ins, row.GetColumnOfMultiRows(f.Name, sep), f.IsNullable, (DefAssembly)bean.AssemblyBase));
                        }
                        catch (Exception e)
                        {
                            throw new Exception($"读取结构:{bean.FullName} 多行字段:{f.Name} 读取 出错 ==> {e.Message}", e);
                        }
                    }
                    else
                    {
                        ExcelStream stream = row.GetColumn(f.Name, sep);
                        try
                        {
                            list.Add(f.CType.Apply(ExcelDataCreator.Ins, f.Remapper, stream, (DefAssembly)bean.AssemblyBase));
                        }
                        catch (Exception e)
                        {
                            throw new Exception($"读取结构:{bean.FullName} 字段:{f.Name} 位置:{stream.CurrentExcelPosition} 出错 ==> {e.Message}", e);
                        }
                    }
                }
            }
            return(list);
        }
        public async Task <IActionResult> Export([FromServices] IApplicationEnvironment env)
        {
            var url       = Request.Scheme + "://" + Request.Host + "/Home/Download/";
            var uid       = User.Current.Id;
            var userEmail = User.Current.Email;
            var allc      = DB.Cities.Select(x => x.Id).ToList();
            var isRoot    = User.IsInRole("Root");
            var cities    = (await UserManager.GetClaimsAsync(User.Current)).Where(x => x.Type == "管辖市区").Select(x => x.Value).ToList();
            var directory = System.IO.Path.Combine(env.ApplicationBasePath, "Export");

            if (!System.IO.Directory.Exists(directory))
            {
                System.IO.Directory.CreateDirectory(directory);
            }
            var fname = System.IO.Path.Combine(directory, Guid.NewGuid() + ".xlsx");

            using (var serviceScope = Resolver.GetRequiredService <IServiceScopeFactory>().CreateScope())
            {
                Task.Factory.StartNew(async() =>
                {
                    var db    = serviceScope.ServiceProvider.GetRequiredService <ChinaTowerContext>();
                    var email = serviceScope.ServiceProvider.GetRequiredService <IEmailSender>();
                    using (var excel = ExcelStream.Create(fname))
                        using (var sheet1 = excel.LoadSheet(1))
                        {
                            var tmp = db.Forms
                                      .Where(x => x.Status == VerificationStatus.Wrong);
                            if (!isRoot)
                            {
                                tmp = tmp.Where(x => cities.Contains(x.City) || (!cities.Contains(x.City) && !allc.Contains(x.City)));
                            }
                            var g = tmp.GroupBy(x => x.StationKey)
                                    .Select(x => new { Key = x.Key, Count = x.Count(), Details = x.Select(y => new { UniqueKey = y.UniqueKey, Type = y.Type, Logs = y.VerificationJson }) })
                                    .ToList();
                            var ids = g.Where(x => x.Key.HasValue)
                                      .Select(x => x.Key.Value.ToString())
                                      .Distinct()
                                      .ToList();
                            var dic = db.Forms
                                      .Where(x => x.Type == FormType.站址 && ids.Contains(x.UniqueKey))
                                      .ToDictionary(x => x.UniqueKey, x => new { Name = x.Name, City = x.City });
                            foreach (var x in g.Where(x => x.Key.HasValue))
                            {
                                if (!dic.ContainsKey(x.Key.Value.ToString()))
                                {
                                    continue;
                                }
                                var rowStr = $"【{dic[x.Key.Value.ToString()].Name}】 站址编码:{x.Key.Value} 错误表单:{x.Count}";
                                if (isRoot)
                                {
                                    sheet1.Add(new CodeComb.Data.Excel.Infrastructure.Row {
                                        dic[x.Key.Value.ToString()].City, rowStr
                                    });
                                }
                                else
                                {
                                    sheet1.Add(new CodeComb.Data.Excel.Infrastructure.Row {
                                        rowStr
                                    });
                                }
                                foreach (var y in x.Details)
                                {
                                    try
                                    {
                                        var log  = JsonConvert.DeserializeObject <ICollection <VerificationLog> >(y.Logs);
                                        var rows = new List <string>();
                                        foreach (var z in log)
                                        {
                                            var lines = z.Reason.Split('\n');
                                            foreach (var line in lines)
                                            {
                                                var subRow = $"┝ ◇[{y.Type}]编号:{y.UniqueKey} {line}";
                                                rows.Add(subRow);
                                            }
                                        }
                                        rows = rows
                                               .Distinct()
                                               .ToList();
                                        foreach (var r in rows)
                                        {
                                            if (isRoot)
                                            {
                                                sheet1.Add(new CodeComb.Data.Excel.Infrastructure.Row {
                                                    dic[x.Key.Value.ToString()].City, r
                                                });
                                            }
                                            else
                                            {
                                                sheet1.Add(new CodeComb.Data.Excel.Infrastructure.Row {
                                                    r
                                                });
                                            }
                                        }
                                    }
                                    catch (Exception ex)
                                    {
                                        Console.WriteLine(ex.ToString());
                                    }
                                }
                            }
                            sheet1.SaveChanges();
                        }
                    var blob = System.IO.File.ReadAllBytes(fname);
                    var b    = new Blob
                    {
                        Content       = blob,
                        ContentType   = "application/vnd.ms-excel",
                        ContentLength = blob.Length,
                        FileName      = $"校验结果导出{ DateTime.Now.ToString("yyyyMMddHHmmss") }.xlsx",
                        Time          = DateTime.Now,
                        UserId        = uid
                    };
                    db.Blobs.Add(b);
                    db.SaveChanges();
                    await email.SendEmailAsync(userEmail, "校验结果导出完毕", $"<a href=\"{ url + b.Id }\">校验结果导出{ DateTime.Now.ToString("yyyyMMddHHmmss") }.xlsx</a>");
                });
            }
            return(Prompt(x =>
            {
                x.Title = "正在导出";
                x.Details = "系统正在为您将校验结果导出到Excel,在导出完毕后,您将收到带有Excel表格附件的电子邮件,请稍候。";
            }));
        }
Example #21
0
 public Sheet(ulong Id, ExcelStream Excel, SharedStrings StringDictionary)
 {
     this.Id = Id;
     this.StringDictionary = StringDictionary;
     this.excel            = Excel;
 }
        public async Task <IActionResult> Import(FormType type, IFormFile file, [FromServices] IApplicationEnvironment env)
        {
            using (var serviceScope = Resolver.GetRequiredService <IServiceScopeFactory>().CreateScope())
            {
                var userEmail = User.Current.Email;
                var IsRoot    = User.IsInRole("Root");
                var cities    = (await UserManager.GetClaimsAsync(User.Current))
                                .Where(x => x.Type == "管辖市区")
                                .Select(x => x.Value)
                                .ToList();
                var allc = DB.Cities
                           .Select(x => x.Id)
                           .ToList();

                var directory = System.IO.Path.Combine(env.ApplicationBasePath, "Upload");
                if (!System.IO.Directory.Exists(directory))
                {
                    System.IO.Directory.CreateDirectory(directory);
                }
                var fname = System.IO.Path.Combine(directory, Guid.NewGuid() + ".xlsx");
                file.SaveAs(fname);

                Task.Factory.StartNew(async() =>
                {
                    var count    = 0;
                    var addition = 0;
                    var updated  = 0;
                    var ignored  = 0;

                    using (var db = serviceScope.ServiceProvider.GetService <ChinaTowerContext>())
                    {
                        var dvrm  = serviceScope.ServiceProvider.GetRequiredService <DataVerificationRuleManager>();
                        var rules = db.VerificationRules
                                    .Include(x => x.Rule)
                                    .Where(x => x.Type == type)
                                    .ToList();
                        using (var excel = new ExcelStream(fname))
                        {
                            foreach (var book in excel.WorkBook)
                            {
                                using (var data = excel.LoadSheet(book.Id))
                                {
                                    foreach (var row in data)
                                    {
                                        // 检查是否为表头
                                        var total   = Hash.Headers[type].Count();
                                        var similar = 0;
                                        for (var i = 0; i < row.Count && i < total; i++)
                                        {
                                            if (Hash.Headers[type][i].Contains(row[i]) || row[i].Contains(Hash.Headers[type][i]))
                                            {
                                                similar++;
                                            }
                                        }
                                        // 如果与表头字段相似度过半则认定为表头
                                        if ((double)similar / (double)total >= 0.5)
                                        {
                                            continue;
                                        }
                                        count++;
                                        var fields = new List <string>();
                                        for (var i = 0; i < Hash.Headers[type].Count(); i++)
                                        {
                                            if (i < row.Count)
                                            {
                                                fields.Add(row[i]);
                                            }
                                            else
                                            {
                                                fields.Add("");
                                            }
                                        }
                                        var verifyResult = new VerifyResult {
                                            IsSuccess = true
                                        };
                                        try
                                        {
                                            foreach (var r in rules)
                                            {
                                                var res = dvrm.Verify(r.RuleId, fields.ToArray());
                                                if (!res.IsSuccess)
                                                {
                                                    verifyResult.IsSuccess = false;
                                                }
                                            }
                                        }
                                        catch
                                        {
                                            verifyResult.Information = "未知错误";
                                            verifyResult.IsSuccess   = false;
                                        }

                                        // 如果没有校验规则
                                        if (rules.Count == 0)
                                        {
                                            verifyResult.IsSuccess   = true;
                                            verifyResult.Information = "";
                                            verifyResult.FailedRules = new List <Rule>();
                                        }
                                        // 获取字段校验结果
                                        var logs = new List <VerificationLog>();
                                        foreach (var x in verifyResult.FailedRules)
                                        {
                                            logs.Add(new VerificationLog
                                            {
                                                Field      = Hash.Headers[type][x.ArgumentIndex],
                                                Time       = DateTime.Now,
                                                Reason     = $"{Hash.Headers[type][x.ArgumentIndex]} 字段没有通过校验",
                                                FieldIndex = x.ArgumentIndex
                                            });
                                        }
                                        var form = new Form
                                        {
                                            FormJson         = JsonConvert.SerializeObject(fields),
                                            StationKey       = type == FormType.站址 ? Convert.ToInt64(fields[Hash.UniqueKey[type]]) : Convert.ToInt64(fields[Hash.StationId[type].Value]),
                                            Type             = type,
                                            UniqueKey        = fields[Hash.UniqueKey[type]],
                                            VerificationJson = JsonConvert.SerializeObject(logs),
                                            VerificationTime = DateTime.Now,
                                            Status           = verifyResult.IsSuccess ? VerificationStatus.Accepted : VerificationStatus.Wrong,
                                            City             = "",
                                            District         = ""
                                        };
                                        // 获取经纬度
                                        var gpsPosition = true;
                                        if (Hash.Lat[type] != null && Hash.Lon[type] != null)
                                        {
                                            try
                                            {
                                                form.Lat = Convert.ToDouble(fields[Hash.Lat[type].Value]);
                                                form.Lon = Convert.ToDouble(fields[Hash.Lon[type].Value]);
                                            }
                                            catch
                                            {
                                                gpsPosition = false;
                                            }
                                        }
                                        // 处理市、区信息
                                        if (Hash.City[type].HasValue)
                                        {
                                            form.City = fields[Hash.City[type].Value];
                                        }
                                        if (Hash.District[type].HasValue)
                                        {
                                            form.District = fields[Hash.District[type].Value];
                                        }
                                        if (!IsRoot && !cities.Contains(form.City) && allc.Contains(form.City))
                                        {
                                            ignored++;
                                            continue;
                                        }

                                        // 如果是站址需要额外判断
                                        if (type == FormType.站址)
                                        {
                                            form.Name = fields[0];
                                            var city  = db.Cities.SingleOrDefault(x => x.Id == form.City);
                                            // 1. 判断城市是否合法
                                            if (city == null)
                                            {
                                                var l = form.VerificationLogs;
                                                l.Add(new VerificationLog {
                                                    Time = DateTime.Now, Field = Hash.Headers[type][3], FieldIndex = 3, Reason = $"不存在城市{form.City}"
                                                });
                                                form.VerificationJson = JsonConvert.SerializeObject(l);
                                                form.Status           = VerificationStatus.Wrong;
                                            }
                                            // 2. 判断区县是否合法
                                            else if (!city.Districts.Contains(form.District))
                                            {
                                                var l = form.VerificationLogs;
                                                l.Add(new VerificationLog {
                                                    Time = DateTime.Now, Field = Hash.Headers[type][4], FieldIndex = 4, Reason = $"{city.Id}中不存在区县{form.District}"
                                                });
                                                form.VerificationJson = JsonConvert.SerializeObject(l);
                                                form.Status           = VerificationStatus.Wrong;
                                            }
                                            // 3a. 判断经纬度是否合法
                                            else if (!gpsPosition)
                                            {
                                                var l = form.VerificationLogs;
                                                l.Add(new VerificationLog {
                                                    Time = DateTime.Now, Field = Hash.Headers[type][Hash.Lon[type].Value], FieldIndex = Hash.Lon[type].Value, Reason = $"({form.Lon.Value}, {form.Lat.Value})不属于{form.City}"
                                                });
                                                l.Add(new VerificationLog {
                                                    Time = DateTime.Now, Field = Hash.Headers[type][Hash.Lat[type].Value], FieldIndex = Hash.Lat[type].Value, Reason = $"({form.Lon.Value}, {form.Lat.Value})不属于{form.City}"
                                                });
                                                form.VerificationJson = JsonConvert.SerializeObject(l);
                                                form.Status           = VerificationStatus.Wrong;
                                            }
                                            // 3b. 判断经纬度是否合法
                                            else if (!city.Edge.IsInPolygon(new CodeComb.Algorithm.Geography.Point {
                                                X = form.Lon.Value, Y = form.Lat.Value, Type = CodeComb.Algorithm.Geography.PointType.WGS
                                            }))
                                            {
                                                var l = form.VerificationLogs;
                                                l.Add(new VerificationLog {
                                                    Time = DateTime.Now, Field = Hash.Headers[type][Hash.Lon[type].Value], FieldIndex = Hash.Lon[type].Value, Reason = $"({form.Lon.Value}, {form.Lat.Value})不属于{form.City}"
                                                });
                                                l.Add(new VerificationLog {
                                                    Time = DateTime.Now, Field = Hash.Headers[type][Hash.Lat[type].Value], FieldIndex = Hash.Lat[type].Value, Reason = $"({form.Lon.Value}, {form.Lat.Value})不属于{form.City}"
                                                });
                                                form.VerificationJson = JsonConvert.SerializeObject(l);
                                                form.Status           = VerificationStatus.Wrong;
                                            }
                                        }
                                        var existedForm = db.Forms
                                                          .AsNoTracking()
                                                          .SingleOrDefault(x => x.UniqueKey == form.UniqueKey && x.Type == type);
                                        // 如果数据库中没有这条数据,则写入
                                        if (existedForm == null)
                                        {
                                            addition++;
                                            try
                                            {
                                                db.Database.ExecuteSqlCommand("INSERT INTO \"Form\" (\"FormJson\", \"StationKey\", \"Type\", \"UniqueKey\", \"VerificationJson\", \"VerificationTime\",\"Status\", \"Lon\", \"Lat\", \"City\", \"District\", \"Name\") VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11});", form.FormJson, form.StationKey, form.Type, form.UniqueKey, form.VerificationJson, form.VerificationTime, (int)form.Status, form.Lon, form.Lat, form.City, form.District, form.Name);
                                            }
                                            catch (Exception ex)
                                            {
                                                Console.WriteLine(ex.ToString());
                                            }
                                        }
                                        // 否则更新
                                        else
                                        {
                                            updated++;
                                            try
                                            {
                                                db.Database.ExecuteSqlCommand("UPDATE \"Form\" SET \"Name\" = {0}, \"Lon\" = {1}, \"Lat\" = {2}, \"StationKey\" = {3}, \"Status\" = {4}, \"Type\" = {5}, \"VerificationJson\" = {6}, \"VerificationTime\" = {7}, \"UniqueKey\" = {8}, \"City\" = {9}, \"District\" = {10} WHERE \"UniqueKey\" = {11}",
                                                                              form.Name,
                                                                              form.Lon,
                                                                              form.Lat,
                                                                              form.StationKey,
                                                                              form.Status,
                                                                              (int)form.Type,
                                                                              form.VerificationJson,
                                                                              form.VerificationTime,
                                                                              form.UniqueKey,
                                                                              form.City,
                                                                              form.District,
                                                                              form.UniqueKey);
                                            }
                                            catch (Exception ex)
                                            {
                                                Console.WriteLine(ex.ToString());
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        var email = serviceScope.ServiceProvider.GetRequiredService <IEmailSender>();
                        await email.SendEmailAsync(userEmail, "导入数据完成", $"本次导入数据已于 { DateTime.Now.ToString("yyyy年MM月dd日 HH时mm分") } 完成,共 {count} 条数据。其中新增 {addition} 条,更新 {updated} 条{ (ignored > 0 ? $",有 { ignored } 条数据不属于您的管辖区,因此系统没有允许您导入这些数据" : "") }。");
                        try
                        {
                            System.IO.File.Delete(fname);
                        }
                        catch { }
                    }
                });
            }
            return(Prompt(x =>
            {
                x.Title = "正在导入";
                x.Details = "服务器正在导入您的数据并进行相关校验,请您稍后查看即可!";
            }));
        }