Setting data labels on scatter chart with values from sheet

NLargeZO · April 8, 2014, 8:26am

So, I have a number of text values that I’ve added to my chart as categories, and (x,y) values that I’ve added to my series using the AddDataPointForScatterSeries method. When I open the output of this chart in power point, I can add data labels to all of the points and set the contents of the labels to be values from cells (in this case the data-range covering my named categories).

int dataIndex = 0;

foreach (var data in ScatterData.Sample)

{

chart.ChartData.Categories.Add(fact.GetCell(defaultWorksheetIndex, dataIndex + 1, 0, data.Name));
<span style="color:#cc99cc;">var</span> series <span style="color:#6699cc;">=</span> chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>Series[<span style="color:#f99157;">0</span>];
<span style="color:#cc99cc;">var</span> xValue <span style="color:#6699cc;">=</span> fact<span style="color:#6699cc;">.</span>GetCell(defaultWorksheetIndex, dataIndex <span style="color:#6699cc;">+</span> <span style="color:#f99157;">1</span>, <span style="color:#f99157;">1</span>, data<span style="color:#6699cc;">.</span>X);
<span style="color:#cc99cc;">var</span> yValue <span style="color:#6699cc;">=</span> fact<span style="color:#6699cc;">.</span>GetCell(defaultWorksheetIndex, dataIndex <span style="color:#6699cc;">+</span> <span style="color:#f99157;">1</span>, <span style="color:#f99157;">2</span>, data<span style="color:#6699cc;">.</span>Y);
<span style="color:#cc99cc;">var</span> point <span style="color:#6699cc;">=</span> series<span style="color:#6699cc;">.</span>DataPoints<span style="color:#6699cc;">.</span>AddDataPointForScatterSeries(xValue, yValue);
dataIndex <span style="color:#6699cc;">+=</span> <span style="color:#f99157;">1</span>;

}

How can I bind the label of the datapoint in my code to a cell on the underlying worksheet?

mudassir.fayyaz · April 8, 2014, 9:24am

Hi,

Thanks for inquiring Aspose.Slides.

I have observed your requirement and like to share that the chart labels are associated with series data of chart categories but not to the cell in the worksheet. If you change the associate the binding of chart series value to a particular cell in worksheet the label will indirectly bind itself to that cell as well as it is bound to chart series. This is a logical behavior of chart and is also a right way.

Please share if I may help you further in this regard.

Many Thanks,

NLargeZO · April 8, 2014, 10:24am

I’m not sure I understand what you’re saying (the subject and object of your sentence are not clear).

If it is the case that labels are bound to categories, how can I set my scatter chart to display the appropriate category name for each marker?

If it is the case that the initial label values are bound to one of the X or Y values on a scatter datavalue, this is not necessarily the only right way - PowerPoint itself allows the labels to be bound to a collection of cells on the underlying worksheet. Is there any way to programmatically control which collection of cells this is, so that it can be duplicated?

(I note that setting DataLabelFormat.ShowCategoryName on the label to true has the effect of displaying the X-Value from the datapoint that the label is for.)

mudassir.fayyaz · April 9, 2014, 2:44am

Hi,

I am sorry for your inconvenience if I have not been able to clearly share my point of view. I have not mentioned that labels are bound to categories. The labels are bound to chart series data of categories. In other words the labels are associated with Chart Series Data of particular category. That is why you are able to have options like show category name, show series name and show value. I am hopeful that following code will be helpful to you in understanding the concept.

public static void testScatter()

