Images, Charts, Comments & Hyperlinks

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

Baseline date: 2026-10-09. This page was written against Apache POI 5.5.1 (the latest release on that date; the 6.0 line is announced but has not been released), Java 21 and later, Spring Boot 4.x on Spring Framework 7 and Jakarta EE 11. It covers everything in a worksheet that is not a cell value: pictures, charts, comments, hyperlinks and embedded objects. Pictures, charts, comments and embedded objects live in a separate layer of the file, the drawing, and all go through the same entry point, the drawing patriarch. Hyperlinks are plain sheet features. The example workbook is books-inventory.xlsx of the Bookshelf scenario described in the section landing page: a logo picture, a comment on an ISBN cell, hyperlinks to the publisher, and a column chart of the copies in stock per genre. The cell styles used for the link text are explained in Cell Styles, Fonts & Formats, and the workbook and sheet model in Spreadsheet Usermodel Basics. Reopening an existing file and keeping its drawings intact is the subject of Templates & Modifying Workbooks.

There is no narrative guide for charts. The Apache POI website documents pictures, comments and hyperlinks in the Spreadsheet Quick Guide, but it has no XDDF chart guide: the chart classes are described only by their Javadoc and by the programs in the poi-examples module. Every chart snippet on this page is therefore derived from BarChart, LineChart and ScatterChart in org.apache.poi.examples.xssf.usermodel and from the org.apache.poi.xddf.usermodel.chart Javadoc, adapted to the Bookshelf data, and has been compiled and run against POI 5.5.1.

The drawing layer

A worksheet stores cell data in one part of the package and everything graphical in another. In an .xlsx file the sheet part (xl/worksheets/sheet1.xml) holds rows and cells, and a <drawing> element in it points to a drawing part (xl/drawings/drawing1.xml). That part lists the pictures, charts, shapes and embedded objects of the sheet, each with an anchor that says where it sits. Each kind of object adds parts of its own:

Object OOXML parts (.xlsx) Binary equivalent (.xls)

Picture

xl/drawings/drawingN.xml (anchor) and xl/media/imageN.png (bytes), related through xl/drawings/_rels/

Escher records in the Workbook stream, with the bitmap in a BLIP record

Chart

xl/charts/chartN.xml; the data is referenced by cell range, not copied

BIFF chart records. POI reads some of them; creation is limited to a very basic bar chart (HSSFChart.createBarChart)

Comment

xl/commentsN.xml (text and author) and a legacy VML drawing xl/drawings/vmlDrawingN.vml (the box shape)

NOTE record and a text-box shape

Hyperlink

A <hyperlink> element in the sheet part; external targets are relationships in xl/worksheets/_rels/

HLINK record

Embedded object

xl/embeddings/ binary part plus a picture shown in the cell

OBJ record and an OLE2 storage

The consequence for the API is two groups of operations. Hyperlinks need no drawing. Pictures, charts, comments and embedded objects are shapes and are created through a Drawing that you obtain from the sheet. The file-format background is in File Formats: OOXML & OLE2.

The drawing patriarch

Sheet.createDrawingPatriarch() returns the top-level drawing object of the sheet. The name comes from the Escher structure of the binary format, where one container is the parent of all shapes of a sheet. Its type is Drawing<?> in the common ss.usermodel interface and XSSFDrawing (or HSSFPatriarch) in the concrete classes. The shape factories hang from it:

Method Creates

createPicture(ClientAnchor, int pictureIndex)

A picture shape from an index returned by Workbook.addPicture

createCellComment(ClientAnchor)

A comment box; attach it with Cell.setCellComment

createChart(ClientAnchor)

A chart. XSSFDrawing.createChart returns an XSSFChart that the XDDF classes configure

createAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2)

An XSSFClientAnchor with all eight values at once (on XSSFDrawing); CreationHelper.createClientAnchor creates an empty one

createObjectData(ClientAnchor, int storageId, int pictureIndex)

An embedded OLE object (low level and format-specific; see Embedded objects)

