• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

使用EasyExcel导入导出excel

互联网 diligentman 1周前 (04-08) 5次浏览

我们实现导入/导出excel的第三方常用类库有 Apache poi、Java Excel(JXL)和阿里开源的 Easyexcel 等。我比较倾向使用Easyexcel,原因有两点:
1、性能强。有大量的数据去处理时,poi和jxl内存消耗比较大,可能造成内存溢出。
2、上手简单。poi是比较容易理解的,但是操作起来麻烦,比如我上一篇的“poi导入导出完整实现 包含工具类”,为了简单实现,代码写了好多。而easyexcel可以自己处理数据,样式也比较好调整。

如果你想更详细的学习easyexcel建议看官方文档
官方api.
官方示例.
easyexcel github 地址.

easyexcel导出
1、加入依赖

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.0.4</version>
            <scope>compile</scope>
        </dependency>

2、编写控制层

@GetMapping("/export")
    @ResponseBody
    public boolean export(HttpServletResponse response, HttpServletRequest request) {
        boolean exportResult = testService.findInfo(response, request);
        return exportResult;
    }

3、编写实现层和导出实体类

boolean findInfo(HttpServletResponse response, HttpServletRequest request);


@Override
    public boolean findInfo(HttpServletResponse response, HttpServletRequest request) {
        try {
            List<Map<String, String>> dataList = new ArrayList<>();
            Map<String, String> map = new HashMap<>();
            map.put("id", "1");
            map.put("name", "测试");
            map.put("phone", "测试");
            map.put("address", "测试");
            map.put("enrolDate", "2021-12-11");
            map.put("des", "测试");
            Map<String, String> map1 = new HashMap<>();
            map1.put("id", "2");
            map1.put("name", "测试1");
            map1.put("phone", "测试1");
            map1.put("address", "测试1");
            map1.put("enrolDate", "2021-12-12");
            map1.put("des", "测试1");
            dataList.add(map);
            dataList.add(map1);
            List<TestBo> boList = dataList.stream().filter(Objects::nonNull)
                    .map(s -> TestBo.builder()
                            .id(Integer.valueOf(s.get("id")))
                            .name(s.get("name"))
                            .phone(s.get("phone"))
                            .address(s.get("address"))
                            .enrolDate(s.get("enrolDate"))
                            .des(s.get("des"))
                            .build()).collect(Collectors.toList());

            //导出文件名称
            String fileName = URLEncoder.encode("测试用户导出数据", "utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), TestBo.class).sheet("用户导出数据").doWrite(boList);
            return true;
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
    }


@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
/**设置 row 高度,不包含表头*/
@ContentRowHeight(25)
/**设置 表头 高度(与 @ContentRowHeight 相反)*/
@HeadRowHeight(25)
/**设置列宽*/
@ColumnWidth(25)
@Accessors(chain = true)

public class TestBo {
    /**设置表头信息*/
    @ExcelProperty("id")
    private Integer id;

    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("电话")
    private String phone;

    @ExcelProperty("地址")
    private String address;

    @ExcelProperty("时间")
    private String enrolDate;

    @ExcelProperty("备注")
    private String des;

}

4、完成测试
使用EasyExcel导入导出excel

easyexcel导入
1、编写控制层

@PostMapping(value = "/importExcel")
    @ResponseBody
    public boolean importExcel(@RequestParam("file") MultipartFile file) {
        return testService.importData(file);
    }

2、编写实现层和实体类


boolean importData(MultipartFile file);


@Override
    @Transactional(rollbackFor = Exception.class)
    public boolean importData(MultipartFile file) {
        try {
            List<Object> objectList =ExcelUtil.readMoreThan1000RowBySheetFromInputStream(file.getInputStream(),null,TestPo.class);
            List<TestPo> poList=new ArrayList<>();
            for (Object object:objectList) {
                System.out.println(object);
                TestPo po=new TestPo();
                List<String> srtList= (List<String>) object;
                po.setId(srtList.get(0)!=null?srtList.get(0).toString():"");
                po.setName(srtList.get(1)!=null?srtList.get(1).toString():"");
                po.setPhone(srtList.get(2)!=null?srtList.get(2).toString():"");
                po.setAddress(srtList.get(3)!=null?srtList.get(3).toString():"");
                po.setEnrolDate(srtList.get(4)!=null?srtList.get(4).toString():"");
                po.setDes(srtList.get(5)!=null?srtList.get(5).toString():"");
                poList.add(po);
            }
            return testDao.saveBatch(poList);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

//实体类
@Data
public class TestPo {
    private String id;

    private String name;

    private String phone;

    private String address;

    private String enrolDate;

    private String des;
}

实际开发业务不同入库方法我就不提供了,提供导入工具

package com.example.mydemo1.util;

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

import java.util.ArrayList;
import java.util.List;

/**
 * 解析监听器,
 * 每解析一行会回调invoke()方法。
 * 整个excel解析结束会执行doAfterAllAnalysed()方法
 * @className ExcelListener
 * @description easyexcel
 * @date 2021/3/26 11:14
 */
public class ExcelListener<T> extends AnalysisEventListener<T> {
    private List<T> datas = new ArrayList<T>();

    public List<T> getDatas() {
        return datas;
    }

    public void setDatas(List<T> datas) {
        this.datas = datas;
    }

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


    /**
     * 解析完所有数据后会调用该方法
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //解析结束销毁不用的资源
    }
}


//=============================================


package com.example.mydemo1.util;

import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import lombok.Data;

import java.util.List;

/**
 * @className MultipleSheelPropety
 * @description easyexcel
 * @date 2021/3/26 11:15
 */
@Data
public class MultipleSheelPropety {
    private List<? extends BaseRowModel> data;
    private Sheet sheet;
}


//=============================================


package com.example.mydemo1.util;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * @className ExcelUtil
 * @description easyexcel工具类
 * @date 2021/3/26 11:10
 */
@Slf4j
public class ExcelUtil {
    private static Sheet initSheet;


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

    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/octet-stream;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            // throw new NormalException(e.getMessage());
        }
    }


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




    /**
     * 读小于1000行数据, 带样式
     * filePath 文件绝对路径
     * initSheet :
     * sheetNo: sheet页码,默认为1
     * headLineMun: 从第几行开始读取数据,默认为0, 表示从第一行开始读取
     * clazz: 返回数据List<Object> 中Object的类名
     */
    public static List<Object> readLessThan1000RowBySheet(String filePath, Sheet sheet) {
        if (!StringUtils.hasText(filePath)) {
            return null;
        }
        sheet = sheet != null ? sheet : initSheet;
        InputStream fileStream = null;
        try {
            fileStream = new FileInputStream(filePath);
            return EasyExcelFactory.read(fileStream, sheet);
        } catch (FileNotFoundException e) {
            log.info("找不到文件或文件路径错误, 文件:{}", filePath);
        } finally {
            try {
                if (fileStream != null) {
                    fileStream.close();
                }
            } catch (IOException e) {
                log.info("excel文件读取失败, 失败原因:{}", e);
            }
        }
        return null;
    }


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


    /**
     * 读大于1000行数据, 带样式
     *
     * @param filePath 文件觉得路径
     * @return
     */
    public static List<Object> readMoreThan1000RowBySheet(String filePath, Sheet sheet) {
        if (!StringUtils.hasText(filePath)) {
            return null;
        }
        sheet = sheet != null ? sheet : initSheet;
        InputStream fileStream = null;
        try {
            fileStream = new FileInputStream(filePath);
            ExcelListener excelListener = new ExcelListener();
            EasyExcelFactory.readBySax(fileStream, sheet, excelListener);
            return excelListener.getDatas();
        } catch (FileNotFoundException e) {
            log.error("找不到文件或文件路径错误, 文件:{}", filePath);
        } finally {
            try {
                if (fileStream != null) {
                    fileStream.close();
                }
            } catch (IOException e) {
                log.error("excel文件读取失败, 失败原因:{}", e);
            }
        }
        return null;
    }


    /**
     * 读大于1000行数据, 带样式
     *
     * @return
     */
    public static List readMoreThan1000RowBySheetFromInputStream(InputStream inputStream, Sheet sheet,Class clazz) {
        sheet = sheet != null ? sheet : initSheet;
        InputStream fileStream = null;
        ExcelListener excelListener = new ExcelListener();
        EasyExcelFactory.readBySax(inputStream, sheet, excelListener);
       /* ExcelReaderBuilder excelReaderBuilder=EasyExcelFactory.read(inputStream,clazz,excelListener);*/
        return excelListener.getDatas();
    }


    /**
     * 生成excle
     *
     * @param filePath 绝对路径
     * @param data   数据源
     * @param head   表头
     */
    public static void writeBySimple(String filePath, List<List<Object>> data, List<String> head) {
        writeSimpleBySheet(filePath, data, head, null);
    }


    /**
     * 生成excle
     *
     * @param filePath 路径
     * @param data   数据源
     * @param sheet  excle页面样式
     * @param head   表头
     */
    public static void writeSimpleBySheet(String filePath, List<List<Object>> data, List<String> head, Sheet sheet) {
        sheet = (sheet != null) ? sheet : initSheet;
        if (head != null) {
            List<List<String>> list = new ArrayList<>();
            head.forEach(h -> list.add(Collections.singletonList(h)));
            sheet.setHead(list);
        }
        OutputStream outputStream = null;
        ExcelWriter writer = null;
        try {
            outputStream = new FileOutputStream(filePath);
            writer = EasyExcelFactory.getWriter(outputStream);
            writer.write1(data, sheet);
        } catch (FileNotFoundException e) {
            log.error("找不到文件或文件路径错误, 文件:{}", filePath);
        } finally {
            try {
                if (writer != null) {
                    writer.finish();
                }

                if (outputStream != null) {
                    outputStream.close();
                }

            } catch (IOException e) {
                log.error("excel文件导出失败, 失败原因:{}", e);
            }
        }
    }


    /**
     * 生成excle
     *
     * @param filePath 路径
     * @param data   数据源
     */
    public static void writeWithTemplate(String filePath, List<? extends BaseRowModel> data) {
        writeWithTemplateAndSheet(filePath, data, null);
    }




    /**
     * 生成excle
     *
     * @param filePath 路径
     * @param data   数据源
     * @param sheet  excle页面样式
     */
    public static void writeWithTemplateAndSheet(String filePath, List<? extends BaseRowModel> data, Sheet sheet) {
        if (CollectionUtils.isEmpty(data)) {
            return;
        }
        sheet = (sheet != null) ? sheet : initSheet;
        sheet.setClazz(data.get(0).getClass());
        OutputStream outputStream = null;
        ExcelWriter writer = null;
        try {
            outputStream = new FileOutputStream(filePath);
            writer = EasyExcelFactory.getWriter(outputStream);
            writer.write(data, sheet);
        } catch (FileNotFoundException e) {
            log.error("找不到文件或文件路径错误, 文件:{}", filePath);
        } finally {
            try {
                if (writer != null) {
                    writer.finish();
                }
                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                log.error("excel文件导出失败, 失败原因:{}", e);
            }
        }




    }




    /**
     * 生成多Sheet的excle
     *
     * @param filePath       路径
     * @param multipleSheelPropetys
     */
    public static void writeWithMultipleSheel(String filePath, List<MultipleSheelPropety> multipleSheelPropetys) {
        if (CollectionUtils.isEmpty(multipleSheelPropetys)) {
            return;
        }
        OutputStream outputStream = null;
        ExcelWriter writer = null;
        try {
            outputStream = new FileOutputStream(filePath);
            writer = EasyExcelFactory.getWriter(outputStream);
            for (MultipleSheelPropety multipleSheelPropety : multipleSheelPropetys) {
                Sheet sheet = multipleSheelPropety.getSheet() != null ? multipleSheelPropety.getSheet() : initSheet;
                if (!CollectionUtils.isEmpty(multipleSheelPropety.getData())) {
                    sheet.setClazz(multipleSheelPropety.getData().get(0).getClass());
                }
                writer.write(multipleSheelPropety.getData(), sheet);
            }

        } catch (FileNotFoundException e) {
            log.error("找不到文件或文件路径错误, 文件:{}", filePath);
        } finally {
            try {
                if (writer != null) {
                    writer.finish();
                }

                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                log.error("excel文件导出失败, 失败原因:{}", e);
            }
        }
    }

}

3、准备导入文件
本文中没有对导入文档做错误校验,建议在实际开发中为用户提供导入模板,可以减少错误的出现。
使用EasyExcel导入导出excel

4、使用postman测试
使用EasyExcel导入导出excel


程序员灯塔
转载请注明原文链接:使用EasyExcel导入导出excel
喜欢 (0)