Word Charts & Embedded Objects

package bookshelf;

import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xddf.usermodel.chart.AxisCrossBetween;
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.BarGrouping;
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.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.xwpf.usermodel.XWPFChart;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;

public final class MonthlyReportChart {

    private static final int GENRE_COLUMN = 0;
    private static final int LOANED_COLUMN = 1;
    private static final int RETURNED_COLUMN = 2;

    public static void main(String[] args) throws IOException, InvalidFormatException {
        String[] genres = {"Fiction", "Fantasy", "Science", "Technology", "History"};
        Double[] loaned = {128.0, 96.0, 61.0, 74.0, 43.0};
        Double[] returned = {120.0, 90.0, 58.0, 69.0, 45.0};

        try (XWPFDocument document = new XWPFDocument();
             FileOutputStream out = new FileOutputStream("monthly-report.docx")) {

            XWPFParagraph title = document.createParagraph();
            XWPFRun titleRun = title.createRun();
            titleRun.setBold(true);
            titleRun.setFontSize(18);
            titleRun.setText("Monthly loans report, September 2026");

            XWPFParagraph intro = document.createParagraph();
            intro.createRun().setText("Loans and returns per genre are compared in the chart below.");

            // The chart is anchored inline in a run of its own paragraph.
            XWPFParagraph chartParagraph = document.createParagraph();
            XWPFRun chartRun = chartParagraph.createRun();
            XWPFChart chart = document.createChart(chartRun,
                    (int) Units.toEMU(440), (int) Units.toEMU(260));

            XDDFCategoryAxis genreAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
            genreAxis.setTitle("Genre");
            XDDFValueAxis countAxis = chart.createValueAxis(AxisPosition.LEFT);
            countAxis.setTitle("Books");
            countAxis.setCrosses(AxisCrosses.AUTO_ZERO);
            countAxis.setCrossBetween(AxisCrossBetween.BETWEEN);

            int points = genres.length;
            // Sheet1 of the embedded workbook: A = genre, B = loaned, C = returned; row 1 holds the series titles.
            XDDFDataSource<String> categories = XDDFDataSourcesFactory.fromArray(
                    genres, chart.formatRange(new CellRangeAddress(1, points, GENRE_COLUMN, GENRE_COLUMN)), GENRE_COLUMN);
            XDDFNumericalDataSource<Double> loanedValues = XDDFDataSourcesFactory.fromArray(
                    loaned, chart.formatRange(new CellRangeAddress(1, points, LOANED_COLUMN, LOANED_COLUMN)), LOANED_COLUMN);
            XDDFNumericalDataSource<Double> returnedValues = XDDFDataSourcesFactory.fromArray(
                    returned, chart.formatRange(new CellRangeAddress(1, points, RETURNED_COLUMN, RETURNED_COLUMN)), RETURNED_COLUMN);

            XDDFBarChartData data = (XDDFBarChartData) chart.createData(ChartTypes.BAR, genreAxis, countAxis);
            data.setBarDirection(BarDirection.COL);
            data.setBarGrouping(BarGrouping.CLUSTERED);

            XDDFBarChartData.Series loanedSeries = (XDDFBarChartData.Series) data.addSeries(categories, loanedValues);
            loanedSeries.setTitle("Loaned", chart.setSheetTitle("Loaned", LOANED_COLUMN));
            XDDFBarChartData.Series returnedSeries = (XDDFBarChartData.Series) data.addSeries(categories, returnedValues);
            returnedSeries.setTitle("Returned", chart.setSheetTitle("Returned", RETURNED_COLUMN));
            chart.plot(data);

            chart.getOrAddLegend().setPosition(LegendPosition.BOTTOM);
            chart.setTitleText("Loans and returns by genre");
            chart.setTitleOverlay(false);

            XWPFParagraph caption = document.createParagraph();
            caption.createRun().setText("Figure 1. Loans and returns by genre, September 2026.");

            document.write(out);
        }
    }
}

