java中poi导出导入excel

1、excel导出方法

1.1 导出代码

jar包：easypoi-base

public static void exportExcel4Map(List<LinkedHashMap<String, Object>> rows, String sheetName, HttpServletResponse response) {
    try {
        if (CollectionUtils.isEmpty(rows)) {
            rows.addAll(EMPTY_ROWS);
        }
        Workbook workbook = generateWorkbook4Map(rows, sheetName);
        String fileName = LocalDateTime.now().format(DateTimeFormatter.ISO_DATE_TIME) + ".xlsx";
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));

        try(OutputStream outputStream = response.getOutputStream()){
            workbook.write(outputStream);
            response.flushBuffer();
            workbook.close();
        }
    } catch (Exception e) {
        log.error("Excel导出错误{}", e);
        throw new RuntimeException(e.getMessage());
    }
}

public static synchronized Workbook generateWorkbook4Map(List<LinkedHashMap<String, Object>> rows, String sheetName) throws Exception {
    ExportParams exportParams = new ExportParams();
    exportParams.setSheetName(sheetName);
    exportParams.setDynamicData(true);

    Workbook workbook = ExcelExportUtil.exportBigExcel(exportParams, generateDynamicField4Map(rows.get(0)), rows);
    workbook.getSheet(sheetName).getRow(0).setZeroHeight(true);
    ExcelExportUtil.closeExportBigExcel();
    return workbook;
}

public static List<ExcelExportEntity> generateDynamicField4Map(LinkedHashMap<String, Object> row) {
    List<ExcelExportEntity> excelExportEntities = new ArrayList<>(row.size());

    row.keySet().stream().forEach(key -> excelExportEntities.add(new ExcelExportEntity(key, key)));
    return excelExportEntities;
}

1.2 设置隐藏行和隐藏列

隐藏行
sheet.setColumnHidden((short)12, true); // 将第13列隐藏
隐藏行：
sheet.getRow(8).setZeroHeight(true);
将第8行隐藏就是将他的高度设为0也等同为隐藏
注意excel的第一列/行用0表示

2、excel导入

2.1 配置文件配置上传文件大小

https://blog.csdn.net/qq_28089993/article/details/76854112
application.properties

spring.servlet.multipart.max-file-size=20MB
spring.servlet.multipart.max-request-size=20MB

application.yml

spring:
  servlet:
    multipart:
      max-file-size: 20MB
      max-request-size: 20MB

2.2 接收文件方法

form表单接收
前端发送类型：enctype=“multipart/form-data”
后端接收入参：
@PostMapping("/uploadTemplateFile")
(@RequestParam(“file”) MultipartFile file, @Valid PrUploadAttachmentParam uploadAttachmentParam)

2.3 导入解析工具类

package com.cainiao.hrwork.payroll.controller.excel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.cainiao.hrwork.payroll.constant.CommonConstants;
import com.cainiao.hrwork.payroll.constant.PrExcelParserConstant;
import org.apache.commons.lang3.StringUtils;

import java.util.*;

/**
 * @description: excel读取处理类
 * @author: caolei
 * @create: 2020-09-03 17:50
 **/
public class PrExcelListener extends AnalysisEventListener<Map<Integer, String>> {

    private List<String> title;

    private int headNumber = 1;

    private int index = 1;

    private Map<Integer, String> headMap;

    private boolean isParseEnd = false;

    private List<Map<String, String>> excelLineList = new ArrayList<>();

    public PrExcelListener(List<String> title, Integer headNumber) {
        this.headNumber = headNumber;
        this.title = title;
    }

    /**
     * 只有读完才能获取到文件内容
     * @return List<Map<String, String>>
     * @throws NullPointerException
     */
    public List<Map<String, String>> getList() throws NullPointerException {
        if (isParseEnd) {
            return excelLineList;
        } else {
            throw new RuntimeException("excel读取失败，请确认模板是否正确");
        }
    }

    /**
     *  EasyExcel默认将数据读取为一个Map<Integer,String>=new LinkedHashMap<>();Integer从0开始代表第一列
     *  不传递java模型时用Map<Integer,String> lineMap接收，传递java模型EasyExcel则会自动封装为模型
     *  Map的key为value在excel中的列数，据此匹配表头和行数据，使其一一对应
    **/
    @Override
    public void invoke(Map<Integer, String> lineMap, AnalysisContext analysisContext) {
        if (headMap == null) {
            throw new RuntimeException("模板不正确");
        }
        Map<String, String> line = new HashMap<>();
        for (Map.Entry<Integer, String> entry : headMap.entrySet()) {
            String title = entry.getValue();
            Integer column = entry.getKey();
            //无关列判读表头时不限制，但无关列的数据读内容时略去
            if (this.title.contains(title)) {
                line.put(title, lineMap.get(column) == null ? null : lineMap.get(column).trim());
            }
        }
        excelLineList.add(line);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        isParseEnd = true;
    }

    /**
     *  从零行开始过滤直到构造函数传递的参数预定的表头行，并删除表头行中值为空的列，验证表头的完整性
     **/
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        if (headNumber != index) {
            index++;
            return;
        }

        List<String> errorTitleDetail = new ArrayList<>();
        if (title == null) {
            throw new RuntimeException("模板不正确");
        }
        // 注释后不保证表头的完全一致，只保证构造传入的表头一定存在
        if (headMap.size() < title.size()) {
            throw new RuntimeException("模板不正确，期望：" + title.size() + "列，实际：" + headMap.size() + "列");
        }

        for (int i = 0; i < title.size(); i++) {
            String s = title.get(i);
            if (!headMap.containsValue(s)) {
                errorTitleDetail.add(PrExcelParserConstant.INDEX_COLUMN.get(i));
            }
        }
        if (errorTitleDetail.size() != 0) {
            throw new RuntimeException("缺少原模板必须的列，缺少列:" + StringUtils.join(errorTitleDetail, CommonConstants.SEPARATOR));
        }
        Iterator<Map.Entry<Integer, String>> iterator = headMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<Integer, String> entry = iterator.next();
            if (entry.getValue() == null) {
                iterator.remove();
            }
        }
        this.headMap = headMap;
    }
}


 PrExcelListener listener = new PrExcelListener(PrExcelParserConstant.EXCEL_TITLE, PrExcelParserConstant.HEAD_ROW);
List<Map<String, String>> dataList = PrEasyExcelReader.read(file.getInputStream(), listener, PrExcelParserConstant.SHEET_NAME, PrExcelParserConstant.HEAD_ROW);
           

package com.cainiao.hrwork.payroll.controller.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import org.apache.commons.lang3.StringUtils;

import java.io.InputStream;
import java.util.List;
import java.util.Map;

/**
 * @description: excel读取处理类
 * @author: caolei
 * @create: 2020-09-03 17:30
 **/
public class PrEasyExcelReader {
    private PrEasyExcelReader() {
        throw new IllegalThreadStateException("PrEasyExcelReader have no construct method");
    }

    public static List<Map<String, String>> read(InputStream inputstream, PrExcelListener listener, String sheetName, Integer headRow) {
        Integer headRowNumber = 1;
        if (headRow != null && headRow != 0) {
            headRowNumber = headRow;
        }
        ExcelReaderBuilder builder = EasyExcel.read(inputstream, null, listener).headRowNumber(headRowNumber);
        if (StringUtils.isBlank(sheetName)) {
            builder.sheet().doRead();
        } else {
            builder.sheet(sheetName).doRead();
        }
        return listener.getList();
    }
}