Hutool 使用 POI 生成自定义 Excel 表格

实现功能

工作中，有许多需要生成 Excel 自定义格式的文件，无需自定义格式的，可以通过 Hutool 工具来进行读取和输出。
如果没有使用 Hutool，也可查看 Apache POI 解析和创建 Excel 文件 文章实现。

本文案例主要涉及内容：合并单元格、单元格内容换行、写入公式、写入图片。

最终效果

主要代码

package com.muycode;

import cn.hutool.core.util.RandomUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.net.URL;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.Map;

/**
 * Created with IntelliJ IDEA by 2022/9/1 9:31.
 *
 * @author muycode
 */
public class ExcelDemo {

    public static void main(String[] args) {
        // 输出文件
        File file = new File("C:/Users/Administrator/Downloads/muycode_test_" + System.currentTimeMillis() + ".xlsx");

        // Hutool 将 Excel 写出封装为 ExcelWriter，原理为包装了 Workbook 对象
        ExcelWriter writer = ExcelUtil.getWriter(file.getPath());

        // 获取 sheet 对象
        Sheet sheet = writer.getSheet();
        // 设置列长
        int lastCell = 7;

        // 设置表头的行焦点
        int headRowIndex = 0;
        // 创建第一行标题
        Row row1 = sheet.createRow(headRowIndex++);
        Cell row1Cell0 = row1.createCell(0);
        row1Cell0.setCellValue(" 测试表头合并单元格 \n 单元格内容换行 ");
        // 合并单元格
        CellRangeAddress rangeAddress1 = new CellRangeAddress(row1.getRowNum(), row1.getRowNum(), 0, lastCell);
        sheet.addMergedRegion(rangeAddress1);
        // 设置表格样式
        setExcelRegionStyle(getCellStyle(writer, true, 16, true, null, true), rangeAddress1, writer);
        // 设置行高
        writer.setRowHeight(row1.getRowNum(), 50);

        // 创建第二行
        Row row2 = sheet.createRow(headRowIndex++);
        writer.setRowHeight(row2.getRowNum(), 20);
        // 第二行列焦点
        int row2CurrentCellIndex = 0;
        // 创建第三行
        Row row3 = sheet.createRow(headRowIndex++);
        writer.setRowHeight(row3.getRowNum(), 20);
        // 第三行列焦点
        int row3CurrentCellIndex = 1;
        // 合并第二、三行单元格
        Cell row2Cell1 = row2.createCell(row2CurrentCellIndex++);
        row2Cell1.setCellValue(" 姓名 ");
        CellRangeAddress row2RangeAddress1 = new CellRangeAddress(row2.getRowNum(), row3.getRowNum(), row2Cell1.getColumnIndex(), row2Cell1.getColumnIndex());
        sheet.addMergedRegion(row2RangeAddress1);
        setExcelRegionStyle(getCellStyle(writer, true, 12, true, null, false), row2RangeAddress1, writer);
        // 设置单元格列宽
        writer.setColumnWidth(row2Cell1.getColumnIndex(), 20);

        // 获取第三个表头内容
        LinkedList<String> heads = getHeads();

        // 合并第二行单元格
        Cell row2Cell2 = row2.createCell(row2CurrentCellIndex++);
        row2Cell2.setCellValue(" 星期 ");
        CellRangeAddress row2RangeAddress2 = new CellRangeAddress(row2.getRowNum(), row2.getRowNum(), row2Cell2.getColumnIndex(), row2Cell2.getColumnIndex() + heads.size());
        sheet.addMergedRegion(row2RangeAddress2);
        setExcelRegionStyle(getCellStyle(writer, true, 12, true, null, false), row2RangeAddress2, writer);

        // 设置第二行列焦点
        row2CurrentCellIndex = row2CurrentCellIndex + heads.size();

        // 合并最后一个单元格
        Cell row2Cell3 = row2.createCell(row2CurrentCellIndex);
        row2Cell3.setCellValue(" 合计 ");
        CellRangeAddress row2RangeAddress3 = new CellRangeAddress(row2.getRowNum(), row3.getRowNum(), row2Cell3.getColumnIndex(), row2Cell3.getColumnIndex());
        sheet.addMergedRegion(row2RangeAddress3);
        setExcelRegionStyle(getCellStyle(writer, true, 12, true, null, false), row2RangeAddress3, writer);
        writer.setColumnWidth(row2Cell3.getColumnIndex(), 15);

        // 动态设置第三行表头
        for (String head : heads) {
            Cell currentCell = row3.createCell(row3CurrentCellIndex++);
            currentCell.setCellValue(head);
            currentCell.setCellStyle(getCellStyle(writer, true, 12, true, null, false));
            writer.setColumnWidth(currentCell.getColumnIndex(), 15);
        }
        // 设置第三行最后一列
        Cell row3LastCell = row3.createCell(row3CurrentCellIndex);
        row3LastCell.setCellValue(" 图片 ");
        row3LastCell.setCellStyle(getCellStyle(writer, true, 12, true, null, false));
        writer.setColumnWidth(row3LastCell.getColumnIndex(), 15);

        // 填充数据
        LinkedHashMap<String, LinkedList<String>> testData = getTestData();
        for (String name : testData.keySet()) {
            int currentIndex = 0;
            // 动态创建行
            Row currentRow = sheet.createRow(headRowIndex++);
            Cell currentCell1 = currentRow.createCell(currentIndex++);
            currentCell1.setCellValue(name);
            currentCell1.setCellStyle(getCellStyle(writer, false, 12, true, null, false));
            // 设置星期数值
            LinkedList<String> list = testData.get(name);
            for (String data : list) {
                Cell currentCell = currentRow.createCell(currentIndex++);
                if (data.contains("http")) {
                    // 说明有图片，需要特殊处理
                    fillExcelImage(sheet, currentRow.getRowNum(), currentCell.getColumnIndex(), data);
                    currentCell.setCellStyle(getCellStyle(writer, false, 12, true, null, false));
                    // 设置高度
                    writer.setRowHeight(currentRow.getRowNum(), 40);
                } else {
                    if (StrUtil.equals(" 无 ", data)) {
                        currentCell.setCellValue(data);
                    } else {
                        // 最后需要对值进行计算，所以需要转换成数值类型，否则不能通过公式计算
                        currentCell.setCellValue(Integer.parseInt(data));
                    }
                    currentCell.setCellStyle(getCellStyle(writer, false, 12, true, null, false));
                }
            }

            // 填充合计列公式
            int rowNum = currentRow.getRowNum() + 1;
            Cell currentLastCell = currentRow.createCell(currentIndex);
            String startLetter = getSheetCellLetter(2);
            String endLetter = getSheetCellLetter(currentIndex - 1);
            String formula = "SUM(" + startLetter + rowNum + ":" + endLetter + rowNum + ")";
            currentLastCell.setCellFormula(formula);
            currentLastCell.setCellStyle(getCellStyle(writer, false, 12, true, null, false));
        }

        // 生成 Excel 文件
        createExcel(writer, file);
    }

