根据 hutool 自定义 导入导出 xlsx 的操作

实测, 200万条数据,6列, 经过字符串替换和时间格式转换 耗时为 30秒 - 40秒
实测, 200万条数据,6列, 经过字符串替换和时间格式转换 耗时为 30秒 - 40秒
实测, 200万条数据,6列, 经过字符串替换和时间格式转换 耗时为 30秒 - 40秒

这边有hutool的导出和导入两种

首先引入以下依赖

创建五个文件：

1. @interface Excel
2. interface ExportUtil
3. class ExportUtils
4. class ExportBigUtils
5. class ImportUtils

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-poi</artifactId>
            <version>5.8.5</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

1. @Excel 导出导入类注解

import org.springframework.core.annotation.AliasFor;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @author xyy
 * @DateTime 2022/8/18 10:58
 * @ClassName Excel
 */
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface Excel {

    @AliasFor("name")
    String value() default "";

    @AliasFor("value")
    String name() default "";

    int index() default 0;

    /**
     * 分隔符,导入导出都能用
     * split = {"0_女","1_男"}
     *
     * @return
     */
    String[] split() default {};

    /**
     * 分组
     * 字符串格式, 可以多个
     *
     * @return
     */
    String[] groups() default {};
}


//使用方式
//class user implements Serializable{
//	@Excel(name = "学员id",index = "1", group = {"user"})
//	int id;
//	@Excel(name = "性别",index = "2", split = {"0_女", "1_男"})
//	Integer sex;
	//setter...  and  getter....
//}

2. hutool 导出文件

2.1 使用方式

		//sql查询
		List<UserPo> list = userDao.findAll();
		//直接使用
        ExportUtil.createBigFile("导出的文件名")
               // .setTotal(list,UserPo.class,"a组")  //这个可以分组
                .setTotal(list,UserPo.class)
                .exportFile(response);

结果:

其他使用方式:
可以用,但不建议, 一般使用class , 注解, list<,po>的数据格式导出, 不建议使用map作为头

		List<User> list = new ArrayList<>();
        //表头
        Map map = new HashMap();
        map.put("id","学员id");
        map.put("name","名字");
        
		//1. 直接导出
		ExportUtil.createFile("fileName").exportFile(list,User.class,"sheetName",httpServletResponse);
		
		//2. 分别设置头部标签和数据  addTodata()追加数据,默认不重复设置表头
		ExportUtil.createFile("fileName")
			.setTitle(map)
			//.setTitle(User.class)
			.setData(list)
			.addToData(list)
			.exportFile(httpServletResponse);
		
		//3. 使用setTotal可以同时设置表头和表体
		ExportUtil.createFile("fileName").setTotal(list,User.class).exportFile(httpServletResponse);
		
		//4. 使用nextSheet(null)切换一下个标签页
		ExportUtil.createFile("fileName").setTotal(list,User.class)
				.nextSheet("sheetName  or  null").exportFile(httpServletResponse);

		//5. 导出百万千万数据使用createBigFile
		//office07版默认1048576 row
		//所以设定一个sheet默认100W条数据,插入数据超过100W自动切换到下一个sheet,
		// 表头 = 100万01条数据,平均55秒可以写完1个sheet(100W的row)
		 for (int i = 0; i < 1000002; i++) {
            User user = new User(i,"name"+i);
            list.add(user);
        }
		ExportUtil.createBigFile("fileName")
				//设置当前页是否只展示表头数据,如果在此之前调用了setData(),addToData(),setTotal(),则当前页失效
				.setOnlyAlias(true)
				//下一次 追加数据前是否有表头
				.setWriteKeyAsHead(true)
                .setTotal(list,User.class)
                //追加
                .addToData(list)
                .exportFile(response);

2.2 ExportUtil 具体实现帮助类接口

create Java class File “ExportUtil”-------这下面的代码复制进去直接就能用的

