private XlStyle CreateYearTotalStyle(XlWorkbook workBook, string styleName) { /* * borders in styles doesnt realy working, very simple using is possible with the index trick. thats all */ XlStyle 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, XlWorkbook workBook, XlWorksheet summarySheet, XlWorksheet afterSheet) { summarySheet.Name = "Summary"; XlStyle 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, 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); }