Excel导入导出后端实现

一：//Controller中增加对应的接口
/**
    * 导入厂商
    */
   @PostMapping("import-supplier")
   @ApiOperationSupport(order = 10)
   @ApiOperation(value = "导入厂商", notes = "传入excel")
   public R importSupplier(MultipartFile file, Integer isCovered) {
      System.out.println("导入参数isCovered：");
      System.out.println(isCovered);
      SupplierImporter supplierImporter = new SupplierImporter(supplierService, isCovered == 1);
      ExcelUtil.save(file, supplierImporter, SupplierExcel.class);
      return R.success("操作成功");
   }

   /**
    * 导出厂商
    */
   @GetMapping("export-supplier")
   @ApiOperationSupport(order = 11)
   public void exportSupplier(@ApiIgnore @RequestParam Map<String, Object> supplier, BladeUser bladeUser, HttpServletResponse response) {
      QueryWrapper<SupplierEntity> queryWrapper = Condition.getQueryWrapper(supplier, SupplierEntity.class);
      if (!AuthUtil.isAdministrator()){
         queryWrapper.lambda().eq(SupplierEntity::getTenantId, bladeUser.getTenantId());
      }
      queryWrapper.lambda().eq(SupplierEntity::getIsDeleted, BladeConstant.DB_NOT_DELETED);
      List<SupplierExcel> list = supplierService.exportSupplier(queryWrapper);
      ExcelUtil.export(response, "厂商数据" + DateUtil.time(), "厂商数据表", list, SupplierExcel.class);
   }

   /**
    * 导出厂商
    */
   @GetMapping("export-template")
   @ApiOperationSupport(order = 12)
   @ApiOperation(value = "导出模板")
   public void exportSupplier(HttpServletResponse response) {
      List<SupplierExcel> list = new ArrayList<>();
      ExcelUtil.export(response, "厂商数据模板", "厂商数据表", list, SupplierExcel.class);
   }
二：

//增加excel包
//在包下创建文件SupplierImporter，内容如下：
package org.springblade.basic.excel;

import lombok.RequiredArgsConstructor;
import org.springblade.basic.service.ISupplierService;
import org.springblade.core.excel.support.ExcelImporter;

import java.util.List;

@RequiredArgsConstructor
public class SupplierImporter implements ExcelImporter<SupplierExcel> {
private final ISupplierService supplierService;
private final Boolean isCovered;

@Override
public void save(List<EmployeeExcel> data) {supplierService.importSupplier(data, isCovered);
}
}

 

//增加导出模板配置文件
package org.springblade.basic.excel;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;

import java.io.Serializable;

@Data
@ColumnWidth(25)
@HeadRowHeight(20)
@ContentRowHeight(18)
public class SupplierExcel implements Serializable {
private static final long serialVersionUID = 1L;

@ColumnWidth(15)
@ExcelProperty("厂商编码")
private String code;

@ColumnWidth(15)
@ExcelProperty("厂商名称")
private String supplier;

@ExcelIgnore
@ExcelProperty("所属机构Id")
private Long deptId;

@ExcelProperty("所属机构")
private String dept;

@ColumnWidth(15)
@ExcelProperty("身份证号")
private String idcard;

@ColumnWidth(15)
@ExcelProperty("手机")
private String phone;

@ColumnWidth(15)
@ExcelProperty("暂停标志")
private Integer stopFlag;

@ColumnWidth(15)
@ExcelProperty("使用标志")
private Integer userFlag;

@ColumnWidth(15)
@ExcelProperty("备注")
private String memo;
}

 

//SupplierMapper文件中增加如下方法：

List<SupplierExcel> exportSupplier(@Param("ew") Wrapper<SupplierEntity> queryWrapper);

//对应的XML中增加
<select id="exportSupplier" resultType="org.springblade.basic.excel.SupplierExcel">
SELECT id, tenant_id,code,supplier,dept_id
idcard,stop_flag,
user_flag,memo,create_user,create_time,create_dept,update_user,
update_time,status,is_deleted
FROM acc_supplier ${ew.customSqlSegment}
</select>

 

三：

