How to Create Pivot Table in Excel using Java?

How to Create Pivot Chart from Pivot Table in Excel using Java?

Last Updated: 06 Dec, 2023

Nandini Gujral

A Pivot Chart is used to analyze data of a table with very little effort (and no formulas) and it gives you the big picture of your raw data. It allows you to analyze data using various types of graphs and layouts. It is considered to be the best chart during a business presentation that involves huge data. To add a pivot chart to an Excel worksheet, you need to use the “WorksheetChartsCollection.add” method.

Before Creating a Pivot Chart, one first needs to go through how to Create Pivot Table in Excel using Java. Now let’s discuss the steps to create a pivot chart in an Excel file in Java using Free Spire.XLS for Java API.

How to Create a Pivot Table in Excel using Java

Step 1: Load the Excel file

Workbook workbook = new Workbook()

String workbookName = “Geeks_For_Geeks.xlsx”;

workbook.loadFromFile(workbookName);

Step 2: Get the first worksheet

Worksheet sheet = workbook.getWorksheets().get(0);

Step 3: Get the first pivot table in the worksheet

IPivotTable pivotTable = sheet.getPivotTables().get(0);

Step 4: Add a clustered column chart based on the pivot table to the second worksheet

Chart chart = workbook.getWorksheets().get(1).getCharts().add(ExcelChartType.ColumnClustered, pivotTable);

Step 5: Set chart position

chart.setTopRow(2);

chart.setBottomRow(15);

Step 6: Set chart title

chart.setChartTitle(“Total”);

Step 7: Save the result file

workbook.saveToFile(workbookName, ExcelVersion.Version2013);

Let’s write a Java Program to create a pivot chart from a pivot table in a spreadsheet.

Java

import com.spire.xls.*;
import com.spire.xls.core.IPivotTable;
 
class GFG {
    public static void main(String[] args)
    {
        // Load the Excel file
        Workbook workbook = new Workbook();
        String workbookName = "Geeks_For_Geeks.xlsx";
        workbook.loadFromFile(workbookName);
 
        // Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
       
        // get the first pivot table in the worksheet
        IPivotTable pivotTable
            = sheet.getPivotTables().get(0);
 
        // Add a clustered column chart based on the pivot
        // table to the second worksheet
        Chart chart
            = workbook.getWorksheets()
                  .get(1)
                  .getCharts()
                  .add(ExcelChartType.ColumnClustered,
                       pivotTable);
       
        // Set chart position
        chart.setTopRow(2);
        chart.setBottomRow(15);
       
        // Set chart title
        chart.setChartTitle("Total");
 
        // Save the result file
        workbook.saveToFile(workbookName,
                            ExcelVersion.Version2013);
        System.out.println(workbookName
                           + " is written successfully");
    }
}

Output

On the console window when the program is successfully executed.

GeeksForGeeks.xlsx is written successfully.

Output

On the Workbook (Excel file)

Create Pivot Chart from Pivot Table in Excel using Java

Conclusion

Creating a Pivot chart from a pivot table in Excel using Java can greatly improve data analysis and visualization. By programmatically generating pivot tables and charts with Apache POI, you can automate repetitive tasks and efficiently handle large datasets. This article provides you step by step implementation of how to create a pivot chart in Excel using Java.

FAQs on How to Create Pivot Chart from Pivot Table in Excel using Java

Q1: What is the need for creating a Pivot chart from a Pivot table in Excel using Java?

Answer:

Creating a pivot chart from the pivot table using Java and Apache POI offers several advantages. It allows you to automate the process of generating Pivot charts, saving you time and effort. Java flexibility enables handling large datasets and enhancing data analysis capabilities.

Q2: How to create a Pivot chart from the pivot table in Excel using Java and Apache POI?

Answer:

After creating a Pivot table, Follow the below steps to create a Pivot Chart:

Step 1: Open the Existing Workbook with the Pivot table.

Step 2: Create a new sheet to hold the Pivot Chart.

Step 3: Define the top-left corner of the Pivot Chart on the new sheet.

Step 4: Draw the Pivot Chart using the Drawing canvas.

Step 5: Set the Pivot table as the data source for the pivot chart.

Q3: Can we customize the Pivot Chart appearance and layout using Java and Apache POI?

Answer:

Yes, you can customize the Pivot Chart appearance and layout using various methods provided by Apache POI. You can change chart titles, axis labels, colors, chart type, and many other properties to create a visually appealing Pivot chart.

Q4: Can I use other libraries or tools instead of Apache POI to create Pivot charts in Excel using Java?

Answer:

Yes, there are other libraries and tools available that allow working with Excel files and charts in Java, such as Apache POI -HSSF, Apache POI-XSSF, JExcelAPI, and Java Excel API(JXL).

Article Tags :