使用POI创建Excel文档，封装成常用格式的工具类

使用POI创建Excel文档，封装成常用格式的工具类

如果对格式没有特殊要求，可以直接把下面的工具类拿去用
汇总数据库的数据，可以使用工具类快速把数据转换成Excel文档（笔者经常使用把生产环境的数据转换成Excel文档）

package net.mingsoft.mdiy.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @author hehui
 * @date 2020/10/15
 */
public class DocumentUtil {

    private static Logger log = LoggerFactory.getLogger(DocumentUtil.class);

    /**
     * 根据给定数据生成excel文档
     * @param path 文档存放路径，绝对路径
     * @param title 自定义标题
     * @param resultList 数据
     * @param colNames 表头
     * @return
     */
    public static String createExcelFile(String path,String title, List<Map<String, Object>> resultList,Map<String, String> colNames){
        try {
            OutputStream out = new FileOutputStream(path);

            Workbook wb = new HSSFWorkbook();

            setStyleAndData4Wb(resultList, colNames,title, wb);

            wb.write(out);
            return path;
        } catch (Exception e) {
            log.error("error",e);
            return null;
        }
    }

    private static void setStyleAndData4Wb(List<Map<String, Object>> resultList, Map<String, String> colNames,String title, Workbook wb) {
        Sheet sheet = wb.createSheet(title);
        sheet.setDefaultColumnWidth(20);

        //设置标题
        setSheetTitle(resultList, wb,title, sheet);

        //设置表头（以是否有数据为前提）
        setSheetHeader(resultList, colNames, wb, sheet);

        for (int i = 0; i < resultList.size(); i++) {
            //内容
            Row row = sheet.createRow(i + 2);
            CellStyle cellStyle = getCellStyle(wb, false, (short) 11, Font.COLOR_NORMAL);
            Map<String, Object> data = resultList.get(i);
            Set<Map.Entry<String, Object>> entrySet = data.entrySet();
            int j = 0;
            for (Map.Entry<String, Object> entry : entrySet) {
                Cell cell = row.createCell(j++);
                cell.setCellValue(entry.getValue() == null ? "" : String.valueOf(entry.getValue()));
                cell.setCellStyle(cellStyle);
            }
        }
    }

    private static void setSheetTitle(List<Map<String, Object>> resultList, Workbook wb,String title, Sheet sheet) {
        int mergeCellNum = 11;
        if (resultList != null && resultList.size() > 0) {
            Map<String, Object> data = resultList.get(0);
            Set<String> keySet = data.keySet();
            mergeCellNum = keySet.size();
        }
        Row row = sheet.createRow(0);
        row.setHeightInPoints((short) 30);
        Cell cell = row.createCell(0);
        cell.setCellValue(title);
        cell.setCellStyle(getCellStyle(wb, true, (short) 14, Font.COLOR_NORMAL));
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0,0,0,mergeCellNum - 1);
        sheet.addMergedRegion(cellRangeAddress);
    }

    private static void setSheetHeader(List<Map<String, Object>> resultList, Map<String, String> colNames, Workbook wb, Sheet sheet) {
        if (resultList != null && resultList.size() > 0) {
            Row row = sheet.createRow(1);
            CellStyle cellStyle = getCellStyle(wb, false, (short) 12, Font.COLOR_RED);
            Map<String, Object> data = resultList.get(0);
            Set<String> keySet = data.keySet();
            int j = 0;
            for (String key : keySet) {
                Cell cell = row.createCell(j++);
                cell.setCellValue(colNames.get(key));
                cell.setCellStyle(cellStyle);
            }
        }
    }

    private static CellStyle getCellStyle(Workbook wb, boolean isBold, short fontSize, short fontColor) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        Font cellFont = wb.createFont();
        cellFont.setBold(isBold);
        cellFont.setFontHeightInPoints(fontSize);
        cellFont.setColor(fontColor);

        cellStyle.setFont(cellFont);

        return cellStyle;
    }

}

使用示例