private void ProceedSummaryPrintSettings(Excel.Worksheet summarySheet) { summarySheet.PageSetup.Orientation = XlPageOrientation.xlLandscape; summarySheet.PageSetup.Zoom = false; summarySheet.PageSetup.FitToPagesTall = 1; summarySheet.PageSetup.FitToPagesWide = 1; }
private void button1_Click(object sender, EventArgs e) { // start excel and turn off msg boxes _excelApplication = new Excel.Application(); _excelApplication.DisplayAlerts = false; // add a new workbook Excel.Workbook workBook = _excelApplication.Workbooks.Add(); Excel.Worksheet workSheet = workBook.Worksheets[1]; // we need some data to display Excel.Range dataRange = PutSampleData(workSheet); // create a nice diagram Excel.ChartObject chart = workSheet.ChartObjects().Add(70, 100, 375, 225); chart.Chart.SetSourceData(dataRange); // save the book string fileExtension = GetDefaultExtension(_excelApplication); string workbookFile = string.Format("{0}\\Example05{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 static Excel.Range PutSampleData(Excel.Worksheet workSheet) { workSheet.Cells[2, 2].Value = "Datum"; workSheet.Cells[3, 2].Value = DateTime.Now.ToShortDateString(); workSheet.Cells[4, 2].Value = DateTime.Now.ToShortDateString(); workSheet.Cells[5, 2].Value = DateTime.Now.ToShortDateString(); workSheet.Cells[6, 2].Value = DateTime.Now.ToShortDateString(); workSheet.Cells[2, 3].Value = "Column1"; workSheet.Cells[3, 3].Value = 25; workSheet.Cells[4, 3].Value = 33; workSheet.Cells[5, 3].Value = 30; workSheet.Cells[6, 3].Value = 22; workSheet.Cells[2, 4].Value = "Column2"; workSheet.Cells[3, 4].Value = 25; workSheet.Cells[4, 4].Value = 33; workSheet.Cells[5, 4].Value = 30; workSheet.Cells[6, 4].Value = 22; workSheet.Cells[2, 5].Value = "Column3"; workSheet.Cells[3, 5].Value = 25; workSheet.Cells[4, 5].Value = 33; workSheet.Cells[5, 5].Value = 30; workSheet.Cells[6, 5].Value = 22; return(workSheet.get_Range("$B2:$E6")); }
private void ProceedProductMonthCharts(Excel.Worksheet 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; Excel.ChartObject chartMonths = productSheet.ChartObjects().Add(1, chartTop, chartWidth, chartHeight); chartMonths.Chart.SetSourceData(productSheet.get_Range("$A9:$M12")); chartTop = productSheet.Rows[31].Top; chartWidth = productSheet.Columns[14].Left; chartHeight = productSheet.Rows[40].Top - productSheet.Rows[33].Top; Excel.ChartObject chartCountMonths = productSheet.ChartObjects().Add(1, chartTop, chartWidth, chartHeight); chartCountMonths.Chart.ChartType = XlChartType.xlLine; chartCountMonths.Chart.SetSourceData(productSheet.get_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; Excel.ChartObject chartCountYears = productSheet.ChartObjects().Add(chartLeft, chartTop, chartWidth, chartHeight); chartCountYears.Chart.ChartType = XlChartType.xlCylinderColClustered; chartCountYears.Chart.SetSourceData(productSheet.get_Range("$O9:$P12")); }
private void ProceedProductPrintSettings(Excel.Worksheet 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 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); }
private void button1_Click(object sender, EventArgs e) { // start excel and turn off msg boxes _excelApplication = new Excel.Application(); _excelApplication.DisplayAlerts = false; // add a new workbook Excel.Workbook workBook = _excelApplication.Workbooks.Add(); Excel.Worksheet workSheet = workBook.Worksheets[1]; /*do background color for cells*/ string listSeperator = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator; // draw the face string rangeAdressFace = string.Format("$C10:$M10{0}$C30:$M30{0}$C11:$C30{0}$M11:$M30", listSeperator); workSheet.get_Range(rangeAdressFace).Interior.Color = ToDouble(Color.DarkGreen); string rangeAdressEyes = string.Format("$F14{0}$J14", listSeperator); workSheet.get_Range(rangeAdressEyes).Interior.Color = ToDouble(Color.Black); string rangeAdressNoise = string.Format("$G18:$I19", listSeperator); workSheet.get_Range(rangeAdressNoise).Interior.Color = ToDouble(Color.DarkGreen); string rangeAdressMouth = string.Format("$F26{0}$J26{0}$G27:$I27", listSeperator); workSheet.get_Range(rangeAdressMouth).Interior.Color = ToDouble(Color.DarkGreen); // border the face with the border arround method workSheet.get_Range(rangeAdressFace).BorderAround(XlLineStyle.xlDashDot, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexNone, Color.BlueViolet.ToArgb()); workSheet.get_Range(rangeAdressEyes).BorderAround(XlLineStyle.xlDashDot, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexNone, Color.BlueViolet.ToArgb()); workSheet.get_Range(rangeAdressNoise).BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexNone, Color.BlueViolet.ToArgb()); // border explicitly workSheet.get_Range(rangeAdressMouth).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; workSheet.get_Range(rangeAdressMouth).Borders[XlBordersIndex.xlEdgeBottom].Weight = 4; workSheet.get_Range(rangeAdressMouth).Borders[XlBordersIndex.xlEdgeBottom].Color = ToDouble(Color.Black); // save the book string fileExtension = GetDefaultExtension(_excelApplication); string workbookFile = string.Format("{0}\\Example01{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 ProceedProductMonthInfo(Excel.Worksheet productSheet, SalesReportProduct itemProduct) { int rowIndex = 9; int iMonthCellIndex = 1; // we use the native invoker to set the style as string Excel.Range range = productSheet.get_Range("$A9:$M13"); LateBindingApi.Core.Invoker.PropertySet(range, "Style", "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 = Helper.ToCellAdress(itemProduct.PrevMonths.Count + 2, 10); string cellAdress2 = Helper.ToCellAdress(itemProduct.PrevMonths.Count + 2, 12); productSheet.get_Range("$B10:$" + cellAdress1).Interior.Color = Helper.ToDouble(System.Drawing.Color.Gainsboro); productSheet.get_Range("$B12:$" + cellAdress2).Interior.Color = Helper.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.get_Range("$B9:$M9").NumberFormat = ""; productSheet.get_Range("$B9:$M9").Font.Bold = true; productSheet.get_Range("$B13:$M13").NumberFormat = ""; productSheet.get_Range("$B13:$M13").HorizontalAlignment = XlHAlign.xlHAlignCenter; if (itemProduct.PrevMonths.Count < 11) { string topLeftMergeCellAdress = "$" + Helper.ToCellAdress(itemProduct.PrevMonths.Count + 3, 10); productSheet.get_Range(topLeftMergeCellAdress + ":$M13").MergeCells = true; } }
private void button1_Click(object sender, EventArgs e) { // start excel and turn off msg boxes _excelApplication = new Excel.Application(); _excelApplication.DisplayAlerts = false; _excelApplication.ScreenUpdating = false; // add a new workbook Excel.Workbook workBook = _excelApplication.Workbooks.Add(); // we use the first sheet as summary sheet and remove the 2 last sheets Excel.Worksheet 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 Excel.Worksheet productSheet = null; foreach (SalesReportProduct itemProduct in _report.Products) { productSheet = workBook.Worksheets.Add(); ProceedProductWorksheet(productSheet, itemProduct); productSheet.Move(Missing.Value, workBook.Worksheets[workBook.Worksheets.Count]); } // write summary sheet ProceedSummaryWorksheet(_report, workBook, summarySheet, productSheet); summarySheet.get_Range("$A2").Select(); // save the book string fileExtension = Helper.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 ProceedProductWorksheetHeader(Excel.Worksheet 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 ProceedProductWorksheet(Excel.Worksheet 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 ProceedProductYearTotalInfo(Excel.Worksheet productSheet, SalesReportProduct itemProduct) { int ColumnIndex = 15; int RowIndex = 9; LateBindingApi.Core.Invoker.PropertySet(productSheet.get_Range("$O9:$R13"), "Style", "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.get_Range("$O10:$P10").Interior.Color = Helper.ToDouble(System.Drawing.Color.Gainsboro); productSheet.get_Range("$O12:$P12").Interior.Color = Helper.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.get_Range("$R10").Interior.Color = Helper.ToDouble(System.Drawing.Color.Gainsboro); productSheet.get_Range("$R12").Interior.Color = Helper.ToDouble(System.Drawing.Color.Gainsboro); productSheet.get_Range("$O9:$R9").NumberFormat = ""; productSheet.get_Range("$O9:$R9").Font.Bold = true; productSheet.get_Range("$O13:$R13").NumberFormat = ""; productSheet.get_Range("$O13:$R13").HorizontalAlignment = XlHAlign.xlHAlignCenter; productSheet.get_Range("$Q9:$Q13").MergeCells = true; }
private void button1_Click(object sender, EventArgs e) { // start excel and turn off msg boxes _excelApplication = new Excel.Application(); _excelApplication.DisplayAlerts = false; // add a new workbook Excel.Workbook workBook = _excelApplication.Workbooks.Add(); Excel.Worksheet workSheet = workBook.Worksheets[1]; workSheet.Cells[1, 1].Value = "these sample shapes was dynamicly created by code."; // create a star Excel.Shape starShape = workSheet.Shapes.AddShape(MsoAutoShapeType.msoShape32pointStar, 10, 50, 200, 20); // create a simple textbox Excel.Shape textBox = workSheet.Shapes.AddTextbox(MsoTextOrientation.msoTextOrientationHorizontal, 10, 150, 200, 50); textBox.TextFrame.Characters().Text = "text"; textBox.TextFrame.Characters().Font.Size = 14; // create a wordart Excel.Shape textEffect = workSheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect14, "WordArt", "Arial", 12, MsoTriState.msoTrue, MsoTriState.msoFalse, 10, 250); // create text effect Excel.Shape textDiagram = workSheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect11, "Effect", "Arial", 14, MsoTriState.msoFalse, MsoTriState.msoFalse, 10, 350); // save the book string fileExtension = GetDefaultExtension(_excelApplication); string workbookFile = string.Format("{0}\\Example04{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 ProceedProductRankingInfo(Excel.Worksheet productSheet, SalesReportProduct itemProduct) { int rowIndex = 3; int columnIndex = 4; // we use the native invoker to set the style as string Excel.Range range = productSheet.get_Range("$D3:$F6"); LateBindingApi.Core.Invoker.PropertySet(range, "Style", "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.get_Range("$D4:$E6").Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlLineStyleNone; }
private void ProceedSummaryWorksheetCharts(Excel.Worksheet summarySheet, int countOfProducts) { string captionRangeAdress = "$A2:$" + Helper.ToCellAdress(1, 1 + countOfProducts); string fieldRangeAdress = "$C2:$" + Helper.ToCellAdress(3, 1 + countOfProducts); double chartTopPosition = summarySheet.Rows[countOfProducts + 5].Top; double chartWidth = summarySheet.Columns[13].Left; Excel.ChartObject chartSummary = summarySheet.ChartObjects().Add(1, chartTopPosition, chartWidth, 260); chartSummary.Chart.SetSourceData(summarySheet.get_Range(captionRangeAdress + ";" + fieldRangeAdress)); fieldRangeAdress = "$D2:$" + Helper.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; Excel.ChartObject chartPercentOutcome = summarySheet.ChartObjects().Add(chartLeftPosition, chartTopPosition, chartWidth, chartHeight); chartPercentOutcome.Chart.ChartType = XlChartType.xlPie; chartPercentOutcome.Chart.SetSourceData(summarySheet.get_Range(captionRangeAdress + ";" + fieldRangeAdress)); }
private void ProceedProductStorageInfo(Excel.Worksheet productSheet, SalesReportProduct itemProduct) { int rowIndex = 3; int columnIndex = 1; // we use the native invoker to set the style as string Excel.Range range = productSheet.get_Range("$A3:$B6"); LateBindingApi.Core.Invoker.PropertySet(range, "Style", "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 button1_Click(object sender, EventArgs e) { // start excel and turn Application msg boxes _excelApplication = new Excel.Application(); _excelApplication.DisplayAlerts = false; // add a new workbook Excel.Workbook workBook = _excelApplication.Workbooks.Add(); Excel.Worksheet workSheet = workBook.Worksheets[1]; /* some kind of numerics */ // the given thread culture in all latebinding calls are stored in Core.Settings. // you can change the culture. default is en-us. CultureInfo cultureInfo = LateBindingApi.Core.Settings.ThreadCulture; string Pattern1 = string.Format("0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator); string Pattern2 = string.Format("#{1}##0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator, cultureInfo.NumberFormat.CurrencyGroupSeparator); workSheet.get_Range("A1").Value = "Type"; workSheet.get_Range("B1").Value = "Value"; workSheet.get_Range("C1").Value = "Formatted " + Pattern1; workSheet.get_Range("D1").Value = "Formatted " + Pattern2; int integerValue = 532234; workSheet.get_Range("A3").Value = "Integer"; workSheet.get_Range("B3").Value = integerValue; workSheet.get_Range("C3").Value = integerValue; workSheet.get_Range("C3").NumberFormat = Pattern1; workSheet.get_Range("D3").Value = integerValue; workSheet.get_Range("D3").NumberFormat = Pattern2; double doubleValue = 23172.64; workSheet.get_Range("A4").Value = "double"; workSheet.get_Range("B4").Value = doubleValue; workSheet.get_Range("C4").Value = doubleValue; workSheet.get_Range("C4").NumberFormat = Pattern1; workSheet.get_Range("D4").Value = doubleValue; workSheet.get_Range("D4").NumberFormat = Pattern2; float floatValue = 84345.9132f; workSheet.get_Range("A5").Value = "float"; workSheet.get_Range("B5").Value = floatValue; workSheet.get_Range("C5").Value = floatValue; workSheet.get_Range("C5").NumberFormat = Pattern1; workSheet.get_Range("D5").Value = floatValue; workSheet.get_Range("D5").NumberFormat = Pattern2; Decimal decimalValue = 7251231.313367m; workSheet.get_Range("A6").Value = "Decimal"; workSheet.get_Range("B6").Value = decimalValue; workSheet.get_Range("C6").Value = decimalValue; workSheet.get_Range("C6").NumberFormat = Pattern1; workSheet.get_Range("D6").Value = decimalValue; workSheet.get_Range("D6").NumberFormat = Pattern2; workSheet.get_Range("A9").Value = "DateTime"; workSheet.get_Range("B10").Value = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.FullDateTimePattern; workSheet.get_Range("C10").Value = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.LongDatePattern; workSheet.get_Range("D10").Value = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.ShortDatePattern; workSheet.get_Range("E10").Value = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.LongTimePattern; workSheet.get_Range("F10").Value = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.ShortTimePattern; // DateTime DateTime dateTimeValue = DateTime.Now; workSheet.get_Range("B11").Value = dateTimeValue; workSheet.get_Range("B11").NumberFormat = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.FullDateTimePattern; workSheet.get_Range("C11").Value = dateTimeValue; workSheet.get_Range("C11").NumberFormat = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.LongDatePattern; workSheet.get_Range("D11").Value = dateTimeValue; workSheet.get_Range("D11").NumberFormat = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.ShortDatePattern; workSheet.get_Range("E11").Value = dateTimeValue; workSheet.get_Range("E11").NumberFormat = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.LongTimePattern; workSheet.get_Range("F11").Value = dateTimeValue; workSheet.get_Range("F11").NumberFormat = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.ShortTimePattern; // string workSheet.get_Range("A14").Value = "String"; workSheet.get_Range("B14").Value = "This is a sample String"; workSheet.get_Range("B14").NumberFormat = "@"; // number as string workSheet.get_Range("B15").Value = "513"; workSheet.get_Range("B15").NumberFormat = "@"; // set colums workSheet.Columns[1].AutoFit(); workSheet.Columns[2].AutoFit(); workSheet.Columns[3].AutoFit(); workSheet.Columns[4].AutoFit(); // save the book string fileExtension = GetDefaultExtension(_excelApplication); string workbookFile = string.Format("{0}\\Example03{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 button1_Click(object sender, EventArgs e) { // start excel and turn off msg boxes _excelApplication = new Excel.Application(); _excelApplication.DisplayAlerts = false; // dont show dialogs with an invisible excel _excelApplication.Visible = true; // add a new workbook Excel.Workbook workBook = _excelApplication.Workbooks.Add(); Excel.Worksheet workSheet = workBook.Worksheets[1]; // show selected window and display user clicks ok or cancel bool returnValue = false; RadioButton radioSelectButton = GetSelectedRadioButton(); switch (radioSelectButton.Text) { case "xlDialogAddinManager": returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogAddinManager].Show(); break; case "xlDialogFont": returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogFont].Show(); break; case "xlDialogEditColor": returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogEditColor].Show(); break; case "xlDialogGallery3dBar": returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogGallery3dBar].Show(); break; case "xlDialogSearch": returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogSearch].Show(); break; case "xlDialogPrinterSetup": returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogPrinterSetup].Show(); break; case "xlDialogFormatNumber": returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogFormatNumber].Show(); break; case "xlDialogApplyStyle": returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogApplyStyle].Show(); break; default: throw (new Exception("Unkown dialog selected.")); } string message = string.Format("The dialog returns {0}.", returnValue); MessageBox.Show(this, message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information); // close excel and dispose reference _excelApplication.Quit(); _excelApplication.Dispose(); }
private void button1_Click(object sender, EventArgs e) { Excel.Application application = new Excel.Application(); application.DisplayAlerts = false; application.Workbooks.Add(); /* * COMVariant is a LateBindingApi defined Type as substitute for the COM Datatype Variant. * * To work with COMVariant use there following properties: * object UnderlyingObject - the mapped original object * bool IsCOMProxy - UnderlyingObject is a COM Proxy or native type * string TypeName - the name of UnderlyingObject type * * You can cast the COMVariant directly in another LateBindingApi Type, see the following example */ /* Example: */ /* the property Selection from Excel.Application is defined as COMVariant */ /* It can have multiple types at runtime what is currently selected, a worksheet, a range, a window or other*/ // select worksheet for example application.Workbooks[1].Worksheets[1].Select(); COMVariant myVariant = application.Selection; if (null != myVariant) { switch (myVariant.TypeName) { case "Worksheet": Excel.Worksheet sheet = (Excel.Worksheet)myVariant; break; case "Range": Excel.Range range = (Excel.Range)myVariant; break; } } /* Another example: */ /* GetOpenFileName returns a COMVariant there can be a string or a boolean in case of user clicks cancel */ myVariant = application.GetOpenFilename("Text Files (*.txt), *.txt"); if (null != myVariant) { string name = myVariant.TypeName; object underlyingObject = myVariant.UnderlyingObject; string message = string.Format("GetOpenFilename returns a {0}\r\n{1}", name, underlyingObject); MessageBox.Show(this, message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information); } /* Last example: */ /* a lot of scalar properties defined as variant in excel (the reason is still unkown)*/ /* use the multiple overload helper function ToCOMVariant */ /* Note: a other way to handle multiple types in a variable is the Invoker, see Tutorial03 */ Excel.Range sampleRange = application.Workbooks[1].Worksheets[1].Cells[1, 1]; int colorIndex = (int)sampleRange.Font.ColorIndex.UnderlyingObject; sampleRange.Font.ColorIndex = COMVariant.ToCOMVariant(colorIndex + 1); /* * COMObject inherited COMVariant and is a LateBindingApi defined Type as substitute for a unkown Dispatch Interface aka System._ComProxy in .NET. * All LateBindingApi WrapperClasses inherites COMObject * You can cast COMObject directly in another LateBindingApi Type, see the following example * To work with COMObject use the following properties: * object UnderlyingObject - the mapped original System._ComProxy object * string TypeName - the name of UnderlyingObject type */ foreach (Office.COMAddIn item in application.COMAddIns) { /* COMAddIn.Application is defined as COMObject*/ string name = item.Application.TypeName; Excel.Application parentApplication = (Excel.Application)item.Application; } application.Quit(); application.Dispose(); }
private void ProceedSummaryWorksheetHeader(Excel.Worksheet 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 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 button1_Click(object sender, EventArgs e) { Excel.Application application = new LateBindingApi.Excel.Application(); application.DisplayAlerts = false; /* * LateBindingApi.Excel manages COM Proxies for you to avoid any kind of memory leaks * and make sure your excel instance removes from process list if you want. */ Excel.Workbook book = application.Workbooks.Add(); /* * now we have 2 new COM Proxies created. * * the first proxy was created while accessing the Workbooks collection from application * the second proxy was created by the Add() method from Workbooks and stored now in book */ Excel.Worksheet sheet = book.Worksheets.Add(); /* * we create also 2 new COM Proxies. * * the first proxy was created while accessing the Worksheets collection from book * the second proxy was created by the Add() method from Worksheets and stored now in sheet * * the 2 proxies was created about the book object and are managed now by the book object * they are now 'child proxies' collected in a internal list. */ book.Dispose(); /* now we have disposed the Workbook, the inner Proxy will be released */ /* all child proxies there was created about the book are also released now */ /* this means the 2 proxies there we have created while add a new worksheet */ /* the local stored variable Excel.Worksheet sheet are not longer valid */ /* * SUMMARY: * With LateBindingApi.Excel you have the choice to use Dispose() for any object or doesnt. * In case of doesn't dont forget to dispose the application and all child proxies will be released automaticly. * It is necessary to understand the demonstrated child proxy concept of LateBindingApi.Excel to avoid any suprises. * With these concept you have a powerfull third way. Use child proxies without Dispose() to get more readable & smaller code. * Dispose the parent object of your childs and thats all. */ /* another example for efficient using child proxy management */ book = application.Workbooks.Add(); foreach (Excel.Worksheet item in book.Worksheets) { for (int i = 1; i <= 100; i++) { item.Cells[1, i].Value = "Hello World"; } } book.Dispose(); /* * we create a new workbook with default 3 new worksheets * we do a foreach to all worksheets and write 100 times "Hello World" in cells * we have created now 300 range proxies 3 worksheet proxies 1 worksheet enumerator proxy and the book proxy * the parent object is ouer book, diposed it and the child proxies will be also disposed * Note: its not allowed in the MS Interop Assemblies to use an enumerator like these. * you must store it in a local variable first and dispose explicitly after using. * With LateBindingApi.Excel you can do this without any problems. */ application.Quit(); application.Dispose(); /*the application object is ouer root object*/ /*dispose them will release himself and any childs*/ /*the excel instance are now removed from process list*/ MessageBox.Show(this, "Done!", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information); }
private void button1_Click(object sender, EventArgs e) { // start excel and turn off msg boxes _excelApplication = new Excel.Application(); _excelApplication.DisplayAlerts = false; // add a new workbook Excel.Workbook workBook = _excelApplication.Workbooks.Add(); Excel.Worksheet workSheet = workBook.Worksheets[1]; // font action workSheet.get_Range("A1").Value = "Arial Size:8 Bold Italic Underline"; workSheet.get_Range("A1").Font.Name = "Arial"; workSheet.get_Range("A1").Font.Size = 8; workSheet.get_Range("A1").Font.Bold = true; workSheet.get_Range("A1").Font.Italic = true; workSheet.get_Range("A1").Font.Underline = true; workSheet.get_Range("A1").Font.Color = Color.Violet.ToArgb(); workSheet.get_Range("A3").Value = "Times New Roman Size:10"; workSheet.get_Range("A3").Font.Name = "Times New Roman"; workSheet.get_Range("A3").Font.Size = 10; workSheet.get_Range("A3").Font.Color = Color.Orange.ToArgb(); workSheet.get_Range("A5").Value = "Comic Sans MS Size:12 WrapText"; workSheet.get_Range("A5").Font.Name = "Comic Sans MS"; workSheet.get_Range("A5").Font.Size = 12; workSheet.get_Range("A5").WrapText = true; workSheet.get_Range("A5").Font.Color = Color.Navy.ToArgb(); // HorizontalAlignment workSheet.get_Range("A7").Value = "xlHAlignLeft"; workSheet.get_Range("A7").HorizontalAlignment = XlHAlign.xlHAlignLeft; workSheet.get_Range("B7").Value = "xlHAlignCenter"; workSheet.get_Range("B7").HorizontalAlignment = XlHAlign.xlHAlignCenter; workSheet.get_Range("C7").Value = "xlHAlignRight"; workSheet.get_Range("C7").HorizontalAlignment = XlHAlign.xlHAlignRight; workSheet.get_Range("D7").Value = "xlHAlignJustify"; workSheet.get_Range("D7").HorizontalAlignment = XlHAlign.xlHAlignJustify; workSheet.get_Range("E7").Value = "xlHAlignDistributed"; workSheet.get_Range("E7").HorizontalAlignment = XlHAlign.xlHAlignDistributed; // VerticalAlignment workSheet.get_Range("A9").Value = "xlVAlignTop"; workSheet.get_Range("A9").VerticalAlignment = XlVAlign.xlVAlignTop; workSheet.get_Range("B9").Value = "xlVAlignCenter"; workSheet.get_Range("B9").VerticalAlignment = XlVAlign.xlVAlignCenter; workSheet.get_Range("C9").Value = "xlVAlignBottom"; workSheet.get_Range("C9").VerticalAlignment = XlVAlign.xlVAlignBottom; workSheet.get_Range("D9").Value = "xlVAlignDistributed"; workSheet.get_Range("D9").VerticalAlignment = XlVAlign.xlVAlignDistributed; workSheet.get_Range("E9").Value = "xlVAlignJustify"; workSheet.get_Range("E9").VerticalAlignment = XlVAlign.xlVAlignJustify; // setup rows and columns workSheet.Columns[1].AutoFit(); workSheet.Columns[2].ColumnWidth = 25; workSheet.Columns[3].ColumnWidth = 25; workSheet.Columns[4].ColumnWidth = 25; workSheet.Columns[5].ColumnWidth = 25; workSheet.Rows[9].RowHeight = 35; // save the book string fileExtension = GetDefaultExtension(_excelApplication); string workbookFile = string.Format("{0}\\Example02{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); }