参考链接

解决问题

-具官方说可以解决大文件内存泄漏问题，效率高

源码
(https://blog.csdn.net/alinyua/article/details/82859577
)
(https://github.com/wangxiaoxiongjuly/easy-excel-utils
)

导出

 <!--excel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.1</version>
        </dependency>

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class XXXModel extends BaseRowModel {

    /**
     * 主键
     */
    @ExcelProperty(index = 0, value = "ID")
    private Long id;

    /**
     * 手机号
     */
    @ExcelProperty(index = 1, value = "手机号")
    private String phone;

    /**
     * 批次号
     */
    @ExcelProperty(index = 2, value = "批次号")
    private String batchNumber;

    /**
     * 触达状态  
     */
    @ExcelProperty(index = 3, value = "触达状态")
    private String deliveryStatusVal;

    /**
     * 点击状态 0 已点击 1 未点击
     */
    @ExcelProperty(index = 4, value = "点击状态")
    private String clickStatusVal;

    /**
     * 长链点击次数 pv
     */
    @ExcelProperty(index = 5, value = "pv")
    private Integer linkClickCount;

    /**
     * 按钮点击次数
     */
    @ExcelProperty(index = 6, value = "按钮点击")
    private Integer buttonClickCount;

    /**
     * 发送时间
     */
    @ExcelProperty(index = 7, value = "发送时间")
    private String smsSendTime;



    public static XXXModel fromSmsEventVo(xxx smsEventVo){

        return XXXModel.builder()
                .id(smsEventVo.getId())
                .phone(smsEventVo.getPhone())
                .batchNumber(smsEventVo.getBatchNumber())
                .deliveryStatusVal(smsEventVo.getDeliveryStatusVal())
                .clickStatusVal(smsEventVo.getClickStatusVal())
                .linkClickCount(smsEventVo.getLinkClickCount())
                .buttonClickCount(smsEventVo.getButtonClickCount())
                .smsSendTime(TimeUtil.DateToString(smsEventVo.getSmsSendTime(),"yyyy-MM-dd HH:mm:ss"))
                .build();

    }
}

    @ApiOperation(value = "xxxexcel导出")
    @RequestMapping(value = "/export", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_VALUE)
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "header",name = "x-auth-token", value = "token授权验证", required = true, dataType = "String")

    })
    public void smsEventListExport(@RequestHeader("x-auth-token") String token,
                                             @RequestBody XXXVo xxxVo,
                                             HttpServletRequest request, HttpServletResponse response) throws IOException {


        log.info("findSmsBatchEventList.requestParam=[{}]", JSONObject.toJSONString(smsEventQueryVo));

        ServletOutputStream out = response.getOutputStream();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setCharacterEncoding("utf-8");
        String fileName = new String(("excel " + new SimpleDateFormat("yyyy-MM-dd").format(new Date()))
                .getBytes(), "UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName + ".xlsx", "utf-8"));

        List<sss> list = smsService.findSmsEventListExport(xxx);
        List<ExcelSmsEventModel> excelSmsEventModels = list.stream().filter(m -> m != null)
                .map(XXXModel::fromSmsEventVo).collect(Collectors.toList());

        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
        Sheet sheet1 = new Sheet(1, 0, XXXModel.class);
        sheet1.setSheetName("sheet1");
        writer.write(excelSmsEventModels, sheet1);
        writer.finish();
        out.flush();

    }

导入

导入问题

• 会出现xlsx/xls 导入失败；inputStream = new BufferedInputStream(excel.getInputStream());
  改成buffer流，否则报错

 /**
     * 手机号导入
     * @param request
     * @return
     */
    @ApiOperation(value = "手机号导入")
    @RequestMapping(value = "/importExcel", method = RequestMethod.POST)
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "header",name = "x-auth-token", value = "token授权验证", required = true, dataType = "String")

    })
    @ResponseBody
    public ResponseResult importExcel(@ApiParam(value = "上传的文件", required = true)
                                  @RequestParam(value = "file")MultipartFile request,
                                      @RequestHeader("x-auth-token") String token){

       return smsBatchService.phoneImport(request);
    }

 @Override
    @Transactional
    public ResponseResult phoneImport(MultipartFile request) {
        String filename = request.getOriginalFilename();
        if (StringUtils.isEmpty(request)) {
            return ResponseResult.build(HttpServletResponse.SC_OK, "文件不存在", null, true);
        }
        // 上传excel 保存服务器路径
        String excelFullPath = "";
        try {
            List<ImportPhoneModel> list = ExcelUtil.readExcel(request,ImportPhoneModel.class);
           
        } catch (ExcelException e) {
            e.printStackTrace();
            log.info("phoneImport.fail");
            return ResponseResult.build(HttpServletResponse.SC_NOT_ACCEPTABLE, e.getMessage(), null, true);
        }

        //excel上传服务器 必须放在后面
        try {
            log.info("phoneImport.transferTo:begin");
            request.transferTo(new File(excelFullPath));
            log.info("phoneImport.transferTo:success");

        } catch (IOException e) {
            e.printStackTrace();
            return ResponseResult.build(HttpServletResponse.SC_NOT_ACCEPTABLE, "上传失败", null, true);

        }
        return ResponseResult.build(HttpServletResponse.SC_OK, "上传成功",  null, true);
    }

