Get relevant cells of ChartDataWorkbook

Aspose.Slides Product Family
1

I have a PPTX file with a chart. So there is automatically an embedded excel file holding the values and maybe even formulas. I’m able to get the corresponding excel by

com.aspose.slides.IChartData.getChartDataWorkbook()

, but then I don’t know how to get the cells, that are relevant for the corresponding chart. How can I achieve this?

2

@fbu,

I suggest you to please visit the following example code where by you can see example to access cells via ChartDataWorkbook.

Presentation pres = new Presentation();

ISlide slide = pres.getSlides().get_Item(0);

// Creating the default chart
IChart chart = slide.getShapes().addChart(ChartType.ClusteredColumn, 0, 0, 600, 400);

// Getting the default chart data worksheet index
int defaultWorksheetIndex = 0;

// Getting the chart data worksheet
IChartDataWorkbook fact = chart.getChartData().getChartDataWorkbook();

// Delete demo series
chart.getChartData().getSeries().clear();



// Adding new categories
chart.getChartData().getCategories().add(fact.getCell(defaultWorksheetIndex, 1, 0, "Adeeco Group AG"));
chart.getChartData().getCategories().add(fact.getCell(defaultWorksheetIndex, 2, 0, "Group Cnt AG"));
chart.getChartData().getCategories().add(fact.getCell(defaultWorksheetIndex, 3, 0, "Insperity, Inc"));
chart.getChartData().getCategories().add(fact.getCell(defaultWorksheetIndex, 4, 0, "Kelly Services, Inc"));
chart.getChartData().getCategories().add(fact.getCell(defaultWorksheetIndex, 5, 0, "Manpower Group, Inc"));
chart.getChartData().getCategories().add(fact.getCell(defaultWorksheetIndex, 6, 0, "RandStand Holding NV"));


// Add new series
chart.getChartData().getSeries().add(fact.getCell(defaultWorksheetIndex, 0, 1, "Sales (in Mn $)"), chart.getType());
//chart.getChartData().getSeries().add(fact.getCell(defaultWorksheetIndex, 0, 2, "Series 2"), chart.getType());


// Take first chart series
IChartSeries series = chart.getChartData().getSeries().get_Item(0);
// Now populating series data
series.getDataPoints().addDataPointForBarSeries(fact.getCell(defaultWorksheetIndex, 1, 1, 24000));
series.getDataPoints().addDataPointForBarSeries(fact.getCell(defaultWorksheetIndex, 2, 1, 3000));
series.getDataPoints().addDataPointForBarSeries(fact.getCell(defaultWorksheetIndex, 3, 1, 3200));
series.getDataPoints().addDataPointForBarSeries(fact.getCell(defaultWorksheetIndex, 4, 1, 7000));
series.getDataPoints().addDataPointForBarSeries(fact.getCell(defaultWorksheetIndex, 5, 1, 19000));
series.getDataPoints().addDataPointForBarSeries(fact.getCell(defaultWorksheetIndex, 6, 50,21000));

// Setting fill color for series
series.getFormat().getFill().setFillType(FillType.Solid);
series.getFormat().getFill().getSolidFillColor().setColor(Color.CYAN);


chart.getChartData().getSeries().add(fact.getCell(defaultWorksheetIndex, 0, 2, "Profit Margin (%)"), ChartType.Line);
series = chart.getChartData().getSeries().get_Item(1);
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 1, 2, 0.04));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 2, 2, 3.79));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 3, 2, 1.48));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 4, 2, 0.95));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 5, 2, 2.17));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 6, 2, 2.7));
series.setPlotOnSecondAxis(true);


// Changing the chart series marker
series.getMarker().setSize(10);
series.getMarker().setSymbol(MarkerStyleType.Square);
series.getMarker().getFormat().getFill().setFillType((byte)FillType.Solid);
series.getMarker().getFormat().getFill().getSolidFillColor().setColor(Color.MAGENTA);

series.getMarker().getFormat().getLine().getFillFormat().setFillType((byte)FillType.Solid);
series.getMarker().getFormat().getLine().getFillFormat().getSolidFillColor().setColor(Color.MAGENTA);
series.getLabels().getDefaultDataLabelFormat().setShowValue(true);

chart.getAxes().getSecondaryHorizontalAxis().setVisible(false);

chart.getAxes().getVerticalAxis().getMajorGridLinesFormat().getLine().getFillFormat().setFillType((byte)FillType.NoFill);
chart.getAxes().getVerticalAxis().getMinorGridLinesFormat().getLine().getFillFormat().setFillType((byte)FillType.NoFill);
chart.getAxes().getSecondaryVerticalAxis().getMajorGridLinesFormat().getLine().getFillFormat().setFillType((byte)FillType.NoFill);
chart.getAxes().getSecondaryVerticalAxis().getMinorGridLinesFormat().getLine().getFillFormat().setFillType((byte)FillType.NoFill);



chart.getAxes().getVerticalAxis().setTitle(true);

chart.getAxes().getVerticalAxis().getTitle().addTextFrameForOverriding("");
IPortion valtitle = chart.getAxes().getVerticalAxis().getTitle().getTextFrameForOverriding().getParagraphs()
                .get_Item(0).getPortions().get_Item(0);
valtitle.setText(chart.getChartData().getSeries().get_Item(0).getName().toString());
chart.getAxes().getVerticalAxis().getTitle().setOverlay(false);

