/// <summary> /// Cancella il contenuto del foglio. /// </summary> private void Clear() { if (_ws.ChartObjects().Count > 0) { _ws.ChartObjects().Delete(); } _ws.Rows.ClearContents(); _ws.Rows.ClearComments(); _ws.Rows.FormatConditions.Delete(); _ws.Rows.EntireRow.Hidden = false; _ws.Rows.UnMerge(); _ws.Rows.Style = "Normal"; _ws.Rows.RowHeight = Struct.cell.height.normal; _ws.Columns.ColumnWidth = Struct.cell.width.dato; _ws.Rows["1:" + (_struttura.rigaBlock - 1)].RowHeight = Struct.cell.height.empty; _ws.Rows[_struttura.rigaGoto].RowHeight = Struct.cell.height.normal; _ws.Columns[1].ColumnWidth = Struct.cell.width.empty; if (_ws.Visible == Excel.XlSheetVisibility.xlSheetVisible) { ((Excel._Worksheet)_ws).Activate(); _ws.Application.ActiveWindow.FreezePanes = false; _ws.Cells[_struttura.rigaBlock, 1].Select(); _ws.Application.ActiveWindow.FreezePanes = true; Workbook.Main.Select(); } _ws.Application.ScreenUpdating = false; }
public void DrawPointChart(string chartname, int row, string xname, string yname, string[] xvalues, double[] yvalues) { var v = string.Join(";", yvalues); DrawTable(chartname, row, xvalues, new string[] { v }); var chartobjs = worksheet.ChartObjects(Type.Missing); var chartobj = chartobjs.Add(20, 15 * row + 45, 500, 200); chartobj.Chart.ChartWizard( worksheet.Range[worksheet.Cells[row + 1, 2], worksheet.Cells[row + 2, yvalues.Length + 1]], Excel.XlChartType.xlLineMarkersStacked, 2, Excel.XlRowCol.xlRows, Type.Missing, 0, false, chartname, xname, yname, Type.Missing); }
private void ButtonApply_Click(object sender, RoutedEventArgs e) { Excel.Worksheet workSheet2 = (Excel.Worksheet)_workBook.Sheets[2]; Excel.Worksheet workSheet3 = (Excel.Worksheet)_workBook.Sheets[3]; int rowIndex = 0; for (int x = 0; x <= 10; x++) { workSheet2.Cells[3, "F"].Value = x; double y = workSheet2.Cells[6, "F"].Value; workSheet3.Cells[rowIndex + 1, "H"].Value = x; workSheet3.Cells[rowIndex + 1, "I"].Value = y; rowIndex++; } Excel.ChartObjects chartObjs = (Excel.ChartObjects)workSheet3.ChartObjects(Type.Missing); Excel.ChartObject myChart = chartObjs.Add(20, 60, 200, 200); Excel.Chart chart = myChart.Chart; Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(Type.Missing); Excel.Series series = seriesCollection.NewSeries(); series.XValues = workSheet3.get_Range("H1", "H10"); series.Values = workSheet3.get_Range("I1", "I10"); chart.ChartType = Excel.XlChartType.xlXYScatterSmooth; }
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 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(); }
private void button1_Click(object sender, EventArgs e) { SaveFileDialog s = new SaveFileDialog(); s.Filter = "File Excel|*.xls"; if (s.ShowDialog() == DialogResult.OK) { string path = s.FileName; reportViewer1.ExportDialog(reportViewer1.LocalReport.ListRenderingExtensions()[0], "", path); try { excel.Application app = new excel.Application(); excel.Workbook book = app.Workbooks.Open(path); excel.Worksheet sheet = book.Sheets[1]; excel.ChartObjects charts = (excel.ChartObjects)sheet.ChartObjects(Type.Missing); excel.ChartObject chart = charts.Add(20, 100, 700, 250); chart.Chart.SetSourceData(sheet.UsedRange); chart.Chart.ChartType = excel.XlChartType.xlColumnClustered; book.Save(); book.Close(true); app.Quit(); MessageBox.Show($"File was created at {path}"); } catch (Exception) { MessageBox.Show("Missing library"); } } }
public void Excel_Create(string file_name, int latin, int kiril, int num) { exel.Application application = new exel.Application(); exel.Workbook workbook = application.Workbooks.Add(); exel.Worksheet worksheet = (exel.Worksheet)workbook.ActiveSheet; worksheet.Name = file_name; worksheet.Cells[1, 1] = "Латинские буквы"; worksheet.Cells[2, 1] = "Кириллица"; worksheet.Cells[3, 1] = "Числа"; worksheet.Cells[1, 2] = latin; worksheet.Cells[2, 2] = kiril; worksheet.Cells[3, 2] = num; worksheet.Columns.AutoFit(); exel.ChartObjects chartObjects = (exel.ChartObjects)worksheet.ChartObjects(Type.Missing); exel.ChartObject chartObject = chartObjects.Add(50, 50, 250, 250); exel.Chart chart = chartObject.Chart; exel.SeriesCollection seriesCollection = (exel.SeriesCollection)chart.SeriesCollection(Type.Missing); exel.Series series = seriesCollection.NewSeries(); chart.ChartType = exel.XlChartType.xl3DPie; series.XValues = worksheet.get_Range("A1", "A3"); series.Values = worksheet.get_Range("B1", "B3"); workbook.SaveAs(); workbook.Close(); application.Quit(); }
public void FinishSheet(string name) { Excel.Worksheet sheet = sheets[name]; int rows = sheetRows[name]; //Table FormatAsTable(sheet.get_Range("A2", "E" + (rows - 1)), "TheTable"); //Chart Excel.ChartObjects xlCharts = (Excel.ChartObjects)sheet.ChartObjects(Type.Missing); Excel.ChartObject chartObj = (Excel.ChartObject)xlCharts.Add(500, 10, 800, 300); chartObj.Chart.ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers; //Erste Series addChartSeries(chartObj, sheet.get_Range("A3", "A" + (rows - 1)), sheet.get_Range("E3", "E" + (rows - 1)), "sell"); //Zweite Series addChartSeries(chartObj, sheet.get_Range("A3", "A" + (rows - 1)), sheet.get_Range("D3", "D" + (rows - 1)), "buy"); //Count Chart Excel.ChartObject countChart = (Excel.ChartObject)xlCharts.Add(500, 330, 800, 300); countChart.Chart.ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers; //Erste Series addChartSeries(countChart, sheet.get_Range("A3", "A" + (rows - 1)), sheet.get_Range("C3", "C" + (rows - 1)), "Count"); }
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); } }
/// <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); } }
private Excel.Chart CreateNewBubbleChart(Excel.Worksheet excelWorksheet, double chartWidth, double chartHeight, double bubbleScaleFactor) { var excelChartObjects = (Excel.ChartObjects)excelWorksheet.ChartObjects(); var chartObject = excelChartObjects.Add(10, 80, chartWidth - HeuristicExtraWidth, chartHeight - HeuristicExtraHeight); var bubbleChart = chartObject.Chart; bubbleChart.ChartType = Excel.XlChartType.xlBubble3DEffect; bubbleChart.ChartGroups(1).BubbleScale = Clamp(100 * bubbleScaleFactor * DefaultBubbleScale, 0, 300); var xAxis = (Excel.Axis)bubbleChart.Axes(Excel.XlAxisType.xlCategory); xAxis.HasTitle = true; xAxis.AxisTitle.Caption = "average Hourly"; xAxis.HasMajorGridlines = true; xAxis.MajorGridlines.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbGainsboro; var yAxis = (Excel.Axis)bubbleChart.Axes(Excel.XlAxisType.xlValue); yAxis.HasTitle = true; yAxis.AxisTitle.Caption = "TG2 Medel"; yAxis.HasMajorGridlines = true; yAxis.TickLabels.NumberFormat = "0%"; yAxis.MajorGridlines.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbGainsboro; return(bubbleChart); }
public TreemapChart Draw(Excel.Worksheet sheet) { Excel.ChartObjects cos = sheet.ChartObjects(); Excel.ChartObject co = cos.Add(ChartArea.Left, ChartArea.Top, ChartArea.Width, ChartArea.Height); Chart = co.Chart; Draw(); return(this); }
internal Excel.ChartObject newEnPIChart() { Excel.Range start = BottomCell().get_Offset(2, 0); double topleft; if (!double.TryParse(start.Top.ToString(), out topleft)) { topleft = 0; } start.EntireRow.RowHeight = Utilities.Constants.CHART_HEIGHT * 1.1; Excel.ChartObject CO = ((Excel.ChartObjects)thisSheet.ChartObjects(System.Type.Missing)) .Add(10, topleft, Utilities.Constants.CHART_WIDTH, Utilities.Constants.CHART_HEIGHT); CO.Placement = Excel.XlPlacement.xlMove; return(CO); }
public void ExportCharts(string item, string folder, string format) { Excel.Worksheet xlWorkSheet = (Excel.Worksheet)_xlWorkBook.Worksheets.Item[item]; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); foreach (Excel.ChartObject xlChart in xlCharts) { Excel.Chart chartPage = xlChart.Chart; chartPage.Export(Path.Combine(folder, $"{chartPage.Name}.{format}"), format, _misValue); } }
public void generarHistograma(int intervalos) { Excel.Range rangoValores; 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; //Rango de valores del grafico rangoValores = xlWorkSheet.get_Range("D5", "D" + (intervalos + 5)); }
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); }
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); }
/// <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 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); }
private static void CreateBoxCart(Excel.Worksheet targetSheet, Excel.Range xValues, String kwartyl1Desc, Excel.Range kwartyl1Values, String medianaDesc, Excel.Range medianaValues, String kwartyl3Desc, Excel.Range kwartyl3Values, Excel.Range ErrorMaksimum, Excel.Range ErrorMadiana, Excel.Range ErrorMinimum) { Excel.ChartObjects xlCharts = (Excel.ChartObjects)targetSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 100, 300, 250); Excel.Chart chartPage = myChart.Chart; chartPage.HasLegend = false; chartPage.HasTitle = true; SeriesCollection seriesCollection = chartPage.SeriesCollection(); Series series1 = seriesCollection.NewSeries(); series1.Name = kwartyl1Desc; series1.XValues = xValues; series1.Values = kwartyl1Values; series1.HasErrorBars = true; series1.ErrorBar(XlErrorBarDirection.xlY, XlErrorBarInclude.xlErrorBarIncludeMinusValues, XlErrorBarType.xlErrorBarTypeCustom, ErrorMinimum, ErrorMinimum); series1.Format.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoFalse; series1 = seriesCollection.NewSeries(); series1.Name = medianaDesc; series1.XValues = xValues; series1.Values = medianaValues; //series1.Format.Line.Parent series1.Format.Line.Weight = 2.0F; series1.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTriStateMixed; //Tri-State series1.Format.Line.ForeColor.RGB = (int)Microsoft.Office.Interop.Excel.XlRgbColor.rgbBlack; //series1.Format.Line.ForeColor.RGB = (int)XlRgbColor.rgbBlack; //series1.Format.Line.Weight = 5; series1 = seriesCollection.NewSeries(); series1.Name = kwartyl3Desc; series1.XValues = xValues; series1.Values = kwartyl3Values; series1.Format.Line.Weight = 2.0F; series1.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTriStateMixed; //Tri-State series1.Format.Line.ForeColor.RGB = (int)Microsoft.Office.Interop.Excel.XlRgbColor.rgbBlack; series1.HasErrorBars = true; series1.ErrorBar(XlErrorBarDirection.xlY, XlErrorBarInclude.xlErrorBarIncludePlusValues, XlErrorBarType.xlErrorBarTypeCustom, ErrorMaksimum, ErrorMaksimum); chartPage.ChartType = Excel.XlChartType.xlColumnStacked; }
private void GenerateSummarySheet(Excel.Worksheet summarySheet) { summarySheet.Name = "收益图表汇总"; Excel.Worksheet curSheet = null; Excel.ChartObject chartObj = null; int rowNumOfPage = 43; int sheetCount = _excelEdit._wb.Sheets.Count; //设置summarySheet的打印范围 string startCell = "$A$1"; string endCell = "$P$" + ((sheetCount - 1) * rowNumOfPage).ToString(); summarySheet.PageSetup.PrintArea = startCell + ":" + endCell; //拷贝所有SubjectSheet的合计收益Chart for (int i = 2; i <= sheetCount; i++) //第一个Sheet为汇总Sheet,所以 i 初始值为2 { curSheet = _excelEdit._wb.Sheets[i]; if (curSheet == null) { continue; } //合计收益Chart chartObj = curSheet.ChartObjects(1); if (chartObj == null) { continue; } int startRowIndex = rowNumOfPage * (i - 2) + 2; chartObj.Chart.ChartArea.Copy(); Excel.Range range = summarySheet.Cells[startRowIndex, 2]; summarySheet.Paste(range, Type.Missing); } //设置summarySheet的ChartArea大小 Excel.ChartArea chartArea = null; for (int i = 1; i < sheetCount; i++) { chartObj = summarySheet.ChartObjects(i); chartArea = chartObj.Chart.ChartArea; chartArea.Height = 510; chartArea.Width = 780; } }
private void build_comp_graphs(Excel.Worksheet worksheet, Excel._Worksheet worksheet2, int irows, int orows) { for (int i = 1; i <= irows; i++) { string col1 = String.Format("A{0}", i); string col2 = String.Format("B{0}", i); string col3 = String.Format("C{0}", i); worksheet.Range[col1].Formula = String.Format("='yearly_steps'!{0}", col1); worksheet.Range[col2].Formula = String.Format("='yearly_steps'!{0}", col2); if (i == 4) { worksheet.Range[col3].Formula = String.Format("={0}", col2); } else if (i > 4) { worksheet.Range[col3].Formula = String.Format("=(({0} - {1}) * {2})+{3}", col1, String.Format("A{0}", i - 1), col2, String.Format("C{0}", i - 1)); } } Excel.ChartObjects charts = worksheet.ChartObjects(); Excel.ChartObject chartObject = charts.Add(200, 100, 750, 400); Excel.Chart chart = chartObject.Chart; chart.ChartType = Excel.XlChartType.xlXYScatterLines; Excel.Series series = chartObject.Chart.SeriesCollection().Add(worksheet2.get_Range("B4", String.Format("B{0}", orows))); series.XValues = worksheet2.get_Range("A4", String.Format("A{0}", orows)); series.Name = "Original Data"; series = chartObject.Chart.SeriesCollection().Add(worksheet.Range[String.Format("B4:B{0}", irows)]); series.XValues = worksheet.Range[String.Format("A4:A{0}", irows)]; series.Name = "Interpolated Data"; series.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleDash; chartObject = charts.Add(200, 525, 750, 400); chart = chartObject.Chart; chart.ChartType = Excel.XlChartType.xlXYScatterLines; series = chartObject.Chart.SeriesCollection().Add(worksheet.get_Range("B4", String.Format("B{0}", orows))); series.XValues = worksheet.get_Range("A4", String.Format("A{0}", orows)); series.Name = "Original Data"; series = chartObject.Chart.SeriesCollection().Add(worksheet.Range[String.Format("C4:C{0}", irows)]); series.XValues = worksheet.Range[String.Format("A4:A{0}", irows)]; series.Name = "Interpolated Data"; series.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleDash; }
private void button1_Click(object sender, EventArgs e) { Excel.Application xlApp = new Excel.Application(); Excel.Workbook wb = xlApp.Workbooks.Add(Missing.Value); Excel.Worksheet ws = wb.ActiveSheet; for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { ws.Cells[1, i + 2].Value = dataGridView1.Rows[i].Cells[0].Value.ToString(); } ws.Cells[2, 1].Value = "Amenities Sold"; ws.Cells[3, 1].Value = "Tickets Sold"; ws.Cells[4, 1].Value = "Commission Earned"; for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { ws.Cells[2, i + 2].Value = dataGridView1.Rows[i].Cells[1].Value.ToString(); ws.Cells[3, i + 2].Value = dataGridView1.Rows[i].Cells[2].Value.ToString(); ws.Cells[4, i + 2].Value = dataGridView1.Rows[i].Cells[3].Value.ToString(); } Excel.ChartObjects xlObj = ws.ChartObjects(Missing.Value); Excel.ChartObject obj = xlObj.Add(10, 50, 450, 250); Excel.Chart chart = obj.Chart; Excel.Series s = chart.SeriesCollection().Add(ws.Range[ws.Cells[1, 1], ws.Cells[4, dataGridView1.Rows.Count]]); //s.XValues = ws.Range[ws.Cells[1, 2], ws.Cells[1, dataGridView1.Rows.Count - 1]]; //Excel.Range rangeSourceChart = ws.Range[ws.Cells[1, 1], ws.Cells[4, dataGridView1.Rows.Count - 1]]; //chart.SetSourceData(rangeSourceChart, Excel.XlRowCol.xlRows); using (SaveFileDialog sf = new SaveFileDialog()) { sf.Filter = "Excel Files|*.xlsx"; sf.FileName = "CommissionReport"; if (sf.ShowDialog() == DialogResult.OK) { wb.SaveAs(sf.FileName); wb.Close(); xlApp.Quit(); } } }
private void napraviBtn_Click(object sender, EventArgs e) { Excel.Worksheet ws = Globals.ThisAddIn.Application.ActiveSheet; Excel.Range selektovano = Globals.ThisAddIn.Application.Selection; var chart = ws.ChartObjects().Add(60, 10, 300, 300).Chart; chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered; chart.HasLegend = false; chart.ChartWizard(Source: selektovano, Title: nazivTxt.Text, CategoryTitle: kategorijaTxt.Text, ValueTitle: valueTxt.Text); this.Visible = false; }
//添加图表路径 private void addGraph() { oxml.Path.Add(new OfficeElement("Workbooks", "0")); if (radioSingleChart.Checked == true) { oxml.Path.Add(new OfficeElement("Charts",(comboChart.SelectedIndex + 1).ToString() + ":" + "0")); //该图表是所有图表中的第几个 ch = (Excel.Chart)xls.Sheets[chartIndex[comboChart.SelectedIndex]]; //获取图表 } else { oxml.Path.Add(new OfficeElement("Charts", (comboSheet.SelectedIndex + 1).ToString() //该工作簿是所有工作簿的第几个 + ":" + (comboChartInSheet.SelectedIndex + 1).ToString())); //该图表是工作簿里面的第几个图表 ws = (Excel.Worksheet)xls.Sheets[worksheetIndex[comboSheet.SelectedIndex]]; ch = ((Excel.ChartObject)ws.ChartObjects(comboChartInSheet.SelectedIndex + 1)).Chart; //获取图表 } }
internal static void AddChart( Excel.Worksheet pivotWorkSheet, string myTitle, Excel.Range pivotData, XlChartType type) { var chartObjects = (Excel.ChartObjects)pivotWorkSheet.ChartObjects(); var pivotChart = chartObjects.Add(Left: 60, Top: 250, Width: 325, Height: 275); pivotChart.Chart.ChartWizard(Source: pivotData, Gallery: type, Title: myTitle, HasLegend: true, CategoryLabels: 6, SeriesLabels: 0); pivotChart.Chart.Location(Where: Excel.XlChartLocation.xlLocationAsNewSheet, Name: Type.Missing); }
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); }
private void build_orig_graphs(Excel.Worksheet worksheet, int orows) { Excel.ChartObjects charts = worksheet.ChartObjects(); Excel.ChartObject chartObject = charts.Add(200, 100, 750, 400); Excel.Chart chart = chartObject.Chart; chart.ChartType = Excel.XlChartType.xlXYScatterLines; Excel.Series series = chartObject.Chart.SeriesCollection().Add(worksheet.get_Range("B4", String.Format("B{0}", orows))); series.XValues = worksheet.get_Range("A4", String.Format("A{0}", orows)); series.Name = "Original Data"; chartObject = charts.Add(200, 525, 750, 400); chart = chartObject.Chart; chart.ChartType = Excel.XlChartType.xlXYScatterLines; series = chartObject.Chart.SeriesCollection().Add(worksheet.Range[String.Format("C4:C{0}", orows)]); series.XValues = worksheet.Range[String.Format("A4:A{0}", orows)]; series.Name = "Original Data"; }
/// <summary> /// Обновление Диаграммы /// </summary> internal void UpdateDiagramm() { Excel.ChartObject shp = SheetUrv11.ChartObjects("Chart 2"); Excel.Chart chartPage = shp.Chart; Excel.SeriesCollection seriesCol = (Excel.SeriesCollection)chartPage.SeriesCollection(); Excel.FullSeriesCollection fullColl = chartPage.FullSeriesCollection(); Debug.WriteLine(fullColl.Count); int lastCol = GetLastColumnUrv(SheetUrv11, _rowStart); int lastRow = GetLastRowUrv11(); int ix = 1; string letterCost = "G"; fullColl.Item(ix).Name = $"={SheetUrv11.Name}!${letterCost}10"; fullColl.Item(ix).Values = $"={SheetUrv11.Name}!${letterCost}{_rowStart}:${letterCost}{lastRow}"; fullColl.Item(ix).XValues = $"={SheetUrv11.Name}!$C{_rowStart}:$C{lastRow}"; for (int col = 9; col <= lastCol; col += 3) { Excel.Range cellFirstCost = SheetUrv11.Cells[_rowStart, col]; string text = cellFirstCost.Value?.ToString() ?? ""; if (string.IsNullOrEmpty(text)) { continue; } letterCost = ExcelHelper.GetColumnLetter(cellFirstCost); ix++; if (ix > fullColl.Count) { seriesCol.NewSeries(); } fullColl.Item(ix).Name = $"={SheetUrv11.Name}!${letterCost}10"; fullColl.Item(ix).Values = $"={SheetUrv11.Name}!${letterCost}{_rowStart}:${letterCost}{lastRow}"; fullColl.Item(ix).XValues = $"={SheetUrv11.Name}!$C{_rowStart}:$C{lastRow}"; } if (ix < fullColl.Count) { for (int i = ix + 1; i <= fullColl.Count; i++) { fullColl.Item(i).Delete(); } } }
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); }
/// <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 void getChartInSheet(int index) { comboChartInSheet.Items.Clear(); if (index != 0) { ws = (Excel.Worksheet)xls.Sheets[index]; chobjs = (Excel.ChartObjects)ws.ChartObjects(nullobj); //Console.WriteLine(chobjs.Count); for (int i = 1; i <= chobjs.Count; i++) { chobj = (Excel.ChartObject)ws.ChartObjects(i); ch = chobj.Chart; string str = chobj.Name + ": "; try { string title = ch.ChartTitle.Text; str += title; } catch { str += "(未命名图表)"; } comboChartInSheet.Items.Add(str); } } if (comboChartInSheet.Items.Count == 0) { comboChartInSheet.Items.Add("(该页无图表!)"); buttonGraph.Enabled = false; groupGraphPoint.Enabled = false; } else { buttonGraph.Enabled = true; groupGraphPoint.Enabled = true; } comboChartInSheet.SelectedIndex = 0; }