/XlLateBinding/ReleaseTags/0.7/Examples/Example10/Form1.cs
C# | 555 lines | 400 code | 128 blank | 27 comment | 7 complexity | b5c6baccb55aa2fae058ef22c403f936 MD5 | raw file
- using System;
- using System.Xml;
- using System.Data;
- using System.Windows.Forms;
- using System.Reflection;
- using System.Globalization;
-
- using LateBindingApi.Excel;
- using LateBindingApi.Excel.Office;
- using LateBindingApi.Excel.Charts;
- using LateBindingApi.Excel.Styles;
- using LateBindingApi.Excel.Enums;
-
- namespace Example10
- {
- public partial class Form1 : Form
- {
- /// <summary>
- /// month and year to report, no selection dialog in this example
- /// the sample data contains datasets from 01/2008 - 06/2010
- /// </summary>
- private readonly int _yearToReport = 2009;
- private readonly int _monthToReport = 5;
-
- #region Fields
-
- XlApplication _excelApplication;
- SalesReport _report;
-
- #endregion
-
- #region Construction
-
- public Form1()
- {
- InitializeComponent();
- }
-
- #endregion
-
- #region Gui Trigger
-
- 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);
- }
-
- #endregion
-
- #region Write Summary to Worksheet Functions
-
- 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 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 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));
-
- }
-
- private void ProceedSummaryWorksheetHeader(XlWorksheet summarySheet)
- {
- summarySheet.PageSetup.LeftHeader = "&D created";
- summarySheet.PageSetup.CenterHeader = "Vintage Digital Inc.";
- summarySheet.PageSetup.RightHeader = string.Format("Monthly Sales Report {1:00}/{0}", _yearToReport, _monthToReport);
- }
-
- private void ProceedSummaryPrintSettings(XlWorksheet summarySheet)
- {
- summarySheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
- summarySheet.PageSetup.Zoom = false;
- summarySheet.PageSetup.FitToPagesTall = 1;
- summarySheet.PageSetup.FitToPagesWide = 1;
- }
-
- private XlStyle CreateSummaryStyle(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.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 = 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;
-
- return newStyle;
-
- }
-
- #endregion
-
- #region Write Product to Worksheet Functions
-
- private void ProceedProductWorksheet(XlWorksheet productSheet, SalesReportProduct itemProduct)
- {
- string sheetName = string.Format("{0}-{1}", itemProduct.ProductName, itemProduct.ProductId.ToString());
- productSheet.Name = sheetName;
-
- // its not a random chain, write data first and create charts second
- ProceedProductStorageInfo(productSheet, itemProduct);
- ProceedProductMonthInfo(productSheet, itemProduct);
- ProceedProductYearTotalInfo(productSheet, itemProduct);
- ProceedProductMonthCharts(productSheet, itemProduct);
- ProceedProductPrintSettings(productSheet);
- productSheet.Columns.AutoFit(); // proceed AutoFit before header & ranking
- ProceedProductWorksheetHeader(productSheet, itemProduct);
- ProceedProductRankingInfo(productSheet, itemProduct);
-
- productSheet.Columns[14].ColumnWidth = 2.14;
- productSheet.Columns[17].ColumnWidth = 5.14;
- }
-
- private void ProceedProductStorageInfo(XlWorksheet productSheet, SalesReportProduct itemProduct)
- {
- int rowIndex = 3;
- int columnIndex = 1;
-
- productSheet.Range("$A3:$B6").StyleAsString = "StorageAndRanking";
-
- productSheet.Cells(rowIndex, columnIndex).Value = "Storage Info";
- productSheet.Cells(rowIndex, columnIndex).Font.Bold = true;
- productSheet.Cells(rowIndex + 1, columnIndex).Value = "Storage Count";
- productSheet.Cells(rowIndex + 2, columnIndex).Value = "Sales in Progress";
- productSheet.Cells(rowIndex + 3, columnIndex).Value = "Recalc Storage Count ";
-
- productSheet.Cells(rowIndex + 1, columnIndex + 1).Value = itemProduct.StorageCount;
- productSheet.Cells(rowIndex + 2, columnIndex + 1).Value = itemProduct.OpenOrders.CountOfSales;
- productSheet.Cells(rowIndex + 3, columnIndex + 1).Value = itemProduct.StorageCount - itemProduct.OpenOrders.CountOfSales;
-
- SetProductStorageCountColor(itemProduct.StorageCount, productSheet.Cells(rowIndex + 1, columnIndex + 1));
- SetProductStorageCountColor(itemProduct.StorageCount - itemProduct.OpenOrders.CountOfSales, productSheet.Cells(rowIndex + 3, columnIndex + 1));
- }
-
- private void ProceedProductRankingInfo(XlWorksheet productSheet, SalesReportProduct itemProduct)
- {
- int rowIndex = 3;
- int columnIndex = 4;
-
- productSheet.Range("$D3:$F6").StyleAsString = "StorageAndRanking";
-
- productSheet.Cells(rowIndex, columnIndex).Value = "Count Ranking";
- productSheet.Cells(rowIndex, columnIndex).Font.Bold = true;
- productSheet.Cells(rowIndex + 1, columnIndex).Value = "Month";
- productSheet.Cells(rowIndex + 2, columnIndex).Value = "Year";
- productSheet.Cells(rowIndex + 3, columnIndex).Value = "Total";
-
- productSheet.Cells(rowIndex + 1, columnIndex + 2).Value = itemProduct.SalesRankMonth;
- productSheet.Cells(rowIndex + 2, columnIndex + 2).Value = itemProduct.SalesRankYear;
- productSheet.Cells(rowIndex + 3, columnIndex + 2).Value = itemProduct.SalesRankTotal;
-
- productSheet.Range("$D4:$E6").Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlLineStyleNone;
- }
-
- 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 ProceedProductPrintSettings(XlWorksheet productSheet)
- {
- productSheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
- productSheet.PageSetup.Zoom = false;
- productSheet.PageSetup.FitToPagesTall = 1;
- productSheet.PageSetup.FitToPagesWide = 1;
- productSheet.PageSetup.PrintArea = "$A$1:$R$39";
- }
-
- private void ProceedProductMonthCharts(XlWorksheet productSheet, SalesReportProduct itemProduct)
- {
-
- double chartTop = productSheet.Rows[15].Top;
- double chartWidth = productSheet.Columns[14].Left;
- double chartHeight = productSheet.Rows[30].Top -productSheet.Rows[15].Top;
-
- XlChartObject chartMonths = productSheet.ChartObjects.Add(1, chartTop, chartWidth, chartHeight);
- chartMonths.Chart.SetSourceData(productSheet.Range("$A9:$M12"));
-
- chartTop = productSheet.Rows[31].Top;
- chartWidth = productSheet.Columns[14].Left;
- chartHeight = productSheet.Rows[40].Top - productSheet.Rows[33].Top;
- XlChartObject chartCountMonths = productSheet.ChartObjects.Add(1, chartTop, chartWidth, chartHeight);
- chartCountMonths.Chart.ChartType = XlChartType.xlLine;
- chartCountMonths.Chart.SetSourceData(productSheet.Range("$A13:$M13"));
-
- double chartLeft = productSheet.Columns[15].Left;
- chartTop = productSheet.Rows[15].Top;
- chartWidth = productSheet.Columns[19].Left - productSheet.Columns[15].Left;
- chartHeight = productSheet.Rows[30].Top - productSheet.Rows[15].Top;
- XlChartObject chartCountYears = productSheet.ChartObjects.Add(chartLeft, chartTop, chartWidth, chartHeight);
- chartCountYears.Chart.ChartType = XlChartType.xlCylinderColClustered;
- chartCountYears.Chart.SetSourceData(productSheet.Range("$O9:$P12"));
-
- }
-
- private void ProceedProductWorksheetHeader(XlWorksheet productSheet, SalesReportProduct itemProduct)
- {
- int rowIndex = 1;
- int columnIndex = 1;
-
- productSheet.PageSetup.LeftHeader = "&D created";
- productSheet.PageSetup.CenterHeader = "Vintage Digital Inc.";
- productSheet.PageSetup.RightHeader = string.Format("Monthly Sales Report {1:00}/{0}", _yearToReport, _monthToReport);
-
- productSheet.Cells(rowIndex, columnIndex).Value = itemProduct.ProductName;
- productSheet.Cells(rowIndex, columnIndex).Font.Bold = true;
- productSheet.Cells(rowIndex, columnIndex).Font.Underline = true;
- productSheet.Cells(rowIndex, columnIndex).Font.Size = 14;
- productSheet.Cells(rowIndex, columnIndex).Font.Name = "Verdana";
-
- }
-
- private void ProceedProductYearTotalInfo(XlWorksheet productSheet, SalesReportProduct itemProduct)
- {
- int ColumnIndex = 15;
- int RowIndex = 9;
-
- productSheet.Range("$O9:$R13").StyleAsString = "YearTotalInfos";
-
- productSheet.Cells(RowIndex, ColumnIndex).Value = "Year " + _yearToReport.ToString();
- productSheet.Cells(RowIndex + 1, ColumnIndex).Value = itemProduct.Year.ManufactorPriceSummary;
- productSheet.Cells(RowIndex + 2, ColumnIndex).Value = itemProduct.Year.SalesPricesSummary;
- productSheet.Cells(RowIndex + 3, ColumnIndex).Value = itemProduct.Year.OutcomeSummary;
- productSheet.Cells(RowIndex + 4, ColumnIndex).Value = itemProduct.Year.CountOfSales;
-
- productSheet.Cells(RowIndex, ColumnIndex + 1).Value = "Year " + (_yearToReport - 1).ToString();
- productSheet.Cells(RowIndex + 1, ColumnIndex + 1).Value = itemProduct.PrevYear.ManufactorPriceSummary;
- productSheet.Cells(RowIndex + 2, ColumnIndex + 1).Value = itemProduct.PrevYear.SalesPricesSummary;
- productSheet.Cells(RowIndex + 3, ColumnIndex + 1).Value = itemProduct.PrevYear.OutcomeSummary;
- productSheet.Cells(RowIndex + 4, ColumnIndex + 1).Value = itemProduct.PrevYear.CountOfSales;
-
- productSheet.Range("$O10:$P10").Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
- productSheet.Range("$O12:$P12").Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
-
- ColumnIndex = 18;
- RowIndex = 9;
-
- productSheet.Cells(RowIndex, ColumnIndex).Value = "Total";
- productSheet.Cells(RowIndex + 1, ColumnIndex).Value = itemProduct.Total.ManufactorPriceSummary;
- productSheet.Cells(RowIndex + 2, ColumnIndex).Value = itemProduct.Total.SalesPricesSummary;
- productSheet.Cells(RowIndex + 3, ColumnIndex).Value = itemProduct.Total.OutcomeSummary;
- productSheet.Cells(RowIndex + 4, ColumnIndex).Value = itemProduct.Total.CountOfSales;
-
- productSheet.Range("$R10").Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
- productSheet.Range("$R12").Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
-
- productSheet.Range("$O9:$R9").NumberFormat = "";
- productSheet.Range("$O9:$R9").Font.Bold = true;
-
- productSheet.Range("$O13:$R13").NumberFormat = "";
- productSheet.Range("$O13:$R13").HorizontalAlignment = XlHAlign.xlHAlignCenter;
-
- productSheet.Range("$Q9:$Q13").MergeCells = true;
-
- }
-
- private XlStyle CreateStorageAndRankingStyle(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.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 = 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;
-
- return newStyle;
-
- }
-
- private XlStyle CreateMonthStyle(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 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 string GetMonthName(int index)
- {
- return LateBindingApi.Excel.XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.MonthNames[index].Substring(0, 3);
- }
-
- private void SetProductStorageCountColor(int storageCount, XlRange range)
- {
- if (storageCount <= 50)
- range.Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Red);
- else if (storageCount <= 100)
- range.Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Yellow);
- else
- range.Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Green);
- }
-
- #endregion
- }
- }