public void WriteData() { this.ChangeState(new Notification { Message = "Generating JSON reports and Importing them into MySql..." }); using (var db = new ArtGalleryDbContext()) { var artWorkReports = db.ArtWorks.Select(a => new ArtWorkJsonReport { Id = a.Id, Name = a.Title, Type = a.Type.ToString(), Status = a.Status.ToString(), Income = a.DateSold.HasValue ? a.Value : 0 }) .AsEnumerable(); var counter = 0; var mySql = new ArtGalleryMySqlDbContext(); foreach (var report in artWorkReports) { this.SaveAndWriteReport(report, report.Id); mySql.Reports.Add(report); mySql.SaveChanges(); counter += 1; if (counter == 20) { mySql.Dispose(); mySql = new ArtGalleryMySqlDbContext(); } } } this.ChangeState(new Notification { Message = "Done." }); }
private void GenerateExcelReport() { List<ArtWorkDetails> details; List<ArtWorkJsonReport> reports; using (var mySql = new ArtGalleryMySqlDbContext()) { reports = mySql.Reports.ToList(); using (var sqLite = new ArtGallerySqlLiteDbContext()) { details = sqLite.ArtWorksDetails.ToList(); } } var joinedData = from report in reports join detail in details on report.Name equals detail.ArtWorkName select new { Name = report.Name, Type = report.Type, Income = report.Income, Weight = detail.Weight, Layers = detail.NumberOfLayersOfMaterial }; // Name, Type, Income, Weight, Materials Layers int rowNumber = 0; var doc = new XSSFWorkbook(); var sheet = (XSSFSheet)doc.CreateSheet(); var firstRow = sheet.CreateRow(rowNumber++); var nameTitle = firstRow.CreateCell(0, NPOI.SS.UserModel.CellType.String); nameTitle.SetCellValue("Name"); var typeTitle = firstRow.CreateCell(1, NPOI.SS.UserModel.CellType.String); typeTitle.SetCellValue("Type"); var incomeTitle = firstRow.CreateCell(2, NPOI.SS.UserModel.CellType.String); incomeTitle.SetCellValue("Income"); var weightTitle = firstRow.CreateCell(3, NPOI.SS.UserModel.CellType.String); weightTitle.SetCellValue("Weight"); var layersTitle = firstRow.CreateCell(4, NPOI.SS.UserModel.CellType.String); layersTitle.SetCellValue("Materials Layers"); foreach (var item in joinedData) { var row = sheet.CreateRow(rowNumber++); var nameCell = row.CreateCell(0, NPOI.SS.UserModel.CellType.String); nameCell.SetCellValue(item.Name); var typeCell = row.CreateCell(1, NPOI.SS.UserModel.CellType.String); typeCell.SetCellValue(item.Type); var incomeCell = row.CreateCell(2, NPOI.SS.UserModel.CellType.Numeric); incomeCell.SetCellValue((double)item.Income); var weightCell = row.CreateCell(3, NPOI.SS.UserModel.CellType.Numeric); weightCell.SetCellValue((double)item.Weight); var layersCell = row.CreateCell(4, NPOI.SS.UserModel.CellType.Numeric); layersCell.SetCellValue(item.Layers); } sheet.DefaultColumnWidth = 30; using (var fs = new FileStream(PathToExcelReport, FileMode.Create, FileAccess.Write)) { doc.Write(fs); } }