private void ProceedSummaryMatrix(SalesReport report, XlWorksheet summarySheet, XlStyle matrixStyle)
{
// table columns
summarySheet.Range("B2").Value = "Count";
summarySheet.Range("C2").Value = "Revenue";
summarySheet.Range("D2").Value = "%";
summarySheet.Range("E2").Value = "Storage";
string leftBottomCellAdress = XlConverter.ToCellAdress(1, 3 + report.Products.Length);
string rightBottomCellAdress = XlConverter.ToCellAdress(5, 3 + report.Products.Length);
summarySheet.Range("$A2:$" + rightBottomCellAdress).Style = matrixStyle;
int rowIndex = 3;
int columnIndex = 1;
int i = 0;
foreach (SalesReportProduct itemProduct in report.Products)
{
string prodName = itemProduct.ProductName;
int prodId = itemProduct.ProductId;
summarySheet.Cells(rowIndex, columnIndex).Value = prodName;
string formula = string.Format("='{0}-{1}'!{2}", itemProduct.ProductName, itemProduct.ProductId, XlConverter.ToCellAdress(_monthToReport + 1, 13));
summarySheet.Cells(rowIndex, columnIndex + 1).Value = formula;
formula = string.Format("='{0}-{1}'!{2}", itemProduct.ProductName, itemProduct.ProductId, XlConverter.ToCellAdress(_monthToReport + 1, 12));
summarySheet.Cells(rowIndex, columnIndex + 2).Value = formula;
formula = string.Format("={0}*100/{1}", XlConverter.ToCellAdress(3, rowIndex), XlConverter.ToCellAdress(3, 3 + report.Products.Length));
summarySheet.Cells(rowIndex, columnIndex + 3).Formula = formula;
formula = string.Format("='{0}-{1}'!{2}", itemProduct.ProductName, itemProduct.ProductId, "B6");
summarySheet.Cells(rowIndex, columnIndex + 4).Value = formula;
int storeCount = Convert.ToInt16(summarySheet.Cells(rowIndex, columnIndex + 4).Value);
if ((i % 2) == 0)
{
summarySheet.Range("$A" + (i + 3).ToString() + ":$E" + (i + 3).ToString()).Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
}
rowIndex++;
i++;
}
string sumFormula = string.Format("=Sum({0}:{1})", "C3", "C" + (report.Products.Length + 3 - 1).ToString());
summarySheet.Cells(rowIndex, columnIndex + 2).Value = sumFormula;
summarySheet.Range("$C3:$C" + (report.Products.Length + 3).ToString()).NumberFormat = "#,##0.00 €";
summarySheet.Range("$D3:$D" + (report.Products.Length + 3).ToString()).NumberFormat = "0\"%\"";
summarySheet.Cells(3 + report.Products.Length, 1).Value = "Total:";
summarySheet.Range("D2").HorizontalAlignment = XlHAlign.xlHAlignCenter;
summarySheet.Range("$B2:$E2").Font.Bold = true;
summarySheet.Range(leftBottomCellAdress + ":" + rightBottomCellAdress).Font.Bold = true;
summarySheet.Range(leftBottomCellAdress + ":" + rightBottomCellAdress).BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlMedium);
}
private void ProceedProductMonthInfo(XlWorksheet productSheet, SalesReportProduct itemProduct)
{
int rowIndex = 9;
int iMonthCellIndex = 1;
productSheet.Range("$A9:$M13").StyleAsString = "MonthInfos";
productSheet.Cells(rowIndex + 1, iMonthCellIndex).Value = "ManufacturerPriceSummary";
productSheet.Cells(rowIndex + 2, iMonthCellIndex).Value = "SalesPricesSummary";
productSheet.Cells(rowIndex + 3, iMonthCellIndex).Value = "TotalRevenue";
productSheet.Cells(rowIndex + 4, iMonthCellIndex).Value = "CountOfSales";
iMonthCellIndex = 2;;
foreach (SalesReportReportEntity itemMonth in itemProduct.PrevMonths)
{
productSheet.Cells(rowIndex, iMonthCellIndex).Value = GetMonthName(iMonthCellIndex - 2);
productSheet.Cells(rowIndex + 1, iMonthCellIndex).Value = itemMonth.ManufactorPriceSummary;
productSheet.Cells(rowIndex + 2, iMonthCellIndex).Value = itemMonth.SalesPricesSummary;
productSheet.Cells(rowIndex + 3, iMonthCellIndex).Value = itemMonth.OutcomeSummary;
productSheet.Cells(rowIndex + 4, iMonthCellIndex).Value = itemMonth.CountOfSales;
iMonthCellIndex++;
}
string cellAdress1 = XlConverter.ToCellAdress(itemProduct.PrevMonths.Count + 2, 10);
string cellAdress2 = XlConverter.ToCellAdress(itemProduct.PrevMonths.Count + 2, 12);
productSheet.Range("$B10:$" + cellAdress1).Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.Range("$B12:$" + cellAdress2).Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.Cells(rowIndex, iMonthCellIndex).Value = GetMonthName(_monthToReport - 1);
productSheet.Cells(rowIndex + 1, iMonthCellIndex).Value = itemProduct.Month.ManufactorPriceSummary;
productSheet.Cells(rowIndex + 2, iMonthCellIndex).Value = itemProduct.Month.SalesPricesSummary;
productSheet.Cells(rowIndex + 3, iMonthCellIndex).Value = itemProduct.Month.OutcomeSummary;
productSheet.Cells(rowIndex + 4, iMonthCellIndex).Value = itemProduct.Month.CountOfSales;
for (int i = itemProduct.PrevMonths.Count + 2; i <= 12; i++)
{
iMonthCellIndex++;
productSheet.Cells(rowIndex, iMonthCellIndex).Value = GetMonthName(i - 1);
}
productSheet.Range("$B9:$M9").NumberFormat = "";
productSheet.Range("$B9:$M9").Font.Bold = true;
productSheet.Range("$B13:$M13").NumberFormat = "";
productSheet.Range("$B13:$M13").HorizontalAlignment = XlHAlign.xlHAlignCenter;
if (itemProduct.PrevMonths.Count < 11)
{
string topLeftMergeCellAdress = "$" + XlConverter.ToCellAdress(itemProduct.PrevMonths.Count + 3, 10);
productSheet.Range(topLeftMergeCellAdress + ":$M13").MergeCells = true;
}
}
private void ProceedSummaryWorksheetCharts(XlWorksheet summarySheet, int countOfProducts)
{
string captionRangeAdress = "$A2:$" + XlConverter.ToCellAdress(1, 1 + countOfProducts);
string fieldRangeAdress = "$C2:$" + XlConverter.ToCellAdress(3, 1 + countOfProducts);
double chartTopPosition = summarySheet.Rows[countOfProducts + 5].Top;
double chartWidth = summarySheet.Columns[13].Left;
XlChartObject chartSummary = summarySheet.ChartObjects.Add(1, chartTopPosition, chartWidth, 260);
chartSummary.Chart.SetSourceData(summarySheet.Range(captionRangeAdress + ";" + fieldRangeAdress));
fieldRangeAdress = "$D2:$" + XlConverter.ToCellAdress(4, 1 + countOfProducts);
chartTopPosition = summarySheet.Rows[2].Top;
double chartLeftPosition = summarySheet.Columns[8].Left;
double chartHeight = summarySheet.Rows[countOfProducts + 3].Top - chartTopPosition;
chartWidth = summarySheet.Columns[13].Left - summarySheet.Columns[8].Left;
XlChartObject chartPercentOutcome = summarySheet.ChartObjects.Add(chartLeftPosition, chartTopPosition, chartWidth, chartHeight);
chartPercentOutcome.Chart.ChartType = XlChartType.xlPie;
chartPercentOutcome.Chart.SetSourceData(summarySheet.Range(captionRangeAdress + ";" + fieldRangeAdress));
}