上一篇博客中笔者提到了自己在工作中使用axios的post请求类型调用excel的导出接口时遇到了一个坑，也提到过在下篇文章中再写一篇关于java使用apache poi组件实现带按钮下载的导出excel文件功能的博客。笔者当然要尽量说道做到，因此在这一边博客中本人继续带来干货，不仅带领读者实现带按钮的导出功能，还要在利用vue整合element-ui库在前端实现美观的接口数据的展示。话不多说，下面展示干货！1 vue整合element-ui库实现带表单查询的数据展示和导出excel文件界面
1.1 参考element-ui官网demo实现带有表单查询和数据导出、导入功能的模板组件
About.vue 模板部分代码如下：
<template>
<div class="excel-example">
<el-form name="searchForm" ref="searchForm" :model="form" size="mini" >
<div class="row">
<el-form-item label="用户账号：">
<el-input v-model="form.userAccount"></el-input>
</el-form-item>
<el-form-item label="用户名：">
<el-input v-model="form.nickName"></el-input>
</el-form-item>
</div>
<div class="row">
<el-form-item label="部门：">
<el-select v-model="form.deptNo" clearable placeholder="请选择部门">
<el-option v-for="item in depts" :key="item.value" :label="item.label" :value="item.value">
</el-option>
</el-select>
</el-form-item>
<el-form-item label="创建时间：" class="datetime-range">
<el-date-picker placeholder="起始查询时间" class="startTime"
v-model="form.startDate" @change="startTimeChange"
clearable size="small" editable align="left" type="datetime" value-format="yyyy-MM-dd HH:mm:ss">
</el-date-picker>
<span style="display:block;width:20px;float:left">~</span>
<el-date-picker placeholder="结束查询时间"
v-model="form.endDate" class="endTime" @change="endTimeChange"
clearable size="small" editable align="left" type="datetime" value-format="yyyy-MM-dd HH:mm:ss">
</el-date-picker>
</el-form-item>
</div>
</el-form>
<form id="hiddenForm" hidden="true" action="http://localhost:8081/springboot/export/searchExcel" method="POST">
<input name="userAccount" :value="form.userAccount"/>
<input name="nickName" :value="form.nickName"/>
<input name="deptNo" :value="form.deptNo"/>
<input name="startDate" :value="form.startDate"/>
<input name="endDate" :value="form.endDate"/>
</form>
<el-row class="btn-group">
<el-button type="primary" icon="el-icon-search" @click="searchUsers">查询</el-button>
<el-button>重置</el-button>
<el-button type="primary" icon="el-icon-download" @click="exportExcel">导出</el-button>
<el-upload class="uploadDemo" ref="upload" name="uploadFile"
action="http://localhost:8081/springboot/importExcel"
:before-upload="beforeUpload"
:on-progress="onProgress">
<el-button size="small" type="primary" icon="el-icon-upload" >导入excel文件数据</el-button>
<div slot="tip" class="el-upload__tip">只能上传xls/xlsx文件，且不超过1MB</div>
</el-upload>
</el-row>
<el-table :data="tableData" border style="width: 100%" id="userData">
<el-table-column v-for="item in columns" :prop="item.prop" :label="item.label" :key="item.key">
</el-table-column>
</el-table>
</div>
</template>以上主要用到了element-ui组件中的Form表单、Input输入框、Select选择器和DatePicker日期选择器等组件，在element-ui官网https://element.eleme.cn/#/zh-CN/component/installation
的“组件菜”单都能找到相应vue实现的相应demo，开发人员需要更具自己的需求作适当修改即可。最新的element-ui版本为2.13版本，笔者项目中用的为2.12版本
1.2 数据model与按钮邦定的函数实现
<script>
export default {
name: 'about',
data(){
return {
form:{
userAccount: '',
nickName: '',
deptNo: -1,
startDate: '',
endDate: ''
},
depts:[
{value:1001,label: '生产部'},
{value:1002,label:'测试部'},
{value:1003,label:'销售部'},
{value: 1004,label: '研发部'},
{value: 1005,label: '采购部'},
{value: 1006,label: '运维部'},
{value: 1007,label: '售后支持部'},
{value:-1,label:'所有部门'}
],
tableData: [],
columns: [{prop: 'userAccount',label: '用户账号',width: '120'},
{prop: 'nickName',label: '用户名', width: '120'},
{prop: 'deptNo',label: '部门编号',width: '120'},
{prop: 'deptName',label: '部门名称', width: '120'},
{prop: 'birthDay',label: '出生日期', width: '120'},
{prop: 'updatedTime',label: '更新时间',width: '150'}]
}
},
methods:{
searchUsers(){
let params = this.form;
let searchUrl = '/user/queryUsers?';
for(let key in params){
let val = params[key]?encodeURIComponent(params[key]):'';
searchUrl += encodeURIComponent(key) +'=' + val + '&';
}
this.$getRequest(searchUrl).then(response=>{
this.tableData = response.data.data;
});
},
exportExcel(){
let hiddenForm = document.getElementById("hiddenForm");
hiddenForm.submit();
},
beforeUpload(file){
console.log(file);
const name = file.name+'';
const flag = name.endsWith(".xlsx") || name.endsWith("xls");
if(!flag){
this.$message.error({message:'文件格式不合要求,只能上传xls/xlsx文件!'});
return false;
}
},
onProgress(event,file){
console.log(event);
console.log(file);
},
startTimeChange(val){
console.log('startTime='+val);
console.log(this.form.startDate);
},
endTimeChange(val){
console.log('endTime='+val);
console.log(this.form.endDate);
}
}
}
</script>注意：以上导出form表单查询数据的excel文件的前端按钮实现使用了一个隐藏的form表单，以及javascript中form节点对象自带的submit函数提交请求。而使用axios的Post请求发送导出excel文件的请求反而会因为Content-Type不是application/octet-stream类型而导致接口报错，而一旦把请求头的Content-Type属性值改为application/octet-stream，form表单中的参数又无法传递到服务器，同样会导致接口报错。而直接调用form.submit()方法却能完美解决上述问题！
为了方便组件对不通请求类型接口的调用，本人参考同行的经验对axios的接口进行了封装处理，并将axios调用接口的函数下挂到了vue组件原型方法中，让每一个实例化的vue组件都自带发送get个post http请求的属性方法
1.3 axios调用后台接口函数的封装
在项目的src目录下新建request文件夹，并在该文件夹下新建api.js文件，编辑代码如下：
import axios from 'axios';
axios.defaults.baseURL = 'http://localhost:8081/springboot';
//get请求
export const getRequest = (url)=>{
return axios({
method: 'GET',
url: `${url}`
});
}
//post请求
export const postRequest=(url,params)=>{
return axios({
method: 'POST',
url: `${url}`,
data: params,
transformRequest: [function(data){
let ret = '';
for(let key in data){
ret += encodeURIComponent(key) + '=' + encodeURIComponent(data[key])+'&';
}
return ret;
}],
headers:{
'Content-Type': 'application/x-www-form-urlencoded'
}
})
}
//put请求
export const putRequest = (url, params) => {
return axios({
method: 'PUT',
url: `${url}`,
data: params,
transformRequest: [function (data) {
let ret = ''
for (let it in data) {
ret += encodeURIComponent(it) + '=' + encodeURIComponent(data[it]) + '&'
}
return ret
}],
headers: {
'Content-Type': 'application/x-www-form-urlencoded'
}
});
}
//delete请求
export const deleteRequest = (url) => {
return axios({
method: 'delete',
url: `${url}`
});
}
//上传文件请求
export const uploadFileRequest = (url, params) => {
return axios({
method: 'post',
url: `${url}`,
data: params,
headers: {
'Content-Type': 'multipart/form-data'
}
});
}然后在main.js文件中引入api.js中的请求方法并添加到vue实例的原型方法中去,实现代码如下
import {getRequest,postRequest,putRequest,deleteRequest,uploadFileRequest} from './request/api.js';
Vue.prototype.$getRequest = getRequest;
Vue.prototype.$postRequest = postRequest;
Vue.prototype.$putRequest = putRequest;
Vue.prototype.$deleteRequest = deleteRequest;
Vue.prototype.$uploadFileRequest = uploadFileRequest;
Vue.config.productionTip = false;
// Vue.config.devtools=true;
Vue.prototype.$message = Message;1.4 定义页面样式
组件页面的样式是根据启动项目后利用开发者工具调试出来的，最终的about.vue文件中的样式代码如下：
<style>
div.excel-example{
margin: 20px 20px;
}
form[name='searchForm']{
width: 80%;
margin-left: 100px;
height: 150px;
border: 2px solid #ccc;
border-radius: 5px;
}
div.row{
width: 100%;
height: 40px;
}
.el-row.btn-group{
margin-top: 16px;
flex-flow: row;
display: flex;
width: 80%;
margin-left: 25%;
}
.uploadDemo{
margin-left: 5%;
height: 26px;
}
.el-form-item.el-form-item--mini{
width: 35%;
min-width: 280px;
float: left;
height: 35px;
margin-top: 20px;
margin-left:10px;
}
.el-form-item.el-form-item--mini>.el-form-item__label{
min-width:60px;
float:left;
}
.el-form-item__content>.el-input.el-input--mini{
width: 40%;
float:left;
}
.el-form-item__content>.el-select{
float: left;
margin-left: 6%;
width: 40%;
}
.el-form-item.datetime-range{
width: 60%;
float: left;
}
.el-form-item__content>.el-date-editor.el-date-editor--daterange{
float: left;
margin-left: 0px;
width: 35%;
}
.el-date-editor.el-input{
width: 195px;
float: left;
}
.el-row{
margin-top: 16px;
}
.el-table{
width: 80%;
margin-top: 16px;
margin-left: 100px;
}
.el-table th>.cell{
text-align: center;
}
.el-table th{
background-color: #F1F2F3;
}
.el-table__body-wrapper tbody tr td{
background-color:#fff;
}
.el-table__body-wrapper tbody tr td>.cell{
text-align: center;
}
</style>注意样式代码中的
保存所有修改过的文件后，在项目根目录下使用git bash 打开一个命令控制台，输入 npm run serve 命令回车后启动本地开发环境服务器，开发环境启动成功后可以看到如下信息
App running at:
- Local: http://localhost:3000/
- Network: http://192.168.1.102:3000/在谷歌浏览器中输入 http://localhost:3000 回车进入主页面后点击About菜单后界面效果图如下所示：
2 后端接口实现
2.1 form表单查询接口实现
控制层代码
MybatisController.java
@GetMapping("/queryUsers")
public ServiceResponse<List<UserTO>> searchUsers(@RequestParam("userAccount") String userAccount,
@RequestParam("nickName") String nickName,
@RequestParam("deptNo") Integer deptNo,
@RequestParam("startDate") String startDate,
@RequestParam("endDate") String endDate){
UserForm userForm = new UserForm(userAccount,null,nickName,deptNo,null,startDate,endDate);
return userService.searchUsers(userForm);
}service层代码
IUserService.java
ServiceResponse<List<UserTO>> searchUsers(UserForm userForm);UserService.java
@Override
public ServiceResponse<List<UserTO>> searchUsers(UserForm userForm) {
logger.info("userForm={}", JSONArray.toJSONString(userForm));
ServiceResponse<List<UserTO>> response = new ServiceResponse<>();
try {
List<UserTO> users = userBusiness.searchUsers(userForm);
response.setStatus(200);
response.setMessage("ok");
response.setData(users);
} catch (Exception e) {
logger.error("searchUsers failed",e);
response.setStatus(500);
response.setMessage("inner server error, caused by: "+e.getMessage());
}
return response;
}Business层代码：
IUserBusiness.java
List<UserTO> searchUsers(UserForm userForm) throws Exception;UserBusiness.java
@Override`在这里插入代码片`
public List<UserTO> searchUsers(UserForm userForm) throws Exception {
List<UserTO> users = userDao.searchUsers(userForm);
return users;
}Dao层代码：
IUserDao.java
List<UserTO> searchUsers(UserForm userForm);IUserDao.xml
<select id="searchUsers" parameterType="com.example.mybatis.form.UserForm" resultType="com.example.mybatis.model.UserTO">
select t.id,t.user_account,t.password,t.nick_name,t.dept_no,
d.dept_name,t.phone_num, t.birth_day,t.updated_by, t.updated_time
from userinfo t
inner join dept d on t.dept_no=d.dept_no
<if test="deptNo!=null and deptNo!=-1">
and d.dept_no = #{deptNo,jdbcType=INTEGER}
</if>
<where>
<if test="userAccount!=null and userAccount!='' ">
and t.user_account like #{userAccount,jdbcType=VARCHAR} || '%'
</if>
<if test="nickName!=null and nickName!=''">
and t.nick_name like #{nickName,jdbcType=VARCHAR} || '%'
</if>
<if test="startDate!=null and startDate!=''">
and t.updated_time <![CDATA[>=]]> #{startDate,jdbcType=VARCHAR}
</if>
<if test="endDate!=null and endDate!=''">
and t.updated_time <![CDATA[<=]]> #{endDate,jdbcType=VARCHAR}
</if>
</where>
</select>2.2 带表单参数查询的Excel导出接口实现
上两篇博客中关于导出导入文件的配置信息都是从xml文件中读取的，本次采用在实体类中添加注解实现读取列头和映射的实体类等excel导出接口的元数据信息
（1） 新建两个注解类ExcelSheet和ExcelColumn，它们分别作用在实体类和实体类的属性上，具体代码如下：
ExcelSheet.java
package com.example.mybatis.annotation;
import org.springframework.core.annotation.AliasFor;
import java.lang.annotation.*;
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelSheet {
@AliasFor(attribute = "value")
String name() default "";
@AliasFor(attribute = "name")
String value() default "";
//全类路径名
String voClass() default "";
}ExcelColumn.java
package com.example.mybatis.annotation;
import org.springframework.core.annotation.AliasFor;
import java.lang.annotation.*;
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelColumn {
@AliasFor(attribute = "value")
String name() default "";
@AliasFor(attribute = "name")
String value() default "";
String displayName() default "";
String type() default "";
String format() default "";
int width() default 0;
int order() default 0;
}(2) 将以上两个注解添加到UserTO实体类上, 添加注解后的代码如下：
package com.example.mybatis.model;
import com.example.mybatis.annotation.ExcelColumn;
import com.example.mybatis.annotation.ExcelSheet;
import java.io.Serializable;
@ExcelSheet(value="userInfo",voClass = "com.example.mybatis.model.UserTO")
public class UserTO implements Serializable {
@ExcelColumn(name = "id", displayName = "ID",type = "Long", width = 50, order = 0)
private Long id;
@ExcelColumn(name="deptNo", displayName = "部门编号", type="Integer", width=80, order=4)
private Integer deptNo;
@ExcelColumn(name="deptName", displayName = "部门名称", type="String", width=120, order=5)
private String deptName;
@ExcelColumn(name="userAccount", displayName = "用户账号", type="String", width=120, order=1)
private String userAccount;
@ExcelColumn(name="password", displayName = "用户密码", type="String", width=120, order=2)
private String password;
@ExcelColumn(name="nickName", displayName = "用户名称", type="String", width=150, order=3 )
private String nickName;
@ExcelColumn(name="emailAddress",displayName = "邮箱地址", type="String", width=180, order=6)
private String emailAddress;
@ExcelColumn(name="birthDay",displayName="出生日期", type="Date", width=150, order=7 )
private String birthDay;
@ExcelColumn(name="phoneNum", displayName = "手机号码", type="String", width=150, order = 8)
private String phoneNum;
@ExcelColumn(name="updatedBy", displayName = "更新人", type="String", width=120, order = 9)
private String updatedBy;
@ExcelColumn(name="updatedTime", displayName = "更新时间", type="Date", width = 180, order = 10)
private String updatedTime;
//此处省略setter和getter方法
}(3) 编辑读取注解配置的元数据工具类
AnnotationSheetUtil.java
package com.example.mybatis.utils;
import com.example.mybatis.annotation.ExcelColumn;
import com.example.mybatis.annotation.ExcelSheet;
import com.example.mybatis.model.ColumnInfo;
import com.example.mybatis.model.SheetInfo;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.util.Comparator;
import java.util.List;
public class AnnotationSheetUtil {
public static SheetInfo initSheetInfo(Class clazz){
SheetInfo sheetInfo=null;
if(clazz.isAnnotationPresent(ExcelSheet.class)){
Annotation annotation = clazz.getDeclaredAnnotation(ExcelSheet.class);
ExcelSheet sheetAnnotation = (ExcelSheet) annotation;
String sheetName = sheetAnnotation.name();
if("".equals(sheetName)){
sheetName = sheetAnnotation.value();
}
if("".equals(sheetName)){
sheetName = clazz.getSimpleName();
}
String voClass = sheetAnnotation.voClass();
if("".equals(voClass)){
voClass = clazz.getName();
}
sheetInfo = new SheetInfo(sheetName,voClass);
List<ColumnInfo> columnInfos = sheetInfo.getColumns();
Field[] fields = clazz.getDeclaredFields();
for(Field field: fields){
if(field.isAnnotationPresent(ExcelColumn.class)){
Annotation columnAnnotation = field.getDeclaredAnnotation(ExcelColumn.class);
ExcelColumn excelColumnAnnotation = (ExcelColumn) columnAnnotation;
String propName = excelColumnAnnotation.name();
if("".equals(propName)){
propName = field.getName();
}
String displayName = excelColumnAnnotation.displayName();
String type = excelColumnAnnotation.type();
Integer width = excelColumnAnnotation.width();
ColumnInfo columnInfo = new ColumnInfo(propName,displayName,type,width);
String format = excelColumnAnnotation.format();
if(!"".equals(format)){
columnInfo.setFormat(format);
}
Integer order = excelColumnAnnotation.order();
columnInfo.setOrder(order);
columnInfos.add(columnInfo);
}
}
columnInfos.sort(Comparator.comparing(item->{
return item.getOrder();
}));
}
return sheetInfo;
}
}(4) Controller层实现
ExcelController.java
@PostMapping("export/searchExcel")
public void exportSearchExcel(UserForm form, HttpServletResponse response){
logger.info("form={}", JSONArray.toJSONString(form));
// response.setHeader("Content-Type","application/octet-stream");
if(searchUserSheet==null){
searchUserSheet = AnnotationSheetUtil.initSheetInfo(UserTO.class);
}
excelService.exportSearchSheet(form,searchUserSheet,response);
}(5) Service层实现
IExcelService.java
void exportSearchSheet(UserForm form, SheetInfo sheetInfo,HttpServletResponse response);ExcelService.java
@Override
public void exportSearchSheet(UserForm form, SheetInfo sheetInfo, HttpServletResponse response) {
String sheetName = sheetInfo.getName();
Date now = new Date();
String dateTime = sdf.format(now);
String fileName = sheetName+dateTime+".xlsx";
try {
response.setHeader("Content-Disposition","attachment;filename="+URLEncoder.encode(fileName,"UTF-8"));
List<UserTO> userList = userBusiness.searchUsers(form);
OutputStream os = response.getOutputStream();
//调用工具类写Excel
ExcelReadWriteUtil.writeSingleExcel(sheetInfo,userList,os);
logger.info("export single excel sheet success!");
} catch (Exception e) {
logger.error("export single excel sheet failed!",e);
throw new RuntimeException("export single excel sheet failed,caused by: "+e.getMessage());
}
}(6) ExcelReadWriteUtil.writeSingleExcel(SheetInfo sheetInfo,List<?> dataList,OutputStream os)静态方法的实现
ExcelReadWriteUtil.java
public static void writeSingleExcel(SheetInfo sheetInfo,List<?> dataList,OutputStream os) throws Exception{
XSSFWorkbook workbook = new XSSFWorkbook();
String sheetName = sheetInfo.getName();
String voClass = sheetInfo.getVoClass();
XSSFSheet sheet = workbook.createSheet(sheetName);
Row headRow = sheet.createRow(0);
//设置行高
// headRow.setHeightInPoints(256*30);
//设置列头
List<ColumnInfo> columnInfos = sheetInfo.getColumns();
for(int i=0;i<columnInfos.size();i++){
Cell cell = headRow.createCell(i,CellType.STRING);
ColumnInfo columnInfo = columnInfos.get(i);
// sheet.setColumnWidth(i,columnInfo.getWidth());
cell.setCellValue(columnInfo.getDisplayName());
}
//设置列主体
Class clazz = Class.forName(voClass);
for(int i=0;i<dataList.size();i++){
Object item = dataList.get(i);
Row contentRow = sheet.createRow(i+1);
// contentRow.setHeightInPoints(256*26);
for(int j=0;j<columnInfos.size();j++){
ColumnInfo columnInfo = columnInfos.get(j);
// Integer width = columnInfo.getWidth();
String name = columnInfo.getName();
String getFieldName = "get"+upCaseFirstChar(name);
Method method = clazz.getDeclaredMethod(getFieldName,null);
Object value = method.invoke(item,null);
String type = columnInfo.getType();
switch (type){
case "Integer":
Integer intVal = (Integer) value;
Cell cell = contentRow.createCell(j,CellType.NUMERIC);
double val = Double.parseDouble(String.valueOf(intVal));
cell.setCellValue(val);
break;
case "Long":
Long longVal = (Long) value;
Cell cell1 = contentRow.createCell(j,CellType.NUMERIC);
double val1 = Double.parseDouble(String.valueOf(longVal));
cell1.setCellValue(val1);
break;
case "Date":
String dateStr = (String) value;
Cell cell2 = contentRow.createCell(j,CellType.STRING);
cell2.setCellValue(dateStr);
default:
String strVal = (String) value;
Cell cell3 = contentRow.createCell(j,CellType.STRING);
cell3.setCellValue(strVal);
break;
}
}
}
//将工作簿写到输出流中
workbook.write(os);
workbook.close();
}保存所有修改过的代码后，在IDEA中以Debug模式启动spring-boot项目，内嵌的tomcat启动成后在控制台中显示如下日志信息时表示项目启动成功：
main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8081 (http) with context path '/springboot'
2020-01-08 22:13:18.352 INFO 916 --- [ main] com.example.mybatis.MybatisApplication : Started MybatisApplication in 5.954 seconds (JVM running for 8.894)3 功能测试
此时可以返回前端界面点击查询按钮和导出按钮测试web页面功能了
（1）当form表单中创建时间的开始时间和结束时间分别为2019-12-20 00:00:00和2019-12-24 00:00:00，部门选项为所有部门，用户账号和中文名为空时点击查询按钮查询符合条件的数据，结果如下图所示：
（2）点击导出按钮，浏览器左下方会显示下载成功的excel文件，下载成功后打开文件部门内容如下图所示：
4 技术栈总结
本文利用springboot作为后端架构，vue-cli 3脚手架工具和element-ui库开发了一个可视化的
页面，实现了数据查询和excel文件导出导入功能。
- 后端利用自定义注解实现读取excel文件导出数据转换实现类元数据信息；
- 利用apache poi api和java 反射知识自定义Excel读写工具类方便开发人员简化Excel导入和导出功能的开发；
- 前端利用vue整合element-ui实现了简单的按钮查询和导出功能，使用封装后axios组件发送http请求，实现与服务端的数据交互。