Easyexcle导入导出

 

 

一、导入

1、依赖

        <!--excel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>

2、excle相关实体类

package com.example.licong.demo.entity.excle;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@HeadRowHeight(value = 40)
public class UserExcel {
    /**
     * 用户名
     */
    @ExcelProperty(value = "用户名", index = 0)
    @ColumnWidth(value = 15)
    private String username;
    /**
     * 显示名称
     */
    @ExcelProperty(value = "昵称", index = 1)
    @ColumnWidth(value = 15)
    private String nickname;
    /**
     * 密码
     */
    @ExcelProperty(value = "密码", index = 2)
    @ColumnWidth(value = 20)
    private String password;
    /**
     * 邮箱
     */
    @ExcelProperty(value = "邮箱", index = 3)
    @ColumnWidth(value = 20)
    private String email;
    /**
     * 头像
     */
    @ExcelProperty(value = "头像", index = 4)
    @ColumnWidth(value = 20)
    private String avatar;
    /**
     * 0 正常
     * 1 禁用
     */
    @ExcelProperty(value = "状态\r0正常,1 禁用", index = 5)
    @ColumnWidth(value = 20)
    private String status;
    /**
     * 注册时间 yyyy-MM-dd HH:mm:ss格式
     */
    @ExcelProperty(value = "注册时间", index = 6)
    @ColumnWidth(value = 20)
    private String createdTime;
}

3、监听器

package com.example.licong.demo.controller.excle;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example.licong.demo.entity.excle.UserExcel;
import org.testng.collections.Lists;

import java.util.List;

public class UserEasyExcelListener extends AnalysisEventListener<UserExcel> {

    List datas = Lists.newArrayList();

    /**
     * 每解析一行，回调该方法
     *
     * @param goodsInfo
     * @param analysisContext
     */
    @Override
    public void invoke(UserExcel goodsInfo, AnalysisContext analysisContext) {

        //这里可以做数据处理

        datas.add(goodsInfo);

    }

    /**
     * 解析完全部回调
     *
     * @param analysisContext
     */

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

    public List getDatas() {
        return datas;
    }

    public void setDatas(List datas) {
        this.datas = datas;

    }

}

4、controller

    @PostMapping("/insert")
    public String insert(@RequestBody MultipartFile file) throws IOException {
        UserEasyExcelListener goodsInfoEasyExcelListener = new UserEasyExcelListener();
        EasyExcel.read(file.getInputStream(), ExhibitsInfoExcel.class, goodsInfoEasyExcelListener).sheet().doRead();
        List <UserExcel> datas = goodsInfoEasyExcelListener.getDatas();
        if (org.apache.commons.collections4.CollectionUtils.isEmpty(datas)){
            return "数据为空";
        }
//        goodsInforService.addGoodsInfoDtos(datas);//这里为数据库新增操作
        return "SUCCESS";
    }

二、导出模板或导出数据

1、依赖

        <!--excel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>

2、excle相关实体类

package com.example.licong.demo.entity.excle;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@HeadRowHeight(value = 40)
public class UserExcel {
    /**
     * 用户名
     */
    @ExcelProperty(value = "用户名", index = 0)
    @ColumnWidth(value = 15)
    private String username;
    /**
     * 显示名称
     */
    @ExcelProperty(value = "昵称", index = 1)
    @ColumnWidth(value = 15)
    private String nickname;
    /**
     * 密码
     */
    @ExcelProperty(value = "密码", index = 2)
    @ColumnWidth(value = 20)
    private String password;
    /**
     * 邮箱
     */
    @ExcelProperty(value = "邮箱", index = 3)
    @ColumnWidth(value = 20)
    private String email;
    /**
     * 头像
     */
    @ExcelProperty(value = "头像", index = 4)
    @ColumnWidth(value = 20)
    private String avatar;
    /**
     * 0 正常
     * 1 禁用
     */
    @ExcelProperty(value = "状态\r0正常,1 禁用", index = 5)
    @ColumnWidth(value = 20)
    private String status;
    /**
     * 注册时间 yyyy-MM-dd HH:mm:ss格式
     */
    @ExcelProperty(value = "注册时间", index = 6)
    @ColumnWidth(value = 20)
    private String createdTime;
}

 

3、导出工具类

package com.example.licong.demo.controller.excle;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

public class ExcelUtil {

    /**
     * 导出
     * @param response
     * @param data
     * @param fileName
     * @param sheetName
     * @param clazz
     * @throws Exception
     */
    public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) throws Exception {
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);
    }
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        return response.getOutputStream();
    }
}

4、controller

    @GetMapping("/excel/template")
    public void downloadTemplate(HttpServletResponse response) {
        String fileName = "导入用户模板";
        String sheetName = "导入用户模板";
        List<UserExcel> userList = new ArrayList<>();
        //这里是随便给的数据，导出时从数据库查询数据就行了
        userList.add(new UserExcel("saysky", "言曌", "123456", "847064370@qq.com", "http://xxx.com/xx.jpg", "0", "2017-12-31 12:13:14"));
        userList.add(new UserExcel("qiqi", "琪琪", "123456", "666666@qq.com", "http://xxx.com/xx.jpg", "0", "2018-5-20 13:14:00"));
        try {
            ExcelUtil.writeExcel(response, userList, fileName, sheetName, UserExcel.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

 完事。

==============================================================分割线====================================================================

导出模板也可以 用这个方式，只是现在遇到一个问题，由于项目中有poi也有easyexcle导致（线上）导出模板报错（本地是可以的），所以暂时用上面那种导出方法，后面改了的话更新文章

---

    @GetMapping("/export")
    public void export(HttpServletResponse response) throws IOException {
        String fileName = "模板名称";
        //通知浏览器以附件的形式下载处理，设置返回头要注意文件名有中文
        response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xlsx");
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        ServletOutputStream out = response.getOutputStream();
        List<UserExcel> areaInfoExcels = new ArrayList <>();
        EasyExcelFactory.write(out,UserExcel.class).sheet("模板名称").doWrite(areaInfoExcels);
        out.flush();
    }