Creating the patriarch can remove existing shapes. The Javadoc of Sheet.createDrawingPatriarch says that it "will normally have the effect of removing any existing drawings on this sheet". This matters when you reopen a workbook that already contains pictures or charts and add one more. The safe habit, valid for both formats, is to ask first and create only when nothing exists:

Drawing<?> drawing = sheet.getDrawingPatriarch();
if (drawing == null) {
    drawing = sheet.createDrawingPatriarch();
}

In a check made for this page, XSSFSheet.createDrawingPatriarch() on a loaded .xlsx that already contained a picture returned the existing drawing with its shape intact. Write the guard anyway: it documents the intent and keeps the code correct for the .xls implementation and for other versions.

Only one patriarch exists per sheet. Create it once per sheet, outside any row loop, and pass it to the helper methods.

Pictures

Adding a picture

Adding an image takes two steps. First the bytes are registered with the workbook, which stores them once in the package and returns an index. Then a picture shape that points to the index is placed on a sheet through an anchor:

int pictureIndex = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);  // 1: store the bytes once
ClientAnchor anchor = factory.createClientAnchor();                         // 2: where to put it
anchor.setCol1(6);
anchor.setRow1(0);
Picture picture = drawing.createPicture(anchor, pictureIndex);
picture.resize();                                                           // natural size

The same index can be used for many shapes on many sheets: a logo on every sheet is one stored image and several shapes, not several copies of the bytes. XSSFWorkbook also has an addPicture(InputStream, int) overload; the byte[] form is the one in the common Workbook interface.

Constant on Workbook Format

PICTURE_TYPE_PNG

PNG. The right default for logos and screenshots

PICTURE_TYPE_JPEG

JPEG. Photographs

PICTURE_TYPE_DIB

Device-independent bitmap (BMP)

PICTURE_TYPE_EMF

Enhanced Metafile (vector, Windows)

PICTURE_TYPE_WMF

Windows Metafile (vector, Windows)

PICTURE_TYPE_PICT

Macintosh PICT

Prefer PNG or JPEG: they are shown identically by Excel, LibreOffice and web viewers. Vector formats are Windows-centred.

Anchors, cell coordinates and EMU

A ClientAnchor describes a rectangle on the sheet by its two corners. Each corner is a cell (zero-based column and row) plus an offset inside the cell. Offsets are in EMU (English Metric Units): 914,400 EMU per inch, 12,700 per point and 9,525 per pixel at 96 dpi. Conversions are in org.apache.poi.util.Units.

Setter or constant Meaning

setCol1, setRow1

Top-left corner cell

setCol2, setRow2

Bottom-right corner cell. Picture.resize() computes it for you

setDx1, setDy1, setDx2, setDy2

Offsets of the corners inside their cells, in EMU. In .xls the offsets are stored in a coarser unit with tighter ranges, so very precise placement is an OOXML feature

Units.EMU_PER_PIXEL, Units.EMU_PER_POINT

Conversion constants

setAnchorType(ClientAnchor.AnchorType)

How the shape behaves when users move, insert or resize cells

The anchor type decides what happens when the layout beneath the shape changes:

AnchorType Behaviour

MOVE_AND_RESIZE

The shape moves and stretches with the cells it covers (the Excel default for charts)

MOVE_DONT_RESIZE

The shape moves with its top-left cell but keeps its size (the Excel default for pictures)

DONT_MOVE_DO_RESIZE

Position is fixed, size follows the cells

DONT_MOVE_AND_RESIZE

Position and size are fixed

Resizing a picture

Picture.resize() sets the second corner so that the picture has its natural size starting at the first corner; resize(double scale) multiplies that size by a factor and resize(scaleX, scaleY) scales each axis separately. The computation converts pixels to cells using the column widths and row heights at the time of the call, and the official guide notes that it is accurate only when the default row heights and the default font are used. To fit an image in a fixed box, set both corners yourself and skip resize(); the image is then stretched to the box, so compute the aspect ratio yourself when it matters.

