DEV Community

CodeSharing
CodeSharing

Posted on

How to Create Excel Column Chart in Java

#java #columnchart #excel

In Excel documents, column chart is one of the most intuitive way to display data, and it makes the comparison between data is clear at a glance. In this article, I will share how to create Excel column chart ( including Stacked Column Chart and Clustered Column Chart) by using Free Spire.XLS for Java.

See also previous articles of how to create:
◆ Pie Chart/Doughnut Chart
◆ Scatter Chart

Installation
Method 1: Download the Free Spire.XLS for Java and unzip it. Then add the Spire.Xls.jar file to your project as dependency.

Method 2: You can also add the jar dependency to maven project by adding the following configurations to the pom.xml.

<repositories>
        <repository>
            <id>com.e-iceblue</id>
            <name>e-iceblue</name>
            <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>
        </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls.free</artifactId>
        <version>3.9.1</version>
    </dependency>
</dependencies>
Enter fullscreen mode Exit fullscreen mode

【Example 1】Generate Stacked Column Chart:

import com.spire.xls.*;
import com.spire.xls.charts.*;
import java.awt.*;

public class ColumnChart{
    public static void main(String[] args) {
        executeStackedColumn(true);
    }
    private static void executeStackedColumn(boolean is3D)
    {
        //Create a Workbook
        Workbook workbook = new Workbook();

        //Get the first sheet and set its name
        Worksheet sheet = workbook.getWorksheets().get(0);
        sheet.setName("StackedColumn");

        //Set chart data
        createChartData(sheet);

        //Add a chart
        Chart chart = sheet.getCharts().add();

        //Set region of chart data
        chart.setDataRange(sheet.getCellRange("A1:C5"));
        chart.setSeriesDataFromRange(false);

        //Set position of chart
        chart.setLeftColumn(1);
        chart.setTopRow(6);
        chart.setRightColumn(11);
        chart.setBottomRow(29);

        if (is3D)
        {
            chart.setChartType(ExcelChartType.Column3DStacked);
        }
        else
        {
            chart.setChartType(ExcelChartType.ColumnStacked);
        }

        //Chart title
        chart.setChartTitle("Sales market by country");
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(12);

        //Chart Axis
        chart.getPrimaryCategoryAxis().setTitle("Country");
        chart.getPrimaryCategoryAxis().getFont().isBold(true);
        chart.getPrimaryCategoryAxis().getTitleArea().isBold(true);

        chart.getPrimaryValueAxis().setTitle("Sales ($)");
        chart.getPrimaryValueAxis().hasMajorGridLines(false);
        chart.getPrimaryValueAxis().setMinValue(1000);
        chart.getPrimaryValueAxis().getTitleArea().isBold(true);
        chart.getPrimaryValueAxis().getTitleArea().setTextRotationAngle(90);

        ChartSeries series = chart.getSeries();
        for (int i = 0;i < series.size();i++)
        {
            ChartSerie cs = series.get(i);
            cs.getFormat().getOptions().isVaryColor(true);
            cs.getDataPoints().getDefaultDataPoint().getDataLabels().hasValue(true);
        }

        //Chart legend
        chart.getLegend().setPosition(LegendPositionType.Top);

        //Save the result file
        workbook.saveToFile("StackedColumn.xlsx",ExcelVersion.Version2010);

    }

    private static void createChartData(Worksheet sheet)
    {
        //Set value of specified cell
        sheet.getCellRange("A1").setValue("Country");
        sheet.getCellRange("A2").setValue("Cuba");
        sheet.getCellRange("A3").setValue("Mexico");
        sheet.getCellRange("A4").setValue("France");
        sheet.getCellRange("A5").setValue("German");

        sheet.getCellRange("B1").setValue("Jun");
        sheet.getCellRange("B2").setNumberValue(6000);
        sheet.getCellRange("B3").setNumberValue(8000);
        sheet.getCellRange("B4").setNumberValue(9000);
        sheet.getCellRange("B5").setNumberValue(8500);

        sheet.getCellRange("C1").setValue("Aug");
        sheet.getCellRange("C2").setNumberValue(3000);
        sheet.getCellRange("C3").setNumberValue(2000);
        sheet.getCellRange("C4").setNumberValue(2300);
        sheet.getCellRange("C5").setNumberValue(4200);

        //Set cell style
        sheet.getCellRange("A1:C1").setRowHeight(15);
        sheet.getCellRange("A1:C1").getCellStyle().setColor(Color.darkGray);
        sheet.getCellRange("A1:C1").getCellStyle().getExcelFont().setColor(Color.white);
        sheet.getCellRange("A1:C1").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
        sheet.getCellRange("A1:C1").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
        sheet.getCellRange("B2:C5").getCellStyle().setNumberFormat("\"$\"#,##0");
    }
}
Enter fullscreen mode Exit fullscreen mode

