Getting point values from a SpreadsheetGear 2010 chart series

Question

I'm using SpreadsheetGear 2010 to draw column charts, and wish to loop through all data points, colouring the negative ones red... But I cannot see a way to get to the "value" of the point as I loop through.

The following (interpreting the data label string as the double value) works most of the time:

for (int i = 0; i < chart.SeriesCollection.Count; i++)
{
    positiveColor = Color.FromArgb(79, 129, 189); // blue
    negativeColor = Color.FromArgb(192, 80, 77); // red
    chart.SeriesCollection[i].HasDataLabels = true;

    for (int j = 0; j < chart.SeriesCollection[i].Points.Count; j++)
    {
        double pointValue;

        // If the point is -0.004 but the number format is "0.00",
        // label will be "0.00"
        string label = chart.SeriesCollection[i].Points[j].DataLabel.Text;

        chart.SeriesCollection[i].Points[j].Format.Fill.ForeColor.RGB = 
            (System.Double.TryParse(label, out pointValue) && pointValue >= 0)
            ? positiveColor
            : negativeColor;
    }
}

... but, if the value is slightly negative, the data label just shows zero, so pointValue >= 0 and I interpret the point as positive. This results in annoying small blue points hanging down from my X-axis.

SpreadsheetGear.Charts.IPoint does not appear to have any useful properties for retrieving the value that was used to draw the point.

chart.SeriesCollection[i].Values looks hopeful, but returns an object whose string interpretation is "=Data!$B$25:$B$44". I can't seem to cast this to anything useful, and can't find any relevant SpreadsheetGear documentation.

Any idea how I can get to the values used to draw the points?

Answer 1

This answer is not very elegant, but it should give you what you need. You already had the idea of using chart.SeriesCollection[i].Values. If the address contained in chart.SeriesCollection[i].Values is used, you can get the value from the same data that the chart is getting the value from to create the column.

Replace the line code where you define the string label.

string label = chart.SeriesCollection[i].Points[j].DataLabel.Text;

With this line.

string label = chart.Sheet.Workbook.Worksheets[chart.Sheet.Name].Cells[chart.SeriesCollection[i].Values.ToString().Replace("=" + chart.Sheet.Name + "!", "")][0, j].Value.ToString();

This way the value is not controlled by the way the label is formatted.

If the cell value is null, this will throw an exception when ToString() is added.

Here is another version with the changed line separated out more so it is less confusing. Also there is a check for null value before using ToString().

//if you do not have direct access to the worksheet object.
SpreadsheetGear.IWorksheet worksheet1 = chart.Sheet.Workbook.Worksheets[chart.Sheet.Name];
for (int i = 0; i < chart.SeriesCollection.Count; i++)
{
  Color positiveColor = Color.FromArgb(79, 129, 189); // blue
  Color negativeColor = Color.FromArgb(192, 80, 77); // red
  chart.SeriesCollection[i].HasDataLabels = true;

  //Get the address of the series
  string address = chart.SeriesCollection[i].Values.ToString().Replace("=" + chart.Sheet.Name + "!", "");
  for (int j = 0; j < chart.SeriesCollection[i].Points.Count; j++)
  {
    double pointValue;
    //bool usePositiveValueColor = false;
    // If the point is -0.004 but the number format is "0.00",
    // label will be "0.00"
    //string label = chart.SeriesCollection[i].Points[j].DataLabel.Text;
    string label = (worksheet1.Cells[address][0, j].Value != null) 
      ? worksheet1.Cells[address][0, j].Value.ToString() : "0";

    chart.SeriesCollection[i].Points[j].Format.Fill.ForeColor.RGB =
        (System.Double.TryParse(label, out pointValue) && pointValue >= 0)
        ? positiveColor
        : negativeColor;
  }
}