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表格附件的电子邮件,请稍候。"; })); }