Generating Temperature Line Charts in Excel with Apache POI (Java)

The article provides a step‑by‑step guide for generating an Excel line chart that visualizes temperature data using the Apache POI library in Java.

Maven dependency:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi‑ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

Java code example:

/**
 * @author alin
 * @date 2024‑06‑03
 */
public class ExportChart {
    public static void main(String[] args) throws Exception {
        testCreateChart();
    }

    /**
     * Create a line chart for temperature data.
     */
    public static void testCreateChart() throws Exception {
        try (XSSFWorkbook wb = new XSSFWorkbook()) {
            String sheetName = "温度折线图";
            XSSFSheet sheet = wb.createSheet(sheetName);
            XSSFDrawing drawing = sheet.createDrawingPatriarch();
            XSSFClientAnchor anchor = drawing.createAnchor(0,0,0,0,2,4,30,50);
            XSSFChart chart = drawing.createChart(anchor);
            chart.setTitleText("单据: ****运输过程温度数据 车牌:豫P12345");
            chart.setTitleOverlay(false);
            setChartTitleFontSize(chart, 16);
            XDDFChartLegend legend = chart.getOrAddLegend();
            legend.setPosition(LegendPosition.TOP_RIGHT);
            XDDFCategoryAxis xAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
            xAxis.setTitle("采集时间");
            setAxisTitleFontSize(xAxis, 16);
            XDDFValueAxis yAxis = chart.createValueAxis(AxisPosition.TOP);
            yAxis.setTitle("温度");
            yAxis.setMajorUnit(5);
            setAxisTitleFontSize(yAxis, 16);

            List<TemperatureModel> list = getResult();
            List<String> xTitleData = new ArrayList<>(list.size());
            List<Double> yData1 = new ArrayList<>(list.size());
            List<Double> yData2 = new ArrayList<>(list.size());
            List<Double> yData3 = new ArrayList<>(list.size());
            for (TemperatureModel layer : list) {
                xTitleData.add(layer.getGatherTime());
                yData1.add(layer.getTemperature() == null ? 0D : layer.getTemperature());
                yData2.add(layer.getTemperature2() == null ? 0D : layer.getTemperature2());
                yData3.add(layer.getTemperature3() == null ? 0D : layer.getTemperature3());
            }

            XDDFDataSource<String> date = XDDFDataSourcesFactory.fromArray(xTitleData.toArray(new String[0]));
            XDDFNumericalDataSource<Double> one = XDDFDataSourcesFactory.fromArray(yData1.toArray(new Double[0]));
            XDDFNumericalDataSource<Double> two = XDDFDataSourcesFactory.fromArray(yData2.toArray(new Double[0]));
            XDDFNumericalDataSource<Double> three = XDDFDataSourcesFactory.fromArray(yData3.toArray(new Double[0]));

            XDDFLineChartData data = (XDDFLineChartData) chart.createData(ChartTypes.LINE, xAxis, yAxis);
            XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) data.addSeries(date, one);
            series1.setTitle("温度1(℃)", null);
            series1.setSmooth(true);
            series1.setMarkerStyle(MarkerStyle.CIRCLE);
            XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) data.addSeries(date, two);
            series2.setTitle("温度2(℃)", null);
            series2.setSmooth(true);
            series2.setMarkerStyle(MarkerStyle.CIRCLE);
            XDDFLineChartData.Series series3 = (XDDFLineChartData.Series) data.addSeries(date, three);
            series3.setTitle("温度3(℃)", null);
            series3.setSmooth(true);
            series3.setMarkerStyle(MarkerStyle.CIRCLE);
            chart.plot(data);

            try (FileOutputStream fileOut = new FileOutputStream("d:/豫P12345运输温度折线图.xlsx")) {
                wb.write(fileOut);
                wb.close();
            }
        }
    }

    /**
     * Simulated temperature data.
     */
    public static List<TemperatureModel> getResult() {
        List<TemperatureModel> list = new ArrayList<>();
        for (int i = 0; i < 30; i++) {
            TemperatureModel temperatureModel = new TemperatureModel();
            temperatureModel.setGatherTime("2024-06-03 10:" + String.format("%02d", i * 2) + ":00");
            temperatureModel.setTemperature(i % 2 == 0 ? -RandomUtils.nextDouble(0, 30) : RandomUtils.nextDouble(0, 30));
            temperatureModel.setTemperature2(RandomUtils.nextDouble(0, 30));
            temperatureModel.setTemperature3(i % 2 == 0 ? -RandomUtils.nextDouble(0, 30) : RandomUtils.nextDouble(0, 30));
            list.add(temperatureModel);
        }
        return list;
    }

    /**
     * Set chart title font size via low‑level XML.
     */
    private static void setChartTitleFontSize(XSSFChart chart, double fontSize) {
        CTChart ctChart = chart.getCTChart();
        if (ctChart.isSetTitle()) {
            CTTitle title = ctChart.getTitle();
            if (title.isSetTx()) {
                CTTextBody rich = title.getTx().getRich();
                CTTextParagraph para = rich.getPArray(0);
                CTTextCharacterProperties rPr = para.getRArray(0).getRPr();
                rPr.setSz((int) (fontSize * 100));
            }
        }
    }

    /**
     * Set X‑axis title font size via reflection.
     */
    private static void setAxisTitleFontSize(XDDFCategoryAxis axis, double fontSize) {
        try {
            Field ctCatAx1 = XDDFCategoryAxis.class.getDeclaredField("ctCatAx");
            ctCatAx1.setAccessible(true);
            CTCatAx ctCatAx = (CTCatAx) ctCatAx1.get(axis);
            if (ctCatAx.isSetTitle()) {
                CTTitle title = ctCatAx.getTitle();
                if (title.isSetTx()) {
                    CTTextBody rich = title.getTx().getRich();
                    if (rich != null && rich.sizeOfPArray() > 0) {
                        CTTextParagraph para = rich.getPArray(0);
                        if (para.sizeOfRArray() > 0) {
                            CTTextCharacterProperties rPr = para.getRArray(0).getRPr();
                            if (rPr == null) rPr = para.getRArray(0).addNewRPr();
                            rPr.setSz((int) (fontSize * 100));
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Set Y‑axis title font size via reflection.
     */
    private static void setAxisTitleFontSize(XDDFValueAxis axis, double fontSize) {
        try {
            Field ctValAx1 = XDDFValueAxis.class.getDeclaredField("ctValAx");
            ctValAx1.setAccessible(true);
            CTValAx ctValAx = (CTValAx) ctValAx1.get(axis);
            if (ctValAx.isSetTitle()) {
                CTTitle title = ctValAx.getTitle();
                if (title.isSetTx()) {
                    CTTextBody rich = title.getTx().getRich();
                    if (rich != null && rich.sizeOfPArray() > 0) {
                        CTTextParagraph para = rich.getPArray(0);
                        if (para.sizeOfRArray() > 0) {
                            CTTextCharacterProperties rPr = para.getRArray(0).getRPr();
                            if (rPr == null) rPr = para.getRArray(0).addNewRPr();
                            rPr.setSz((int) (fontSize * 100));
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Data
    public static class TemperatureModel {
        /** Gather time */
        private String gatherTime;
        /** Temperature 1 */
        private Double temperature;
        /** Temperature 2 */
        private Double temperature2;
        /** Temperature 3 */
        private Double temperature3;
    }
}

The generated Excel file (example shown below) contains a line chart with three temperature series, custom titles, and properly sized fonts.