利用@Excel实现复杂表头导入

EasyPoi导入

<a-upload name="file" :showUploadList="false" :multiple="false" :headers="tokenHeader" :action="importExcelUrl"
          @change="handleImportExcel">
  <a-button type="primary" icon="import">导入数据</a-button>
</a-upload>

data() {
    return {
      url: {
        importExcelUrl: 'cs/csBalanceAgent/importExcel'
      }
    }
  },
methods: {
    importExcelUrl: function() {
      return `${window._CONFIG['domianURL']}/${this.url.importExcelUrl}`
    },
    /* 导入 */
    handleImportExcel(info) {
      console.log('info-----------------', info)
      this.loading = true
      if (info.file.status !== 'uploading') {
        console.log('file==================', info.file, info.fileList)
      }
      if (info.file.status === 'done') {
        this.loading = false
        if (info.file.response.success) {
          // this.$message.success(`${info.file.name} 文件上传成功`);
          if (info.file.response.code === 201) {
            let { message, result: { msg, fileUrl, fileName } } = info.file.response
            let href = window._CONFIG['domianURL'] + fileUrl
            this.$warning({
              title: message,
              content: (<div>
                  <span>{msg}</span><br />
                  <span>具体详情请 <a href={href} target="_blank" download={fileName}>点击下载</a> </span>
                </div>
              )
            })
          } else {
            this.$message.success(info.file.response.message || `${info.file.name} 文件上传成功`)
          }
          // this.loadData()
          this.$refs.tablePage.refreshTable()
        } else {
          this.$message.error(`${info.file.name} ${info.file.response.message}.`)
        }
      } else if (info.file.status === 'error') {
        this.loading = false
        if (info.file.response.status === 500) {
          let data = info.file.response
          const token = Vue.ls.get(ACCESS_TOKEN)
          if (token && data.message.includes('Token失效')) {
            this.$error({
              title: '登录已过期',
              content: '很抱歉，登录已过期，请重新登录',
              okText: '重新登录',
              mask: false,
              onOk: () => {
                store.dispatch('Logout').then(() => {
                  Vue.ls.remove(ACCESS_TOKEN)
                  window.location.reload()
                })
              }
            })
          }
        } else {
          this.$message.error(`文件上传失败: ${info.file.msg} `)
        }
      }
    },
}	

Controller

/**
 * 通过excel导入数据
 *
 * @return
 */
@RequestMapping(value = "/importExcel", method = RequestMethod.POST)
public Result<?> importExcel(@RequestParam("file") MultipartFile file) {
 return csBalanceAgentService.importExcelData(file);
}

实现类

@Override
public Result<?> importExcelData(MultipartFile file) {
    List<CsBalanceAgent> list = null;

    ImportParams importParams = new ImportParams();
    importParams.setTitleRows(1);
    importParams.setHeadRows(2);

    try {
        list = ExcelImportUtil.importExcel(file.getInputStream(), CsBalanceAgent.class, importParams);
    } catch (Exception e) {
        e.printStackTrace();
    }

    if (list == null) {
        return Result.error("没有有效的数据,导入数据失败");
    }

    this.saveBatch(list);
    return Result.OK("导入成功");
}

实体类处理

复杂表头导入

• 通过needMerge属性实现表头合并多行

• 再开始分组的第一列属性添加groupName属性即可解决导入为空的现象

实体类

@Data
@TableName("cs_balance_agent")

@ApiModel(value = "cs_balance_agent对象", description="平衡剂")
public class CsBalanceAgent {
    