try (XWPFDocument document = new XWPFDocument();
     FileOutputStream out = new FileOutputStream("genre-share.docx")) {

    // createChart(width, height) appends a new paragraph at the end of the body.
    XWPFChart chart = document.createChart((int) Units.toEMU(360), (int) Units.toEMU(260));

    XDDFDataSource<String> categories = XDDFDataSourcesFactory.fromArray(
            genres, chart.formatRange(new CellRangeAddress(1, genres.length, 0, 0)), 0);
    XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromArray(
            copies, chart.formatRange(new CellRangeAddress(1, genres.length, 1, 1)), 1);

    // A pie chart has no axes: pass null for both.
    XDDFPieChartData data = (XDDFPieChartData) chart.createData(ChartTypes.PIE, null, null);
    data.setVaryColors(true);
    XDDFChartData.Series series = data.addSeries(categories, values);
    series.setTitle("Copies", chart.setSheetTitle("Copies", 1));
    chart.plot(data);

    chart.getOrAddLegend().setPosition(LegendPosition.RIGHT);
    chart.setTitleText("Copies in stock by genre");
    chart.setTitleOverlay(false);
    document.write(out);
}

XWPFChart chart = document.getCharts().get(0);
XDDFBarChartData bar = (XDDFBarChartData) chart.getChartSeries().get(0);

// Series colours: a hexadecimal RGB colour and a preset colour.
bar.getSeries(0).setFillProperties(new XDDFSolidFillProperties(
        XDDFColor.from(new byte[] {(byte) 0x1F, (byte) 0x5F, (byte) 0xA6})));
bar.getSeries(1).setFillProperties(new XDDFSolidFillProperties(
        XDDFColor.from(PresetColor.ORANGE)));

bar.setGapWidth(80);                       // percent of a column width between two groups

XDDFValueAxis valueAxis = bar.getValueAxes().get(0);
valueAxis.setMinimum(0);
valueAxis.setMaximum(150);
valueAxis.setMajorUnit(25);
valueAxis.setNumberFormat("0");

chart.plot(bar);                           // apply the changes to the chart part

package bookshelf;

import java.awt.Color;
import java.awt.Graphics2D;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import javax.imageio.ImageIO;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.openxml4j.opc.PackagePartName;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.PackagingURIHelper;
import org.apache.poi.openxml4j.opc.TargetMode;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.apache.xmlbeans.XmlException;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTObject;

public final class EmbedWorkbook {

    private static final String XLSX_TYPE =
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    private static final String PACKAGE_REL =
            "http://schemas.openxmlformats.org/officeDocument/2006/relationships/package";

    public static void main(String[] args) throws IOException, InvalidFormatException, XmlException {
        try (XWPFDocument document = new XWPFDocument();
             FileOutputStream out = new FileOutputStream("loan-letter-with-data.docx")) {

            document.createParagraph().createRun().setText("The loan history of your account is attached as a workbook:");

            // 1. The embedded workbook is a new part of the package, named like Word names it.
            PackagePartName partName = PackagingURIHelper.createPartName(
                    "/word/embeddings/Microsoft_Excel_Worksheet1.xlsx");
            OPCPackage pkg = document.getPackage();
            PackagePart workbookPart = pkg.createPart(partName, XLSX_TYPE);
            try (OutputStream partOut = workbookPart.getOutputStream()) {
                partOut.write(loanHistoryWorkbook());
            }

            // 2. The document part points to it with a "package" relationship.
            PackageRelationship relationship = document.getPackagePart()
                    .addRelationship(partName, TargetMode.INTERNAL, PACKAGE_REL);

            // 3. The icon Word shows in place of the workbook is an ordinary picture part.
            String iconId = document.addPictureData(icon(), org.apache.poi.common.usermodel.PictureType.PNG);

            // 4. The run holds a w:object that ties the icon (VML) and the OLE object together.
            XWPFParagraph paragraph = document.createParagraph();
            XWPFRun run = paragraph.createRun();
            CTObject object = run.getCTR().addNewObject();
            object.set(CTObject.Factory.parse(objectXml(iconId, relationship.getId())));

            document.write(out);
        }
    }