    /**
     * 设置表格头内容
     *
     * @return
     */
    private static LinkedList<String> getHeads() {
        LinkedList<String> list = new LinkedList<>();
        list.add(" 星期一 ");
        list.add(" 星期二 ");
        list.add(" 星期三 ");
        list.add(" 星期四 ");
        list.add(" 星期五 ");
        return list;
    }

    /**
     * 模拟数据
     *
     * @return
     */
    private static LinkedHashMap<String, LinkedList<String>> getTestData() {
        LinkedHashMap<String, LinkedList<String>> resultMap = new LinkedHashMap<>(16);
        LinkedList<String> list1 = new LinkedList<>();
        for (int i = 0; i < 5; i++) {
            list1.add(RandomUtil.randomInt(10, 100) + "");
        }
        list1.add(" 无 ");
        resultMap.put(" 张三 ", list1);

        LinkedList<String> list2 = new LinkedList<>();
        for (int i = 0; i < 5; i++) {
            list2.add(RandomUtil.randomInt(10, 100) + "");
        }
        list2.add(" 无 ");
        resultMap.put(" 李四 ", list2);

        LinkedList<String> list3 = new LinkedList<>();
        for (int i = 0; i < 5; i++) {
            list3.add(RandomUtil.randomInt(10, 100) + "");
        }
        list3.add(" 无 ");
        resultMap.put(" 王五 ", list3);

        LinkedList<String> list4 = new LinkedList<>();
        for (int i = 0; i < 5; i++) {
            list4.add(RandomUtil.randomInt(10, 100) + "");
        }
        list4.add(" 无 ");
        resultMap.put(" 赵六 ", list4);

        LinkedList<String> list5 = new LinkedList<>();
        for (int i = 0; i < 5; i++) {
            list5.add(RandomUtil.randomInt(10, 100) + "");
        }
        // 模拟一个图片
        list5.add("https://all-imgs.muycode.com/m-auth.jpg");
        resultMap.put(" 孙七 ", list5);
        return resultMap;
    }

