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);
}
}