package com.easy.common.hutool.io.export;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.log.Log;
import com.easy.common.hutool.io.annotation.Excel;
import com.easy.common.utils.ClassUtils;
import com.easy.common.utils.JsonUtils;
import com.easy.common.utils.StringUtils;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.lang.reflect.Field;
import java.util.*;

/**
 * 建议使用ExportBigUtils, 从20条数据到200W条数据比ExportUtils以指数级上涨
 * 1. 建议使用class对象,及classList 导出,  不建议使用 map,  更不建议在同一个导出交替使用!!!
 * 2. 如果有分组需要, 建议使用 .setTotal(list,class,groups).exportFile(response)
 * 不然的话直接使用.exportFile(list,class,null,response)即可
 *
 */
public interface ExportUtil{
    Log log = Log.get();
    /**
     * 创建小对象
     * @param fileName
     * @return
     */
    @Deprecated
    static ExportUtil createFile(String fileName){
        return ExportUtils.create(fileName);
    }

    /**
     * 创建大文件导出
     * @param fileName
     * @return
     */
    static ExportUtil createBigFile(String fileName){
        return ExportBigUtils.create(fileName);
    }

    /**
     * 重置当前页
     * @return
     */
    ExportUtil reset();

    /**
     * 根据 map 编辑表头
     *
     * @param map .kay  字段名
     * @param map .value  表头名
     * @return ExportUtil
     */
    ExportUtil setTitle(LinkedHashMap<String, String> map);

    /**
     * 追加本页数据
     * <p>
     * 写入workbook,不写入文件
     *
     * @param list
     * @return
     */
    ExportUtil setData(List<?> list);

    /**
     * 根据 实体类 编辑表头
     *
     * @param clazz
     * @param groups
     * @return ExportUtil
     */
    ExportUtil setTitle(Class<?> clazz, String... groups);

    /**
     * 追加数据
     *
     * @param list
     * @return
     */
    default ExportUtil addToData(List<?> list) {
        setData(list);
        return this;
    }

    /**
     * 设置表头和表体
     *
     * @param list
     * @param clazz
     * @return
     */
    default ExportUtil setTotal(List<?> list, Class<?> clazz, String... groups) {
        setTitle(clazz, groups);
        setData(list);
        return this;
    }

    /**
     * 设置当前页是否只展示表头数据,如果当前页调用了setData(),则当前页失效
     * @param onlyAlias
     * @return
     */
    ExportUtil setOnlyAlias(boolean onlyAlias);

    /**
     * 下一次 追加数据前是否 有请求头
     * @param isWriteKeyAsHead
     * @return
     */
    ExportUtil setWriteKeyAsHead(boolean isWriteKeyAsHead);

    /**
     * 设置当前标签页的名称
     * @param reSheetName
     * @return ExportUtil
     */
    ExportUtil reSheetName(String reSheetName);

    /**
     * 重命名文件
     * @param fileName
     * @return
     */
    ExportUtil setFileName(String fileName);

    /**
     * 切换到下一页,数据重置
     * @param sheetName
     * @return
     */
    ExportUtil nextSheet(String sheetName);

    /**
     * 导出
     *
     * @param response
     */
    void exportFile(HttpServletResponse response);

    /**
     * 设置表头和表体
     *
     * @param list
     * @param map
     * @return
     */
    default ExportUtil setTotal(List<?> list, LinkedHashMap<String, String> map) {
        setTitle(map);
        setData(list);
        return this;
    }

    /**
     * 导出
     * @param list
     * @param clazz
     * @param sheetName
     * @param response
     */
    default void exportFile(List<?> list, Class<?> clazz, String sheetName, HttpServletResponse response) {
        if (org.springframework.util.StringUtils.hasLength(sheetName)) {
            reSheetName(sheetName);
        }
        setTitle(clazz);
        setData(list);
        exportFile(response);
    }

