使用EasyExcel上传Excel表格数据

之前使用的导入Excel工具有些烦琐，现在使用阿里的EasyExcel框架，很简单：

目前需求：将Excel表格信息导入后端，得到对应的实体类的list集合

实现步骤：

1.导入依赖：

  <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
   </dependency>

       <!-- 阿里JSON解析器 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.69</version>
        </dependency>

2.实体类：

和excel一一对应，id是excel表格没有的，注意一定要有@Data或编辑get和set方法，index 是对应excel表格的列顺序

要使用驼峰命名，否则数据得不到

@Data
public class UnitExcel {

    /**
     * 主键
     */
    private Integer id;
    /**
     * 单元名
     */
    @ExcelProperty(value = "单元名称",index = 0)
    private String name;
    /**
     * 学期id
     */
    @ExcelProperty(value = "学期",index = 1)
    private Integer termId;
    /**
     * 创建时间
     */
    @ExcelProperty(value = "创建时间",index = 2)
    private Date createTime;

}

Excel表格：

3.controller层：

    @PostMapping("/upload")
    @ResponseBody
    public String upload(MultipartFile file) throws IOException {
        
        List<UnitExcel> list = EasyExcel.read( file.getInputStream()).head(UnitExcel.class).sheet().doReadSync();
        for (UnitExcel data : list) {
            log.info("读取到数据:{}", JSON.toJSONString(data));
        }
        return "ok";
    }

这有个缺点：数据量不能过大，否则会oom

============================================

2.导出：

@GetMapping("/getExcelxxx")
@ResponseBody
public void getExcelxxx(Integer courseId, Integer lessonId, HttpServletResponse response) throws IOException {

    if (courseId == null) {
        throw new RuntimeException("课程id不能为空");
    }
    List<pojo> list = courseLessonService.getExcelxxxss(courseId, lessonId);
    log.info("导出统计课节下的学生上课信息:" + list.toString());

    Map columnWidth = new HashMap();
    columnWidth.put(0, 8000);
    Sheet sheet1 = new Sheet(1, 0, LessonCountVO.class);
    sheet1.setColumnWidthMap(columnWidth);
    String fileName = "导出统计课节下的学生上课信息";
    sheet1.setSheetName(fileName);
    EasyExcelUtils.writeExcelOneSheet(response, list, sheet1, fileName);

}

==============================

package com.ruoyi.common.utils;

/**
 * @创建人： liup
 * @创建时间: 2021/9/28
 * @描述
 */

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.CollectionUtils;
import lombok.Data;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author: lockie
 * @Date: 2019/8/6 10:18
 * @Description:
 */
public class EasyExcelUtils {
    private static final Logger logger = LoggerFactory.getLogger(com.ruoyi.common.utils.EasyExcelUtils.class);

    private static Sheet initSheet;

    static {
        initSheet = new Sheet(1, 0);
        initSheet.setSheetName("sheet");
        // 设置自适应宽度
        initSheet.setAutoWidth(Boolean.TRUE);
    }

    /**
     * 读取少于1000行数据
     *
     * @param filePath 文件绝对路径
     * @return
     */
    public static List<Object> readLessThan1000Row(String filePath) {
        return readLessThan1000RowBySheet(filePath, null);
    }

