springBoot+vue(ElementUI组件) poi 简单的导入上传Excel，导出下载excel

说明：这是手写的excel导出所以没有样式。首先我们搭建一个springboot项目。打开地址：https://start.spring.io/加入依赖。我贴出我们需要的。<dependency><groupId>org.springframework.boot</groupId><artifactId>sprin...

优秀的秃顶美男子

4885人浏览 · 2019-11-29 14:06:33

优秀的秃顶美男子 · 2019-11-29 14:06:33 发布

说明：这是手写的excel导出所以没有样式。
首先我们搭建一个springboot项目。
打开地址：

https://start.spring.io/

生成基本的spring boot项目
加入依赖。我贴出我们需要的。

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>RELEASE</version>
		</dependency>

实体类user

package com.qq11852104.poiexcelDemo.model;

import lombok.Data;

@Data
public class User {
    private String name;
    private String sex;
    private int age;
}

通用包

package com.qq11852104.poiexcelDemo.common;

import lombok.Data;
import lombok.NoArgsConstructor;

@Data
    @NoArgsConstructor
    public class BaseResponse<T> {
        //  响应代码
        private String code;
        //  错误信息
        private String errMsg;
        //  响应数据
        private T data;

        public BaseResponse(String code, String errMsg, T data){
            this.code = code;
            this.errMsg = errMsg;
            this.data  = data;
        }

        public static <T> BaseResponse<T> newBaseResponse(T data, String code, String errMsg){
            return new BaseResponse<T>(code, errMsg, data);
        }

        public static <T> BaseResponse<T> newBaseResponse(T data){
            return new BaseResponse<T>(ResponseCode.SUCCESS.getCode(), "", data);
        }

}```

package com.qq11852104.poiexcelDemo.common;

public enum ResponseCode {
	SUCCESS("0000","操作成功"),
	BIND_SUCCESS("SUCCESS","手机号已绑定"),
	UNBIND_SUCCESS("UNBOUND","手机号没有绑定"),
	METHOD_ARGUMENT_NOT_VALID_ERROR("4000","请求数据校验错误"),
	METHOD_ARGUMENT_NOT_PRESENT("4001","参数没有传"),
	BUSINESSEXCEPTION("0008", "业务逻辑错误"),
	EXTERNALEXCEPTION("0009", "外部错误"),
	SYSTEMEXCEPTION("0010","内部异常"),
	DATA_NULL("0020","没有查询到数据"),
	SESSECCCARD("",""),
	HANDLING( "HANDLING", "已申请，请勿重复提交！"),
	CARD_SUCCESS("SUCCESS","充值成功"),
	CARD_ISNOTUSE("SUCCESS", "卡号未绑定，可以使用！"),
	CARD_ISUSE("FAIL", "卡号已被使用"),
	CARD_ISPHONEUSE("FAIL", "卡号已被您绑定"),
	CARD_FAIL("FAIL","操作失败"),
	FAIL("1001","操作失败"),
	PASSING_PARAMETER_ERROR("1002","传参数错误"),
	RESHTER_ERROR("注册失败","已点击五次"),
	GOTO_BAD("1003","申请失败，批次号错误"),
	FILE_EXPORT_REQUESTED("1004","文件导出已申请");

    private String code;
    private String massage; 
    
    private ResponseCode(String code, String massage) {  
        this.code = code;  
        this.massage = massage;  
    }

	public String getCode() {
		return code;
	}

	public void setCode(String code) {
		this.code = code;
	}

	public String getMassage() {
		return massage;
	}

	public void setMassage(String massage) {
		this.massage = massage;
	}
}```

接下来写导出的EXCELController

package com.qq11852104.poiexcelDemo.controller;