    /**
     * 导出
     *
     * @param list
     * @param map
     * @param sheetName
     * @param response
     */
    default void exportFile(List<?> list, LinkedHashMap<String, String> map, String sheetName, HttpServletResponse response) {
        if (org.springframework.util.StringUtils.hasLength(sheetName)) {
            reSheetName(sheetName);
        }
        setTitle(map);
        setData(list);
        exportFile(response);
    }

    /**
     * 通过注解反射更新属性的值
     *
     * @param objVal
     */
    static void getValByClass(Map<String, Object> objVal, List<Field> declaredFields, boolean isExport) {
        for (Field declaredField : declaredFields) {
            Excel annotation = declaredField.getAnnotation(Excel.class);
            // 属性名
            String name = declaredField.getName();
            // 获取注解中 要替换的 属性
            String[] split = annotation.split();
            if (split.length != 0) {
                // 设置权限
                declaredField.setAccessible(true);
                // 获取属性值
                Object val = objVal.get(name);
                if (ObjectUtil.isNotNull(val)) {
                    // 将值进行匹配，如果匹配就更新为code，不匹配还更新为原来的值进行校验
                    // 这里可以自定义不匹配的情况下制空或者其他
                    Object obj = reverseByExp(String.valueOf(val), split, isExport);
                    // 如果val不为空，更新属性值
                    objVal.put(name, obj);
                }
            }
        }
    }

    /**
     * 进行数据处理
     *
     * @param list
     * @return
     */
    default List<Map<String, Object>> dataFormat(List<?> list, Class<?> clazz) {
        List<Field> declaredFields = ClassUtils.getAllFieldsByAnnotation(clazz, Excel.class);
        long l = System.currentTimeMillis();
        List<Map<String, Object>> oldData = JsonUtils.objectList2ListMap(list);
        long l1 = System.currentTimeMillis();
        log.info("{}",l1-l);
        l = System.currentTimeMillis();
        oldData.stream().parallel().forEach( obj ->{
            getValByClass(obj, declaredFields, true);
        });
        l1 = System.currentTimeMillis();
        log.info("{}",l1-l);
        return oldData;
    }


    /**
     * 反向解析值 0=男,1=女
     *
     * @param propertyValue 参数值
     * @param convertSource 翻译注解
     * @return 解析后值，如果存在，则返回对应的值，不存在就返回原始值
     */
    static Object reverseByExp(String propertyValue, String[] convertSource, boolean isExport)
    {
        if (!StringUtils.hasLength(propertyValue)) {
            return null;
        }
        Map<String, Object> map = new HashMap<>(16);
        for (String item : convertSource)
        {
            String[] itemArray = item.split("_");
            if(isExport) {
                map.put(itemArray[0], itemArray[1]);
            }else {
                map.put(itemArray[1], itemArray[0]);
            }
        }
        if (!map.isEmpty()) {
            if (map.containsKey(propertyValue)) {
                return map.get(propertyValue);
            }
            return propertyValue;
        }
        return propertyValue;
    }
}

2.3 ExportUtils 导出文件,要求不大的,

废弃``废弃``废弃``废弃``废弃``废弃``废弃``废弃``废弃``废弃``废弃``废弃``废弃``废弃``废弃

create Java class File “ExportUtils”-------这下面的代码复制进去直接就能用的

package com.common.hutool.io.export;

import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.common.hutool.annotation.Excel;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;


/**
 * @author xyy
 * @DateTime 2022/8/19 11:19
 * @ClassName ExportUtils
 */
public class ExportUtils implements ExportUtil {

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

    private ExcelWriter writer;

    private String fileName;

    private Class<?> clazz;

    /**
     * 当前页是否有表头
     * isWriteKeyAsHead == true ? 显示表头  :  不显示表头
     */
    private boolean isWriteKeyAsHead;

    /**
     * 当前页是否只展示设置了表头的数据
     * onlyAlias == true ? 只展示设置了表头的数据  :  不设置表头时默认属性名当表头
     *
     */
    private boolean onlyAlias;

    private Integer pageNum;


