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);
}
}
private void GenerateSqLiteRecords()
{
using (var db = new ArtGallerySqlLiteDbContext())
{
var random = new Random();
ICollection<string> art = this.dataLoader.GetArtworks();
foreach (var item in art)
{
var details = new ArtWorkDetails
{
ArtWorkName = item,
NumberOfLayersOfMaterial = random.Next(1, 6),
Weight = ((decimal)random.Next(10, 1000)) / 10
};
db.ArtWorksDetails.Add(details);
}
db.SaveChanges();
}
}