    /**
     * 根据列数获取列字母
     *
     * @param cell
     * @return
     */
    private static String getSheetCellLetter(int cell) {
        Map<Integer, String> map = new HashMap<>(16);
        map.put(1, "A");
        map.put(2, "B");
        map.put(3, "C");
        map.put(4, "D");
        map.put(5, "E");
        map.put(6, "F");
        map.put(7, "G");
        map.put(8, "H");
        map.put(9, "I");
        map.put(10, "J");
        map.put(11, "K");
        map.put(12, "L");
        map.put(13, "M");
        map.put(14, "N");
        map.put(15, "O");
        map.put(16, "P");
        map.put(17, "Q");
        map.put(18, "R");
        map.put(19, "S");
        map.put(20, "T");
        map.put(21, "U");
        map.put(22, "V");
        map.put(23, "W");
        map.put(24, "X");
        map.put(25, "Y");
        map.put(26, "Z");
        return map.get(cell);
    }

    // ================ 辅助方法 ===============================================
    
    /**
     * 获取单元格 样式
     *
     * @param writer
     * @param bold       字体是否加粗
     * @param fontHeight 字体大小
     * @param border     边框
     * @param alignment  对齐方式
     * @param wrapText   换行
     */
    private static CellStyle getCellStyle(ExcelWriter writer, boolean bold, int fontHeight, boolean border, HorizontalAlignment alignment, boolean wrapText) {
        Font font = writer.createFont();
        font.setFontName(" 宋体 ");
        if (bold) {
            font.setBold(true);
        }
        if (fontHeight > 0) {
            font.setFontHeightInPoints((short) fontHeight);
        }
        CellStyle cellStyle = writer.getWorkbook().createCellStyle();
        if (wrapText) {
            cellStyle.setWrapText(true);
        }
        cellStyle.setFont(font);
        if (null == alignment) {
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
        } else {
            cellStyle.setAlignment(alignment);
        }
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        if (border) {
            cellStyle.setBorderBottom(BorderStyle.valueOf((short) 1));
            cellStyle.setBorderTop(BorderStyle.valueOf((short) 1));
            cellStyle.setBorderLeft(BorderStyle.valueOf((short) 1));
            cellStyle.setBorderRight(BorderStyle.valueOf((short) 1));
        }
        return cellStyle;
    }

    /**
     * 设置合并后的单元格样式
     *
     * @param cellStyle
     * @param region
     * @param writer
     */
    private static void setExcelRegionStyle(CellStyle cellStyle, CellRangeAddress region, ExcelWriter writer) {
        Sheet sheet = writer.getSheet();
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            Row row = sheet.getRow(i);
            if (null == row) {
                row = sheet.createRow(i);
            }
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                Cell cell = row.getCell(j);
                if (null == cell) {
                    cell = row.createCell(j);
                    cell.setCellValue("");
                }
                cell.setCellStyle(cellStyle);
            }
        }
    }

    /**
     * 填充图片
     *
     * @param sheet
     * @param rowIndex
     * @param cellIndex
     * @param url
     */
    private static void fillExcelImage(Sheet sheet, int rowIndex, int cellIndex, String url) {
        try {
            Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
            XSSFClientAnchor anchor = new XSSFClientAnchor(100, 100, 100, 100, cellIndex, rowIndex, cellIndex + 1, rowIndex + 1);
            anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_DO_RESIZE);
            ByteArrayOutputStream byteOutPut = new ByteArrayOutputStream();
            BufferedImage bufferImage = ImageIO.read(new URL(url));
            ImageIO.write(bufferImage, "PNG", byteOutPut);
            drawingPatriarch.createPicture(anchor, sheet.getWorkbook().addPicture(byteOutPut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 生成 Excel 文件
     *
     * @param writer Hutool 包装的 Workbook 对象
     * @param file   输出文件
     */
    private static void createExcel(ExcelWriter writer, File file) {
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(file);
            writer.getWorkbook().write(fileOutputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                // 关闭 ExcelWriter 对象, 否则带有数据的 Workbook 一直会常驻内存
                if (null != writer) {
                    writer.close();
                }
                if (null != fileOutputStream) {
                    fileOutputStream.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}