{

Presentation pres = new Presentation();

ISlide slide = pres.Slides[0];

//Creating the default chart

IChart chart = slide.Shapes.AddChart(ChartType.ScatterWithSmoothLines, 0, 0, 400, 400);

//Getting the default chart data worksheet index

int defaultWorksheetIndex = 0;

//Accessing the chart data worksheet

IChartDataWorkbook fact = chart.ChartData.ChartDataWorkbook;

//Delete demo series

chart.ChartData.Series.Clear();

//Add new series

chart.ChartData.Series.Add(fact.GetCell(defaultWorksheetIndex, 1, 1, “Series 1”), chart.Type);

chart.ChartData.Series.Add(fact.GetCell(defaultWorksheetIndex, 1, 3, “Series 2”), chart.Type);

//Take first chart series

IChartSeries series = chart.ChartData.Series[0];

//Add new point (1:3) there.

series.DataPoints.AddDataPointForScatterSeries(fact.GetCell(defaultWorksheetIndex, 2, 1, 1), fact.GetCell(defaultWorksheetIndex, 2, 2, 3));

//Add new point (2:10)

series.DataPoints.AddDataPointForScatterSeries(fact.GetCell(defaultWorksheetIndex, 3, 1, 2), fact.GetCell(defaultWorksheetIndex, 3, 2, 10));

//Edit the type of series

series.Type = ChartType.ScatterWithStraightLinesAndMarkers;

//Changing the chart series marker

series.Marker.Size = 10;

series.Marker.Symbol = MarkerStyleType.Star;

foreach (IDataLabel label in series.Labels)

{

IDataLabelFormat FormatLabel = label.DataLabelFormat;

FormatLabel.ShowCategoryName = true;

FormatLabel.ShowSeriesName = true;

FormatLabel.ShowValue = true;

}

//Setting Legends Text Properties

//Take second chart series

series = chart.ChartData.Series[1];

//Add new point (5:2) there.

series.DataPoints.AddDataPointForScatterSeries(fact.GetCell(defaultWorksheetIndex, 2, 3, 5), fact.GetCell(defaultWorksheetIndex, 2, 4, 2));

//Add new point (3:1)

series.DataPoints.AddDataPointForScatterSeries(fact.GetCell(defaultWorksheetIndex, 3, 3, 3), fact.GetCell(defaultWorksheetIndex, 3, 4, 1));

//Add new point (2:2)

series.DataPoints.AddDataPointForScatterSeries(fact.GetCell(defaultWorksheetIndex, 4, 3, 2), fact.GetCell(defaultWorksheetIndex, 4, 4, 2));

//Add new point (5:1)

series.DataPoints.AddDataPointForScatterSeries(fact.GetCell(defaultWorksheetIndex, 5, 3, 5), fact.GetCell(defaultWorksheetIndex, 5, 4, 1));

//Changing the chart series marker

series.Marker.Size = 10;

series.Marker.Symbol = MarkerStyleType.Circle;

foreach (IDataLabel label in series.Labels)

{

IDataLabelFormat FormatLabel = label.DataLabelFormat;

FormatLabel.ShowCategoryName = true;

FormatLabel.ShowSeriesName = true;

FormatLabel.ShowValue = true;

}

pres.Save(“C:\Presentations\AsposeScatterChart.pptx”, SaveFormat.Pptx);

}

Please share, if I may help you further in this regard.

Many Thanks,

NLargeZO · June 17, 2014, 8:37am

Having come back to this issue, I’m finding that this isn’t much help at all.

A scatter chart datapoint is added using the AddDataPointForScatterSeries method on the Datapoints collection of the chart. This adds a cell for the x-value and a cell for the y-value. I also add a category by setting a value to a separate category cell and then adding it to the Categories collection on the ChartData object.

By changing the boolean properties of the corresponding DataLabelFormat, I can see that the ShowCategoryName property displays the X-value and that the ShowValue property displays the Y-value. Neither has any affect on the visibility of the content of the Category cell that I’ve added.

This is the code I’m using, and attached are two example charts. The ‘Actual’ chart is the output of the below code, and the ‘Target’ chart is what I’m trying to achieve.

        class Item