import com.qq11852104.poiexcelDemo.model.User;
import com.qq11852104.poiexcelDemo.service.PoiExcelExportService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping("/api")
public class PoiExcelExportController {
    @Autowired
    private PoiExcelExportService poiExcelExportService;
    @GetMapping("/hello")
    public String getHello() {
        return "你好";
    }
    @GetMapping("/excel")
    public void getExcel(HttpServletResponse response) {
        System.out.println("开始导出");
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        HSSFRow nRow = sheet.createRow(0);
        HSSFCell nCell = nRow.createCell(0);
        int rowNo = 0;
        int colNo = 0;
//        设置excel第一行的标题
        String[] title;
        title = new String[]{"姓名", "性别", "年龄"};
        nRow = sheet.createRow(rowNo++);
        for (int i = 0; i < title.length; i++) {
            nCell = nRow.createCell(i);
            nCell.setCellValue(title[i]);
        }
        try {
            /*
             * 调用逻辑层函数查询
             */
            List<User> excelList = poiExcelExportService.getExcelList();
            //遍历并且创建行列
            for (User dto : excelList) {
                //控制列号
                colNo = 0;
                //每遍历一次创建一行
                nRow = sheet.createRow(rowNo++);
                //姓名
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(dto.getName());//姓名
                //性别
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(dto.getSex());
                //年龄
                nCell = nRow.createCell(colNo++);
                nCell.setCellValue(dto.getAge());

            }

//            返回到浏览器
            ServletOutputStream outputStream = response.getOutputStream();
            response.reset();
//            下列输出的xls名字可以自己修改（批量采购表样.xls）这个是例子
            response.setHeader("Content-Disposition","attchement;filename=" + new String("批量采购表样.xls".getBytes("gb2312"), "ISO8859-1"));
            response.setContentType("application/msexcel");
            wb.write(outputStream);
            wb.close();

//            输出到本地
//            FileOutputStream fout = new FileOutputStream("E:/usrer.xls");
//            wb.write(fout);
//            fout.close();
        } catch (Exception e) {
            System.out.println("你异常了~~~快来看看我。。");
        }


    }

}

这里我没有操作数据库。增删改查自己可以写
我这里就返回一个假的数据。

package com.qq11852104.poiexcelDemo.service;

import com.qq11852104.poiexcelDemo.model.User;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
@Service
public class PoiExcelExportService {

    public List<User> getExcelList() {
        List<User> list = new ArrayList<>();
        User user = new User();
        user.setName("刘德华");
        user.setSex("男");
        user.setAge(30);
        User user1 = new User();
        user1.setName("刘亦菲");
        user1.setSex("女");
        user1.setAge(18);
        list.add(user);
        list.add(user1);
        return list;
    }
}

这样导出的就结束啦！
前端浏览器访问后台接口就自动下载了。下面的vue中我也写了按钮导出

这是导出的excel
接下来我们来写一个读取Excel的接口。注释掉的代码可以直接删除掉。我就不删除了

package com.qq11852104.poiexcelDemo.controller;

import com.qq11852104.poiexcelDemo.common.BaseResponse;
import com.qq11852104.poiexcelDemo.model.User;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;

