Introduction

In this article, we will explore how to create dynamic charts in Apache POI using Groovy. Apache POI is a library that allows you to manipulate Microsoft Office documents, including Excel spreadsheets. By using Apache POI along with Groovy, we can create and customize charts in Excel dynamically.

Prerequisites

Before we begin, make sure you have the following prerequisites:

1. Java Development Kit (JDK) installed on your system
2. Apache POI library added to your project dependencies
3. Groovy installed on your system

Creating a Simple Excel Spreadsheet

First, let's create a simple Excel spreadsheet using Apache POI. We will use this spreadsheet to add our chart later on.

import org.apache.poi.ss.usermodel.*
import org.apache.poi.xssf.usermodel.*

def workbook = new XSSFWorkbook()
def sheet = workbook.createSheet("DataSheet")

// Create some data
def data = [
    [1, 10],
    [2, 20],
    [3, 30],
    [4, 40],
    [5, 50]
]

// Add data to the spreadsheet
data.eachWithIndex { rowData, rowIndex ->
    def row = sheet.createRow(rowIndex)
    rowData.eachWithIndex { cellData, columnIndex ->
        def cell = row.createCell(columnIndex)
        cell.setCellValue(cellData)
    }
}

// Save the workbook to a file
def file = new File("data.xlsx")
def fileOutputStream = new FileOutputStream(file)
workbook.write(fileOutputStream)
fileOutputStream.close()

In the above code, we first import the necessary classes from Apache POI to work with Excel. We then create an instance of XSSFWorkbook, which represents an Excel workbook. We create a sheet named "DataSheet" within the workbook.

Next, we define some data in a 2D array and iterate over it to add the data to the spreadsheet. Each row in the array represents a row in the spreadsheet, and each cell value represents a cell in the spreadsheet.

Finally, we save the workbook to a file named "data.xlsx" using FileOutputStream.

Adding a Chart to the Spreadsheet

Now that we have our simple spreadsheet ready, let's add a chart to it using Apache POI.

// Load the existing workbook
def existingWorkbook = new XSSFWorkbook(new FileInputStream("data.xlsx"))

// Get the sheet
def sheet = existingWorkbook.getSheet("DataSheet")

// Create a drawing canvas on the sheet
def drawing = sheet.createDrawingPatriarch()

// Create an anchor point for the chart
def anchor = drawing.createAnchor(0, 0, 0, 0, 4, 1, 10, 20)

// Create a chart
def chart = drawing.createChart(anchor)

// Create a category axis
def categoryAxis = chart.chartAxes.createCategoryAxis(AxisPosition.BOTTOM)
categoryAxis.crosses(AxisCrosses.MIN)

// Create a value axis
def valueAxis = chart.chartAxes.createValueAxis(AxisPosition.LEFT)
valueAxis.crosses(AxisCrosses.AUTO_ZERO)

// Create a bar chart
def barChartData = chart.barChartDataFactory.createBarChartData()

// Create data series for the chart
def series = barChartData.addSeries(categoryAxis, valueAxis)
def dataRange = sheet.getDataRange(new CellRangeAddress(0, 4, 0, 1))
series.data(dataRange)

// Set the chart title
chart.setTitleText("Sample Chart")

// Write the changes back to the workbook
existingWorkbook.write(new FileOutputStream("data.xlsx"))
existingWorkbook.close()

In the above code, we first load the existing workbook using XSSFWorkbook and FileInputStream. We then access the "DataSheet" using getSheet().

Next, we create a drawing canvas on the sheet using createDrawingPatriarch(). This canvas is used to hold the chart.

We create an anchor point for the chart using createAnchor(). The anchor point specifies the cells that the chart will occupy in the spreadsheet. In this example, the chart will start at cell E2 and span 10 columns and 20 rows.

We create a chart using createChart() and specify the anchor point for the chart.

We create a category axis and a value axis using createCategoryAxis() and createValueAxis() respectively. These axes will be used to label the chart.

We create a bar chart using createBarChartData().

Next, we create a data series for the chart using addSeries(). We specify the category axis and value axis for the series. We also set the data range for the series using data() and specify the range of cells containing the data.

We set the chart title using setTitleText().

Finally, we write the changes back to the workbook using write() and close the workbook using close().

Conclusion

In this article, we have learned how to create dynamic charts in Apache POI using Groovy. We started by creating a simple Excel spreadsheet using Apache POI. Then, we added a chart to the spreadsheet by creating a drawing canvas, anchor point, and various chart elements.

By leveraging the power of Apache POI and Groovy, we can now create and customize charts in Excel dynamically, opening up a world of possibilities for data analysis and visualization.