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 button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
_excelApplication = new XlApplication();
_excelApplication.DisplayAlerts = false;
_excelApplication.ScreenUpdating = false;
// add a new workbook
XlWorkbook workBook = _excelApplication.Workbooks.Add();
// we use the first sheet as summary sheet and remove the 2 last sheets
XlWorksheet summarySheet = workBook.Worksheets[1];
workBook.Worksheets[3].Delete();
workBook.Worksheets[2].Delete();
// we get the data & perform the report
_report = new SalesReport(_yearToReport, _monthToReport);
_report.Proceed();
// we create named styles for the range.Style property
CreateStorageAndRankingStyle(workBook, "StorageAndRanking");
CreateMonthStyle(workBook, "MonthInfos");
CreateMonthStyle(workBook, "YearTotalInfos");
// write product sheets
XlWorksheet productSheet = null;
foreach (SalesReportProduct itemProduct in _report.Products)
{
productSheet = workBook.Worksheets.Add();
ProceedProductWorksheet(productSheet, itemProduct);
productSheet.Move(null, workBook.Worksheets[workBook.Worksheets.Count]);
}
// write summary sheet
ProceedSummaryWorksheet(_report, workBook, summarySheet, productSheet);
summarySheet.Range("$A2").Select();
// save the book
string fileExtension = XlConverter.GetDefaultExtension(_excelApplication);
string workbookFile = string.Format("{0}\\Example10{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
_excelApplication.Quit();
_excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
private void ProceedSummaryWorksheet(SalesReport report, Excel.Workbook workBook, Excel.Worksheet summarySheet, Excel.Worksheet afterSheet)
{
summarySheet.Name = "Summary";
Excel.Style matrixStyle = CreateSummaryStyle(workBook, "MatrixStyle");
ProceedSummaryMatrix(report, summarySheet, matrixStyle);
ProceedSummaryWorksheetCharts(summarySheet, report.Products.Length + 1);
ProceedSummaryPrintSettings(summarySheet);
summarySheet.Columns.AutoFit();// proceed AutoFit before header
ProceedSummaryWorksheetHeader(summarySheet);
summarySheet.Select();
}
public SalesReportProduct(SalesReport parent)
{
_parent = parent;
}
