阿里 EasyExcel 使用及避坑

java1234

共 4639字,需浏览 10分钟

 ·

2020-08-10 21:25

点击上方蓝色字体,选择“标星公众号”

优质文章,第一时间送达

  作者 | 谁将新樽辞旧月,今月曾经照古人

来源 | cnblogs.com/jiangwz/p/10564749.html

精品帖子大汇总

github地址:https://github.com/alibaba/easyexcel

原本在项目中使用EasyPoi读取excel,后来为了统一技术方案,改用阿里的EasyExcel。EasyExcel和EasyPoi有一定的相似之处。

EasyExcel和EasyPoi效率对比:

因为数据量少,从效率上看几乎没有差别,EasyExcel略胜一筹。

 

使用maven的方式引用EasyExcel

https://mvnrepository.com/artifact/com.alibaba/easyexcel



        <dependency>
            <groupId>com.alibabagroupId>

            <artifactId>easyexcelartifactId>
            <version>1.1.2-beat1version>
        dependency>


使用Java模型的方式使用easyexcel

Java模型


@Data
public class TotalAmount extends BaseRowModel implements Serializable {
    
    private Integer id;

    @ExcelProperty(value ="类型",index = 0)
    private String type;//开支类型 信用卡等

    @ExcelProperty(value = "金额",index =1)
    private String sum;

    @ExcelProperty(value = "来源",index =2)
    private String name;//开支来源 如:**银行信用卡

    @ExcelProperty(value = "日期",index =3)
    private String date;

    @ExcelProperty(value = "状态",index =4)
    private Integer status;

    @ExcelProperty(value = "备注",index =5)
    private String descr;


}


使用Java模型的方式需要继承 BaseRowModel ,字段上使用 @ExcelProperty 注解,注解中 value 属性指定字段名,index属性指定字段排序。

注意:这里和EasyExcel不同的是,目前可以使用只指定index和同时指定index和value的方式来匹配excel文件,但是如果只指定value,则无法读取。


@RequestMapping("/importExce")
    @ResponseBody
    public JsonResponse importExcel(@RequestParam("excelFile") MultipartFile excelFile, String type) throws IOException {
        JsonResponse jsonResponse = new JsonResponse();
        String sm="2019-02";
        List<Object> dataList = null;
        dataList = EasyExcelFactory.read(excelFile.getInputStream(), new Sheet(3, 1, TotalAmount.class));
        int scuess = 0;
        int error = 0;
        for (Object o : dataList) {
            if (o instanceof TotalAmount) {
                TotalAmount importEntity = (TotalAmount) o;

                try {

                } catch (Exception e) {
                    error++;
                    e.printStackTrace();
                    continue;
                }
            }
        }
    }

/* @RequestMapping("/importExce")
    @ResponseBody
    public JsonResponse importExce(){
        JsonResponse jsonResponse = new JsonResponse();
        File excelFile = new File
                ("E:\\工作文档\\部门架构201902(bug).xlsx");
        String sm="2019-02";
        InputStream inputStream = new FileInputStream(excelFile);
        List dataList = null;
        dataList = EasyExcelFactory.read(inputStream, new Sheet(3, 1, TotalAmount.class));
        int scuess = 0;
        int error = 0;
        for (Object o : dataList) {
            if (o instanceof TotalAmount) {
                TotalAmount importEntity = (TotalAmount) o;

                try {

                } catch (Exception e) {
                    error++;
                    e.printStackTrace();
                    continue;
                }
            }
        }
    }*/


注意:在使用EasyExcel时容易出的几个错误:

For input "" 类型错误,应该是double等类型的字段有非double类型的数据

java.lang.NumberFormatException: multiple points 多线程使用非线程安全类报错,实际是在日期格式里有并非指定日期格式的数据,比如空格,比如指定 yyyy/mm/dd 但数据是 yyyy-mm-dd

使用easyexcel写出excel:

使用Java模型方式,返回模型列表,带入方法即可


/**
     * 导出Excel
     *
     * @param request
     * @param response
     * @param map
     * @throws IOException
     */

    @RequestMapping("export.do")
    public void export(HttpServletRequest request, String type, HttpServletResponse response,
                       @RequestParam Map<String, Object> map) throws IOException {
        ServletOutputStream out = null;
        try {
            out = response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
        String filename;
        String fileName = null;
        try {
            filename = new Date().toLocaleString();
            fileName = new String((filename).getBytes(), "UTF-8");
            Sheet sheet2 = new Sheet(2, 3, ImportEntityEasyExcel.class, "sheet", null);
            List list = service.getData(map);
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "utf-8"));
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            writer.finish();
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


总结:

easyexcel还有一些并不完善,但是大数据量操作效率高于easypoi



粉丝福利:108本java从入门到大神精选电子书领取

???

?长按上方二维码 2 秒
回复「1234」即可获取资料以及
可以进入java1234官方微信群



感谢点赞支持下哈 

浏览 48
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报