@RestController
@RequestMapping("lyl")
public class PoiExcelReadController {
    @PostMapping("/readExcel")
    public BaseResponse<?> readExcel(HttpServletRequest request,
//                          @RequestParam String data, //可以携带参数
                          @RequestParam(value = "file") MultipartFile excelFile) {

        String fileName = excelFile.getOriginalFilename();
        String bath_id = null;
        try {
//            log.info("上传文件名：{}", fileName);
            int surfixIndex = fileName.lastIndexOf(".");
            if (surfixIndex < 0) {
                return BaseResponse.newBaseResponse("上传文件错误", "0004", "上传文件错误，请重新选择文件");
            }
            String surfix = fileName.substring(surfixIndex).toLowerCase();
            switch (surfix) {
                case ".xls":
                case ".xlsx":
                    break;
                default:
                    return BaseResponse.newBaseResponse("FileError", "0004", "不支持的文件格式，请重新选择文件");
            }
            if (excelFile.isEmpty()) {
                return BaseResponse.newBaseResponse("FileError", "0004", "文件上传失败，请重新选择文件");
            }

            List<User> users = parse(excelFile);

//            for (User orderDetialReq : users) {
//                bath_id = orderDetialReq.getBath_ID();
//                break;
//            }
            if (users.size() <= 1) {
                return BaseResponse.newBaseResponse("FileError", "0003", "文件数据为空，请填入后再上传");
            }


//            points.remove(0);
            if (users.size() > 1000) {
                return BaseResponse.newBaseResponse("FileError", "0003", "您本次导入的数据大于1000条，系统无法处理，请重新选择文件上传");
            }
//            这下面的就是插入数据库里的逻辑了
//            OutOrderDetialRequest outOrderDetialRequest = new OutOrderDetialRequest();
//            outOrderDetialRequest.setPlatform(data);
//            outOrderDetialRequest.setOutOrderDetialReqs(outOrderDetialReq);
//            BaseResponse<Boolean> response =outOrderService.addOutExcel(outOrderDetialRequest);
            return BaseResponse.newBaseResponse(bath_id, "0000", "记录成功！");
        } catch (Exception e) {
//            log.error(e.toString(), e);
            System.out.println("我异常了，好累！");
            return BaseResponse.newBaseResponse(bath_id, "0003", "生成记录失败或者重复提交");
        }

    }
    public List<User> parse(MultipartFile file) {
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);//默认第一个sheet