Call resize() after the widths of the covered columns and the heights of the covered rows are final. A later setColumnWidth, autoSizeColumn or setHeightInPoints leaves the picture with a stale second corner.

Comments and hyperlinks

Cell comments

A comment (Excel calls it a note in recent versions) is a box shown when the pointer rests on a cell. It is created from the patriarch and an anchor that places the box, then attached to the cell:

Method Effect

Drawing.createCellComment(ClientAnchor)

Creates the comment shape; the anchor is the position of the pop-up box, not of the cell

Comment.setString(RichTextString)

The text; use CreationHelper.createRichTextString, which can carry fonts

Comment.setAuthor(String)

Author shown in the status bar

Comment.setVisible(boolean)

false shows the box only on hover; true keeps it permanently shown

Cell.setCellComment(Comment)

Attaches the comment; passing null removes it

Sheet.getCellComment(CellAddress), Sheet.getCellComments()

Reading, one cell or the whole sheet as a map

Practical rules: one comment per cell; a comment text is limited to 32,767 characters by the Excel limits; set an explicit anchor size for long texts because the default box is small; and comments cost extra parts (the comments part and a VML drawing), so avoid thousands of them in generated reports. Threaded comments (the modern conversation-style comments of Microsoft 365) are a different structure from these notes; POI creates the notes described here.

Hyperlinks

CreationHelper.createHyperlink(HyperlinkType) creates the link; setAddress sets the target; Cell.setHyperlink attaches it. Hyperlinks are the only item on this page that does not need the drawing patriarch.

HyperlinkType Target and example address

URL

A web address, https://example.org/publisher

EMAIL

A mailto: address, optionally with a subject: mailto:librarian@example.org?subject=Inventory

FILE

A path to another file, absolute or relative to the workbook

DOCUMENT

A place inside the workbook: a sheet and a cell, written 'Inventory'!A1 (single quotes needed for sheet names with spaces)

NONE

No link (used when reading)

Hyperlink.setLabel sets the text of the link and setFirstRow/setLastRow (and column variants) make it span a range for the file formats that store ranges. A hyperlink does not change how the cell looks: Excel shows the standard blue underlined style only when a cell style says so, so apply a font with setUnderline(Font.U_SINGLE) and a blue colour, from a style that you create once and share. Addresses are not validated; a malformed URL is written as is. When the address comes from user input, check the scheme before writing it (allow https, http and mailto), because a workbook is often opened by a person who trusts it.

The complete example

The class below decorates the Inventory sheet with a logo, a comment on the ISBN cell and the three common kinds of hyperlinks. It works on any Workbook; replace XSSFWorkbook by HSSFWorkbook for a binary file.

package bookshelf;