    private ExportUtils(String fileName){
        this.pageNum = 1;
        if(writer == null){
            reset();
            this.fileName = StringUtils.hasLength(fileName) ? encodeUTF8(fileName) : "export-data-file";
            writer = ExcelUtil.getWriter(true);
        }
    }


    /**
     * 创建ExcelWriter对象
     * @return
     */
    public static ExportUtil create(String fileName){
        log.info("导出小文件===========  start  =============>文件名:[{}]",fileName);
        return new ExportUtils(fileName);
    }

    @Override
    public ExportUtil reset(){
        log.info("重置第{}页数据",pageNum);
        this.onlyAlias = true;
        this.isWriteKeyAsHead = true;
        return this;
    }


    /**
     * 重新定义文件名
     * @param fileName
     */
    @Override
    public ExportUtil setFileName(String fileName) {
        if(!StringUtils.hasLength(fileName)){
            throw new NullPointerException();
        }
        this.fileName = encodeUTF8(fileName);
        return this;
    }


    @Override
    public ExportUtil setTitle(Class<?> clazz, String... groups) {
        this.clazz = clazz;
        Field[] superClassDeclaredFields = clazz.getSuperclass().getDeclaredFields();
        Field[] thisClassDeclaredFields = clazz.getDeclaredFields();
        Field[] allFields = ArrayUtils.addAll(superClassDeclaredFields, thisClassDeclaredFields);
        LinkedHashMap<String, String> headMap = new LinkedHashMap<>(16);
        Arrays.stream(allFields)
                .filter(field -> field.isAnnotationPresent(Excel.class))
                .sorted((e1, e2) -> {
                    Excel var1 = e1.getAnnotation(Excel.class);
                    Excel var2 = e2.getAnnotation(Excel.class);
                    return Integer.valueOf(var1.index()).compareTo(Integer.valueOf(var2.index()));
                }).collect(Collectors.toList())
                .forEach(field -> {
                    String key = field.getName();
                    String value = field.getAnnotation(Excel.class).name();
                    if(!StringUtils.hasLength(value)){
                        value = key;
                    }
                    headMap.put(key,value);
//                    writer.addHeaderAlias(key,value);
                });
        return setTitle(headMap);
    }

    @Override
    public ExportUtil setTitle(LinkedHashMap<String, String> map) {
        Iterator<Map.Entry<String, String>> iterator = map.entrySet().iterator();
        while (iterator.hasNext()){
            Map.Entry<String, String> next = iterator.next();
            writer.addHeaderAlias(next.getKey(),next.getValue());
        }
//        log.info("编辑表头==========>头数据:[{}]",map);
        //是否保留有别名的字段
        writer.setOnlyAlias(onlyAlias);
        return this;
    }


    /**
     * 写入数据
     * @param list
     * @return
     */
    @Override
    public ExportUtil setData(List<?> list) {
        if(CollectionUtils.isEmpty(list)){
            throw new NullPointerException();
        }
//        log.info("追加数据==========>row:[{}],是否显示头:[{}]",list.size(),this.isWriteKeyAsHead ? "显示" : "不显示");
//        long startTime = System.currentTimeMillis();
        if(this.clazz != null){
            writer.write( this.dataFormat(list, clazz),true);
        }else {
            writer.write(list, true);
        }
//        long endTime = System.currentTimeMillis();
//        log.info("写入sheet中成功,耗时:[{}]",endTime-startTime);
        return this;
    }

    @Override
    public ExportUtil setOnlyAlias(boolean onlyAlias) {
        this.onlyAlias = onlyAlias;
        return this;
    }

    @Override
    public ExportUtil setWriteKeyAsHead(boolean writeKeyAsHead) {
//        log.info("是否显示表头==========>[{}]",pageNum,writeKeyAsHead);
        isWriteKeyAsHead = writeKeyAsHead;
        return this;
    }

    @Override
    public ExportUtil reSheetName(String reSheetName){
//        log.info("重命名==========>重命名为:[{}],原名为:[{}]",pageNum,reSheetName,writer.getSheet().getSheetName());
        this.writer.renameSheet(reSheetName);
        return this;
    }

