分享一下简易的POI Excel工具类，快速导出excel_poi excel 工具 getworkbook-CSDN博客

本文链接：https://blog.csdn.net/SerikaOnoe/article/details/115131780

PoiExcelHelper

工具类说明

能够导出最简单的数据表，通过web流下载
行高会根据换行符 \n\r 自动判断，也可以自己设置正则
列宽会根据换行符 \n\r 自动判断，也可以自己设置正则
有默认的单元格和Header样式，可以自己设置
由于业务数据多变，因此每一列的数据处理没有继续封装（如传入富文本之类的情况）
使用起来已经算是非常简洁，可以看具体示例
导入还没做（偷懒）

依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.16</version>
</dependency>

效果图

使用示例

/**
 * 导出excel示例
 */
@GetMapping(value = "/exportXls")
@ApiOperation(value = "excel导出")
public void exportXls(HttpServletResponse response) throws IOException {
    //行数据，从其他业务接口来，这里mock一下 todo
    List<Map<String, Object>> dataRows = new ArrayList<>();
    Map<String, Object> row = new HashMap<>();
    row.put("order", "01");
    row.put("partProject", "地基工程");
    row.put("descritopn", "基底浮土清理不干净");
    row.put("changeRequirement", "无");
    row.put("problemLevel", "四级");
    row.put("status", "正常");
    row.put("limitTime", "3");
    dataRows.add(row);

    //开始生成表格
    PoiExcelHelper poiExcelHelper = new PoiExcelHelper();
    poiExcelHelper.createSheet("质量问题库");
    List<String> headerValues = Arrays.asList("序号", "分部分项工程", "描述", "整改要求", "问题等级", "状态", "整改时限");
  	//表头
    poiExcelHelper.createHeaderRow(headerValues);
    //数据行
    dataRows.forEach(data -> {
        HSSFRow dataRow = poiExcelHelper.createRow();
        //序号
        Object order = data.get("order");
        poiExcelHelper.createCell(dataRow, order);
        //分部分项工程
        Object partProject = data.get("partProject");
        poiExcelHelper.createCell(dataRow, partProject);
        //描述，可以单独设置cell的富文本
        String descritopn = (String) data.get("descritopn");
        Font font = poiExcelHelper.getWorkbook().createFont();
        font.setColor(HSSFColor.HSSFColorPredefined.LIGHT_BLUE.getIndex());
        HSSFRichTextString richDescription = new HSSFRichTextString(descritopn);
        richDescription.applyFont(0, descritopn.length(), font);
        poiExcelHelper.createCell(dataRow, richDescription);
        //整改要求
        Object changeRequirement = data.get("changeRequirement");
        poiExcelHelper.createCell(dataRow, changeRequirement);
        //问题等级
        Object problemLevel = data.get("problemLevel");
        poiExcelHelper.createCell(dataRow, problemLevel);
        //状态
        Object status = data.get("status");
        poiExcelHelper.createCell(dataRow, status);
        //整改时限
        Object limitTime = data.get("limitTime");
        poiExcelHelper.createCell(dataRow, limitTime);
    });
    //web下载
    poiExcelHelper.downloadWebFile(response, "质量问题库");
}

工具类

package org.jeecg.config;

import lombok.Setter;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * @author Yoko
 * @date 2021.03.23
 * @description POI Excel帮助工具类封装
 */
@Setter
public class PoiExcelHelper {

    private static final String DEFAULT_SHEET_NAME = "默认";

    public double baseHeightUnit = 256 * 1.5;
    public double baseWidthUnit = 256 * 3.0;
    public String defaultFontFamily = "宋体";
    public short defaultFontSize = 11;
    public short defaultFontColor = HSSFFont.COLOR_NORMAL;

    private HSSFWorkbook workbook;
    private HSSFSheet defaultSheet;

    private HSSFFont font;
    private HSSFFont boldFont;
    private HSSFCellStyle cellStyle;
    private HSSFCellStyle boldCellStyle;

    public PoiExcelHelper(HSSFWorkbook workbook) {
        this.workbook = workbook;
    }

