private static void ReportExcelToImage(String filePath, String[] columnList, String title, ChartType chartType = ChartType.COLUMN) { FileInfo file = new FileInfo(filePath); String directory = ""; if (Constant.ANALYZE_CHART_DIR.Equals("")) { directory = file.DirectoryName; } else { directory = Constant.ANALYZE_CHART_DIR; } String fileName = file.Name.Substring(0, file.Name.IndexOf('.')); String imagePath = directory + @"\" + fileName + "-" + title + ".jpg"; Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Open(filePath); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 1000, 850); Excel.Chart chartPage = myChart.Chart; //non-empty count of csv file int count = Convert.ToInt32(xlApp.WorksheetFunction.CountA(xlWorkSheet.get_Range("A:A"))); //non-empty count of header int headerCount = Convert.ToInt32(xlApp.WorksheetFunction.CountA(xlWorkSheet.Rows[1])); string chartString = "A1:A" + count; foreach (var column in columnList) { int columnIndex = GetColumnIndex(xlWorkSheet, headerCount, column); chartString += "," + GetString(columnIndex) + "1:" + GetString(columnIndex) + count; } chartRange = xlWorkSheet.get_Range(chartString); chartPage.SetSourceData(chartRange, misValue); //chartPage.ChartType = Excel.XlChartType.xlColumnClustered; chartPage.ChartType = GetChartType(chartType); chartPage.Axes(Excel.XlAxisType.xlValue); //export chart as picture file chartPage.Export(imagePath, "JPG", misValue); xlWorkBook.Close(false, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); }
/// <summary> /// re modify the chart with numbers of row changed /// </summary> /// <param name="numRows"></param> /// <returns></returns> public bool ModifyChart(int numRows) { try { object misValue = System.Reflection.Missing.Value; // assign fomular for each sheet for (int i = 2; i <= xlWorkBook.Worksheets.Count; i++)// sheet { xlWorkSheet = xlWorkBook.Worksheets[i]; // delete old chart ChartObjects objs = xlWorkSheet.ChartObjects(); int chartCount = objs.Count; for (int j = 2; j <= chartCount; j++) { ChartObject currChartObj = objs.Item(j - 1); Microsoft.Office.Interop.Excel.Range chartRange; Microsoft.Office.Interop.Excel.Chart chartPage = currChartObj.Chart; chartRange = xlWorkSheet.Range[String.Format("A1:A{0},{1}1:{1}{0}", numRows + "", (char)((int)'A' + j - 1))]; chartPage.SetSourceData(chartRange, misValue); } } return(true); } catch (Exception ex) { MessageBox.Show(ex.Message); return(false); } }
public static void CreateChart( ref Excel.Worksheet page, ExcelChartInfo excelCI) { try { var xlCharts = page.ChartObjects() as Excel.ChartObjects; Excel.ChartObject myChart = xlCharts.Add(excelCI.m_Left, excelCI.m_Top, excelCI.m_Width, excelCI.m_Height); Excel.Chart chartPage = myChart.Chart; var chartRange = page.get_Range(excelCI.m_NameLRange, excelCI.m_NameHRange); var chartRange2 = page.get_Range(excelCI.m_SourceLRange, excelCI.m_SourceHRange); chartPage.SetSourceData(chartRange); chartPage.ChartType = excelCI.m_ChartType; //xlLine || xlColumnClustered chartPage.ChartWizard( Source: chartRange2, Title: excelCI.m_Title, CategoryTitle: excelCI.m_CategoryTitle, ValueTitle: excelCI.m_ValueTitle); } catch (System.Runtime.InteropServices.COMException ex) { MessageBox.Show(ex.Message); } }
private void AddBugChart( ExcelInterop.Worksheet sheet, int chartStartRow, string chartStartCol, int chartEndRow, string chartEndCol, string bugDataSource, string chartTitle) { ExcelInterop.Range bugChartRange = sheet.Range[sheet.Cells[chartStartRow, chartStartCol], sheet.Cells[chartEndRow, chartEndCol]]; ExcelInterop.ChartObjects charts = sheet.ChartObjects(Type.Missing) as ExcelInterop.ChartObjects; Utility.AddNativieResource(charts); ExcelInterop.ChartObject bugChartObject = charts.Add(0, 0, bugChartRange.Width, bugChartRange.Height); Utility.AddNativieResource(bugChartObject); ExcelInterop.Chart bugChart = bugChartObject.Chart;//设置图表数据区域。 Utility.AddNativieResource(bugChart); ExcelInterop.Range datasource = sheet.get_Range(bugDataSource);//不是:"B14:B25","F14:F25" Utility.AddNativieResource(datasource); bugChart.SetSourceData(datasource); bugChart.ChartType = ExcelInterop.XlChartType.xlColumnClustered; //bugChart.ChartWizard(datasource, XlChartType.xlColumnClustered, Type.Missing, XlRowCol.xlColumns, 1, 1, false, chartTitle, "", "", Type.Missing); bugChart.ApplyDataLabels();//图形上面显示具体的值 //将图表移到数据区域之下。 bugChartObject.Left = Convert.ToDouble(bugChartRange.Left) + 20; bugChartObject.Top = Convert.ToDouble(bugChartRange.Top) + 20; bugChartObject.Locked = false; bugChartObject.Select(); bugChartObject.Activate(); }
protected void CreateGraph(int columnIndex) { // Get the alphabetical column index... string columnAlphabetical = ((char)(columnIndex + 64)).ToString( ); // Activate the test Chart worksheet... this.testChartWorkSheet.Activate( ); // Get any charts currently existing in the worksheet... Excel.ChartObjects charts = (Excel.ChartObjects) this.testChartWorkSheet.ChartObjects(Type.Missing); // Adds a chart at x = 100, y = 300, 500 points wide and 300 tall. Excel.ChartObject chartObj = (Excel.ChartObject)charts.Item(columnIndex - 1); Excel.Chart chart = chartObj.Chart; // Gets the data cells ( X values ) Excel.Range chartRange = this.testWorkSheet.get_Range((columnAlphabetical + "2"), (columnAlphabetical + (rows).ToString( ))); chart.SetSourceData(chartRange, Type.Missing); // Set the type of the chart... chart.ChartType = Excel.XlChartType.xlLine; // Get any existing series collection (there should be only one)... Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(Type.Missing); Excel.Series series = seriesCollection.Item(seriesCollection.Count); // Gets the data cells ( Y values ) series.XValues = this.testWorkSheet.get_Range("A2", "A" + (rows).ToString( )); }
private void buttonGrafik_Click(object sender, EventArgs e) { excelOrnek = new EXCEL.Application(); excelKitap = excelOrnek.Workbooks.Add(kayipDeger); excelSayfa = (EXCEL.Worksheet)excelKitap.Worksheets.get_Item(1); excelSayfa.Cells[1, 1] = ""; excelSayfa.Cells[1, 2] = "İstanbul"; excelSayfa.Cells[1, 3] = "Ankara"; excelSayfa.Cells[2, 1] = "Ocak"; excelSayfa.Cells[2, 2] = "350"; excelSayfa.Cells[2, 3] = "500"; excelSayfa.Cells[3, 1] = "Şubat"; excelSayfa.Cells[3, 2] = "300"; excelSayfa.Cells[3, 3] = "450"; excelSayfa.Cells[4, 1] = "Mart"; excelSayfa.Cells[4, 2] = "400"; excelSayfa.Cells[4, 3] = "600"; EXCEL.ChartObjects excelGrafikler = (EXCEL.ChartObjects)excelSayfa.ChartObjects(Type.Missing); EXCEL.ChartObject excelGrafik = (EXCEL.ChartObject)excelGrafikler.Add(10, 80, 300, 250); EXCEL.Chart grafikSayfa = excelGrafik.Chart; EXCEL.Range grafikAlan = excelSayfa.get_Range("A1", "C4"); grafikSayfa.SetSourceData(grafikAlan, kayipDeger); grafikSayfa.ChartType = EXCEL.XlChartType.xlColumnClustered; excelKitap.SaveAs("C:\\Users\\ufukcelik\\OneDrive\\ornekGrafik.xlsx", EXCEL.XlFileFormat.xlWorkbookNormal, kayipDeger, kayipDeger, kayipDeger, kayipDeger, EXCEL.XlSaveAsAccessMode.xlExclusive, kayipDeger, kayipDeger, kayipDeger, kayipDeger, kayipDeger); excelKitap.Close(true, kayipDeger, kayipDeger); excelOrnek.Quit(); richTextBoxEXCEL.Text = "Excel Grafik Dosyası oluşturuldu"; System.Runtime.InteropServices.Marshal.ReleaseComObject(excelOrnek); excelOrnek = null; GC.Collect(); }
private void button3_Click(object sender, EventArgs e) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //add data //xlWorkSheet.Cells[1, 1] = ""; //xlWorkSheet.Cells[1, 2] = "Student1"; //xlWorkSheet.Cells[1, 3] = "Student2"; //xlWorkSheet.Cells[1, 4] = "Student3"; //xlWorkSheet.Cells[2, 1] = "Term1"; //xlWorkSheet.Cells[2, 2] = "80"; //xlWorkSheet.Cells[2, 3] = "65"; //xlWorkSheet.Cells[2, 4] = "45"; //xlWorkSheet.Cells[3, 1] = "Term2"; //xlWorkSheet.Cells[3, 2] = "78"; //xlWorkSheet.Cells[3, 3] = "72"; //xlWorkSheet.Cells[3, 4] = "60"; //xlWorkSheet.Cells[4, 1] = "Term3"; //xlWorkSheet.Cells[4, 2] = "82"; //xlWorkSheet.Cells[4, 3] = "80"; //xlWorkSheet.Cells[4, 4] = "65"; //xlWorkSheet.Cells[5, 1] = "Term4"; //xlWorkSheet.Cells[5, 2] = "75"; //xlWorkSheet.Cells[5, 3] = "82"; //xlWorkSheet.Cells[5, 4] = "68"; Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250); Excel.Chart chartPage = myChart.Chart; chartRange = xlWorkSheet.get_Range("A1", "d5"); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xl3DArea; xlWorkBook.SaveAs("\\\\dsview.pcoip.ki.agh.edu.pl\\Biblioteki-Pracownicy$\\szsz\\Desktop\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls"); }
private void ReportExcel2() { Excel.Application app = new Excel.Application(); app.Visible = true; Excel.Workbook wb = app.Workbooks.Add(); Excel.Worksheet ws = wb.Worksheets[1]; ws.Columns[1].ColumnWidth = 40; ws.Columns[2].ColumnWidth = 20; ws.Columns["A:B"].WrapText = true; Excel.Range rng = ws.Range["A1:B1"]; ws.Cells[1, 1].value = "ОТЧЕТ О ПРОДАЖАХ ЗА " + dateTimePicker1.Value.ToShortDateString() + " - " + dateTimePicker2.Value.ToShortDateString(); rng.Font.Bold = true; rng.Font.Size = 14; rng.MergeCells = true; ws.Range["A1:B2"].HorizontalAlignment = Excel.Constants.xlCenter; ws.Cells[2, 1].value = "Поставщик"; ws.Cells[2, 2].value = "Сумма продаж"; int i = 3; report2TableAdapter1.Fill(kondirDataSet1.Report2, dateTimePicker1.Value, dateTimePicker2.Value); foreach (KondirDataSet.Report2Row r in kondirDataSet1.Report2) { ws.Cells[i, 1].value = r.SupplierName; ws.Cells[i, 2].value = r.Total; i++; } ws.Cells[i, 1].value = "ИТОГО"; ws.Cells[i, 2].FormulaR1C1 = "=SUM(R[-" + (i - 3) + "]C:R[-1]C)"; rng = ws.Range[ws.Cells[2, 1], ws.Cells[i, 2]]; rng.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; rng.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; rng.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; rng.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; rng.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; rng.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; Excel.Shape sh = ws.Shapes.AddChart(Excel.XlChartType.xlPie); sh.Left = 0; sh.Top = (float)ws.Rows[i + 2].Top; float scale = (float)ws.Columns[3].Left / sh.Width; sh.ScaleWidth(scale, MsoTriState.msoFalse, MsoScaleFrom.msoScaleFromTopLeft); Excel.Chart ch = sh.Chart; ch.SetSourceData(ws.Range[ws.Cells[2, 1], ws.Cells[i - 1, 2]]); ch.SeriesCollection(1).ApplyDataLabels(); }
private void CreateChart(Excel.Worksheet worksheet) { Excel.Range usedRange = worksheet.UsedRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = xlCharts.Add(300, 80, 600, 350); Excel.Chart chartPage = myChart.Chart; chartPage.ChartType = Excel.XlChartType.xlXYScatterLines; chartPage.SetSourceData(usedRange, System.Reflection.Missing.Value); }
//Создаем лист книги static void DisplayInExcel(IEnumerable <Account> accounts) { //создаем объект приложения и делаем его видимым var excelApp = new Excel.Application(); excelApp.Visible = true; //создаем новую рабочую книгу - она автоматически становится активной excelApp.Workbooks.Add(); //этот пример использует только одну таблицу Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet; //вставляем значения в первые два столбца первой строки (заголовок) workSheet.Cells[1, "A"] = "ID Number"; workSheet.Cells[1, "B"] = "Current Balance"; //помещаем данные о счетах в таблицу var row = 1; foreach (var acct in accounts) { row++; workSheet.Cells[row, "A"] = acct.ID; workSheet.Cells[row, "B"] = acct.Balance; } //Вычисляем сумму по всем счетам Excel.Range rng = workSheet.Range["B4"]; // указываем ячейку где будет прописанна сумма rng.Formula = "=СУММ(B2:B3)"; rng.FormulaHidden = false; //форматирование таблицы - выделяем границы у ячейки суммы Excel.Borders border = rng.Borders; border.LineStyle = Excel.XlLineStyle.xlContinuous; //чтобы ширина столбца изменилась в соответствии с содержимым //workSheet.Columns.AutoFit(); ((Excel.Range)workSheet.Columns[1]).AutoFit(); ((Excel.Range)workSheet.Columns[2]).AutoFit(); //строим круговую диаграмму Excel.ChartObjects chartObjs = (Excel.ChartObjects)workSheet.ChartObjects(); Excel.ChartObject chartObj = chartObjs.Add(50, 100, 300, 300); Excel.Chart xlChart = chartObj.Chart; Excel.Range rng2 = workSheet.Range["B1:B3"]; //Устанавливаем тип диаграммы xlChart.ChartType = Excel.XlChartType.xlColumnClustered; //устанавливаем источник даных xlChart.SetSourceData(rng2); }
private Excel.Chart MakeColumnChart(Excel.Worksheet xlWorkSheet) { Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = xlCharts.Add(10, 80, 300, 250); Excel.Chart chartPage = myChart.Chart; var chartRange = xlWorkSheet.Range["A1", "d5"]; chartPage.SetSourceData(chartRange, _misValue); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; return(chartPage); }
private void GenerateChart() { Excel.Worksheet activeSheet = null; Excel.Range selectedRange = null; Excel.Shapes shapes = null; Excel.Chart chart = null; Excel.ChartTitle chartTitle = null; try { activeSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet; Excel.Range rangePivot; rangePivot = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "AA2000"); // selectedRange = (Excel.Range)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Selection; selectedRange = IdentifyPivotRanges(rangePivot); shapes = activeSheet.Shapes; shapes.AddChart2(Style: 201, XlChartType: Excel.XlChartType.xlColumnClustered, Left: 480, Top: 190, Width: 450, Height: Type.Missing, NewLayout: true).Select(); chart = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveChart; chart.HasTitle = false; // chart.ChartTitle.Text = "Threats History"; chart.ChartArea.Interior.Color = System.Drawing.Color.FromArgb(242, 244, 244); // Change chart to light gray // chart.ChartArea.Interior.Color = System.Drawing.Color.FromRgb(0, 255, 0); chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, true, true, true, true, true, true); // Turn on data labels chart.HasLegend = true; chart.SetSourceData(selectedRange); (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select(); } finally { if (chartTitle != null) { Marshal.ReleaseComObject(chartTitle); } if (chart != null) { Marshal.ReleaseComObject(chart); } if (shapes != null) { Marshal.ReleaseComObject(shapes); } if (selectedRange != null) { Marshal.ReleaseComObject(selectedRange); } } }
public void Saveto_Excel_Click() { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; // xlApp.Visible = true; //xlApp.UserControl = true; System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US"); xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //add data xlWorkSheet.Cells[2, 1] = user.LastName; xlWorkSheet.Cells[3, 1] = "Total Points"; for (int i = 1; i <= Name_Stage.Count; i++) { xlWorkSheet.Cells[1, i + 1] = Name_Stage[i - 1]; } for (int j = 1; j <= Student_Stage_Count.Count; j++) { xlWorkSheet.Cells[2, j + 1] = Student_Stage_Count[j - 1].ToString(); } for (int j = 1; j <= Total_Stage_Count.Count; j++) { xlWorkSheet.Cells[3, j + 1] = Total_Stage_Count[j - 1].ToString(); } Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250); Excel.Chart chartPage = myChart.Chart; chartRange = xlWorkSheet.get_Range("g3", "a1"); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; string temp_name = "Statistic for studet " + user.LastName + " for curriculum " + curriculum.Name + ".xls"; xlWorkBook.SaveAs(temp_name, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); }
/// <summary> /// Writes an Excel worksheet containing a Respiratory Signal's values and Plot /// </summary> /// <param name="ws"> The Excel worksheet object </param> /// <param name="SignalName"> The name of the respiratory signal </param> /// <param name="table"> The respiratory signal values and peak/onset locations </param> /// <param name="ROWS"> The number of rows in the table </param> /// <param name="COLUMNS"> The number of columns in the table </param> public static void AddRespiratorySignalToWorksheet(Excel.Worksheet ws, string SignalName, object[,] table, int ROWS, int COLUMNS) { // Make Table with Values Excel.Range range = ws.Range[ws.Cells[3, 2], ws.Cells[3 + ROWS - 1, 2 + COLUMNS - 1]]; range.Value = table; ws.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, range, System.Reflection.Missing.Value, Excel.XlYesNoGuess.xlGuess, System.Reflection.Missing.Value).Name = ws.Name; ws.ListObjects[ws.Name].TableStyle = "TableStyleLight9"; ws.Columns["A:I"].ColumnWidth = 20; ws.Columns["E:H"].Hidden = true; ws.Columns["B:H"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // Add Conditional Formatting Excel.Range range2 = ws.Range[ws.Cells[4, 2], ws.Cells[3 + ROWS - 1, 2 + COLUMNS - 1]]; range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($E4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value); range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($F4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value); range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($G4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value); range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($H4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value); range2.FormatConditions[1].Interior.Color = 5296274; range2.FormatConditions[2].Interior.Color = 255; range2.FormatConditions[3].Interior.Color = 65535; range2.FormatConditions[4].Interior.Color = 15773696; range2.Columns[2].NumberFormat = "m/d/yyyy h:mm:ss.000"; // Add Chart Excel.Chart chart = ((Excel.ChartObject)((Excel.ChartObjects)ws.ChartObjects()).Add(500, 100, 900, 500)).Chart; chart.SetSourceData(range.Columns["B:G"]); chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterLines; chart.ChartWizard(Source: range.Columns["B:G"], Title: SignalName, CategoryTitle: "Time", ValueTitle: SignalName); chart.PlotVisibleOnly = false; ((Excel.Series)chart.SeriesCollection(1)).ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers; ((Excel.Series)chart.SeriesCollection(2)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare; ((Excel.Series)chart.SeriesCollection(3)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare; ((Excel.Series)chart.SeriesCollection(4)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare; ((Excel.Series)chart.SeriesCollection(5)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare; ((Excel.Series)chart.SeriesCollection(2)).Format.Fill.ForeColor.RGB = 5296274; ((Excel.Series)chart.SeriesCollection(3)).Format.Fill.ForeColor.RGB = 255; ((Excel.Series)chart.SeriesCollection(4)).Format.Fill.ForeColor.RGB = 65535; ((Excel.Series)chart.SeriesCollection(5)).Format.Fill.ForeColor.RGB = 15773696; ((Excel.Series)chart.SeriesCollection(1)).Format.Line.ForeColor.RGB = 38450; ((Excel.Series)chart.SeriesCollection(2)).Format.Line.ForeColor.RGB = 5296274; ((Excel.Series)chart.SeriesCollection(3)).Format.Line.ForeColor.RGB = 255; ((Excel.Series)chart.SeriesCollection(4)).Format.Line.ForeColor.RGB = 65535; ((Excel.Series)chart.SeriesCollection(5)).Format.Line.ForeColor.RGB = 15773696; System.Runtime.InteropServices.Marshal.ReleaseComObject(chart); System.Runtime.InteropServices.Marshal.ReleaseComObject(range); System.Runtime.InteropServices.Marshal.ReleaseComObject(range2); }
public void Create(List <ExcelValues> dict) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 1] = "Time"; xlWorkSheet.Cells[1, 2] = "Value"; for (int i = 0; i < dict.Count; i++) { xlWorkSheet.Cells[i + 2, 1] = string.Format("{0}:{1}:{2}", dict[i].time.Hours, dict[i].time.Minutes, dict[i].time.Seconds); xlWorkSheet.Cells[i + 2, 2] = dict[i].value; } //xlWorkSheet.Cells[2, 1] = "00:0:0"; //xlWorkSheet.Cells[2, 2] = "25.8"; Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250); Excel.Chart chartPage = myChart.Chart; string c = (dict.Count + 1).ToString(); chartRange = xlWorkSheet.get_Range("A1", "b" + c); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xlLine; string f = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location); try { xlWorkBook.SaveAs(System.IO.Path.Combine(f, @"informations.xls"), Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); } catch (Exception ex) { Console.WriteLine(ex.Message); } xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); }
public void SetChartData(int dataCol, int legCol, int rowStart, int rowEnd, Excel.XlChartType chartType, bool showLegend) { Excel.Worksheet wss = (Excel.Worksheet)ewb.Sheets["Graph"]; Excel.ChartObjects cos = (Excel.ChartObjects)wss.ChartObjects(Type.Missing); Excel.ChartObject co = (Excel.ChartObject)cos.Item("Диаграмма 1"); Excel.Chart ch = (Excel.Chart)co.Chart; ch.HasLegend = showLegend; ch.ChartType = chartType; ch.SetSourceData(ewsh.Range[ewsh.Cells[rowStart, legCol], ewsh.Cells[rowEnd, dataCol]], Type.Missing); ReleaseObject(ch); ReleaseObject(co); ReleaseObject(cos); ReleaseObject(wss); }
public static void PieChartReport(int pass, int fail, int notRun) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int totalNumberOfTestcase = pass + fail + notRun; //add data xlWorkSheet.Cells[1, 1] = "Status"; xlWorkSheet.Cells[1, 2] = "Result"; xlWorkSheet.Cells[2, 1] = "Pass"; xlWorkSheet.Cells[2, 2] = pass; xlWorkSheet.Cells[3, 1] = "Fail"; xlWorkSheet.Cells[3, 2] = fail; xlWorkSheet.Cells[4, 1] = "NotRun"; xlWorkSheet.Cells[4, 2] = notRun; Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250); Excel.Chart chartPage = myChart.Chart; chartRange = xlWorkSheet.get_Range("A1", "B4"); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xl3DPie; xlWorkBook.SaveAs(ParallelSelenium.Parallel_Quest.targetdir + "\\" + "Rrsult_Piechart.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook);; releaseObject(xlApp); }
internal static void Export(DataGridView dataTable) { _Application excelApp = new _Excel.Application(); Workbook wb; wb = excelApp.Workbooks.Add(); Worksheet ws = wb.Worksheets[1]; double[] arrOfX = new double[dataTable.Rows.Count - 1]; double[] arrOfY = new double[dataTable.Rows.Count - 1]; GetValues.FillArrays(arrOfX, arrOfY, dataTable); excelApp.Visible = true; excelApp.Cells[1, 1] = "X:"; excelApp.Cells[1, 2] = "Y:"; int row = 3; int column = 1; //начинать заполнение необходимо с 3 строчки, т к при построении графика левая верхняя клеточка должна быть пустой for (int i = 0; i < arrOfX.Length; i++) { excelApp.Cells[row, column] = arrOfX[i]; column++; excelApp.Cells[row, column] = arrOfY[i]; column--; row++; } _Excel.Range eRange = ws.get_Range("a2", "b" + row); _Excel.ChartObjects eChartObjects = (_Excel.ChartObjects)ws.ChartObjects(Type.Missing); _Excel.ChartObject eChartObj = eChartObjects.Add(10, 30, 300, 300); //размеры диаграммы _Excel.Chart eChart = eChartObj.Chart; eChart.ChartType = _Excel.XlChartType.xlLine; eChart.ChartWizard( Source: eRange, Title: "Cassini Oval", CategoryTitle: "xAxis", CategoryLabels: 2, ValueTitle: "yAxis", HasLegend: false); eChart.SetSourceData(eRange); }
public void createTempChart() { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook .Worksheets.get_Item(1); xlWorkSheet.Cells[1, 2] = "a"; xlWorkSheet.Cells[1, 3] = "b"; Excel.Application xlApp1 = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp1.Workbooks.Open (@"E:\temp.csv"); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet .ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250); Excel.Chart chartPage = myChart.Chart; chartRange = xlWorkSheet.get_Range("A1", "C6"); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; // Export chart as picture file chartPage.Export(@"E:\temp_data.bmp", "BMP", misValue); xlWorkBook.SaveAs("temp.csv", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); DeallocateObject(xlWorkSheet); DeallocateObject(xlWorkBook); DeallocateObject(xlApp); DeallocateObject(xlApp1); }
private void button1_Click(object sender, EventArgs e) { if (cht1.Series[0].Points.Count > 1) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); for (int i = 0; i < cht1.Series.Count; i++) { xlWorkSheet.Cells[1, 1] = ""; xlWorkSheet.Cells[1, 2] = "DateTime"; //put your column heading here xlWorkSheet.Cells[1, 3] = "Data"; // put your column heading here for (int j = 0; j < cht1.Series[i].Points.Count; j++) { xlWorkSheet.Cells[j + 2, 2] = cht1.Series[i].Points[j].XValue; xlWorkSheet.Cells[j + 2, 3] = cht1.Series[i].Points[j].YValues[0]; } } Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(150, 10, 800, 300); Excel.Chart chartPage = myChart.Chart; chartRange = xlWorkSheet.get_Range("c2", "c100");//update the range here chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; xlWorkBook.SaveAs("grafico.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Arquivo excel grafico.xls criado na pasta documentos"); } }
public static void CreateChart(string filename) { Excel.Application excelApplication = new Excel.Application(); excelApplication.Visible = true; //Opens the excel file Excel.Workbook excelWorkbook = excelApplication.Workbooks.Add(); Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets.get_Item(1); excelWorksheet.Cells[1, 1] = "Product ID"; excelWorksheet.Cells[1, 2] = "Stock"; excelWorksheet.Cells[2, 1] = "BB12"; excelWorksheet.Cells[2, 2] = "15"; excelWorksheet.Cells[3, 1] = "xy40"; excelWorksheet.Cells[3, 2] = "18"; excelWorksheet.Cells[4, 1] = "AX50"; excelWorksheet.Cells[4, 2] = "23"; //Add a char object Excel.Chart myChart = null; Excel.ChartObjects charts = excelWorksheet.ChartObjects(); Excel.ChartObject chartObj = charts.Add(50, 50, 300, 300); //left; top; width; height myChart = chartObj.Chart; //set chart range -- cell values to be used in the graph Excel.Range myRange = excelWorksheet.get_Range("B1:B4"); myChart.SetSourceData(myRange); //chart properties using the named properties and default parameters functionality in //the .Net Framework myChart.ChartType = Excel.XlChartType.xlLine; myChart.ChartWizard(Source: myRange, Title: "Graph Title", CategoryTitle: "Title of X axis... ", ValueTitle: "Title of Y axis... "); excelWorkbook.SaveAs(filename); excelWorkbook.Close(); excelApplication.Quit(); ReleaseCOMObjects(excelWorksheet); ReleaseCOMObjects(excelWorkbook); ReleaseCOMObjects(excelApplication); }
/// <summary> /// Writes an Excel worksheet containing a signal's values and Plot /// </summary> /// <param name="ws"> The Excel worksheet object </param> /// <param name="SignalName"> The name of the signal </param> /// <param name="table"> The signal values </param> /// <param name="ROWS"> The number of rows in the table </param> /// <param name="COLUMNS"> The number of columns in the table </param> /// <param name="color"> The color of the plot </param> public static void AddSignalToWorksheet(Excel.Worksheet ws, string SignalName, object[,] table, int ROWS, int COLUMNS, OxyColor color) { Excel.Range range = ws.Range[ws.Cells[3, 2], ws.Cells[3 + ROWS - 1, 2 + COLUMNS - 1]]; range.Value = table; ws.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, range, System.Reflection.Missing.Value, Excel.XlYesNoGuess.xlGuess, System.Reflection.Missing.Value).Name = ws.Name; ws.ListObjects[ws.Name].TableStyle = "TableStyleLight9"; ws.Columns["A:I"].ColumnWidth = 20; ws.Columns["B:H"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.Columns[2].NumberFormat = "m/d/yyyy h:mm:ss.000"; Excel.Chart chart = ((Excel.ChartObject)((Excel.ChartObjects)ws.ChartObjects()).Add(500, 100, 900, 500)).Chart; chart.SetSourceData(range.Columns["B:C"]); chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterLines; chart.ChartWizard(Source: range.Columns["B:C"], Title: SignalName, CategoryTitle: "Time", ValueTitle: SignalName); ((Excel.Series)chart.SeriesCollection(1)).ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers; ((Excel.Series)chart.SeriesCollection(1)).Format.Line.ForeColor.RGB = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(color.A, color.R, color.G, color.B)); System.Runtime.InteropServices.Marshal.ReleaseComObject(chart); System.Runtime.InteropServices.Marshal.ReleaseComObject(range); }
private Excel.Chart MakePolarChart(Excel.Worksheet xlWorkSheet) { xlWorkSheet.Range["A1", "A5"].Value2 = 22; xlWorkSheet.Range["B1", "B5"].Value2 = 55; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = xlCharts.Add(10, 80, 300, 250); Excel.Chart chartPage = myChart.Chart; chartPage.SetSourceData(xlWorkSheet.Range["A1", "B5"], Excel.XlRowCol.xlColumns); chartPage.ChartType = Excel.XlChartType.xlRadar; Excel.ChartGroup group = (Excel.ChartGroup)chartPage.RadarGroups(1); group.HasRadarAxisLabels = true; return(chartPage); }
static void Main(string[] args) { // Создаём экземпляр нашего приложения Excel.Application excelApp = new Excel.Application(); // Создаём экземпляр рабочий книги Excel Excel.Workbook workBook; // Создаём экземпляр листа Excel Excel.Worksheet workSheet; workBook = excelApp.Workbooks.Add(); workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1); // Заполняем первую строку числами от 1 до 10 for (int j = 1; j <= 10; j++) { workSheet.Cells[1, j] = j; } // Вычисляем сумму этих чисел Excel.Range rng = workSheet.Range["A2"]; rng.Formula = "=SUM(A1:L1)"; rng.FormulaHidden = false; // Выделяем границы у этой ячейки Excel.Borders border = rng.Borders; border.LineStyle = Excel.XlLineStyle.xlContinuous; // Строим круговую диаграмму Excel.ChartObjects chartObjs = (Excel.ChartObjects)workSheet.ChartObjects(); Excel.ChartObject chartObj = chartObjs.Add(5, 50, 300, 300); Excel.Chart xlChart = chartObj.Chart; Excel.Range rng2 = workSheet.Range["A1:L1"]; // Устанавливаем тип диаграммы xlChart.ChartType = Excel.XlChartType.xlPie; // Устанавливаем источник данных (значения от 1 до 10) xlChart.SetSourceData(rng2); // Открываем созданный excel-файл excelApp.Visible = true; excelApp.UserControl = true; }
/// <summary> /// создание Excel отчета о наиболее/наименее продаваемом букете /// </summary> /// <param name="pie_values"></param> public void Create_Excel_Doc(List <KeyValuePair <string, int> > pie_values) { if (pie_values != null) { Excel.Application exapp = new Excel.Application(); exapp.SheetsInNewWorkbook = 1; exapp.Workbooks.Add(Type.Missing); exapp.DisplayAlerts = true; exapp.Visible = true; Excel.Workbooks exappworkbooks = exapp.Workbooks; Excel.Workbook exappworkbook = exappworkbooks[1]; exappworkbook.Saved = false; Excel.Sheets excellsheets = exappworkbook.Worksheets; Excel.Worksheet excellworksheet = (Excel.Worksheet)excellsheets.get_Item(1); excellworksheet.Activate(); Excel.Range excelcells; exapp.Cells[1, 1] = "Букет"; exapp.Cells[1, 2] = "Количество"; exapp.Cells[2, 1] = pie_values[0].Key; exapp.Cells[2, 2] = pie_values[0].Value; exapp.Cells[3, 1] = pie_values[1].Key; exapp.Cells[3, 2] = pie_values[1].Value; Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)excellworksheet.ChartObjects(Type.Missing); Excel.ChartObject chartsobj = chartsobjrcts.Add(10, 200, 500, 300); Excel.Chart excelchart = chartsobj.Chart; excelcells = excellworksheet.get_Range("A1", "B3"); excelchart.SetSourceData(excelcells, Type.Missing); excelchart.ChartType = Excel.XlChartType.xlPie; excelchart.HasTitle = true; excelchart.ChartTitle.Text = "Наиболее/наименее продаваемый букет"; } else { System.Windows.MessageBox.Show("нет данных для отчета"); } }
/// <summary> /// Создать в Excel таблицу, по которой можно построить 3D поверхность /// </summary> /// <param name="f">Функция двух переменных</param> /// <param name="x0">Начало отрезка по первому аргументу</param> /// <param name="X">Конец отрезка по первому аргументу</param> /// <param name="xcount">Число шагов по первому аргументу</param> /// <param name="y0">Начало отрезка по второму аргументу</param> /// <param name="Y">Конец отрезка по второму аргументу</param> /// <param name="ycount">Число шагов по второму аргументу</param> public static void CreatTableInExcel(DRealFunc f, double x0, double X, int xcount, double y0, double Y, int ycount) { Application application = new Application(); application.Workbooks.Add(Type.Missing); Worksheet sheet = (Worksheet)application.Sheets[1]; var t = Type.Missing; double hx = (X - x0) / (xcount - 1), hy = (Y - y0) / (ycount - 1); for (int i = 0; i < ycount; i++) { sheet.Range[CharForExcel('B', i) + "2", t].Value2 = y0 + i * hy; } for (int i = 0; i < xcount; i++) { sheet.Range["A" + (3 + i).ToString(), t].Value2 = x0 + i * hx; } //for (int j = 0; j < xcount; j++) Parallel.For(0, xcount, (int j) => { for (int i = 0; i < ycount; i++) { sheet.Range[CharForExcel('B', i) + (3 + j).ToString(), t].Value2 = f(sheet.Range["A" + (3 + j).ToString(), t].Value2, sheet.Range[CharForExcel('B', i) + "2", t].Value2); } }); sheet.Range["A2", CharForExcel('B', ycount) + (3 + xcount).ToString()].Activate(); Excel.Chart chart = sheet.ChartObjects().Add(10, 10, 500, 500).Chart; chart.ChartType = Excel.XlChartType.xl3DArea; chart.SetSourceData(sheet.Range["A2", CharForExcel('B', ycount) + (3 + xcount).ToString()]); application.Visible = true; }
private void button3_Click(object sender, EventArgs e) //Refresh/ get charts { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250); Excel.Chart chartPage = myChart.Chart; chartRange = xlWorkSheet.get_Range("A1", "d5"); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; //export chart as picture file chartPage.Export(@"C:\Dumpster\testcsv.bmp", "BMP", misValue); // pictureBox1.Image = new Bitmap(@"C:\Dumpster\testcsv.bmp"); xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Excel file created , you can find the file c:\\csharp-Excel.xls"); }
private void EditChart(Excel.Chart chart, int index) { chart.ChartType = Excel.XlChartType.xlColumnClustered; var range1 = shAuswertung1.Range[shAuswertung1.Cells[1, 1], shAuswertung1.Cells[index, 1]]; var range2 = shAuswertung1.Range[shAuswertung1.Cells[1, 5], shAuswertung1.Cells[index, 5]]; var range3 = shAuswertung1.Range[shAuswertung1.Cells[1, 9], shAuswertung1.Cells[index, 9]]; var range4 = shAuswertung1.Range[shAuswertung1.Cells[1, 10], shAuswertung1.Cells[index, 10]]; chart.SetSourceData(Source: Globals.ThisAddIn.Application.Union(range1, range2, range3, range4)); chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom; chart.Legend.Font.Size = 8; chart.Legend.Font.Name = "Arial"; chart.SeriesCollection(1).Format.Fill.ForeColor.RGB = Color.FromArgb(192, 0, 0); chart.SeriesCollection(2).Format.Fill.ForeColor.RGB = Color.FromArgb(0, 112, 192); chart.SeriesCollection(3).Format.Fill.ForeColor.RGB = Color.FromArgb(255, 140, 0); chart.Axes(Excel.XlAxisType.xlCategory).CategoryType = Excel.XlCategoryType.xlCategoryScale; chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Font.Size = 8; chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Font.Name = "Arial"; chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Orientation = 90; chart.Axes(Excel.XlAxisType.xlValue).TickLabels.Font.Size = 8; chart.Axes(Excel.XlAxisType.xlValue).TickLabels.Font.Name = "Arial"; chart.Axes(Excel.XlAxisType.xlValue).MinimumScale = 0; }
public void CreateChart(string pivotTableName, int chartTop, string cTitle) { Excel.Worksheet activeSheet = null; Excel.Range selectedRange = null; Excel.Shapes shapes = null; Excel.Chart chart = null; Excel.ChartTitle chartTitle = null; try { activeSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet; Excel.Range rangePivot; rangePivot = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "AA20000"); selectedRange = IdentifyPivotRangesByName(pivotTableName); shapes = activeSheet.Shapes; // Width original 255 if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0") { // 204 is a nice style with shadow shapes.AddChart2(Style: 259, XlChartType: Excel.XlChartType.xlColumnClustered, Left: 10, Top: chartTop, Width: 450, Height: 210, NewLayout: true).Select(); } else { shapes.AddChart(XlChartType: Excel.XlChartType.xlColumnClustered, Left: 10, Top: chartTop, Width: 450, Height: 210).Select(); } chart = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveChart; chart.HasTitle = true; chart.ChartTitle.Text = cTitle; chart.ChartTitle.Format.TextFrame2.TextRange.Font.Caps = Microsoft.Office.Core.MsoTextCaps.msoNoCaps; chart.ChartArea.Interior.Color = System.Drawing.Color.FromArgb(242, 244, 244); // Change chart to light gray // chart.ChartArea.Interior.Color = System.Drawing.Color.FromRgb(0, 255, 0); // chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, true, true, true, true, true, true); // Turn on data labels chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, false, false, true, true, true, true); // Turn on data labels chart.SetSourceData(selectedRange); chart.HasLegend = false; chart.ApplyDataLabels(); if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0") { chart.FullSeriesCollection(1).DataLabels.ShowValue = true; } else { chart.SeriesCollection(1).DataLabels.ShowValue = true; } if (pivotTableName == "PivotTableApplicationName") { chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Orientation = Excel.XlOrientation.xlUpward; } selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = 0; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].TintAndShade = 0; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = 0; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].TintAndShade = 0; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin; Globals.ChartBottom = (int)chart.ChartArea.Top + (int)chart.ChartArea.Height + 15; (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select(); // chart.Export(pivotTableName + ".png"); } catch (Exception ex) { // ex.Data.Add("ExcelHelper", "Error occurred in the CreateChart() method"); ex.Data.Add("ExcelHelper", " Error occurred in the CreateChart() method\r\n" + ex.Message); throw; } finally { if (chartTitle != null) { Marshal.ReleaseComObject(chartTitle); } if (chart != null) { Marshal.ReleaseComObject(chart); } if (shapes != null) { Marshal.ReleaseComObject(shapes); } if (selectedRange != null) { Marshal.ReleaseComObject(selectedRange); } } }
private void CreateChart() { chart = oWS.ChartObjects().Add(0,0,800,500).Chart; chart.SetSourceData(oWS.UsedRange); chart.ChartType = Excel.XlChartType.xlColumnClustered; chart.ChartStyle = 17; foreach (Excel.ChartGroup group in chart.ChartGroups()) { group.GapWidth = 0; group.Overlap = 0; } }
private void отчетToolStripMenuItem_Click(object sender, EventArgs e) { SelectDateRange srd = new SelectDateRange(); srd.dateTimePicker1.Value = (DateTime)db.Invoices.First().sale_date; DialogResult dr = srd.ShowDialog(this); DateTime startDate = srd.dateTimePicker1.Value; DateTime endDate = srd.dateTimePicker2.Value; List <Invoice> invoices = db.Invoices.Where(i => i.sale_date.Value >= startDate && i.sale_date.Value <= endDate).OrderByDescending(i => i.Product.name).ToList(); //string[] head = { "Код", "Название товара", "Цена за единицу", "Заказано КГ.", "Дата продажи", "Общая стоимость" }; if ((dr == DialogResult.OK) && (0 < invoices.Count)) { // Load Excel application Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); // Create empty workbook excel.Workbooks.Add(); // Create Worksheet from active sheet Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet; try { workSheet.Cells[1, "A"] = "Код накладной"; workSheet.Cells[1, "B"] = "Название товара"; workSheet.Cells[1, "C"] = "Цена за единицу"; workSheet.Cells[1, "D"] = "Заказано (КГ.)"; workSheet.Cells[1, "E"] = "Дата покупки"; workSheet.Cells[1, "F"] = "Сумма за покупку"; workSheet.Cells[1, "H"] = "Название товара"; workSheet.Cells[1, "I"] = "Количество заказанного товара"; workSheet.Cells[1, "K"] = "Нужно закупить больше:"; workSheet.Cells[1, "L"] = "Нужно закупить меньше:"; int row = 2; ArrayList nameArray = new ArrayList(); ArrayList sumArray = new ArrayList(); int sum; string name; foreach (Invoice invoice in invoices) { workSheet.Cells[row, "A"] = invoice.invoice_code; workSheet.Cells[row, "B"] = invoice.Product.name; workSheet.Cells[row, "C"] = string.Format("{0} грн.", invoice.Product.price); workSheet.Cells[row, "D"] = string.Format("{0} кг.", invoice.quantity); workSheet.Cells[row, "E"] = invoice.sale_date.Value.Date; workSheet.Cells[row, "F"] = string.Format("{0} грн.", invoice.price); name = db.Invoices.Where(nn => nn.product_id == invoice.Product.id && nn.sale_date.Value >= startDate && nn.sale_date.Value <= endDate).First().Product.name.ToString(); sum = (int)db.Invoices.Where(i => i.Product.name == invoice.Product.name && i.sale_date.Value >= startDate && i.sale_date.Value <= endDate).Sum(s => s.quantity); if (!nameArray.Contains(name)) { nameArray.Add(name); } if (!sumArray.Contains(sum)) { sumArray.Add(sum); } row++; } row = 2; foreach (string nameRow in nameArray) { workSheet.Cells[row, "H"] = nameRow; row++; } row = 2; foreach (int sumRow in sumArray) { workSheet.Cells[row, "I"] = sumRow; row++; } Excel.ChartObjects chartObjs = (Excel.ChartObjects)workSheet.ChartObjects(Type.Missing); Excel.ChartObject chartObj = chartObjs.Add(100, 20, 150, 200); Excel.Chart xlChart = chartObj.Chart; Excel.Range rg = workSheet.get_Range("H2:H" + (row - 1).ToString(), "I2:I" + (row - 1).ToString()); xlChart.ChartType = Excel.XlChartType.xlPieExploded; xlChart.SetSourceData(rg, Type.Missing); //rg.FormulaLocal = "MAX(I2:I"+(row-1).ToString(); //workSheet.Cells[2, "M"].Value = "=MAX(I2:I" + (row - 1).ToString(); //workSheet.Cells[2, "N"].Value = "=MIN(I2:I" + (row - 1).ToString(); //for (int i = 2; i < row; i++) // { // workSheet.Cells[i, "K"].Value = "=ЕСЛИ(M2=I" + i.ToString() + ";H" + i.ToString() + ";0)"; // } //for (int i = 2; i < row; i++) // { // workSheet.Cells[i, "L"].Value = "=ЕСЛИ(N2=I" + i.ToString() + ";H" + i.ToString() + ";0)"; // } // rg.FormulaLocal = "MAX(I2:I"+(row-1).ToString(); //rg.FormulaLocal = "MIN(I2:I"+(row-1).ToString(); // Apply some predefined styles for data to look nicely :) workSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1); workSheet.Range["H1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1); workSheet.Range["K1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1); // Define filename string fileName = string.Format(@"{0}\ExcelData.xlsx", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory)); // Save this data as a file workSheet.SaveAs(fileName); // Display SUCCESS message MessageBox.Show(string.Format("Файл '{0}' успешно сохранен!", fileName)); } catch (Exception exception) { MessageBox.Show("Exception", "Ошибка записи файла!\n" + exception.Message, MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { // Quit Excel application excel.Quit(); //excel.Workbooks.Open(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory)); // Release COM objects (very important!) if (excel != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); } if (workSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); } // Empty variables excel = null; workSheet = null; // Force garbage collector cleaning GC.Collect(); } } else { MessageBox.Show("В выбраном диапазоне, нет накладных!"); } if (dr == DialogResult.Cancel) { return; } }