vue2 数据导入excel
1、安装 npm install xlsx
一、前端
<el-upload
style="display: inline-block"
action
accept=".xlsx, .xls"
:auto-upload="false"
:show-file-list="false"
:on-change="handleUpload"
>
<el-button type="primary" icon="el-icon-upload2" round>导入</el-button>
</el-upload>
二、逻辑
npm install xlsx
<script>
/* eslint-disable */
import * as XLSX from "xlsx";
// 导入
handleUpload(ev){
let yearMonth =this.selectForm.yearMonth
// 如果有数据则给出提示
if(this.tableCount >0){
this.$confirm(yearMonth+'有数据，是否进行替换？','提示',{
confirmButtonText: '确定',
cancelButtonText: '取消',
type:'warning'
}).then(() =>{
const file = ev.raw
const fileReader = new FileReader();
fileReader.readAsArrayBuffer(file);
fileReader.onload = (ev) =>{
const data = new Uint8Array(ev.target.result);
const workbook = XLSX.read(data, { type: 'array' });
const firstSheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[firstSheetName];
// 转译范围
const range = { s: { r: 2, c: 3 }, e: { r: 32, c: 18 } }
this.importData =(XLSX.utils.sheet_to_json(worksheet,{ header: 1,defval:'',range: range}));
let formData={
list: JSON.parse(JSON.stringify(this.importData)),
yearMonth: this.selectForm.yearMonth
}
importData(formData).then((res) =>{
if(res.code == 200){
this.getDataList()
this.$publicmethod.showMessage("导入成功",this.$publicmethod.SuccessType)
}else{
this.$publicmethod.showMessage("导入失败-选择的模版不正确！",this.$publicmethod.ErrorType)
}
})
}
})
}else{
const file = ev.raw
const fileReader = new FileReader();
fileReader.readAsArrayBuffer(file);
fileReader.onload = (ev) =>{
const data = new Uint8Array(ev.target.result);
const workbook = XLSX.read(data, { type: 'array' });
const firstSheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[firstSheetName];
// 转译范围
const range = { s: { r: 2, c: 3 }, e: { r: 32, c: 18 } }
this.importData =(XLSX.utils.sheet_to_json(worksheet,{ header: 1,defval:'',range: range}));
let formData={
list: JSON.parse(JSON.stringify(this.importData)),
yearMonth: this.selectForm.yearMonth
}
importData(formData).then((res) =>{
if(res.code == 200){
this.getDataList()
this.$publicmethod.showMessage("导入成功",this.$publicmethod.SuccessType)
}else{
this.$publicmethod.showMessage("导入失败-选择的模版不正确！",this.$publicmethod.ErrorType)
}
})
}
}
},
</script>
三、接口
export function importData(data) {
return request({
url: 'api/operation_report/importData',
method: 'post',
data
})
}
四、后端实现
1、控制器类
@ApiOperation(value = "导入数据")
@PostMapping(value = "/importData")
public JsonBean importData( @RequestBody @Validated ReportQueryDo queryDo) {
List<String> list =queryDo.getList();
String yearMonth = queryDo.getYearMonth();
return operationReportService.importData(list,yearMonth);
}
2、实现类
// 导入
@Override
@Transactional(value = "MainTransactionManager", rollbackFor = Exception.class)
public JsonBean importData(List<String> list,String yearMonth) {
try{
List<OperationReport> addList = new ArrayList<>();
// 将 excel数据保存在数据库中,传过来的是字符串，将字符串转成对应的对象进行数据的添加
list.stream().forEach(data ->{
OperationReport report = new OperationReport();
report.setId(SnowIdUtil.getId());
report.setYearMonth(yearMonth);
JSONArray arrayData =JSON.parseArray(data);
// 将数据转换成list集合进行操作
if(arrayData !=null){
// 公司名称
String orgName = arrayData.getString(0);
int orgId=getOrgId(orgName);
// -1 代表没有当前组织
if(orgId != -1){
report.setOrgId(orgId);
}else{
String msg="不存在"+orgName+"公司";
logger.error(msg);
throw new RuntimeException(msg);
}
report = changeDataEntity(report,arrayData);
addList.add(report);
}
});
// 数据批量添加
if(CollectionUtils.isNotEmpty(addList)){
// 先删除数据再进行添加
Map<String,Object> map = new HashMap<>();
map.put("yearMonth",yearMonth);
operationReportMapper.delete(map);
operationReportMapper.batchInsert(addList);
}
return new JsonBean(ResultCode.SERVICE_OK);
}catch (Exception e){
logger.error(e.toString());
return new JsonBean(ResultCode.SERVICE_ERR);
}
}
// 将excel表格数据转成实体
private OperationReport changeDataEntity(OperationReport report,JSONArray arrayData){
// 人员总数
String personnelTotalCount = arrayData.getString(1);
if(StringUtils.isNotEmpty(personnelTotalCount)){
report.setPersonnelTotalCount(Integer.parseInt(personnelTotalCount));
}else{
report.setPersonnelTotalCount(0);
}
// 机关人员总数
String officialCount = arrayData.getString(2);
if(StringUtils.isNotEmpty(officialCount)){
report.setOfficialCount(Integer.parseInt(officialCount));
}else{
report.setOfficialCount(0);
}
// 一线业务人员
String frontLineBusinessCount = arrayData.getString(3);
if(StringUtils.isNotEmpty(frontLineBusinessCount)){
report.setFrontLineBusinessCount(Integer.parseInt(frontLineBusinessCount));
}else{
report.setFrontLineBusinessCount(0);
}
// 车辆总数
String vehicleCount = arrayData.getString(4);
if(StringUtils.isNotEmpty(vehicleCount)){
report.setVehicleCount(Integer.parseInt(vehicleCount));
}else{
report.setVehicleCount(0);
}
.....
return report;
}
3、根据公司名称获取对应的公司编号
public class ConstantEnum {
// 创建HashMap来存储城市公司名称和编号的映射
private static HashMap<String,Integer> orgMap = new HashMap<>();
// 静态代码块，初始化公司编号数据
static{
orgMap.put("A",00001);
orgMap.put("B",00002);
}
// 根据公司名称获取公司编号的方法
public static int getOrgId(String orgName){
return orgMap.containsKey(orgName)?orgMap.get(orgName):-1;
}
}
4、批量添加xml
<!--批量添加 -->
<insert id="batchInsert" parameterType="java.util.List">
insert into operation_report (id,org_id,yearMonth,personnel_total_count) values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.id},#{item.orgId},#{item.yearMonth},#{item.personnelTotalCount})
</foreach>
</insert>
五、导入的文件模版（行、列数据，只是转译内容，前面的数据不需要）