import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public final class InventoryDecorations {

    public static void main(String[] args) throws IOException {
        byte[] logo = Files.readAllBytes(Path.of("bookshelf-logo.png"));

        try (Workbook workbook = new XSSFWorkbook()) {
            CreationHelper factory = workbook.getCreationHelper();
            Sheet sheet = workbook.createSheet("Inventory");
            Drawing<?> drawing = sheet.createDrawingPatriarch();

            // One shared style for every link cell.
            Font linkFont = workbook.createFont();
            linkFont.setUnderline(Font.U_SINGLE);
            linkFont.setColor(IndexedColors.BLUE.getIndex());
            CellStyle linkStyle = workbook.createCellStyle();
            linkStyle.setFont(linkFont);

            // Logo at G1, natural size scaled to half.
            int pictureIndex = workbook.addPicture(logo, Workbook.PICTURE_TYPE_PNG);
            ClientAnchor logoAnchor = factory.createClientAnchor();
            logoAnchor.setCol1(6);
            logoAnchor.setRow1(0);
            Picture logoPicture = drawing.createPicture(logoAnchor, pictureIndex);
            logoPicture.resize(0.5);

            Row row = sheet.createRow(1);

            // Comment on A2.
            Cell isbn = row.createCell(0);
            isbn.setCellValue("978-0-306-40615-7");
            ClientAnchor box = factory.createClientAnchor();
            box.setCol1(isbn.getColumnIndex() + 1);
            box.setCol2(isbn.getColumnIndex() + 4);
            box.setRow1(row.getRowNum());
            box.setRow2(row.getRowNum() + 3);
            Comment comment = drawing.createCellComment(box);
            comment.setString(factory.createRichTextString("ISBN checked against the national catalogue."));
            comment.setAuthor("catalogue-service");
            comment.setVisible(false);
            isbn.setCellComment(comment);

            // External web link.
            Cell site = row.createCell(1);
            site.setCellValue("Publisher site");
            Hyperlink url = factory.createHyperlink(HyperlinkType.URL);
            url.setAddress("https://example.org/publisher");
            site.setHyperlink(url);
            site.setCellStyle(linkStyle);

            // Link inside the workbook.
            Cell back = row.createCell(2);
            back.setCellValue("Go to top");
            Hyperlink doc = factory.createHyperlink(HyperlinkType.DOCUMENT);
            doc.setAddress("'Inventory'!A1");
            back.setHyperlink(doc);
            back.setCellStyle(linkStyle);

            // Mail link.
            Cell mail = row.createCell(3);
            mail.setCellValue("Ask the librarian");
            Hyperlink email = factory.createHyperlink(HyperlinkType.EMAIL);
            email.setAddress("mailto:librarian@example.org?subject=Inventory");
            mail.setHyperlink(email);
            mail.setCellStyle(linkStyle);

            try (FileOutputStream out = new FileOutputStream("decorations.xlsx")) {
                workbook.write(out);
            }
        }
    }
}

Source: the Images, Cell comments and Hyperlinks sections of the Spreadsheet Quick Guide, and the Javadoc of Drawing, Picture and Hyperlink.

Reading drawings back

Reading is the reverse path, and is useful for tests and for import pipelines that must reject workbooks with unexpected content (see the section landing page for the import pages that come later). The XSSF classes expose the shapes of the patriarch, the stored pictures and per-cell comments and links:

package bookshelf;

import java.io.File;
import java.io.IOException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFPictureData;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public final class InventoryInspector {

    public static void main(String[] args) throws IOException, InvalidFormatException {
        try (XSSFWorkbook workbook = new XSSFWorkbook(new File("decorations.xlsx"))) {
            for (XSSFPictureData data : workbook.getAllPictures()) {
                System.out.println(data.getPackagePart().getPartName() + " " + data.getData().length + " bytes");
            }
            XSSFSheet sheet = workbook.getSheet("Inventory");
            XSSFDrawing drawing = sheet.getDrawingPatriarch();
            if (drawing != null) {
                for (XSSFShape shape : drawing.getShapes()) {
                    if (shape instanceof XSSFPicture picture) {
                        System.out.println("picture " + picture.getPictureData().suggestFileExtension());
                    }
                }
                for (XSSFChart chart : drawing.getCharts()) {
                    System.out.println("chart " + chart.getTitleText());
                }
            }
            for (Row row : sheet) {
                for (Cell cell : row) {
                    if (cell.getHyperlink() != null) {
                        System.out.println(cell.getAddress() + " -> " + cell.getHyperlink().getAddress());
                    }
                    if (cell.getCellComment() != null) {
                        System.out.println(cell.getAddress() + " comment by " + cell.getCellComment().getAuthor());
                    }
                }
            }
        }
    }
}

Running it on the file of the previous example prints:

/xl/media/image1.png 99 bytes
picture png
A2 comment by catalogue-service
B2 -> https://example.org/publisher
C2 -> 'Inventory'!A1
D2 -> mailto:librarian@example.org?subject=Inventory

Source: the Javadoc of XSSFDrawing and XSSFWorkbook.getAllPictures.

Charts with XDDF

The chart classes and what POI offers

Charts are an OOXML feature in POI. XSSFDrawing.createChart creates an XSSFChart, and the classes in org.apache.poi.xddf.usermodel.chart describe what is plotted. XDDF stands for XML Drawing Document Format and wraps the DrawingML chart schema of the c: and a: namespaces. The classes were introduced in the 4.x line to replace the older chart helpers of the 3.x line. All chart types, axes and series share the same sequence of steps:

