动态配置导入excel

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();
}

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容