{

public string Name { get; set; }

public double X { get; set; }

public double Y { get; set; }

}
    <span style="color:#cc99cc;">public</span> <span style="color:#cc99cc;">static</span> <span style="color:#cc99cc;">void</span> testScatterClean()
    {
        <span style="color:#ffcc66;">Presentation</span> pres <span style="color:#6699cc;">=</span> <span style="color:#cc99cc;">new</span> <span style="color:#ffcc66;">Presentation</span>();

        <span style="color:#cc99cc;">ISlide</span> slide <span style="color:#6699cc;">=</span> pres<span style="color:#6699cc;">.</span>Slides[<span style="color:#f99157;">0</span>];

        <span style="color:#747369;">//Creating the default chart</span>
        <span style="color:#cc99cc;">IChart</span> chart <span style="color:#6699cc;">=</span> slide<span style="color:#6699cc;">.</span>Shapes<span style="color:#6699cc;">.</span>AddChart(<span style="color:#66cccc;">ChartType</span><span style="color:#6699cc;">.</span>ScatterWithMarkers, <span style="color:#f99157;">0</span>, <span style="color:#f99157;">0</span>, <span style="color:#f99157;">400</span>, <span style="color:#f99157;">400</span>);

        <span style="color:#747369;">//Getting the default chart data worksheet index</span>
        <span style="color:#cc99cc;">int</span> defaultWorksheetIndex <span style="color:#6699cc;">=</span> <span style="color:#f99157;">0</span>;

        <span style="color:#747369;">//Accessing the chart data worksheet</span>
        <span style="color:#cc99cc;">IChartDataWorkbook</span> fact <span style="color:#6699cc;">=</span> chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>ChartDataWorkbook;

        <span style="color:#747369;">//Delete demo series</span>
        <span style="color:#cc99cc;">foreach</span> (<span style="color:#cc99cc;">var</span> series <span style="color:#cc99cc;">in</span> chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>Series)
        {
            series<span style="color:#6699cc;">.</span>DataPoints<span style="color:#6699cc;">.</span>Clear();
        }
        chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>Series<span style="color:#6699cc;">.</span>Clear();
        chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>Categories<span style="color:#6699cc;">.</span>Clear();
        chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>ChartDataWorkbook<span style="color:#6699cc;">.</span>Clear(<span style="color:#f99157;">0</span>);


        <span style="color:#747369;">//Add new series</span>
        <span style="color:#cc99cc;">var</span> seriesNameRow <span style="color:#6699cc;">=</span> <span style="color:#f99157;">0</span>;
        <span style="color:#cc99cc;">var</span> initialColumnForSeries <span style="color:#6699cc;">=</span> <span style="color:#f99157;">0</span>;

        <span style="color:#cc99cc;">var</span> seriesNameCell <span style="color:#6699cc;">=</span> fact<span style="color:#6699cc;">.</span>GetCell(defaultWorksheetIndex, seriesNameRow, initialColumnForSeries, <span style="color:#99cc99;">"Scatter Values"</span>);
        chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>Series<span style="color:#6699cc;">.</span>Add(seriesNameCell, chart<span style="color:#6699cc;">.</span>Type);


        <span style="color:#747369;">//Take first chart series</span>
        <span style="color:#cc99cc;">var</span> chartSeries <span style="color:#6699cc;">=</span> chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>Series[<span style="color:#f99157;">0</span>];

        <span style="color:#cc99cc;">var</span> mySeriesData <span style="color:#6699cc;">=</span> <span style="color:#cc99cc;">new</span> <span style="color:#ffcc66;">List</span><span style="color:#6699cc;"><</span><span style="color:#ffcc66;">Item</span><span style="color:#6699cc;">></span>
                             {
                                 <span style="color:#cc99cc;">new</span> <span style="color:#ffcc66;">Item</span> { X <span style="color:#6699cc;">=</span> <span style="color:#f99157;">1</span>, Y <span style="color:#6699cc;">=</span> <span style="color:#f99157;">2</span>, Name <span style="color:#6699cc;">=</span> <span style="color:#99cc99;">"foo"</span>},
                                 <span style="color:#cc99cc;">new</span> <span style="color:#ffcc66;">Item</span> { X <span style="color:#6699cc;">=</span> <span style="color:#f99157;">2</span>, Y <span style="color:#6699cc;">=</span> <span style="color:#f99157;">2.2</span>, Name <span style="color:#6699cc;">=</span> <span style="color:#99cc99;">"baz"</span>},
                                 <span style="color:#cc99cc;">new</span> <span style="color:#ffcc66;">Item</span> { X <span style="color:#6699cc;">=</span> <span style="color:#f99157;">0.5</span>, Y <span style="color:#6699cc;">=</span> <span style="color:#f99157;">1.2</span>, Name <span style="color:#6699cc;">=</span> <span style="color:#99cc99;">"argh"</span>},
                                 <span style="color:#cc99cc;">new</span> <span style="color:#ffcc66;">Item</span> { X <span style="color:#6699cc;">=</span> <span style="color:#f99157;">2.5</span>, Y <span style="color:#6699cc;">=</span> <span style="color:#f99157;">2</span>, Name <span style="color:#6699cc;">=</span> <span style="color:#99cc99;">"green"</span>},
                                 <span style="color:#cc99cc;">new</span> <span style="color:#ffcc66;">Item</span> { X <span style="color:#6699cc;">=</span> <span style="color:#f99157;">5</span>, Y <span style="color:#6699cc;">=</span> <span style="color:#f99157;">3</span>, Name <span style="color:#6699cc;">=</span> <span style="color:#99cc99;">"knee"</span>}
                             };

        <span style="color:#cc99cc;">int</span> row <span style="color:#6699cc;">=</span> <span style="color:#f99157;">1</span>;
        <span style="color:#cc99cc;">int</span> xCol <span style="color:#6699cc;">=</span> <span style="color:#f99157;">1</span>, yCol <span style="color:#6699cc;">=</span> <span style="color:#f99157;">2</span>, categoryCol <span style="color:#6699cc;">=</span> <span style="color:#f99157;">0</span>;

        <span style="color:#cc99cc;">foreach</span> (<span style="color:#cc99cc;">var</span> data <span style="color:#cc99cc;">in</span> mySeriesData)
        {
            <span style="color:#cc99cc;">var</span> chartData <span style="color:#6699cc;">=</span> chartSeries<span style="color:#6699cc;">.</span>Chart<span style="color:#6699cc;">.</span>ChartData;

            <span style="color:#cc99cc;">var</span> xValueCell <span style="color:#6699cc;">=</span> chartData<span style="color:#6699cc;">.</span>ChartDataWorkbook<span style="color:#6699cc;">.</span>GetCell(defaultWorksheetIndex, row, xCol, data<span style="color:#6699cc;">.</span>X);
            <span style="color:#cc99cc;">var</span> yValueCell <span style="color:#6699cc;">=</span> chartData<span style="color:#6699cc;">.</span>ChartDataWorkbook<span style="color:#6699cc;">.</span>GetCell(defaultWorksheetIndex, row, yCol, data<span style="color:#6699cc;">.</span>Y);
            <span style="color:#cc99cc;">var</span> dataPoint <span style="color:#6699cc;">=</span> chartData<span style="color:#6699cc;">.</span>Series[<span style="color:#f99157;">0</span>]<span style="color:#6699cc;">.</span>DataPoints<span style="color:#6699cc;">.</span>AddDataPointForScatterSeries(xValueCell, yValueCell);

            <span style="color:#cc99cc;">var</span> categoryDataCell <span style="color:#6699cc;">=</span> chartData<span style="color:#6699cc;">.</span>ChartDataWorkbook<span style="color:#6699cc;">.</span>GetCell(defaultWorksheetIndex, row, categoryCol, data<span style="color:#6699cc;">.</span>Name);
            <span style="color:#cc99cc;">var</span> category <span style="color:#6699cc;">=</span> chartData<span style="color:#6699cc;">.</span>Categories<span style="color:#6699cc;">.</span>Add(categoryDataCell);

            row <span style="color:#6699cc;">+=</span> <span style="color:#f99157;">1</span>;
        }
        
        <span style="color:#747369;">//Changing the chart series marker</span>
        chartSeries<span style="color:#6699cc;">.</span>Marker<span style="color:#6699cc;">.</span>Size <span style="color:#6699cc;">=</span> <span style="color:#f99157;">10</span>;
        chartSeries<span style="color:#6699cc;">.</span>Marker<span style="color:#6699cc;">.</span>Symbol <span style="color:#6699cc;">=</span> <span style="color:#66cccc;">MarkerStyleType</span><span style="color:#6699cc;">.</span>Diamond;

        <span style="color:#cc99cc;">foreach</span> (<span style="color:#cc99cc;">IDataLabel</span> label <span style="color:#cc99cc;">in</span> chartSeries<span style="color:#6699cc;">.</span>Labels)
        {
            <span style="color:#cc99cc;">IDataLabelFormat</span> FormatLabel <span style="color:#6699cc;">=</span> label<span style="color:#6699cc;">.</span>DataLabelFormat;
            FormatLabel<span style="color:#6699cc;">.</span>ShowCategoryName <span style="color:#6699cc;">=</span> <span style="color:#cc99cc;">true</span>;
            FormatLabel<span style="color:#6699cc;">.</span>ShowSeriesName <span style="color:#6699cc;">=</span> <span style="color:#cc99cc;">true</span>;
            FormatLabel<span style="color:#6699cc;">.</span>ShowValue <span style="color:#6699cc;">=</span> <span style="color:#cc99cc;">true</span>;
        }

        pres<span style="color:#6699cc;">.</span>Save(<span style="color:#99cc99;">"C:\\temp\\AsposeScatterChart.pptx"</span>, <span style="color:#66cccc;">SaveFormat</span><span style="color:#6699cc;">.</span>Pptx);
    }</pre></div><div><br></div><div>One other point of note is that the Clear() methods on the DataPoints collection in the series don't work to clear all content from the underlying worksheet, and calling clear at the worksheet level (via chart.ChartData.ChartDataWorkbook.Clear(0);) seems to completely remove the worksheet, leaving the data unable to be edited.</div><div><br></div><div>Incidentally, I'm aware that I can override the text in the labels for each datapoint, but that breaks the linking of chart and underlying worksheet that I want to achieve.</div><div><br></div><div><br></div>