flowchart LR A["Sheet with the data
cell ranges"] --> B["XSSFDrawing.createChart
anchor"] B --> C["Axes
createCategoryAxis
createValueAxis"] C --> D["chart.createData
ChartTypes + axes"] D --> E["addSeries
categories and values"] E --> F["chart.plot data"] F --> G["workbook.write"]
Class or method Role

XSSFDrawing.createChart(ClientAnchor)

Creates the chart part and the shape that displays it

XSSFChart.setTitleText, setTitleOverlay

Title and whether it overlaps the plot

XSSFChart.getOrAddLegend() and XDDFChartLegend.setPosition

Legend; LegendPosition has TOP, TOP_RIGHT, RIGHT, BOTTOM and LEFT

createCategoryAxis(AxisPosition), createValueAxis(AxisPosition), createDateAxis(AxisPosition)

Axes. A category axis holds labels, a value axis numbers, a date axis a time scale

XDDFValueAxis.setCrosses, setMinimum, setMaximum, setNumberFormat, setMajorUnit

Scale and look of the value axis

XDDFDataSourcesFactory.fromStringCellRange, fromNumericCellRange

Wraps a CellRangeAddress of the sheet as a data source (a reference, not a copy)

XDDFDataSourcesFactory.fromArray

Wraps Java arrays when no cell holds the data (the chart embeds literal values)

chart.createData(ChartTypes, category axis, value axis)

Creates the data container of one chart type: BAR, LINE, PIE, AREA, RADAR, SCATTER, DOUGHNUT, SURFACE and their 3-D variants (see the ChartTypes Javadoc for the exact list of the version you use)

XDDFChartData.addSeries(categories, values)

Adds a series; cast it to the concrete Series type to set colours, markers, smoothing

chart.plot(XDDFChartData)

Commits the data container to the chart. Needed once per data container

The cell ranges are references: the chart in the file contains a reference such as Inventory!$B$2:$B$6 and may contain a cached copy of the values. Excel and LibreOffice redraw the chart from the cells when the file is opened, so changing a cell value later (or filling the cells after the chart is built) changes the chart without touching it.

Bar chart: copies in stock by genre

The following program writes the data to A1:B6 and puts a column chart to its right, in cells E2 to M19 (the anchor uses zero-based columns and rows). It is BarChart from poi-examples reduced to the Bookshelf data:

package bookshelf;

import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xddf.usermodel.chart.AxisCrosses;
import org.apache.poi.xddf.usermodel.chart.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.BarDirection;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;
import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFValueAxis;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public final class InventoryChart {

    public static void main(String[] args) throws IOException {
        String[] genres = {"Fiction", "Fantasy", "Science", "Technology", "History"};
        int[] copies = {42, 35, 28, 24, 19};

        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            XSSFSheet sheet = workbook.createSheet("Inventory");

            Row header = sheet.createRow(0);
            header.createCell(0).setCellValue("Genre");
            header.createCell(1).setCellValue("Copies");
            for (int i = 0; i < genres.length; i++) {
                Row row = sheet.createRow(i + 1);
                row.createCell(0).setCellValue(genres[i]);
                row.createCell(1).setCellValue(copies[i]);
            }

            XSSFDrawing drawing = sheet.createDrawingPatriarch();
            XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 4, 1, 13, 19);
            XSSFChart chart = drawing.createChart(anchor);
            chart.setTitleText("Copies in stock by genre");
            chart.setTitleOverlay(false);

            XDDFChartLegend legend = chart.getOrAddLegend();
            legend.setPosition(LegendPosition.TOP_RIGHT);

            XDDFCategoryAxis genreAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
            genreAxis.setTitle("Genre");
            XDDFValueAxis copiesAxis = chart.createValueAxis(AxisPosition.LEFT);
            copiesAxis.setTitle("Copies");
            copiesAxis.setCrosses(AxisCrosses.AUTO_ZERO);

            XDDFDataSource<String> categories = XDDFDataSourcesFactory.fromStringCellRange(
                    sheet, new CellRangeAddress(1, genres.length, 0, 0));
            XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(
                    sheet, new CellRangeAddress(1, genres.length, 1, 1));

            XDDFBarChartData data = (XDDFBarChartData) chart.createData(
                    ChartTypes.BAR, genreAxis, copiesAxis);
            data.setBarDirection(BarDirection.COL);
            XDDFBarChartData.Series series = (XDDFBarChartData.Series) data.addSeries(categories, values);
            series.setTitle("Copies", new CellReference(sheet.getSheetName(), 0, 1, true, true));
            chart.plot(data);

            try (FileOutputStream out = new FileOutputStream("books-inventory.xlsx")) {
                workbook.write(out);
            }
        }
    }
}

