[Java] Creat a Chart Using Excel Data in PowerPoint documents

This tutorial will demonstrate how to create a chart using the data from an existing Excel document in a PowerPoint document. It’s worth mentioning that I used a free third-party library called Free Spire.Office for Java to accomplish the function above.

About Free Spire.Office for Java

Free Spire.Office for Java is a combination of all free Java APIs that are offered by E-iceblue. Developers can use it to perform a wide range of office document operations simply and efficiently within Java applications. Meanwhile, it doesn't require Microsoft Office, Adobe Acrobat or any other third-party libraries to be installed on system.

Add Spire.Office.jar

Before running codes, you need add Spire.Office.jar in the library to IDEA. You can download the package from the link, find the Jar file in the “lib” folder and then manually add it to IDEA. Of course you can directly refer to it by using the following Maven configuration.

<repositories>
<repository>
<id>com.e-iceblue</id>
<url>http://repo.e-iceblue.cn/repository/maven-public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.office.free</artifactId>
<version>4.3.0</version>
</dependency>
</dependencies>

Using the code

Below is a screenshot of the Excel document:

import com.spire.presentation.FileFormat;
import com.spire.presentation.Presentation;
import com.spire.presentation.SlideSizeType;
import com.spire.presentation.charts.ChartStyle;
import com.spire.presentation.charts.ChartType;
import com.spire.presentation.charts.IChart;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import java.awt.geom.Rectangle2D;

public class CreateChartFromExcelData {
    public static void main(String[] args) throws Exception {
        //Create a Presentation object
        Presentation presentation = new Presentation();
        presentation.getSlideSize().setType(SlideSizeType.SCREEN_16_X_9);

        //Add a clustered column chart to slide
        Rectangle2D rect = new Rectangle2D.Float(200, 100, 550, 320);
        IChart chart = presentation.getSlides().get(0).getShapes().appendChart(ChartType.COLUMN_CLUSTERED,rect);

        //Clear the default dummy data
        chart.getChartData().clear(0,0,5,5 );

        //Load an existing Excel file to Workbook object
        Workbook wb = new Workbook();
        wb.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Import data from the sheet to chart table
        for (int r = 0; r < sheet.getAllocatedRange().getRowCount(); r++)
        {
            for (int c = 0; c < sheet.getAllocatedRange().getColumnCount(); c++)
            {
                chart.getChartData().get(r,c).setValue(sheet.getCellRange(r+1, c+1).getValue2());
            }
        }

        //Add chart title
        chart.getChartTitle().getTextProperties().setText("Male/Female Ratio Per Grade");
        chart.getChartTitle().getTextProperties().isCentered(true);
        chart.getChartTitle().setHeight(25f);
        chart.hasTitle(true);

        //Set the series label
        chart.getSeries().setSeriesLabel(chart.getChartData().get("B1","C1"));

        //Set the category labels
        chart.getCategories().setCategoryLabels(chart.getChartData().get("A2","A4"));

        //Set the series values
        chart.getSeries().get(0).setValues(chart.getChartData().get("B2","B4"));
        chart.getSeries().get(1).setValues(chart.getChartData().get("C2", "C4"));

        //Apply built-in chart style
        chart.setChartStyle(ChartStyle.STYLE_11);

        //Set overlap
        chart.setOverLap(-50);

        //Set gap width
        chart.setGapWidth(200);

        //Save to file
        presentation.saveToFile("output/CreateChartFromExcelData.pptx", FileFormat.PPTX_2013);
    }
}

Output