    @Override
    public ExportUtil nextSheet(String sheetName){
        reset();
        pageNum += 1;
        sheetName = StringUtils.hasLength(sheetName) ? sheetName : "sheet" + pageNum;
        this.writer.setSheet(sheetName);
//        log.info("换页==========>本页是第{}页,名称为:[{}]",pageNum,sheetName);
        return this;
    }

    @Override
    public void exportFile(HttpServletResponse response) {
//        log.info("===================正式导出=================");
        ServletOutputStream outputStream = null;
        try {
            //设置content—type
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset:utf-8");
            //Content-disposition是MIME协议的扩展，MIME协议指示MIME用户代理如何显示附加的文件。
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
            outputStream = response.getOutputStream();
            writer.flush(outputStream,true);
        } catch (IOException e) {
            log.error("文件导出失败======>>>"+e.getMessage());
            e.printStackTrace();
        }finally {
            try {
                if(outputStream != null) {
                    outputStream.close();
                }
                writer.close();
            } catch (IOException e) {
                log.error("文件导出失败======>>>"+e.getMessage());
                e.printStackTrace();
            }
        }
        log.info("导出文件================>文件名:[{}] 成功",fileName);
    }

    private String encodeUTF8(String var){
        try {
            return URLEncoder.encode(var,"UTF-8");
        } catch (UnsupportedEncodingException e) {
            log.error(e.getMessage());
            e.printStackTrace();
            return "export-data-file";
        }
    }

}

2.4 ExportBigUtils 导出bigFile, 上百万千万

create Java class File “ExportBigUtils”-------这下面的代码复制进去直接就能用的

import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.easy.common.hutool.io.annotation.Excel;
import com.easy.common.utils.ClassUtils;
import com.easy.common.utils.DateUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;

public class ExportBigUtils implements ExportUtil {

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

    /**
     * excel处理的对象
     */
    private ExcelWriter excelWriter;

    /**
     * 表头
     */
    private LinkedHashMap<String, String> headMap;

    private String fileName;

    /**
     * 写入时,这个不为null才会替换
     */
    private Class<?> clazz;

    /**
     * 当前页是否有表头
     * isWriteKeyAsHead == true ? 显示表头  :  不显示表头
     */
    private boolean isWriteKeyAsHead;

    /**
     * 当前页是否只展示设置了表头的数据
     * onlyAlias == true ? 只展示设置了表头的数据  :  不设置表头时默认属性名当表头
     */
    private boolean onlyAlias;

    /**
     * 页码
     */
    private Integer pageNum;

    /**
     * excel当前行
     */
    private Integer currentRow;

    /**
     * 100 万一页 (xls最大行数为1048576),加上表头(下标0)
     */
    private final static int PAGE_MAX_ROW = 1000000;

    /**
     * 本页还可写入多少数据
     */
    private Integer pageRemainRow;


    public ExportBigUtils(String fileName) {
        this.pageNum = 1;
        reset();
        this.headMap = new LinkedHashMap<>(16);
        fileName = fileName + "-" + DateUtils.getDateString();
        this.fileName = StringUtils.hasLength(fileName) ? encodeUTF8(fileName) : "export-data-file";
        /**
         * rowAccessWindowSize  ==  -1  (默认100，写入磁盘，
         *      但是调用setSheet()之后调用write()写入会报错
         *      throw new IllegalArgumentException(
         *            "Attempting to write a row["+rownum+"] " +
         *             "in the range [0," + _writer.getLastFlushedRow() + "] that is already written to disk.");
         */
        excelWriter = ExcelUtil.getBigWriter(-1);
        this.currentRow = excelWriter.getCurrentRow();
        this.pageRemainRow = PAGE_MAX_ROW - currentRow + 1;
        String sheetName = excelWriter.getSheet().getSheetName();
        log.info("创建shell ==========>本页是第{}页,名称为:[{}]", pageNum, sheetName);
    }