Source: BarChart in poi-examples (package org.apache.poi.examples.xssf.usermodel), and the Javadoc of XDDFBarChartData.

Excel and LibreOffice render this file as the figure below. The picture is an illustration drawn for this page, not a screenshot: the application applies its own theme colours and fonts, but the data, the axes, the title and the legend match the code.

Rendered Bookshelf chart, a column chart of copies in stock by genre next to the data A1:B6

Remarks on the code:

  • new CellRangeAddress(1, 5, 0, 0) is A2:A6: the header row is not part of the series; its text is given to the series through setTitle("Copies", new CellReference(...)), which also stores the reference Inventory!$B$1.

  • The data container is cast from XDDFChartData to XDDFBarChartData, and the series from XDDFChartData.Series to XDDFBarChartData.Series. These casts are in the official examples and are unavoidable with this API.

  • BarDirection.COL draws vertical columns; BarDirection.BAR draws horizontal bars. Use setBarGrouping for clustered, stacked or percent-stacked bars.

  • Call chart.plot after all series are added.

Line chart: loans per month

A line chart shares the structure; only the data container and the series type change. The value axis must exist, so the same axis calls are used. The series below plots the loans per month stored in B2:B13:

XDDFCategoryAxis monthAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
XDDFValueAxis loanAxis = chart.createValueAxis(AxisPosition.LEFT);
loanAxis.setCrosses(AxisCrosses.AUTO_ZERO);
loanAxis.setMinimum(0);

XDDFDataSource<String> months = XDDFDataSourcesFactory.fromStringCellRange(
        sheet, new CellRangeAddress(1, 12, 0, 0));
XDDFNumericalDataSource<Double> loans = XDDFDataSourcesFactory.fromNumericCellRange(
        sheet, new CellRangeAddress(1, 12, 1, 1));

XDDFLineChartData lineData = (XDDFLineChartData) chart.createData(
        ChartTypes.LINE, monthAxis, loanAxis);
XDDFLineChartData.Series loanSeries = (XDDFLineChartData.Series) lineData.addSeries(months, loans);
loanSeries.setTitle("Loans", new CellReference(sheet.getSheetName(), 0, 1, true, true));
loanSeries.setSmooth(false);
loanSeries.setMarkerStyle(MarkerStyle.CIRCLE);
chart.plot(lineData);

Source: LineChart in poi-examples, and the Javadoc of XDDFLineChartData. The fragment needs XDDFLineChartData and org.apache.poi.xddf.usermodel.chart.MarkerStyle imports in addition to the ones of the bar-chart program.

Scatter chart: price against pages

A scatter chart needs two value axes (no category axis) and numeric sources for both coordinates; it is the chart for relations such as the price of a book against its page count:

XDDFValueAxis pagesAxis = chart.createValueAxis(AxisPosition.BOTTOM);
pagesAxis.setTitle("Pages");
XDDFValueAxis priceAxis = chart.createValueAxis(AxisPosition.LEFT);
priceAxis.setTitle("Price");
priceAxis.setCrosses(AxisCrosses.AUTO_ZERO);

XDDFNumericalDataSource<Double> pages = XDDFDataSourcesFactory.fromNumericCellRange(
        sheet, new CellRangeAddress(1, 40, 2, 2));
