563 lines
30 KiB
C#
563 lines
30 KiB
C#
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();
|
||
}
|
||
|
||
}
|
||
} |