java 使用easyExcel简单用法（附工具类）

目录

 1.导入easyexcel依赖

2.录入easyexcel的工具类

3.控制器代码块（读取excel例子） 

4.读取的excel对应的对象实体 

5.读取excel实现类代码 

6.excel文件模板的例子 

---

 1.导入easyexcel依赖

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

2.录入easyexcel的工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.mdd.common.core.AjaxResult;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 对excel操作的工具类
 * 可以根据自己用的类去生成
 * @Author 阿杰
 * @create 2021-03-31 15:50
 */
public class EasyExcelUtil {

    /**
     * 读取excel
     * @param file 导入的文件流
     * @param model 生成的类
     * @param <T>
     * @return 对象数组
     */
    public static<T> List<T> readExcel(InputStream file, Class<T> model) {
        List<T> list = new ArrayList<>();
        EasyExcel
                //读取的文件
                .read(file)
                //反射获取类型
                .head(model)
                //excel类型 (可以指定 xlx,xlsx,csx)
                //.excelType(ExcelTypeEnum.XLSX)
                //读取的excel左下角的名字
                .sheet(0)
                //注册监听器
                .registerReadListener(new AnalysisEventListener<T>() {
                    @Override
                    public void invoke(T t, AnalysisContext analysisContext) {
                        list.add(t);
                    }

                    @Override
                    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                        System.out.println("读取完毕" + model);
                    }
                }).doRead();
        return list;
    }

    /**
     * 响应给浏览器的excel文件
     * @param response servlet响应对象
     * @param fileName 设置文件明
     * @param list  数据列表
     * @param clazz 响应类
     * @param <T>
     * @throws IOException
     */
    public static<T> void writerExcel(HttpServletResponse response, String fileName, List<T> list, Class<T> clazz) throws IOException {
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("UTF-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyExcel没有关系
            String encode = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + encode + ".xlsx");
            EasyExcel.write(response.getOutputStream(), clazz)
                    //设置不自动关闭流
                    .autoCloseStream(Boolean.FALSE)
                    .sheet(fileName)
                    .doWrite(list);
        } catch (Exception e) {
            //重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(AjaxResult.failed(map));
        }
    }
}

3.控制器代码块（读取excel例子） 

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

/**
 * 回收统计管理
 */
@Api(tags = "回收统计管理")
@RestController
@RequestMapping("api/recoveryEntry")
public class RecoveryEntryController {

    @Resource
    IRecoveryEntryService iRecoveryEntryService;


    @ApiOperation(value = "文件录入",notes = "文件录入")
    @PostMapping(value = "/upload", headers = "content-type=multipart/form-data")
    public AjaxResult upload(@RequestPart("file") MultipartFile file){
        return iRecoveryEntryService.upload(file);
    }


}

4.读取的excel对应的对象实体 

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.io.Serializable;


@Data
@EqualsAndHashCode
@ApiModel(value="RecoveryEntryImportVo对象", description="RecoveryEntryImportVo")
public class RecoveryEntryImportVo implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "序号")
    @ExcelProperty(value="序号", index = 0) //这里位置对应excel模板的顺序位置
    private Long id;      // 主键

    @ApiModelProperty(value = "回收日期")
    @DateTimeFormat("yyyy/MM/dd")
    @ExcelProperty(value="回收日期", index = 1)
    private String recycleTime;    // 回收日期

    @ApiModelProperty(value = "回收地区")
    @ExcelProperty(value="回收地区", index = 2)
    private String recycleArea;      // 回收地区

    @ApiModelProperty(value = "回收详细地址")
    @ExcelProperty(value="回收地区", index = 3)
    private String recycleDetail;    // 回收详细地址

    @ApiModelProperty(value = "回收点名称")
    @ExcelProperty(value="回收地区", index = 4)
    private String recycleName;    // 回收点名称

    @ApiModelProperty(value = "回收重量(kg)")
    @ExcelProperty(value="回收地区", index = 5)
    private Double recycleWeight;    // 回收重量

    @ApiModelProperty(value = "经度")
    @ExcelProperty(value="经度", index = 6)
    private String longitude;    // 经度

    @ApiModelProperty(value = "维度")
    @ExcelProperty(value="经度", index = 7)
    private String latitude;    // 维度


}

5.读取excel实现类代码 

    public AjaxResult upload(MultipartFile file) {
        logger.info("===================== upload  文件上传开始 =====================");
        if (null == file || file.isEmpty()) {
            throw new OperateException(ErrorCode.FILE_IS_NULL);
        }
        // 文件后缀名
        String extName = FilenameUtils.getExtension(file.getOriginalFilename());
        if (StringUtils.isEmpty(extName)) {
            throw new OperateException(ErrorCode.FILE_NOT_SUPPORT);
        }
        List<String> stringList = ArrayUtils.objectToListAsStr(GlobalConfig.uploadExcelExt);
        if(!stringList.contains(extName)){
            throw new OperateException(ErrorCode.FILE_TYPE_NOT_SUPPORT);
        }

        try {
            List<RecoveryEntryImportVo> dataList = EasyExcelUtil.readExcel(file.getInputStream(), RecoveryEntryImportVo.class);
            if (CollUtil.isEmpty(dataList)){
                StringBuilder importTips = getImportTips(0, 0, "0");
                return AjaxResult.success(importTips);
            }
            int total = dataList.size();
            int failCount = 0;
            String failRow = "";
            for (int i = 0; i < dataList.size(); i++) {
                       try {
                           RecoveryEntryImportVo recoveryEntry = dataList.get(i);
                           RecoveryEntry recovery = recoveryEntryMapper.selectById(recoveryEntry.getId());
                           if(ObjectUtil.isNull(recovery)){
                               RecoveryEntry entry = new RecoveryEntry();
                               BeanUtils.copyProperties(recoveryEntry, entry);
                               entry.setRecycleTime(TimeUtils.dateToTimestamp(recoveryEntry.getRecycleTime()));
                               entry.setUpdateTime(TimeUtils.timestamp());
                               entry.setCreateTime(TimeUtils.timestamp());
                               recoveryEntryMapper.insert(entry);
                           }else {
                               BeanUtils.copyProperties(recoveryEntry, recovery);
                               recovery.setRecycleTime(TimeUtils.dateToTimestamp(recoveryEntry.getRecycleTime()));
                               recovery.setUpdateTime(TimeUtils.timestamp());
                               recoveryEntryMapper.updateById(recovery);
                           }
                   } catch (Exception e) {
                       failCount ++ ;
                       logger.error("====================== 第 "  + (i+2) + " 行循环异常");
                       failRow = failRow + "," + (i + 2);
                       logger.error("====================== 回收资料录入异常 {}" + e.getMessage());
                   }
               }
            failRow = StringUtils.isNotEmpty(failRow) ? failRow.substring(1) : "0";
            StringBuilder sb = getImportTips(total, failCount, failRow);
            logger.info("===================== upload  文件上传成功结束 =====================");
            return AjaxResult.success(sb);
        } catch (IOException e) {
            logger.error("====================== 回收资料录入IOException异常 {}" + e.getMessage());
        }
        return AjaxResult.failed(HttpEnum.FAILED.getCode());
    }

6.excel文件模板的例子 

easeExcel真的好用，基本直接复制这个就可以直接使用了，具体更改根据自己业务修改~~~