Hutool导出Excel支持多级表头、自定义RGB颜色设置、百分比数据格式

　　本文主要是使用hutool工具生成一个Excel文件，包括表头合并、自定义背景颜色设置，单元格百分比数据格式设置等功能点。

注意点：

1. XSSFCellStyle 可以 setFillForegroundColor(XSSFColor color)， 而CellStyle 只支持 setFillForegroundColor(short var1)
2. 表头未合并的单元格需单独设置样式
3. RGB颜色 new XSSFColor(rgbB, null)
4. 百分比样式可由 NumberUtil.formatPercent(0.0234, 2) 替换

以下是可以直接运行的demo

package com.xx.common.util;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.StyleSet;
import org.apache.commons.codec.DecoderException;
import org.apache.commons.codec.binary.Hex;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;

import java.io.File;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.Map;


public class HutoolExcelTest {

    public static void main(String[] args) {
        Map<String, String> titleNameMap = new LinkedHashMap<>();
        titleNameMap.put("name", "姓名");
        titleNameMap.put("age", "年龄");
        titleNameMap.put("score", "成绩");
        titleNameMap.put("pass", "是否合格");
        titleNameMap.put("date", "考试日期");

        Map<String, Object> row1 = new LinkedHashMap<>();
        row1.put("name", "张三");
        row1.put("age", 23);
        row1.put("score", 0.3262);
        row1.put("pass", true);
        row1.put("date", DateUtil.date());

        Map<String, Object> row2 = new LinkedHashMap<>();
        row2.put("name", "李四");
        row2.put("age", 33);
        row2.put("score", 0.5016);
        row2.put("pass", false);
        row2.put("date", DateUtil.date());

        ArrayList<Map<String, Object>> rows = CollUtil.newArrayList(row1, row2);

        File existFile = new File("/Users/test.xlsx");
        existFile.delete();

        // 通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getWriter("/Users/test.xlsx");
        // 表头合并
        for (int i = 0; i < 1; i++) {
            // 创建一行空表头占位置
            writer.writeHeadRow(new LinkedList<>());
        }

        writer.merge(0, 1, 0, 0, "姓名合并", assembleHeadFieldStyle(writer, "#008A6C"));
        writer.merge(0, 1, 1, 1, "年龄合并", assembleHeadFieldStyle(writer, "#DB70DB"));
        writer.merge(0, 0, 2, 3, "成绩合并", assembleHeadFieldStyle(writer, "#856363"));
        writer.merge(0, 1, 4, 4, "考试日期", assembleHeadFieldStyle(writer, "#238E23"));

        // 合并单元格后的标题行，使用默认标题样式
        //writer.merge(4, "一班成绩单");

        writer.setOnlyAlias(true).setHeaderAlias(titleNameMap);
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(rows, true);

        Workbook workbook = writer.getWorkbook();
        org.apache.poi.ss.usermodel.Font headFont = workbook.createFont();
        headFont.setColor(IndexedColors.WHITE.getIndex());
        headFont.setBold(true);

        //覆写某个单元格的值和样式
        Cell cell = writer.getCell(2, 1);
        cell.setCellValue("成绩");
        XSSFCellStyle cellStyle = (XSSFCellStyle) writer.createCellStyle();
        cellStyle.setFont(headFont);
        cellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cell.setCellStyle(cellStyle);

        Cell cell2 = writer.getCell(3, 1);
        cell2.setCellValue("是否合格");
        XSSFCellStyle cellStyle1 = (XSSFCellStyle) writer.createCellStyle();
        cellStyle1.setFont(headFont);
        cellStyle1.setFillForegroundColor(IndexedColors.RED.getIndex());
        cellStyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle1.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cell2.setCellStyle(cellStyle1);


        CellStyle headCellStyle = writer.getHeadCellStyle();
        headCellStyle.setBorderTop(BorderStyle.NONE);
        headCellStyle.setBorderBottom(BorderStyle.NONE);
        headCellStyle.setBorderLeft(BorderStyle.NONE);
        headCellStyle.setBorderRight(BorderStyle.NONE);

        Integer rowsCnt = writer.getRowCount() - 1;
        // 方式一：设置格式为百分比　
        CellStyle percentStyle = createBaseCellStyle(writer);
        short pformat = workbook.createDataFormat().getFormat("0.00%");
        percentStyle.setDataFormat(pformat);
        for (int i = 2; i <= rowsCnt; i++) {   // 这里`i`从`1`开始，是为了排除表头　
            // 你需要知道使用哪一列的index，这里假设我们设置第一列，即index为`0`
            Cell celli = writer.getCell(2, i);
            celli.setCellStyle(percentStyle);
        }

        //获取整个Excel的样式，设置单元格格式为文本
        StyleSet styleSet = writer.getStyleSet();
        CellStyle txtCellStyle = styleSet.getCellStyleForNumber();
        DataFormat format = writer.getWorkbook().createDataFormat();
        txtCellStyle.setDataFormat(format.getFormat("@"));
        writer.setStyleSet(styleSet);

        // 方式二：获取cell value，然后使用以下工具类进行格式转换后，再次 set cell value
        String a = NumberUtil.formatPercent(0.0234, 2);
        String b = NumberUtil.formatPercent(0.0234, 1);
        //0 =》 取一位整数
        //0.00 =》 取一位整数和两位小数
        //00.000 =》 取两位整数和三位小数
        //# =》 取所有整数部分
        //#.##% =》 以百分比方式计数，并取两位小数
        //#.#####E0 =》 显示为科学计数法，并取五位小数
        //,### =》 每三位以逗号进行分隔，例如：299,792,458
        String c = NumberUtil.decimalFormat("#", 1.1234);
        String d = NumberUtil.decimalFormat("#.0", 1.1234);
        String e = NumberUtil.decimalFormat("#.00", 1.1234);
        String f = NumberUtil.decimalFormat(",###.0", 12345);
        double div = NumberUtil.div(67, 1000, 2);
        System.err.println(a + ", " + b + " " + c + ", " + d + ", " + e + ", " + f + "," + div + "k元");


        // 关闭writer，释放内存
        writer.close();
    }

    private static XSSFCellStyle createBaseCellStyle(ExcelWriter writer) {
        XSSFCellStyle cellStyle = (XSSFCellStyle) writer.createCellStyle();
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        return cellStyle;
    }

    public static XSSFCellStyle assembleHeadFieldStyle(ExcelWriter writer, String hex) {
        Workbook workbook = writer.getWorkbook();
        org.apache.poi.ss.usermodel.Font headFont = workbook.createFont();
        headFont.setColor(IndexedColors.WHITE.getIndex());
        headFont.setBold(true);
        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) writer.createCellStyle();
        xssfCellStyle.setFont(headFont);
        xssfCellStyle.setBorderTop(BorderStyle.NONE);
        xssfCellStyle.setBorderBottom(BorderStyle.NONE);
        xssfCellStyle.setBorderLeft(BorderStyle.NONE);
        xssfCellStyle.setBorderRight(BorderStyle.NONE);
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        xssfCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        xssfCellStyle.setFillForegroundColor(hexToRgbColor(hex));

        return xssfCellStyle;
    }

    public static XSSFColor hexToRgbColor(String hex) {
        // 去掉 # 符号
        hex = hex.replace("#", "");
        byte[] rgbB = new byte[0];
        try {
            rgbB = Hex.decodeHex(hex);
        } catch (DecoderException e) {
            e.printStackTrace();
        }
        return new XSSFColor(rgbB, null);
    }

}