记录SpringBoot中使用poi进行excel的导入导出

1. 导入依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.15</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.15</version>
</dependency>

2. 编写工具类

• 多表头导出工具类

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;

/***
 * @ClassName: ExcelUtils
 * @Description:POI实现导出含多级表头和含有表尾部信息的excel
 */
public class ExcelExportUtils {
    /**
     * @Description 多级表头Excel文件导出
     * @Param [sheetName, head, dataList, type, tableEndData，out，titles] sheet名,多级表头,导出数据,导出类型,表尾,输出文件对象,首行标题
     **/
    public static XSSFWorkbook exportMultilevelHeader(String sheetName, String[][] head, List<?> dataList, Class type, String[][] tableEndData, OutputStream out, String titles) {

        /*变量*/
        String[] properties;
        Object[] rowValue;
        List<Object[]> values;
        Field[] fields;
        XSSFCell cell;
        String vo;

        /*导出Excel*/
        // 第一步，创建一个workBook，对应一个Excel文件
        XSSFWorkbook wb = new XSSFWorkbook();
        // 表头 标题样式
        XSSFFont titleFont = wb.createFont();
        titleFont.setColor(IndexedColors.BLUE_GREY.getIndex());
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 18);
        titleFont.setFontName("宋体");//字体
        XSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        titleStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        titleStyle.setFont(titleFont);
        titleStyle.setLocked(true);

        // 第二步，在workBook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = wb.createSheet(sheetName);
        //设置第一行的单元格列宽为20个字符
        for (int i = 0; i < head[1].length; i++) {
            sheet.setColumnWidth(i, 20 * 256);
        }
        sheet.setColumnWidth(2, 24 * 256);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        XSSFRow row;
        // 第四步，创建单元格，并设置值表头 设置表头居中

        XSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 13);
        font.setFontName("宋体");//字体
        //生成一个Style
        XSSFCellStyle style = wb.createCellStyle();
        style.setWrapText(true);
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        style.setFont(font);
        int mergerNum = 0; //合并数
        //添加表格标题
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, type.getDeclaredFields().length - 1));
        row = sheet.createRow(0);//创建一行表格
        row.setHeight((short) 0x349);//设置高度
        cell = row.createCell(0);//创建单元格
        cell.setCellStyle(titleStyle);//设置样式
        cell.setCellValue(titles);//设置标题

        //给单元格设置值
        for (int i = 0; i < head.length; i++) {
            row = sheet.createRow(i + 1);
            row.setHeight((short) 700);
            for (int j = 0; j < head[i].length; j++) {
                cell = row.createCell(j);
                cell.setCellStyle(style);
                cell.setCellValue(head[i][j]);
            }
        }
        Map<Integer, List<Integer>> map = new HashMap<>();   // 合并行时要跳过的行列
        //合并行
        for (int i = 0; i < head[head.length - 1].length; i++) {

            if ("".equals(head[head.length - 1][i])) {

                for (int j = head.length - 2; j >= 0; j--) {

                    if (!"".equals(head[j][i])) {

                        sheet.addMergedRegion(new CellRangeAddress(j + 1, head.length, i, i)); // 合并单元格
                        break;
                    } else {

                        if (map.containsKey(j)) {

                            List<Integer> list = map.get(j);
                            list.add(i);
                            map.put(j, list);
                        } else {

                            List<Integer> list = new ArrayList<Integer>();
                            list.add(i);
                            map.put(j, list);
                        }
                    }
                }
            }
        }
        //合并列
        for (int i = 0; i < head.length - 1; i++) {

            for (int j = 0; j < head[i].length; j++) {

                List<Integer> list = map.get(i);
                if (list == null || (list != null && !list.contains(j))) {

                    if ("".equals(head[i][j])) {

                        mergerNum++;
                        if (mergerNum != 0 && j == (head[i].length - 1)) {

                            sheet.addMergedRegion(new CellRangeAddress(i, i, j - mergerNum, j)); // 合并单元格
                            mergerNum = 0;
                        }
                    } else {

                        if (mergerNum != 0) {

                            sheet.addMergedRegion(new CellRangeAddress(i + 1, i + 1, j - mergerNum - 1, j - 1)); // 合并单元格
                            mergerNum = 0;
                        }
                    }
                }
            }
        }
        //解析导出类型
        Class<Record> recordClass = Record.class;
        if (null == type) {
            //导出失败
            return null;
        } else if (type.equals(recordClass)) {

            //导出List<Record>
            //获取Record中包含的properties，用于生成表格头及创建Cell
            properties = getRecordProperties(dataList, null);
            vo = "record";
        } else {

            //导出List<Bean>
            //获取Bean的Field
            fields = type.getDeclaredFields();
            properties = getRecordProperties(null, fields);
            vo = "bean";
        }

        if (null == head) {

            int i = 0;
            if (head.length > 0) {

                i = head.length - 1;
            }
            head[i] = properties;
        }

        // 第五步，写入实体数据
        /*表头行数*/
        int m = 1;
        if (head.length > 0) {

            m = head.length;
        }
        values = getRowValue(dataList, properties, vo);
        for (int i = 0; i < dataList.size(); i++) {

            row = sheet.createRow(i + m + 1); //创建行
            rowValue = values.get(i);
            // 第四步，创建单元格，并设置值
            for (int j = 0; j < properties.length; j++) {

                cell = row.createCell(j);
                cell.setCellStyle(style);
                setCellValue(cell, rowValue[j]);
            }
        }

        //第六步，处理表格尾部的数据
        if (tableEndData != null && tableEndData.length > 0) {

            for (int i = 0; i < tableEndData.length; i++) {

                row = sheet.createRow(dataList.size() + m + i);
                sheet.addMergedRegion(new CellRangeAddress(dataList.size() + m + i, dataList.size() + m + i, 0, type.getDeclaredFields().length - 1));
                for (int j = 0; j < tableEndData[i].length; j++) {

                    cell = row.createCell(j);
                    cell.setCellStyle(style);
                    setCellValue(cell, tableEndData[i][j]);
                }
            }
        }
        //冻结表头
        wb.getSheet(sheetName).createFreezePane(0, 3, 0, 3);
        try {
            wb.write(out);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * @return java.lang.String[] 包含properties
     * @Description 获取Record包含的所有properties
     * @Param [list, fields] 列名,属性
     **/
    private static String[] getRecordProperties(List<?> list, Field[] fields) {

        if (null != list && null == fields) {

            Record record = (Record) list.get(0);
            Set<String> keySet = record.keySet();
            List<String> keysList = new ArrayList<>(keySet);
            return keysList.toArray(new String[keysList.size()]);
        } else if (null != fields && null == list) {

            String[] properties = new String[fields.length];
            for (int i = 0; i < fields.length; i++) {

                properties[i] = fields[i].getName();
            }
            return properties;
        }
        return new String[0];
    }

    /**
     * @return java.util.List<java.lang.Object [ ]> 转换后的数据
     * @Description 转换列表数据
     * @Param [list, properties, vo] 数据列表,属性列表,类型
     **/
    private static List<Object[]> getRowValue(List<?> list, String[] properties, String vo) {

        List<Object[]> resultList = new ArrayList<>();
        Record record;
        if (StringUtils.isBlank(vo)) {

            return resultList;
        } else if ("record".equals(vo)) {

            for (Object object : list) {

                record = (Record) object;
                Object[] values = new Object[properties.length];    //定义在外部数组值会被最后写入的覆盖
                for (int i = 0; i < properties.length; i++) {


                    values[i] = record.get(properties[i]);

                }
                resultList.add(values);
            }
            return resultList;
        } else if ("bean".equals(vo)) {

            for (Object object : list) {

                Class cf = object.getClass();
                Object[] values = new Object[properties.length];    //定义在外部数组值会被最后写入的覆盖
                for (int i = 0; i < properties.length; i++) {

                    char[] name = properties[i].toCharArray();
                    name[0] -= 32;
                    try {

                        Method method = cf.getMethod("get" + String.valueOf(name));
                        values[i] = method.invoke(object);
                    } catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {

                        e.printStackTrace();
                    }
                }
                resultList.add(values);
            }
            return resultList;
        }
        return resultList;
    }

    /**
     * @return void
     * @Author lyb
     * @Description //TODO 设置单元格值
     * @Date 11:34 2019/12/17
     * @Param [cell, value] 单元格,值
     **/
    private static void setCellValue(XSSFCell cell, Object value) {
        if (null == value) {
            cell.setCellValue("");
        } else if (value instanceof String) {
            cell.setCellValue((String) value);
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
        } else if (value instanceof Date) {

            cell.setCellValue((Date) value);
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
        } else if (value instanceof Boolean) {

            cell.setCellValue((Boolean) value);
            cell.setCellType(XSSFCell.CELL_TYPE_BOOLEAN);
        } else if (value instanceof Double) {

            cell.setCellValue((Double) value);
            cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
        } else if (value instanceof Calendar) {

            cell.setCellValue((Calendar) value);
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
        } else if (value instanceof RichTextString) {

            cell.setCellValue((RichTextString) value);
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
        } else {

            cell.setCellValue(String.valueOf(value));
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
        }
    }
}

