导出导入下载数据接口

置顶小别墅是毛坯

已于 2024-09-05 11:32:54 修改

阅读量280

点赞数 1

分类专栏：导出导入下载数据接口文章标签： java 前端数据库

于 2019-08-15 15:29:44 首次发布

本文链接：https://blog.csdn.net/weixin_42595331/article/details/99638214

版权

导出导入下载数据接口专栏收录该内容

1 篇文章 0 订阅

订阅专栏

pom

/*********************pom***************/
      <dependency>
          <groupId>com.fasterxml.jackson.datatype</groupId>
          <artifactId>jackson-datatype-joda</artifactId>
          <version>2.10.2</version>
      </dependency>

      <dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>easyexcel</artifactId>
          <version>2.1.6</version>
      </dependency>
/**************

实体类RecommendGoodsDto 继承 BaseRowModel

第一种

///实体类属性上加上注解 @ExcelProperty(value = “属性名”)

controller

**********/
/** 导出**/
    @PostMapping(value = "/token")
    @ApiImplicitParams({@ApiImplicitParam(name = "ACCESS_TOKEN", value = "接口调用凭证", defaultValue = "06855244f2f221da4cd0a395c0d3c68c", dataType = "string", required = true, paramType = "query")})
    public void getTOKENRecordExport(HttpServletResponse response, @ApiParam(value = "条件") @RequestBody InquireConditionParam param) {
        List<DataTokenRecordDto> pageInfo = dataTokenRecordMapper.getTokenList(param);
        String fileName = new String(new SimpleDateFormat("yyyy-MM-dd").format(new Date()).getBytes(), StandardCharsets.UTF_8);
        try {
            ExcelWriter excelWriter = ExcelUtils.writeExcel(response.getOutputStream(), ExcelTypeEnum.XLSX, true, pageInfo);
            if (CollectionUtils.isEmpty(pageInfo)) {
                fileName = "无数据";
            }
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=T享TOKEN列表-" + fileName + ".xlsx");
            excelWriter.finish();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

   @ApiOperation(value = "下载Excel模板", notes = "下载Excel模板")
@PostMapping("/download/excel")
public void downloadExcel() {
    try {
        String path = getClass().getResource("/static/excel/").getPath() + "democratic.xlsx";
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=democratic.xlsx");
        ExcelUtils.downloadExcel(path, response.getOutputStream());
    } catch (IOException e) {
        e.printStackTrace();
    }
    
@ApiOperation(value = "导入Excel数据", notes = "导入Excel数据")
@PostMapping("/import/excel/{cid}")
public void importHouseExcel(MultipartFile file, @ApiParam("小区id") @PathVariable("cid") Integer cid) {
    try {
        ExcelListener listener = new ExcelListener();
        ExcelReader excelReader = ExcelUtils.readerExcel(file.getInputStream(), listener, HouseExcelParam.class);
        houseService.importHouseExcel(listener.getData(), cid);
        excelReader.finish();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

service


@Data
@Slf4j
public class ExcelListener<T> extends AnalysisEventListener {

    private List<T> data = Lists.newArrayList();

    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        log.info(o.toString());
        T t = (T) o;
        data.add(t);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}
    /**
     * 导入数据
     *
     * @param excelParamList
     */
    @Transactional(rollbackFor = Exception.class)
    public List<String> importExcel(List<DemocraticAppraisalExcelParam> excelParamList) {
        AssertUtil.notEmpty(excelParamList, 10000, "无效的excel信息");

        excelParamList.forEach(obj -> obj.setOrganizationId(getOrgIdByName(obj.getOrganizationName())));

        List<String> msg = Lists.newArrayList();
        excelParamList.forEach(obj -> msg.add("未找到名为 ：[" + obj.getOrganizationName() + "] 的组织信息"));
        List<DemocraticAppraisalExcelParam> result = excelParamList.stream().filter(obj -> obj.getOrganizationId() != null || StringUtils.isNotBlank(obj.getOrganizationName())).collect(Collectors.toList());

        partyDemocraticAppraisalMapper.importExcel(result);
        return msg;
    }

    private Integer getOrgIdByName(String orgName) {
        if (StringUtils.isBlank(orgName)) {
            return null;
        }

        Example example = new Example(PartyOrgmanageOrg.class);
        example.createCriteria().andEqualTo("name", orgName)
                .andEqualTo("deleteFlag", DeleteFlagEnum.NotDeleted.getCode());

        List<PartyOrgmanageOrg> partyOrgManageOrgList = partyOrgmanageOrgMapper.selectByExample(example);
        return CollectionUtils.isEmpty(partyOrgManageOrgList) ? null : partyOrgManageOrgList.get(0).getId();
    }

/*****************

utls

********************/


  @Slf4j
public final class ExcelUtils {

    /**
     * 导出
     *
     * @param out
     * @param excelType
     * @param needHead
     * @param data
     * @return
     */
    public static ExcelWriter writeExcel(OutputStream out, ExcelTypeEnum excelType, boolean needHead, List<? extends BaseRowModel> data) {
        ExcelWriter excelWriter = EasyExcelFactory.getWriter(out, excelType, needHead);
        Sheet sheet = new Sheet(1, 0, data.get(0).getClass());
        excelWriter.write(data, sheet);
        return excelWriter;

    }

    /**
     * 导入
     *
     * @param in
     * @param listener
     * @param clazz
     * @return
     */
    public static ExcelReader readerExcel(InputStream in, ExcelListener<? extends BaseRowModel> listener, Class<? extends BaseRowModel> clazz) {
        ExcelReader excelReader = EasyExcelFactory.getReader(in, listener);
        Sheet sheet = new Sheet(1, 1, clazz);
        excelReader.read(sheet);
        return excelReader;
    }

    /**
     * 下载Excel
     *
     * @param path
     * @param out
     * @return
     * @throws IOException
     */
    public static void downloadExcel(String path, OutputStream out) throws IOException {
        FileInputStream input = new FileInputStream(path);
        byte[] bytes = new byte[2048];
        int len;
        while ((len = input.read(bytes)) != -1) {
            out.write(bytes, 0, len);
        }

        input.close();
    }

或者简单的方法

第二种


**/
  @ApiOperation(value = "导出推荐商品", produces = "application/octet-stream")
    @GetMapping(value = "/export")
    @ApiImplicitParams({@ApiImplicitParam(name = "ACCESS_TOKEN", value = "接口调用凭证", defaultValue = "d3d9446802a44259755d38e6d163e820", dataType = "string", required = true, paramType = "query")})
    public void exportAddresses(HttpServletResponse response) throws IOException {
        List<RecommendGoodsDto> recommendGoodsDto = goodsMapper.getRecommendGoodsDto(2, null, null);
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        String fileName = URLEncoder.encode("推荐商品", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), RecommendGoodsDto.class)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .sheet("推荐商品").doWrite(recommendGoodsDto);
    }
    /**导入
    @RequestMapping(value = "/import",method = RequestMethod.POST)
    public String importExcel(@RequestParam("file") MultipartFile file) throws IOException {

            InputStream inputStream = file.getInputStream();

            //实例化实现了AnalysisEventListener接口的类
            ExcelListener listener = new ExcelListener();
            //传入参数
            ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLSX, null, listener);

            //读取信息
            excelReader.read(new Sheet(1, 1, RecommendGoodsDto.class));
            //获取数据
            List<Object> list = listener.getDatas();
            AssertUtil.notEmpty(list,1000,"无效的文件");
            RecommendGoodsDto catagory = new RecommendGoodsDto();
            //转换数据类型,并插入到数据库
            for (int i = 0; i < list.size(); i++) {
                AssertUtil.notNull(catagory.getGoodsName(),1000,"上传的文件格式不正确");
                catagory = (RecommendGoodsDto) list.get(i);
                System.out.println(catagory.getGoodsName());
                System.out.println(catagory.getGoodsId());
                //添加到数据库
            }
            return "导入成功";

    }

//创建 ExcelListener 

public class ExcelListener extends AnalysisEventListener{

    //可以通过实例获取该值
    private List<Object> datas = new ArrayList<Object>();
    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        datas.add(o);//数据存储到list，供批量处理，或后续自己业务逻辑处理。
        doSomething(o);//根据自己业务做处理
    }

    private void doSomething(Object object) {
        //1、入库调用接口
    }

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

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

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // datas.clear();//解析结束销毁不用的资源
    }

第三种

@Data
@ApiModel("店铺列表")
public class ShopInfoDto implements Serializable {

    @ApiModelProperty("用户id")
    @ExcelIgnore  //不需要导出
    private Integer userId;
    @ApiModelProperty("店铺id")
    @ExcelIgnore
    private Integer shopId;
    @ExcelProperty(value = "医生姓名")  //需要导出的
    @ApiModelProperty("医生姓名")
    private String name;
    @ExcelProperty(value = "头像")
    @ApiModelProperty("头像")
    private String portrait;
    @ExcelProperty(value = "备注")
    @ApiModelProperty("备注")
    private String remark;
    @ExcelProperty(value = "标签")
    @ApiModelProperty("标签 多个用逗号隔开")
    private String lables;
    @Ex

  @ApiOperation(value = "导出店铺列表")
    @RequestMapping(value = "/excel", method = RequestMethod.POST)
    public void mchExcel( HttpServletResponse response) throws IOException {
        operatorTokenDao.getToken(SessionUtil.getToken(request));
        OperatingAccounts user = SessionUtil.getUser(request, OperatingAccounts.class);
        PageInfo<ShopInfoDto> merchantList = merchantInfoService.getMerchantList(user.getId(), 1, 9999,user );
        List<ShopInfoDto> list = merchantList.getList();
        AssertUtil.notEmpty(list, 100, "店铺数据为空");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        String fileName = URLEncoder.encode("店铺列表", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), ShopInfoDto.class)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .sheet("店铺列表").doWrite(list);
    }