            if (sheet == null) {
            }

//            Integer totalNum = sheet.getLastRowNum();//总数
            List<User> usrers = new ArrayList<>();
            for(int line = 1; line <= sheet.getLastRowNum();line++){
               User user = new User();
                Row row = sheet.getRow(line);
                if(null == row){
                    continue;
                }
                /**
                 * 获取第一个单元格的内容
                 */
//                String username = row.getCell(0).getStringCellValue();
                user.setName(row.getCell(0).getStringCellValue());//姓名
                user.setSex(row.getCell(1).getStringCellValue());//性别
                System.out.println(row.getCell(2));
                Cell cell = row.getCell(2);
                System.out.println(cell);
                if ("NUMERIC".equals(cell.getCellType().name())) {
                    double numericCellValue = row.getCell(2).getNumericCellValue();
                    int age = (int) numericCellValue;
                    user.setAge(age);//年龄
                } else {
                    user.setAge(0);
                }


//                outOrderDetialReq.setTotalprice(row.getCell(3).getStringCellValue());//商品总价(元)
//                outOrderDetialReq.setStorediscount(row.getCell(4).getStringCellValue()); //店铺优惠折扣(元)
//                outOrderDetialReq.setPlatformdiscount(row.getCell(5).getStringCellValue());//平台优惠折扣(元)
//                outOrderDetialReq.setPostage(row.getCell(6).getStringCellValue());//邮费(元)
//                outOrderDetialReq.setHomeinstallation(row.getCell(7).getStringCellValue());//上门安装费(元)
//                outOrderDetialReq.setDeliveryprice(row.getCell(8).getStringCellValue());//送货入户费(元)
//                outOrderDetialReq.setDeliveryhomeprice(row.getCell(9).getStringCellValue());//送货入户并安装费(元)
//                outOrderDetialReq.setMerchantreceive(row.getCell(10).getStringCellValue());//商家实收金额(元)
//                outOrderDetialReq.setCommodityno(row.getCell(11).getStringCellValue());// 商品数量(件)
//                outOrderDetialReq.setUsername(row.getCell(12).getStringCellValue());//身份证姓名
//                outOrderDetialReq.setIdcardno(row.getCell(13).getStringCellValue());//身份证号码
//                outOrderDetialReq.setConsignee(row.getCell(14).getStringCellValue());//收货人
//                outOrderDetialReq.setMobile(row.getCell(15).getStringCellValue());//手机
//                outOrderDetialReq.setIsexception(row.getCell(16).getStringCellValue());//是否异常
//                outOrderDetialReq.setProvince(row.getCell(17).getStringCellValue());//省
//                outOrderDetialReq.setCity(row.getCell(18).getStringCellValue());//市
//                outOrderDetialReq.setDistrict(row.getCell(19).getStringCellValue());//区
//                outOrderDetialReq.setStreet(row.getCell(20).getStringCellValue());// 街道
//                outOrderDetialReq.setCrowdorderingsuccesstime(doData(String.valueOf(row.getCell(21).getDateCellValue())));//拼单成功时间
//                outOrderDetialReq.setOrderconfirmation(doData(String.valueOf(row.getCell(22).getDateCellValue())));//订单确认时间
//                outOrderDetialReq.setCommitmentdelivery(doData(String.valueOf(row.getCell(23).getDateCellValue())));//承诺发货时间
//                outOrderDetialReq.setShiptime(doData(String.valueOf(row.getCell(24).getDateCellValue())));//发货时间
//                String name = row.getCell(25).getCellType().name();//确认收货时间
//                if (!"NUMBER".equals(name)) {
//                    outOrderDetialReq.setConfirmreceipttime(null);
//                } else {
//                    outOrderDetialReq.setConfirmreceipttime(doData(String.valueOf(row.getCell(25).getDateCellValue())));
//                }
                System.out.println(row.getCell(25)==null?null:row.getCell(25).getDateCellValue());;//确认收货时间
//                outOrderDetialReq.setGoodsid(row.getCell(26).getStringCellValue());//商品ID
//                outOrderDetialReq.setSpc(row.getCell(27).getStringCellValue());//商品规格
//                outOrderDetialReq.setBuymobile(row.getCell(28).getStringCellValue());// 用户购买手机号
//                outOrderDetialReq.setStyleid(row.getCell(29).getStringCellValue());// 样式ID
//                outOrderDetialReq.setSkumerchant(row.getCell(30).getStringCellValue());// 商家编码-SKU维度
//                outOrderDetialReq.setGoodsmerchant(row.getCell(31).getStringCellValue());// 商家编码-商品维度
//                if (row.getCell(32) == null) {
//                    outOrderDetialReq.setExpressnumber(null);
//                } else {
//                    outOrderDetialReq.setExpressnumber(row.getCell(32).getStringCellValue());// 快递单号
//                }
//                if (row.getCell(33) == null) {
//                    outOrderDetialReq.setExpresscompany(null);
//                } else {
//                    outOrderDetialReq.setExpresscompany(row.getCell(33).getStringCellValue());// 快递公司
//                }
//                outOrderDetialReq.setCrossbordershoppingno(row.getCell(34).getStringCellValue());// 海淘清关订单号
//                outOrderDetialReq.setPayid(row.getCell(35).getStringCellValue());// 支付ID
//                outOrderDetialReq.setPaytype(row.getCell(36).getStringCellValue());// 支付方式
//                outOrderDetialReq.setIszore(row.getCell(37).getStringCellValue());// 是否抽奖或0元试用
//                outOrderDetialReq.setRemarkmerchant(row.getCell(38).getStringCellValue());// 商家备注
//                outOrderDetialReq.setAftermarketstatus(row.getCell(39).getStringCellValue());// 售后状态
//                outOrderDetialReq.setBuyermessage(row.getCell(40).getStringCellValue());// 买家留言
//                outOrderDetialReq.setAssociatedcode(row.getCell(41).getStringCellValue());// 关联货品编码
//                outOrderDetialReq.setProductname(row.getCell(42).getStringCellValue());// 货品名称
//                outOrderDetialReq.setProducttype(row.getCell(43).getStringCellValue());// 货品类型
//                outOrderDetialReq.setChildproduct(row.getCell(44).getStringCellValue());// 子货品
//                outOrderDetialReq.setStorehousename(row.getCell(45).getStringCellValue());// 仓库名称
//                outOrderDetialReq.setStorehouseaddress(row.getCell(46).getStringCellValue());// 仓库所在地址
//                outOrderDetialReq.setSelfpickup(row.getCell(47).getStringCellValue());// 是否门店自提
//                outOrderDetialReq.setStorename(row.getCell(48).getStringCellValue());// 门店名称
//                outOrderDetialReq.setStorecode(row.getCell(49).getStringCellValue());// 门店自定义编码
//                Date date = new Date();
//                SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHssmm");
//                String batchId = format.format(date);
//                outOrderDetialReq.setBath_ID(batchId);
                usrers.add(user);

            }
            return usrers;
        } catch (IOException ex) {
//            log.error("解析excel文件出错！");
            System.out.println("哎呀~解析文件出错了！！！！");
            return Collections.emptyList();
        }

    }
}