    private static String objectXml(String iconRelationshipId, String workbookRelationshipId) {
        return """
                <xml-fragment xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"
                          xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
                          xmlns:v="urn:schemas-microsoft-com:vml"
                          xmlns:o="urn:schemas-microsoft-com:office:office"
                          w:dxaOrig="1520" w:dyaOrig="960">
                  <v:shape id="_x0000_i1025" type="#_x0000_t75" style="width:76pt;height:48pt" o:ole="">
                    <v:imagedata r:id="%s" o:title=""/>
                  </v:shape>
                  <o:OLEObject Type="Embed" ProgID="Excel.Sheet.12" ShapeID="_x0000_i1025"
                               DrawAspect="Icon" ObjectID="_1" r:id="%s"/>
                </xml-fragment>
                """.formatted(iconRelationshipId, workbookRelationshipId);
    }

    private static byte[] loanHistoryWorkbook() throws IOException {
        try (XSSFWorkbook workbook = new XSSFWorkbook();
             ByteArrayOutputStream bytes = new ByteArrayOutputStream()) {
            XSSFSheet sheet = workbook.createSheet("Loans");
            Row header = sheet.createRow(0);
            header.createCell(0).setCellValue("Title");
            header.createCell(1).setCellValue("Loaned");
            header.createCell(2).setCellValue("Due");
            Row row = sheet.createRow(1);
            row.createCell(0).setCellValue("The Left Hand of Darkness");
            row.createCell(1).setCellValue("2026-09-02");
            row.createCell(2).setCellValue("2026-09-23");
            workbook.write(bytes);
            return bytes.toByteArray();
        }
    }

    private static byte[] icon() throws IOException {
        BufferedImage image = new BufferedImage(76, 48, BufferedImage.TYPE_INT_ARGB);
        Graphics2D g = image.createGraphics();
        g.setColor(new Color(0x1D6F42));
        g.fillRoundRect(0, 0, 76, 48, 8, 8);
        g.setColor(Color.WHITE);
        g.drawString("Loans.xlsx", 8, 28);
        g.dispose();
        try (ByteArrayOutputStream bytes = new ByteArrayOutputStream()) {
            ImageIO.write(image, "png", bytes);
            return bytes.toByteArray();
        }
    }
}

package bookshelf;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.List;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xwpf.usermodel.XWPFDocument;

public final class UpdateEmbeddedWorkbooks {

    public static void main(String[] args) throws IOException, OpenXML4JException {
        try (InputStream in = Files.newInputStream(Path.of("loan-letter-with-data.docx"));
             XWPFDocument document = new XWPFDocument(in);
             FileOutputStream out = new FileOutputStream("loan-letter-updated.docx")) {

            List<PackagePart> embedded = document.getAllEmbeddedParts();
            for (PackagePart part : embedded) {
                String extension = part.getPartName().getExtension();
                System.out.printf("%s (%s)%n", part.getPartName(), part.getContentType());
                if (!"xlsx".equals(extension) && !"xls".equals(extension)) {
                    continue;   // another kind of object: leave it untouched
                }

                // Read the whole part first, then replace it: the part's output stream
                // discards the old content, so never read and write it at the same time.
                byte[] updated;
                try (InputStream partIn = part.getInputStream();
                     Workbook workbook = WorkbookFactory.create(partIn);
                     ByteArrayOutputStream bytes = new ByteArrayOutputStream()) {
                    Sheet sheet = workbook.getSheetAt(0);
                    sheet.getRow(1).getCell(2).setCellValue("2026-10-14");   // extended due date
                    workbook.write(bytes);
                    updated = bytes.toByteArray();
                }
                try (OutputStream partOut = part.getOutputStream()) {
                    partOut.write(updated);
                }
            }

            document.write(out);
        }
    }
}