    public PoiExcelHelper() {
    }

    public HSSFWorkbook getWorkbook() {
        if (null == workbook) {
            workbook = new HSSFWorkbook();
        }
        return workbook;
    }

    public HSSFSheet getDefaultSheet() {
        if (null == defaultSheet) {
            defaultSheet = getWorkbook().getSheet(DEFAULT_SHEET_NAME);
        }
        return defaultSheet;
    }

    public HSSFFont getFont() {
        if (null == font) {
            font = getWorkbook().createFont();
            font.setFontHeightInPoints(defaultFontSize); //字体高度
            font.setColor(defaultFontColor); //字体颜色
            font.setFontName(defaultFontFamily); //字体
        }
        return font;
    }

    public HSSFFont getBoldFont() {
        if (null == boldFont) {
            boldFont = getWorkbook().createFont();
            boldFont.setFontHeightInPoints(defaultFontSize); //字体高度
            boldFont.setColor(defaultFontColor); //字体颜色
            boldFont.setFontName(defaultFontFamily); //字体
            boldFont.setBold(true);
        }
        return boldFont;
    }

    public HSSFCellStyle getCellStyle() {
        if (null == cellStyle) {
            cellStyle = getWorkbook().createCellStyle();
            cellStyle.setFont(getFont());
            cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平布局：居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//单元格垂直居中
            cellStyle.setWrapText(true);//换行
        }
        return cellStyle;
    }

    public HSSFCellStyle getBoldCellStyle() {
        if (null == boldCellStyle) {
            boldCellStyle = getWorkbook().createCellStyle();
            boldCellStyle.cloneStyleFrom(getCellStyle());
            boldCellStyle.setFont(getBoldFont());
        }
        return boldCellStyle;
    }

    public HSSFSheet createSheet(String sheetName) {
        if (null == workbook) {
            workbook = new HSSFWorkbook();
        }
        HSSFSheet sheet = workbook.createSheet(sheetName);
        if (defaultSheet == null) {
            defaultSheet = sheet;
        }
        return sheet;
    }

    public HSSFSheet getSheet(String sheetName) {
        return getWorkbook().getSheet(sheetName);
    }

    public HSSFSheet getSheet(int sheetIndex) {
        return getWorkbook().getSheetAt(sheetIndex);
    }

    /**
     * 自动创建新行
     *
     * @param sheet
     * @param rownum
     * @return
     */
    public HSSFRow createRow(HSSFSheet sheet, int rownum) {
        if (null == sheet) {
            sheet = getDefaultSheet();
        }
        if (rownum < 0) {
            rownum = sheet.getLastRowNum() + 1;
        }
        return sheet.createRow(rownum);
    }

    public HSSFRow createRow(int rownum) {
        return createRow(null, rownum);
    }

    public HSSFRow createRow(HSSFSheet sheet) {
        return createRow(sheet, -1);
    }

    public HSSFRow createRow() {
        return createRow(null, -1);
    }

    /**
     * 创建header头
     *
     * @param sheet
     * @param values
     * @return
     */
    public HSSFRow createHeaderRow(HSSFSheet sheet, List<String> values) {
        if (null == sheet) {
            sheet = getDefaultSheet();
        }
        HSSFRow header = createRow(0);
        for (int i = 0; i < values.size(); i++) {
            String value = values.get(i);
            setColumnWidth(sheet, i, value, null);
            HSSFCell cell = header.createCell(i);
            cell.setCellStyle(getBoldCellStyle());
            cell.setCellValue(value);
        }
        return header;
    }

    public HSSFRow createHeaderRow(List<String> values) {
        return createHeaderRow(null, values);
    }

    public HSSFCell createCell(HSSFRow row, Object value) {
        return createCell(row, -1, value, null, true, true);
    }

