EasyExcel读取和写入java model数据-白红宇

EasyExcel读取和写入java model数据

阅读量：4180 次

发布时间：2019-05-26

本文共 10012 字，大约阅读时间需要 33 分钟。

Pom.xml


               
    
     com.alibaba
                
    
     easyexcel
                
    
     2.0.2

一、JAVA Model

package com.xiaobu.entity.vo;import com.alibaba.excel.annotation.ExcelProperty;import com.alibaba.excel.metadata.BaseRowModel;import lombok.Data;import lombok.EqualsAndHashCode;import java.io.Serializable;/** * @author xiaobu * @version JDK1.8.0_171 * @date on  2018/12/13 14:28 * @description V1.0 */@EqualsAndHashCode(callSuper = true)@Datapublic class MaterialVo2 extends BaseRowModel implements Serializable {    private static final long serialVersionUID = -915961683240343754L;    private String id;    @ExcelProperty(value = "物料sn",index =0)    private String materialSn;    @ExcelProperty(value = "物料类型",index =1)    private  String materialType;    @ExcelProperty(value = "采购订单号",index =2)    private String orderNumber;    @ExcelProperty(value = "行项目号",index =3)    private String itemNumber;    @ExcelProperty(value = "供应商",index =4)    private  String supplier;    @ExcelProperty(value = "需求单位",index =5)    private  String applicantId;    @ExcelProperty(value = "计划单价",index =6)    private double planPrice;    @ExcelProperty(value = "采购单价",index =7)    private double purPrice;    @ExcelProperty(value = "物料状态",index =8)    private int materialState;    //质检员    @ExcelProperty(value = "质检员",index =9)    private  String  assessorw;    @ExcelProperty(value = "采购人",index =10)    private String buyer ;    @ExcelProperty(value = "操作人",index =11)    private String userId;    //计量单位    @ExcelProperty(value = "计量单位",index =12)    private  String count;    @ExcelProperty(value = "创建时间",index =13)    private long createTime = System.currentTimeMillis();    @ExcelProperty(value = "物料描述",index =14)    private  String description;    @ExcelProperty(value = "单个SN对应数量",index =15)    private int  amount=1;    @ExcelProperty(value = "计划打包金额",index =16)    private double planTotal;    @ExcelProperty(value = "采购打包金额",index =17)    private double totalPrice;}

二、mapper方法


   
       
            
             
             
             
         
        
        
            
             
             
             
             
             
             
             
             
             
             
             
             
             
             
             
             
             
             
         
        
        
    
             insert into xg_material (materialSn, materialType, orderNumber, itemNumber, supplier, applicantId, planPrice,                                 purPrice, materialState, assessorw, buyer, userId,                                 count, createTime, description, amount, planTotal, totalPrice,id)        values (#{materialSn}, #{materialType}, #{orderNumber}, #{itemNumber}, #{supplier}, #{applicantId},                #{planPrice}, #{purPrice}, #{materialState},                #{assessorw}, #{buyer}, #{userId}, #{count}, #{createTime}, #{description}, #{amount}, #{planTotal},                #{totalPrice},#{id})

三、读取和写入

package com.xiaobu.easyexcel;import com.alibaba.excel.ExcelReader;import com.alibaba.excel.ExcelWriter;import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.event.AnalysisEventListener;import com.alibaba.excel.metadata.Sheet;import com.alibaba.excel.support.ExcelTypeEnum;import com.xiaobu.base.utils.UUIDUtils;import com.xiaobu.entity.Material;import com.xiaobu.entity.vo.MaterialVo2;import com.xiaobu.mapper.MaterialMapper;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import org.springframework.test.context.junit4.SpringRunner;import tk.mybatis.mapper.entity.Example;import java.io.*;import java.util.List;import static org.apache.tomcat.util.file.ConfigFileLoader.getInputStream;//import com.xiaobu.mapper.Demo1Mapper;/** * @author xiaobu * @version JDK1.8.0_171 * @date on  2018/12/12 14:24 * @description V1.0  E:\Practise\SpringBoot */@RunWith(SpringRunner.class)@SpringBootTestpublic class EasyExcelDemo1 {    @Autowired    private MaterialMapper materialMapper;    /**     * 功能描述:导出java model的数据     *     * @return void     * @author xiaobu     * @date 2019/4/8 9:14     * @version 1.0 两种方式获取ExcelWriter 一个是通过     * public ExcelWriter(OutputStream outputStream, ExcelTypeEnum typeEnum) {     * this(outputStream, typeEnum, true);     * }     * public ExcelWriter(OutputStream outputStream, ExcelTypeEnum typeEnum, boolean needHead) {     * this.excelBuilder = new ExcelBuilderImpl((InputStream)null, outputStream, typeEnum, needHead, (WriteHandler)null);     * }     *      * ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX,true);     * 
     * 另外是通过EasyExcelFactory.getWriter 最终还是调用的ExcelWriter带有是否需要表头和excel版本的构造方法     * public static ExcelWriter getWriter(OutputStream outputStream) {     * return new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true);     * }     * 
     * public static ExcelWriter getWriter(OutputStream outputStream, ExcelTypeEnum typeEnum, boolean needHead) {     * return new ExcelWriter(outputStream, typeEnum, needHead);     * }     * 
     * new ExcelWriter(outputStream, typeEnum, needHead)     * 
     */    @Test    public void writeExcelByPage() {        try {            OutputStream out = new FileOutputStream("E:\\Practise\\demo1.xlsx");            //最直接的方法            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);            Sheet sheet = new Sheet(1, 0, MaterialVo2.class, "第一个sheet", null);            List
    
      materialVos = materialMapper.findAll2();            System.out.println("materialVos = " + materialVos);            writer.write(materialVos, sheet);            writer.finish();            try {                out.close();                System.out.println(" demo1.xlsx生成成功。。。。");            } catch (IOException e) {                e.printStackTrace();            }        } catch (FileNotFoundException e) {            e.printStackTrace();        }    }    /**     * 功能描述:读取java model的excel数据     *     * @return void     * @author xiaobu     * @date 2019/4/8 9:13     * @version 1.0 两种方式，一个是通过ExcelReader的构造方法获取     * 
    
     * public ExcelReader(InputStream in, Object customContent, AnalysisEventListener eventListener) {     * this(in, customContent, eventListener, true);x     * }     * 
     * public ExcelReader(InputStream in, Object customContent, AnalysisEventListener eventListener, boolean trim) {     * ExcelTypeEnum excelTypeEnum = ExcelTypeEnum.valueOf(in);     * this.validateParam(in, eventListener);     * this.analyser = new ExcelAnalyserImpl(in, excelTypeEnum, customContent, eventListener, trim);     * }     * 
     * 第二种是通过EasyExcelFactory.getReader获取     * public static ExcelReader getReader(InputStream in, AnalysisEventListener listener) {     * return new ExcelReader(in, (Object)null, listener);     * }     * 最终还是调用的     * public ExcelReader(InputStream in, Object customContent, AnalysisEventListener eventListener, boolean trim)     */    @Test    public void saxReadSheetsV2007() {        InputStream inputStream = null;        try {            inputStream = getInputStream("E:\\Practise\\demo1.xlsx");            AnalysisEventListener
    
      listener = new AnalysisEventListener
     
      () {                @Override                public void invoke(MaterialVo2 m, AnalysisContext context) {                    System.err.println("Row:" + context.getCurrentRowNum() + " Data:" + m);                    String id = UUIDUtils.getUUID();                    m.setId(id);                    materialMapper.insertMaterialVo2(m);                }                @Override                public void doAfterAllAnalysed(AnalysisContext context) {                    System.err.println("doAfterAllAnalysed...");                }            };            ExcelReader excelReader = new ExcelReader(inputStream, null, listener);            // ExcelReader excelReader = EasyExcelFactory.getReader(inputStream, listener);            // 第二个参数为表头行数，按照实际设置            excelReader.read(new Sheet(1, 1, MaterialVo2.class));            // 解析每行结果在listener中处理        } catch (Exception e) {            e.printStackTrace();        } finally {            try {                assert inputStream != null;                inputStream.close();            } catch (IOException e) {                e.printStackTrace();            }        }    } }

public void export(HttpServletResponse response) {      List
   
     testUsers=  testUserMapper.selectAll(null);        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");        response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系        String fileName = "用户.xlsx";        try {            //fileName = URLEncoder.encode(fileName, "UTF-8");            // 设置文件头：最后一个参数是设置下载文件名            response.setHeader("Content-Disposition", "attachment;fileName=" + new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));            //response.setHeader("Content-Disposition", "attachment;fileName=" + fileName);            // 设置文件ContentType类型，这样设置，会自动判断下载文件类型            EasyExcel.write(response.getOutputStream(), TestUser.class).sheet("模板").doWrite(testUsers);        } catch (IOException e) {            e.printStackTrace();        }    }

转载地址：http://dbgai.baihongyu.com/

你可能感兴趣的文章