    /**
     * 创建ExcelWriter对象
     * @return
     */
    public static ExportUtil create(String fileName){
        return new ExportBigUtils(fileName);
    }

    @Override
    public ExportUtil reset() {
        this.onlyAlias = true;
        this.isWriteKeyAsHead = true;
        return this;
    }

    @Override
    public ExportUtil setTitle(Class<?> clazz, String... groups) {
        this.clazz = clazz;
        List<Field> allFields = ClassUtils.getAllFieldsByAnnotation(clazz, Excel.class);
        allFields.stream().filter(field -> {
            // 判断是否包含这个注解  取反
            if (field.isAnnotationPresent(Excel.class)) {
                String[] var1 = field.getAnnotation(Excel.class).groups();
                // 是否分组, 没有的话为通用导出
                if (var1.length == 0) {
                    return true;
                }
                // 判断分组入参是否为null, null的话不分组导出
                if (groups.length == 0) {
                    return true;
                }
                // 循环判断注解中的group是否包含入参的group,
                // 是的话直接返回该对象,
                // 不是的话继续循环,直到没有后返回false
                List<String> groupList = Arrays.asList(var1);
                for (String group : groups) {
                    if (groupList.contains(group)) {
                        return true;
                    }
                }
            }
            return false;
        })
                .sorted((e1, e2) -> {
                    Excel var1 = e1.getAnnotation(Excel.class);
                    Excel var2 = e2.getAnnotation(Excel.class);
                    return Integer.compare(var1.index(), var2.index());
                })
                .forEach(field -> {
                    String key = field.getName();
                    String name = field.getAnnotation(Excel.class).name();
                    String value = field.getAnnotation(Excel.class).value();
                    value = StringUtils.hasLength(value) ? value : name;
                    if (!StringUtils.hasLength(value)) {
                        value = key;
                    }
                    this.headMap.put(key, value);
                });
        return setTitle(this.headMap);
    }

    @Override
    public ExportUtil setTitle(LinkedHashMap<String, String> map) {
        for (Map.Entry<String, String> next : map.entrySet()) {
            excelWriter.addHeaderAlias(next.getKey(), next.getValue());
        }
        //是否保留有别名的字段
        excelWriter.setOnlyAlias(this.onlyAlias);
        return this;
    }

    @Override
    public ExportUtil setData(List<?> list) {
        if (CollectionUtils.isEmpty(list)) {
            log.warn("page : [{}], setData size is 0 or null,", pageNum);
            return this;
        }
        //当前行(当前行为数据行，要切换到下一行写数据)
        this.currentRow = excelWriter.getCurrentRow();
        //本页还可写入多少数据
        this.pageRemainRow = PAGE_MAX_ROW - currentRow;
        if (this.currentRow != 0) {
            this.pageRemainRow++;
        }
        if (this.clazz != null) {
            list = this.dataFormat(list, clazz);
        }
        long startTime = System.currentTimeMillis();
        //要写入sheet的数据大小
        int size = list.size();
        // 本次添加条数 - 本页剩余数量
        int var1 = size - pageRemainRow;
        //本地可添加多少页
        int pages = 0;
        //本次添加页数后剩余多少条数据未添加
        int rows = 0;
        if(size > pageRemainRow){
            pages = var1 / PAGE_MAX_ROW;
            rows = var1 % PAGE_MAX_ROW;
        }
        if(pageRemainRow > 0){
            if(size <= pageRemainRow) {
                writes(list, this.isWriteKeyAsHead);
            }else{
                //这里代表了当前页拼接数据,不写头
                writes(list.subList(0 , pageRemainRow), this.isWriteKeyAsHead);
                for (int i = 0; i < pages; i++) {
                    nextSheet(null);
                    setTitle(headMap);
                    int offset = i * PAGE_MAX_ROW  + pageRemainRow;
                    writes(list.subList(offset , offset + (PAGE_MAX_ROW)), this.isWriteKeyAsHead);
                }
                if(rows > 0){
                    nextSheet(null);
                    setTitle(headMap);
                    List<?> objects = list.subList(size - rows, size);
                    writes(objects, true);
                }
            }
        }else{
            for (int i = 0; i < pages; i++) {
                nextSheet(null);
                setTitle(headMap);
                int offset = i * PAGE_MAX_ROW;
                writes(list.subList(offset, offset + PAGE_MAX_ROW), this.isWriteKeyAsHead);
            }
            if (rows > 0) {
                nextSheet(null);
                setTitle(headMap);
                writes(list.subList(pages * PAGE_MAX_ROW, size), this.isWriteKeyAsHead);
            }
        }
        long endTime = System.currentTimeMillis();
        log.info("本页剩余条数[{}],本次添加条数:[{}],耗时:[{}]", pageRemainRow, size, endTime - startTime);
        return this;
    }

