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;
}
}
