java 导出execl 表格数据

创建实体类，mapper，service，serviceimpl：

package com.ecc.srb.core.pojo.dto;

import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

/**
 * @author sunyc
 * @create 2022-07-07 9:41
 */
@Data
@TableName(value = "sunchao")
public class ExeclSunchao {
    @ExcelProperty("id")
    @TableId(value = "id",type = IdType.AUTO)
    private Long id;
    @ExcelProperty("上级id")
    private Long parentId;
    @ExcelProperty("名称")
    private String name;
    @ExcelProperty("值")
    private Integer value;
    @ExcelProperty("编码")
    private String dictCode;
}

mapper、

package com.ecc.srb.core.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ecc.srb.core.pojo.dto.ExeclSunchao;
import sun.print.BackgroundServiceLookup;

/**
 * @author sunyc
 * @create 2022-07-07 9:42
 */
public interface ExeclSunchaoMapper extends BaseMapper<ExeclSunchao> {
}

service

package com.ecc.srb.core.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.ecc.srb.core.pojo.dto.ExeclSunchao;

import java.io.InputStream;
import java.util.List;

/**
 * @author sunyc
 * @create 2022-07-07 9:43
 */
public interface ExeclSunchaoService extends IService<ExeclSunchao> {
    //文件导入的接口
    void importFile(InputStream inputStream);

    List listDictData();
}

serviceimpl

package com.ecc.srb.core.service.impl;

import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ecc.srb.core.listener.ExecDictDtoListener;
import com.ecc.srb.core.listener.ExeclSunchaoListener;
import com.ecc.srb.core.mapper.ExeclSunchaoMapper;
import com.ecc.srb.core.pojo.dto.ExecDictDto;
import com.ecc.srb.core.pojo.dto.ExeclSunchao;
import com.ecc.srb.core.service.ExeclSunchaoService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.InputStream;
import java.util.List;

/**
 * @author sunyc
 * @create 2022-07-07 9:44
 */
@Service
@Slf4j
public class ExeclSunchaoServiceImpl extends ServiceImpl<ExeclSunchaoMapper, ExeclSunchao> implements ExeclSunchaoService {

    @Transactional(rollbackFor = {Exception.class})
    @Override //文件导入接口的实现类
    public void importFile(InputStream inputStream) {
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        System.out.println("DictServiceImpl.importData,,Execl 导入的第2步--------------开始解析");
        //创建一个新的 new ExeclSunchaoListener(baseMapper)).sheet().doRead()
        EasyExcel.read(inputStream, ExeclSunchao.class,new ExeclSunchaoListener(baseMapper)).sheet().doRead();
        log.info("importData finished");
    }

    @Override
    public List listDictData() {
        return baseMapper.selectList(null);
    }
}

controller层：

package com.ecc.srb.core.controller.admin;

import com.alibaba.excel.EasyExcel;
import com.ecc.common.exception.BusinessException;
import com.ecc.common.result.R;
import com.ecc.common.result.ResponseEnum;
import com.ecc.srb.core.pojo.dto.ExecDictDto;
import com.ecc.srb.core.pojo.dto.ExeclSunchao;
import com.ecc.srb.core.service.ExeclSunchaoService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;

/**
 * @author sunyc
 * @create 2022-07-07 9:50
 */
@Api(tags = "导入execl表的数据")
@RequestMapping("/admin/sunchao")
@Slf4j
@RestController
@CrossOrigin
public class AdminExeclSunchaoController {

    @Autowired
    ExeclSunchaoService execlSunchaoService;

    @PostMapping("/importfile")
    @ApiOperation("Execl 批量导入数据字典")
    public R importFile(@ApiParam(value = "Execl 文件",required = true)
                        @RequestParam("file")MultipartFile file){
        System.out.println("AdminExeclSunchaoController.importFile-----第一步导入文件");
        //获取一个文件输入流
        try {
            InputStream inputStream=file.getInputStream();
            execlSunchaoService.importFile(inputStream);
            return R.ok().message("字典数据导入成功");
        } catch (IOException e) {
            //return e.printStackTrace();
            throw new BusinessException(ResponseEnum.UPLOAD_ERROR,e);
        }
    }
    @ApiOperation("批量导出数据字典Execl ")
    @GetMapping("/export")
    public R export(HttpServletResponse response){

        try {
            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("mydict", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), ExeclSunchao.class).sheet("数据字典").doWrite(execlSunchaoService.listDictData());
            return R.ok();
        } catch (IOException e) {
            //EXPORT_DATA_ERROR(104, "数据导出失败"),
            throw  new BusinessException(ResponseEnum.EXPORT_DATA_ERROR, e);
        }
    }

}