public class ExcelException extends Exception {
    /**
     * Constructs a new exception with the specified detail message.  The
     * cause is not initialized, and may subsequently be initialized by
     * a call to {@link #initCause}.
     *
     * @param message the detail message. The detail message is saved for
     *                later retrieval by the {@link #getMessage()} method.
     */
    public ExcelException(String message) {
        super(message);
    }
}


import org.apache.poi.ss.usermodel.IndexedColors;

import java.lang.annotation.*;

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface CellFontFormat {

    String fontName() default "";

    short fontHeightInPoints() default 11;

    IndexedColors fontColor() default IndexedColors.BLACK;

    boolean bold() default false;
}


import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;

import java.lang.annotation.*;

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface CellStyleFormat {

    /**
     * 水平居中方式 默认左居中
     * @see HorizontalAlignment
     */
    HorizontalAlignment horizontalAlignment() default HorizontalAlignment.LEFT;

    /**
     * 字体设置
     * @see org.apache.poi.xssf.usermodel.XSSFFont
     * @see org.apache.poi.hssf.usermodel.HSSFFont
     */
    CellFontFormat cellFont() default @CellFontFormat();


    /**
     * 背景颜色
     * @see IndexedColors
     */
    IndexedColors fillBackgroundColor() default IndexedColors.WHITE;

}

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

public class ExcelListener extends AnalysisEventListener {

    private List<Object> dataList = new ArrayList<>();

    /**
     * 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
     */
    @Override
    public void invoke(Object object, AnalysisContext context) {
        if(!checkObjAllFieldsIsNull(object)) {
            dataList.add(object);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //do something
    }

    private static final String SERIAL_VERSION_UID = "serialVersionUID";

    /**
     * 判断对象中属性值是否全为空
     */
    private static boolean checkObjAllFieldsIsNull(Object object) {
        if (null == object) {
            return true;
        }
        try {
            for (Field f : object.getClass().getDeclaredFields()) {
                f.setAccessible(true);
                //只校验带ExcelProperty注解的属性
                ExcelProperty property = f.getAnnotation(ExcelProperty.class);
                if(property == null || SERIAL_VERSION_UID.equals(f.getName())){
                    continue;
                }
                if (f.get(object) != null && MyStringUtils.isNotBlank(f.get(object).toString())) {
                    return false;
                }
            }
        } catch (Exception e) {
            //do something
        }
        return true;
    }

    public List<Object> getDataList() {
        return dataList;
    }
}


import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.geotmt.etc.controller.excel.utils.exception.ExcelException;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * ExcelUtil
 * 基于easyExcel的开源框架，poi版本3.17
 * BeanCopy ExcelException 属于自定义数据，属于可自定义依赖
 * 工具类尽可能还是需要减少对其他java的包的依赖
 * Created by fdd on 2019/7/26.
 */
public class ExcelUtil {
    /**
     * 私有化构造方法
     */
    private ExcelUtil(){}

