POI批量导出Excel
<!DOCTYPE html>
<html>
<head>
<!-- 页面meta -->
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>传智健康</title>
<meta name="description" content="传智健康">
<meta name="keywords" content="传智健康">
<meta content="width=device-width,initial-scale=1,maximum-scale=1,user-scalable=no" name="viewport">
<!-- 引入样式 -->
<link rel="stylesheet" href="../plugins/elementui/index.css">
<link rel="stylesheet" href="../plugins/font-awesome/css/font-awesome.min.css">
<link rel="stylesheet" href="../css/style.css">
</head>
<body class="hold-transition">
<div id="app">
<div class="content-header">
<h1>会员管理<small>会员档案</small></h1>
<el-breadcrumb separator-class="el-icon-arrow-right" class="breadcrumb">
<el-breadcrumb-item :to="{ path: '/' }">首页</el-breadcrumb-item>
<el-breadcrumb-item>会员管理</el-breadcrumb-item>
<el-breadcrumb-item>会员档案</el-breadcrumb-item>
</el-breadcrumb>
</div>
<div class="app-container">
<div class="box">
<div class="filter-container">
<el-input placeholder="档案号/姓名/手机号" v-model="pagination.queryString" style="width: 200px;" class="filter-item"></el-input>
<el-button @click="findPage()" class="dalfBut">查询</el-button>
<el-button type="primary" class="butT" @click="handleCreate()">新建</el-button>
</div>
<div class="excelTitle" > <el-button @click="exportExcel">批量导出Excel</el-button></div>
<!--@row-click="clickRow" ref="moviesTable" -->
<el-table size="small" current-row-key="id" @selection-change="changeFun" ref="moviesTable" :data="dataList" stripe highlight-current-row>
<el-table-column type="selection" v-model="memberIds" align="center" ></el-table-column>
<el-table-column prop="fileNumber" label="档案号" align="center"></el-table-column>
<el-table-column prop="name" label="姓名" align="center"></el-table-column>
<el-table-column label="性别" align="center">
<template slot-scope="scope">
<span>{{ scope.row.sex == '0' ? '不限' : scope.row.sex == '1' ? '男' : '女'}}</span>
</template>
</el-table-column>
<el-table-column prop="age" label="年龄" align="center"></el-table-column>
<el-table-column prop="type" label="健康管理师" align="center"></el-table-column>
<el-table-column prop="regTime" label="注册时间" align="center"></el-table-column>
<el-table-column prop="phoneNumber" label="手机号码" align="center"></el-table-column>
<el-table-column label="操作" align="center">
<template slot-scope="scope">
<el-button type="primary" size="mini" @click="handleUpdate(scope.row)">编辑</el-button>
<el-button size="mini" type="danger" @click="handleDelete(scope.row)">删除</el-button>
</template>
</el-table-column>
</el-table>
<div class="pagination-container">
<el-pagination
class="pagiantion"
@current-change="handleCurrentChange"
:current-page="pagination.currentPage"
:page-size="pagination.pageSize"
layout="total, prev, pager, next, jumper"
:total="pagination.total">
</el-pagination>
</div>
<!-- 新增标签弹层 -->
<div class="add-form">
<el-dialog title="新增会员" :visible.sync="dialogFormVisible">
<el-form ref="dataAddForm" :model="formData" :rules="rules" label-position="right" label-width="100px">
<el-row>
<el-col :span="12">
<el-form-item label="姓名" prop="name">
<el-input v-model="formData.name"/>
</el-form-item>
</el-col>
<el-col :span="12">
<el-form-item label="手机号码" prop="phoneNumber">
<el-input v-model="formData.phoneNumber"/>
</el-form-item>
</el-col>
</el-row>
<el-row>
<el-col :span="12">
<el-form-item label="性别">
<el-select v-model="formData.sex">
<el-option label="不限" value="0"></el-option>
<el-option label="男" value="1"></el-option>
<el-option label="女" value="2"></el-option>
</el-select>
</el-form-item>
</el-col>
<el-col :span="12">
<el-form-item label="出生日期" prop="checkDate">
<!--<el-input v-model="formData.birthday"/>-->
<!--<el-input v-model="formData.birthday"/>-->
<el-date-picker
v-model="formData.birthday"
type="date"
placeholder="选择日期">
</el-date-picker>
</el-form-item>
</el-col>
</el-row>
<el-row>
<el-col :span="12">
<el-form-item label="年龄" prop="age">
<el-input v-model="formData.age"/>
</el-form-item>
</el-col>
<el-col :span="12">
<el-form-item label="注册日期" prop="regTime">
<!--<el-input v-model="formData.regTime"/>-->
<el-date-picker
v-model="formData.regTime"
type="date"
placeholder="选择日期">
</el-date-picker>
</el-form-item>
</el-col>
</el-row>
<el-row>
<el-col :span="12">
<el-form-item label="健康管理师">
<el-select v-model="formData.type">
<el-option v-for="r in helthList" v-bind:value="r.username" v-text="r.username"></el-option>
<!--<el-option label="检查" value="1"></el-option>
<el-option label="检验" value="2"></el-option>-->
</el-select>
</el-form-item>
</el-col>
<el-col :span="12">
<el-form-item label="身份证号码">
<el-input v-model="formData.idCard"/>
</el-form-item>
</el-col>
</el-row>
<el-row>
<el-col :span="24">
<el-form-item label="档案号">
<el-input v-model="formData.fileNumber" type="textarea"></el-input>
</el-form-item>
</el-col>
</el-row>
<el-row>
<el-col :span="24">
<el-form-item label="项目说明">
<el-input v-model="formData.remark" type="textarea"></el-input>
</el-form-item>
</el-col>
</el-row>
</el-form>
<div slot="footer" class="dialog-footer">
<el-button @click="dialogFormVisible = false">取消</el-button>
<el-button type="primary" @click="handleAdd()">确定</el-button>
</div>
</el-dialog>
</div>
<!-- 编辑标签弹层 -->
<div class="add-form">
<el-dialog title="编辑检查项" :visible.sync="dialogFormVisible4Edit">
<el-form ref="dataEditForm" :model="formData" :rules="rules" label-position="right" label-width="100px">
<el-row>
<el-col :span="12">
<el-form-item label="姓名" prop="name">
<el-input v-model="formData.name"/>
</el-form-item>
</el-col>
<el-col :span="12">
<el-form-item label="手机号码" prop="phoneNumber">
<el-input v-model="formData.phoneNumber"/>
</el-form-item>
</el-col>
</el-row>
<el-row>
<el-col :span="12">
<el-form-item label="性别">
<el-select v-model="formData.sex">
<el-option label="不限" value="0"></el-option>
<el-option label="男" value="1"></el-option>
<el-option label="女" value="2"></el-option>
</el-select>
</el-form-item>
</el-col>
<el-col :span="12">
<el-form-item label="出生日期" prop="checkDate">
<el-date-picker
v-model="formData.birthday"
type="date"
placeholder="选择日期">
</el-date-picker>
</el-form-item>
</el-col>
</el-row>
<el-row>
<el-col :span="12">
<el-form-item label="年龄" prop="age">
<el-input v-model="formData.age"/>
</el-form-item>
</el-col>
<el-col :span="12">
<el-form-item label="注册日期" prop="regTime">
<!--<el-input v-model="formData.regTime"/>-->
<el-date-picker
v-model="formData.regTime"
type="date"
placeholder="选择日期">
</el-date-picker>
</el-form-item>
</el-col>
</el-row>
<el-row>
<el-col :span="12">
<el-form-item label="健康管理师">
<el-select v-model="formData.type">
<el-option v-for="r in helthList" v-bind:value="r.username" v-text="r.username"></el-option>
<!--<el-option label="检查" value="1"></el-option>
<el-option label="检验" value="2"></el-option>-->
</el-select>
</el-form-item>
</el-col>
<el-col :span="12">
<el-form-item label="身份证号码">
<el-input v-model="formData.idCard"/>
</el-form-item>
</el-col>
</el-row>
<el-row>
<el-col :span="24">
<el-form-item label="档案号">
<el-input v-model="formData.fileNumber" type="textarea"></el-input>
</el-form-item>
</el-col>
</el-row>
<el-row>
<el-col :span="24">
<el-form-item label="项目说明">
<el-input v-model="formData.remark" type="textarea"></el-input>
</el-form-item>
</el-col>
</el-row>
</el-form>
<div slot="footer" class="dialog-footer">
<el-button @click="dialogFormVisible4Edit = false">取消</el-button>
<el-button type="primary" @click="handleEdit()">确定</el-button>
</div>
</el-dialog>
</div>
</div>
</div>
</div>
</body>
<!-- 引入组件库 -->
<script src="../js/vue.js"></script>
<script src="../plugins/elementui/index.js"></script>
<script type="text/javascript" src="../js/jquery.min.js"></script>
<script src="../js/axios-0.18.0.js"></script>
<script>
var vue = new Vue({
el: '#app',
data:{
pagination: {//分页相关模型数据
currentPage: 1,//当前页码
pageSize:10,//每页显示的记录数
total:0,//总记录数
queryString:null//查询条件
},
dataList: [],//当前页要展示的分页列表数据
helthList:[],//
formData: {},//表单数据
managerList: [],
memberIds:[],
dialogFormVisible: false,//增加表单是否可见
dialogFormVisible4Edit:false,//编辑表单是否可见
rules: {//校验规则
phoneNumber: [{ required: true, message: '手机号码为必填项', trigger: 'blur' }],
name: [{ required: true, message: '姓名为必填项', trigger: 'blur' }]
}
},
//钩子函数，VUE对象初始化完成后自动执行
created() {
this.findPage();
this.findhelth();
},
methods: {
//批量导出EXcel
exportExcel(){
var memberIds = [];
if (this.managerList.length!=0) {
$.each(this.managerList,function(index,item){
memberIds.push(item.id)
})
}
if(memberIds.length==0){
//你还没选择数据
this.$message.error("你还没有选择数据")
return false;
}
window.location.href = "/report/exportAll.do?memberIds="+memberIds;
},
/* clickRow(row){
/!* this.$refs.moviesTable.toggleRowSelection(row)*!/
var str = JSON.stringify(row);
alert(str)
},*/
changeFun(row) {
// console.log(row)
this.managerList = row;
},
//查询健康师
findhelth(){
axios.post("/member/findhelth.do").then((resp)=>{
this.helthList =resp.data;
})
},
//编辑里的确定
handleEdit() {
//表单校验下
this.$refs['dataEditForm'].validate((valid)=>{
if(valid){
//表单校验通过发送请求
axios.post("/member/editMember.do",this.formData).then((resp)=>{
if(resp.data.flag){
//编辑成功,弹出成功提示信息
this.$message({
message:resp.data.message,
type:'success'
});
}else {
//编辑失败,弹出错误信息
this.$message.error(resp.data.message);
}
}).finally(()=>{
this.findPage();
this.dialogFormVisible4Edit = false;
})
}else {
this.$message.error("表单数据校验失败")
}
})
},
//添加
handleAdd () {
//提交表单校验输入的是否合法
this.$refs['dataAddForm'].validate((valid)=>{
if(valid){
//表单校验通过,发送ajax请求将表单数据提交到后台
axios.post("/member/add.do",this.formData).then((resp)=>{
//隐藏新增窗口
this.dialogFormVisible = false;
//判断后台返回的flag值
if(resp.data.flag){
this.$message({
message:resp.data.message,
type:'success'
})
}else {
this.$message.error(resp.data.message);
}
}).finally(()=>{
this.findPage();
});
} else {
//表单校验失败
this.$message.error("表单数据校验失败,请输入必填项")
}
});
},
//分页查询
findPage() {
//分页参数
var param = {
currentPage:this.pagination.currentPage,//当前页
pageSize:this.pagination.pageSize,
queryString:this.pagination.queryString
};
//请求后台
axios.post("/member/findPage.do",param).then((resp)=>{
//为模型数据赋值,基于VUE双向绑定展示到页面
this.dataList = resp.data.rows;
this.pagination.total = resp.data.total;
})
},
// 重置表单
resetForm() {
},
// 弹出添加窗口
handleCreate() {
this.formData = {};
this.dialogFormVisible = true;
},
// 弹出编辑窗口
handleUpdate(row) {
//发送ajax请求获取会员信息-----回显
/*this.dialogFormVisible4Edit =true;*/
axios.get("/member/findMemberAll.do?id=" + row.id).then((resp)=>{
if(resp.data.flag){
//弹出编辑窗口
this.dialogFormVisible4Edit =true;
//为模型数据设置值,基于VUE双向数据绑定回显到页面
this.formData =resp.data.data;
}else{
this.$message.error("获取数据失败,请重新刷新当前页面")
}
})
},
//切换页码
handleCurrentChange(currentPage) {
//currentPage为切换后的页码
this.pagination.currentPage =currentPage;
this.findPage();
},
// 删除
handleDelete(row) {
this.$confirm('确定要删除当前选择的记录吗?老铁','提示',{
confirmButtonText: '确定',
cancelButtonText: '取消',
type: 'warning'
}).then(()=>{
axios.get("/member/deleteMember.do?id="+row.id).then((resp)=>{
if(resp.data.flag){
this.$message({
type:'success',
message:'删除成功,老铁'
});
this.findPage();
}else {
this.$message.error(resp.data.message);
}
})
}).catch(()=>{
this.$message({
type:'info',
message:'已取消删除,老铁'
})
});
}
}
})
</script>
</html>
通过多对多查询所有包含的信息,封装到pojo.
/**
* 会员数据导出
* @return
*/
@RequestMapping("/exportAll")
public Result exportAll(HttpServletResponse response,HttpServletRequest request,Integer[] memberIds) throws Exception{
List<Order> orderList = new ArrayList<>();
//获取member的id查询
for (Integer id : memberIds) {
Order order = orderService.findParticularMember(id);
if (order != null) {
if ("1".equals(order.getMember().getSex())) {
order.getMember().setSex("男");
} else {
order.getMember().setSex("女");
}
/*if (order.getAddress() !=null) {
orderList.add(order);
}*/
orderList.add(order);
}
}
/* for (Order order : orderList) {
System.out.println(order.getMember().getName());
System.out.println(order.getMember().getSex());
System.out.println(order.getMember().getPhoneNumber());
System.out.println(order.getAddress().getAddress());
Setmeal setmeal = order.getSetmeal();
System.out.println(setmeal.getName());
for (CheckGroup checkGroup : setmeal.getCheckGroups()) {
System.out.println(checkGroup.getName());
}
}*/
//提供绝对路径
String filePath = request.getSession().getServletContext().getRealPath("template") + File.separator + "health.xlsx";
//基于提供的Excel模板文件在内存中创建一个Excel表格对象
XSSFWorkbook excel = new XSSFWorkbook(new FileInputStream(new File(filePath)));
//读取第一个工作表
XSSFSheet sheet = excel.getSheetAt(0);
XSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue("姓名");
row.createCell(1).setCellValue("性别");
row.createCell(2).setCellValue("手机号");
row.createCell(3).setCellValue("机构地址");
row.createCell(4).setCellValue("套餐");
row.createCell(5).setCellValue("检查组");
row.createCell(6).setCellValue("检查项");
int i = 1;
for (Order order : orderList) {
row = sheet.createRow(i);
row.createCell(0).setCellValue(order.getMember().getName());
row.createCell(1).setCellValue(order.getMember().getSex());
row.createCell(2).setCellValue(order.getMember().getPhoneNumber());
if(order.getAddress()!=null) {
row.createCell(3).setCellValue(order.getAddress().getName());
}
row.createCell(4).setCellValue(order.getSetmeal().getName());
StringBuilder sb1 = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
for (CheckGroup checkGroup : order.getSetmeal().getCheckGroups()) {
sb1.append(checkGroup.getName()+" ,");
for (CheckItem checkItem : checkGroup.getCheckItems()) {
sb2.append(checkItem.getName()+" ,");
}
}
row.createCell(5).setCellValue(sb1.toString());
row.createCell(6).setCellValue(sb2.toString());
i++;
}
OutputStream out = response.getOutputStream();
//使用输出流进行表格下载,基于浏览器作为客户端下载
response.setContentType("application/x-download");//代表的是Excel文件类型
response.setHeader("content-Disposition", "attachment;filename=health.xlsx");//指定以附件形式进行下载
excel.write(out);
out.flush();
out.close();
excel.close();
// return new Result(true, "导出成功");
return null;
}
Service接口:
//查询预约详情
//查询预约详情
Order findParticularMember(Integer id);
ServiceImpl层:
//查询预约详情
//查询预约详情
@Override
public Order findParticularMember(Integer id) {
return orderDao.findParticularMember(id);
}
OrderDao层
//查询预约详情
//查询预约详情
Order findParticularMember(Integer id);
映射文件
<mapper namespace="com.itheima.dao.OrderDao">
<resultMap id="baseResultMap" type="com.itheima.pojo.Order">
<id column="id" property="id"/>
<result column="member_id" property="memberId"/>
<result column="orderDate" property="orderDate"/>
<result column="orderType" property="orderType"/>
<result column="orderStatus" property="orderStatus"/>
<result column="setmeal_id" property="setmealId"/>
</resultMap>
<resultMap id="findParticularAddress" type="com.itheima.pojo.Order" extends="baseResultMap">
<!--查询会员信息-->
<association property="member" javaType="com.itheima.pojo.Member"
select="com.itheima.dao.MemberDao.findParticularMember"
column="member_id">
</association>
<!--查询地址信息-->
<association property="address" javaType="com.itheima.pojo.Address"
select="com.itheima.dao.AddressDao.findParticularAddress"
column="id">
</association>
<!--查询套餐信息-->
<association property="setmeal" javaType="com.itheima.pojo.Setmeal"
select="com.itheima.dao.SetmealDao.findParticularSetmeal"
column="setmeal_id">
</association>
</resultMap>
<!--批量上传excel-->
<select id="findParticularMember" parameterType="int" resultMap="findParticularAddress">
select *
from t_order
where member_id = #{value}
</select>
<!--查询会员信息映射文件里的sql-->
<!--批量上传excel-->
<select id="findParticularMember" parameterType="int" resultType="com.itheima.pojo.Member">
select *
from t_member
where id = #{value}
</select>
com.itheima.dao.AddressDao.findParticularAddress
<!--批量上传excel-->
<select id="findParticularAddress" parameterType="int" resultType="com.itheima.pojo.Address">
select * from t_address where id =(select address_id from t_order_address where order_id = #{value})
</select>
<!--批量导入excel-->
<select id="findParticularSetmeal" parameterType="int" resultMap="findByIdResultMap">
select * from t_setmeal where id=#{value}
</select>
com.itheima.dao.AddressDao.findParticularAddress
<select id="findParticularAddress" parameterType="int" resultType="com.itheima.pojo.Address">
select * from t_address where id =(select address_id from t_order_address where order_id = #{value})
</select>
<select id="findParticularSetmeal" parameterType="int" resultMap="findByIdResultMap">
select * from t_setmeal where id=#{value}
</select>
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