• Record类

package com.tjhq.wsjrj.mjz.multipletable.domain;

/**
 * @ClassName: Record
 * @Description:该类用于POI多级表头Excel文件导出,用于接收导出实体属性
 */
import java.math.BigDecimal;
import java.util.Date;
import java.util.LinkedHashMap;

public class Record extends LinkedHashMap<String,Object> {
    public void set(String field,Object value){
        put(field,value);
    }
    public String getString(String field){
        return (String)get(field);
    }
    public Integer getInteger(String field){
        return (Integer)get(field);
    }
    public Long getLong(String field){
        return (Long)get(field);
    }
    public BigDecimal getBigDecimal(String field){
        return (BigDecimal)get(field);
    }
    public Date getDate(String field){
        return (Date)get(field);
    }
    public Boolean getBoolean(String field){
        return (Boolean) get(field);
    }
}

• 测试方法

//获得分页数据list
List<ViewAi> list = viewAiService.list(queryWrapper);
//表头名
String[][] headNames = {{"编号", "姓名", "身份证号", "关联部门", "所属年份", "所属月份", "是否死亡", "死亡时间", "死亡信息详情",
        "退役", "", "", "",
        "残联", "", "", "",
        "民政局", "", "", "", "",
        "乡村振兴", "", "",
        "医保局", "", "",
        "人社局", "", "", "", "", "", "",
        "-"},
        {"", "", "", "", "", "", "", "", "",
                "人员类别", "对象状态", "退伍时间", "入伍时间",
                "残疾类别", "残疾详情", "残疾等级", "持证状态",
                "基本生活保障类型", "儿童保障类型", "残疾人保障类型1", "残疾人保障类型2", "高龄保障类型",
                "户类型", "监测对象类型", "风险是否消除",
                "慢病卡病种", "个人自付金额", "是否资助参保",
                "参保状态", "月待遇金额", "就业单位", "补贴类型", "补贴金额", "培训时间", "培训工种"
        }};
//表尾名
String[][] tableEnd = {{""}};

response.reset();
response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString()));
ExcelExportUtils.exportMultilevelHeader("导出数据", headNames, list, ViewAi.class, tableEnd, response.getOutputStream(), titles);

• 简单导入导出工具类

import cn.hutool.core.date.DateTime;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.CharUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.MediaType;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
import java.util.stream.Stream;


/**
 * 根据poi自定义excel工具类
 */
public class MyExcelUtils {

    static YCLogUtil log = YCLogUtil.log(SysCommon.SYSTEM_LOG_NAME, "mjz");


    private final static String EXCEL2003 = "xls";
    private final static String EXCEL2007 = "xlsx";