    /**
     * 读取少于1000行数据，带样式的
     *
     * @param filePath 文件绝对路径
     * @param sheet
     * @return
     */
    public static List<Object> readLessThan1000RowBySheet(String filePath, Sheet sheet) {
//        if (!StringUtils.hasText(filePath)) {
//            return null;
//        }

        sheet = sheet != null ? sheet : initSheet;

        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(filePath);
            return EasyExcelFactory.read(inputStream, sheet);
        } catch (FileNotFoundException e) {
            logger.error("找不到文件或者文件路径错误", e);
        } finally {
            try {
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                logger.error("excel文件读取失败，失败原因：{}", e);
            }
        }
        return null;
    }

    /**
     * 读取大于1000行数据
     * @param filePath
     * @return
     */
    public static List<Object> readMoreThan1000Row(String filePath) {
        return readMoreThan1000RowBySheet(filePath, null);
    }

    /**
     * 读取大于1000行数据
     * @param filePath
     * @param sheet
     * @return
     */
    public static List<Object> readMoreThan1000RowBySheet(String filePath, Sheet sheet) {
//        if (!StringUtils.hasText(filePath)) {
//            return null;
//        }
        sheet = sheet != null ? sheet : initSheet;

        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(filePath);
            ExcelListener excelListener = new ExcelListener();
            EasyExcelFactory.readBySax(inputStream, sheet, excelListener);
            return excelListener.getDatas();
        } catch (FileNotFoundException e) {
            logger.error("找不到文件或者文件路径错误");
        } finally {
            try {
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                logger.error("excel文件读取失败，失败原因：{}", e);
            }
        }
        return null;
    }

    /**
     * 导出单个sheet
     * @param response
     * @param dataList
     * @param sheet
     * @param fileName
     * @throws UnsupportedEncodingException
     */
    public static void writeExcelOneSheet(HttpServletResponse response, List<? extends BaseRowModel> dataList, Sheet sheet, String fileName) throws UnsupportedEncodingException {
        if (CollectionUtils.isEmpty(dataList)) {
            return;
        }
        // 如果sheet为空，则使用默认的
        if (null == sheet) {
            sheet = initSheet;
        }
        try {
            String value = "attachment; filename=" + new String(
                    (fileName + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ExcelTypeEnum.XLSX.getValue()).getBytes("gb2312"), "ISO8859-1");
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-disposition", value);

            ServletOutputStream out = response.getOutputStream();
            ExcelWriter writer = EasyExcelFactory.getWriter(out, ExcelTypeEnum.XLSX, true);
            // 设置属性类
            sheet.setClazz(dataList.get(0).getClass());
            writer.write(dataList, sheet);
            writer.finish();
            out.flush();
        } catch (IOException e) {
            logger.error("导出失败，失败原因：{}", e);
        }
    }

    /**
     * @Author lockie
     * @Description 导出excel 支持一张表导出多个sheet
     * @Param OutputStream 输出流
     * Map<String, List>  sheetName和每个sheet的数据
     * ExcelTypeEnum 要导出的excel的类型 有ExcelTypeEnum.xls 和有ExcelTypeEnum.xlsx
     * @Date 上午12:16 2019/1/31
     */
    public static void writeExcelMutilSheet(HttpServletResponse response, Map<String, List<? extends BaseRowModel>> dataList, String fileName) throws UnsupportedEncodingException {
        if (CollectionUtils.isEmpty(dataList)) {
            return;
        }
        try {
            String value = "attachment; filename=" + new String(
                    (fileName + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ExcelTypeEnum.XLSX.getValue()).getBytes("gb2312"), "ISO8859-1");
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-disposition", value);
            ServletOutputStream out = response.getOutputStream();
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
            // 设置多个sheet
            setMutilSheet(dataList, writer);
            writer.finish();
            out.flush();
        } catch (IOException e) {
            logger.error("导出异常", e);
        }
    }


    /**
     * @Author lockie
     * @Description //setSheet数据
     * @Date 上午12:39 2019/1/31
     */
    private static void setMutilSheet(Map<String, List<? extends BaseRowModel>> dataList, ExcelWriter writer) {
        int sheetNum = 1;
        for (Map.Entry<String, List<? extends BaseRowModel>> stringListEntry : dataList.entrySet()) {
            Sheet sheet = new Sheet(sheetNum, 0, stringListEntry.getValue().get(0).getClass());
            sheet.setSheetName(stringListEntry.getKey());
            writer.write(stringListEntry.getValue(), sheet);
            sheetNum++;
        }
    }



    /**
     * 导出监听
     */
    @Data
    public static class ExcelListener extends AnalysisEventListener {
        private List<Object> datas = new ArrayList<>();

        /**
         * 逐行解析
         * @param object 当前行的数据
         * @param analysisContext
         */
        @Override
        public void invoke(Object object, AnalysisContext analysisContext) {
            // 当前行
//            analysisContext.getCurrentRowNum()
            if (object != null) {
                datas.add(object);
            }
        }


        /**
         * 解析完所有数据后会调用该方法
         * @param analysisContext
         */
        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {

        }
    }


    /**
     * test
     *
     * @param args
     * @param response
     * @throws UnsupportedEncodingException
     */
    public static void main(String[] args, HttpServletResponse response) throws UnsupportedEncodingException {
        // 导出多个sheet
//        List<OrderExportDTO> orderExportDTOList = new ArrayList<>();
//        Map<String, List<? extends BaseRowModel>> map = new HashMap<>();
//        map.put("自营订单", orderExportDTOList);
//        map.put("互联互通", orderExportDTOList);
//        String fileName = new String(("测试导出2019").getBytes(), "UTF-8");
//        writeExcelMutilSheet(response, map, fileName);

        // 导出单个sheet
//        writeExcelOneSheet(response, orderExportDTOList, null, fileName);
    }

}