    private void writes(List<?> list,boolean isWriteKeyAsHead){
        this.excelWriter.write(list, isWriteKeyAsHead);
        this.currentRow = this.excelWriter.getCurrentRow();
        this.pageRemainRow = PAGE_MAX_ROW - currentRow + 1;
    }


    @Override
    public ExportUtil setOnlyAlias(boolean onlyAlias) {
        this.onlyAlias = onlyAlias;
        return this;
    }

    @Override
    public ExportUtil setWriteKeyAsHead(boolean writeKeyAsHead) {
        isWriteKeyAsHead = writeKeyAsHead;
        return this;
    }

    @Override
    public ExportUtil reSheetName(String reSheetName){
        log.info("重命名==========>重命名为:[{}],原名为:[{}]", reSheetName, excelWriter.getSheet().getSheetName());
        this.excelWriter.renameSheet(reSheetName);
        return this;
    }

    /**
     * 重新定义文件名
     * @param fileName
     */
    @Override
    public ExportUtil setFileName(String fileName) {
        if(!StringUtils.hasLength(fileName)){
            throw new NullPointerException();
        }
        this.fileName = encodeUTF8(fileName + "-" + DateUtils.getTimeString());
        return this;
    }

    @Override
    public ExportUtil nextSheet(String sheetName){
        reset();
        pageNum += 1;
        sheetName = StringUtils.hasLength(sheetName) ? sheetName : "sheet"+pageNum;
        this.excelWriter.setSheet(sheetName);
        this.currentRow = this.excelWriter.getCurrentRow();
        this.pageRemainRow = PAGE_MAX_ROW - currentRow;
        if(this.currentRow != 0){
            this.pageRemainRow ++;
        }
        log.info("换页==========>本页是第{}页,名称为:[{}]", pageNum, sheetName);
        return this;
    }

    @Override
    public void exportFile(HttpServletResponse response) {
        log.info("===================正式导出=================");
        ServletOutputStream outputStream = null;
        try {
            //设置content—type
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset:utf-8");
            //Content-disposition是MIME协议的扩展，MIME协议指示MIME用户代理如何显示附加的文件。
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
            outputStream = response.getOutputStream();
            excelWriter.flush(outputStream,true);
        } catch (IOException e) {
            log.error("文件导出失败======>>>"+e.getMessage());
            e.printStackTrace();
        }finally {
            try {
                if(outputStream != null) {
                    outputStream.close();
                }
                System.gc();
                excelWriter.close();
            } catch (IOException e) {
                log.error("文件导出失败======>>>"+e.getMessage());
                e.printStackTrace();
            }
        }
        log.info("导出文件================>文件名:[{}] 成功",fileName);
    }

    private String encodeUTF8(String var){
        try {
            return URLEncoder.encode(var,"UTF-8");
        } catch (UnsupportedEncodingException e) {
            log.error(e.getMessage());
            e.printStackTrace();
            return "export-data-file";
        }
    }
}

导出的效果为:

名字	性别
张三	人妖
李四	四爱

---

---

---

3. hutool导入文件

