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 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 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 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 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 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 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 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 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]; // 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); }