Controller
/**
* 导出填报配置列表
*/
@PostMapping("/importData")
public R importData(@RequestPart("file") MultipartFile file, @NotNull(message = "报表ID不可为空") Integer id) throws Exception {
try {
// 查询表的列配置
List<TbFieldsConfig> headers = iTbConfigService.getFieldsConfigList(id);
// 解析excel
TbTableDataImportListener listener = new TbTableDataImportListener(headers);
EasyExcel.read(file.getInputStream(), listener).sheet().doRead();
List<JSONObject> dataList = listener.getDataList();
// 插入数据
iTbConfigService.insertDataList(dataList, id);
return R.ok("导入成功");
} catch (IOException e) {
return R.ok("导入失败：" + e.getMessage());
}
}
Listener
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONObject;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.zhby.common.exception.ServiceException;
import com.zhby.tb.domain.TbFieldsConfig;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
@Slf4j
public class TbTableDataImportListener extends AnalysisEventListener<Map<Integer, Object>> {
private Map<String, String> headerConfig = null;
private Map<Integer, String> headerIndexToDbField = new LinkedHashMap<>(); // 列索引到数据库字段名的映射
private final List<TbFieldsConfig> headers;
private final List<JSONObject> dataList = new ArrayList<>();
public TbTableDataImportListener( List<TbFieldsConfig> headers) {
this.headers = headers;
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
if (null == headers || headers.size() <= 0) {
throw new ServiceException("没有配置报表的字段配置信！！！");
}
// 获取表头配置
headerConfig = new LinkedHashMap<>();
for (Map.Entry<Integer, String> entry : headMap.entrySet()) {
String chineseHeader = entry.getValue(); // 假设这是中文表头
if (StrUtil.isNotBlank(chineseHeader)) { // 只保留有效的列
String dbFieldName = getDbFieldName(chineseHeader); // 从中文表头获取数据库字段名
if (StrUtil.isNotBlank(dbFieldName)) {
// 只保留有效的列
headerConfig.put(dbFieldName, chineseHeader);
headerIndexToDbField.put(entry.getKey(), dbFieldName);
}
}
}
}
private String getDbFieldName(String chineseHeader) {
String key = null;
for (int i = 0; i < headers.size(); i++) {
if (chineseHeader.equals(headers.get(i).getFieldName())) {
key = headers.get(i).getFieldCode();
}
}
return key;
}
@Override
public void invoke(Map<Integer, Object> object, AnalysisContext context) {
JSONObject row = new JSONObject();
for (Map.Entry<Integer, String> entry : headerIndexToDbField.entrySet()) {
Integer columnIndex = entry.getKey();
String dbFieldName = entry.getValue();
Object cellValue = object.get(columnIndex);
row.put(dbFieldName, cellValue);
}
dataList.add(row);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
public List<JSONObject> getDataList() {
return dataList;
}
}
Service
@Transactional(rollbackFor = Exception.class)
@Override
public int insertDataList(List<JSONObject> datas, Integer id) {
// 根据表名查询所有指标表的字段
List<TbFieldsConfig> fields = getFieldsConfigList(id);
List<Map> dataMaps = new ArrayList<>();
//把数据转换成code的map
格式
datas.forEach(data -> {
Map<String, Object> dataMap = new HashMap<>();
for (TbFieldsConfig field : fields) {
dataMap.put( field.getFieldCode(), data.get(field.getFieldCode()));
}
Map<String, Object> params = new HashMap<>();
params.put("tableName", id);
params.put("columnMap", dataMap);
dataMaps.add(params);
});
dataMaps.forEach(params -> {
try {
// 插入数据
baseMapper.insertByMap(params);
} catch (Exception e) {
throw new RuntimeException(e);
}
});
return datas.size();
}
Mapper
<insert id="insertByMap" parameterType="java.util.Map" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
INSERT INTO ${tableName} (
<foreach collection="columnMap" item="value" index="key" separator=",">
${key}
</foreach>
)
values (
<foreach collection="columnMap" item="value" index="key" separator=",">
#{value}
</foreach>
)
</insert>
Vue
<el-button type="warning" plain icon="Top" @click="handleImport">导入 </el-button>
<el-dialog :title="upload.title" v-model="upload.open" width="400px" :append-to-body="true">
<el-upload
ref="uploadRef"
:limit="1"
accept=".xlsx, .xls"
:headers="upload.headers"
:action="upload.url+'?id='+upload.id"
:on-progress="handleFileUploadProgress"
:on-success="handleFileSuccess"
:auto-upload="false"
drag
>
<el-icon class="el-icon--upload">
<i-ep-upload-filled/>
</el-icon>
<div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
<template #tip>
<div class="text-center el-upload__tip">
<div class="el-upload__tip">
是否更新已经存在的指标数据
</div>
<span>仅允许导入xls、xlsx格式文件。</span>
</div>
</template>
</el-upload>
<template #footer>
<div class="dialog-footer">
<el-button type="primary" @click="submitFileForm">确 定</el-button>
<el-button @click="upload.open = false">取 消</el-button>
</div>
</template>
</el-dialog>
Js
const uploadRef = ref();
/*** 用户导入参数 */
const upload = ref({
// 是否显示弹出层
open: false,
// 弹出层标题
title: "",
// 设置上传的请求头部
headers: globalHeaders(),
id: "",
// 上传的地址
url: import.meta.env.VITE_APP_BASE_API + "/tb/config/importData"
});
/** 导入按钮操作 */
const handleImport = () => {
upload.value.title = checkbb.value.name + "报表数据导入";
upload.value.id = checkbb.value.id;
upload.value.open = true;
};
/**文件上传中处理 */
const handleFileUploadProgress = () => {
upload.value.isUploading = true;
};
/** 文件上传成功处理 */
const handleFileSuccess = (response, file) => {
upload.value.open = false;
upload.value.isUploading = false;
uploadRef.value?.handleRemove(file);
proxy.$modal.msgSuccess(response.msg);
handleSearch();
};
/** 提交上传文件 */
function submitFileForm() {
uploadRef.value?.submit();
}
网友评论