public void Test_Creat_Excel()
{
using (var x = _ExcelStream.Create(_ExcelPath))
{
using (var sheet = x.LoadSheet(1))
{
sheet.Add(new Pomelo.Data.Excel.Infrastructure.Row
{
"Create test"
});
sheet.SaveChanges();
}
}
Assert.True(File.Exists(_ExcelPath));
}
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 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 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> 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表格附件的电子邮件，请稍候。";
}));
}
