Data not removed from the excel sheet present in the pptx file containing the chart

Hi,


I have observed that when I delete a series/category from the chart in a pptx presentation, the data was not deleted from the embedded excel document.

The steps that I followed were as follows:
1. I cleared all the data from the chart using the following code:
ChartCategoryExCollection categoryExCol = chart.getChartData().getCategories();
List categoryToRemove = new ArrayList();
Iterator<?> itrCol = categoryExCol.iterator();
while (itrCol.hasNext()) {
ChartCategoryEx cc = (ChartCategoryEx) itrCol.next();
categoryToRemove.add(cc);
}
for(ChartCategoryEx cc : categoryToRemove){
chart.getChartData().getCategories().remove(cc);
}
ChartSeriesExCollection seriesExCol = chart.getChartData().getSeries();
List seriesToRemove = new ArrayList();
Iterator<?> itrSer = seriesExCol.iterator();
while (itrSer.hasNext()) {
ChartSeriesEx cs = (ChartSeriesEx) itrSer.next();
seriesToRemove.add(cs);
}
for(ChartSeriesEx cc : seriesToRemove){
chart.getChartData().getSeries().remove(cc);
}
chart.getChartData().getSeries().clear();
chart.getChartData().getCategories().clear();
The existing data was having 4 categories and 3 series.
2. I added the new data for 3 categories and 2 series.
3. Save the new pptx file.
4. When I open the modified pptx file the chart reflects 3 categories and 2 series correctly, but after I open the excel file by right clicking on the chart and selecting the “Edit Chart”, it is showing data for 4 categories and 3 series. Only the range(blue line) shows that it contains the first 3 categories and 2 series.
I need to completeley remove the unused data i.e. the last series and category from the excel file. Is there any api that I can use to achieve this?

Kindly find the original and the attached modified excel sheet.

Thanks,
Gaurang.

Hi Gaurang,

I have observed the requirement shared by you and like to share that when you remove chart series or category then its association with data in excel file is de-assocaited but the data remains there. When you are removing all series and categories then in that case you can use the following code sample after removing the chart series and categories in your application.

chart.getChartData().getChartDataWorkbook().clear(0);//0 is sheet index

The above code clears the data worksheet for the chart. Please share, if I may help you further in this regard.

Many Thanks,

Hi Mudassir,


That code did clear the existing data and now I am getting what is required in the excel sheet. Thank you for your help.

Thanks,
Gaurang.