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 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 = "服务器正在导入您的数据并进行相关校验，请您稍后查看即可！";
}));
}