How to specify a Spread Cell Range for a Chart using R1C1 notation

Posted by: mikedempsey on 8 September 2017, 2:34 pm EST

  • Posted 8 September 2017, 2:34 pm EST

    <P>I have no problem charting a range of cells in a Spread control if I specify the range using A1 notation but I can not get it to work with R1C1 notation.</P>
    <P>Since my app generates data on the fly (from database tables) my spreadsheets always use R1C1 references, since I have no idea what is in which column - or even how many columns there will be.</P>
    <P>Is there a way to specify that I want to use R1C1 notation in the Chart.SeriesDataField() function?<BR>eg.<BR>   Chart.SeriesDataField(Spread, <FONT color=#a31515 size=2><FONT color=#a31515 size=2>"Col2"</FONT></FONT><FONT size=2>, </FONT><FONT color=#a31515 size=2><FONT color=#a31515 size=2>"Sheet1!R1C2:R5C2"</FONT></FONT><FONT size=2>)</FONT><BR>instead of:<BR><FONT size=2>   Chart.SeriesDataField(Spread, </FONT><FONT color=#a31515 size=2><FONT color=#a31515 size=2>"ColB"</FONT></FONT><FONT size=2>, </FONT><FONT color=#a31515 size=2><FONT color=#a31515 size=2>"Sheet1!$B$1:$B$5"</FONT></FONT><FONT size=2>)</FONT></P>
    <P><FONT size=2>If not, is there a function available to convert integers to their corresponding Letter strings?</FONT></P>
    <P><FONT size=2>Thanks<BR>Mike</P></FONT>
  • Replied 8 September 2017, 2:34 pm EST

    <p>Hello,</p><p>Here is the code I used with R1C1 reference style and it works fine at my end. Please have a look at the code and let me know if it helps.<br></p><blockquote>            FarPoint.Win.Chart.BarSeries series = new FarPoint.Win.Chart.BarSeries();
    <br>            series.BarShape = FarPoint.Win.Chart.BarShape.FullCone;
    <br>            series.Values.DataSource = new FarPoint.Win.Spread.Chart.SeriesDataField(fpSpread1, "Values", "Sheet1!R1C1:R5C1");
    <br>            series.CategoryNames.DataSource = new FarPoint.Win.Spread.Chart.SeriesDataField(fpSpread1, "Text", "Sheet1!R1C2:R5C2", FarPoint.Win.Spread.Chart.SegmentDataType.Text);
    <br>            FarPoint.Win.Chart.YPlotArea plotArea = new FarPoint.Win.Chart.YPlotArea();
    <br>            plotArea.Location = new PointF(0.2f, 0.2f);
    <br>            plotArea.Size = new SizeF(0.6f, 0.6f);
    <br>            plotArea.Series.Add(series);
    <br>            FarPoint.Win.Chart.LabelArea label = new FarPoint.Win.Chart.LabelArea();
    <br>            label.Text = this.fpSpread1.ActiveSheet.Cells[0, 1].Text;
    <br>            label.Location = new PointF(0.5f, 0.02f);
    <br>            label.AlignmentX = 0.5f;
    <br>            label.AlignmentY = 0.0f;
    <br>            FarPoint.Win.Chart.ChartModel model = new FarPoint.Win.Chart.ChartModel();
    <br>            model.LabelAreas.Add(label);
    <br>            model.PlotAreas.Add(plotArea);
    <br>            FarPoint.Win.Spread.Chart.SpreadChart chart = new FarPoint.Win.Spread.Chart.SpreadChart();
    <br>            chart.Size = new Size(533, 300);
    <br>            chart.Location = new Point(10, 20);
    <br>            chart.Model = model;
    <br>            FarPoint.Win.Chart.FpChart fpchart1 = new FarPoint.Win.Chart.FpChart();
    <br>            this.Controls.Add(fpchart1);
    <br>            fpchart1.Location = new Point(60, 60);
    <br>            fpchart1.Size = new Size(600, 600);
    <br>            fpchart1.Model = model;</blockquote>Thanks. 
  • Replied 8 September 2017, 2:34 pm EST

    <P>I think my problem was that I forgot that RnCn notation is 1 based rather than zero based.</P>
    <P>My test referenced R0 in one of the entries ... and that's what killed it.</P>
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels