说明:这是手写的excel导出 所以没有样式。
首先我们搭建一个springboot项目。
打开地址:
https://start.spring.io/
加入依赖。我贴出我们需要的。
<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的接口。注释掉的代码 可以直接删除掉。我就不删除了
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了!
有什么需要的可以找我。接受批评~~