    public static <T> List<T> readExcel(Class<T> cls, MultipartFile file) {

        String fileName = file.getOriginalFilename();
        if (!fileName.matches("^.+\.(?i)(xls)$") && !fileName.matches("^.+\.(?i)(xlsx)$")) {
            log.info("上传文件格式不正确:"+fileName);
            return null;
        }
        List<T> dataList = new ArrayList<>();
        Workbook workbook = null;
        try {
            InputStream is = file.getInputStream();
            if (fileName.endsWith(EXCEL2007)) {
                workbook = new XSSFWorkbook(is);
            }
            if (fileName.endsWith(EXCEL2003)) {
                workbook = new HSSFWorkbook(is);
            }
            if (workbook != null) {
                //类映射  注解 value-->bean columns
                Map<String, List<Field>> classMap = new HashMap<>();
                List<Field> fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());
                fields.forEach(
                        field -> {
                            ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                            if (annotation != null) {
                                String value = annotation.value();
                                if (StringUtils.isBlank(value)) {
                                    return;//return起到的作用和continue是相同的 语法
                                }
                                if (!classMap.containsKey(value)) {
                                    classMap.put(value, new ArrayList<>());
                                }
                                field.setAccessible(true);
                                classMap.get(value).add(field);
                            }
                        }
                );
                //索引-->columns
                Map<Integer, List<Field>> reflectionMap = new HashMap<>(16);
                //默认读取第一个sheet
                Sheet sheet = workbook.getSheetAt(0);

                boolean firstRow = true;
                for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    //首行  提取注解
                    if (firstRow) {
                        for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                            Cell cell = row.getCell(j);
                            String cellValue = getCellValue(cell);
                            if (classMap.containsKey(cellValue)) {
                                reflectionMap.put(j, classMap.get(cellValue));
                            }
                        }
                        firstRow = false;
                    } else {
                        //忽略空白行
                        if (row == null) {
                            continue;
                        }
                        try {
                            T t = cls.newInstance();
                            //判断是否为空白行
                            boolean allBlank = true;
                            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                                if (reflectionMap.containsKey(j)) {
                                    Cell cell = row.getCell(j);
                                    String cellValue = getCellValue(cell);
                                    if (StringUtils.isNotBlank(cellValue)) {
                                        allBlank = false;
                                    }
                                    List<Field> fieldList = reflectionMap.get(j);
                                    fieldList.forEach(
                                            x -> {
                                                try {
                                                    handleField(t, cellValue, x);
                                                } catch (Exception e) {
                                                    log.info(String.format("reflect field:%s value:%s exception!", x.getName(), cellValue), e);
                                                }
                                            }
                                    );
                                }
                            }
                            if (!allBlank) {
                                dataList.add(t);
                            } else {
                                log.info(String.format("row:%s is blank ignore!", i));
                            }
                        } catch (Exception e) {
                            log.info(String.format("parse row:%s exception!", i), e);
                        }
                    }
                }
            }
        } catch (Exception e) {
            log.info("parse excel exception!", e);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    log.info(String.format("parse excel exception!"), e);
                }
            }
        }
        return dataList;
    }

    private static <T> void handleField(T t, String value, Field field) throws Exception {
        Class<?> type = field.getType();
        if (type == null || type == void.class || StringUtils.isBlank(value)) {
            return;
        }
        if (type == Object.class) {
            field.set(t, value);
            //数字类型
        } else if (type.getSuperclass() == null || type.getSuperclass() == Number.class) {
            if (type == int.class || type == Integer.class) {
                field.set(t, NumberUtils.toInt(value));
            } else if (type == long.class || type == Long.class) {
                field.set(t, NumberUtils.toLong(value));
            } else if (type == byte.class || type == Byte.class) {
                field.set(t, NumberUtils.toByte(value));
            } else if (type == short.class || type == Short.class) {
                field.set(t, NumberUtils.toShort(value));
            } else if (type == double.class || type == Double.class) {
                field.set(t, NumberUtils.toDouble(value));
            } else if (type == float.class || type == Float.class) {
                field.set(t, NumberUtils.toFloat(value));
            } else if (type == char.class || type == Character.class) {
                field.set(t, CharUtils.toChar(value));
            } else if (type == boolean.class) {
                field.set(t, BooleanUtils.toBoolean(value));
            } else if (type == BigDecimal.class) {
                field.set(t, new BigDecimal(value));
            }
        } else if (type == Boolean.class) {
            field.set(t, BooleanUtils.toBoolean(value));
        } else if (type == Date.class) {
            //
            field.set(t, value);
        } else if (type == String.class) {
            field.set(t, value);
        } else {
            Constructor<?> constructor = type.getConstructor(String.class);
            field.set(t, constructor.newInstance(value));
        }
    }

    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                return HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
            } else {
                return new BigDecimal(cell.getNumericCellValue()).toString();
            }
        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return StringUtils.trimToEmpty(cell.getStringCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return StringUtils.trimToEmpty(cell.getCellFormula());
        } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            return "";
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
            return "ERROR";
        } else {
            return cell.toString().trim();
        }

    }

    public static <T> Result writeExcel(HttpServletResponse response, List<T> dataList, Class<T> cls) {
        log.info("进入writeExcel");
        Field[] fields = cls.getDeclaredFields();
        List<Field> fieldList = Arrays.stream(fields)
                .filter(field -> {
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                    if (annotation != null && annotation.col() > 0) {
                        field.setAccessible(true);
                        return true;
                    }
                    return false;
                }).sorted(Comparator.comparing(field -> {
                    int col = 0;
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                    if (annotation != null) {
                        col = annotation.col();
                    }
                    return col;
                })).collect(Collectors.toList());
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("导出数据");
        AtomicInteger ai = new AtomicInteger();
        {
            Row row = sheet.createRow(ai.getAndIncrement());

            //设置第一行的单元格列宽为20个字符
            for (int i = 0; i < fieldList.size(); i++) {
                sheet.setColumnWidth(i, 20 * 256);
            }
            sheet.setColumnWidth(2, 25 * 256);
            sheet.setColumnWidth(17, 25 * 256);
            sheet.setColumnWidth(18, 25 * 256);
            sheet.setColumnWidth(19, 25 * 256);
            sheet.setColumnWidth(20, 25 * 256);
            sheet.setColumnWidth(21, 25 * 256);

            AtomicInteger aj = new AtomicInteger();
            //写入头部
            fieldList.forEach(field -> {
                ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                String columnName = "";
                if (annotation != null) {
                    columnName = annotation.value();
                }
                //单元格第一行
                Cell cell = row.createCell(aj.getAndIncrement());
                //设置样式
                CellStyle cellStyle = wb.createCellStyle();
                cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
                cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());

                Font font = wb.createFont();
                font.setColor(IndexedColors.BLUE_GREY.getIndex());
                font.setBold(true);
                font.setFontHeightInPoints((short) 16);

                cellStyle.setFont(font);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(columnName);
            });
        }
        if (CollectionUtils.isNotEmpty(dataList)) {
            dataList.forEach(t -> {
                Row row1 = sheet.createRow(ai.getAndIncrement());
                AtomicInteger aj = new AtomicInteger();
                fieldList.forEach(field -> {
                    Class<?> type = field.getType();
                    Object value = "";
                    try {
                        value = field.get(t);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    //单元格内容
                    Cell cell = row1.createCell(aj.getAndIncrement());
                    //设置样式
                    CellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
                    cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());

                    Font font = wb.createFont();
                    font.setFontHeightInPoints((short) 13);

                    cellStyle.setFont(font);
                    cell.setCellStyle(cellStyle);
                    //设置值
                    if (value != null) {
                        if (type == Date.class) {
                            cell.setCellValue(value.toString());
                        } else {
                            cell.setCellValue(value.toString());
                        }
                        cell.setCellValue(value.toString());
                    }
                });
            });
        }
        log.info("=====================================================");
        log.info("导出数据dataList:" + dataList);
        log.info("=====================================================");
        //冻结窗格
        wb.getSheet("导出数据").createFreezePane(0, 1, 0, 1);