mudassir.fayyaz · June 18, 2014, 1:29am

Hi,

I have worked over the requirements shared and like to share that in your code you are setting the values on independent labels levels to show Category Name (X), Value (Y) and Series name. The code is working as expected and there is no issue in it. You are trying to set the label text to some custom text , “CELLRANGE”. You need to do it differently and I have attached the modified sample code for your kind reference in this regard to achieve desired result.

Now, when you call Clear() method on DataPoints collection then its values are cleared inside chart to the cells of excel file that is holding those values. The linking of DataPoint collection is removed from those cells holding the values. However, the values that are no more linked will reside in excel file and can be cleared using explicit call to ChartData.ChartDataWorkbook.Clear(0). I hope this will clear the concept.

Please share, if I may help you further in this regard.

Many Thanks,

NLargeZO · June 18, 2014, 7:56am

To reiterate:

"One other point of note is that the Clear() methods on the DataPoints collection in the series don’t work to clear all content from the underlying worksheet, and calling clear at the worksheet level (via chart.ChartData.ChartDataWorkbook.Clear(0)

seems to completely remove the worksheet, leaving the data unable to be edited.|

"Incidentally, I’m aware that I can override the text in the labels for each datapoint, but that breaks the linking of chart and underlying worksheet that I want to achieve."

The key thing that I want to achieve is to allow the data to be edited via the underlying worksheet.

Attached is a spreadsheet where I cleared the workbook. I am unable to edit the data, although it does display the dots. Here is the code for it.

        public static void testScatterCleanWithClear()