    /**
     * 读取 Excel(多个 sheet)
     * 将多sheet合并成一个list数据集，通过自定义ExcelReader继承AnalysisEventListener
     * 重写invoke doAfterAllAnalysed方法
     * getExtendsBeanList 主要是做Bean的属性拷贝 ，可以通过ExcelReader中添加的数据集直接获取
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @return Excel 数据 list
     */
    public static <T extends BaseRowModel> List<T> readExcel(MultipartFile excel,Class<T>  rowModel) throws ExcelException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return new ArrayList<>();
        }
        for (Sheet sheet : reader.getSheets()) {
            sheet.setClazz(rowModel);
            reader.read(sheet);
        }
        return getExtendsBeanList(excelListener.getDataList(),rowModel);
    }

    /**
     * 读取某个 sheet 的 Excel
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @param sheetNo  sheet 的序号 从1开始
     * @return Excel 数据 list
     */
    public static <T extends BaseRowModel> List<T> readExcel(MultipartFile excel, Class<T>  rowModel, int sheetNo)  throws ExcelException{
        return readExcel(excel, rowModel, sheetNo, 1);
    }

    /**
     * 读取某个 sheet 的 Excel
     * @param excel       文件
     * @param rowModel    实体类映射，继承 BaseRowModel 类
     * @param sheetNo     sheet 的序号 从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel 数据 list
     */
    public static <T extends BaseRowModel> List<T> readExcel(MultipartFile excel, Class<T>  rowModel, int sheetNo,
                                                             int headLineNum) throws ExcelException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return new ArrayList<>();
        }
        reader.read(new Sheet(sheetNo, headLineNum, rowModel));
        return getExtendsBeanList(excelListener.getDataList(),rowModel);
    }


    /**
     * 导出 Excel ：多个 sheet，带表头
     * @param response  HttpServletResponse
     * @param list      数据 list，每个元素为一个 BaseRowModel
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param object    映射实体类，Excel 模型
     */
    public static ExcelWriterFactory writeExcelWithSheets(HttpServletResponse response, List<? extends BaseRowModel> list,
                                                          String fileName, String sheetName, BaseRowModel object, ExcelTypeEnum excelTypeEnum) throws ExcelException {
        ExcelWriterFactory writer = new ExcelWriterFactory(getOutputStream(fileName, response,excelTypeEnum), excelTypeEnum);
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        return writer;
    }

    /**
     * 导出文件时为Writer生成OutputStream
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response, ExcelTypeEnum excelTypeEnum) throws ExcelException{
        //创建本地文件
        String filePath = fileName + excelTypeEnum.getValue();
        try {
            fileName = new String(filePath.getBytes(), "ISO-8859-1");
            response.addHeader("Content-Disposition", "filename=" + fileName);
            return response.getOutputStream();
        } catch (IOException e) {
            throw new ExcelException("创建文件失败！");
        }
    }

    /**
     * 返回 ExcelReader
     * @param excel         需要解析的 Excel 文件
     * @param excelListener new ExcelListener()
     */
    private static ExcelReader getReader(MultipartFile excel,
                                         ExcelListener excelListener) throws ExcelException{
        String fileName = excel.getOriginalFilename();
        if (fileName == null ) {
            throw new ExcelException("文件格式错误！");
        }
        if (!fileName.toLowerCase().endsWith(ExcelTypeEnum.XLS.getValue()) && !fileName.toLowerCase().endsWith(ExcelTypeEnum.XLSX.getValue())) {
            throw new ExcelException("文件格式错误！");
        }
        InputStream inputStream;

        try {
            inputStream = new BufferedInputStream(excel.getInputStream());
//            , ExcelTypeEnum.XLSX
            return new ExcelReader(inputStream,null, excelListener, false);
        } catch (IOException e) {
            //do something
        }
        return null;
    }

    /**
     * 利用BeanCopy转换list
     */
    public static <T extends BaseRowModel> List<T> getExtendsBeanList(List<?> list,Class<T> typeClazz){
        return MyBeanCopy.convert(list,typeClazz);
    }
}

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;

import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

public class ExcelWriterFactory extends ExcelWriter {
    private OutputStream outputStream;
    private int sheetNo = 1;

    ExcelWriterFactory(OutputStream outputStream, ExcelTypeEnum typeEnum) {
        super(outputStream, typeEnum);
        this.outputStream = outputStream;
    }

    public ExcelWriterFactory write(List<? extends BaseRowModel> list, String sheetName,
                                    BaseRowModel object) {
        this.sheetNo++;
        try {
            Sheet sheet = new Sheet(sheetNo, 0, object.getClass());
            sheet.setSheetName(sheetName);
            this.write(list, sheet);
        } catch (Exception ex) {
            try {
                outputStream.flush();
            } catch (IOException e) {
                //do something
            }
        }
        return this;
    }

    @Override
    public void finish() {
        super.finish();
        try {
            outputStream.flush();
        } catch (IOException e) {
            //do something
        }
    }

    public void close(){
        try {
            outputStream.close();
        } catch (IOException e) {
            //do something
        }
    }
}

import org.springframework.beans.*;
import org.springframework.beans.propertyeditors.CustomDateEditor;
import org.springframework.core.convert.ConversionService;
import org.springframework.core.convert.converter.Converter;
import org.springframework.core.convert.support.DefaultConversionService;
import org.springframework.core.convert.support.GenericConversionService;
import org.springframework.util.Assert;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;

public class MyBeanCopy {

    private final class DateToStringConverter implements Converter<Date, String> {
        private DateFormat df ;
        private DateToStringConverter(String format) {
            df = new SimpleDateFormat(format);
        }
        @Override
        public String convert(Date source) {

            return df.format(source);
        }
    }

    private static final String DATE_FORMAT = "yyyy-MM-dd";

    private static Object convertForProperty(Wrapper wrapper, Object object, Object value, String propertyName)
            throws TypeMismatchException {
        Object result;
        if (wrapper == null) {
            result = null;
        } else {
            wrapper.setWrappedInstance(object);
            result = wrapper.getBeanWrapper().convertForProperty(value, propertyName);
        }
        return result;
    }