   /**主键*/
   @TableId(type = IdType.ASSIGN_ID)
    @ApiModelProperty(value = "主键")
   private String id;
   /**公司名称*/
   @Excel(name = "公司名称", width = 35, needMerge = true)
    @ApiModelProperty(value = "公司名称")
   private String companyName;
   /**装置*/
   @Excel(name = "装置", width = 35, needMerge = true)
    @ApiModelProperty(value = "装置")
   private String device;
   /**样品类型*/
   @Excel(name = "样品类型", width = 35, needMerge = true)
    @ApiModelProperty(value = "样品类型")
   private String sampleType;
   /**样品编号*/
   @Excel(name = "样品编号", width = 35, needMerge = true)
    @ApiModelProperty(value = "样品编号")
   private String sampleNum;
   /**采样日期*/
   @Excel(name = "采样日期", width = 20, format = "yyyy-MM-dd", needMerge = true)
    @ApiModelProperty(value = "采样日期")
   private Date sampleDate;
   /**分析日期*/
   @Excel(name = "分析日期", width = 20, format = "yyyy-MM-dd", needMerge = true)
    @ApiModelProperty(value = "分析日期")
   private Date analyseDate;
   /**报送时间*/
   @Excel(name = "报送时间", width = 20, format = "yyyy-MM-dd", needMerge = true)
    @ApiModelProperty(value = "报送时间")
   private Date submitTime;
   /**比表面积*/
   @Excel(name = "比表面积(m2/g)", width = 35, groupName = "物理性质")
    @ApiModelProperty(value = "比表面积(m2/g)")
   private Integer bSurfaceArea;
   /**孔体积*/
   @Excel(name = "孔体积 (ml/g)", width = 35)
    @ApiModelProperty(value = "孔体积 (ml/g)")
   private BigDecimal holeHole;
   /**表观密度*/
   @Excel(name = "表观密度 (g/ml)", width = 35)
    @ApiModelProperty(value = "表观密度 (g/ml)")
   private BigDecimal surfaceDensity;
   /**充气密度*/
   @Excel(name = "充气密度(g/mL)", width = 35)
    @ApiModelProperty(value = "充气密度(g/mL)")
   private BigDecimal aerateDensity;
   /**压实密度*/
   @Excel(name = "压实密度(g/mL)", width = 35)
    @ApiModelProperty(value = "压实密度(g/mL)")
   private BigDecimal compactDensity;
   /**沉降密度*/
   @Excel(name = "沉降密度(g/mL)", width = 35)
    @ApiModelProperty(value = "沉降密度(g/mL)")
   private BigDecimal settleDensity;
   /**APS*/
   @Excel(name = "APS(μm)", width = 35)
    @ApiModelProperty(value = "APS(μm)")
   private BigDecimal aps;
   /**0-20μm*/
   @Excel(name = "0-20μm(%(v/v)）", width = 35)
    @ApiModelProperty(value = "0-20μm(%(v/v)）")
   private BigDecimal zeroToTwenty;
   /**20-40μm*/
   @Excel(name = "20-40μm（%(v/v)）", width = 35)
    @ApiModelProperty(value = "20-40μm（%(v/v)）")
   private BigDecimal twentyToForty;
   /**40-60μm*/
   @Excel(name = "40-60μm（%(v/v)）", width = 35)
    @ApiModelProperty(value = "40-60μm（%(v/v)）")
   private BigDecimal fortySixty;
   /**60-80μm*/
   @Excel(name = "60-80μm（%(v/v)）", width = 35)
    @ApiModelProperty(value = "60-80μm（%(v/v)）")
   private BigDecimal sixtyEighty;
   /**80-110μm*/
   @Excel(name = "80-110μm（%(v/v)）", width = 35)
    @ApiModelProperty(value = "80-110μm（%(v/v)）")
   private BigDecimal eightyHundredTen;
   /**>111μm*/
   @Excel(name = ">111μm（%(v/v)）", width = 35)
    @ApiModelProperty(value = ">111μm（%(v/v)）")
   @TableField(value = "g_oneoneone")
   private BigDecimal goneoneone;
   /**RE2O3*/
   @Excel(name = "RE2O3（μg/g）", width = 35, groupName = "化学性质")
    @ApiModelProperty(value = "RE2O3（μg/g）")
   private Integer retwoo3;
   /**AL2O3*/
   @Excel(name = "AL2O3（μg/g）", width = 35)
    @ApiModelProperty(value = "AL2O3（μg/g）")
   private Integer altwoothree;
   /**NA2O*/
   @Excel(name = "NA2O（μg/g）", width = 35)
    @ApiModelProperty(value = "NA2O（μg/g）")
   private Integer natwoo;
   /**Ca*/
   @Excel(name = "Ca（μg/g）", width = 35)
    @ApiModelProperty(value = "Ca（μg/g）")
   private Integer ca;
   /**Cu*/
   @Excel(name = "Cu（μg/g）", width = 35)
    @ApiModelProperty(value = "Cu（μg/g）")
   private Integer cu;
   /**Fe*/
   @Excel(name = "Fe（μg/g）", width = 35)
    @ApiModelProperty(value = "Fe（μg/g）")
   private Integer fe;
   /**Na*/
   @Excel(name = "Na（μg/g）", width = 35)
    @ApiModelProperty(value = "Na（μg/g）")
   private Integer na;
   /**Ni*/
   @Excel(name = "Ni（μg/g）", width = 35)
    @ApiModelProperty(value = "Ni（μg/g）")
   private Integer ni;
   /**Sb*/
   @Excel(name = "Sb（μg/g）", width = 35)
    @ApiModelProperty(value = "Sb（μg/g）")
   private Integer sb;
   /**V*/
   @Excel(name = "V（μg/g）", width = 35)
    @ApiModelProperty(value = "V（μg/g）")
   private Integer v;
   /**C*/
   @Excel(name = "C（μg/g）", width = 35)
    @ApiModelProperty(value = "C（μg/g）")
   private BigDecimal c;
   /**微活*/
   @Excel(name = "微活（%）", width = 35)
    @ApiModelProperty(value = "微活（%）")
   private Integer microactivity;
   /**创建人*/
   @ExcelIgnore
    @ApiModelProperty(value = "创建人")
   private String createBy;
   /**创建时间*/
   @ExcelIgnore
    @ApiModelProperty(value = "创建时间")
   private Date createTime;
   /**修改人*/
   @ExcelIgnore
    @ApiModelProperty(value = "修改人")
   private String updateBy;
   /**修改时间*/
   @ExcelIgnore
    @ApiModelProperty(value = "修改时间")
   private Date updateTime;
}

@excel中都有哪些属见 https://blog.csdn.net/m0_63686648/article/details/126429863