    /**
     * 创建默认cell
     *
     * @param row
     * @param value
     * @param style
     * @param autoWidth
     * @param autoHeight
     * @return
     */
    public HSSFCell createCell(HSSFRow row, int index, Object value, HSSFCellStyle style, boolean autoWidth, boolean autoHeight) {
        if (null == row) return null;
        if (null == style) {
            style = getCellStyle();
        }
        HSSFCell newCell = null;
        //初始格子
        int lastCellNum = row.getLastCellNum();
        if (-1 == lastCellNum) {
            //没有第0格的时候，值为-1，有了第0格，值会变成1
            lastCellNum = 0;
        }
        if (index > -1) {
            lastCellNum = index;
        }
        newCell = row.createCell(lastCellNum);
        newCell.setCellStyle(style);
        //值封装
        if (value == null) {
            value = "";
        }
        if (value instanceof String || value instanceof Integer || value instanceof Short) {
            newCell.setCellValue(value + "");
        }
        if (value instanceof Date) {
            newCell.setCellValue((Date) value);
        }
        if (value instanceof Calendar) {
            newCell.setCellValue((Calendar) value);
        }
        if (value instanceof Double || value instanceof Float) {
            newCell.setCellValue(Double.valueOf(value + ""));
        }
        if (value instanceof RichTextString) {
            newCell.setCellValue((RichTextString) value);
        }

        if (autoWidth) {
            setColumnWidth(row.getSheet(), newCell.getColumnIndex(), value.toString(), null);
        }
        if (autoHeight) {
            setRowHeight(row, value.toString());
        }
        return newCell;
    }


    /**
     * 根据内容长度设置单元格的高度，若未超过当前高不设置
     *
     * @param row
     * @param valStr
     */
    public void setRowHeight(HSSFRow row, String valStr) {
        setRowHeight(row, valStr, null);
    }

    /**
     * 根据内容长度设置单元格的高度，若未超过当前高不设置
     *
     * @param row
     * @param valStr
     * @param reg    自定义正则
     */
    public void setRowHeight(HSSFRow row, String valStr, String reg) {
        if (null == valStr) valStr = "";
        if (null == reg || "".equals(reg.trim())) {
            reg = "\r\n";
        }
        String[] split = valStr.split(reg);
        short newHeight = (short) (split.length * baseHeightUnit);
        if (row.getHeight() < newHeight) {
            row.setHeight(newHeight);
        }
    }

    /**
     * 根据内容长度设置sheet列的宽度，若未超过当前宽不设置
     *
     * @param sheet
     * @param columnIndex
     * @param valStr
     */
    public void setColumnWidth(HSSFSheet sheet, int columnIndex, String valStr, String reg) {
        if (null == sheet) {
            sheet = getDefaultSheet();
        }
        if (null == valStr){ valStr = "";}

        //判断当前宽度
        int width = sheet.getColumnWidth(columnIndex);
        int newWidth = width;
        //判断每一行的数据长度，遍历每行取最长
        if(null == reg || "".equals(reg.trim())){ reg = "\r\n";}
        for (String line : Arrays.asList(valStr.split(reg))) {
            double lineWidth = line.length() * baseWidthUnit;
            if (lineWidth > newWidth) {
                newWidth = (int) lineWidth;
            }
        }
        if (newWidth > width) {
            sheet.setColumnWidth(columnIndex, newWidth);
        }
    }

    public void setColumnWidth(int columnIndex, String valStr) {
        setColumnWidth(null, columnIndex, valStr, null);
    }

    /**
     * 下载excel文件
     *
     * @param response
     * @param name     带.xls后缀
     * @throws IOException
     */
    public void downloadWebFile(HttpServletResponse response, String name) throws IOException {
        OutputStream output = response.getOutputStream();
        if (!name.contains(".xls")) {
            name += ".xls";
        }
        //中文乱码处理
        String fileName = URLEncoder.encode(name, "utf-8");
        response.setHeader("Content-disposition",
                "attachment;filename=" + fileName +
                        ";filename*=utf-8''" + fileName);
        response.setContentType("application/x-download");//下面三行是关键代码，处理乱码问题
        response.setCharacterEncoding("utf-8");
        getWorkbook().write(output);
        output.close();
    }

}