{

Presentation pres = new Presentation();
        <span style="color:#cc99cc;">ISlide</span> slide <span style="color:#6699cc;">=</span> pres<span style="color:#6699cc;">.</span>Slides[<span style="color:#f99157;">0</span>];

        <span style="color:#747369;">//Creating the default chart</span>
        <span style="color:#cc99cc;">IChart</span> chart <span style="color:#6699cc;">=</span> slide<span style="color:#6699cc;">.</span>Shapes<span style="color:#6699cc;">.</span>AddChart(<span style="color:#66cccc;">ChartType</span><span style="color:#6699cc;">.</span>ScatterWithMarkers, <span style="color:#f99157;">0</span>, <span style="color:#f99157;">0</span>, <span style="color:#f99157;">400</span>, <span style="color:#f99157;">400</span>);
        
        <span style="color:#747369;">//Getting the default chart data worksheet index</span>
        <span style="color:#cc99cc;">int</span> defaultWorksheetIndex <span style="color:#6699cc;">=</span> <span style="color:#f99157;">0</span>;

        <span style="color:#747369;">//Accessing the chart data worksheet</span>
        <span style="color:#cc99cc;">IChartDataWorkbook</span> fact <span style="color:#6699cc;">=</span> chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>ChartDataWorkbook;

        <span style="color:#747369;">//Delete demo series</span>
        <span style="color:#cc99cc;">foreach</span> (<span style="color:#cc99cc;">var</span> series <span style="color:#cc99cc;">in</span> chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>Series)
        {
            series<span style="color:#6699cc;">.</span>DataPoints<span style="color:#6699cc;">.</span>Clear();
        }
        chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>Series<span style="color:#6699cc;">.</span>Clear();
        chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>Categories<span style="color:#6699cc;">.</span>Clear();
        chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>ChartDataWorkbook<span style="color:#6699cc;">.</span>Clear(<span style="color:#f99157;">0</span>);


        <span style="color:#747369;">//Add new series</span>
        <span style="color:#cc99cc;">var</span> seriesNameRow <span style="color:#6699cc;">=</span> <span style="color:#f99157;">0</span>;
        <span style="color:#cc99cc;">var</span> initialColumnForSeries <span style="color:#6699cc;">=</span> <span style="color:#f99157;">0</span>;

        <span style="color:#cc99cc;">var</span> seriesNameCell <span style="color:#6699cc;">=</span> fact<span style="color:#6699cc;">.</span>GetCell(defaultWorksheetIndex, seriesNameRow, initialColumnForSeries, <span style="color:#99cc99;">"Scatter Values"</span>);
        chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>Series<span style="color:#6699cc;">.</span>Add(seriesNameCell, chart<span style="color:#6699cc;">.</span>Type);


        <span style="color:#747369;">//Take first chart series</span>
        <span style="color:#cc99cc;">var</span> chartSeries <span style="color:#6699cc;">=</span> chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>Series[<span style="color:#f99157;">0</span>];

        <span style="color:#cc99cc;">var</span> mySeriesData <span style="color:#6699cc;">=</span> <span style="color:#cc99cc;">new</span> <span style="color:#ffcc66;">List</span><span style="color:#6699cc;"><</span><span style="color:#ffcc66;">Item</span><span style="color:#6699cc;">></span>
                             {
                                 <span style="color:#cc99cc;">new</span> <span style="color:#ffcc66;">Item</span> { X <span style="color:#6699cc;">=</span> <span style="color:#f99157;">1</span>, Y <span style="color:#6699cc;">=</span> <span style="color:#f99157;">2</span>, Category <span style="color:#6699cc;">=</span> <span style="color:#99cc99;">"foo"</span>},
                                 <span style="color:#cc99cc;">new</span> <span style="color:#ffcc66;">Item</span> { X <span style="color:#6699cc;">=</span> <span style="color:#f99157;">2</span>, Y <span style="color:#6699cc;">=</span> <span style="color:#f99157;">2.2</span>, Category <span style="color:#6699cc;">=</span> <span style="color:#99cc99;">"baz"</span>},
                                 <span style="color:#cc99cc;">new</span> <span style="color:#ffcc66;">Item</span> { X <span style="color:#6699cc;">=</span> <span style="color:#f99157;">0.5</span>, Y <span style="color:#6699cc;">=</span> <span style="color:#f99157;">1.2</span>, Category <span style="color:#6699cc;">=</span> <span style="color:#99cc99;">"argh"</span>},
                                 <span style="color:#cc99cc;">new</span> <span style="color:#ffcc66;">Item</span> { X <span style="color:#6699cc;">=</span> <span style="color:#f99157;">2.5</span>, Y <span style="color:#6699cc;">=</span> <span style="color:#f99157;">2</span>, Category <span style="color:#6699cc;">=</span> <span style="color:#99cc99;">"green"</span>},
                                 <span style="color:#cc99cc;">new</span> <span style="color:#ffcc66;">Item</span> { X <span style="color:#6699cc;">=</span> <span style="color:#f99157;">5</span>, Y <span style="color:#6699cc;">=</span> <span style="color:#f99157;">3</span>, Category <span style="color:#6699cc;">=</span> <span style="color:#99cc99;">"knee"</span>}
                             };

        <span style="color:#cc99cc;">int</span> row <span style="color:#6699cc;">=</span> <span style="color:#f99157;">1</span>;
        <span style="color:#cc99cc;">int</span> xCol <span style="color:#6699cc;">=</span> <span style="color:#f99157;">1</span>, yCol <span style="color:#6699cc;">=</span> <span style="color:#f99157;">2</span>, categoryCol <span style="color:#6699cc;">=</span> <span style="color:#f99157;">0</span>;

        <span style="color:#cc99cc;">foreach</span> (<span style="color:#cc99cc;">var</span> data <span style="color:#cc99cc;">in</span> mySeriesData)
        {
            <span style="color:#cc99cc;">var</span> chartData <span style="color:#6699cc;">=</span> chartSeries<span style="color:#6699cc;">.</span>Chart<span style="color:#6699cc;">.</span>ChartData;

            
            <span style="color:#cc99cc;">var</span> xValueCell <span style="color:#6699cc;">=</span> chartData<span style="color:#6699cc;">.</span>ChartDataWorkbook<span style="color:#6699cc;">.</span>GetCell(defaultWorksheetIndex, row, xCol, data<span style="color:#6699cc;">.</span>X);
            <span style="color:#cc99cc;">var</span> yValueCell <span style="color:#6699cc;">=</span> chartData<span style="color:#6699cc;">.</span>ChartDataWorkbook<span style="color:#6699cc;">.</span>GetCell(defaultWorksheetIndex, row, yCol, data<span style="color:#6699cc;">.</span>Y);
            <span style="color:#cc99cc;">var</span> dataPoint <span style="color:#6699cc;">=</span> chartData<span style="color:#6699cc;">.</span>Series[<span style="color:#f99157;">0</span>]<span style="color:#6699cc;">.</span>DataPoints<span style="color:#6699cc;">.</span>AddDataPointForScatterSeries(xValueCell, yValueCell);

            <span style="color:#cc99cc;">var</span> categoryDataCell <span style="color:#6699cc;">=</span> chartData<span style="color:#6699cc;">.</span>ChartDataWorkbook<span style="color:#6699cc;">.</span>GetCell(defaultWorksheetIndex, row, categoryCol, data<span style="color:#6699cc;">.</span>Category);
            <span style="color:#cc99cc;">var</span> category <span style="color:#6699cc;">=</span> chartData<span style="color:#6699cc;">.</span>Categories<span style="color:#6699cc;">.</span>Add(categoryDataCell);

            row <span style="color:#6699cc;">+=</span> <span style="color:#f99157;">1</span>;
        }
        
        <span style="color:#747369;">//Changing the chart series marker</span>
        chartSeries<span style="color:#6699cc;">.</span>Marker<span style="color:#6699cc;">.</span>Size <span style="color:#6699cc;">=</span> <span style="color:#f99157;">10</span>;
        chartSeries<span style="color:#6699cc;">.</span>Marker<span style="color:#6699cc;">.</span>Symbol <span style="color:#6699cc;">=</span> <span style="color:#66cccc;">MarkerStyleType</span><span style="color:#6699cc;">.</span>Diamond;

        <span style="color:#cc99cc;">foreach</span> (<span style="color:#cc99cc;">IDataLabel</span> label <span style="color:#cc99cc;">in</span> chartSeries<span style="color:#6699cc;">.</span>Labels)
        {
            <span style="color:#cc99cc;">IDataLabelFormat</span> FormatLabel <span style="color:#6699cc;">=</span> label<span style="color:#6699cc;">.</span>DataLabelFormat;
            FormatLabel<span style="color:#6699cc;">.</span>ShowCategoryName <span style="color:#6699cc;">=</span> <span style="color:#cc99cc;">true</span>;
            FormatLabel<span style="color:#6699cc;">.</span>ShowSeriesName <span style="color:#6699cc;">=</span> <span style="color:#cc99cc;">true</span>;
            FormatLabel<span style="color:#6699cc;">.</span>ShowValue <span style="color:#6699cc;">=</span> <span style="color:#cc99cc;">true</span>;
        }
        
        pres<span style="color:#6699cc;">.</span>Save(<span style="color:#99cc99;">"C:\\temp\\AsposeScatterChart - ClearedWorksheet.pptx"</span>, <span style="color:#66cccc;">SaveFormat</span><span style="color:#6699cc;">.</span>Pptx);
    }</pre></div><div><br></div><div><br></div><div>I've also attached an example chart where I've explicitly set the label text on each datapoint. However, this clearly breaks the links to the underlying data on the worksheet. Is this the only way to do it? (Code follows)</div><div><br></div><div><pre style="font-family:Consolas;font-size:13;color:#d3d0c8;background:#272727;">        <span style="color:#cc99cc;">public</span> <span style="color:#cc99cc;">static</span> <span style="color:#cc99cc;">void</span> testScatterCleanWithLabels()
    {
        <span style="color:#ffcc66;">Presentation</span> pres <span style="color:#6699cc;">=</span> <span style="color:#cc99cc;">new</span> <span style="color:#ffcc66;">Presentation</span>();

        <span style="color:#cc99cc;">ISlide</span> slide <span style="color:#6699cc;">=</span> pres<span style="color:#6699cc;">.</span>Slides[<span style="color:#f99157;">0</span>];

        <span style="color:#747369;">//Creating the default chart</span>
        <span style="color:#cc99cc;">IChart</span> chart <span style="color:#6699cc;">=</span> slide<span style="color:#6699cc;">.</span>Shapes<span style="color:#6699cc;">.</span>AddChart(<span style="color:#66cccc;">ChartType</span><span style="color:#6699cc;">.</span>ScatterWithMarkers, <span style="color:#f99157;">0</span>, <span style="color:#f99157;">0</span>, <span style="color:#f99157;">400</span>, <span style="color:#f99157;">400</span>);

        <span style="color:#747369;">//Getting the default chart data worksheet index</span>
        <span style="color:#cc99cc;">int</span> defaultWorksheetIndex <span style="color:#6699cc;">=</span> <span style="color:#f99157;">0</span>;

        <span style="color:#747369;">//Accessing the chart data worksheet</span>
        <span style="color:#cc99cc;">IChartDataWorkbook</span> fact <span style="color:#6699cc;">=</span> chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>ChartDataWorkbook;

        <span style="color:#747369;">//Delete demo series</span>
        <span style="color:#cc99cc;">foreach</span> (<span style="color:#cc99cc;">var</span> series <span style="color:#cc99cc;">in</span> chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>Series)
        {
            series<span style="color:#6699cc;">.</span>DataPoints<span style="color:#6699cc;">.</span>Clear();
        }
        chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>Series<span style="color:#6699cc;">.</span>Clear();
        chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>Categories<span style="color:#6699cc;">.</span>Clear();
        

        <span style="color:#747369;">//Add new series</span>
        <span style="color:#cc99cc;">var</span> seriesNameRow <span style="color:#6699cc;">=</span> <span style="color:#f99157;">0</span>;
        <span style="color:#cc99cc;">var</span> initialColumnForSeries <span style="color:#6699cc;">=</span> <span style="color:#f99157;">0</span>;

        <span style="color:#cc99cc;">var</span> seriesNameCell <span style="color:#6699cc;">=</span> fact<span style="color:#6699cc;">.</span>GetCell(defaultWorksheetIndex, seriesNameRow, initialColumnForSeries, <span style="color:#99cc99;">"Scatter Values"</span>);
        chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>Series<span style="color:#6699cc;">.</span>Add(seriesNameCell, chart<span style="color:#6699cc;">.</span>Type);


        <span style="color:#747369;">//Take first chart series</span>
        <span style="color:#cc99cc;">var</span> chartSeries <span style="color:#6699cc;">=</span> chart<span style="color:#6699cc;">.</span>ChartData<span style="color:#6699cc;">.</span>Series[<span style="color:#f99157;">0</span>];

        <span style="color:#cc99cc;">var</span> mySeriesData <span style="color:#6699cc;">=</span> <span style="color:#cc99cc;">new</span> []
                             {
                                 <span style="color:#cc99cc;">new</span> { X <span style="color:#6699cc;">=</span> <span style="color:#f99157;">1.0</span>, Y <span style="color:#6699cc;">=</span> <span style="color:#f99157;">2.0</span>, Category <span style="color:#6699cc;">=</span> <span style="color:#99cc99;">"foo"</span>},
                                 <span style="color:#cc99cc;">new</span> { X <span style="color:#6699cc;">=</span> <span style="color:#f99157;">2.0</span>, Y <span style="color:#6699cc;">=</span> <span style="color:#f99157;">2.2</span>, Category <span style="color:#6699cc;">=</span> <span style="color:#99cc99;">"baz"</span>},
                                 <span style="color:#cc99cc;">new</span> { X <span style="color:#6699cc;">=</span> <span style="color:#f99157;">0.5</span>, Y <span style="color:#6699cc;">=</span> <span style="color:#f99157;">1.2</span>, Category <span style="color:#6699cc;">=</span> <span style="color:#99cc99;">"argh"</span>},
                                 <span style="color:#cc99cc;">new</span> { X <span style="color:#6699cc;">=</span> <span style="color:#f99157;">2.5</span>, Y <span style="color:#6699cc;">=</span> <span style="color:#f99157;">2.0</span>, Category <span style="color:#6699cc;">=</span> <span style="color:#99cc99;">"green"</span>},
                                 <span style="color:#cc99cc;">new</span> { X <span style="color:#6699cc;">=</span> <span style="color:#f99157;">5.0</span>, Y <span style="color:#6699cc;">=</span> <span style="color:#f99157;">3.0</span>, Category <span style="color:#6699cc;">=</span> <span style="color:#99cc99;">"knee"</span>}
                             };

        <span style="color:#cc99cc;">int</span> row <span style="color:#6699cc;">=</span> <span style="color:#f99157;">1</span>;
        <span style="color:#cc99cc;">int</span> xCol <span style="color:#6699cc;">=</span> <span style="color:#f99157;">1</span>, yCol <span style="color:#6699cc;">=</span> <span style="color:#f99157;">2</span>, categoryCol <span style="color:#6699cc;">=</span> <span style="color:#f99157;">0</span>;

        <span style="color:#cc99cc;">foreach</span> (<span style="color:#cc99cc;">var</span> data <span style="color:#cc99cc;">in</span> mySeriesData)
        {
            <span style="color:#cc99cc;">var</span> chartData <span style="color:#6699cc;">=</span> chartSeries<span style="color:#6699cc;">.</span>Chart<span style="color:#6699cc;">.</span>ChartData;


            <span style="color:#cc99cc;">var</span> xValueCell <span style="color:#6699cc;">=</span> chartData<span style="color:#6699cc;">.</span>ChartDataWorkbook<span style="color:#6699cc;">.</span>GetCell(defaultWorksheetIndex, row, xCol, data<span style="color:#6699cc;">.</span>X);
            <span style="color:#cc99cc;">var</span> yValueCell <span style="color:#6699cc;">=</span> chartData<span style="color:#6699cc;">.</span>ChartDataWorkbook<span style="color:#6699cc;">.</span>GetCell(defaultWorksheetIndex, row, yCol, data<span style="color:#6699cc;">.</span>Y);
            <span style="color:#cc99cc;">var</span> dataPoint <span style="color:#6699cc;">=</span> chartData<span style="color:#6699cc;">.</span>Series[<span style="color:#f99157;">0</span>]<span style="color:#6699cc;">.</span>DataPoints<span style="color:#6699cc;">.</span>AddDataPointForScatterSeries(xValueCell, yValueCell);

            <span style="color:#cc99cc;">var</span> categoryDataCell <span style="color:#6699cc;">=</span> chartData<span style="color:#6699cc;">.</span>ChartDataWorkbook<span style="color:#6699cc;">.</span>GetCell(defaultWorksheetIndex, row, categoryCol, data<span style="color:#6699cc;">.</span>Category);
            <span style="color:#cc99cc;">var</span> category <span style="color:#6699cc;">=</span> chartData<span style="color:#6699cc;">.</span>Categories<span style="color:#6699cc;">.</span>Add(categoryDataCell);

            dataPoint<span style="color:#6699cc;">.</span>Label<span style="color:#6699cc;">.</span>AsIOverridableText<span style="color:#6699cc;">.</span>TextFrameForOverriding<span style="color:#6699cc;">.</span>Text <span style="color:#6699cc;">=</span> data<span style="color:#6699cc;">.</span>Category;
            dataPoint<span style="color:#6699cc;">.</span>Label<span style="color:#6699cc;">.</span>TextFrameForOverriding<span style="color:#6699cc;">.</span>Paragraphs[<span style="color:#f99157;">0</span>]<span style="color:#6699cc;">.</span>Portions[<span style="color:#f99157;">0</span>]<span style="color:#6699cc;">.</span>PortionFormat<span style="color:#6699cc;">.</span>FontHeight <span style="color:#6699cc;">=</span> <span style="color:#f99157;">10</span>;

            row <span style="color:#6699cc;">+=</span> <span style="color:#f99157;">1</span>;
        }

        <span style="color:#747369;">//Changing the chart series marker</span>
        chartSeries<span style="color:#6699cc;">.</span>Marker<span style="color:#6699cc;">.</span>Size <span style="color:#6699cc;">=</span> <span style="color:#f99157;">10</span>;
        chartSeries<span style="color:#6699cc;">.</span>Marker<span style="color:#6699cc;">.</span>Symbol <span style="color:#6699cc;">=</span> <span style="color:#66cccc;">MarkerStyleType</span><span style="color:#6699cc;">.</span>Diamond;

        pres<span style="color:#6699cc;">.</span>Save(<span style="color:#99cc99;">"C:\\temp\\AsposeScatterChart - Labeled.pptx"</span>, <span style="color:#66cccc;">SaveFormat</span><span style="color:#6699cc;">.</span>Pptx);
    }</pre></div><div><br></div><div><br></div><div><br></div><div><br></div><div><br></div><div><br></div><div><br></div><div><br></div>

