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

    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.


    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.


    Is there a way to specify that I want to use R1C1 notation in the Chart.SeriesDataField() function?
    eg.
       Chart.SeriesDataField(Spread, "Col2", "Sheet1!R1C2:R5C2")
    instead of:
       Chart.SeriesDataField(Spread, "ColB", "Sheet1!$B$1:$B$5")


    If not, is there a function available to convert integers to their corresponding Letter strings?


    Thanks
    Mike

  • Replied 8 September 2017, 2:34 pm EST

    Hello,

    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.

                FarPoint.Win.Chart.BarSeries series = new FarPoint.Win.Chart.BarSeries();

                series.BarShape = FarPoint.Win.Chart.BarShape.FullCone;

                series.Values.DataSource = new FarPoint.Win.Spread.Chart.SeriesDataField(fpSpread1, "Values", "Sheet1!R1C1:R5C1");

                series.CategoryNames.DataSource = new FarPoint.Win.Spread.Chart.SeriesDataField(fpSpread1, "Text", "Sheet1!R1C2:R5C2", FarPoint.Win.Spread.Chart.SegmentDataType.Text);

                FarPoint.Win.Chart.YPlotArea plotArea = new FarPoint.Win.Chart.YPlotArea();

                plotArea.Location = new PointF(0.2f, 0.2f);

                plotArea.Size = new SizeF(0.6f, 0.6f);

                plotArea.Series.Add(series);

                FarPoint.Win.Chart.LabelArea label = new FarPoint.Win.Chart.LabelArea();

                label.Text = this.fpSpread1.ActiveSheet.Cells[0, 1].Text;

                label.Location = new PointF(0.5f, 0.02f);

                label.AlignmentX = 0.5f;

                label.AlignmentY = 0.0f;

                FarPoint.Win.Chart.ChartModel model = new FarPoint.Win.Chart.ChartModel();

                model.LabelAreas.Add(label);

                model.PlotAreas.Add(plotArea);

                FarPoint.Win.Spread.Chart.SpreadChart chart = new FarPoint.Win.Spread.Chart.SpreadChart();

                chart.Size = new Size(533, 300);

                chart.Location = new Point(10, 20);

                chart.Model = model;

                FarPoint.Win.Chart.FpChart fpchart1 = new FarPoint.Win.Chart.FpChart();

                this.Controls.Add(fpchart1);

                fpchart1.Location = new Point(60, 60);

                fpchart1.Size = new Size(600, 600);

                fpchart1.Model = model;
    Thanks. 
  • Replied 8 September 2017, 2:34 pm EST

    I think my problem was that I forgot that RnCn notation is 1 based rather than zero based.


    My test referenced R0 in one of the entries ... and that's what killed it.


    Thanks

Need extra support?

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

Learn More

Forum Channels