vue+springboot整合easyexcel

前言：

最近Vue+ssm开发项目的excel导入导出下载整合

代码编写

项目依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <!--目前最新的版本-->
    <version>LATEST</version>
</dependency>

Excel录入功能

将excel转换为表数据

后端代码:

mybatis-plus添加查询数据

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.UUID;

import org.springframework.beans.BeanUtils;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.thunisoft.bid.entity.BidInfor;
import com.thunisoft.bid.exception.BusinessException;
import com.thunisoft.bid.service.BidExcelService;
import com.thunisoft.bid.vo.req.ExcelVo;

public class SubjectExcelListener extends AnalysisEventListener<ExcelVo> {

    //因为SubjectExcelListener不能交给spring进行管理，需要自己new，不能注入其他对象
    //不能实现数据库操作
    private BidExcelService bidExcelService;

    public SubjectExcelListener() {
    }

    public SubjectExcelListener(BidExcelService bidExcelService) {
        this.bidExcelService = bidExcelService;
    }

    //读取excel内容，一行一行进行读取，不会读取excel中的表头数据
    @Override
    public void invoke(ExcelVo excelVo, AnalysisContext analysisContext) {
        if(excelVo == null){
            throw new BusinessException(20001,"文件中的数据为空！");
        }
        //一行一行读取，每次读取
        //  判断数据库中是否存在相同项目名称、中标公司、采购单位数据，如果不存在则添加到数据库
        BidInfor bidInfor = existOneSubject(bidExcelService, excelVo.getProjectName(), excelVo.getPurchaseCompany(), excelVo.getBidCompany());
        if( bidInfor == null){
            bidInfor = new BidInfor();
            BeanUtils.copyProperties(excelVo, bidInfor);
            if (bidInfor.getBusinessLabel() == null) {
                bidInfor.setBusinessLabel("未定义");
            }
            try {
                bidInfor.setBidDate(simpleDateFormat.parse(excelVo.getBidDate().replaceAll("/", "-")));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            bidInfor.setId(UUID.randomUUID().toString().replaceAll("-", ""));
            bidInfor.setCreateDate(new Date());
            bidExcelService.save(bidInfor);
        }

    }

    //判断避免重复添加
    public BidInfor existOneSubject(BidExcelService bidExcelService, String subjectName, String purchaseCompany, String bidCompany){
        QueryWrapper<BidInfor> BidInforQueryWrapper = new QueryWrapper<>();
        BidInforQueryWrapper.eq("project_name", subjectName);
        BidInforQueryWrapper.eq("purchase_company", purchaseCompany);
        BidInforQueryWrapper.eq("bid_company", bidCompany);
        BidInfor bidInfor = bidExcelService.getOne(BidInforQueryWrapper);
        return bidInfor;
    }


    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

@Service
public class BidExcelServiceImpl extends ServiceImpl<BidInforMapper, BidInfor> implements BidExcelService {
    @Override
    public void addSubject(MultipartFile file, BidExcelService bidExcelService) {
        //文件输入流
        InputStream in = null;
        try {
            in = file.getInputStream();
            //调用方法进行读取,通过带参数的构造器将spring容器中的EduSubjectService对象传入到监听器中
            EasyExcel.read(in, ExcelVo.class,new SubjectExcelListener(bidExcelService)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

前端代码

Excel导入功能页面代码:

<template>
  <el-row class="home" :gutter="20" style="margin-top: 10px">
    <el-col :span="24">
      <el-card shadow="hover">
        <el-upload
          class="upload-demo"
          action=""
          :on-preview="handlePreview"
          :on-remove="handleRemove"
          :before-remove="beforeRemove"
          :http-request="uploadExcel"
          multiple
          :limit="1"
          :on-exceed="handleExceed"
          :file-list="fileList"
        >
          <el-button size="small" type="primary">点击上传Excel</el-button>
          <div slot="tip" class="el-upload__tip">只能上传xlsx/xls文件</div>
        </el-upload>
      </el-card>
    </el-col>
  </el-row>
</template>

<script>
export default {
  data() {
    return {
      fileList: []
    }
  },
  methods: {
    handleRemove(file, fileList) {
      console.log(file, fileList)
    },
    handlePreview(file) {
      console.log(file)
    },
    handleExceed(files, fileList) {
      this.$message.warning(`当前限制选择 1 个文件，本次选择了 ${files.length} 个文件，共选择了 ${files.length + fileList.length} 个文件`);
    },
    // eslint-disable-next-line no-unused-vars
    beforeRemove(file, fileList) {
      return this.$confirm(`确定移除 ${file.name}？`)
    },
    uploadExcel(fileObj) {
      let formData = new FormData()
      formData.set('file', fileObj.file)
      this.$axios
        .post('/excelData', formData, {
          headers: {
            'Content-type': 'multipart/form-data'
          }
        })
        .then()
        .catch()
    }
  }
}
</script>

<style lang="scss" scoped></style>

前端样式：

将数据库数据导出为excel并提供用户下载接口

后端代码

接口代码:

//excel表录入添加数据
   @PostMapping("/excelData")
   public DataResult addBidDataFromExcel (MultipartFile file) {
       bidExcelService.addSubject(file, bidExcelService);
       return DataResult.success();
   }

   /**
    * 文件下载（失败了会返回一个有部分数据的Excel）
    * <p>
    * 1. 创建excel对应的实体对象 参照{}
    * <p>
    * 2. 设置返回的 参数
    * <p>
    * 3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
    */
   @GetMapping("/download")
   public void download(HttpServletResponse response) throws IOException {
       // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
       response.setContentType("application/vnd.ms-excel");
       response.setCharacterEncoding("utf-8");
       // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
       String fileName = URLEncoder.encode("中标系统导出数据", "UTF-8").replaceAll("\\+", "%20");
       response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
       List<BidInfor> bidInforList = bidExcelService.list(
           Wrappers.<BidInfor>lambdaQuery().orderByDesc(BidInfor::getBidDate).select(BidInfor::getIndustry, BidInfor::getBidDate, BidInfor::getProvince,
               BidInfor::getProjectName, BidInfor::getPurchaseWay,BidInfor::getPurchaseCompany, BidInfor::getDataSource, BidInfor::getBidCompany, BidInfor::getAmount, BidInfor::getNote,
               BidInfor::getBusinessLabel, BidInfor::getTechnologyLabel, BidInfor::getCustomerLevel));
       List<DownloadDataVo> downloadDataVoList = new LinkedList<>();
       for (BidInfor bidInfor : bidInforList) {
           downloadDataVoList.add(new DownloadDataVo(bidInfor.getIndustry(), bidInfor.getBidDate(), bidInfor.getProvince(), bidInfor.getProjectName(), bidInfor.getPurchaseWay(),
               bidInfor.getPurchaseCompany(), bidInfor.getDataSource(), bidInfor.getBidCompany(), bidInfor.getAmount(), bidInfor.getNote(), bidInfor.getBusinessLabel(),
               bidInfor.getTechnologyLabel(), bidInfor.getCustomerLevel()));
       }
       EasyExcel.write(response.getOutputStream(), DownloadDataVo.class).sheet("中标系统导出数据").doWrite(downloadDataVoList);
   }

前端代码:

1	<el-button type="primary" @click="downloadExcel">导出全部数据</el-button>

 methods: {
downloadExcel() {
      window.open('http://localhost:8080/api/download')
      //location.href = 'http://localhost:8080/api/download'
    }
}

问题记录

注意请勿使用以下代码发生请求下载

this.$axios
     .get('/download', {})
     .then()
     .catch(error => {
        console.log(error)
    })

将导致以下结果:

原因:发送Ajax请求后，后端响应的是二进制数据，jQuery的Ajax只能接收JSON等数据。

解决方案:

1.在新的标签页请求接口，在请求接口上自己拼接参数

1
2
3

function download() {
    window.open("/download");
}

2.创建一个form表单，提交表单的方式下载

function download() {
    var studentName = 1;
    var form = document.createElement('form');
    form.id = 'form';
    form.name = 'form';
    //把这个form放在body里面
    document.body.appendChild(form);
    //给form里面创建一个input框，隐藏掉，来存参数
    var input = document.createElement('input');
    input.type = 'hidden';
    input.name = 'studentName';
    input.value = studentName;
    //把input放在form里面
    form.appendChild(input);
    form.method = "GET" //请求方式
    form.action = '/download';
    form.submit();
    //删掉form
    document.body.removeChild(form)
}

This is copyright.

...

00:00

vue+springboot整合easyexcel

vue+springboot整合easyexcel

前言：

代码编写

Excel录入功能

后端代码:

前端代码

将数据库数据导出为excel并提供用户下载接口

后端代码

前端代码:

问题记录

...

FEATURED TAGS

FRIENDS