POI 生成自定义表格

实现结果

相关方法

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();
        }
    }
}