public void AddChart(Tools.Worksheet worksheet, Excel.Range range, Template template) { var charts = worksheet.Application.ActiveWorkbook.Charts; Tools.Chart chart; if (worksheet.Controls.Contains("chart")) { chart = (Tools.Chart)worksheet.Controls["chart"]; Excel.SeriesCollection sc = (Excel.SeriesCollection)chart.SeriesCollection(); while (sc.Count > 0) { sc.Item(1).Delete(); } } else { chart = worksheet.Controls.AddChart(range, "chart"); } //chart.ChartType = Excel.XlChartType.xlLine; Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(); var series = seriesCollection.NewSeries(); series.Values = GenerateTestData(this.random, 100); //series.XValues = new string[] { "A", "B", "C", "D" }; series.Name = "Series Name"; chart.ApplyChartTemplate(TemplateDictionary[template]); }
void elementChart_MouseDown(int Button, int Shift, int x, int y) { if (flag == 1) { Excel.Chart chart = Globals.ThisAddIn.Application.ActiveChart; //Int32 ; Int32 elementID = 0; Int32 arg1 = 0; Int32 arg2 = 0; chart.GetChartElement(x, y, ref elementID, ref arg1, ref arg2); // string element = ((Excel.XlChartItem)elementID).ToString(); if (element == "xlSeries") { Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries = series.Item(arg1); Sseries.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(SelectRGB[2], SelectRGB[1], SelectRGB[0]).ToArgb(); Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(SelectRGB[2], SelectRGB[1], SelectRGB[0]).ToArgb(); flag = 0; } } // MessageBox.Show("Chart element is: " + ((Excel.XlChartItem)elementID).ToString() // + "\n arg1 is: " + arg1.ToString() + "\n arg2 is: " + arg2.ToString()); }
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( )); }
public void ExcelDiagr2() { Excel.Application excelapp = new Excel.Application(); try { // excelapp.Visible = true; Excel.Workbooks excelappworkbooks = excelapp.Workbooks; Excel.Workbook excelappworkbook = excelapp.Workbooks.Open("book.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Excel.Sheets excelsheets = excelappworkbook.Worksheets; //excelappworkbook = excelappworkbooks[2]; //excelappworkbook.Activate(); //excelsheets = excelapp.ActiveWorkbook.Worksheets; Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1); excelworksheet.Activate(); //Excel.Worksheet excelworksheet = (Excel.Worksheet)excelappworkbook.Sheets[2]; Excel.Range excelcells = excelworksheet.get_Range("B1", "B12"); excelcells.Select(); Excel.Chart excelchart = (Excel.Chart)excelapp.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); excelapp.ActiveChart.HasTitle = true; excelapp.ActiveChart.ChartTitle.Text = "Количество заказов"; DataForBD.iter = 5; ((Excel.Axis)excelapp.ActiveChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary)).HasTitle = true; ((Excel.Axis)excelapp.ActiveChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary)).AxisTitle.Text = "Месяц"; ((Excel.Axis)excelapp.ActiveChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary)).HasTitle = true; ((Excel.Axis)excelapp.ActiveChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary)).AxisTitle.Text = "Количество"; Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)excelapp.ActiveChart.SeriesCollection(Type.Missing); Excel.Series series = seriesCollection.Item(1); series.Name = "Количество заказов"; //excelworksheet.Name = "Количество заказов"; excelapp.DisplayAlerts = false; excelappworkbook.SaveAs("book.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); excelapp.Quit(); } catch (Exception exc) { excelapp.Quit(); throw exc; } }
/// <summary> /// 开始生成整个绘图图表 /// </summary> /// <param name="SelectedRegion">窗口中所有选择的要进行绘图的基坑区域</param> /// <remarks></remarks> private void GenerateChart(List <clsData_ProcessRegionData> SelectedRegion) { //列表中选择的基坑区域 int count = System.Convert.ToInt32(SelectedRegion.Count); if (count > 0) { //---------------- 打开用于绘图的Excel程序,并进行界面设计 Microsoft.Office.Interop.Excel.Worksheet DrawingSheet = this.GetDrawingSheet(); Microsoft.Office.Interop.Excel.Application DrawingApp = DrawingSheet.Application; try { //------------------- 在绘图工作表中进行绘图 this.F_DrawingChart = DrawChart(DrawingSheet, SelectedRegion); // ----------- 绘制数据系列图 --------------------------- Microsoft.Office.Interop.Excel.SeriesCollection src = this.F_DrawingChart.SeriesCollection(); Series series_DeepestExca = src.Item(1); Series series_Depth = src.Item(2); Series[] DataSeries = new Series[2]; DataSeries = SetDataSeries(this.F_DrawingChart, series_DeepestExca, series_Depth, SelectedRegion); //------------------------------------------------------- DateSpan date_Span = GetDateSpan(SelectedRegion); //------------------------------------------------------- ClsDrawing_ExcavationElevation shtEle = new ClsDrawing_ExcavationElevation(series_DeepestExca, series_Depth, SelectedRegion, date_Span, this.F_Textbox_Info, DrawingType.Xls_SectionalView); } catch (Exception ex) { MessageBox.Show("绘制基坑区域开挖标高图失败!" + "\r\n" + ex.Message + "\r\n" + "报错位置:" + ex.TargetSite.Name, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { //------- Excel的界面美化 -------------------- ExcelAppBeauty(DrawingApp); } } }
private void button1_Click(object sender, EventArgs e) { colorDialog1.ShowDialog(); RGB0 = colorDialog1.Color; button_ColorSelection.BackColor = RGB0; button_ColorSelection.ForeColor = RGB0; Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Point point; Excel.Series Sseries; int SquareSize; //RGB0 = System.Drawing.Color.FromArgb(255, 109, 118, 248); for (int i = 1; i < rows; i++) { Sseries = series.Item(i); Sseries.Format.Line.Visible = Office.MsoTriState.msoFalse; Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle; Sseries.MarkerBackgroundColor = System.Drawing.Color.FromArgb(255, RGB0.B, RGB0.G, RGB0.R).ToArgb(); Sseries.MarkerForegroundColor = System.Drawing.Color.FromArgb(255, RGB0.B, RGB0.G, RGB0.R).ToArgb(); Sseries.Format.Fill.Solid(); Sseries.Format.Fill.Visible = Office.MsoTriState.msoCTrue; Sseries.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb(); Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB0.B, RGB0.G, RGB0.R).ToArgb(); Sseries.Format.Fill.Transparency = 0.2F; for (int j = 1; j < cols; j++) { point = (Excel.Point)Sseries.Points(j); //SquareSize = (int)(double.Parse(str[i, j]) / Max_size * ratio) + 2; SquareSize = (int)((Math.Sqrt(double.Parse(str[i, j]) * ratio) / Max_size) * Min_MarkerSize + 2); //SquareSize = (int)((double.Parse(str[i, j]) / Max_size * ratio) + 2);// - Min_size) / (Max_size - Min_size) * ratio) + 2; if (SquareSize > 72) { SquareSize = 72; } point.MarkerSize = SquareSize; } } chart.Refresh(); worksheet.Activate(); }
private void textBox_Bandwidth_TextChanged(object sender, EventArgs e) { if (textBox_Bandwidth.Text == "") { return; } Hrange = double.Parse(textBox_Bandwidth.Text); if (Hrange == 0) { return; } Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries = series.Item(1); Excel.Point point; System.Drawing.Color RGB1 = System.Drawing.Color.FromArgb(255, 96, 157, 202); int[] HSV0 = new int[3]; Graphic.RGB2HSV(RGB0, ref HSV0); int[] HSV = new int[3]; HSV0.CopyTo(HSV, 0); double ratio; for (int i = 1; i < rows; i++) { point = (Excel.Point)Sseries.Points(i); point.Format.Fill.Solid(); point.Format.Fill.Visible = Office.MsoTriState.msoCTrue; point.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 229, 229, 229).ToArgb(); ratio = (double.Parse(str[i, 2]) - Min_Value) / (Max_Value - Min_Value); HSV[2] = HSV0[2] + Convert.ToInt32(Hrange * (ratio - 0.5)); Graphic.HSV2RGB(ref RGB1, HSV); point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB1.B, RGB1.G, RGB1.R).ToArgb(); point.Format.Fill.Transparency = 0.0F; } chart.Refresh(); worksheet.Activate(); }
private void textBox_Bandwidth_TextChanged(object sender, EventArgs e) { if (textBox_Bandwidth.Text == "") { return; } ratio = double.Parse(textBox_Bandwidth.Text);; if (ratio == 0) { return; } Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries = series.Item(1); Excel.Point point; int SquareSize; for (int i = 1; i < rows; i++) { point = (Excel.Point)Sseries.Points(i); //SquareSize = (int)((Math.Sqrt(double.Parse(str[i, 2]))));// - Min_size) / (Max_size - Min_size) * ratio) + 2; SquareSize = (int)((Math.Sqrt(double.Parse(str[i, 2])) / Max_size * ratio) + 2); //point = (Excel.Point)Sseries.Points(i); //SquareSize = (int)((double.Parse(str[i, 2]) - Min_size) / (Max_size - Min_size) * ratio) + 2; //PointSize[i - 1, 0] = SquareSize; //((Excel.Range)worksheet.Cells[Graphic.start_row + i, Graphic.start_col + 3]).Value2 = SquareSize; point.MarkerSize = SquareSize; } Sseries.MarkerBackgroundColor = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); Sseries.MarkerForegroundColor = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); Sseries.Format.Fill.Solid(); Sseries.Format.Fill.Visible = Office.MsoTriState.msoCTrue; Sseries.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 229, 229, 229).ToArgb(); Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); Sseries.Format.Fill.Transparency = 0.3F; chart.Refresh(); worksheet.Activate(); }
public DensityScatter() { InitializeComponent(); //hScrollBar_Parameter1.Value = 2; //Graphics Graphic = new Graphics(); Bandwidth = double.Parse(textBox_Bandwidth.Text); //if (Form_Parater.flag == 0) return; //int rows = 1; //int cols = 1; //string[,] str = new string[1, 1]; Graphic.RangeData(ref str, ref rows, ref cols); worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell; start_col = activecells.Column; start_row = activecells.Row; int i; ((Excel.Range)worksheet.Cells[start_row, start_col + cols]).Value2 = "X-axis Value"; ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]).Value2 = "Y-axis Value"; double Pdensity = Bandwidth * 100; double Maxtemp = 1000; double[,] data = new double[rows, 2]; for (i = 1; i < rows; i++) { data[i - 1, 0] = Math.Floor(double.Parse(str[i, 0]) * Maxtemp / Pdensity) * Pdensity / Maxtemp; data[i - 1, 1] = Math.Floor(double.Parse(str[i, 1]) * Maxtemp / Pdensity) * Pdensity / Maxtemp; //((Excel.Range)worksheet.Cells[start_row + i, start_col + cols]).Value2 = Math.Floor(double.Parse(str[i, 0]) * Maxtemp / Pdensity) * Pdensity / Maxtemp; //((Excel.Range)worksheet.Cells[start_row + i, start_col + cols + 1]).Value2 = Math.Floor(double.Parse(str[i, 1]) * Maxtemp / Pdensity) * Pdensity / Maxtemp; } Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols]; Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols + cols - 1]; Excel.Range range = worksheet.get_Range(c1, c2); range.Value = data; string ChartOrder = "DensityScatter" + Convert.ToString(Nchart); chart = worksheet.Controls.AddChart(250, 50, 450, 400, ChartOrder); Nchart = Nchart + 1; c1 = (Excel.Range)worksheet.Cells[start_row, start_col + cols]; c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols + cols - 1]; chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns); chart.ChartType = Excel.XlChartType.xlXYScatter; Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); if (series.Count == 2) { Excel.Series Sseries2 = series.Item(2); Sseries2.Delete(); } Excel.Series Sseries = series.Item(1); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols]; c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols]; Sseries.XValues = worksheet.get_Range(c1, c2); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1]; c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols + 1]; Sseries.Values = worksheet.get_Range(c1, c2); Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle; //Sseries.Format.Line.Visible = Office.MsoTriState.msoFalse; Sseries.MarkerSize = 5; Sseries.MarkerBackgroundColor = System.Drawing.Color.FromArgb(255, 109, 118, 248).ToArgb(); Sseries.MarkerForegroundColor = System.Drawing.Color.FromArgb(255, 109, 118, 248).ToArgb(); //Sseries.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse; Sseries.Format.Fill.Solid(); Sseries.Format.Fill.Visible = Office.MsoTriState.msoCTrue; Sseries.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb(); Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 109, 118, 248).ToArgb(); Sseries.Format.Fill.Transparency = 0.9F; worksheet.Activate(); }
private void buildXlsChart(Xls.Chart chart, Xls.Worksheet dataSheet, ChartInfo graph, int currentDataColumn, out int dataColumnsWritten) { chart.ChartTitle.Text = string.Format(Strings.ChartTitle, graph.PrimaryTitle, graph.SecondaryTitle); dataColumnsWritten = 0; double[,] xValues = null, yValues = null; int seriesCounter = 0; foreach (var s in graph.Series) { if (seriesCounter == 0) { dataSheet.Cells[2, currentDataColumn].Value = s.XUnitName; xValues = ConvertToTwoDimensionalArray(s.XValues); dataSheet.Range[dataSheet.Cells[3, currentDataColumn], dataSheet.Cells[3 + xValues.Length - 1, currentDataColumn]].Value2 = xValues; dataColumnsWritten += 1; } dataSheet.Cells[1, currentDataColumn + dataColumnsWritten].Value = s.Name; dataSheet.Cells[2, currentDataColumn + dataColumnsWritten].Value = s.YUnitName; yValues = ConvertToTwoDimensionalArray(s.YValues); dataSheet.Range[dataSheet.Cells[3, currentDataColumn + dataColumnsWritten], dataSheet.Cells[3 + yValues.Length - 1, currentDataColumn + dataColumnsWritten]].Value2 = yValues; dataColumnsWritten += 1; seriesCounter++; } if (seriesCounter > 0) { chart.SetSourceData(Source: dataSheet.Range[dataSheet.Cells[3, currentDataColumn], dataSheet.Cells[3 + xValues.Length - 1, currentDataColumn + dataColumnsWritten - 1]]); Xls.SeriesCollection collection = chart.SeriesCollection(); Xls.Axes axes = null; try { for (int i = 0; i < collection.Count; i++) { collection.Item(i + 1).Name = graph.Series.ElementAt(i).Name; if (graph.Series.ElementAt(i).ShowInSecondaryVerticalAxis&& graph.HasSecondaryVerticalAxis) { collection.Item(i + 1).AxisGroup = Xls.XlAxisGroup.xlSecondary; } } axes = chart.Axes(); axes.Item(Xls.XlAxisType.xlCategory, Xls.XlAxisGroup.xlPrimary).AxisTitle.Text = graph.GetHorizontalAxisTitle(); axes.Item(Xls.XlAxisType.xlValue, Xls.XlAxisGroup.xlPrimary).AxisTitle.Text = graph.GetPrimaryVerticalAxisTitle(); if (graph.HasSecondaryVerticalAxis) { axes.Item(Xls.XlAxisType.xlValue, Xls.XlAxisGroup.xlSecondary).HasTitle = true; axes.Item(Xls.XlAxisType.xlValue, Xls.XlAxisGroup.xlSecondary).AxisTitle.Text = graph.GetSecondaryVerticalAxisTitle(); } } finally { if (axes != null) { Marshal.FinalReleaseComObject(axes); } Marshal.FinalReleaseComObject(collection); } } }
public ColumnColor() { InitializeComponent(); Graphic.RangeData(ref str, ref rows, ref cols); worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell; start_col = activecells.Column; start_row = activecells.Row; Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row, start_col]; Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols - 2]; string ChartOrder = "ColorColumn" + Convert.ToString(Nchart); chart = worksheet.Controls.AddChart(250, 50, 450, 400, ChartOrder); Nchart = Nchart + 1; chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns); chart.ChartType = Excel.XlChartType.xlColumnClustered; Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); if (series.Count == 2) { Excel.Series Sseries2 = series.Item(2); Sseries2.Delete(); } Excel.Series Sseries = series.Item(1); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col]; c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col]; Sseries.XValues = worksheet.get_Range(c1, c2); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + 1]; c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + 1]; Sseries.Values = worksheet.get_Range(c1, c2); Excel.Point point; int[] HSV0 = new int[3]; RGB0 = System.Drawing.Color.FromArgb(255, 96, 157, 202); System.Drawing.Color RGB1 = System.Drawing.Color.FromArgb(255, 96, 157, 202); Graphic.RGB2HSV(RGB0, ref HSV0); Max_Value = Double.MinValue; Min_Value = Double.MaxValue; for (int i = 1; i < rows; i++) { if (double.Parse(str[i, 2]) > Max_Value) { Max_Value = double.Parse(str[i, 1]); } if (double.Parse(str[i, 2]) < Min_Value) { Min_Value = double.Parse(str[i, 1]); } } int[] HSV = new int[3]; HSV0.CopyTo(HSV, 0); double ratio; Hrange = double.Parse(textBox_Bandwidth.Text); for (int i = 1; i < rows; i++) { point = (Excel.Point)Sseries.Points(i); point.Format.Fill.Solid(); point.Format.Fill.Visible = Office.MsoTriState.msoCTrue; point.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 229, 229, 229).ToArgb(); ratio = (double.Parse(str[i, 2]) - Min_Value) / (Max_Value - Min_Value); HSV[2] = HSV0[2] + Convert.ToInt32(Hrange * (ratio - 0.5)); Graphic.HSV2RGB(ref RGB1, HSV); point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB1.B, RGB1.G, RGB1.R).ToArgb(); point.Format.Fill.Transparency = 0.0F; } //Sseries.MarkerBackgroundColor = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); //Sseries.MarkerForegroundColor = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); Sseries.Format.Line.Visible = Office.MsoTriState.msoCTrue; Sseries.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb(); Sseries.Format.Line.Weight = 0.75F; Excel.ChartGroup group = (Excel.ChartGroup)chart.ChartGroups(1); group.GapWidth = 0; chart.HasLegend = false; chart.HasTitle = false; //chart.ChartTitle.Delete(); worksheet.Activate(); }
private void textBox_Bandwidth_TextChanged(object sender, EventArgs e) { if (textBox_Bandwidth.Text == "") { return; } Bandwidth = double.Parse(textBox_Bandwidth.Text);; if (Bandwidth == 0) { return; } double Step = Bandwidth / Nstep; int Nrows = Convert.ToInt32((Max_Value - Min_Value) / Step + 2); double[,] data = new double[Nrows, 1]; double[,] x = new double[Nrows, 1]; double tempx; for (int i = 0; i < Nrows; i++) { x[i, 0] = i * Step + Min_Value; for (int j = 1; j < rows; j++) { tempx = (x[i, 0] - double.Parse(str[j, 0])) / Bandwidth; data[i, 0] = data[i, 0] + 1 / ((rows - 1) * Bandwidth) * Math.Exp(-tempx * tempx / 2) / Math.Sqrt(2 * 3.124259); } } ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]).Value2 = "X axis"; ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]).Value2 = "Y axis"; Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1]; Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 1]; Excel.Range range = worksheet.get_Range(c1, c2); range.Value = x; c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 2]; range = worksheet.get_Range(c1, c2); range.Value = data; c1 = (Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 2]; chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns); //chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlArea; Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries2 = series.Item(2); Sseries2.Delete(); Excel.Series Sseries = series.Item(1); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 1]; Sseries.XValues = worksheet.get_Range(c1, c2); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 2]; Sseries.Values = worksheet.get_Range(c1, c2); chart.Refresh(); worksheet.Activate(); }
public Form_Color_Matrix() { InitializeComponent(); height = int.Parse(textBox_height.Text); Graphic.RangeData(ref str, ref rows, ref cols); worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell; start_col = activecells.Column; start_row = activecells.Row; int width = (int)Math.Ceiling(Convert.ToDouble(rows) / Convert.ToDouble(height)); int[,] data = new int[height, width]; int[] temp = new int[width]; // for (int i = 0; i < height; i++) // { //temp = new int[width]; // for (int j = 0; j < width; j++) // { // // temp[j] = 1; // data[i, j] = 1; // ((range)worksheet.Cells[start_row + rows + 1 + i , start_col + j ]).Value2 = 1; // } //Sseries = series.NewSeries(); //Sseries.Values = temp; // } range c1 = (range)worksheet.Cells[start_row + rows + 1, start_col]; range c2 = (range)worksheet.Cells[start_row + rows + 1 + height - 1, start_col + width - 1]; string ChartOrder = "chart" + Convert.ToString(Nchart); chart = worksheet.Controls.AddChart(250, 50, 450, 400, ChartOrder); Nchart = Nchart + 1; //chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns); chart.ChartType = Excel.XlChartType.xlColumnStacked100; Excel.ChartGroup group = (Excel.ChartGroup)chart.ChartGroups(1); group.GapWidth = 0; Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries; Excel.Point point; int RGB_B, RGB_G, RGB_R; for (int i = 0; i < height; i++) { temp = new int[width]; for (int j = 0; j < width; j++) { temp[j] = 1; data[i, j] = 1; } Sseries = series.NewSeries(); Sseries.Values = temp; } for (int i = 1; i <= height; i++) { Sseries = series.Item(i); for (int j = 1; j <= width; j++) { { point = (Excel.Point)Sseries.Points(j); point.Format.Fill.Solid(); point.Format.Fill.Visible = Office.MsoTriState.msoCTrue; point.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 229, 229, 229).ToArgb(); int index = (i - 1) * width + j; if (index < rows) { RGB_B = Convert.ToInt32(double.Parse(str[index, cols - 1])); if (RGB_B < 0) { RGB_B = 0; } if (RGB_B > 255) { RGB_B = 255; } RGB_G = Convert.ToInt32(double.Parse(str[index, cols - 2])); if (RGB_G < 0) { RGB_G = 0; } if (RGB_G > 255) { RGB_G = 255; } RGB_R = Convert.ToInt32(double.Parse(str[index, cols - 3])); if (RGB_R < 0) { RGB_R = 0; } if (RGB_R > 255) { RGB_R = 255; } point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB_B, RGB_G, RGB_R).ToArgb(); point.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 0, 0, 0).ToArgb(); } else { point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb(); point.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb(); } } } } }
// データテーブルをエクセルへエクスポート(XR管理図・ヒストグラム付き) public void ExportToExcelWithXrChart(DataTable dt) { if (dt.Rows.Count == 0) { return; } 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); // column headings for (int i = 0; i < dt.Columns.Count; i++) { xlWorkSheet.Cells[1, (i + 1)] = dt.Columns[i].ColumnName; } // rows for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { xlWorkSheet.Cells[(i + 2), (j + 1)] = dt.Rows[i][j]; } } int row = xlWorkSheet.UsedRange.Rows.Count; string address1 = "B1:B" + row.ToString() + ",K1:K" + row.ToString() + ",M1:N" + row.ToString(); string address2 = "B1:B" + row.ToString() + ",L1:L" + row.ToString(); string address3 = "AB41:AC53"; string address4 = "F1:J" + row.ToString(); // チャート1(X-R管理図1) Excel.Range chartRange1; Excel.ChartObjects xlCharts1 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart1 = (Excel.ChartObject)xlCharts1.Add(800, 10, 600, 250); Excel.Chart chartPage1 = myChart1.Chart; chartRange1 = xlWorkSheet.get_Range(address1); chartPage1.SetSourceData(chartRange1, misValue); chartPage1.ChartType = Excel.XlChartType.xlLine; chartPage1.HasLegend = false; chartPage1.HasTitle = true; chartPage1.ChartTitle.Text = "X " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString(); Excel.Axis xAxis1 = (Excel.Axis)chartPage1.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); xAxis1.CategoryType = Excel.XlCategoryType.xlCategoryScale; Excel.SeriesCollection SeriesCollection1 = (Excel.SeriesCollection)myChart1.Chart.SeriesCollection(misValue); Excel.Series s2 = SeriesCollection1.Item(2); s2.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbCoral; Excel.Series s3 = SeriesCollection1.Item(3); s3.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbCoral; // チャート2(X-R管理図2) Excel.Range chartRange2; Excel.ChartObjects xlCharts2 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart2 = (Excel.ChartObject)xlCharts1.Add(800, 280, 600, 250); Excel.Chart chartPage2 = myChart2.Chart; chartRange2 = xlWorkSheet.get_Range(address2); chartPage2.SetSourceData(chartRange2, misValue); chartPage2.ChartType = Excel.XlChartType.xlLine; chartPage2.HasLegend = false; chartPage2.HasTitle = true; chartPage2.ChartTitle.Text = "R " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString(); Excel.Axis xAxis2 = (Excel.Axis)chartPage2.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); xAxis2.CategoryType = Excel.XlCategoryType.xlCategoryScale; // チャート3(ヒストグラム) Excel.Range chartRange3; Excel.ChartObjects xlCharts3 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart3 = (Excel.ChartObject)xlCharts1.Add(800, 550, 350, 250); Excel.Chart chartPage3 = myChart3.Chart; string[,] formulas = new string[13, 3]; string[] formula1 = new string[] { "BIN", "=MIN(" + address4 + ")", "=AA42+(AA$53-AA$42)/10", "=AA43+(AA$53-AA$42)/10", "=AA44+(AA$53-AA$42)/10", "=AA45+(AA$53-AA$42)/10", "=AA46+(AA$53-AA$42)/10", "=AA47+(AA$53-AA$42)/10", "=AA48+(AA$53-AA$42)/10", "=AA49+(AA$53-AA$42)/10", "=AA50+(AA$53-AA$42)/10", "=AA51+(AA$53-AA$42)/10", "=MAX(" + address4 + ")", }; string[] formula2 = new string[] { @"LABEL", @"=TEXT(AA42,""0.0"")", @"=TEXT(AA42,""0.0"")&"" - ""&TEXT(AA43,""0.0"")", @"=TEXT(AA43,""0.0"")&"" - ""&TEXT(AA44,""0.0"")", @"=TEXT(AA44,""0.0"")&"" - ""&TEXT(AA45,""0.0"")", @"=TEXT(AA45,""0.0"")&"" - ""&TEXT(AA46,""0.0"")", @"=TEXT(AA46,""0.0"")&"" - ""&TEXT(AA47,""0.0"")", @"=TEXT(AA47,""0.0"")&"" - ""&TEXT(AA48,""0.0"")", @"=TEXT(AA48,""0.0"")&"" - ""&TEXT(AA49,""0.0"")", @"=TEXT(AA49,""0.0"")&"" - ""&TEXT(AA50,""0.0"")", @"=TEXT(AA50,""0.0"")&"" - ""&TEXT(AA51,""0.0"")", @"=TEXT(AA51,""0.0"")&"" - ""&TEXT(AA52,""0.0"")", @"=TEXT(AA53,""0.0"")" }; string[] formula3 = new string[] { @"FREQUENCY", @"=COUNTIF(" + address4 + @",""<=""&AA42)", @"=COUNTIF(" + address4 + @","">""&AA42)-COUNTIF(" + address4 + @","">""&AA43)", @"=COUNTIF(" + address4 + @","">""&AA43)-COUNTIF(" + address4 + @","">""&AA44)", @"=COUNTIF(" + address4 + @","">""&AA44)-COUNTIF(" + address4 + @","">""&AA45)", @"=COUNTIF(" + address4 + @","">""&AA45)-COUNTIF(" + address4 + @","">""&AA46)", @"=COUNTIF(" + address4 + @","">""&AA46)-COUNTIF(" + address4 + @","">""&AA47)", @"=COUNTIF(" + address4 + @","">""&AA47)-COUNTIF(" + address4 + @","">""&AA48)", @"=COUNTIF(" + address4 + @","">""&AA48)-COUNTIF(" + address4 + @","">""&AA49)", @"=COUNTIF(" + address4 + @","">""&AA49)-COUNTIF(" + address4 + @","">""&AA50)", @"=COUNTIF(" + address4 + @","">""&AA50)-COUNTIF(" + address4 + @","">""&AA51)", @"=COUNTIF(" + address4 + @","">""&AA51)-COUNTIF(" + address4 + @","">=""&AA52)", @"=COUNTIF(" + address4 + @","">=""&AA53)" }; for (int i = 0; i < 13; i++) { xlWorkSheet.Cells[41 + i, 27].Formula = formula1[i]; xlWorkSheet.Cells[41 + i, 28].Formula = formula2[i]; xlWorkSheet.Cells[41 + i, 29].Formula = formula3[i]; } chartRange3 = xlWorkSheet.get_Range(address3); chartPage3.SetSourceData(chartRange3, misValue); chartPage3.ChartType = Excel.XlChartType.xlColumnClustered; chartPage3.HasLegend = false; chartPage3.HasTitle = true; chartPage3.ChartTitle.Text = "Frequency " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString(); Excel.ChartGroup ChartGroup1 = (Excel.ChartGroup)myChart3.Chart.ChartGroups(1); ChartGroup1.GapWidth = 0; xlApp.Visible = true; }
public CurveLOESS() { InitializeComponent(); //int rows = 1; //int cols = 1; //string[,] str = new string[1, 1]; Graphic.RangeData(ref str, ref rows, ref cols); worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell; start_col = activecells.Column; start_row = activecells.Row; Y = new double[rows - 1]; X = new double[rows - 1]; int j; for (j = 1; j < rows; j++) { X[j - 1] = double.Parse(str[j, 0]); Y[j - 1] = double.Parse(str[j, 1]); } Span = long.Parse(textBox_Bandwidth.Text); double[] yLoess = new double[rows - 1]; QLOESS(ref Y, ref X, ref yLoess, Span); double[,] data = new double[rows - 1, 1]; for (j = 1; j < rows; j++) { data[j - 1, 0] = yLoess[j - 1]; } ((Excel.Range)worksheet.Cells[start_row, start_col + cols]).Value2 = "Smooth Y"; Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols]; Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + 1 + rows - 2, start_col + cols]; Excel.Range range = worksheet.get_Range(c1, c2); range.Value = data; c1 = (Excel.Range)worksheet.Cells[start_row, start_col]; c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols]; string ChartOrder = "CurveLOESS" + Convert.ToString(Nchart); chart = worksheet.Controls.AddChart(250, 50, 450, 400, ChartOrder); Nchart = Nchart + 1; chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns); chart.ChartType = Excel.XlChartType.xlXYScatter; Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries2 = series.Item(2); Sseries2.Format.Line.Visible = Office.MsoTriState.msoFalse; Sseries2.Format.Fill.Solid(); Sseries2.Format.Fill.Visible = Office.MsoTriState.msoCTrue; //Sseries2.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb(); //Sseries2.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255,229, 184,0).ToArgb(); //Sseries2.Format.Fill.Transparency = 0.6F; Sseries2.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle; Sseries2.MarkerSize = 6; Excel.Series Sseries3 = series.Item(3); Sseries3.Format.Fill.Visible = Office.MsoTriState.msoFalse; Sseries3.Format.Line.Visible = Office.MsoTriState.msoTrue; Sseries3.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone; //Sseries3.Format.Line.ForeColor.RGB= System.Drawing.Color.FromArgb(255, 248, 118, 109).ToArgb(); Sseries3.Format.Line.Weight = 1.25F; Excel.Series Sseries1 = series.Item(1); Sseries1.Delete(); }
public ColumnFrequency() { InitializeComponent(); //hScrollBar_Parameter1.Value = 2; //Graphics Graphic = new Graphics(); Bandwidth = double.Parse(textBox_Bandwidth.Text); //if (Form_Parater.flag == 0) return; //int rows = 1; //int cols = 1; //string[,] str = new string[1, 1]; Graphic.RangeData(ref str, ref rows, ref cols); worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell; start_col = activecells.Column; start_row = activecells.Row; Max_Value = Double.MinValue; Min_Value = Double.MaxValue; for (int i = 1; i < rows; i++) { if (double.Parse(str[i, 0]) > Max_Value) { Max_Value = double.Parse(str[i, 0]); } if (double.Parse(str[i, 0]) < Min_Value) { Min_Value = double.Parse(str[i, 0]); } } int Nrows = Convert.ToInt32((Max_Value - Min_Value) / Bandwidth + 2); double[,] data = new double[Nrows, 1]; int idx = 0; for (int i = 1; i < rows; i++) { idx = Convert.ToInt32((double.Parse(str[i, 0]) - Min_Value) / Bandwidth); data[idx, 0] = data[idx, 0] + 1; } data[Nrows - 2, 0] = data[Nrows - 2, 0] + data[Nrows - 1, 0]; double[,] x = new double[Nrows - 1, 1]; string[,] label = new string[Nrows - 1, 1]; for (int i = 0; i < Nrows - 1; i++) { label[i, 0] = "[" + Convert.ToString(Math.Floor((i * Bandwidth + Min_Value) * 100) / 100) + "," + Convert.ToString(Math.Floor(((i + 1) * Bandwidth + Min_Value) * 100) / 100) + ")"; //((Excel.Range)worksheet.Cells[start_row + 1 + i, start_col + cols + 1]).Value2 = label[i]; //((Excel.Range)worksheet.Cells[start_row + 1 + i, start_col + cols + 3]).Value2 = data[i]; x[i, 0] = i * Bandwidth + Min_Value; } ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]).Value2 = "X bandwidth"; ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]).Value2 = "X axis"; ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 3]).Value2 = "Y axis"; Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1]; Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 1]; Excel.Range range = worksheet.get_Range(c1, c2); range.Value = label; c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 2]; range = worksheet.get_Range(c1, c2); range.Value = x; c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 3]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 3]; range = worksheet.get_Range(c1, c2); range.Value = data; ((Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 3]).Value2 = "";; string ChartOrder = "FrequencyColumn" + Convert.ToString(Nchart); chart = worksheet.Controls.AddChart(300, 50, 450, 400, ChartOrder); Nchart = Nchart + 1; c1 = (Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 3]; chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns); chart.ChartType = Excel.XlChartType.xlColumnClustered; Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); if (series.Count == 2) { Excel.Series Sseries2 = series.Item(2); Sseries2.Delete(); } Excel.Series Sseries = series.Item(1); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 2]; Sseries.XValues = worksheet.get_Range(c1, c2); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 3]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 3]; Sseries.Values = worksheet.get_Range(c1, c2); Sseries.Format.Fill.Visible = Office.MsoTriState.msoTrue; Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 88, 180, 13).ToArgb(); Sseries.Format.Line.Visible = Office.MsoTriState.msoTrue; Sseries.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 0, 0, 0).ToArgb(); Excel.ChartGroup group = (Excel.ChartGroup)chart.ChartGroups(1); group.GapWidth = 0; chart.HasLegend = false; chart.HasTitle = false; worksheet.Activate(); }
/// <summary> /// Draws Chart or Graph for given component. /// </summary> /// <param name="componentName"></param> /// <param name="oSummarySheet"></param> /// <param name="webAppUrlColumn"></param> /// <param name="componentColumnCount"></param> /// <param name="counter"></param> /// <param name="row"></param> /// <param name="chartType"></param> /// <param name="chartWidth"></param> /// <param name="chartHeight"></param> /// <param name="chartStyle"></param> /// <param name="CellIndex"></param> public static void DrawGraph(string componentName, Excel.Worksheet oSummarySheet, string webAppUrlColumn, int componentColumnCount, int counter, int row, string chartType, int chartWidth, int chartHeight, int chartStyle, char CellIndex) { //Create chart object Excel.Shape _Shape = oSummarySheet.Shapes.AddChart2(); //Specify type of chart if (chartType.Equals("pie")) { _Shape.Chart.ChartType = Excel.XlChartType.xlPie; } else if (chartType.Equals("3dpie")) { _Shape.Chart.ChartType = Excel.XlChartType.xl3DPie; } else if (chartType.Equals("line")) { _Shape.Chart.ChartType = Excel.XlChartType.xlLine; } else if (chartType.Equals("3dline")) { _Shape.Chart.ChartType = Excel.XlChartType.xl3DLine; } else if (chartType.Equals("3dcolumn")) { _Shape.Chart.ChartType = Excel.XlChartType.xl3DColumn; } else if (chartType.Equals("clusteredcolumn")) { _Shape.Chart.ChartType = Excel.XlChartType.xlColumnClustered; } else if (chartType.Equals("3dclusteredcolumn")) { _Shape.Chart.ChartType = Excel.XlChartType.xl3DColumnClustered; } //Series object for the graph Excel.Series series = null; string exceptionComment = "[DrawGraph] Processing for Component :" + componentName; Logger.LogInfoMessage(String.Format("[GeneratePivotReports][DrawGraph] Processing Started for (" + componentName + ")"), false); try { //Get Series Column from SummaryView table string componentColumn = ((Char)(Convert.ToUInt16(CellIndex) + componentColumnCount)).ToString(); //Set Series column for the Graph series = _Shape.Chart.SeriesCollection().Add(oSummarySheet.Range[componentColumn + row + ":" + componentColumn + (counter).ToString()]); //Set Categories column for the graph series.XValues = oSummarySheet.Range[webAppUrlColumn + (row + 1).ToString() + ":" + webAppUrlColumn + (counter).ToString()]; //Apply data labels for the graph _Shape.Chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes); //Apply legend for the graph _Shape.Chart.HasLegend = true; //apply style to chart _Shape.Chart.ChartStyle = chartStyle; //Hide Display Labels when their value is zero (0) Excel.SeriesCollection oSeriesCollection = (Excel.SeriesCollection)_Shape.Chart.SeriesCollection(Type.Missing); for (int j = 1; j <= oSeriesCollection.Count; j++) { Excel.Series oSeries = (Excel.Series)oSeriesCollection.Item(j); System.Array Values = (System.Array)((object)oSeries.Values); //Array Values = (Array)oSeries.Values; for (int k = 1; k <= Values.Length; k++) { Excel.DataLabel oDataLabel = (Excel.DataLabel)oSeries.DataLabels(k); string caption = oDataLabel.Caption.ToString(); if (caption.Equals("0")) { oDataLabel.ShowValue = false; } } } //Set the Size of the Chart _Shape.Width = chartWidth; _Shape.Height = chartHeight; //Calculations for the position of Chart int columnIndex = counter + 3; if (componentColumnCount > 3 && (componentColumnCount / 3 > 0)) { columnIndex = (counter + 3) + (((componentColumnCount - 1) / 3) * 16); } string charPositionColumn = webAppUrlColumn; if (componentColumnCount % 3 == 2) { charPositionColumn = ((Char)(Convert.ToUInt16(CellIndex) + 7)).ToString(); } if (componentColumnCount % 3 == 0) { charPositionColumn = ((Char)(Convert.ToUInt16(CellIndex) + 14)).ToString(); } _Shape.Left = (float)oSummarySheet.get_Range(charPositionColumn + columnIndex.ToString()).Left; _Shape.Top = (float)oSummarySheet.get_Range(charPositionColumn + columnIndex.ToString()).Top; Logger.LogInfoMessage(String.Format("[GeneratePivotReports][DrawGraph] Process Completed for (" + componentName + ")"), true); } catch (Exception ex) { Logger.LogErrorMessage(String.Format("[GeneratePivotReports][DrawGraph][Exception]: " + ex.Message + ", " + exceptionComment), true); ExceptionCsv.WriteException(Constants.NotApplicable, Constants.NotApplicable, Constants.NotApplicable, "Pivot", ex.Message, ex.ToString(), "[GeneratePivotReports]: DrawGraph", ex.GetType().ToString(), exceptionComment); } finally { _Shape = null; series = null; oSummarySheet = null; } }
private void diagram_Click(object sender, EventArgs e) { KURS.allDataSet kursds = new KURS.allDataSet(); KURS.allDataSetTableAdapters.ZakazTableAdapter zta = new KURS.allDataSetTableAdapters.ZakazTableAdapter(); zta.Fill(kursds.Zakaz); // object misValue = System.Reflection.Missing.Value; // Excel.Application excelapp = new Excel.Application(); excelapp.Visible = true; excelapp.SheetsInNewWorkbook = 1; excelapp.Workbooks.Add(misValue); Excel.Workbooks excelappworkbooks = excelapp.Workbooks; Excel.Workbook excelappworkbook = excelappworkbooks[1]; Excel.Sheets excelsheets = excelappworkbook.Worksheets; Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1); //excelworksheet.Activate(); // var c = from p in kursds.Zakaz.AsEnumerable() where p.Data.Month == dateTimePicker1.Value.Month select p; int count = 0; excelapp.Cells[1][1] = "date"; excelapp.Cells[2][1] = "summ"; foreach (var p in c) { count++; } // int[] a = new int[count]; double[] b = new double[count]; int i = 0; double sum = 0; while (i < count) { foreach (var p in c) { a[i] = p.Data.Day; if (i == 0) { sum += p.Summ; } if (i > 0) { if (a[i] == a[i - 1]) { sum += p.Summ; } else { b[i - 1] = sum; sum = p.Summ; i++; } } else { i++; } } b[i - 1] = sum; count = i; i = 0; break; } Dictionary <int, double> dic = new Dictionary <int, double>(); while (i < count) { excelapp.Cells[1][i + 2] = a[i]; excelapp.Cells[2][i + 2] = b[i]; dic.Add(a[i], b[i]); i++; } // string str = (count + 1).ToString(); // //Excel.Window excelWindow = null; Excel.Range excelcells = null; //Определяем диаграммы как объекты Excel.ChartObjects Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)excelworksheet.ChartObjects(Type.Missing); //Добавляем одну диаграмму в Excel.ChartObjects - диаграмма пока не выбрана, но место для нее выделено в методе Add Excel.ChartObject chartsobjrct = chartsobjrcts.Add(100, 40, 300, 300); Excel.Chart chartPage = chartsobjrct.Chart; excelcells = excelworksheet.get_Range("B1", "B" + str); chartPage.SetSourceData(excelcells, misValue); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; Excel.Axis axis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); axis.HasTitle = true; axis.AxisTitle.Text = "Data"; // Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection(Type.Missing); Excel.Series series = seriesCollection.Item(1); series.XValues = a; // excelappworkbook.SaveAs(@"D:\siple.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); excelappworkbook.Close(true, misValue, misValue); excelapp.Quit(); this.chart1.Titles.Add("Отчет за месяц"); this.chart1.Series[0].Points.Clear(); Series ser = this.chart1.Series[0]; ser.LegendText = "Summ"; foreach (KeyValuePair <int, double> pair in dic) { ser.Points.AddXY(pair.Key, pair.Value); } // }
/*'*************************************************************** * 'FFT0 数组下标以0开始 FFT1 数组下标以1开始 * 'AR() 数据实部 AI() 数据虚部 * 'N 数据点数,为2的整数次幂 * 'NI 变换方向 1为正变换,-1为反变换 * '*************************************************************** */ private void Form_Fourier_Load(object sender, EventArgs e) { Graphic.RangeData(ref str, ref rows, ref cols); worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell; start_col = activecells.Column; start_row = activecells.Row; //double[] FFTProjectionArray = new double[rows - 1]; double[,] NewFFTProjectionArray = new double[rows - 1, 1]; //store the projection curve of FFT double TwoTime = 0; double TwoResult = rows - 1; while (TwoResult > 1) { TwoResult = TwoResult / 2; TwoTime = TwoTime + 1; } Nrows = Convert.ToInt32(Math.Pow(2.0, TwoTime)); double[] AR = new double[Nrows]; //double[] NewAR = new double[Nrows]; for (int j = 1; j < rows; j++) { //FFTProjectionArray[j - 1] = double.Parse(str[j, 1]); AR[j - 1] = double.Parse(str[j, 1]); } double[] AI = new double[Nrows]; FFT0(ref AR, ref AI, Nrows, 1); Span = double.Parse(textBox_Bandwidth.Text); comboBox_FourierMethod.Text = "Low Pass"; for (int j = Convert.ToInt32(rows * Span) + 1; j < Nrows; j++) { AR[j] = 0; AI[j] = 0; } FFT0(ref AR, ref AI, Nrows, -1); for (int j = 1; j < rows; j++) { NewFFTProjectionArray[j - 1, 0] = AR[j - 1]; } ((Excel.Range)worksheet.Cells[start_row, start_col + cols]).Value2 = "Smooth Y"; Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols]; Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + 1 + rows - 2, start_col + cols]; Excel.Range range = worksheet.get_Range(c1, c2); range.Value = NewFFTProjectionArray; //*******************************************************************************************8 c1 = (Excel.Range)worksheet.Cells[start_row, start_col]; c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols]; string ChartOrder = "CurveFourier" + Convert.ToString(Nchart); chart = worksheet.Controls.AddChart(250, 50, 450, 400, ChartOrder); Nchart = Nchart + 1; chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns); chart.ChartType = Excel.XlChartType.xlXYScatter; Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries2 = series.Item(2); Sseries2.Format.Line.Visible = Office.MsoTriState.msoFalse; Sseries2.Format.Fill.Solid(); Sseries2.Format.Fill.Visible = Office.MsoTriState.msoCTrue; //Sseries2.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb(); //Sseries2.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255,229, 184,0).ToArgb(); //Sseries2.Format.Fill.Transparency = 0.6F; Sseries2.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle; Sseries2.MarkerSize = 6; Excel.Series Sseries3 = series.Item(3); Sseries3.Format.Fill.Visible = Office.MsoTriState.msoFalse; Sseries3.Format.Line.Visible = Office.MsoTriState.msoTrue; Sseries3.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone; //Sseries3.Format.Line.ForeColor.RGB= System.Drawing.Color.FromArgb(255, 248, 118, 109).ToArgb(); Sseries3.Format.Line.Weight = 1.25F; Excel.Series Sseries1 = series.Item(1); Sseries1.Delete(); }
public DensityCurve() { InitializeComponent(); Bandwidth = double.Parse(textBox_Bandwidth.Text); Graphic.RangeData(ref str, ref rows, ref cols); worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell; start_col = activecells.Column; start_row = activecells.Row; Max_Value = Double.MinValue; Min_Value = Double.MaxValue; for (int i = 1; i < rows; i++) { if (double.Parse(str[i, 0]) > Max_Value) { Max_Value = double.Parse(str[i, 0]); } if (double.Parse(str[i, 0]) < Min_Value) { Min_Value = double.Parse(str[i, 0]); } } double Step = Bandwidth / Nstep; int Nrows = Convert.ToInt32((Max_Value - Min_Value) / Step + 2); double[,] data = new double[Nrows, 1]; double[,] x = new double[Nrows, 1]; double tempx; for (int i = 0; i < Nrows; i++) { x[i, 0] = i * Step + Min_Value; for (int j = 1; j < rows; j++) { tempx = (x[i, 0] - double.Parse(str[j, 0])) / Bandwidth; data[i, 0] = data[i, 0] + 1 / ((rows - 1) * Bandwidth) * Math.Exp(-tempx * tempx / 2) / Math.Sqrt(2 * 3.124259); } } ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]).Value2 = "X axis"; ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]).Value2 = "Y axis"; Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1]; Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 1]; Excel.Range range = worksheet.get_Range(c1, c2); range.Value = x; c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 2]; range = worksheet.get_Range(c1, c2); range.Value = data; string ChartOrder = "DensityCurve" + Convert.ToString(Nchart); chart = worksheet.Controls.AddChart(300, 50, 450, 400, ChartOrder); Nchart = Nchart + 1; c1 = (Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 2]; chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns); chart.ChartType = Excel.XlChartType.xlArea; Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries2 = series.Item(2); Sseries2.Delete(); Excel.Series Sseries = series.Item(1); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 1]; Sseries.XValues = worksheet.get_Range(c1, c2); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 2]; Sseries.Values = worksheet.get_Range(c1, c2); chart.HasLegend = false; chart.HasTitle = false; worksheet.Activate(); }
public AreaThreshold() { InitializeComponent(); Graphic.RangeData(ref str, ref rows, ref cols); worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell; start_col = activecells.Column; start_row = activecells.Row; double Max_Value = Double.MinValue; double Min_Value = Double.MaxValue; for (int i = 1; i < rows; i++) { if (double.Parse(str[i, 0]) > Max_Value) { Max_Value = double.Parse(str[i, 1]); } if (double.Parse(str[i, 0]) < Min_Value) { Min_Value = double.Parse(str[i, 1]); } } ThreholdArea = (Max_Value + Min_Value) / 2; textBox_Bandwidth.Text = Convert.ToString(ThreholdArea); textBox_Bandwidth.Refresh(); ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]).Value2 = "Assiatant"; ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]).Value2 = "<=Threshold"; ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 3]).Value2 = ">Threshold"; double[,] data = new double[rows - 1, 3]; for (int i = 1; i < rows; i++) { if (double.Parse(str[i, 1]) > ThreholdArea) { data[i - 1, 2] = double.Parse(str[i, 1]) - ThreholdArea; data[i - 1, 1] = 0; data[i - 1, 0] = ThreholdArea - data[i - 1, 1]; } else { data[i - 1, 2] = 0; data[i - 1, 1] = ThreholdArea - double.Parse(str[i, 1]); data[i - 1, 0] = ThreholdArea - data[i - 1, 1]; } } //double Max_data = Double.MinValue; //double Min_data = Double.MaxValue; //for (int i = 1; i < rows; i++) //{ // if (data[i - 1, 0] > Max_data) Max_data = data[i - 1, 0]; // if (data[i - 1, 1] < Min_data) Min_data = data[i - 1, 1]; //} Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1]; Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols + 3]; Excel.Range range = worksheet.get_Range(c1, c2); range.Value = data; string ChartOrder = "AreaThreshold" + Convert.ToString(Nchart); chart = worksheet.Controls.AddChart(300, 50, 450, 400, ChartOrder); Nchart = Nchart + 1; c1 = (Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]; c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols + 3]; chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns); chart.ChartType = Excel.XlChartType.xlAreaStacked; //*****************************************Primary Axis******************************************** Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries1 = series.Item(1); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col]; c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col]; Sseries1.XValues = worksheet.get_Range(c1, c2); //c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1]; //c2 = (Excel.Range)worksheet.Cells[start_row + rows-1, start_col + cols + 1]; //Sseries1.Values = worksheet.get_Range(c1, c2); //c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col]; //c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col]; //Sseries2.XValues = worksheet.get_Range(c1, c2); //c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2]; //c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols + 2]; //Sseries2.Values = worksheet.get_Range(c1, c2); ((Excel.Range)worksheet.Cells[start_row, start_col + cols]).Value2 = "Threshold"; ((Excel.Range)worksheet.Cells[start_row + 1, start_col + cols]).Value2 = ThreholdArea; //*****************************************Chart Style******************************************** //Excel.Series Sseries1 = series.Item(1); Sseries1.Format.Fill.Visible = Office.MsoTriState.msoFalse; Sseries1.Format.Line.Visible = Office.MsoTriState.msoFalse; Excel.Series Sseries2 = series.Item(2); Sseries2.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 196, 191, 0).ToArgb(); Sseries2.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 0, 0, 0).ToArgb(); Sseries2.Format.Line.Weight = 0.25F; Excel.Series Sseries3 = series.Item(3); Sseries3.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 109, 118, 248).ToArgb(); Sseries3.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 0, 0, 0).ToArgb(); Sseries3.Format.Line.Weight = 0.25F; //************************************************************************************************ chart.PlotArea.Format.Fill.Visible = Office.MsoTriState.msoCTrue; chart.PlotArea.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(229, 229, 229).ToArgb(); chart.PlotArea.Format.Fill.Transparency = 0; chart.PlotArea.Format.Line.Visible = Office.MsoTriState.msoFalse; // Legend chart.SetElement(Office.MsoChartElementType.msoElementLegendRight); chart.Legend.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb(); chart.Legend.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman"; chart.Legend.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman"; chart.Legend.Format.TextFrame2.TextRange.Font.Name = "Times New Roman"; chart.Legend.Format.TextFrame2.TextRange.Font.Size = 10; // ChartArea Line chart.ChartArea.Format.Line.Visible = Office.MsoTriState.msoFalse; chart.ChartArea.Height = 340.157480315; chart.ChartArea.Width = 380.5039370079; chart.SetElement(Office.MsoChartElementType.msoElementPrimaryValueGridLinesMinorMajor); chart.SetElement(Office.MsoChartElementType.msoElementPrimaryCategoryGridLinesMinorMajor); chart.SetElement(Office.MsoChartElementType.msoElementPrimaryValueAxisTitleAdjacentToAxis); chart.SetElement(Office.MsoChartElementType.msoElementPrimaryCategoryAxisTitleAdjacentToAxis); //y axis Excel.Axis axis = (Excel.Axis)chart.Axes( Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); axis.MinorUnit = axis.MajorUnit / 2; axis.MajorGridlines.Format.Line.Visible = Office.MsoTriState.msoCTrue; axis.MajorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb(); axis.MajorGridlines.Format.Line.Weight = (float)1.25; axis.MajorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid; axis.MinorGridlines.Format.Line.Visible = Office.MsoTriState.msoCTrue; axis.MinorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(242, 242, 242).ToArgb(); axis.MinorGridlines.Format.Line.Weight = (float)0.25; axis.MinorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid; axis.Format.Line.Visible = Office.MsoTriState.msoFalse; axis.HasTitle = true; axis.AxisTitle.Text = "y axis"; axis.TickLabels.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb(); axis.TickLabels.Font.Name = "Times New Roman"; axis.TickLabels.Font.Size = 10; axis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionLow; axis.AxisTitle.Format.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb(); axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman"; axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman"; axis.AxisTitle.Format.TextFrame2.TextRange.Font.Name = "Times New Roman"; axis.AxisTitle.Format.TextFrame2.TextRange.Font.Size = 10; axis.AxisTitle.Format.TextFrame2.TextRange.Font.Bold = Office.MsoTriState.msoFalse; //x axis axis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); axis.TickMarkSpacing = 3; axis.MajorGridlines.Format.Line.Visible = Office.MsoTriState.msoCTrue; axis.MajorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb(); axis.MajorGridlines.Format.Line.Weight = (float)1.25; axis.MajorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid; axis.MinorGridlines.Format.Line.Visible = Office.MsoTriState.msoCTrue; axis.MinorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(242, 242, 242).ToArgb(); axis.MinorGridlines.Format.Line.Weight = (float)0.25; axis.MinorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid; axis.Format.Line.Visible = Office.MsoTriState.msoFalse; axis.HasTitle = true; axis.AxisTitle.Text = "x axis"; axis.TickLabels.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb(); axis.TickLabels.Font.Name = "Times New Roman"; axis.TickLabels.Font.Size = 10; axis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionLow; axis.AxisTitle.Format.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb(); axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman"; axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman"; axis.AxisTitle.Format.TextFrame2.TextRange.Font.Name = "Times New Roman"; axis.AxisTitle.Format.TextFrame2.TextRange.Font.Size = 10; axis.AxisTitle.Format.TextFrame2.TextRange.Font.Bold = Office.MsoTriState.msoFalse; // Change plot area ForeColor chart.HasLegend = false; chart.HasTitle = false; chart.Refresh(); //worksheet.Activate(); flag = 1; }
private Excel.Chart SetStyleXlsChart(Excel.Chart xlChart) { Excel.SeriesCollection sc = xlChart.SeriesCollection(); Excel.Series xlSeries; for (int i = 0; i < sc.Count; i++) { xlSeries = sc.Item(i + 1); xlSeries.ChartType = GetSeriesChartType(chart.Series[i]); if (chart.Series[i].ChartType == SeriesChartType.Bar || chart.Series[i].ChartType == SeriesChartType.Column) { for (int j = 1; j <= chart.Series[i].Points.Count; j++) { xlSeries.Points(j).Interior.Color = ColorTranslator.ToOle(chart.Series[i].Color); } } else { xlSeries.Border.Color = chart.Series[i].Color; } xlSeries.Border.LineStyle = GetLineDashStyle(chart.Series[i]); xlSeries.Border.Weight = chart.Series[i].BorderWidth; xlSeries.MarkerStyle = GetSeriesMarkerStyle(chart.Series[i]); xlSeries.Name = chart.Series[i].Name; progress++; } if (!string.IsNullOrWhiteSpace(chart.Titles[0].Text)) { xlChart.HasTitle = true; xlChart.ChartTitle.Text = chart.Titles[0].Text; } else { xlChart.HasTitle = false; } progress++; xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasTitle = true; xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = chart.ChartAreas[0].AxisX.Title; xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasMajorGridlines = chart.ChartAreas[0].AxisX.MajorGrid.Enabled; xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasMinorGridlines = chart.ChartAreas[0].AxisX.MinorGrid.Enabled; if (xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasMajorGridlines) { xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).MajorGridlines.Border.LineStyle = GetLineDashStyle(chart.ChartAreas[0].AxisX.MajorGrid); } if (xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasMinorGridlines) { xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).MinorGridlines.Border.LineStyle = GetLineDashStyle(chart.ChartAreas[0].AxisX.MinorGrid); } progress += 4; xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasTitle = true; xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = chart.ChartAreas[0].AxisY.Title; xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasMajorGridlines = chart.ChartAreas[0].AxisY.MajorGrid.Enabled; xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasMinorGridlines = chart.ChartAreas[0].AxisY.MinorGrid.Enabled; if (xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasMajorGridlines) { xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).MajorGridlines.Border.LineStyle = GetLineDashStyle(chart.ChartAreas[0].AxisY.MajorGrid); } if (xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasMinorGridlines) { xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).MinorGridlines.Border.LineStyle = GetLineDashStyle(chart.ChartAreas[0].AxisY.MinorGrid); } progress += 4; xlChart.HasLegend = true; progress++; return(xlChart); }
/// <summary> /// 给工作表添加一个图表 /// </summary> /// <param name="firstRange">数据工作表</param> /// <param name="xData">x轴数据区域:格式A1:A14</param> /// <param name="ydata">y轴数据区域:格式B1:E14</param> /// <param name="chartName">图标名称</param> /// <param name="xname">y轴名称</param> /// <param name="yname">y轴名称</param> /// <param name="chartType">图表类型</param> public static void createInnerChart(Excel.Worksheet dataSheet, List <ChartStuct> charts, string chartName, string categoryName) { Excel.ChartObjects ChartObjects = (Excel.ChartObjects)dataSheet.ChartObjects(Missing.Value); int cols = 0; foreach (ChartStuct chartStuct in charts) { cols += chartStuct.dataRange.Columns.Count; } Excel.ChartObject chartObject = ChartObjects.Add(cols * 60, 30, 800, 350); if (charts.Count < 1) { return; //无数不处理 } //绘制第一维图表 Excel.Range firstRange = charts[0].dataRange; Excel.XlChartType chartType = charts[0].chartType; string yname = charts[0].yname; chartObject.Chart.ChartWizard(firstRange, chartType, Missing.Value, Excel.XlRowCol.xlColumns, 1, 1, true, chartName, categoryName, yname, Missing.Value); chartObject.Chart.ChartArea.ClearFormats(); chartObject.Chart.ChartType = chartType; chartObject.Chart.ChartArea.Fill.BackColor.SchemeColor = 2;//整个图表区背景 //Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.SchemeColor); //Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.RGB); chartObject.Chart.ChartArea.Fill.ForeColor.SchemeColor = 2; //Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.RGB); //Console.WriteLine(chartObject.Chart.PlotArea.Interior.ColorIndex); //chartObject.Chart.PlotArea.Interior.PatternColorIndex = 19; chartObject.Chart.PlotArea.Interior.ColorIndex = 2;//图例区域背景 Excel.Axis firstAxis = (Excel.Axis)chartObject.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); firstAxis.HasMajorGridlines = true;//不显示横向网格线 Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartObject.Chart.SeriesCollection(Type.Missing); int sc = seriesCollection.Count; if (seriesCollection.Count > 0) { Excel.Series seires1 = (Excel.Series)seriesCollection.Item(1);//具体数据图表区颜色 //seires1.Fill.BackColor.SchemeColor = 18; seires1.Fill.ForeColor.SchemeColor = 46; seires1.MarkerForegroundColorIndex = Excel.XlColorIndex.xlColorIndexNone; seires1.HasLeaderLines = false; } //绘制第二维图表 //修改第一维图表的各个序列的图例,这里有个bug,会用最后的标题覆盖前面的 // for (int i = 0; i < charts[0].seriesTitles.Length; i++) //{ //string stitle = charts[0].seriesTitles[i]; //Excel.Series tmpSeires = (Excel.Series)seriesCollection.Item(1); //tmpSeires.Name = stitle; //} //绘制第二个图表,多维这里设置 当有图表类型为XlChartType.xl3DColumn或其他3d效果时,抛 xlSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary;异常 for (int i = 1; i < charts.Count; i++) { ChartStuct cs = charts[i]; string stitle = cs.seriesTitles[0]; Excel.Series xlSeries = (Excel.Series)seriesCollection.NewSeries(); xlSeries.Name = stitle; xlSeries.HasLeaderLines = true; xlSeries.Fill.ForeColor.SchemeColor = 46; try { xlSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary; } catch (Exception eee) { Console.WriteLine(eee.Message); } xlSeries.ChartType = cs.chartType; xlSeries.Values = cs.dataRange; Excel.Axis valueAxis = (Excel.Axis)chartObject.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlSecondary); valueAxis.HasTitle = true; valueAxis.AxisTitle.Text = cs.yname; } }
private void textBox_Bandwidth_TextChanged(object sender, EventArgs e) { if (textBox_Bandwidth.Text == "") { return; } ratio = double.Parse(textBox_Bandwidth.Text);; if (ratio == 0) { return; } Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Point point; Excel.Series Sseries; int SquareSize; //RGB0 = System.Drawing.Color.FromArgb(255, 109, 118, 248); for (int i = 1; i < rows; i++) { Sseries = series.Item(i); Sseries.Format.Line.Visible = Office.MsoTriState.msoFalse; //Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle; if (comboBox_FourierMethod.SelectedItem.Equals("Circle")) { Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle; } else if (comboBox_FourierMethod.SelectedItem.Equals("Square")) { Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare; } Sseries.MarkerBackgroundColor = System.Drawing.Color.FromArgb(255, RGB0.B, RGB0.G, RGB0.R).ToArgb(); Sseries.MarkerForegroundColor = System.Drawing.Color.FromArgb(255, RGB0.B, RGB0.G, RGB0.R).ToArgb(); Sseries.Format.Fill.Solid(); Sseries.Format.Fill.Visible = Office.MsoTriState.msoCTrue; Sseries.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb(); Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB0.B, RGB0.G, RGB0.R).ToArgb(); Sseries.Format.Fill.Transparency = 0.2F; for (int j = 1; j < cols; j++) { point = (Excel.Point)Sseries.Points(j); SquareSize = (int)((Math.Sqrt(double.Parse(str[i, j]) * ratio) / Max_size) * Min_MarkerSize + 2); //SquareSize = (int)((double.Parse(str[i, j]) - Min_size) / (Max_size - Min_size) * ratio) + 2; //SquareSize = (int)((double.Parse(str[i, j]) / (Max_size * Max_size) * ratio) + 2);// - Min_size) / (Max_size - Min_size) * ratio) + 2; if (SquareSize > 72) { SquareSize = 72; } point.MarkerSize = SquareSize; } } chart.Refresh(); //worksheet.Activate(); }
// データテーブルをエクセルへエクスポート(箱ヒゲ図付き) public void ExportToExcelWithBoxPlotChart(DataTable dt) { if (dt.Rows.Count == 0) { return; } 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); // column headings for (int i = 0; i < dt.Columns.Count; i++) { xlWorkSheet.Cells[1, (i + 1)] = dt.Columns[i].ColumnName; } // rows for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { xlWorkSheet.Cells[(i + 2), (j + 1)] = dt.Rows[i][j]; } } int row = xlWorkSheet.UsedRange.Rows.Count; string address1 = "C1:C" + row.ToString() + ",H1:L" + row.ToString(); string address2 = "M2:M" + row.ToString(); string address3 = "N2:N" + row.ToString(); Excel.Range chartRange1; Excel.Range chartRange2; Excel.Range chartRange3; Excel.ChartObjects xlCharts1 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart1 = (Excel.ChartObject)xlCharts1.Add(800, 10, 600, 250); Excel.Chart chartPage1 = myChart1.Chart; chartRange1 = xlWorkSheet.get_Range(address1); chartRange2 = xlWorkSheet.get_Range(address2); chartRange3 = xlWorkSheet.get_Range(address3); chartPage1.SetSourceData(chartRange1, misValue); chartPage1.ChartType = Excel.XlChartType.xlColumnStacked; chartPage1.HasTitle = true; chartPage1.ChartTitle.Text = dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString(); chartPage1.HasLegend = false; Excel.Axis xAxis1 = (Excel.Axis)chartPage1.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); xAxis1.CategoryType = Excel.XlCategoryType.xlCategoryScale; Excel.SeriesCollection oSeriesCollection = (Excel.SeriesCollection)myChart1.Chart.SeriesCollection(misValue); Excel.Series s5 = oSeriesCollection.Item(5); s5.ChartType = Excel.XlChartType.xlLine; s5.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbDarkOrchid; Excel.Series s4 = oSeriesCollection.Item(4); s4.ChartType = Excel.XlChartType.xlLine; s4.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbDarkOrchid; Excel.Series s1 = oSeriesCollection.Item(1); s1.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbWhite; s1.Format.Fill.Transparency = 1; s1.Format.Line.Weight = 0; s1.HasErrorBars = true; s1.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludeMinusValues, Excel.XlErrorBarType.xlErrorBarTypeCustom, chartRange2, chartRange2); Excel.Series s3 = oSeriesCollection.Item(3); s3.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbAqua; s3.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack; s3.Format.Line.Weight = 1.0F; s3.HasErrorBars = true; s3.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludePlusValues, Excel.XlErrorBarType.xlErrorBarTypeCustom, chartRange3, chartRange3); Excel.Series s2 = oSeriesCollection.Item(2); s2.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbAqua; s2.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack; s2.Format.Line.Weight = 1.0F; xlApp.Visible = true; }
public BubbleRelationShip() { InitializeComponent(); ratio = double.Parse(textBox_Bandwidth.Text);; Graphic.RangeData(ref str, ref rows, ref cols); worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell; start_col = activecells.Column; start_row = activecells.Row; int i, j; for (j = 1; j < cols; j++) { ((Excel.Range)worksheet.Cells[start_row + rows + 1, start_col + j]).Value2 = str[0, j]; } for (j = 1; j < rows; j++) { ((Excel.Range)worksheet.Cells[start_row + rows + 1 + j, start_col]).Value2 = str[j, 0]; } for (i = 1; i < rows; i++) { for (j = 1; j < cols; j++) { ((Excel.Range)worksheet.Cells[start_row + rows + 1 + i, start_col + j]).Value2 = i;// str[i, 0]; } } ((Excel.Range)worksheet.Cells[start_row + rows + 1 + rows, start_col]).Value2 = "Assistant1"; for (j = 1; j < rows; j++) { ((Excel.Range)worksheet.Cells[start_row + rows + 1 + rows, start_col + j]).Value2 = 0; } ((Excel.Range)worksheet.Cells[start_row + rows + 1 + rows + 1, start_col]).Value2 = "Assistant2"; for (j = 1; j < rows; j++) { ((Excel.Range)worksheet.Cells[start_row + rows + 1 + rows + 1, start_col + j]).Value2 = j; } Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + rows + 1, start_col]; Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + rows + 1 + rows + 1, start_col + rows - 1]; string ChartOrder = "BubbleRelationShip" + Convert.ToString(Nchart); chart = worksheet.Controls.AddChart(250, 50, 450, 400, ChartOrder); Nchart = Nchart + 1; chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlRows); chart.ChartType = Excel.XlChartType.xlXYScatter; //double ratio = 21; //double Max_size = 0; Max_size = Double.MinValue; Min_size = Double.MaxValue; for (i = 1; i < rows; i++) { for (j = 1; j < cols; j++) { if (double.Parse(str[i, j]) > Max_size) { Max_size = double.Parse(str[i, j]); } if (double.Parse(str[i, j]) < Min_size) { Min_size = double.Parse(str[i, j]); } } } Max_size = Math.Sqrt(Max_size); Min_size = Math.Sqrt(Min_size); Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Point point; Excel.Series Sseries; int SquareSize; RGB0 = System.Drawing.Color.FromArgb(255, 248, 118, 109); for (i = 1; i < rows; i++) { Sseries = series.Item(i); Sseries.Format.Line.Visible = Office.MsoTriState.msoFalse; //if (comboBox_FourierMethod.SelectedItem.Equals("Circle")) //{ Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle; //} //else if (comboBox_FourierMethod.SelectedItem.Equals("Square")) //{ // Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare; //} Sseries.MarkerBackgroundColor = System.Drawing.Color.FromArgb(255, RGB0.B, RGB0.G, RGB0.R).ToArgb(); Sseries.MarkerForegroundColor = System.Drawing.Color.FromArgb(255, RGB0.B, RGB0.G, RGB0.R).ToArgb(); Sseries.Format.Fill.Solid(); Sseries.Format.Fill.Visible = Office.MsoTriState.msoCTrue; Sseries.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb(); Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB0.B, RGB0.G, RGB0.R).ToArgb(); Sseries.Format.Fill.Transparency = 0.2F; //Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle; for (j = 1; j < cols; j++) { point = (Excel.Point)Sseries.Points(j); //SquareSize = (int)((double.Parse(str[i, j])-Min_size) / (Max_size-Min_size) * ratio) + 2; SquareSize = (int)((Math.Sqrt(double.Parse(str[i, j]) * ratio) / Max_size) * Min_MarkerSize + 2); // - Min_size) / (Max_size - Min_size) * ratio) + 2; //SquareSize = (int)((double.Parse(str[i, j]) / (Max_size * Max_size) * ratio) + 2);// - Min_size) / (Max_size - Min_size) * ratio) + 2; if (SquareSize > 72) { SquareSize = 72; } point.MarkerSize = SquareSize; } } Excel.Series SseriesX = series.Item(rows); for (j = 1; j < cols; j++) { point = (Excel.Point)SseriesX.Points(j); point.HasDataLabel = true; point.DataLabel.Text = str[0, j]; point.DataLabel.Position = Excel.XlDataLabelPosition.xlLabelPositionBelow; point.DataLabel.Format.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb(); point.DataLabel.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman"; point.DataLabel.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman"; point.DataLabel.Format.TextFrame2.TextRange.Font.Name = "Times New Roman"; point.DataLabel.Format.TextFrame2.TextRange.Font.Size = 10; point.DataLabel.Format.TextFrame2.TextRange.Font.Bold = Office.MsoTriState.msoFalse; } SseriesX.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone; Excel.Series SseriesY = series.Item(rows + 1); c1 = (Excel.Range)worksheet.Cells[start_row + rows + 1 + rows, start_col + +1]; c2 = (Excel.Range)worksheet.Cells[start_row + rows + 1 + rows, start_col + rows - 1]; SseriesY.XValues = worksheet.get_Range(c1, c2); c1 = (Excel.Range)worksheet.Cells[start_row + rows + 1 + rows + 1, start_col + 1]; c2 = (Excel.Range)worksheet.Cells[start_row + rows + 1 + rows + 1, start_col + rows - 1]; SseriesY.Values = worksheet.get_Range(c1, c2); SseriesY.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone; for (j = 1; j < rows; j++) { point = (Excel.Point)SseriesY.Points(j); point.HasDataLabel = true; point.DataLabel.Text = str[j, 0]; point.DataLabel.Position = Excel.XlDataLabelPosition.xlLabelPositionLeft; point.DataLabel.Format.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb(); point.DataLabel.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman"; point.DataLabel.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman"; point.DataLabel.Format.TextFrame2.TextRange.Font.Name = "Times New Roman"; point.DataLabel.Format.TextFrame2.TextRange.Font.Size = 10; point.DataLabel.Format.TextFrame2.TextRange.Font.Bold = Office.MsoTriState.msoFalse; } //***************************************************** Style********************************************** chart.PlotArea.Format.Fill.Visible = Office.MsoTriState.msoCTrue; chart.PlotArea.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(229, 229, 229).ToArgb(); chart.PlotArea.Format.Fill.Transparency = 0; chart.PlotArea.Format.Line.Visible = Office.MsoTriState.msoFalse; // ChartArea Line chart.ChartArea.Format.Line.Visible = Office.MsoTriState.msoFalse; //chart.ChartArea.Height = 340.157480315; //chart.ChartArea.Width = 380.5039370079; // Chart Type: XYScatter // Add GridLinesMinorMajor chart.SetElement(Office.MsoChartElementType.msoElementPrimaryValueGridLinesMinorMajor); chart.SetElement(Office.MsoChartElementType.msoElementPrimaryCategoryGridLinesMinorMajor); chart.SetElement(Office.MsoChartElementType.msoElementPrimaryValueAxisTitleAdjacentToAxis); chart.SetElement(Office.MsoChartElementType.msoElementPrimaryCategoryAxisTitleAdjacentToAxis); //y axis Excel.Axis axis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); axis.MinorUnit = 1; axis.MajorUnit = 1; axis.MajorGridlines.Format.Line.Visible = Office.MsoTriState.msoCTrue; axis.MajorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb(); axis.MajorGridlines.Format.Line.Weight = (float)0.25; axis.MajorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid; axis.MinorGridlines.Format.Line.Visible = Office.MsoTriState.msoFalse; //axis.MinorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(242, 242, 242).ToArgb(); //axis.MinorGridlines.Format.Line.Weight = (float)0.25; //axis.MinorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid; axis.Format.Line.Visible = Office.MsoTriState.msoFalse; axis.HasTitle = true; axis.AxisTitle.Text = "y axis"; axis.HasDisplayUnitLabel = false; //axis.TickLabels.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb(); //axis.TickLabels.Font.Name = "Times New Roman"; //axis.TickLabels.Font.Size = 10; axis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionNone; //axis.AxisTitle.Format.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb(); //axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman"; //axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman"; //axis.AxisTitle.Format.TextFrame2.TextRange.Font.Name = "Times New Roman"; //axis.AxisTitle.Format.TextFrame2.TextRange.Font.Size = 10; //axis.AxisTitle.Format.TextFrame2.TextRange.Font.Bold = Office.MsoTriState.msoFalse; //x axis axis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); axis.MaximumScale = cols; axis.MinimumScale = 0; //axis.AxisBetweenCategories = false; axis.MinorUnit = 1; axis.MajorUnit = 1; axis.MinorGridlines.Format.Line.Visible = Office.MsoTriState.msoFalse; //axis.MajorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb(); //axis.MajorGridlines.Format.Line.Weight = (float)1.25; //axis.MajorGridlines.Format.Line.DashStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineSolid; axis.MajorGridlines.Format.Line.Visible = Office.MsoTriState.msoCTrue; axis.MajorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb(); axis.MajorGridlines.Format.Line.Weight = (float)0.25; axis.MajorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid; axis.Format.Line.Visible = Office.MsoTriState.msoFalse; axis.HasTitle = true; axis.AxisTitle.Text = "x axis"; //axis.TickLabels.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb(); //axis.TickLabels.Font.Name = "Times New Roman"; //axis.TickLabels.Font.Size = 10; axis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionNone; //axis.AxisTitle.Format.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb(); //axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman"; //axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman"; //axis.AxisTitle.Format.TextFrame2.TextRange.Font.Name = "Times New Roman"; //axis.AxisTitle.Format.TextFrame2.TextRange.Font.Size = 10; //axis.AxisTitle.Format.TextFrame2.TextRange.Font.Bold = Office.MsoTriState.msoFalse; chart.HasLegend = false; chart.HasTitle = false; //chart.ChartTitle.Delete(); chart.Refresh(); //worksheet.Activate(); }
private void textBox_Bandwidth_TextChanged(object sender, EventArgs e) { if (textBox_Bandwidth.Text == "") { return; } Bandwidth = double.Parse(textBox_Bandwidth.Text);; if (Bandwidth == 0) { return; } int Nrows = Convert.ToInt32((Max_Value - Min_Value) / Bandwidth + 2); double[,] data = new double[Nrows, 1]; int idx = 0; for (int i = 1; i < rows; i++) { idx = Convert.ToInt32((double.Parse(str[i, 0]) - Min_Value) / Bandwidth); data[idx, 0] = data[idx, 0] + 1; } data[Nrows - 2, 0] = data[Nrows - 2, 0] + data[Nrows - 1, 0]; double[,] x = new double[Nrows - 1, 1]; string[,] label = new string[Nrows - 1, 1]; for (int i = 0; i < Nrows - 1; i++) { label[i, 0] = "[" + Convert.ToString(Math.Floor((i * Bandwidth + Min_Value) * 100) / 100) + "," + Convert.ToString(Math.Floor(((i + 1) * Bandwidth + Min_Value) * 100) / 100) + ")"; //((Excel.Range)worksheet.Cells[start_row + 1 + i, start_col + cols + 1]).Value2 = label[i]; //((Excel.Range)worksheet.Cells[start_row + 1 + i, start_col + cols + 3]).Value2 = data[i]; x[i, 0] = i * Bandwidth + Min_Value; } ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]).Value2 = "X bandwidth"; ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]).Value2 = "X axis"; ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 3]).Value2 = "Y axis"; Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1]; Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 1]; Excel.Range range = worksheet.get_Range(c1, c2); range.Value = label; c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 2]; range = worksheet.get_Range(c1, c2); range.Value = x; c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 3]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 3]; range = worksheet.get_Range(c1, c2); range.Value = data; ((Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 3]).Delete();; c1 = (Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 3]; chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns); Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries2 = series.Item(2); Sseries2.Delete(); Excel.Series Sseries = series.Item(1); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 2]; Sseries.XValues = worksheet.get_Range(c1, c2); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 3]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 3]; Sseries.Values = worksheet.get_Range(c1, c2); chart.Refresh(); worksheet.Activate(); }
private void button_OK_Click(object sender, EventArgs e) { height = int.Parse(textBox_height.Text); int width = (int)Math.Ceiling(Convert.ToDouble(rows) / Convert.ToDouble(height)); int[,] data = new int[height, width]; int[] temp = new int[width]; Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); int N_Ser = series.Count; Excel.Series Sseries; if (N_Ser > height) { for (int i = N_Ser; i > height; i--) { series.Item(i).Delete(); } } else { for (int i = N_Ser + 1; i <= height; i++) { Sseries = series.NewSeries(); } } Excel.Point point; int RGB_B, RGB_G, RGB_R; for (int i = 0; i < height; i++) { temp = new int[width]; for (int j = 0; j < width; j++) { temp[j] = 1; data[i, j] = 1; } //Sseries = series.NewSeries(); series.Item(i + 1).Values = temp; } for (int i = 1; i <= height; i++) { Sseries = series.Item(i); for (int j = 1; j <= width; j++) { { point = (Excel.Point)Sseries.Points(j); point.Format.Fill.Solid(); point.Format.Fill.Visible = Office.MsoTriState.msoCTrue; point.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 229, 229, 229).ToArgb(); int index = (i - 1) * width + j; if (index < rows) { RGB_B = Convert.ToInt32(double.Parse(str[index, cols - 1])); if (RGB_B < 0) { RGB_B = 0; } if (RGB_B > 255) { RGB_B = 255; } RGB_G = Convert.ToInt32(double.Parse(str[index, cols - 2])); if (RGB_G < 0) { RGB_G = 0; } if (RGB_G > 255) { RGB_G = 255; } RGB_R = Convert.ToInt32(double.Parse(str[index, cols - 3])); if (RGB_R < 0) { RGB_R = 0; } if (RGB_R > 255) { RGB_R = 255; } point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB_B, RGB_G, RGB_R).ToArgb(); point.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 0, 0, 0).ToArgb(); } else { point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb(); point.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb(); } } } } }
public static void CreateChart(ref Excel.Worksheet ws) { Excel.Workbook wb; Excel.Chart chart; Excel.ChartObject chartobject; wb = (Excel.Workbook)ws.Parent; long lastrow = ws.UsedRange.Rows.Count; long lastcol = ws.UsedRange.Columns.Count; //Console.WriteLine("lastrow: {0}, lastcol: {1}", lastrow, lastcol); if (lastrow <= 2) { Console.WriteLine("有效数据不足"); return; } // delete old chart if (((Excel.ChartObjects)ws.ChartObjects()).Count >= 1) { //Console.WriteLine("Chart num: {0}", ((Excel.ChartObjects)ws.ChartObjects()).Count); ((Excel.ChartObjects)ws.ChartObjects()).Delete(); } for (int col = 2; col <= lastcol; col++) { if (((Excel.Range)ws.Cells[1, col]).Value == null) { break; } List <double> data = new List <double>(); for (int row = 2; row <= lastrow; row++) { double?d = ((Excel.Range)ws.Cells[row, col]).Value; if (d != null) { data.Add((double)d); } } double mean = data.Average(); double sd = data.StandardDeviation(); double cv = sd / mean; string chartTitle = string.Format("{0} {1} 累积均值: {2:F2} 累积标准差: {3:F2} CV: {4:P2}", ws.Name, ((Excel.Range)ws.Cells[1, col]).Value, mean, sd, cv); Console.WriteLine(chartTitle); //固定均值,标准差设置 ws.Range["J1"].Offset[0, col - 2].Value = ws.Cells[1, col].Value; ws.Range["I2"].Value = "固定均值"; ws.Range["I3"].Value = "固定标准差"; if (ws.Range["J2"].Offset[0, col - 2].Value != null) { mean = ws.Range["J2"].Offset[0, col - 2].Value; } else { Console.WriteLine("未设置{0}的固定均值", ws.Cells[1, col].Value); } if (ws.Range["J3"].Offset[0, col - 2].Value != null) { sd = ws.Range["J3"].Offset[0, col - 2].Value; } else { Console.WriteLine("未设置{0}的固定标准差", ws.Cells[1, col].Value); } double[] meanarray = new double[lastrow - 1]; double[] plusOneSDarray = new double[lastrow - 1]; double[] plusTwoSDarray = new double[lastrow - 1]; double[] plusThreeSDarray = new double[lastrow - 1]; double[] minusOneSDarray = new double[lastrow - 1]; double[] minusTwoSDarray = new double[lastrow - 1]; double[] minusThreeSDarray = new double[lastrow - 1]; // 初始化 for (int i = 0; i < lastrow - 1; i++) { meanarray[i] = mean; plusOneSDarray[i] = mean + sd; plusTwoSDarray[i] = mean + 2 * sd; plusThreeSDarray[i] = mean + 3 * sd; minusOneSDarray[i] = mean - sd; minusTwoSDarray[i] = mean - 2 * sd; minusThreeSDarray[i] = mean - 3 * sd; } // Create a new chart chart = (Excel.Chart)wb.Charts.Add(); Excel.Range XRng = ws.Range[ws.Cells[2, 1], ws.Cells[lastrow, 1]]; Excel.Range dataRng = ws.Range[ws.Cells[1, col], ws.Cells[lastrow, col]]; chart.SetSourceData(dataRng, Excel.XlRowCol.xlColumns); chart.ChartType = Excel.XlChartType.xlLineMarkers; chart.HasLegend = false; chart.HasTitle = true; chart.ChartTitle.Text = chartTitle; // 纵坐标范围 string min = (mean - 3.75 * sd) > data.Min() ? data.Min().ToString("F2") : (mean - 3.75 * sd).ToString("F2"); string max = data.Max() > (mean + 3.75 * sd) ? data.Max().ToString("F2") : (mean + 3.75 * sd).ToString("F2"); chart.Axes(Excel.XlAxisType.xlValue).MinimumScale = min; chart.Axes(Excel.XlAxisType.xlValue).MaximumScale = max; // Embedding chart on a worksheet chart.Location(Excel.XlChartLocation.xlLocationAsObject, ws.Name); // get activate chartobject chartobject = (Excel.ChartObject)ws.ChartObjects(col - 1); // 去除网格线 chartobject.Chart.Axes(Excel.XlAxisType.xlValue).HasMajorGridlines = false; // 位置大小 chartobject.Left = 439; chartobject.Top = 105 + (col - 2) * 255; chartobject.Height = 255; chartobject.Width = 810; Excel.SeriesCollection se = chartobject.Chart.SeriesCollection(); se.Item(1).XValues = XRng; // 横坐标值 se.Item(1).Format.Line.Weight = 1.2F; // mean se.NewSeries(); se.Item(2).Name = "mean"; se.Item(2).Values = meanarray; se.Item(2).Format.Line.Weight = 1; se.Item(2).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone; se.Item(2).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbSkyBlue; // mean + sd se.NewSeries(); se.Item(3).Name = "mean+sd"; se.Item(3).Values = plusOneSDarray; se.Item(3).Format.Line.Weight = 1; se.Item(3).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone; se.Item(3).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlue; // mean - sd se.NewSeries(); se.Item(4).Name = "mean-sd"; se.Item(4).Values = minusOneSDarray; se.Item(4).Format.Line.Weight = 1; se.Item(4).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone; se.Item(4).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlue; // mean + 2sd se.NewSeries(); se.Item(5).Name = "mean+2sd"; se.Item(5).Values = plusTwoSDarray; se.Item(5).Format.Line.Weight = 1; se.Item(5).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone; se.Item(5).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbOliveDrab; // mean - 2sd se.NewSeries(); se.Item(6).Name = "mean-2sd"; se.Item(6).Values = minusTwoSDarray; se.Item(6).Format.Line.Weight = 1; se.Item(6).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone; se.Item(6).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbOliveDrab; // mean + 3sd se.NewSeries(); se.Item(7).Name = "mean+3sd"; se.Item(7).Values = plusThreeSDarray; se.Item(7).Format.Line.Weight = 1; se.Item(7).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone; se.Item(7).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbIndianRed; // mean - 3sd se.NewSeries(); se.Item(8).Name = "mean-3sd"; se.Item(8).Values = minusThreeSDarray; se.Item(8).Format.Line.Weight = 1; se.Item(8).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone; se.Item(8).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbIndianRed; // 修改超过Mean +- 2SD的点的颜色 // 修改超过Mean +- 3SD的点的颜色 double meanplus2sd = mean + 2 * sd; double meanminus2sd = mean - 2 * sd; double meanplus3sd = mean + 3 * sd; double meanminus3sd = mean - 3 * sd; for (int i = 2; i <= lastrow; i++) { Excel.Range rng = (Excel.Range)ws.Cells[i, col]; if (rng.Value == null) { continue; } if ((double)rng.Value >= meanplus3sd) { rng.Interior.Color = 255; // vbred se.Item(1).Points(i - 1).Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbRed; } else if ((double)rng.Value >= meanplus2sd) { rng.Interior.Color = 65535; // vbYellow se.Item(1).Points(i - 1).Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbYellow; } else if ((double)rng.Value <= meanminus3sd) { rng.Interior.Color = 255; se.Item(1).Points(i - 1).Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbRed; } else if ((double)rng.Value <= meanminus2sd) { rng.Interior.Color = 65535; se.Item(1).Points(i - 1).Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbYellow; } } } }
private Excel.Chart F_Chart; // VBConversions Note: Initial value cannot be assigned here since it is non-static. Assignment has been moved to the class constructors. /// <summary> /// 当在数据列上双击时执行相应的操作 /// </summary> /// <param name="ElementID">在图表上双击击中的对象</param> /// <param name="Arg1">所选数据系列在集合中的索引下标值,注意,第一第曲线的下标值为1,而不是0。</param> /// <param name="Arg2"></param> /// <param name="Cancel"></param> /// <remarks>要么将其删除,要么将其锁定在图表中,要么什么都不做</remarks> private void SeriesChange(int ElementID, int Arg1, int Arg2, ref bool Cancel) { if (ElementID == (int)Excel.XlChartItem.xlSeries) { Debug.Print("当前选择的曲线下标值为: " + System.Convert.ToString(Arg1)); //所选数据系列在集合中的索引下标值,注意,第一第曲线的下标值为1,而不是0 int seriesIndex = Arg1; //所选的数据系列 Excel.Series seri; Excel.SeriesCollection seriColl = this.Chart.SeriesCollection() as Excel.SeriesCollection; seri = seriColl.Item(seriesIndex); // -------- 打开处理对话框,并根据返回的不同结果来执行不同的操作 DiaFrm_LockDelete diafrm = new DiaFrm_LockDelete(); //判断要删除的曲线是否为当前滚动的曲线,有如下两种判断方法 bool blnDeletingTheRollingCurve = false; blnDeletingTheRollingCurve = seriesIndex == cst_LboundOfSeriesInCollection; //blnDeletingTheRollingCurve = (seri.Name = F_movingSeries.Name) if (F_CurvesCount == 1 || blnDeletingTheRollingCurve) { //如果图表中只有一条曲线,或者点击的正好是要正在进行滚动的曲线,那么这条曲线不能被删除 diafrm.btn2.Enabled = false; diafrm.AcceptButton = diafrm.btn1; } else //只能进行删除 { diafrm.btn1.Enabled = false; diafrm.AcceptButton = diafrm.btn2; } //在界面上取消图表对象的选择 this.Sheet_Drawing.Range["A1"].Activate(); this.Application.ScreenUpdating = false; //---------------------------------- 开始执行数据系列的添加或删除 //此数据列对应的施工日期 DateTime t_date = default(DateTime); SeriesTag SrTag = default(SeriesTag); try { SrTag = this.F_DicSeries_Tag[seriesIndex]; t_date = SrTag.ConstructionDate; } catch (Exception ex) { MessageBox.Show("提取字典中的元素出错,字典中没有此元素。" + "\r\n" + ex.Message + "\r\n" + "报错位置:" + ex.TargetSite.Name, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } //---------------------------------------- M_DialogResult result = default(M_DialogResult); string strPrompt = "The date value for the selected series is:" + "\r\n" + t_date.ToString(AMEApplication.DateFormat) + "\r\n" + "Choose to Lock or Delete this series..."; result = diafrm.ShowDialog(strPrompt, "Lock or Delete Series"); //---------------------------------------- switch (result) { case M_DialogResult.Delete: //删除数据系列 try { DeleteSeries(seriesIndex); } catch (Exception ex) { Debug.Print("删除数据系列出错。" + "\r\n" + ex.Message + "\r\n" + "报错位置:" + ex.TargetSite.Name); } break; case M_DialogResult.Lock: //添加数据系列 try { CopySeries(seriesIndex); } catch (Exception ex) { Debug.Print(ex.Message); MessageBox.Show("添加数据系列出错。" + "\r\n" + ex.Message + "\r\n" + "报错位置:" + ex.TargetSite.Name, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } break; } this.Application.ScreenUpdating = true; } //覆盖原来的双击操作 Cancel = true; this.Sheet_Drawing.Range["A1"].Activate(); }