java - Create Bar Chart in Excel with Apache POI

Java - Create Bar Chart in Excel with Apache POI

Creating a bar chart in Excel using Apache POI involves several steps to set up the data and chart properties correctly. Below is a detailed guide on how to achieve this:

Step-by-Step Guide

1. Add Apache POI Dependency

First, make sure you have Apache POI added to your project. If you're using Maven, include the following dependencies in your pom.xml:

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>5.3.0</version> <!-- Or the latest version --> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>5.3.0</version> <!-- Or the latest version --> </dependency>

2. Create a Workbook and Sheet

import org.apache.poi.ss.usermodel.*; public class ExcelBarChartExample { public static void main(String[] args) throws Exception { // Create a workbook Workbook workbook = new XSSFWorkbook(); // For .xlsx format // Workbook workbook = new HSSFWorkbook(); // For .xls format // Create a sheet Sheet sheet = workbook.createSheet("Bar Chart Example"); // Define data for the bar chart String[] categories = {"Category 1", "Category 2", "Category 3"}; Integer[] values = {10, 20, 15}; // Create headers Row headerRow = sheet.createRow(0); headerRow.createCell(0).setCellValue("Category"); headerRow.createCell(1).setCellValue("Value"); // Populate data for (int i = 0; i < categories.length; i++) { Row row = sheet.createRow(i + 1); row.createCell(0).setCellValue(categories[i]); row.createCell(1).setCellValue(values[i]); } // Create a drawing patriarch to hold the chart Drawing<?> drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 20); // Anchor position of the chart // Create a chart object Chart chart = drawing.createChart(anchor); // Create data sources for the chart ChartDataSource<String> category = DataSources.fromStringCellRange(sheet, new CellRangeAddress(1, categories.length, 0, 0)); ChartDataSource<Number> data = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, categories.length, 1, 1)); // Create a bar chart type ChartAxis bottomAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM); ChartAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); ChartSeries series = chart.addSeries(category, data); series.setTitle("Bar Chart"); // Write the workbook content to a file try (FileOutputStream fileOut = new FileOutputStream("bar_chart_example.xlsx")) { workbook.write(fileOut); } // Close the workbook workbook.close(); } }

Explanation

  • Workbook and Sheet: Creates a new workbook (XSSFWorkbook for .xlsx format) and a sheet named "Bar Chart Example".

  • Data Setup: Defines categories (categories) and corresponding values (values) for the bar chart.

  • Populate Data: Populates the sheet with headers and data from arrays (categories and values).

  • Chart Creation:

    • Uses Drawing and ClientAnchor to create a chart anchor position on the sheet.
    • Creates a bar chart using drawing.createChart(anchor).

  • Chart Data Sources: Uses DataSources to define data sources (category and data) for the chart based on cell ranges.

  • Chart Axis: Sets up chart axes (bottomAxis and leftAxis) for category and value axes.

  • Chart Series: Adds a series to the chart using chart.addSeries(category, data).

  • Output: Writes the workbook content to a file (bar_chart_example.xlsx) and closes the workbook.

Notes

  • Ensure you have appropriate dependencies (poi and poi-ooxml) added to your project.
  • Adjust anchor coordinates (createAnchor parameters) and chart properties (ChartAxis, ChartSeries) as needed for your specific requirements.
  • This example creates a simple bar chart. You can explore other chart types and options available in Apache POI based on your needs.

By following these steps, you can effectively create a bar chart in Excel using Apache POI in your Java application. Adjust the data and chart configurations according to your specific data and presentation requirements.

Examples

  1. How to create a simple bar chart in Excel using Apache POI in Java

    Description: Create a basic bar chart in an Excel sheet using Apache POI.

    Code:

    import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFChart; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend; import org.apache.poi.xddf.usermodel.chart.XDDFDataSource; import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource; import org.apache.poi.xddf.usermodel.chart.XDDFCategoryDataSource; import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData; import org.apache.poi.xddf.usermodel.chart.XDDFChartData; import java.io.FileOutputStream; import java.io.IOException; public class BarChartExample { public static void main(String[] args) throws IOException { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Bar Chart Example"); // Create sample data Object[][] data = { {"Category", "Value"}, {"A", 10}, {"B", 20}, {"C", 30}, {"D", 40}, }; int rowCount = 0; for (Object[] rowData : data) { XSSFRow row = sheet.createRow(rowCount++); int columnCount = 0; for (Object field : rowData) { Cell cell = row.createCell(columnCount++); if (field instanceof String) { cell.setCellValue((String) field); } else if (field instanceof Integer) { cell.setCellValue((Integer) field); } } } Drawing<?> drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 20); XSSFChart chart = (XSSFChart) drawing.createChart(anchor); XDDFChartLegend legend = chart.getOrAddLegend(); legend.setPosition(LegendPosition.TOP_RIGHT); XDDFCategoryDataSource categories = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(1, 4, 0, 0)); XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 4, 1, 1)); XDDFBarChartData dataChart = (XDDFBarChartData) chart.createData(ChartTypes.BAR, null, null); XDDFBarChartData.Series series = (XDDFBarChartData.Series) dataChart.addSeries(categories, values); series.setTitle("Values", null); chart.plot(dataChart); try (FileOutputStream fileOut = new FileOutputStream("BarChartExample.xlsx")) { workbook.write(fileOut); } workbook.close(); } }

  2. How to customize bar chart colors in Apache POI

    Description: Change the color of bars in a bar chart created with Apache POI.

    Code:

    import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFChart; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend; import org.apache.poi.xddf.usermodel.chart.XDDFDataSource; import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource; import org.apache.poi.xddf.usermodel.chart.XDDFCategoryDataSource; import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData; import org.apache.poi.xddf.usermodel.chart.XDDFChartData; import org.apache.poi.xddf.usermodel.chart.XDDFSolidFillProperties; import org.apache.poi.xddf.usermodel.chart.XDDFShapeProperties; import org.apache.poi.xddf.usermodel.chart.XDDFChartData.Series; import org.apache.poi.xddf.usermodel.XDDFColor; import java.io.FileOutputStream; import java.io.IOException; public class BarChartWithColor { public static void main(String[] args) throws IOException { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Bar Chart Color"); // Create sample data Object[][] data = { {"Category", "Value"}, {"A", 10}, {"B", 20}, {"C", 30}, {"D", 40}, }; int rowCount = 0; for (Object[] rowData : data) { XSSFRow row = sheet.createRow(rowCount++); int columnCount = 0; for (Object field : rowData) { Cell cell = row.createCell(columnCount++); if (field instanceof String) { cell.setCellValue((String) field); } else if (field instanceof Integer) { cell.setCellValue((Integer) field); } } } Drawing<?> drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 20); XSSFChart chart = (XSSFChart) drawing.createChart(anchor); XDDFChartLegend legend = chart.getOrAddLegend(); legend.setPosition(LegendPosition.TOP_RIGHT); XDDFCategoryDataSource categories = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(1, 4, 0, 0)); XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 4, 1, 1)); XDDFBarChartData dataChart = (XDDFBarChartData) chart.createData(ChartTypes.BAR, null, null); XDDFBarChartData.Series series = (XDDFBarChartData.Series) dataChart.addSeries(categories, values); series.setTitle("Values", null); chart.plot(dataChart); // Customize colors XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.BLUE)); XDDFShapeProperties properties = new XDDFShapeProperties(); properties.setFillProperties(fill); series.setShapeProperties(properties); try (FileOutputStream fileOut = new FileOutputStream("BarChartWithColor.xlsx")) { workbook.write(fileOut); } workbook.close(); } }

  3. How to add data labels to a bar chart in Apache POI

    Description: Add data labels to each bar in a bar chart created with Apache POI.

    Code:

    import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFChart; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend; import org.apache.poi.xddf.usermodel.chart.XDDFDataSource; import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource; import org.apache.poi.xddf.usermodel.chart.XDDFCategoryDataSource; import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData; import org.apache.poi.xddf.usermodel.chart.XDDFChartData; import org.apache.poi.xddf.usermodel.chart.XDDFDataLabels; import org.apache.poi.xddf.usermodel.chart.XDDFShapeProperties; import java.io.FileOutputStream; import java.io.IOException; public class BarChartWithLabels { public static void main(String[] args) throws IOException { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Bar Chart Labels"); // Create sample data Object[][] data = { {"Category", "Value"}, {"A", 10}, {"B", 20}, {"C", 30}, {"D", 40}, }; int rowCount = 0; for (Object[] rowData : data) { XSSFRow row = sheet.createRow(rowCount++); int columnCount = 0; for (Object field : rowData) { Cell cell = row.createCell(columnCount++); if (field instanceof String) { cell.setCellValue((String) field); } else if (field instanceof Integer) { cell.setCellValue((Integer) field); } } } Drawing<?> drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 20); XSSFChart chart = (XSSFChart) drawing.createChart(anchor); XDDFChartLegend legend = chart.getOrAddLegend(); legend.setPosition(LegendPosition.TOP_RIGHT); XDDFCategoryDataSource categories = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(1, 4, 0, 0)); XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 4, 1, 1)); XDDFBarChartData dataChart = (XDDFBarChartData) chart.createData(ChartTypes.BAR, null, null); XDDFBarChartData.Series series = (XDDFBarChartData.Series) dataChart.addSeries(categories, values); series.setTitle("Values", null); chart.plot(dataChart); XDDFDataLabels dataLabels = series.getDataLabels(); dataLabels.setShowValue(true); try (FileOutputStream fileOut = new FileOutputStream("BarChartWithLabels.xlsx")) { workbook.write(fileOut); } workbook.close(); } }

  4. How to change the axis labels in a bar chart using Apache POI

    Description: Modify the axis labels of a bar chart created with Apache POI.

    Code:

    import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFChart; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend; import org.apache.poi.xddf.usermodel.chart.XDDFDataSource; import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource; import org.apache.poi.xddf.usermodel.chart.XDDFCategoryDataSource; import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData; import org.apache.poi.xddf.usermodel.chart.XDDFChartData; import org.apache.poi.xddf.usermodel.chart.XDDFValueAxis; import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis; import java.io.FileOutputStream; import java.io.IOException; public class BarChartWithCustomAxis { public static void main(String[] args) throws IOException { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Bar Chart Custom Axis"); // Create sample data Object[][] data = { {"Category", "Value"}, {"A", 10}, {"B", 20}, {"C", 30}, {"D", 40}, }; int rowCount = 0; for (Object[] rowData : data) { XSSFRow row = sheet.createRow(rowCount++); int columnCount = 0; for (Object field : rowData) { Cell cell = row.createCell(columnCount++); if (field instanceof String) { cell.setCellValue((String) field); } else if (field instanceof Integer) { cell.setCellValue((Integer) field); } } } Drawing<?> drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 20); XSSFChart chart = (XSSFChart) drawing.createChart(anchor); XDDFChartLegend legend = chart.getOrAddLegend(); legend.setPosition(LegendPosition.TOP_RIGHT); XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM); bottomAxis.setTitle("Custom Category Axis"); XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT); leftAxis.setTitle("Custom Value Axis"); XDDFCategoryDataSource categories = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(1, 4, 0, 0)); XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 4, 1, 1)); XDDFBarChartData dataChart = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, leftAxis); XDDFBarChartData.Series series = (XDDFBarChartData.Series) dataChart.addSeries(categories, values); series.setTitle("Values", null); chart.plot(dataChart); try (FileOutputStream fileOut = new FileOutputStream("BarChartWithCustomAxis.xlsx")) { workbook.write(fileOut); } workbook.close(); } }

  5. How to create a horizontal bar chart in Excel using Apache POI

    Description: Create a horizontal bar chart instead of the default vertical bar chart in an Excel sheet using Apache POI.

    Code:

    import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFChart; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend; import org.apache.poi.xddf.usermodel.chart.XDDFDataSource; import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource; import org.apache.poi.xddf.usermodel.chart.XDDFCategoryDataSource; import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData; import org.apache.poi.xddf.usermodel.chart.XDDFChartData; import java.io.FileOutputStream; import java.io.IOException; public class HorizontalBarChartExample { public static void main(String[] args) throws IOException { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Horizontal Bar Chart"); // Create sample data Object[][] data = { {"Category", "Value"}, {"A", 10}, {"B", 20}, {"C", 30}, {"D", 40}, }; int rowCount = 0; for (Object[] rowData : data) { XSSFRow row = sheet.createRow(rowCount++); int columnCount = 0; for (Object field : rowData) { Cell cell = row.createCell(columnCount++); if (field instanceof String) { cell.setCellValue((String) field); } else if (field instanceof Integer) { cell.setCellValue((Integer) field); } } } Drawing<?> drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 20); XSSFChart chart = (XSSFChart) drawing.createChart(anchor); XDDFChartLegend legend = chart.getOrAddLegend(); legend.setPosition(LegendPosition.TOP_RIGHT); XDDFCategoryDataSource categories = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(1, 4, 0, 0)); XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 4, 1, 1)); XDDFBarChartData dataChart = (XDDFBarChartData) chart.createData(ChartTypes.BAR, null, null); XDDFBarChartData.Series series = (XDDFBarChartData.Series) dataChart.addSeries(categories, values); series.setTitle("Values", null); dataChart.setBarDirection(BarDirection.BAR); chart.plot(dataChart); try (FileOutputStream fileOut = new FileOutputStream("HorizontalBarChartExample.xlsx")) { workbook.write(fileOut); } workbook.close(); } }

More Tags

numeric-input iphone time pascal spring-boot ios-simulator jitpack ssrs-tablix android-testing blur

More Programming Questions

More Entertainment Anecdotes Calculators

More Trees & Forestry Calculators

More Bio laboratory Calculators

More Other animals Calculators