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);
}
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();
}
}
}
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);
}
}
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时间表";
}));
}
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();
}
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();
}
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();
}
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"));
}
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();
}
}
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
ExcelStream.Dispose();
ExcelDoc.Dispose();
}
ExcelStream = null;
ExcelDoc = null;
disposedValue = true;
}
}
/// <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);
}
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();
}
public Sheet(ulong id, ExcelStream excel, SharedStrings stringDictionary)
{
_id = id;
_excel = excel;
StringDictionary = stringDictionary;
}
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表格附件的电子邮件，请稍候。";
}));
}
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 = "服务器正在导入您的数据并进行相关校验，请您稍后查看即可！";
}));
}