//        String resExcelPath = SysCommon.EXPORT_ADDRESS + "导出数据 - " + System.currentTimeMillis() + ".xlsx";
        //生成excel文件
//        buildExcelFile(resExcelPath, wb);
        //浏览器下载excel
        return buildExcelDocument("导出数据" + DateTime.now().toString("yyyy-MM-dd HH:mm") + ".xlsx", wb, response);
    }

    /**
     * 浏览器下载excel
     *
     * @param fileName
     * @param wb
     * @param response
     */

    private static Result buildExcelDocument(String fileName, Workbook wb, HttpServletResponse response) {
        try {
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            // 下载文件能正常显示中文
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString()));
            response.flushBuffer();
            wb.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
            log.info("下载excel失败");
        }
        return Result.OK("下载成功");
    }

    /**
     * 生成excel文件
     *
     * @param path 生成excel路径
     * @param wb
     */
    private static void buildExcelFile(String path, Workbook wb) {
        log.info("生成excel路径:" + path);
        File file = new File(path);
        if (file.exists()) {
            file.delete();
        }
        try {
            wb.write(Files.newOutputStream(file.toPath()));
            log.info("生成成功！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

• 自定义注解

import java.lang.annotation.*;

/**
 * @author mjz
 * 自定义实体类需要的bean(Excel属性标题、位置等)
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelColumn {

    //Excel标题
    String value() default "";

    //Excel从左到右排列位置
    int col() default 0;
}
//实例
public class ExcelVo implements Serializable {

    /**
     * 所属年份
     */
    @ExcelColumn(value = "所属年份", col = 1)
    private String sznf;

    /**
     * 所在月份
     */
    @ExcelColumn(value = "所属月份", col = 2)
    private String szyf;
}

• 导入测试方法

//传入导入类型和excel文件即可获得数据列表
List<CjraExcelVo> vos = MyExcelUtils.readExcel(CjraExcelVo.class, file);

• 导出测试方法

MyExcelUtils.writeExcel(response, dataList,cls);
//会生成一个excel文件输出到response