q020385791/demo20230512
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
main
demo20230512/Supports/ProductLCAExcelExporter.cs

563 lines
30 KiB
C#
Raw Permalink Normal View History

init
2023-05-12 10:20:28 +08:00
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.DataVisualization.Charting;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Net;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using Weee.Models;
using Weee.Models.ExtensionMethods;
using Resources;
namespace Weee.Supports
{
public class ProductLCAExcelExporter : ExcelExporter
{
public void InsertLcaData(IWorkbook workbook, ISheet sheet, ProductLCA productLca)
{
var grayColor = new XSSFColor(Color.LightGray);
var headerStyle = CreateCommonStyleWithBorder(workbook, grayColor);
var borderStyle = CreateCommonStyleWithBorder(workbook);
var noneBorderStyle = CreateCommonStyleWithoutBorder(workbook);
if (productLca.Owner != null)
InsertLogo(workbook, sheet, productLca.Owner.LogoUrl);
if (productLca.SnapShotSurveyResult == null)
throw new Exception("SnapShotSurveyResult can not be null");
InsertTitle(sheet);
SetCellStyle(sheet, noneBorderStyle, 0, 0, 0, 1);
InsertFabSubTitle(sheet);
SetCellStyle(sheet, noneBorderStyle, 4, 4, 0, 0);
InsertPrimaryFabDataScopeTitle(sheet);
SetCellStyle(sheet, headerStyle, 5, 5, 0, 8);
InsertPrimaryFabDataScopeOne(sheet, productLca);
SetCellStyle(sheet, headerStyle, 6, 6, 0, 8);
SetCellStyle(sheet, borderStyle, 7, 7, 0, 8);
InsertPrimaryFabDataScopeTwo(sheet, productLca);
SetCellStyle(sheet, headerStyle, 9, 9, 0, 3);
SetCellStyle(sheet, borderStyle, 10, 10, 0, 3);
InsertPrimaryFabDataScopeThree(sheet, productLca);
SetCellStyle(sheet, headerStyle, 12, 12, 0, 4);
SetCellStyle(sheet, borderStyle, 13, 13, 0, 4);
InsertPrimaryFabData(sheet, productLca);
SetCellStyle(sheet, headerStyle, 15, 17, 0, 1);
SetCellStyle(sheet, headerStyle, 15, 16, 2, 2);
SetCellStyle(sheet, borderStyle, 17, 17, 2, 2);
InsertSecondaryFabData(sheet, productLca);
SetCellStyle(sheet, headerStyle, 15, 16, 4, 5);
SetCellStyle(sheet, borderStyle, 17, 17, 4, 5);
InsertTotalFabData(sheet, productLca);
SetCellStyle(sheet, headerStyle, 15, 16, 7, 8);
SetCellStyle(sheet, borderStyle, 17, 17, 7, 8);
InsertProductSubTitle(sheet);
SetCellStyle(sheet, noneBorderStyle, 19, 19, 0, 0);
InsertProductInfo(sheet, productLca);
SetCellStyle(sheet, noneBorderStyle, 20, 21, 0, 1);
InsertProductData(sheet, productLca);
SetCellStyle(sheet, headerStyle, 22, 23, 0, 2);
SetCellStyle(sheet, borderStyle, 24, 26, 0, 2);
InsertPrimaryAndSecondaryFabData(sheet, productLca);
SetCellStyle(sheet, headerStyle, 22, 23, 4, 8);
SetCellStyle(sheet, borderStyle, 24, 26, 4, 8);
InsertSecondaryProductData(sheet, productLca.SnapShotSurveyResult);
SetCellStyle(sheet, headerStyle, 28, 29, 4, 7);
SetCellStyle(sheet, borderStyle, 30, 30, 4, 7);
var totalKgCO2e = productLca.SnapShotSurveyResult.Scope1FabResult
+ productLca.SnapShotSurveyResult.Scope2FabResult
+ productLca.SnapShotSurveyResult.Scope3FabResult
+ productLca.SnapShotSurveyResult.SecondaryWasteResult
+ productLca.SnapShotSurveyResult.SecondaryWasteTransportResult;
InsertChartWithFabCarbonEmissionDistributeByWeight(workbook, sheet, productLca, totalKgCO2e);
InsertChartWithFabCarbonEmissionDistributeByArea(workbook, sheet, productLca, totalKgCO2e);
InsertChartWithFabCarbonEmissionDistributeByHour(workbook, sheet, productLca, totalKgCO2e);
}
private void InsertLogo(IWorkbook workbook, ISheet sheet, string PhotoUrl)
{
if (PhotoUrl != null)
{
//Dl-53 begin
string actPath = AppDomain.CurrentDomain.BaseDirectory;
string uriPath = System.Web.HttpContext.Current.Request.Url.Authority;
PhotoUrl = actPath + (PhotoUrl.Substring(1)).Replace("/", "\\");
var uri = new Uri(PhotoUrl);
var imageRequest = WebRequest.Create(uri);
//var imageRequest = (HttpWebRequest)WebRequest.Create(PhotoUrl);
//end
var imageResponse = imageRequest.GetResponse();
var image = Image.FromStream(imageResponse.GetResponseStream());
var imageMemoryStream = new MemoryStream();
image.Save(imageMemoryStream, ImageFormat.Png);
var patriarch = sheet.CreateDrawingPatriarch();
var anchor = new XSSFClientAnchor(0, 0, 2, 3, 0, 0, 1, 3);
var pictureIndex = workbook.AddPicture(imageMemoryStream.GetBuffer(), PictureType.PNG);
patriarch.CreatePicture(anchor, pictureIndex);
imageMemoryStream.Close();
}
}
private void InsertTitle(ISheet sheet)
{
sheet.AddMergedRegion(new CellRangeAddress(0, 2, 1, 8));
sheet.GetRow(0).GetCell(1).SetCellValue(Resource.ProductLcaInterrogationTitle);
}
private void InsertFabSubTitle(ISheet sheet)
{
sheet.GetRow(4).GetCell(0).SetCellValue(Resource.FabCarbonEmissionsA);
}
private void InsertPrimaryFabDataScopeTitle(ISheet sheet)
{
sheet.AddMergedRegion(new CellRangeAddress(5, 5, 0, 1));
sheet.AddMergedRegion(new CellRangeAddress(5, 5, 2, 8));
sheet.GetRow(5).GetCell(0).SetCellValue("Scope");
sheet.GetRow(5).GetCell(2).SetCellValue(Resource.TheInterrogationOfCarbonEmissions);
}
private void InsertPrimaryFabDataScopeOne(ISheet sheet, ProductLCA productLca)
{
sheet.AddMergedRegion(new CellRangeAddress(6, 7, 0, 0));
sheet.AddMergedRegion(new CellRangeAddress(6, 7, 1, 1));
sheet.GetRow(6).GetCell(0).SetCellValue("Ⅰ");
sheet.GetRow(6).GetCell(1).SetCellValue(Resource.ScopeOneTitle);
sheet.GetRow(6).GetCell(2).SetCellValue(Resource.SepticOfTotalWorkHour);
sheet.GetRow(6).GetCell(3).SetCellValue(Resource.CompanyVehicle);
sheet.GetRow(6).GetCell(4).SetCellValue(Resource.DieselEquipment);
sheet.GetRow(6).GetCell(5).SetCellValue(Resource.CompanyKitchenAndOtherFuelsA);
sheet.GetRow(6).GetCell(6).SetCellValue(Resource.RefrigerantEquipment);
sheet.GetRow(6).GetCell(7).SetCellValue(Resource.FireEquipment);
sheet.GetRow(6).GetCell(8).SetCellValue(Resource.OtherCompound);
sheet.GetRow(7).GetCell(2).SetCellValue(Math.Round(productLca.WorkHourSheet.GetTotalKgCO2e(), 2).ToString());
sheet.GetRow(7).GetCell(3).SetCellValue(Math.Round(productLca.VehicleSheet.Where(x => x.Type == Vehicle.VehicleType.owned).ToList().GetTotalKgCO2e(), 2).ToString());
sheet.GetRow(7).GetCell(4).SetCellValue(Math.Round(productLca.GasolineEquipmentSheet.GetTotalKgCO2e(), 2).ToString());
sheet.GetRow(7).GetCell(5).SetCellValue(Math.Round(productLca.KitchenSheet.Where(x => x.Type != Kitchen.KitchenType.outsourcing).ToList().GetTotalKgCO2e(), 2).ToString());
sheet.GetRow(7).GetCell(6).SetCellValue(Math.Round(productLca.RefrigerantSheet.GetTotalKgCO2e(), 2).ToString());
sheet.GetRow(7).GetCell(7).SetCellValue(Math.Round(productLca.FireEquipmentSheet.GetTotalKgCO2e(), 2).ToString());
sheet.GetRow(7).GetCell(8).SetCellValue(Math.Round(productLca.OtherCompoundSheet.GetTotalKgCO2e(), 2).ToString());
}
private void InsertPrimaryFabDataScopeTwo(ISheet sheet, ProductLCA productLca)
{
sheet.AddMergedRegion(new CellRangeAddress(9, 10, 0, 0));
sheet.AddMergedRegion(new CellRangeAddress(9, 10, 1, 1));
sheet.GetRow(9).GetCell(0).SetCellValue("Ⅱ");
sheet.GetRow(9).GetCell(1).SetCellValue(Resource.ScopeTwoTitle);
sheet.GetRow(9).GetCell(2).SetCellValue(Resource.PowerUsage);
sheet.GetRow(9).GetCell(3).SetCellValue(Resource.SteamUsage);
sheet.GetRow(10).GetCell(2).SetCellValue(Math.Round(productLca.PowerUsageSheet.GetTotalKgCO2e(), 2).ToString());
sheet.GetRow(10).GetCell(3).SetCellValue(Math.Round(productLca.SteamUsageSheet.GetTotalKgCO2e(), 2).ToString());
}
private void InsertPrimaryFabDataScopeThree(ISheet sheet, ProductLCA productLca)
{
sheet.AddMergedRegion(new CellRangeAddress(12, 13, 0, 0));
sheet.AddMergedRegion(new CellRangeAddress(12, 13, 1, 1));
sheet.GetRow(12).GetCell(0).SetCellValue("Ⅲ");
sheet.GetRow(12).GetCell(1).SetCellValue(Resource.ScopeThreeTitle);
sheet.GetRow(12).GetCell(2).SetCellValue(Resource.WaterUsage);
sheet.GetRow(12).GetCell(3).SetCellValue(Resource.RentVehicle);
sheet.GetRow(12).GetCell(4).SetCellValue(Resource.OutSourcingKitchenFuel);
sheet.GetRow(13).GetCell(2).SetCellValue(productLca.WaterUsageSheet.GetTotalKgCO2e().ToString());
sheet.GetRow(13).GetCell(3).SetCellValue(productLca.VehicleSheet.Where(x => x.Type == Vehicle.VehicleType.rental).ToList().GetTotalKgCO2e().ToString());
sheet.GetRow(13).GetCell(4).SetCellValue(productLca.KitchenSheet.Where(x => x.Type == Kitchen.KitchenType.outsourcing).ToList().GetTotalKgCO2e().ToString());
}
private void InsertPrimaryFabData(ISheet sheet, ProductLCA productLca)
{
sheet.AddMergedRegion(new CellRangeAddress(15, 17, 0, 1));
sheet.GetRow(15).GetCell(0).SetCellValue(Resource.FabCarbonEmissionsB);
sheet.GetRow(15).GetCell(2).SetCellValue(Resource.PrimaryData);
sheet.GetRow(16).GetCell(2).SetCellValue("Scope Ⅰ, Ⅱ, Ⅲ");
sheet.GetRow(17).GetCell(2).SetCellValue(Math.Round(productLca.GetPrimaryFabKgCO2e(), 2).ToString());
}
private void InsertSecondaryFabData(ISheet sheet, ProductLCA productLca)
{
sheet.GetRow(16).GetCell(3).SetCellValue("+");
sheet.AddMergedRegion(new CellRangeAddress(15, 15, 4, 5));
sheet.GetRow(15).GetCell(4).SetCellValue(Resource.SecondaryData);
sheet.GetRow(16).GetCell(4).SetCellValue(Resource.Waste);
sheet.GetRow(16).GetCell(5).SetCellValue(Resource.WasteTransportation);
sheet.GetRow(17).GetCell(4).SetCellValue(Math.Round(productLca.WasteSheet.GetTotalKgCO2e(), 2).ToString());
sheet.GetRow(17).GetCell(5).SetCellValue(Math.Round(productLca.WasteTransportSheet.GetTotalKgCO2e(), 2).ToString());
}
private void InsertTotalFabData(ISheet sheet, ProductLCA productLca)
{
sheet.GetRow(16).GetCell(6).SetCellValue("=");
sheet.AddMergedRegion(new CellRangeAddress(15, 15, 7, 8));
sheet.AddMergedRegion(new CellRangeAddress(16, 16, 7, 8));
sheet.AddMergedRegion(new CellRangeAddress(17, 17, 7, 8));
sheet.GetRow(15).GetCell(7).SetCellValue(Resource.TotalEmissions);
sheet.GetRow(16).GetCell(7).SetCellValue("KgCO2e");
sheet.GetRow(17).GetCell(7).SetCellValue(productLca.GetTotalFabKgCO2e().ToString());
}
private void InsertProductSubTitle(ISheet sheet)
{
sheet.GetRow(19).GetCell(0).SetCellValue(Resource.ProductCarbonEmission);
}
private void InsertProductInfo(ISheet sheet, ProductLCA productLca)
{
sheet.GetRow(20).GetCell(0).SetCellValue(Resource.ProductName + ":");
sheet.GetRow(20).GetCell(1).SetCellValue(productLca.LCATypeDisplayName);
sheet.GetRow(21).GetCell(1).SetCellValue(productLca.TargetProduct.SpecDescription);
}
private void InsertProductData(ISheet sheet, ProductLCA productLca)
{
sheet.AddMergedRegion(new CellRangeAddress(22, 23, 0, 1));
sheet.AddMergedRegion(new CellRangeAddress(22, 23, 2, 2));
sheet.AddMergedRegion(new CellRangeAddress(24, 24, 0, 1));
sheet.AddMergedRegion(new CellRangeAddress(25, 25, 0, 1));
sheet.AddMergedRegion(new CellRangeAddress(26, 26, 0, 1));
sheet.GetRow(22).GetCell(0).SetCellValue(Resource.VariousAspectsOfTheProductDistribution);
sheet.GetRow(22).GetCell(2).SetCellValue(Resource.ProductCarbonEmission + " (KgCO2e)");
sheet.GetRow(24).GetCell(0).SetCellValue(Resource.Weight);
sheet.GetRow(24).GetCell(2).SetCellValue(productLca.DistributeKgCO2eInWeight.HasValue ? productLca.DistributeKgCO2eInWeight.ToString() : "");
sheet.GetRow(25).GetCell(0).SetCellValue(Resource.AreaB);
sheet.GetRow(25).GetCell(2).SetCellValue(productLca.DistributeKgCO2eInArea.HasValue ? productLca.DistributeKgCO2eInArea.ToString() : "");
sheet.GetRow(26).GetCell(0).SetCellValue(Resource.WorkHour);
sheet.GetRow(26).GetCell(2).SetCellValue(productLca.DistributeKgCO2eInWorkHour.HasValue ? productLca.DistributeKgCO2eInWorkHour.ToString() : "");
}
private void InsertPrimaryAndSecondaryFabData(ISheet sheet, ProductLCA productLca)
{
var snapShot = productLca.SnapShotSurveyResult;
var weightFactor = snapShot.constantFactorInWeight;
var areaFactor = snapShot.constantFactorInArea;
var workhourFactor = snapShot.constantFactorInWorkHour;
var wasteTotalKgCO2e = productLca.WasteSheet.Sum(x => x.KgCO2e);
var wastetransportTotalKgCO2e = productLca.WasteTransportSheet.Sum(x => x.KgCO2e);
sheet.AddMergedRegion(new CellRangeAddress(22, 22, 4, 6));
sheet.AddMergedRegion(new CellRangeAddress(22, 22, 7, 8));
sheet.GetRow(22).GetCell(4).SetCellValue(Resource.PrimaryData + " / Scope");
sheet.GetRow(22).GetCell(7).SetCellValue("Secondary Data");
sheet.GetRow(23).GetCell(4).SetCellValue("Ⅰ");
sheet.GetRow(23).GetCell(5).SetCellValue("Ⅱ");
sheet.GetRow(23).GetCell(6).SetCellValue("Ⅲ");
sheet.GetRow(23).GetCell(7).SetCellValue(Resource.Waste);
sheet.GetRow(23).GetCell(8).SetCellValue(Resource.WasteTransportation);
//Math.Round(productLca.GetScopeOneDistributeByWeightFabKgCO2e(), 2).ToString()
sheet.GetRow(24).GetCell(4).SetCellValue(weightFactor == null ? "#DIV/0" : (snapShot.Scope1FabResult * weightFactor.Value).ToString());
sheet.GetRow(24).GetCell(5).SetCellValue(weightFactor == null ? "#DIV/0" : (snapShot.Scope2FabResult * weightFactor.Value).ToString());
sheet.GetRow(24).GetCell(6).SetCellValue(weightFactor == null ? "#DIV/0" : (snapShot.Scope3FabResult * weightFactor.Value).ToString());
sheet.GetRow(24).GetCell(7).SetCellValue(weightFactor == null ? "#DIV/0" : (wasteTotalKgCO2e * weightFactor.Value).ToString());
sheet.GetRow(24).GetCell(8).SetCellValue(weightFactor == null ? "#DIV/0" : (wastetransportTotalKgCO2e * weightFactor.Value).ToString());
sheet.GetRow(25).GetCell(4).SetCellValue(areaFactor == null ? "#DIV/0" : (snapShot.Scope1FabResult * areaFactor.Value).ToString());
sheet.GetRow(25).GetCell(5).SetCellValue(areaFactor == null ? "#DIV/0" : (snapShot.Scope2FabResult * areaFactor.Value).ToString());
sheet.GetRow(25).GetCell(6).SetCellValue(areaFactor == null ? "#DIV/0" : (snapShot.Scope3FabResult * areaFactor.Value).ToString());
sheet.GetRow(25).GetCell(7).SetCellValue(areaFactor == null ? "#DIV/0" : (wasteTotalKgCO2e * areaFactor.Value).ToString());
sheet.GetRow(25).GetCell(8).SetCellValue(areaFactor == null ? "#DIV/0" : (wastetransportTotalKgCO2e * areaFactor.Value).ToString());
sheet.GetRow(26).GetCell(4).SetCellValue(workhourFactor == null ? "#DIV/0" : (snapShot.Scope1FabResult * workhourFactor.Value).ToString());
sheet.GetRow(26).GetCell(5).SetCellValue(workhourFactor == null ? "#DIV/0" : (snapShot.Scope2FabResult * workhourFactor.Value).ToString());
sheet.GetRow(26).GetCell(6).SetCellValue(workhourFactor == null ? "#DIV/0" : (snapShot.Scope3FabResult * workhourFactor.Value).ToString());
sheet.GetRow(26).GetCell(7).SetCellValue(workhourFactor == null ? "#DIV/0" : (wasteTotalKgCO2e * workhourFactor.Value).ToString());
sheet.GetRow(26).GetCell(8).SetCellValue(workhourFactor == null ? "#DIV/0" : (wastetransportTotalKgCO2e * workhourFactor.Value).ToString());
}
private void InsertSecondaryProductData(ISheet sheet, SnapShotSurveyResult snapShot)
{
sheet.AddMergedRegion(new CellRangeAddress(28, 28, 4, 7));
sheet.GetRow(28).GetCell(4).SetCellValue(Resource.SecondaryData);
sheet.GetRow(29).GetCell(4).SetCellValue(Resource.StaticLabelGlobal_LCIDirectMaterial);
sheet.GetRow(29).GetCell(5).SetCellValue(Resource.StaticLabelGlobal_LCIIndirectMaterial);
sheet.GetRow(29).GetCell(6).SetCellValue(Resource.StaticLabelGlobal_LCIWrapMaterial);
sheet.GetRow(29).GetCell(7).SetCellValue(Resource.Transport);
sheet.GetRow(30).GetCell(4).SetCellValue(snapShot.SecondaryDirectMaterialResult.ToString());
sheet.GetRow(30).GetCell(5).SetCellValue(snapShot.SecondaryIndirectMaterialResult.ToString());
sheet.GetRow(30).GetCell(6).SetCellValue(snapShot.SecondaryWrapMaterialResult.ToString());
sheet.GetRow(30).GetCell(7).SetCellValue(snapShot.SecondaryTransportResult.ToString());
}
private void InsertChartWithFabCarbonEmissionDistributeByWeight(IWorkbook workbook, ISheet sheet, ProductLCA productLca,decimal totalKgCO2e)
{
var isNotValid = productLca.SnapShotSurveyResult.constantFactorInWeight == null || totalKgCO2e == 0m;
var chart = new Chart();
chart.Titles.Add(Resource.DistributeByWeight);
chart.Width = 1280;
chart.Height = 720;
var chartArea = chart.ChartAreas.Add("distributeByWeight");
chartArea.AxisY.Title = Resource.Percentage;
chartArea.AxisX.MajorGrid.LineWidth = 0;
chartArea.AxisX2.Enabled = AxisEnabled.False;
chartArea.AxisX.Interval = 1;
var series = chart.Series.Add("distributeByWeight");
series.ChartType = SeriesChartType.Column;
series.ChartArea = "distributeByWeight";
series.IsValueShownAsLabel = true;
var workHour = productLca.WorkHourSheet;
var workHourTotalKGCO2e = workHour.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.Septic, isNotValid ? 0 : (workHourTotalKGCO2e / totalKgCO2e));
var ownVehicle = productLca.VehicleSheet.Where(x => x.Type == Vehicle.VehicleType.owned).ToList().Sum(x=>x.KgCO2e);
series.Points.AddXY(Resource.CompanyVehicle, isNotValid ? 0 : (ownVehicle / totalKgCO2e));
var gaslineEquipment = productLca.GasolineEquipmentSheet.Sum(x=>x.KgCO2e);
series.Points.AddXY(Resource.DieselEquipment, isNotValid ? 0 : (gaslineEquipment / totalKgCO2e));
var ownedAndOtherKitchen = productLca.KitchenSheet
.Where(x => x.Type != Kitchen.KitchenType.outsourcing)
.ToList()
.Sum(x=>x.KgCO2e);
series.Points.AddXY(Resource.CompanyKitchenAndOtherFuelsB, isNotValid ? 0 : (ownedAndOtherKitchen / totalKgCO2e));
var refrigerant = productLca.RefrigerantSheet.Sum(x=>x.KgCO2e);
series.Points.AddXY(Resource.EquipmentContainingRefrigerant, isNotValid ? 0 : (refrigerant / totalKgCO2e));
var fireEquipment = productLca.FireEquipmentSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.FireEquipment, isNotValid ? 0 : (fireEquipment / totalKgCO2e));
var otherCompound = productLca.OtherCompoundSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.OtherCompound, isNotValid ? 0 : (otherCompound / totalKgCO2e));
var powerUsage = productLca.PowerUsageSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.Power, isNotValid ? 0 : (powerUsage / totalKgCO2e));
var steamUsage = productLca.SteamUsageSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.Steam, isNotValid ? 0 : (steamUsage / totalKgCO2e));
var waterUsage = productLca.WaterUsageSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.Water, isNotValid ? 0 : (waterUsage / totalKgCO2e));
var rentalVehicle = productLca.VehicleSheet
.Where(x => x.Type == Vehicle.VehicleType.rental)
.ToList()
.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.RentVehicle, isNotValid ? 0 : (rentalVehicle / totalKgCO2e));
var outSourcingKitchen = productLca.KitchenSheet
.Where(x => x.Type == Kitchen.KitchenType.outsourcing)
.ToList()
.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.OutSourcingKitchenAndOtherFuels, isNotValid ? 0 : (outSourcingKitchen / totalKgCO2e));
var waste = productLca.WasteSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.Waste, isNotValid ? 0 : (waste / totalKgCO2e));
var wasteTransport = productLca.WasteTransportSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.WasteTransportation, isNotValid ? 0 : (wasteTransport / totalKgCO2e));
CreateImage(workbook, sheet, chart, 0, 37, 9, 62);
}
private void InsertChartWithFabCarbonEmissionDistributeByArea(IWorkbook workbook, ISheet sheet, ProductLCA productLca, decimal totalKgCO2e)
{
var isNotValid = productLca.SnapShotSurveyResult.constantFactorInArea == null || totalKgCO2e == 0m;
var chart = new Chart();
chart.Titles.Add(Resource.DistributeByArea);
chart.Width = 1280;
chart.Height = 720;
var chartArea = chart.ChartAreas.Add("distributeByArea");
chartArea.AxisY.Title = Resource.Percentage;
chartArea.AxisX.MajorGrid.LineWidth = 0;
chartArea.AxisX2.Enabled = AxisEnabled.False;
chartArea.AxisX.Interval = 1;
var series = chart.Series.Add("distributeByArea");
series.ChartType = SeriesChartType.Column; // Chart type
series.ChartArea = "distributeByArea";
series.IsValueShownAsLabel = true;
var workHour = productLca.WorkHourSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.Septic, isNotValid ? 0 : (workHour / totalKgCO2e));
var ownedVehicle = productLca.VehicleSheet
.Where(x => x.Type == Vehicle.VehicleType.owned)
.ToList()
.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.CompanyVehicle, isNotValid ? 0 : (ownedVehicle / totalKgCO2e));
var gasolineEquipment = productLca.GasolineEquipmentSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.DieselEquipment, isNotValid ? 0 : (gasolineEquipment / totalKgCO2e));
var ownedAndOtherKitchen = productLca.KitchenSheet
.Where(x => x.Type != Kitchen.KitchenType.outsourcing)
.ToList()
.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.CompanyKitchenAndOtherFuelsB, isNotValid ? 0 : (ownedAndOtherKitchen / totalKgCO2e));
var refrigerant = productLca.RefrigerantSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.EquipmentContainingRefrigerant, isNotValid ? 0 : (refrigerant / totalKgCO2e));
var fireEquipment = productLca.FireEquipmentSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.FireEquipment, isNotValid ? 0 : (fireEquipment / totalKgCO2e));
var otherCompound = productLca.OtherCompoundSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.OtherCompound, isNotValid ? 0 : (otherCompound / totalKgCO2e));
var powerUsage = productLca.PowerUsageSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.Power, isNotValid ? 0 : (powerUsage / totalKgCO2e));
var steamUsage = productLca.SteamUsageSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.Steam, isNotValid ? 0 : (steamUsage / totalKgCO2e));
var waterUsage = productLca.WaterUsageSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.Water, isNotValid ? 0 : (waterUsage / totalKgCO2e));
var rentalVehicle = productLca.VehicleSheet
.Where(x => x.Type == Vehicle.VehicleType.rental)
.ToList()
.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.RentVehicle, isNotValid ? 0 : (rentalVehicle / totalKgCO2e));
var outSourcingKitchen = productLca.KitchenSheet
.Where(x => x.Type == Kitchen.KitchenType.outsourcing)
.ToList()
.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.OutSourcingKitchenAndOtherFuels, isNotValid ? 0 : (outSourcingKitchen / totalKgCO2e));
var waste = productLca.WasteSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.Waste, isNotValid ? 0 : (waste / totalKgCO2e));
var wasteTransport = productLca.WasteTransportSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.WasteTransportation, isNotValid ? 0 : (wasteTransport / totalKgCO2e));
CreateImage(workbook, sheet, chart, 0, 64, 9, 89);
}
private void InsertChartWithFabCarbonEmissionDistributeByHour(IWorkbook workbook, ISheet sheet, ProductLCA productLca, decimal totalKgCO2e)
{
var isNotValid = productLca.SnapShotSurveyResult.constantFactorInWorkHour == null||totalKgCO2e==0m;
var chart = new Chart();
chart.Titles.Add(Resource.DistributeByHour);
chart.Width = 1280;
chart.Height = 720;
var chartArea = chart.ChartAreas.Add("distributeByHour");
chartArea.AxisY.Title = Resource.Percentage;
chartArea.AxisX.MajorGrid.LineWidth = 0;
chartArea.AxisX2.Enabled = AxisEnabled.False;
chartArea.AxisX.Interval = 1;
var series = chart.Series.Add("distributeByHour");
series.ChartType = SeriesChartType.Column; // Chart type
series.ChartArea = "distributeByHour";
series.IsValueShownAsLabel = true;
var workHour = productLca.WorkHourSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.Septic, isNotValid ? 0 : (workHour / totalKgCO2e));
var ownedVehicle = productLca.VehicleSheet
.Where(x => x.Type == Vehicle.VehicleType.owned)
.ToList()
.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.CompanyVehicle, isNotValid ? 0 : (ownedVehicle / totalKgCO2e));
var gasolineEquipment = productLca.GasolineEquipmentSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.DieselEquipment, isNotValid ? 0 : (gasolineEquipment / totalKgCO2e));
var ownedAndOtherKitchen = productLca.KitchenSheet
.Where(x => x.Type != Kitchen.KitchenType.outsourcing)
.ToList()
.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.CompanyKitchenAndOtherFuelsB, isNotValid ? 0 : (ownedAndOtherKitchen / totalKgCO2e));
var refrigerant = productLca.RefrigerantSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.EquipmentContainingRefrigerant, isNotValid ? 0 : (refrigerant / totalKgCO2e));
var fireEquipment = productLca.FireEquipmentSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.FireEquipment, isNotValid ? 0 : (fireEquipment / totalKgCO2e));
var otherCompound = productLca.OtherCompoundSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.OtherCompound, isNotValid ? 0 : (otherCompound / totalKgCO2e));
var powerUsage = productLca.PowerUsageSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.Power, isNotValid ? 0 : (powerUsage / totalKgCO2e));
var steamUsage = productLca.SteamUsageSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.Steam, isNotValid ? 0 : (steamUsage / totalKgCO2e));
var waterUsage = productLca.WaterUsageSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.Water, isNotValid ? 0 : (waterUsage / totalKgCO2e));
var rentalVehicle = productLca.VehicleSheet
.Where(x => x.Type == Vehicle.VehicleType.rental)
.ToList()
.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.RentVehicle, isNotValid ? 0 : (rentalVehicle / totalKgCO2e));
var outSourcingKitchen = productLca.KitchenSheet
.Where(x => x.Type == Kitchen.KitchenType.outsourcing)
.ToList()
.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.OutSourcingKitchenAndOtherFuels, isNotValid ? 0 : (outSourcingKitchen / totalKgCO2e));
var waste = productLca.WasteSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.Waste, isNotValid ? 0 : (waste / totalKgCO2e));
var wasteTransport = productLca.WasteTransportSheet.Sum(x => x.KgCO2e);
series.Points.AddXY(Resource.WasteTransportation, isNotValid ? 0 : (wasteTransport / totalKgCO2e));
CreateImage(workbook, sheet, chart, 0, 91, 9, 116);
}
private void CreateImage(IWorkbook workbook, ISheet sheet, Chart chart, int col1, int row1, int col2, int row2)
{
var imageMemoryStream = new MemoryStream();
chart.SaveImage(imageMemoryStream, ChartImageFormat.Png);
var patriarch = sheet.CreateDrawingPatriarch();
var anchor = new XSSFClientAnchor(0, 0, 0, 0, col1, row1, col2, row2);
var pictureIndex = workbook.AddPicture(imageMemoryStream.ToArray(), PictureType.PNG);
var signaturePicture = patriarch.CreatePicture(anchor, pictureIndex);
imageMemoryStream.Close();
}
}
}
Reference in New Issue Copy Permalink