//接口增加如下接口：
/**
* 新增厂商
*
* @param SupplierEntity
* @return
*/
boolean submit(SupplierEntity supplierEntity);

/**
* 更新厂商
*
* @param supplierEntity
* @return
*/
boolean updateEmployee(SupplierEntity supplierEntity);
/**
* 导入厂商数据
*
* @param data SupplierExcel
* @return
*/
void importEmployee(List<SupplierExcel> data, Boolean isCovered);

/**
* 获取导厂商数据
*
* @param queryWrapper
* @return
*/
List<SupplierExcel> exportSupplier(Wrapper<SupplierEntity> queryWrapper);

 

四：

//实现接口方法：

private IDeptService deptService;

@Override
@Transactional(rollbackFor = Exception.class)
public boolean submit(SupplierEntity supplierEntity) {
if (StringUtil.isBlank(supplierEntity.getTenantId())) {
String tenantId = SecureUtil.getTenantId();
supplierEntity.setTenantId(tenantId);
}
String tenantId = supplierEntity.getTenantId();
Tenant tenant = SysCache.getTenant(tenantId);

Integer supplierCount = baseMapper.selectCount(Wrappers.<SupplierEntity>query().lambda().eq(SupplierEntity::getTenantId, tenantId).eq(SupplierEntity::getEmployee, supplierEntity.getEmployee()));
if (supplierCount > 0 && Func.isEmpty(supplierEntity.getId())) {
throw new ServiceException(StringUtil.format("当前厂商 [{}] 已存在!", supplierEntity.getEmployee()));
}
return save(supplierEntity);
}
@Override
@Transactional(rollbackFor = Exception.class)
public void importEmployee(List<SupplierExcel> data, Boolean isCovered) {
//BranchOfficeEntity branchOfficeEntity =new BranchOfficeEntity();
Dept dept=new Dept();
data.forEach(supplierExcel -> {
SupplierEntity supplierEntity = Objects.requireNonNull(BeanUtil.copy(supplierExcel,SupplierEntity.class));
dept.setDeptName(supplierExcel.getDept());
Dept detail =deptService.getOne(Condition.getQueryWrapper(dept));
supplierEntity.setDeptId(detail.getId());
// 覆盖数据
if (isCovered) {
// 查询员工是否存在
// String tenantId = supplierEntity.getTenantId();//无法获取租户ID
String tenantId = SecureUtil.getTenantId();//使用系统的SecureUtil获取租户ID
SupplierEntity oldSupplier=baseMapper.selectOne(Wrappers.<SupplierEntity>query().lambda()
.eq(SupplierEntity::getTenantId, tenantId)
.eq(SupplierEntity::getEmployee,supplierExcel.getEmployee()));
if (oldSupplier != null && oldSupplier.getId() != null) {
supplierEntity.setId(oldSupplier.getId());
this.updateEmployee(supplierEntity);
return;
}
}
this.submit(supplierEntity);
});
}
@Override
@Transactional(rollbackFor = Exception.class)
public boolean updateEmployee(SupplierEntity supplierEntity) {
String tenantId = supplierEntity.getTenantId();
Integer supplierCount = baseMapper.selectCount(
Wrappers.<SupplierEntity>query().lambda()
.eq(SupplierEntity::getTenantId, tenantId)
.eq(SupplierEntity::getEmployee, supplierEntity.getEmployee())
.notIn(SupplierEntity::getId, supplierEntity.getId())
);
if (supplierCount > 0) {
throw new ServiceException(StringUtil.format("当前厂商 [{}] 已存在!", supplierEntity.getEmployee()));
}
return updateById(supplierEntity);
}

@Override
public List<SupplierExcel> exportSupplier(Wrapper<SupplierEntity> queryWrapper) {
List<SupplierExcel> supplierExcelList = baseMapper.exportSupplier(queryWrapper);
supplierExcelList.forEach(supplier -> {
if (supplier.getDeptId()!=null) {
supplier.setDept(deptService.getById(supplier.getDeptId()).getDeptName());
}
else{
supplier.setDept("");
System.out.println("Null"+supplier.getDeptId());
}

});
return supplierExcelList;
}