增加Maven坐标:
<!-- excel导入 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
修改VUE请求头:
postUpload(url, data) {
//data.append('ticket', user(null))
return new Promise((resolve, reject) => {
axiosUpload
.post(url, data, { headers: { 'Content-Type': 'multipart/form-data' } })
.then(
res => {
if (res.res === 'failed') {
if (res.code === 'encrypt') {
// 转到无权限页
// console.log('触发无权限')
_this.$router.push('/encrypt')
} else if (res.code === 'no-encrypt') {
// console.log('触发重新登录')
// 转到登录页(redirect)
MessageBox.alert('登录信息已失效,请重新登录!', '提示', {
confirmButtonText: '确定',
// cancelButtonText: '',
type: 'warning'
})
.then(() => {
_this.$router.push('/login')
})
.catch(() => {
_this.$router.push('/login')
})
} else {
resolve(res)
}
} else {
resolve(res)
}
},
err => {
reject(err)
}
)
})
},
export default {
post(url, data) {
console.log('post request url', url)
return service({
method: 'post',
url,
params: data
})
},
或者用
upLoadFile(url, data) {
console.log('upLoad request url', rul)
return axiosx.post(url, data, {
headers: { 'Content-Type': 'multipart/form-data' }
})
}
JAVA:
@ApiOperation(value = "导入国家字典信息", notes = "导入国家字典信息")
@ApiImplicitParam(name = "ticket", value = "用户标识", required = true, dataType = "String", paramType = "form")
@PostMapping(value = "/importCountyExcelFile" )
public void importCountyExcelFile(HttpServletRequest request, HttpServletResponse response,
@ApiParam(value="文件",required=true) @RequestPart MultipartFile file) {
ServletCom com = new ServletCom(request, response);
List<Map<String,String>> resultList =new ArrayList<Map<String,String>>();
List<Map<String,String>> tempList =new ArrayList<Map<String,String>>();
Map<String,Map<String,String>> codeMap =new HashMap<String,Map<String,String>>();
Map<String,Map<String,String>> nameMap =new HashMap<String,Map<String,String>>();
Map<String,Map<String,String>> eNameMap =new HashMap<String,Map<String,String>>();
Map<String,Object> resultInfo = Maps.newHashMap();
try{
InputStream orgExcelFileIS =file.getInputStream();
Workbook hssfWorkbook = WorkbookFactory.create(orgExcelFileIS);//new XSSFWorkbook(orgExcelFileIS);
Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
int count =0;
// 循环取出excel 每行数据,进行验证处理
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
int flag = 0;
Row hssfRow = hssfSheet.getRow(rowNum);
Map<String,String> m = Maps.newHashMap();
// 编码
Cell countryCode = hssfRow.getCell(0);
String code = getCellValue(countryCode);
if (!StringUtils.isEmpty(code)) {
m.put("code",code);
flag++;
count++;
}
// 国家名称
Cell countryName = hssfRow.getCell(1);
String name = getCellValue(countryName);
if (!StringUtils.isEmpty(name)) {
m.put("name",name);
flag++;
}
//英文名
Cell eNmae = hssfRow.getCell(2);
String eName = getCellValue(eNmae);
if (!StringUtils.isEmpty(eName)) {
m.put("eName",eName);
flag++;
}
m.put("rowNum",String.valueOf(rowNum+1));
if(flag==3){
tempList.add(m);
codeMap.put(m.get("code"),m);
nameMap.put(m.get("name"),m);
eNameMap.put(m.get("eName"),m);
}
}
// 准备做数据校验
StringBuilder errorMsg = new StringBuilder();
List<String> countyCodeList = new ArrayList<String>();
for(Map<String,String>m:tempList){
countyCodeList.add(m.get("code"));
}
if(countyCodeList.size()<=0) {
resultInfo.put("code","00000");
resultInfo.put("msgError","没有可上传的数据");
com.renderJson(resultInfo);
return ;
}
if(count > 301) {
resultInfo.put("code","00000");
resultInfo.put("msgError","单次最多只能上传300条数据");
com.renderJson(resultInfo);
return ;
}
// 验证编码是否重复
// 由于数据量特别小,不超过200条,所以全表撸了,减少多次根据条件操作数据库,并且减少写循环
List<Map<String,String>> dataAll = iCountryMsgService.findByAll();
if(dataAll!=null){
for(Map<String,String> m:dataAll){
if(codeMap.get(m.get("code"))!=null) {
errorMsg.append("第"+codeMap.get(m.get("code")).get("rowNum")+"行,国家编码重复.");
}
if(nameMap.get(m.get("name"))!=null) {
errorMsg.append("第"+nameMap.get(m.get("name")).get("rowNum")+"行,国家名称重复.");
}
if(eNameMap.get(m.get("eName"))!=null) {
errorMsg.append("第"+eNameMap.get(m.get("eName")).get("rowNum")+"行,英文名称重复.");
}
}
if(errorMsg.length()>0){
resultInfo.put("code","00001");
resultInfo.put("errorMsg",errorMsg.toString());
com.renderJson(resultInfo);
return;
}
}
// 正则校验格式
// 编码
Pattern p1 = Pattern.compile("^((?!_)(?!.*?_$)[a-zA-Z0-9_]|_(?!_){1,10}$");
// 中文最多输入十个字
Pattern p2 = Pattern.compile("^[\\u4e00-\\u9fa5]{1,10}$");
// 英文
Pattern p3 = Pattern.compile("^[a-zA-Z]{1,20}$");
for(Map<String,String> m:tempList) {
int a = 0;
if(!p1.matcher(m.get("code")).matches()){
errorMsg.append("第"+m.get("rowNum")+"行,国家编码格式或长度不正确!");
a++;
}
if(!p2.matcher(m.get("name")).matches()){
errorMsg.append("第"+m.get("rowNum")+"行,国家名称格式或长度不正确!");
a++;
}
if(!p3.matcher(m.get("eName")).matches()){
errorMsg.append("第"+m.get("rowNum")+"行,英文名称格式或长度不正确!");
a++;
}
if(a==0) {
String id = iSequenceService.generate19(Constants.CENTER_ID);
m.put("id", id);
resultList.add(m);
}
}
if(errorMsg.length()>0){
resultInfo.put("code","00001");
resultInfo.put("errorMsg",errorMsg.toString());
}else{
int a = iCountryMsgService.insertBatchInsert(resultList);
resultInfo.put("data",a);
resultInfo.put("length",resultList.size());
resultInfo.put(Constants.RESULT, Constants.SUCCESS);
resultInfo.put(Cons.RESULT_MSG, Constants.B0001);
}
}catch (Exception e){
e.printStackTrace();
resultInfo.put(Constants.RESULT, Cons.FAILED);
resultInfo.put(Cons.RESULT_MSG, Constants.S0005);
}
com.renderJson(resultInfo);
}
@SuppressWarnings("deprecation")
private String getCellValue(Cell cell) {
String result = "";
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
if (!StringUtils.isEmpty(cell.getStringCellValue())) {
result = cell.getStringCellValue();
}
break;
case Cell.CELL_TYPE_NUMERIC:
result = String.valueOf((int) cell.getNumericCellValue());
}
}
return result;
}
VUE:
<!--
@author wanghao
@modifydate 2019年8月09日 09:51:14s
@description 国家字典
-->
<!--template name: demoQuery-->
<template>
<div>
<el-card >
<div slot="header">
<span>{{ $t('国家管理') }}</span>
<div class="is-pulled-right">
<div class="is-pulled-left">
<el-upload
:multiple="false"
:auto-upload="true"
:before-upload="beforeUpload"
:limit="1"
:on-exceed="handleExceed"
:http-request="uploadFile"
accept=".xls,.xlsx"
action="" >
<el-button
size="small"
type="primary"
>导入全架Excel文件
<i class="el-icon-upload"/></el-button>
</el-upload>
</div>
</div>
<div class="is-pulled-right">
<el-button
size="small"
type="primary"
@click="handleAdd">
{{ $t('新增') }}
</el-button>
</div>
</div>
<div class="columns columns-table">
<div class="column">
<div class="columns">
<div class="column is-full">
<el-table
:key="tableKey"
:data="tables"
stripe
fit
border>
<el-table-column
:label="$t('序号')"
type="index"
align="center"
prop="val"/>
<el-table-column
:label="$t('编码')"
align="center"
prop="code"/>
<el-table-column
:label="$t('国家')"
align="center"
prop="name"/>
<el-table-column
:label="$t('英文名')"
align="center"
prop="eName"/>
<el-table-column
:label="$t('操作')"
align="center"
fixed ="right">
<template slot-scope="scope">
<el-button
size="mini"
type="primary"
plain
@click="handleEdit(scope.row)"> {{ $t('编辑') }} </el-button>
</template>
</el-table-column>
</el-table>
<!-- 分页信息
<div class="pagination-container">
<el-pagination
:current-page="PagingAttributes.currentPage"
:page-sizes="PagingAttributes.sizes"
:page-size="PagingAttributes.size"
:total="total"
:background="PagingAttributes.background"
:layout="PagingAttributes.layout"
@size-change="handleSizeChange"
@current-change="handleCurrentChange"/>
</div> -->
</div>
</div>
</div>
</div>
<el-dialog
:visible.sync="dialogVisible"
:title="title"
width="60%">
<div class="columns">
<div class="column">
<el-form
ref="formDiaLog"
:model="dialogForm"
:rules="rules"
size="mini"
label-width="120px">
<div class="clearfix">
<div class="is-clearfix clearfixs-max4-2">
<el-form-item
:label="$t('国家编码')"
prop="code">
<el-input
v-model="dialogForm.code"
maxlength="10"
/>
</el-form-item></div>
<div class="is-clearfix clearfixs-max4-2">
<el-form-item
:label="$t('国家名称')"
prop="name">
<el-input
v-model="dialogForm.name"
maxlength="10"
/>
</el-form-item></div>
<div class="is-clearfix clearfixs-max4-2">
<el-form-item
:label="$t('英文简称')"
prop="eName">
<el-input
v-model="dialogForm.eName"
maxlength="20"
/>
</el-form-item></div>
<div class="columns jl-footer-btn">
<div class="column">
<div class="clearfix">
<div align="center">
<el-button
size="small"
@click="dialogVisible = false">{{ $t('取消') }}</el-button>
<el-button
:disabled="creatSign"
type="primary"
size="small"
@click="save('dialogForm')">{{ $t('保存') }}</el-button>
</div>
</div>
</div>
</div>
</div>
</el-form>
</div>
</div>
</el-dialog>
</el-card>
</div>
</template>
<script>
import { mapGetters, mapActions } from 'vuex'
import ruler from '~/utils/ruler'
export default {
name: 'CountryMsg',
filters: {},
data() {
return {
allExcalMsg: '', // Excal表中所有的数据
useExcalMsg: false, // 使用excal的数据
excalMsg: [], // excal导入的数据
dialogForm: {},
creatSign: false,
rules: {},
tables: [],
dialogVisible: false,
title: '',
PagingAttributes: {},
tableKey: 0
}
},
computed: {
...mapGetters('orgCen/dataDict', [])
},
created() {
this.searchList()
this.rules = {
code: [
ruler.required(this.$t('国家编码不能为空!'), 'blur'),
ruler.code(this.$t('国家编码格式不正确!'), 'blur'),
ruler.validator((rule, value, callback) => {
this.checkCountryRule({
code: this.dialogForm.code,
id: this.dialogForm.id
}).then(res => {
if (res.res === 'success') {
callback()
} else {
callback(new Error(this.$t('国家编码已存在,请重新输入!')))
}
})
})
],
name: [
ruler.required(this.$t('国家名称不能为空!'), 'blur'),
ruler.chineseVerify(this.$t('国家名称格式不正确!'), 'blur'),
ruler.validator((rule, value, callback) => {
this.checkCountryRule({
name: this.dialogForm.name,
id: this.dialogForm.id
}).then(res => {
if (res.res === 'success') {
callback()
} else {
callback(new Error(this.$t('国家名称已存在,请重新输入!')))
}
})
})
],
eName: [
ruler.required(this.$t('英文简称不能为空!'), 'blur'),
ruler.englishVerify(this.$t('英文简称格式不正确!'), 'blur'),
ruler.validator((rule, value, callback) => {
this.checkCountryRule({
eName: this.dialogForm.eName,
id: this.dialogForm.id
}).then(res => {
if (res.res === 'success') {
callback()
} else {
callback(new Error(this.$t('英文简称已存在,请重新输入!')))
}
})
})
]
}
},
methods: {
...mapActions('modules/commCenter/commConfig/countryMsg/countryMsg', [
'searchCountryList',
'checkCountryRule',
'importCountyExcelFile',
'saveCountry',
'getCountryDetail'
]),
searchList() {
this.creatSign = false
this.searchCountryList()
.then(res => {
if (res.res === 'success') {
this.tables = res.data.data
} else {
this.$message.error(res.msg)
}
})
.catch(e => {
this.$message.error(this.$t('orderCenMsg.catchError'))
})
},
// 上传文件之前的钩子
beforeUpload(file) {},
// 上传文件个数超过定义的数量
handleExceed(files, fileList) {
this.$message.error(`当前限制选择 1 个文件,请删除后继续上传`)
},
// 上传文件
uploadFile(item) {
const fileObj = item.file
// FormData 对象
let form = new FormData()
// 文件对象
form.append('file', fileObj)
form.append('shelfId', this.shelfId)
this.$confirm(
'导入Excel将会全部替换已存在的商品列表,确认继续么?',
'提示',
{
confirmButtonText: '确定',
cancelButtonText: '取消',
type: 'warning'
}
)
.then(() => {
// 确定上传
this.importCountyExcelFile(form)
.then(res => {
if (res.data.code === '00000') {
this.$message({
type: 'error',
message: 'Excal中的数据不可超过300条'
})
return
} else if (res.data.code === '00001') {
this.$message({
type: 'error',
message: res.data.errorMsg
})
return
}
this.searchList()
this.$message({
type: 'success',
message: '导入成功'
})
})
.catch(res => {
this.$message({
type: 'error',
message: '导入失败'
})
})
})
.catch(() => {
this.$message({
type: 'error',
message: '已取消操作'
})
return false
})
},
save() {
this.$refs['formDiaLog'].validate(res => {
if (res) {
this.creatSign = true
this.saveCountry(this.dialogForm)
.then(res => {
if (res.res === 'success') {
this.searchList()
this.dialogVisible = false
} else {
this.$message.error(res.msg)
}
})
.catch(e => {
this.dialogVisible = false
this.$message.error(this.$t('orderCenMsg.catchError'))
})
}
})
},
handleAdd() {
this.dialogForm = {}
this.title = this.$t('新增')
this.dialogVisible = true
this.$nextTick(() => {
if (this.$refs.formDiaLog) {
this.$refs.formDiaLog.clearValidate()
}
})
},
handleEdit(row) {
this.$nextTick(() => {
if (this.$refs.formDiaLog) {
this.$refs.formDiaLog.clearValidate()
}
})
this.dialogForm = {}
this.getCountryDetail({ id: row.id })
.then(res => {
if (res.res === 'success') {
this.dialogForm = res.data
} else {
this.$message.error(res.msg)
}
})
.catch(e => {
this.$message.error(this.$t('orderCenMsg.catchError'))
})
this.title = this.$t('编辑')
this.dialogVisible = true
}
}
}
</script>