IAxis axis=chart.getAxes().getVerticalAxis();
axis.setAutomaticMajorUnit(false);
axis.setAutomaticMaxValue(false);
axis.setAutomaticMinValue(false);
axis.setAutomaticMinorUnit(false);
axis.setMinValue(0);
axis.setMaxValue(28000);
axis.setMajorUnit(4000);
axis.setMinorUnit(0);
axis.setMajorTickMark(TickMarkType.Inside);
axis.setMinorTickMark(TickMarkType.None);
axis.setDisplayUnit(DisplayUnitType.None);



chart.getAxes().getSecondaryVerticalAxis().setTitle(true);//.getTitle().
boolean is=chart.getAxes().getSecondaryVerticalAxis().hasTitle();

chart.getAxes().getSecondaryVerticalAxis().getTitle().addTextFrameForOverriding("");
valtitle = chart.getAxes().getSecondaryVerticalAxis().getTitle().getTextFrameForOverriding().getParagraphs()
                .get_Item(0).getPortions().get_Item(0);
valtitle.setText(chart.getChartData().getSeries().get_Item(1).getName().toString());
chart.getAxes().getSecondaryVerticalAxis().getTitle().setOverlay(false);



chart.getAxes().getHorizontalAxis().getMajorGridLinesFormat().getLine().getFillFormat().setFillType((byte)FillType.NoFill);
chart.getAxes().getHorizontalAxis().getMinorGridLinesFormat().getLine().getFillFormat().setFillType((byte)FillType.NoFill);
chart.getAxes().getSecondaryHorizontalAxis().getMajorGridLinesFormat().getLine().getFillFormat().setFillType((byte)FillType.NoFill);
chart.getAxes().getSecondaryHorizontalAxis().getMinorGridLinesFormat().getLine().getFillFormat().setFillType((byte)FillType.NoFill);


pres.save("C:\\Aspose Data\\AsposeScatterChart.pptx", SaveFormat.Pptx);
3

Iterating over the ChartSeries of a chart did the trick. There is no need to open the workbook directly (that’s great!). But now I’m facing the problem, that the workbook is not included anymore in the resulting PPTX file. I’m replacing the workbook data with the following code snipped:

private void replaceChart(Chart chart) {
sysLogger.debug(chart.getName());
IChartData chartData = chart.getChartData();

chartData.getSeries().forEach(series -> {
  if (series instanceof ChartSeries) {
    ChartSeries chartSeries = (ChartSeries) series;
    chartSeries.getDataPoints().forEach(dataPoint -> {
      IChartDataCell cell = dataPoint.getValue().getAsCell();
      String newValue = getNewValue(cell.getValue);
      cell.setValue(newValue);
      }
    });
  }
});

}

Please see the attached PPTX file, that is saved afterwards: missing workbook.zip (139.1 KB)
In the template, the workbook is present. After setting new values, the workbook is missing. Am I doing something wrong?

4

@fbu,

I have observed your following comments and have not been able to observe your requirements. Can you please elaborate the requirements that what is not being achieved using Aspose.Slides.

5

Of course. In our application we are processing the “missing workbook template.pptx” file. While processing, we change the values, that are displayed in the chart. E.g. the string “${vgl#j3#Materialaufwandsquote}” gets replaced by “106.5”. I’m trying to replace the value in the IChartDataCell instance (see the code nipped in my previous comment). The file “missing workbook.pptx” is the resulting file when I save the “com.aspose.slides.Presentation” instance after replacing the values. If you open the “missing workbook.pptx” file and try to open the linked, embedded excel file, that contains the values of the chart, then Excel tells you, that the Excel file is not included in the PowerPoint file.

Going deeper :wink: into the PowerPoint files (unzip them)
missing workbook template.pptx:
In “chart1.xml.rels”, the chart1 is linked with “…/embeddings/Microsoft_Excel_Worksheet.xlsx”. The file exists there.
missing workbook.pptx:

(…)

I found the problem while writing these lines. The new value, that I’m setting into a cell is a complex java object - no string or primitive data type. That’s the reason why “Microsoft_Excel_Worksheet1.xlsx” of “missing workbook.pptx” can’t be opened. Thanks for your support :slight_smile:.

6

@fbu,

I have observed your comments. You meant that when you set the new formula values for chart series data point the chart failed to get edited in PowerPoint by opening its Excel sheet. The support for formula recalculation is not available in Aspose.Slides at the moment. An issue with ID SLIDESJAVA-34897 has already been added in our issue tracking system to provide the requested support. Please share your comments related to my comments in first line.

7

The problem for this thread isn’t missing formula recalculation. It’s about breaking Excel files by putting invalid values into cells. That’s what we did by changing missing workbook template.pptx into missing workbook.pptx: We set illegal values as cell value. And afterwards, the embedded Excel file couldn’t get opened by Microsoft PowerPoint / Excel.

8

@fbu,

I have observed the presentation files shared by you and able to observe the issue in generated presentation. I request you to please provide the working sample project reproducing the issue so that we may log that in our issue tracking system.

9

We discussed your request internally and we decided to don’t fulfill it. The solution of the issue won’t help us right known. So please see the last issue as solved. The solution for us is to use Aspose.Cells in a proper way and don’t set complex java objects as values into cells :slight_smile:.

10

@fbu,

Thank you for sharing your feedback.