后端结束。
vue前端如果不知道elementui的使用的我写过一个使用elementui的文章可以去看看。
写前端页面

<template>
  <div>
       <el-form :inline="true" class="demo-form-inline">
      <el-form-item label="选择需要生成的文件:" :rules="[{ required: true}]">
        <el-upload
          ref="upload"
          class="upload-demo"
          action="/lyl/readExcel"
          :on-change="successChange"
          :before-upload="beforeUpload"
          :on-preview="handlePreview"
          :on-remove="handleRemove"
          :before-remove="beforeRemove"
          :on-success="handleSuccess"
          multiple
          :limit="1"
          accept=".xls, .xlsx"
          :on-exceed="handleExceed"
          :file-list="fileList"
          :auto-upload="false"
          :data="getData()"
        >
          <el-button size="small" type="warning">选择文件</el-button>
        </el-upload>
      </el-form-item>
      <el-form-item>
        <el-button size="small" type="warning" @click="submit">生成记录</el-button>
      </el-form-item>
      <el-form-item>
        <!-- <el-button size="small" type="warning" @click="applyDownload">申请导出</el-button> -->
        <el-button size="small" type="warning" @click="applyDownload">申请导出</el-button>
      </el-form-item>
    </el-form>
  </div>
</template>

<script>
export default {
 data() {
    return {
      batchId:"",
      platform: "",
      fileList: [],
      fileName: "",
      applicationNumber: "",
      enterpriseId: null
    };
  },
  methods:{
beforeUpload(file) {},
    handlePreview(file) {
      console.log(file);
    },
    handleRemove(file, fileList) {
      console.log(file, fileList);
    },
    successChange(file, fileList) {
    },
    beforeRemove(file, fileList) {
      return this.$confirm(`确定移除 ${file.name}？`);
    },
    handleSuccess(response, file, fileList) {
      console.log(response.data)
      this.batchId=response.data
      if (response.code == "0000") {
        this.$alert(
          "生成数据已提交成功！",
          "操作提示",
          {
            dangerouslyUseHTMLString: true
          }
        );
        window.location.reload;
      } else {
        this.fileList = [];
        this.$alert(response.errMsg, "操作提示", {
          dangerouslyUseHTMLString: true
        });
      }
    },
    handleExceed(files, fileList) {
      this.$message.warning(
        `当前限制选择 1 个文件，本次选择了 ${
          files.length
        } 个文件，共选择了 ${files.length + fileList.length} 个文件`
      );
    },
    getData() {
    //   console.log(this.platform);
    //   return {
    //     data: JSON.stringify(this.platform)
    //   };
    },
    submit() {


      if (this.$refs.upload._data.uploadFiles.length == 0) {
        alert("请选择需要上传的文件")
        return;
      }

      this.$refs.upload.submit();
    },


    getTableData() {

    },

    applyDownload() {
      // window.location.href = 'http://localhost:6666/api/excel'
      
      // this.batchId = "20191128093017";

    //   this.$ajax
    //     .get("/plat/checkBatchId", {
    //       params: {
    //         batchId: this.batchId
    //       }
    //     })
    //     .then(res => {
    //       console.log(res);
    //       if(res.data.code=="200"){
            // location.href = 'http://192.168.1.60:8081/plat/outExcel'
            var url ="http://"+document.location.host+'/api/excel'
            window.location.href = url
        //   }else{
        //     alert("偶哦~出错了！")
        //   }
        // });

    }
  }
  
}
</script>