Output:
Alt Text

【Example 2】Generate Clustered Column Chart:

import com.spire.xls.*;
import com.spire.xls.charts.*;
import java.awt.*;

public class ColumnChart {
    public static void main(String[] args) {
        executeClusteredColumn(true);
    }
    private static void executeClusteredColumn(boolean is3D)
    {
        //Create a Workbook
        Workbook workbook = new Workbook();

        //Get the first sheet and set its name
        Worksheet sheet = workbook.getWorksheets().get(0);
        sheet.setName("ClusteredColumn");

        //Set chart data
        createChartData(sheet);

        //Add a chart
        Chart chart = sheet.getCharts().add();

        //Set region of chart data
        chart.setDataRange(sheet.getCellRange("A1:C5"));
        chart.setSeriesDataFromRange(false);

        //Set position of chart
        chart.setLeftColumn(1);
        chart.setTopRow(6);
        chart.setRightColumn(11);
        chart.setBottomRow(29);

        if (is3D)
        {
            chart.setChartType(ExcelChartType.Column3DClustered);
        }
        else
        {
            chart.setChartType(ExcelChartType.ColumnClustered);
        }

        //Chart title
        chart.setChartTitle("Sales market by country");
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(12);

        //Chart Axis
        chart.getPrimaryCategoryAxis().setTitle("Country");
        chart.getPrimaryCategoryAxis().getFont().isBold(true);
        chart.getPrimaryCategoryAxis().getTitleArea().isBold(true);

        chart.getPrimaryValueAxis().setTitle("Sales ($)");
        chart.getPrimaryValueAxis().hasMajorGridLines(false);
        chart.getPrimaryValueAxis().setMinValue(1000);
        chart.getPrimaryValueAxis().getTitleArea().isBold(true);
        chart.getPrimaryValueAxis().getTitleArea().setTextRotationAngle(90);

        ChartSeries series = chart.getSeries();
        for (int i = 0;i < series.size();i++)
        {
            ChartSerie cs = series.get(i);
            cs.getFormat().getOptions().isVaryColor(true);
            cs.getDataPoints().getDefaultDataPoint().getDataLabels().hasValue(true);
        }

        //Chart legend
        chart.getLegend().setPosition(LegendPositionType.Top);

        //Save the result file
        workbook.saveToFile("ClusteredColumn.xlsx",ExcelVersion.Version2010);

    }

    private static void createChartData(Worksheet sheet)
    {
        //Set value of specified cell
        sheet.getCellRange("A1").setValue("Country");
        sheet.getCellRange("A2").setValue("Cuba");
        sheet.getCellRange("A3").setValue("Mexico");
        sheet.getCellRange("A4").setValue("France");
        sheet.getCellRange("A5").setValue("German");

        sheet.getCellRange("B1").setValue("Jun");
        sheet.getCellRange("B2").setNumberValue(6000);
        sheet.getCellRange("B3").setNumberValue(8000);
        sheet.getCellRange("B4").setNumberValue(9000);
        sheet.getCellRange("B5").setNumberValue(8500);

        sheet.getCellRange("C1").setValue("Aug");
        sheet.getCellRange("C2").setNumberValue(3000);
        sheet.getCellRange("C3").setNumberValue(2000);
        sheet.getCellRange("C4").setNumberValue(2300);
        sheet.getCellRange("C5").setNumberValue(4200);

        //Set cell style
        sheet.getCellRange("A1:C1").setRowHeight(15);
        sheet.getCellRange("A1:C1").getCellStyle().setColor(Color.darkGray);
        sheet.getCellRange("A1:C1").getCellStyle().getExcelFont().setColor(Color.white);
        sheet.getCellRange("A1:C1").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
        sheet.getCellRange("A1:C1").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
        sheet.getCellRange("B2:C5").getCellStyle().setNumberFormat("\"$\"#,##0");
    }
}
Enter fullscreen mode Exit fullscreen mode

Output:
Alt Text

Top comments (0)

Read next

crawlbase profile image

How to Bypass CAPTCHAs in Web Scraping 2024

Crawlbase -

anogneva profile image

PVS-Studio extension for Visual Studio Code: searching for errors in Java code

Anastasiia Ogneva -

masanori_msl profile image

Try Micronaut and Doma2

Masui Masanori -

2020nani profile image

Saga Pattern in Microservices

Hernani Almeida -