阿里巴巴easyexcel，高性能excel操作工具的使用

首先pom文件中引入相关依赖

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

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

创建excel读取监听类

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.sun.collierycommon.utils.EasyExcelValidateHelper;
import org.apache.commons.lang3.StringUtils;

/**
 * @Description:
 * @Author: Tarzan Liu
 * @Date: 2020/1/2 15:49
 */

public class ExcelListener<T> extends AnalysisEventListener<T> {


        //验证工具
        private EasyExcelValidateHelper excelCheckManager;



        /**
         * 这个每一条数据解析都会来调用
         *
         * @param data
         * @param context
         */
        @Override
        public void invoke(T data, AnalysisContext context) {
                String errMsg;
                try {
                        //根据excel数据实体中的javax.validation + 正则表达式来校验excel数据
                        errMsg = EasyExcelValidateHelper.validateEntity(data);
                } catch (NoSuchFieldException e) {
                        errMsg = "解析数据出错";
                        throw new ExcelAnalysisException("解析excel出错，请传入正确格式的excel");
                }
                if (!StringUtils.isBlank(errMsg)){
                        throw new ExcelAnalysisException(errMsg);
                }
        }

        /**
         * 所有数据解析完成了 都会来调用
         *
         * @param context
         */
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
               // System.out.println(JSON.toJSONString(list));
        }


}

excel的读取方法

	public ResponseDTO<String> importExcel(MultipartFile file) throws IOException {
		List<ProductionStopingProgressDetailEXCELDTO> rows= EasyExcel.read(file.getInputStream(), ProductionStopingProgressDetailEXCELDTO.class,new ExcelListener()).sheet().doReadSync();
		if(CollectionUtils.isNotEmpty(rows)){
			List<ProductionStopingProgressDetailEntity> list=rows.stream().map(item->{
				ProductionStopingProgressDetailEntity entity=SmartBeanUtil.copy(item,ProductionStopingProgressDetailEntity.class);
				return entity;
			}).collect(Collectors.toList());
			if(list.size()>20000){
				return ResponseDTO.wrap(ResponseCodeConst.ERROR,"文件过大，请上传小于20000条的excel");
			}
			//插入数据库
			this.insertBatch(list);
		}
		return ResponseDTO.succ();

	}

excel的导出方法

	public ResponseDTO<String> exportExcel(Long[] ids, HttpServletResponse response) throws IOException {
		List idList = Arrays.asList(ids);
		List<ProductionStopingProgressDetailEXCELDTO> list = productionStopingProgressDetailDao.selectBatchIds(idList);
		String fileName = URLEncoder.encode("导出的文件.xlsx", "UTF-8");
		response.setHeader("Content-disposition", "attachment; filename*=UTF-8''" + fileName);
		EasyExcel.write(response.getOutputStream(), ProductionStopingProgressDetailEXCELDTO.class).sheet("sheet").doWrite(list);
		return ResponseDTO.succ();
	}

excelDTO对象（注解名称和读取的excel列明一致，或者用index下表和excel的列数一致）

@Data
public class ProductionStopingProgressDetailEXCELDTO {

	@ExcelProperty("工作面")
	private String workFace;

	@ExcelProperty("设计可采长度_上巷")
	private BigDecimal designWorkableLengthUp;

	@ExcelProperty("设计可采长度_下巷")
	private BigDecimal designWorkableLengthDown;

	@ExcelProperty("斜长")
	private BigDecimal dipLength;

	@ExcelProperty("累计累计回采进尺_上巷")
	private BigDecimal cumulativeProgressUp;

	@ExcelProperty("累计累计回采进尺_下巷")
	private BigDecimal cumulativeProgressDown;

	@ExcelProperty("日推进度_上巷")
	private BigDecimal dayProgressUp;

	@ExcelProperty("日推进度_下巷")
	private BigDecimal dayProgressDown;

	@ExcelProperty("月推进度_上巷")
	private BigDecimal monthProgressUp;

	@ExcelProperty("月推进度_下巷")
	private BigDecimal monthProgressDown;

	@ExcelProperty("剩余锁标_上巷")
	private BigDecimal residueLockUp;

	@ExcelProperty("剩余锁标_下巷")
	private BigDecimal residueLockDown;

	@ExcelProperty("剩余可采长度_上巷")
	private BigDecimal residueRecoverableLenthUp;

	@ExcelProperty("剩余可采长度_下巷")
	private BigDecimal residueRecoverableLenthDown;

	@ExcelProperty("三班工作量")
	private String threeShiftWorkload;



}