导入/导出/下载模板
vue代码
<el-button v-if="isAuth('school:paper:import')" type="primary" @click="exportHandle(1)">导出</el-button>
<el-button v-if="isAuth('school:paper:import')" type="primary" @click="exportHandle(0)">下载模板</el-button>
<el-button v-if="isAuth('school:paper:import')" type="primary" @click="importHandle()">导入</el-button>
导入 弹框
<!-- 弹窗, 导入 -->
<import-excel v-if="importVisible" ref="importExcel" @refreshDataList="getDataList"></import-excel>
引入导入页面
import ImportExcel from './paper-import'
components: {
ImportExcel
},
// 下载模板
exportHandle(val){
this.$http({
url: this.$http.adornUrl('/school/paper/downTemplate/'+val),
method: 'get',
responseType: 'blob',
}).then(res => {
let fileName = '题库模板'+(new Date()).getTime()+'.xlsx'
this.download(res.data,fileName)
});
},
download (data,fileName) {
if (!data) {
return
}
let url = window.URL.createObjectURL(new Blob([data]))
let link = document.createElement('a')
link.style.display = 'none'
link.href = url
link.setAttribute('download', fileName)
document.body.appendChild(link)
link.click()
},
// 导入
importHandle(){
this.importVisible = true
this.$nextTick(() => {
this.$refs.importExcel.init()
})
},
导入页面代码
<template>
<el-dialog
:title="'导入'"
:close-on-click-modal="false"
:visible.sync="visible">
<el-form @keyup.enter.native="submitUpload()" label-width="80px">
<el-form-item label="上传文件" prop="explain">
<el-upload
class="upload-demo"
ref="upload"
action="doUpload"
accept=".xls,.xlsx"
:limit="1"
:file-list="fileList"
:before-upload="beforeUpload">
<el-button slot="trigger" size="small" type="primary">上传题库</el-button>
<div slot="tip" class="el-upload__tip">请选择xls/xlsx文件上传</div>
<div slot="tip" class="el-upload-list__item-name">{{fileName}}</div>
</el-upload>
</el-form-item>
</el-form>
<span slot="footer" class="dialog-footer">
<el-button @click="visible = false">取消</el-button>
<el-button type="primary" @click="submitUpload()">确定</el-button>
</span>
</el-dialog>
</template>
<script>
export default {
data () {
return {
visible: false,
files:"",
fileName:'',
fileList: [],
}
},
methods: {
init () {
this.visible = true
},
submitUpload() {
debugger
console.log('上传'+this.files.name)
if(this.fileName == ""){
this.$message.warning('请选择要上传的文件!')
return false
}
let fileFormData = new FormData();
fileFormData.append('file', this.files, this.fileName);
let requestConfig = {
headers: {
'Content-Type': 'multipart/form-data'
},
}
this.$http.post(this.$http.adornUrl(`/school/paper/import`), fileFormData, requestConfig).then(({data}) => {
if (data && data.code === 0) {
this.$message({
message: '操作成功',
type: 'success',
duration: 1500,
onClose: () => {
this.visible = false
this.$emit('refreshDataList')
}
})
} else if(data.code === -1){
this.$message.error(data.msg)
this.visible = false
} else {
this.$message.error(data.msg)
this.visible = false
}
})
},
// 上传前对文件的大小的判断
beforeUpload(file){
this.files = file;
const extension = file.name.split('.')[1] === 'xls'
const extension2 = file.name.split('.')[1] === 'xlsx'
const isLt2M = file.size / 1024 / 1024 < 5
if (!extension && !extension2) {
this.$message.warning('上传模板只能是 xls、xlsx格式!')
return
}
if (!isLt2M) {
this.$message.warning('上传模板大小不能超过 5MB!')
return
}
this.fileName = file.name;
return false // 返回false不会自动上传
},
}
}
</script>
后台
引入依赖
<dependency>
<groupId>xml-apis</groupId>
<artifactId>xml-apis</artifactId>
<version>1.4.01</version>
</dependency>
controller
一行代码构建 Excel 导入模板
使用 ExcelKit 提供的API 构建导入模板, 会根据配置生成批注, 下拉框等
/**
* 导出
*/
@GetMapping("/downTemplate/{type}")
@RequiresPermissions("school:paper:import")
public void downTemplate(HttpServletResponse response,@PathVariable("type") Integer type) {
List<PaperEntity> list = new ArrayList<>();
if(type == 1){
list = paperService.selectList(new EntityWrapper<PaperEntity>());
ExcelKit.$Export(PaperEntity.class, response).downXlsx(list, false);
}else{
list = paperService.getListLimit();
ExcelKit.$Export(PaperEntity.class, response).downXlsx(list, true);
}
}
/**
* 导入文件
*/
@SysLog("导入题库")
@PostMapping("/import")
@RequiresPermissions("school:paper:import")
public R upload(@RequestParam("file") MultipartFile file) {
if (file.isEmpty()) {
throw new RRException("上传文件不能为空");
}
//上传文件
String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
if(StringUtils.equalsAnyIgnoreCase(suffix,".xls")
||StringUtils.equalsAnyIgnoreCase(suffix,".xlsx")){
List<PaperEntity> successList = new ArrayList();
List<Map<String, Object>> errorList = new ArrayList();
try {
//题号
Integer type1 = 0;Integer type2 = 0;Integer type3 = 0;Integer type4 = 0;
List<PaperEntity> mapType = paperService.getTypeNumMax();
for (PaperEntity obj : mapType) {
type1 = "1".equals(obj.getType())?obj.getNum():0;
type2 = "2".equals(obj.getType())?obj.getNum():0;
type3 = "3".equals(obj.getType())?obj.getNum():0;
type4 = "4".equals(obj.getType())?obj.getNum():0;
}
Integer num1 = type1;
Integer num2 = type2;
Integer num3 = type3;
Integer num4 = type4;
ExcelKit.$Import(PaperEntity.class).readXlsx(file.getInputStream(), new ExcelReadHandler<PaperEntity>() {
@Override
public void onSuccess(int sheetIndex, int rowIndex, PaperEntity entity) {
Integer num = 0;
switch (entity.getType()){
case "1":
num = num1 + 1;
break;
case "2":
num = num2 + 1;
break;
case "3":
num = num3 + 1;
break;
case "4":
num = num4 + 1;
break;
default:
num = 0;
}
entity.setNum(num);
successList.add(entity); // 单行读取成功,加入sucessList。
}
@Override
public void onError(int sheetIndex, int rowIndex,
List<ExcelErrorField> errorFields) {
// 读取数据失败,记录了当前行所有失败的数据
Map<String, Object> map = new HashMap<>();
map.put("sheetIndex", sheetIndex);
map.put("rowIndex", rowIndex);
map.put("errorFields", errorFields);
errorList.add(map);
}
});
} catch (IOException e) {
e.printStackTrace();
logger.error("导入失败:"+e.getMessage());
throw new RRException("导入失败");
}
if(successList.size() > 0){
paperService.insertBatch(successList);
}
if(errorList.size() > 0){
return R.error(-1,"导入失败数据",errorList);
}
return R.ok("成功导入数据:"+successList.size()+"条");
}else{
logger.error("上传文件类型不允许,请请上传xls/xlsx文件");
throw new RRException("上传文件类型不允许,请请上传xls/xlsx文件");
}
}
entity 根据自己的业务场景实现,目前只展示部分代码
@Excel("题库")
@TableName("paper")
public class PaperEntity implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 主键
*/
@TableId
private Integer id;
/**
* 问题
*/
@ExcelField(value = "问题",required = true,comment = "请填写问题")
private String questions;
/**
* 选项1
*/
@ExcelField(value = "选项1",required = true,comment = "请设置选项1")
private String answer1;
/**
* 选项2
*/
@ExcelField(value = "选项2",required = true,comment = "请设置选项2")
private String answer2;
/**
* 选项3
*/
@ExcelField(value = "选项3",required = true,comment = "请设置选项3")
private String answer3;
/**
* 选项4
*/
@ExcelField(value = "选项4",required = true,comment = "请设置选项4")
private String answer4;
/**
* 正确答案
*/
@ExcelField(value = "正确答案",required = true,comment = "请设置正确答案")
private String trueanswer;
/**
* 类型
*/
@ExcelField(value = "类型",required = true,comment = "请填写类型",
options = PaperType.class,
// 写文件时,将数字转字符串
writeConverter = PaperTypeWrite.class,
// 读文件时,将字符串转数字
readConverter = PaperTypeRead.class)
private String type;
/**
* 解析
*/
@ExcelField(value = "解析",required = true,comment = "请填写解析")
private String explain;
}
实现相关的转换器
设备导出的下拉选择
public class PaperType implements Options{
@Override
public String[] get() {
return new String[]{"中国近代史", "中国古代史","世界古代史","世界近现代史"};
}
}
写文件时,将值进行转换(此处示例为将数值拼接为指定格式的字符串)
public class PaperTypeWrite implements WriteConverter {
@Override
public String convert(Object o) throws ExcelKitWriteConverterException {
String typeName = "";
String type = o.toString();
switch (type){
case "1" :
typeName = "中国近代史";
break;
case "2" :
typeName = "中国古代史";
break;
case "3" :
typeName = "世界古代史";
break;
case "4" :
typeName = "世界近现代史";
break;
default:
return "";
}
return typeName;
}
}
写文件时,将值进行转换(此处示例为将数值拼接为指定格式的字符串)
public class PaperTypeWrite implements WriteConverter {
@Override
public String convert(Object o) throws ExcelKitWriteConverterException {
String typeName = "";
String type = o.toString();
switch (type){
case "1" :
typeName = "中国近代史";
break;
case "2" :
typeName = "中国古代史";
break;
case "3" :
typeName = "世界古代史";
break;
case "4" :
typeName = "世界近现代史";
break;
default:
return "";
}
return typeName;
}
}
以上就可实现通过ExcelKit 导入/导出excel文件