<style>

</style>

记得去路由里去配置下

import Vue from 'vue'
import Router from 'vue-router'
import HelloWorld from '@/components/HelloWorld'
import UploadExcel from '@/components/UploadExcel'

Vue.use(Router)

export default new Router({
  routes: [
    {
      path: '/',
      name: 'HelloWorld',
      component: HelloWorld
    },
    {
      path: '/uploadExcel',
      name: 'UploadExcel',
      component: UploadExcel
    }
  ]
})

我的前端配置文件
。只要需改 proxyTable: {}地址为自己的后端接口就行

'use strict'
// Template version: 1.3.1
// see http://vuejs-templates.github.io/webpack for documentation.

const path = require('path')

module.exports = {
  dev: {

    // Paths
    assetsSubDirectory: 'static',
    assetsPublicPath: '/',
    proxyTable: {
      "/api": {
        target: 'http://192.168.1.60:6666/',
        // pathRewrite: {'^/api' : '/'},
        changeOrigin: true
      },
      "/lyl": {
        target: 'http://192.168.1.60:6666/',
        // pathRewrite: {'^/lyl' : '/'},
        changeOrigin: true
      }
    },

    // Various Dev Server settings
    host: 'localhost', // can be overwritten by process.env.HOST
    port: 8080, // can be overwritten by process.env.PORT, if port is in use, a free one will be determined
    autoOpenBrowser: false,
    errorOverlay: true,
    notifyOnErrors: true,
    poll: false, // https://webpack.js.org/configuration/dev-server/#devserver-watchoptions-

    
    /**
     * Source Maps
     */

    // https://webpack.js.org/configuration/devtool/#development
    devtool: 'cheap-module-eval-source-map',

    // If you have problems debugging vue-files in devtools,
    // set this to false - it *may* help
    // https://vue-loader.vuejs.org/en/options.html#cachebusting
    cacheBusting: true,

    cssSourceMap: true
  },

  build: {
    // Template for index.html
    index: path.resolve(__dirname, '../dist/index.html'),

    // Paths
    assetsRoot: path.resolve(__dirname, '../dist'),
    assetsSubDirectory: 'static',
    assetsPublicPath: '/',

    /**
     * Source Maps
     */

    productionSourceMap: true,
    // https://webpack.js.org/configuration/devtool/#production
    devtool: '#source-map',

    // Gzip off by default as many popular static hosts such as
    // Surge or Netlify already gzip all static assets for you.
    // Before setting to `true`, make sure to:
    // npm install --save-dev compression-webpack-plugin
    productionGzip: false,
    productionGzipExtensions: ['js', 'css'],

    // Run the build command with an extra argument to
    // View the bundle analyzer report after build finishes:
    // `npm run build --report`
    // Set to `true` or `false` to always turn it on or off
    bundleAnalyzerReport: process.env.npm_config_report
  }
}

OK了！

有什么需要的可以找我。接受批评~~

Vue

前往低代码交流专区

更多推荐

vue 表单验证

1、6位小写字母和数字必须包含两个字母rules: [{ required: true, message: "XXX不能为空", trigger: "blur" },{ max: 6, message: "最大长度为6位字符", trigger: "blur" },{pattern: /^(?=(?:[^a-z]*[a-z]){2})[a-z0-9]{6,6}$/, //不连续的两位字母// /^