    private static Object copyProperties(Object source, Object target) throws BeansException {
        Wrapper wrapper = new MyBeanCopy().new Wrapper(source);
        copyProperties(wrapper, source, target);
        return target;
    }

    /**
     * Copy the property values of the given source bean into the target bean.
     * <p>
     * Note: The source and target classes do not have to match or even be derived from each other, as long as the properties match. Any bean
     * properties that the source bean exposes but the target bean does not will silently be ignored.
     * <p>
     * This is just a convenience method. For more complex transfer needs, consider using a full BeanWrapper.
     * 
     * @param source
     *            the source bean
     * @param target
     *            the target bean
     * @throws BeansException
     *             if the copying failed
     */
    private static void copyProperties(Wrapper wrapper, Object source, Object target) throws BeansException {
        Assert.notNull(source, "Source must not be null");
        Assert.notNull(target, "Target must not be null");

        Class<?> actualEditable = target.getClass();
        PropertyDescriptor[] targetPds = BeanUtils.getPropertyDescriptors(actualEditable);

        for (PropertyDescriptor targetPd : targetPds) {
            if (targetPd.getWriteMethod() != null) {
                PropertyDescriptor sourcePd = BeanUtils.getPropertyDescriptor(source.getClass(), targetPd.getName());
                if ( sourcePd.getReadMethod() != null) {
                    try {
                        Method readMethod = sourcePd.getReadMethod();
                        if (!Modifier.isPublic(readMethod.getDeclaringClass().getModifiers())) {
                            readMethod.setAccessible(true);
                        }
                        Object value = readMethod.invoke(source);
                        // 判断是否类型不一致
                        if (value != null && !(targetPd.getPropertyType().isInstance(value))) {
                            // 数据转型
                            value = convertForProperty(wrapper, target, value, targetPd.getName());
                        }
                        Method writeMethod = targetPd.getWriteMethod();
                        if (!Modifier.isPublic(writeMethod.getDeclaringClass().getModifiers())) {
                            writeMethod.setAccessible(true);
                        }
                        writeMethod.invoke(target, value);
                    } catch (Exception ex) {
                        throw new FatalBeanException("Could not copy properties from source to target", ex);
                    }
                }
            }
        }

    }

    private final class Wrapper {

        private GenericConversionService conversion;
        private BeanWrapperImpl bean;

        private Wrapper(Object object) {
            conversion = initDefaultConversionService();
            bean = initDefaultBeanWrapper(conversion, object);
        }

        private void setWrappedInstance(Object object) {
            bean.setWrappedInstance(object);
        }

        private GenericConversionService initDefaultConversionService() {
            GenericConversionService conversionService = new DefaultConversionService();
            conversionService.addConverter(new DateToStringConverter(DATE_FORMAT));
            return conversionService;
        }

        private BeanWrapperImpl initDefaultBeanWrapper(@SuppressWarnings("hiding") ConversionService conversion,
                                                       Object object) {
            BeanWrapperImpl beanWrapper = new BeanWrapperImpl(object);
            beanWrapper.setConversionService(conversion);
            SimpleDateFormat dateFormat = new SimpleDateFormat(DATE_FORMAT);
            dateFormat.setLenient(false);
            beanWrapper.registerCustomEditor(Date.class, new CustomDateEditor(dateFormat, true));
            return beanWrapper;
        }

        private BeanWrapperImpl getBeanWrapper() {
            return bean;
        }
    }

    /**
     * 复制源对象到目的对象
     */
    private static void convert(Object source, Object target) {
        copyProperties(source, target);
    }

    public static <T> List<T> convert(List<?> sources, Class<T> targetClass) {
        List<?> sourcesObj = sources;
        if (sourcesObj == null) {
            sourcesObj = Collections.emptyList();
        }
        List<T> targets = new ArrayList<>(sourcesObj.size());
        MyBeanCopy.convert(sourcesObj, targets, targetClass);
        return targets;
    }

    private static <T> void convert(List<?> sources, List<T> targets, Class<T> targetClass) {
        if (targets == null) {
            return;
        }
        targets.clear();
        if (sources == null) {
            return;
        }
        for (Object obj : sources) {
            try {
                T target = targetClass.newInstance();
                targets.add(target);
                convert(obj, target);
            } catch (Exception e) {
                //do something
                return;
            }
        }
    }
}
public class MyStringUtils {

    private MyStringUtils() {}

    private static boolean isBlank(final CharSequence cs) {
        int strLen;
        if (cs == null || (strLen = cs.length()) == 0) {
            return true;
        }
        for (int i = 0; i < strLen; i++) {
            if (!Character.isWhitespace(cs.charAt(i))) {
                return false;
            }
        }
        return true;
    }

    public static boolean isNotBlank(final CharSequence cs) {
        return !isBlank(cs);
    }
}