Saving line chart(.xlsx based) data from slide(.pptx) using apache poi

Question

I want to save the excel file(.xlsx) with data corresponding to a line chart in a powerpoint slide(.pptx) using apache poi.

Answer 1

Using current apacjhe poi 5.0.0 there is XDDFChart.importContent which imports the chart content from one XDDFChart to another. And there is XDDFChart.getWorkbook which gets the data source workbook behind that XDDFChart.

If the need is to extract a chart from PowerPoint into a Excel file, then the following needs to be done:

Get the XSLFChart from the PowerPoint slide show.

Get the data source workbook and the data source sheet behind that chart.

Create a XSSFChart in that sheet.

Import the chart content from the XSLFChart.

Save the workbook into a file.

Example:

Source PowerPoint.pptx:

Code:

import java.io.*;

import org.apache.poi.xslf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.util.Units;

public class CreateExcelXDDFChartFromPowerPointXDDFChart {

 public static void main(String[] args) throws Exception {

  String powerPointPath = "./PowerPoint.pptx";
  String excelPath = "./Excel.xlsx";

  // get the PowerPoint slide show
  XMLSlideShow slideShow = new XMLSlideShow(new FileInputStream(powerPointPath));
  // get first chart
  XSLFChart powerPointchart = slideShow.getCharts().get(0);

  // get data source workbook behind that chart
  XSSFWorkbook workbook = powerPointchart.getWorkbook();
  // get data source sheet
  XSSFSheet sheet = workbook.getSheetAt(0);

  // create the chart in Excel
  XSSFDrawing drawing = sheet.createDrawingPatriarch();
  XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 5, 0, 15, 30);
  XSSFChart excelChart = drawing.createChart(anchor);

  // get chart data from PowerPoint chart
  excelChart.importContent(powerPointchart);
 
  // Write the output to a file
  try (FileOutputStream fileOut = new FileOutputStream(excelPath)) {
   workbook.write(fileOut);
  }

  slideShow.close();
  workbook.close();
 }
}

Result Excel.xlsx:

---

To perform the other way araound. To put a Excel chart into a PowerPoint silde show, the following needs to be done:

Get the XSSFChart from the sheet in given Excel workbook.

Create create a new PowerPoint slide show or open one existent.

Create a XSLFChart in a slide of that slide show.

Import the chart content from the XSSFChart.

Save the Excel workbook as PowerPoint chart's data source. For this we need XDDFChart.saveWorkbook which writes the given XSSFWorkbook as the data source of that chart into an embedded Excel file.

Example:

Given Excel.xlsx:

Code:

import java.io.*;

import org.apache.poi.xslf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.util.Units;

public class CreatePowerPointXDDFChartFromExcelXDDFChart {

 public static void main(String[] args) throws Exception {

  String excelPath = "./Excel.xlsx";
  String powerPointPath = "./PowerPoint.pptx";

  // get the Excel workbook
  XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(excelPath));
  // get first sheet
  XSSFSheet sheet = workbook.getSheetAt(0);
  // get first chart in that sheet
  XSSFDrawing drawing = sheet.createDrawingPatriarch();
  XSSFChart excelChart = drawing.getCharts().get(0);

  // create new PowerPoint slide show
  XMLSlideShow slideShow = new XMLSlideShow();

  // create new slide
  XSLFSlide slide = slideShow.createSlide();

  // create the chart
  XSLFChart powerPointchart = slideShow.createChart();
  // add chart to slide
  slide.addChart(powerPointchart, new java.awt.geom.Rectangle2D.Double(1d*Units.EMU_PER_CENTIMETER, 1d*Units.EMU_PER_CENTIMETER, 20d*Units.EMU_PER_CENTIMETER, 15d*Units.EMU_PER_CENTIMETER));
   
  // get chart data from Excel chart
  powerPointchart.importContent(excelChart);
  // save Excel workbook as PowerPoint chart's data source
  powerPointchart.saveWorkbook(workbook);
 
  // Write the output to a file
  try (FileOutputStream fileOut = new FileOutputStream(powerPointPath)) {
   slideShow.write(fileOut);
  }

  workbook.close();
  slideShow.close();
 }
}

Resultig PowerPoint.pptx: