导入
Vue
<div class="ruleSetBox">
<div class="label"><span>限定用户白名单</span></div>
<div class="switch">
<el-switch
v-model="ruleData.whiteFlag"
:active-value="1"
active-color="#409EFF"
:inactive-value="0"
inactive-color="#ff4949"
:disabled="onlyread?true:false">
</el-switch>
</div>
<div class="input" v-if="ruleData.whiteFlag">
<span class="clickSpan">
设置白名单用户
<input type="file" id="inputFile" class="inputFile" @change="importWhiteInfo()" v-if="!onlyread">
</span>
(
<a href="./whiteModel.xlsx" download="白名单模板.xlsx" >模板下载</a>
)
</div>
</div>
export default {
methods:{
importWhiteInfo() { // 导入白名单
let files = document.getElementById('inputFile').files
let data = new FormData();
data.append('file', files[0]);
this.$http.post('/xxx/userWhiteInfo?id=' + this.id, data).then(res => {
if (res.data.code == 0) {
this.$message(res.data.msg)
} else {
this.$message.error(res.data.msg);
}
})
}
}
}
<style lang="scss">
.ruleSet {
padding: 30px;
.ruleSetBox {
height: 50px;
display: flex;
align-items: center;
.label {
width: 180px;
}
.switch {
margin-right: 50px;
}
.input {
width: 350px;
.el-date-editor {
width: 350px;
}
.el-input {
width: 205px;
}
.el-select {
width: 200px;
}
span.clickSpan {
color: #409EFF;
font-size: 0.9rem;
display: inline-block;
position: relative;
cursor: pointer;
&:hover {opacity: 0.8;}
.inputFile {
position: absolute;
top: 0;
left: 0;
width: 100%;
height: 100%;
opacity: 0;
cursor: pointer;
}
}
}
}
}
</style>
pom.xml
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.3.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.3.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.3.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.3.0</version>
</dependency>
UserWhiteInfoDTO
import cn.afterturn.easypoi.excel.annotation.Excel;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.databind.ser.std.ToStringSerializer;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.io.Serializable;
@Data
@ApiModel(value = "白名单信息表")
public class UserWhiteInfoDTO implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "白名单物理主键")
@JsonSerialize(using= ToStringSerializer.class)
private Long id;
@ApiModelProperty(value = "问卷ID")
@JsonSerialize(using= ToStringSerializer.class)
private Long questionnaireId;
@Excel(name = "姓名")
@ApiModelProperty(value = "姓名")
private String playerName;
@Excel(name = "联系方式")
@ApiModelProperty(value = "联系方式")
private String phone;
}
UserWhiteInfoEntity
import cn.afterturn.easypoi.excel.annotation.Excel;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.EqualsAndHashCode;
@Data
@EqualsAndHashCode(callSuper=false)
@TableName("user_white_info")
public class UserWhiteInfoEntity {
private static final long serialVersionUID = 1L;
/**
* id
*/
@Excel(name = "序号")
private Long id;
/**
* 问卷ID
*/
@Excel(name = "问卷ID")
private Long questionnaireId;
/**
* 姓名
*/
@Excel(name = "姓名")
private String playerName;
/**
* 联系方式
*/
@Excel(name = "联系方式")
private String phone;
}
Controller
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
@PostMapping(value = "/UserWhiteInfo")
@ApiOperation(value = "导入白名单Excel", notes = "导入")
@LogOperation(value = "导入白名单Excel")
public Result<String> importWhiteInfo(MultipartFile file, @RequestParam Long questionnaireId) throws Exception {
// 携带questionnaireId用于判断此问卷白名单是否已经导入
if (!ObjectUtils.isEmpty(questionnaireId)){
Map<String,Object> param = new HashMap<>();
param.put("questionnaireId", questionnaireId);
userWhiteInfoService.deleteById(param);
}
List<UserWhiteInfoDTO> userWhiteInfoDTOList = ExcelImportUtil.importExcel(file.getInputStream()
, UserWhiteInfoDTO.class, new ImportParams());
userWhiteInfoDTOList.forEach(e -> e.setQuestionnaireId(questionnaireId));
userWhiteInfoService.insertBatch(ConvertUtils.sourceToTarget(userWhiteInfoDTOList, UserWhiteInfoEntity.class));
Result<String> result = new Result();
result.setMsg("导入成功");
return result;
}
导出
Vue
<div class="queryBox">
<div class="left"></div>
<div class="right">
<el-button type="primary" size="small" @click="export()">导出数据</el-button>
</div>
</div>
export default {
methods:{
export() { // 导出数据
let anchor = document.createElement("a");
anchor.href = window.SITE_CONFIG['apiURL'] + '/xxx/userSignRecord/export?questionnaireId=' + this.questionnaireId;
anchor.setAttribute("target", '_blank');
anchor.innerHTML = "downloading...";
anchor.style.display = "none";
document.body.appendChild(anchor);
setTimeout(() => {
anchor.click();
document.body.removeChild(anchor);
setTimeout(() => { self.URL.revokeObjectURL(anchor.href); }, 250);
}, 66);
}
}
}
<style lang="scss">
.reportSignin {
.queryBox {
display: flex;
justify-content: flex-end;
}
.tableBox {
margin-top: 20px;
.el-pagination {
text-align: center;
}
}
}
</style>
UserSignRecord
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.databind.ser.std.ToStringSerializer;
import lombok.Data;
import java.io.Serializable;
@Data
public class UserSignRecord implements Serializable {
@JsonSerialize(using= ToStringSerializer.class)
private long id;
private String username;
private String realName;
private String phoneNum;
private String createDate;
}
UseSignExcel
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
@Data
public class UseSignExcel{
@Excel(name = "序号")
private Long id;
@Excel(name = "用户昵称")
private String username;
@Excel(name = "真实姓名")
private String realName;
@Excel(name = "联系方式")
private String phoneNum;
@Excel(name = "签到日期")
private String createDate;
}
Controller
@GetMapping("/UserSignRecord/export")
@ApiOperation("导出签到记录")
@LogOperation("导出签到记录")
public Result<String> exportSignRecord(@ApiIgnore @RequestParam Map<String, Object> params) throws Exception{
// 参数校验
Long questionnaireId= MapUtils.getLong(params, "questionnaireId");
if (ObjectUtils.isEmpty(questionnaireId)) {
return new Result().error("导出失败");
}
List<UserSignRecord> list = userSignRecordService.getUserSignRecordList(params);
ExcelExportUtil.exportExcel(new ExportParams("用户签到记录","表格"), UseSignExcel.class, list);
Result<String> result = new Result();
result.setMsg("导出成功");
return result;
}
或者使用自定义导出工具类
ExcelUtils
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.BeanUtils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
/**
* Excel导出工具类
*
*/
public class ExcelUtils {
/**
* Excel导出
*
* @param response response
* @param fileName 文件名
* @param list 数据List
* @param pojoClass 对象Class
*/
public static void exportExcel(HttpServletResponse response, String fileName, Collection<?> list,
Class<?> pojoClass) throws IOException {
if(StringUtils.isBlank(fileName)){
//当前日期
fileName = DateUtils.format(new Date());
}
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), pojoClass, list);
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
ServletOutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
}
/**
* Excel导出:先将sourceList转换成List<targetClass>再导出
*
* @param response response
* @param fileName 文件名
* @param sourceList 原数据List
* @param targetClass 目标对象Class
*/
public static void exportExcelToTarget(HttpServletResponse response, String fileName, Collection<?> sourceList,
Class<?> targetClass) throws Exception {
List targetList = new ArrayList<>(sourceList.size());
for(Object source : sourceList){
Object target = targetClass.newInstance();
BeanUtils.copyProperties(source, target);
targetList.add(target);
}
exportExcel(response, fileName, targetList, targetClass);
}
}
Controller
@GetMapping("/UserSignRecord/export")
@ApiOperation("导出签到记录")
@LogOperation("导出签到记录")
public Result<String> exportSignRecord(@ApiIgnore @RequestParam Map<String, Object> params, HttpServletResponse response) throws Exception {
// 参数校验
Long questionnaireId= MapUtils.getLong(params, "questionnaireId");
if (ObjectUtils.isEmpty(questionnaireId)) {
return new Result().error("导出失败");
}
List<UserSignRecord> list = userSignRecordService.getUserSignRecordList(params);
ExcelUtils.exportExcelToTarget(response, "用户签到情况", list, UseSignExcel.class);
Result<String> result = new Result();
result.setMsg("导出成功");
return result;
}