3.1 使用方式

    @PostMapping("/import")
    public void inport(@RequestParam("file")MultipartFile file) throws IOException {
        InputStream inputStream = file.getInputStream();
        List<ScoreEntity> scoreEntities = ImportUtils.importFile(file, UserPo.class);
        //List<ScoreEntity> scoreEntities = ImportUtils.importFile(inputStream, ScoreEntity.class);
		//List<ScoreEntity> scoreEntities = ImportUtils.importFile(new File(), ScoreEntity.class);
        System.out.println("批量插入");
    }

导入的文件:
C的哪里是空的不用管,有没有都一样,测试有空列会不会影响数据(不会),顺序会不会影响(不会),字母大小写会不会影响(不会)

3.2 导入代码

**create Java class File “ImportUtils”-------这下面的代码复制进去直接就能用的

package com.common.hutool.io.improt;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.common.exception.CommonException;
import com.common.hutool.annotation.Excel;
import com.common.hutool.io.export.ExportUtil;
import com.common.utils.ClassUtils;
import com.common.utils.JsonUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;


/**
 * @author xyy
 * @DateTime 2022/9/28 17:36
 * @ClassName ImportUtils
 */
public class ImportUtils {
    private final static Logger log = LoggerFactory.getLogger(ImportUtils.class);

    private final static Integer MAX_COLUMN = 16384;

    private final static Pattern p = Pattern.compile("\\s*|\t|\r|\n");

    public static  <T> List<T> importFile(MultipartFile file, Class<T> clazz) {
        if(file == null){
            throw new NullPointerException("file is null !");
        }
        InputStream is = null;

        try {
            is = file.getInputStream();
            return importFile(is, clazz);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if(null != is){
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        throw new CommonException("unknown error");
    }

    public static <T> List<T> importFile(File file, Class<T> clazz){
        InputStream is = null;
        try {
            is = new FileInputStream(file);
            return importFile(is, clazz);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if(null != is){
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        throw new CommonException("unknown error");
    }

    public static <T> List<T> importFile(InputStream is,Class<T> clazz){
        ExcelReader reader = ExcelUtil.getReader(is);
        //获取本页的第一行表头
        Row row = reader.getSheet().getRow(0);
        //根据 bean 的类型获取列 归于 那个属性
        HashMap<String, String> linkMap = editTitle(clazz);
        //一共有多少有效列
        final int physicalNumberOfCells = row.getPhysicalNumberOfCells();
        //已读取多少有效列
        int num = 0;
        for (int i = 0; i < MAX_COLUMN; i++) {
            Cell cell = row.getCell(i);
            //判断是否已经读取完所有有效列
            if(ObjectUtil.hasNull(cell) && num == physicalNumberOfCells){
                break;
            }
            num++;
            String title = cell.getStringCellValue();
            title = p.matcher(title).replaceAll("").toUpperCase();
            cell.setCellValue(linkMap.get(title));
        }
        List<Map<String, Object>> list = reader.readAll();
        List<Field> declaredFields = ClassUtils.getAllFieldsByAnnotation(clazz, Excel.class);
        list.forEach(e-> ExportUtil.getValByClass(e, declaredFields,false));
        return JsonUtils.beanToArray(list,clazz);
    }

    private static HashMap<String,String> editTitle(Class<?> clazz) {
        Field[] declaredFields = clazz.getDeclaredFields();
        HashMap<String,String> headMap = new HashMap<>(16);
        Arrays.stream(declaredFields)
                .filter(field -> field.isAnnotationPresent(Excel.class))
                .forEach(field -> {
                    //注解的name当key(导出的表头)
                    String key = field.getAnnotation(Excel.class).name().toUpperCase();
                    //注解的属性当value(实际的表头)
                    String value = field.getName();
                    headMap.put(key,value);
                });
        return headMap;
    }


}

导入注意

导入的请求头无视 空格s 回车r 换行n 水平制表t 符号，无视大小写,无视null列,但不无视 "" 列

一些帮助类请看: http://t.csdn.cn/ZS3sS