XDDFNumericalDataSource<Double> prices = XDDFDataSourcesFactory.fromNumericCellRange(
        sheet, new CellRangeAddress(1, 40, 3, 3));

XDDFScatterChartData scatterData = (XDDFScatterChartData) chart.createData(
        ChartTypes.SCATTER, pagesAxis, priceAxis);
XDDFScatterChartData.Series scatter = (XDDFScatterChartData.Series) scatterData.addSeries(pages, prices);
scatter.setTitle("Price against pages", null);
scatter.setMarkerStyle(MarkerStyle.DIAMOND);
chart.plot(scatterData);

Source: ScatterChart in poi-examples and the Javadoc of XDDFScatterChartData. Excel may connect scatter points with lines by default; the ScatterChart example shows how to give the series a no-fill line when only markers are wanted.

Choosing a chart type

ChartTypes Use for Needs

BAR

Comparing categories (copies per genre); BarDirection.COL or BAR

Category axis plus value axis

LINE

Trends over ordered categories (loans per month)

Category (or date) axis plus value axis

SCATTER

Relations between two numeric variables

Two value axes

PIE, DOUGHNUT

Shares of a total (loans per genre); few slices only

No axes

AREA

Cumulative volumes over time

Category axis plus value axis

RADAR, SURFACE, the 3-D variants

Special cases; the 3-D types render differently in each application

Depends on the type

Series colours and formats come from XDDFSolidFillProperties, XDDFColor and XDDFShapeProperties set through series.setShapeProperties. If you do not set them, Excel assigns the theme palette, which is the safest choice for reports that will be restyled.

Chart pitfalls

Symptom Cause and fix

NoClassDefFoundError for a schema class when saving a chart

The lite schema jar can lack classes that a particular chart feature needs; the bar chart of this page runs with the default poi-ooxml dependency, but if a more advanced chart fails, switch to the full schemas as described in Setup & Dependencies

Chart is empty or shows only zeros

The ranges point to cells that were not written yet, are on another sheet that is misspelled, or contain text. Range indexes are zero-based, and the end is inclusive

Chart works in Excel but is empty in LibreOffice (or the reverse)

Cached values are missing or stale. Write the cell values before closing the workbook and open the file in both applications when it matters

Second chart replaces or garbles the first

Create one patriarch and call createChart once per chart with different anchors; do not create a new patriarch per chart

Series title missing

setTitle(String, CellReference) needs the reference of a real cell; pass null for a literal title

Chart disappears after round trip of an old .xls

.xls charts are not modelled, see Charts in .xls files and the template workaround

Generated report with live data is slow

Charts are small parts, but each series reads the whole range at write time. Keep chart ranges to what is shown

POI never renders a chart: it cannot produce a picture of it. To put a chart in a PDF or a web page, render the chart with a charting library, or convert the workbook with LibreOffice outside POI.

Charts in .xls files and the template workaround

HSSFWorkbook has only a very limited chart-creation API: HSSFChart.createBarChart builds a basic bar chart from BIFF records (its Javadoc calls the API unfinished), with no control over series or styling. The HSSF classes read the BIFF chart records only to a limited degree, and there is no counterpart of XDDF. Existing charts in a loaded .xls file are kept as opaque records when the workbook is saved and generally survive a read, modify and write cycle, but they are not editable through the API and POI cannot guarantee that every chart version round trips. The practical options are:

  • Prefer .xlsx. New work should use XSSFWorkbook and XDDF. Only keep .xls when a consumer requires it (see Legacy .xls with HSSF).

  • Template workaround. Create the workbook with the chart once in Excel or LibreOffice, with the chart pointing to fixed ranges such as Inventory!$A$2:$A$6. Load the template with POI, write the cell values into those ranges and save under a new name. The chart is redrawn from the cells when the file is opened. This also works for .xlsx, and it is the better option when designers need to style the chart (colours, fonts, 3-D) without a code change, because XDDF can express only part of DrawingML. The topic is continued in Templates & Modifying Workbooks.

  • Insert a picture. Render the chart elsewhere and add it with addPicture as shown above. It is static and not editable in Excel, but it looks identical everywhere.