mudassir.fayyaz · June 19, 2014, 3:01am

Hi,

I have observed the comments and code samples shared by you. As far as Clear() method for DataPoint Collection, I have added an issue with ID SLIDESNET-35440 as new feature request in our issue tracking system to investigate the possibility of providing the support that when Clear() method is called from DataPoint collection, it not only removes the data for points in chart series but also remove that from respective cells of chart data worksheet.

Now, coming to your question regarding displaying category name in the chart labels, I like to share that you are creating a scatter chart with marker. The scatter chart has only option to display X, Y and Series name in labels of chart series datapoints. There is no other option to display the category name in it. The only thing that you can do is to set the overridden labels with any defined text for every datapoint but that is not going to be available in chart data worksheet as chart data worksheet only hold the chart data but not the labels data. I hope this will clear the point.

Please share, if I may help you further in this regard.

Many Thanks,

mudassir.fayyaz · March 6, 2017, 3:04am

Hi Cuong,

I have observed your requirements and like to share that we have added support for setting the chart workbook data cell value as chart data label in Aspose.Slides for .NET 17.1.0. Please visit this documentation link to serve the purpose.

I hope the shared information will be helpful.

Many Thanks,

truonghoangcuong · March 6, 2017, 9:43pm

Thanks so much for your quick reply. It’s so helpful on my side

Adnan.Ahmad · March 7, 2017, 2:26am

Hi Cuong,

You are very welcome.

Best Regards,

aspose.notifier · July 26, 2019, 6:47pm

The issues you have found earlier (filed as SLIDESNET-35440) have been fixed in this update.