Java生成Excel中的组合图

使用Java+poi生成 依赖

▼
js
复制代码
<!--poi-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version>
        </dependency>

实体类：

▼
js
复制代码
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ChartExcelDto implements Serializable {
    /**
     * 图表数据
     */
    private Map<String, List<Object>> data;
    /**
     * 折线图和柱状图共用指定的x轴值
     */
    private String categories;
    /**
     * 指定的折线图y轴
     */
    private List<String> valueList;
    /**
     * 指定的柱状图y轴
     */
    private String barValue;
}

代码，中间的雪花id，需要自己引入hutool依赖，这里是将json数据作为画图数据源，其实也可以改写为上传excel，根据excel中的数据生成组合图，看自己需求改造。

▼
js
复制代码
public String createChartExcel(ChartExcelDto chartExcelDto) {
        Map<String, List<Object>> data = chartExcelDto.getData();
        // x轴
        List<Object> categoryList = data.get(chartExcelDto.getCategories());
        String[] categories = categoryList.stream()
                .map(Object::toString)
                .toArray(String[]::new);

        // 折线图y轴
        List<String> valueKeyList = chartExcelDto.getValueList();
        List<List<Object>> valueList = new ArrayList<>();
        for (String key : valueKeyList) {
            List<Object> value = data.get(key);
            valueList.add(value);
        }

        // 支持 Integer、Float 等
        List<Double[]> values = new ArrayList<>();
        for (List<Object> list : valueList) {
            Double[] value = list.stream()
                    .map(obj -> ((Number) obj).doubleValue())
                    .toArray(Double[]::new);
            values.add(value);
        }

        // 柱状图y轴
        Double[] barValue = data.get(chartExcelDto.getBarValue()).stream()
                .map(obj -> ((Number) obj).doubleValue())
                .toArray(Double[]::new);

        // 创建数据源
        XDDFDataSource<String> categoriesData = XDDFDataSourcesFactory.fromArray(categories);
        List<XDDFNumericalDataSource<Double>> lineValuesDataList = new ArrayList<>();
        for (Double[] value : values) {
            lineValuesDataList.add(XDDFDataSourcesFactory.fromArray(value));
        }
        XDDFNumericalDataSource<Double> barValuesData = XDDFDataSourcesFactory.fromArray(barValue);

        // 创建一个新的工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("数据表");

        // 合并单元格并居中显示标题
        XSSFRow headerRow = sheet.createRow(0);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 7));

        XSSFCell titleCell = headerRow.createCell(3);
        titleCell.setCellValue("趋势图");

        XSSFCellStyle centerStyle = workbook.createCellStyle();
        centerStyle.setAlignment(org.apache.poi.ss.usermodel.HorizontalAlignment.CENTER);
        centerStyle.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);
        titleCell.setCellStyle(centerStyle);

        // 创建绘图工具
        XSSFDrawing drawingPatriarch = sheet.createDrawingPatriarch();
        XSSFClientAnchor anchor = workbook.getCreationHelper().createClientAnchor();
        anchor.setCol1(3);
        anchor.setRow1(1);
        anchor.setCol2(8);
        anchor.setRow2(20);
        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);

        XSSFChart chart = drawingPatriarch.createChart(anchor);

        // 配置坐标轴
        XDDFCategoryAxis categoryAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
        XDDFValueAxis valueAxisLeft = chart.createValueAxis(AxisPosition.LEFT);

        // 柱状图
        XDDFBarChartData barData = (XDDFBarChartData) chart.createData(ChartTypes.BAR, categoryAxis, valueAxisLeft);
        barData.setBarDirection(BarDirection.COL);
        XDDFChartData.Series barSeries = barData.addSeries(categoriesData, barValuesData);
        barSeries.setTitle(chartExcelDto.getBarValue(), null);

        // 折线图
        XDDFLineChartData lineData = (XDDFLineChartData) chart.createData(ChartTypes.LINE, categoryAxis, valueAxisLeft);
        for (int i = 0; i < lineValuesDataList.size(); i++) {
            XDDFChartData.Series lineSeries = lineData.addSeries(categoriesData, lineValuesDataList.get(i));
            lineSeries.setTitle(valueKeyList.get(i), null);
        }
        chart.plot(barData);
        chart.plot(lineData);
        // 定义保存路径（Linux 路径）
        // 定义 Linux 下的文件保存路径
        String basePath = "/opt/excel/output/";
        String fileName = IdUtil.getSnowflakeNextIdStr() + ".xlsx";
        String filePath = basePath + fileName;
        File dir = new File(basePath);
        if (!dir.exists()) {
            boolean mkdirSuccess = dir.mkdirs();
            if (!mkdirSuccess) {
                log.error("创建目录失败: {}", basePath);
                return "创建文件目录失败";
            }
        }
        // 保存文件到 Linux 服务器
        try (FileOutputStream fileOut = new FileOutputStream(filePath)) {
            workbook.write(fileOut);
        }
        catch (IOException e) {
            log.error("写入Excel文件失败: {}", e.getMessage(), e);
            return "生成文件失败";
        }
        finally {
            try {
                workbook.close();
            }
            catch (IOException e) {
                log.error("关闭Excel工作簿失败: {}", e.getMessage(), e);
            }
        }
        log.info("生成excel图表成功");
        return filePath;
    }

参数：

▼
js
复制代码
{
	"data": {
		"Series1": [
			10,
			20,
			30,
			40,
			50
		],
		"Series2": [
			40,
			20,
			100
		],
		"Series3": [
			10,
			20,
			80,
			70,
			60
		],
		"bar": [
			20,
			40,
			60,
			70,
			10
		],
		"Month": [
			"2025-01",
			"2025-02",
			"2025-03",
			"2025-04",
			"2025-05"
		]
	},
	"categories": "Month",
	"valueList": [
		"Series1",
		"Series2",
		"Series3"
	],
	"barValue": "bar"
}

实现效果：