A template chart has one limit: the number of data points is the number of rows in the referenced range. For a variable number of rows, define a named range with a dynamic formula in the template, or rewrite the series range with XDDF (XSSFChart.getCTChart() gives access to the underlying XMLBeans objects for what the high-level API does not cover; see the note on internals in Introduction & Architecture).

Embedded objects

An embedded object is an arbitrary file stored inside the workbook, shown as an icon in a cell: a PDF specification next to a book, say. XSSF supports adding and reading them, with these limits:

Operation API

Adding an arbitrary file in .xlsx

XSSFWorkbook.addOlePackage(byte[] data, String label, String fileName, String command) returns the index of the new object part. The returned part still needs a preview picture and an XSSFObjectData shape in the drawing

Adding in .xls

HSSFWorkbook.addOlePackage(byte[] data, String label, String fileName, String command) returns the id of the storage; HSSFPatriarch.createObjectData uses it

Reading

XSSFWorkbook.getAllEmbeddedParts() lists the package parts of the embedded objects; for .xls, HSSFWorkbook.getAllEmbeddedObjects() lists HSSFObjectData

The shape part (the drawing record, the preview image and the relationship) is low-level work, and the official examples xssf.usermodel.EmbeddedObjects, hssf.usermodel.EmbeddedObjects and ss.LoadEmbedded (reading), together with the Workbook.addOlePackage and Drawing.createObjectData Javadoc (writing), are the reference. When a link to a file is enough, a HyperlinkType.FILE hyperlink is simpler, smaller and safer. Embedded objects are also a security concern for the receiving user: Excel asks before opening them, and a workbook import must never open or run an embedded file (see the section landing page for the security page of the section).

Limits and versions

Item Limit or note

Comment text

32,767 characters per comment (the Excel cell limit)

Hyperlinks

Excel documents a limit of 65,530 hyperlinks per worksheet and the length of an address is limited; the exact length is application-specific, so keep addresses short and test them

Pictures per workbook

No fixed small limit in .xlsx; each stored image is one package part, so the file size grows with the bytes

Picture formats

PNG, JPEG, DIB, EMF, WMF and PICT constants are common to both formats

Charts in .xls

Not created by POI; kept as binary records

Charts and pictures with SXSSF

SXSSFSheet is a streaming writer with a limited drawing support; if a large report needs a chart or an image, test it with the target application, or write the data with SXSSF and add the decorations in a second pass over a template (see Streaming Writes with SXSSF)

Fonts for rendering

Picture.resize and autoSizeColumn need a font that the JVM has; a headless server without fonts can fail or give wrong sizes
Version Change relevant to this page

3.x to 4.0

XDDF introduced for charts; the HyperlinkType enum replaces the integer Hyperlink.LINK_* constants of older code

4.x to 5.x

Deprecated integer constants and helpers are removed over the 4.x and 5.x lines (see Introduction & Architecture for the versioning policy); the code of this page uses only the current enum-based API

5.1 to 5.5

Incremental XDDF additions and fixes (more series properties, more axis options); check the changes list for the version you use

6.0 (planned)

Expected to remove deprecated chart and picture methods and to require a newer Java; nothing has been released, so verify against the release notes when it ships. Code in this section uses only non-deprecated API

Pitfalls summary

  • Calling createDrawingPatriarch on a loaded .xls that has drawings can erase them; check getDrawingPatriarch() first.

  • Set column widths and row heights before Picture.resize().

  • A picture index is per workbook. Do not reuse an index from another workbook.

  • Hyperlinks look like plain text until a style gives them the link font.

  • A comment’s anchor positions the pop-up box, not the cell: forgetting to set it gives a box the size of one cell.

  • Verify generated charts with a test that reopens the file and counts getCharts(); schema-class errors appear at write time.

  • Never trust a hyperlink address that came from user input without checking its scheme.

  • There is no PDF or image export for a chart; POI does not render.

References