private Excel.Style CreateSummaryStyle(Excel.Workbook workBook, string styleName)
{
/*
* borders in styles doesnt realy working, very simple using is possible with the index trick. thats all
*/
Excel.Style newStyle = workBook.Styles.Add(styleName);
newStyle.Font.Size = 12;
newStyle.Font.Name = "Courier New";
newStyle.Borders[(XlBordersIndex)Constants.xlTop].LineStyle = XlLineStyle.xlContinuous;
newStyle.Borders[(XlBordersIndex)Constants.xlTop].Color = 0;
newStyle.Borders[(XlBordersIndex)Constants.xlTop].Weight = 2;
newStyle.Borders[(XlBordersIndex)Constants.xlBottom].LineStyle = XlLineStyle.xlContinuous;
newStyle.Borders[(XlBordersIndex)Constants.xlBottom].Color = 0;
newStyle.Borders[(XlBordersIndex)Constants.xlBottom].Weight = 2;
newStyle.Borders[(XlBordersIndex)Constants.xlLeft].LineStyle = XlLineStyle.xlDouble;
newStyle.Borders[(XlBordersIndex)Constants.xlLeft].Color = 0;
newStyle.Borders[(XlBordersIndex)Constants.xlLeft].Weight = 2;
newStyle.Borders[(XlBordersIndex)Constants.xlRight].LineStyle = XlLineStyle.xlLineStyleNone;
newStyle.Borders[(XlBordersIndex)Constants.xlRight].Color = 0;
newStyle.Borders[(XlBordersIndex)Constants.xlRight].Weight = 2;
return(newStyle);
}
private Excel.Style CreateYearTotalStyle(Excel.Workbook workBook, string styleName)
{
/*
* borders in styles doesnt realy working, very simple using is possible with the index trick. thats all
*/
Excel.Style newStyle = workBook.Styles.Add(styleName);
newStyle.Font.Size = 12;
newStyle.Borders[(XlBordersIndex)Constants.xlTop].LineStyle = XlLineStyle.xlContinuous;
newStyle.Borders[(XlBordersIndex)Constants.xlTop].Color = 0;
newStyle.Borders[(XlBordersIndex)Constants.xlTop].Weight = 2;
newStyle.Borders[(XlBordersIndex)Constants.xlBottom].LineStyle = LateBindingApi.Excel.Enums.XlLineStyle.xlContinuous;
newStyle.Borders[(XlBordersIndex)Constants.xlBottom].Color = 0;
newStyle.Borders[(XlBordersIndex)Constants.xlBottom].Weight = 2;
newStyle.Borders[(XlBordersIndex)Constants.xlLeft].LineStyle = LateBindingApi.Excel.Enums.XlLineStyle.xlDouble;
newStyle.Borders[(XlBordersIndex)Constants.xlLeft].Color = 0;
newStyle.Borders[(XlBordersIndex)Constants.xlLeft].Weight = 2;
newStyle.Borders[(XlBordersIndex)Constants.xlRight].LineStyle = LateBindingApi.Excel.Enums.XlLineStyle.xlLineStyleNone;
newStyle.Borders[(XlBordersIndex)Constants.xlRight].Color = 0;
newStyle.Borders[(XlBordersIndex)Constants.xlRight].Weight = 2;
newStyle.NumberFormat = "#,##0.00 €";
return(newStyle);
}
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();
}
private void ProceedSummaryMatrix(SalesReport report, Excel.Worksheet summarySheet, Excel.Style matrixStyle)
{
// table columns
summarySheet.get_Range("B2").Value = "Count";
summarySheet.get_Range("C2").Value = "Revenue";
summarySheet.get_Range("D2").Value = "%";
summarySheet.get_Range("E2").Value = "Storage";
string leftBottomCellAdress = Helper.ToCellAdress(1, 3 + report.Products.Length);
string rightBottomCellAdress = Helper.ToCellAdress(5, 3 + report.Products.Length);
summarySheet.get_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, Helper.ToCellAdress(_monthToReport + 1, 13));
summarySheet.Cells[rowIndex, columnIndex + 1].Value = formula;
formula = string.Format("='{0}-{1}'!{2}", itemProduct.ProductName, itemProduct.ProductId, Helper.ToCellAdress(_monthToReport + 1, 12));
summarySheet.Cells[rowIndex, columnIndex + 2].Value = formula;
formula = string.Format("={0}*100/{1}", Helper.ToCellAdress(3, rowIndex), Helper.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.get_Range("$A" + (i + 3).ToString() + ":$E" + (i + 3).ToString()).Interior.Color = Helper.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.get_Range("$C3:$C" + (report.Products.Length + 3).ToString()).NumberFormat = "#,##0.00 €";
summarySheet.get_Range("$D3:$D" + (report.Products.Length + 3).ToString()).NumberFormat = "0\"%\"";
summarySheet.Cells[3 + report.Products.Length, 1].Value = "Total:";
summarySheet.get_Range("D2").HorizontalAlignment = XlHAlign.xlHAlignCenter;
summarySheet.get_Range("$B2:$E2").Font.Bold = true;
summarySheet.get_Range(leftBottomCellAdress + ":" + rightBottomCellAdress).Font.Bold = true;
summarySheet.get_Range(leftBottomCellAdress + ":" + rightBottomCellAdress).BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlMedium);
}