private double[] GetLeaderLineDataLabelAttachingPoint(Excel.DataLabel dataLabel, double bubbleX, double bubbleY) { var labelAttachingX = Clamp(bubbleX, dataLabel.Left, dataLabel.Left + dataLabel.Width); var labelAttachingY = Clamp(bubbleY, dataLabel.Top, dataLabel.Top + dataLabel.Height); return(new[] { labelAttachingX, labelAttachingY }); }
private void SpreadOutDataLabels(Excel.Chart chart) { var occupiedRectangles = new List <Rectangle>(); var occupiedCircles = new List <Circle>(); var leaderLineAttachingPoints = new List <double[]>(); var seriesCollection = chart.SeriesCollection(); // First populated the list with all data bubbles... double i = 0; foreach (Excel.Series series in seriesCollection) { foreach (Excel.Point point in series.Points()) { var circle = new Circle(point.Left + point.Width / 2, point.Top + point.Height / 2, point.Width / 2, i); occupiedCircles.Add(circle); Debug.WriteLine("Added bubble " + point.DataLabel.Text + " at " + circle.CenterX + ", " + circle.CenterY + " with radius " + circle.Radius); i++; } } // ... and denote the X-axis as an occupied spot... var xAxis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory); occupiedRectangles.Add(new Rectangle(xAxis.Left, xAxis.Top, xAxis.Width, xAxis.Height)); // ... then make another pass, this time placing the data labels i = 0; foreach (Excel.Series series in seriesCollection) { foreach (Excel.Point point in series.Points()) { Excel.DataLabel dataLabel = series.DataLabels(1); var bubble = new Circle(point.Left + point.Width / 2, point.Top + point.Height / 2, point.Width / 2, i); Debug.WriteLine("Now finding placement for data label " + dataLabel.Text + "..."); var unoccupiedRectangle = FindUnoccupiedRectangleNearCircle(dataLabel.Width, dataLabel.Height, occupiedRectangles, occupiedCircles, bubble, out var leaderLineAttachingCoordinates); dataLabel.Left = unoccupiedRectangle.MinX; dataLabel.Top = unoccupiedRectangle.MinY; leaderLineAttachingPoints.Add(leaderLineAttachingCoordinates); occupiedRectangles.Add(unoccupiedRectangle); Debug.WriteLine("Added data label " + point.DataLabel.Text + " at " + unoccupiedRectangle.MinX + ", " + unoccupiedRectangle.MinY); i++; } } DrawLeaderLines(chart, leaderLineAttachingPoints); }
public void click_importData(Office.IRibbonControl control) { // Initialize Open File Dialog OpenFileDialog dialog_openFile = new OpenFileDialog(); dialog_openFile.Filter = "Excel Files (*.xls;*.xlsx; *.xlsm; *.csv)| *.xls; *.csv; *.xlsx; *.xlsm|Unicode Text|*.txt"; dialog_openFile.Title = "Select Files To Import..."; dialog_openFile.Multiselect = true; // Set Active Elements Excel.Workbook workbook_active = GetActiveWorkbook(); // Set Default Directory SetDefaultDirectory(dialog_openFile, workbook_active); // Show Open File Dialog DialogResult result_openFile = dialog_openFile.ShowDialog(); if (result_openFile == System.Windows.Forms.DialogResult.OK) { DataSet dataset_import = new DataSet(); foreach (String fileName in dialog_openFile.FileNames) { try { DataTable datatable_temp = new DataTable(Path.GetFileNameWithoutExtension(fileName)); string ext = Path.GetExtension(fileName); using (TextFieldParser parser = new TextFieldParser(@fileName)) { parser.TextFieldType = FieldType.Delimited; string delimeter = ConfigurationManager.AppSettings[ext + "_delimeter"]; parser.SetDelimiters(delimeter); parser.HasFieldsEnclosedInQuotes = Convert.ToBoolean(ConfigurationManager.AppSettings[ext + "_quotes"]); if (ext == ".csv") { string str_checkName = ConfigurationManager.AppSettings[ext + "_name"]; string str_checkValue = ConfigurationManager.AppSettings[ext + "_value"]; string str_checkTitle = ConfigurationManager.AppSettings[ext + "_title"]; while (!parser.EndOfData) { // TODO: DO NOT HARDCODE FIRST VAL and TYPEOF VALUES string[] fields_temp = parser.ReadFields(); if (fields_temp[0] == str_checkName) { for (int i = 1; i < fields_temp.Length; i++) { datatable_temp.Columns.Add(new DataColumn(fields_temp[i], typeof(double))); } } else if (fields_temp[0] == str_checkValue) { double[] values_temp = Array.ConvertAll <string, double>(SubArray <string>(fields_temp, 1, fields_temp.Length - 1), Convert.ToDouble); if (values_temp.Length != datatable_temp.Columns.Count) { throw new Exception("Column mismatch."); } DataRow datarow_temp = datatable_temp.NewRow(); for (int i = 0; i < values_temp.Length; i++) { datarow_temp[i] = values_temp[i]; } datatable_temp.Rows.Add(datarow_temp); } else if (fields_temp[1] == str_checkTitle) { datatable_temp.TableName = fields_temp[2]; } } } } // End of 'using' dataset_import.Tables.Add(datatable_temp); } catch (Exception IE) { MessageBox.Show(IE.Message + " error: could not load file"); continue; } } Excel.Worksheet worksheet_new = CreateNewWorksheet(); string[] arr_names = new string[dataset_import.Tables.Count + 1]; DataTable voltage_table = dataset_import.Tables[0]; arr_names[0] = "Vgs"; double[] voltage_values = voltage_table.AsEnumerable().Select(r => r.Field <double>("Vgs")).ToArray(); for (int v = 0; v < voltage_table.Rows.Count; v++) { worksheet_new.Cells[v + 2, 1] = voltage_values[v]; } for (int i = 0; i < dataset_import.Tables.Count; i++) { DataTable datatable_temp = dataset_import.Tables[i]; arr_names[i + 1] = "Id of " + datatable_temp.TableName; double[] values_fromTable = datatable_temp.AsEnumerable().Select(r => r.Field <double>("Id")).ToArray(); for (int j = 0; j < datatable_temp.Rows.Count; j++) { worksheet_new.Cells[j + 2, i + 2] = -1 * values_fromTable[j]; } } for (int h = 0; h < arr_names.Length; h++) { worksheet_new.Cells[1, h + 1] = arr_names[h]; } object misValue = System.Reflection.Missing.Value; Excel.Range range_chart; Excel.ChartObjects xlCharts = (Excel.ChartObjects)worksheet_new.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(50, 150, 500, 250); Excel.Chart chartPage = myChart.Chart; range_chart = worksheet_new.UsedRange; Excel.Range range_tempA = range_chart.Columns[1].Find("-40"); range_chart = range_chart.Resize[range_tempA.Row]; Excel.Range range_tempB = worksheet_new.Cells[range_chart.Columns[1].Find("-20").Row, 2]; Excel.Range c2 = worksheet_new.Cells[range_chart.Rows.Count, range_chart.Columns.Count]; Excel.Range range_trendline = (Excel.Range)worksheet_new.get_Range(range_tempB, c2); range_chart.Cells[1, 1].Value = ""; chartPage.SetSourceData(range_chart, misValue); //chartPage.SetSourceData(range_trendline, misValue); chartPage.ChartType = Excel.XlChartType.xlXYScatter; foreach (Excel.Series series in chartPage.SeriesCollection()) { Excel.Trendline trendline = series.Trendlines().Add(Excel.XlTrendlineType.xlLinear, System.Type.Missing, System.Type.Missing, 20, System.Type.Missing, System.Type.Missing, true, true, System.Type.Missing); Excel.DataLabel datalabel_temp = trendline.DataLabel; datalabel_temp.NumberFormat = "0.0000E+00"; } Excel.Range a1 = range_chart.Columns[1].Find("-20"); Excel.Range a2 = worksheet_new.Cells[range_chart.Rows.Count, 1]; Excel.Range a3 = (Excel.Range)worksheet_new.get_Range(a1, a2); for (int i = 1; i <= range_trendline.Columns.Count; i++) { Excel.Series series_temp = chartPage.SeriesCollection().Add(range_trendline.Columns[i]); series_temp.XValues = a3; Excel.Trendlines trendlines_temp = series_temp.Trendlines(); Excel.Trendline trendline_temp = trendlines_temp.Add(Excel.XlTrendlineType.xlLinear, System.Type.Missing, System.Type.Missing, 20, System.Type.Missing, System.Type.Missing, true, true, System.Type.Missing); Excel.DataLabel datalabel_temp = trendline_temp.DataLabel; datalabel_temp.NumberFormat = "0.0000E+00"; } } }
public void click_analyzeChart(Office.IRibbonControl control) { Excel.Chart chart_active = GetActiveChart(); List <double> x_intercepts = new List <double>(); foreach (Excel.Series series in chart_active.SeriesCollection()) { Excel.Trendlines trendlines_temp = series.Trendlines(); if (trendlines_temp.Count > 0) { Excel.DataLabel dlabel_temp = trendlines_temp.Item(1).DataLabel; string label = dlabel_temp.Text; int x = label.IndexOf("R²"); int y = label.IndexOf("y"); string equation = label.Substring(y, x); string r_val = label.Substring(x); Regex pattern = new Regex(@"[\d]*\.?[\d]+(E[-+][\d]+)?"); Match match = pattern.Match(r_val); double r_squared = Double.Parse(match.Value, System.Globalization.NumberStyles.Float); if (r_squared < 0.9) { trendlines_temp.Item(1).Delete(); if (r_squared < 0.7) { series.Delete(); } continue; } Regex pattern_x = new Regex(@"[\d]*\.?[\d]+(E[-+][\d]+)?"); MatchCollection matches = pattern_x.Matches(equation); Stack <double> eq_vals = new Stack <double>(); foreach (Match match_temp in matches) { double val = Double.Parse(match_temp.Value, System.Globalization.NumberStyles.Float); eq_vals.Push(val); } double y_int = eq_vals.Pop(); double y_slope = eq_vals.Pop(); if (y_slope < 1e-8) { trendlines_temp.Item(1).Delete(); series.Delete(); continue; } double x_int = (-1 * y_int) / y_slope; x_intercepts.Add(x_int); } } if (x_intercepts.Count > 0) { MessageBox.Show(x_intercepts.Average().ToString()); } }
private void AddDataPoints(Excel.Chart bubbleChart, Excel.Worksheet sourceWorksheet) { var dataMatrix = GetDataMatrixFromExcelWorksheet(sourceWorksheet); var seriesCollection = bubbleChart.SeriesCollection(); var minXValue = double.MaxValue; var minYValue = double.MaxValue; var maxXValue = double.MinValue; var maxYValue = double.MinValue; foreach (var row in dataMatrix) { var series = seriesCollection.NewSeries(); series.Has3DEffect = true; series.HasDataLabels = true; var dataLabelText = row[0]; var xValue = Convert.ToDouble(row[1]); var yValue = Convert.ToDouble(row[2]); var revenue = Convert.ToDouble(row[3]) / 1000; var projectTypeId = GetProjectTypeIdFromProjectTypeString(row[4]); var bubbleSize = GetBubbleSizeFromRevenue(revenue); series.Format.Fill.ForeColor.RGB = _projectTypeColors[projectTypeId]; series.XValues = new[] { xValue }; series.Values = new[] { yValue }; series.BubbleSizes = new[] { bubbleSize }; Excel.DataLabel dataLabel = series.DataLabels(1); dataLabel.Text = dataLabelText; if (xValue < minXValue) { minXValue = xValue; } if (yValue < minYValue) { minYValue = yValue; } if (xValue > maxXValue) { maxXValue = xValue; } if (yValue > maxYValue) { maxYValue = yValue; } } var xAxis = (Excel.Axis)bubbleChart.Axes(Excel.XlAxisType.xlCategory); xAxis.MinimumScale = (Math.Floor(minXValue / 100) - 1) * 100; xAxis.MaximumScale = (Math.Ceiling(maxXValue / 100) + 1) * 100; var yAxis = (Excel.Axis)bubbleChart.Axes(Excel.XlAxisType.xlValue); yAxis.MinimumScale = (Math.Floor(minYValue * 10) - 1) / 10; yAxis.MaximumScale = (Math.Ceiling(maxYValue * 10) + 1) / 10; }
/// <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; } }