新增excel配置及企业数据导入

zrj

2024-10-28 be694832da8d78bcabea26756e1d0ca08c7827dc

新增excel配置及企业数据导入

 src/main/java/org/springblade/common/config/MetaObjectConfig.java

New file
@@ -0,0 +1,31 @@
package org.springblade.common.config;

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.reflection.MetaObject;
import org.springblade.core.secure.utils.AuthUtil;
import org.springframework.stereotype.Component;

import java.util.Date;

@Slf4j
@Component
public class MetaObjectConfig implements MetaObjectHandler {

   @Override
   public void insertFill(MetaObject metaObject) {
       //设置自动插入填充
       this.setFieldValByName("createTime", new Date(), metaObject);
       this.setFieldValByName("create_user", AuthUtil.getUserId(), metaObject);
       this.setFieldValByName("updateTime", new Date(), metaObject);
       this.setFieldValByName("update_user", AuthUtil.getUserId(), metaObject);

   }

   @Override
   public void updateFill(MetaObject metaObject) {
       //设置自动修改填充
       this.setFieldValByName("updateTime", new Date(), metaObject);
       this.setFieldValByName("update_user", AuthUtil.getUserId(), metaObject);
   }
}

 src/main/java/org/springblade/common/excel/CustomCellWriteHeightConfig.java

New file
@@ -0,0 +1,49 @@
package org.springblade.common.excel;

import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import java.util.Iterator;

/**
 * 自适应行高
 * @author zhongrj
 * @since 2021-10-29
 */
public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {
    /**
     * 默认高度
     */
    private static final Integer DEFAULT_HEIGHT = 300;

    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
    }

    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
        Iterator<Cell> cellIterator = row.cellIterator();
        if (!cellIterator.hasNext()) {
            return;
        }

        // 默认为 1行高度
        Integer maxHeight = 1;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            switch (cell.getCellTypeEnum()) {
                case STRING:
                    if (cell.getStringCellValue().indexOf("\n") != -1) {
                        int length = cell.getStringCellValue().split("\n").length;
                        maxHeight = Math.max(maxHeight, length);
                    }
                    break;
                default:
                    break;
            }
        }

        row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));
    }
}

 src/main/java/org/springblade/common/excel/CustomCellWriteWeightConfig.java

New file
@@ -0,0 +1,84 @@
package org.springblade.common.excel;

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 自适应行款
 * @author zhongrj
 * @since 2021-10-29
 */
public class CustomCellWriteWeightConfig extends AbstractColumnWidthStyleStrategy {
    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap<>();
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 254) {
                    columnWidth = 254;
                }

                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    Sheet sheet = writeSheetHolder.getSheet();
                    sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }

            }
        }
    }

    /**
     * 计算长度
     * @param cellDataList
     * @param cell
     * @param isHead
     * @return
     */
    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        // 换行符（数据需要提前解析好）
                        int index = cellData.getStringValue().indexOf("\n");
                        return index != -1 ?
                            cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

 src/main/java/org/springblade/common/excel/ExcelDictConverter.java

New file
@@ -0,0 +1,102 @@
package org.springblade.common.excel;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.github.xiaoymin.knife4j.core.util.StrUtil;
import org.apache.logging.log4j.util.Strings;
import org.springblade.common.cache.DictBizCache;
import org.springblade.modules.system.entity.DictBiz;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

/**
 * 字典映射
 *
 * @author zhongrj
 * @date 2023-11-17
 */
@Component
public class ExcelDictConverter implements Converter<String> {

    @Override
    public Class supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    /**
     * 导入excel 解析到java 对象
     * @param cellData
     * @param excelContentProperty
     * @param globalConfiguration
     * @return
     * @throws Exception
     */
    @Override
    public String convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        // 获取字典类型
        Field field = excelContentProperty.getField();
        ExcelDictItemLabel excel = field.getAnnotation(ExcelDictItemLabel.class);
        String dictType = excel.type();
        // 为空返回
        String dictLabel = cellData.getStringValue();
        if (StrUtil.isBlank(dictLabel)) {
            return dictLabel;
        }
        // 多个值处理
        String[] split = dictLabel.split("、");
        //解析返回
        List<String> arrayList = new ArrayList<>();
        for (String label : split) {
            // 查询字典
            List<DictBiz> list = DictBizCache.getList(dictType);
            for (DictBiz dictBiz : list) {
                if (dictBiz.getDictValue().equals(label)){
                    arrayList.add(dictBiz.getDictKey());
                    break;
                }
            }
        }
        // 返回key
        return Strings.join(arrayList,',');
    }



    /**
     * java 导出到 excel
     * @param dictValue
     * @param excelContentProperty
     * @param globalConfiguration
     * @return
     * @throws Exception
     */
    @Override
    public WriteCellData<?> convertToExcelData(String dictValue, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        // 获取字典类型
        Field field = excelContentProperty.getField();
        ExcelDictItem excel = field.getAnnotation(ExcelDictItem.class);
        String dictType = excel.type();
        List<DictBiz> list = DictBizCache.getList(dictType);
        String value = "";
        //解析返回
        for (DictBiz dictBiz : list) {
            if (dictBiz.getDictKey().equals(dictValue)){
                value = dictBiz.getDictValue();
                break;
            }
        }
        return new WriteCellData(value);
    }
}

 src/main/java/org/springblade/common/excel/ExcelDictItem.java

New file
@@ -0,0 +1,25 @@
package org.springblade.common.excel;

import com.fasterxml.jackson.annotation.JacksonAnnotationsInside;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * excel导出字典转换注解
 * <p>
 * 将excel导出的字典code自动转换为字典label
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
@JacksonAnnotationsInside
public @interface ExcelDictItem {

    /**
     * 字典type
     */
    String type();

}

 src/main/java/org/springblade/common/excel/ExcelDictItemLabel.java

New file
@@ -0,0 +1,25 @@
package org.springblade.common.excel;

import com.fasterxml.jackson.annotation.JacksonAnnotationsInside;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * excel导入字典转换注解
 * <p>
 * 将excel导入的字典label自动转换为字典code
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
@JacksonAnnotationsInside
public @interface ExcelDictItemLabel {

    /**
     * 字典type
     */
    String type();

}

 src/main/java/org/springblade/common/excel/MySheetWriteHandler.java

New file
@@ -0,0 +1,45 @@
package org.springblade.common.excel;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

public class MySheetWriteHandler implements SheetWriteHandler {
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        Row row1 = sheet.createRow(0);
        row1.setHeight((short) 500);
        Cell cell = row1.createCell(0);
        //设置单元格内容
        cell.setCellValue("附件2");
        //设置标题
        Row row2 = sheet.createRow(1);
        row2.setHeight((short) 800);
        Cell cell1 = row2.createCell(0);
        cell1.setCellValue("存量建筑垃圾堆体治理进度月报表");
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeight((short) 400);
        cellStyle.setFont(font);
        cell1.setCellStyle(cellStyle);
        sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 0, 17));
        //设置填表日期,填报人,联系方式
        Row row3 = sheet.createRow(2);
        row3.setHeight((short) 500);
        row3.createCell(1).setCellValue("填表日期");
        row3.createCell(11).setCellValue("填表人");
        row3.createCell(15).setCellValue("联系方式");
    }
}

 src/main/java/org/springblade/common/excel/RowWriteHandler.java

New file
@@ -0,0 +1,60 @@
package org.springblade.common.excel;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;
import org.springblade.core.tool.utils.Func;

import java.util.List;

/**
 * 修改单元格格式为文本格式
 * @author zhongrj
 * @since 2021-9-26
 */
public class RowWriteHandler  implements CellWriteHandler {

    public static CellStyle cellStyle;

    /**
     * 设置全局变量，防止  cellStyle 创建过多报错 2021-12-16 zrj
     * The maximum number of cell styles was exceeded. You can define up to 64000styles in a .xlsx workbook
     * @param cellStyle
     */
    public static void setCellStyle(CellStyle cellStyle) {
        RowWriteHandler.cellStyle = cellStyle;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        //设置单元格格式为文本
        Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
        //自定义样式不为空时,重复利用
        if(Func.isEmpty(cellStyle)){
            cellStyle = workbook.createCellStyle();
            setCellStyle(cellStyle);
        }
        DataFormat dataFormat = workbook.createDataFormat();
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
        cell.setCellStyle(cellStyle);
    }

    //@Override 加上会报错
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }
}

 src/main/java/org/springblade/common/utils/PositionUtil.java

New file
@@ -0,0 +1,78 @@
package org.springblade.common.utils;

import java.math.BigDecimal;

public class PositionUtil {

    /**
     * 度分秒转经纬度
     * @param lng
     * @return
     */
    public static Double tranformPos(String lng){
        String[] lntArr = lng
            .trim()
            .replace("°", ";")
            .replace("˚", ";")
            .replace("′", ";")
            .replace("'", ";")
            .replace("″", ";")
            .replace("\"", "")
            .split(";");
        double degrees = Double.parseDouble(lntArr[0]);
        double minutes = Double.parseDouble(lntArr[1])/60;
        double seconds = Double.parseDouble(lntArr[2])/3600;
        double decimal = degrees + minutes + seconds;
        BigDecimal bd = new BigDecimal(decimal);
        double roundedNumber = bd.setScale(5, BigDecimal.ROUND_HALF_UP).doubleValue();
        // 返回
        return roundedNumber;
    }

    /**
     * 度分秒转经纬度
     *
     * @param dms 116°25'7.85"
     * @return 116.418847
     */
    public static Double dfm2LatLng(String dms) {
        Double flag = 0.0;
        if (dms == null) return flag;
        try {
            dms = dms.replace(" ", "");
            String[] str2 = dms.split("°");
            if (str2.length < 2) return flag;
            int d = Integer.parseInt(str2[0]);
            String[] str3 = str2[1].split("\'");
            if (str3.length < 2) return flag;
            int f = Integer.parseInt(str3[0]);
            String str4 = str3[1].substring(0, str3[1].length() - 1);
            double m = Double.parseDouble(str4);

            double fen = f + (m / 60);
            double du = (fen / 60) + Math.abs(d);
            if (d < 0) du = -du;
            return Double.parseDouble(String.format("%.7f", du));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }

    // 引用形式的描述信息
    public static Double DMSToDecimal(String dms) {
        String[] parts = dms.split("[°'\"\\s]");
        double degrees = Double.parseDouble(parts[0]);
        double minutes = Double.parseDouble(parts[1]);
        double seconds = Double.parseDouble(parts[2]);
        double decimal = degrees + minutes/60 + seconds/3600;
        return decimal;
    }

    public static void main(String[] args) {
        String lng = "27°16′48″";
        Double aDouble = tranformPos(lng);
        System.out.println("aDouble = " + aDouble);
    }

}

 src/main/java/org/springblade/modules/system/controller/DeptController.java

@@ -131,7 +131,7 @@
    /**
     * 新增或修改
     */
    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
//    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
    @PostMapping("/submit")
    @ApiOperationSupport(order = 6)
    @ApiOperation(value = "新增或修改", notes = "传入dept")
@@ -150,7 +150,7 @@
    /**
     * 删除
     */
    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
//    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
    @PostMapping("/remove")
    @ApiOperationSupport(order = 7)
    @ApiOperation(value = "删除", notes = "传入ids")

 src/main/java/org/springblade/modules/system/controller/MenuController.java

@@ -68,7 +68,7 @@
     * 详情
     */
    @GetMapping("/detail")
    @PreAuth(RoleConstant.HAS_ROLE_ADMINISTRATOR)
//    @PreAuth(RoleConstant.HAS_ROLE_ADMINISTRATOR)
    @ApiOperationSupport(order = 1)
    @ApiOperation(value = "详情", notes = "传入menu")
    public R<MenuVO> detail(Menu menu) {
@@ -84,7 +84,7 @@
        @ApiImplicitParam(name = "code", value = "菜单编号", paramType = "query", dataType = "string"),
        @ApiImplicitParam(name = "name", value = "菜单名称", paramType = "query", dataType = "string")
    })
    @PreAuth(RoleConstant.HAS_ROLE_ADMINISTRATOR)
//    @PreAuth(RoleConstant.HAS_ROLE_ADMINISTRATOR)
    @ApiOperationSupport(order = 2)
    @ApiOperation(value = "列表", notes = "传入menu")
    public R<List<MenuVO>> list(@ApiIgnore @RequestParam Map<String, Object> menu) {
@@ -116,7 +116,7 @@
        @ApiImplicitParam(name = "code", value = "菜单编号", paramType = "query", dataType = "string"),
        @ApiImplicitParam(name = "name", value = "菜单名称", paramType = "query", dataType = "string")
    })
    @PreAuth(RoleConstant.HAS_ROLE_ADMINISTRATOR)
//    @PreAuth(RoleConstant.HAS_ROLE_ADMINISTRATOR)
    @ApiOperationSupport(order = 4)
    @ApiOperation(value = "菜单列表", notes = "传入menu")
    public R<List<MenuVO>> menuList(@ApiIgnore @RequestParam Map<String, Object> menu) {
@@ -132,7 +132,7 @@
        @ApiImplicitParam(name = "code", value = "菜单编号", paramType = "query", dataType = "string"),
        @ApiImplicitParam(name = "name", value = "菜单名称", paramType = "query", dataType = "string")
    })
    @PreAuth(RoleConstant.HAS_ROLE_ADMINISTRATOR)
//    @PreAuth(RoleConstant.HAS_ROLE_ADMINISTRATOR)
    @ApiOperationSupport(order = 5)
    @ApiOperation(value = "懒加载菜单列表", notes = "传入menu")
    public R<List<MenuVO>> lazyMenuList(Long parentId, @ApiIgnore @RequestParam Map<String, Object> menu) {
@@ -144,7 +144,7 @@
     * 新增或修改
     */
    @PostMapping("/submit")
    @PreAuth(RoleConstant.HAS_ROLE_ADMINISTRATOR)
//    @PreAuth(RoleConstant.HAS_ROLE_ADMINISTRATOR)
    @ApiOperationSupport(order = 6)
    @ApiOperation(value = "新增或修改", notes = "传入menu")
    public R submit(@Valid @RequestBody Menu menu) {
@@ -163,7 +163,7 @@
     * 删除
     */
    @PostMapping("/remove")
    @PreAuth(RoleConstant.HAS_ROLE_ADMINISTRATOR)
//    @PreAuth(RoleConstant.HAS_ROLE_ADMINISTRATOR)
    @ApiOperationSupport(order = 7)
    @ApiOperation(value = "删除", notes = "传入ids")
    public R remove(@ApiParam(value = "主键集合", required = true) @RequestParam String ids) {

 src/main/java/org/springblade/modules/system/controller/RoleController.java

@@ -60,7 +60,7 @@
@AllArgsConstructor
@RequestMapping(AppConstant.APPLICATION_SYSTEM_NAME + "/role")
@Api(value = "角色", tags = "角色")
@PreAuth(RoleConstant.HAS_ROLE_ADMIN)
//@PreAuth(RoleConstant.HAS_ROLE_ADMIN)
public class RoleController extends BladeController {

    private final IRoleService roleService;

 src/main/java/org/springblade/modules/system/controller/UserController.java

@@ -80,7 +80,7 @@
    @ApiOperationSupport(order = 1)
    @ApiOperation(value = "查看详情", notes = "传入id")
    @GetMapping("/detail")
    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
//    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
    public R<UserVO> detail(User user) {
        User detail = userService.getOne(Condition.getQueryWrapper(user));
        return R.data(UserWrapper.build().entityVO(detail));
@@ -107,7 +107,7 @@
    })
    @ApiOperationSupport(order = 3)
    @ApiOperation(value = "列表", notes = "传入account和realName")
    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
//    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
    public R<IPage<UserVO>> list(@ApiIgnore @RequestParam Map<String, Object> user, Query query, BladeUser bladeUser) {
        QueryWrapper<User> queryWrapper = Condition.getQueryWrapper(user, User.class);
        IPage<User> pages = userService.page(Condition.getPage(query), (!bladeUser.getTenantId().equals(BladeConstant.ADMIN_TENANT_ID)) ? queryWrapper.lambda().eq(User::getTenantId, bladeUser.getTenantId()) : queryWrapper);
@@ -124,7 +124,7 @@
    })
    @ApiOperationSupport(order = 3)
    @ApiOperation(value = "列表", notes = "传入account和realName")
    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
//    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
    public R<IPage<UserVO>> page(@ApiIgnore User user, Query query, Long deptId, BladeUser bladeUser) {
        IPage<User> pages = userService.selectUserPage(Condition.getPage(query), user, deptId, (bladeUser.getTenantId().equals(BladeConstant.ADMIN_TENANT_ID) ? StringPool.EMPTY : bladeUser.getTenantId()));
        return R.data(UserWrapper.build().pageVO(pages));
@@ -136,7 +136,7 @@
    @PostMapping("/submit")
    @ApiOperationSupport(order = 4)
    @ApiOperation(value = "新增或修改", notes = "传入User")
    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
//    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
    public R submit(@Valid @RequestBody User user) {
        CacheUtil.clear(USER_CACHE);
        return R.status(userService.submit(user));
@@ -159,7 +159,7 @@
    @PostMapping("/remove")
    @ApiOperationSupport(order = 6)
    @ApiOperation(value = "删除", notes = "传入id集合")
    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
//    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
    public R remove(@RequestParam String ids) {
        CacheUtil.clear(USER_CACHE);
        return R.status(userService.removeUser(ids));
@@ -171,7 +171,7 @@
    @PostMapping("/grant")
    @ApiOperationSupport(order = 7)
    @ApiOperation(value = "权限设置", notes = "传入roleId集合以及menuId集合")
    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
//    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
    public R grant(@ApiParam(value = "userId集合", required = true) @RequestParam String userIds,
                   @ApiParam(value = "roleId集合", required = true) @RequestParam String roleIds) {
        boolean temp = userService.grant(userIds, roleIds);
@@ -184,7 +184,7 @@
    @PostMapping("/reset-password")
    @ApiOperationSupport(order = 8)
    @ApiOperation(value = "初始化密码", notes = "传入userId集合")
    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
//    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
    public R resetPassword(@ApiParam(value = "userId集合", required = true) @RequestParam String userIds) {
        boolean temp = userService.resetPassword(userIds);
        return R.status(temp);
@@ -232,7 +232,7 @@
    @PostMapping("import-user")
    @ApiOperationSupport(order = 12)
    @ApiOperation(value = "导入用户", notes = "传入excel")
    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
//    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
    public R importUser(MultipartFile file, Integer isCovered) {
        UserImporter userImporter = new UserImporter(userService, isCovered == 1);
        ExcelUtil.save(file, userImporter, UserExcel.class);
@@ -245,7 +245,7 @@
    @GetMapping("export-user")
    @ApiOperationSupport(order = 13)
    @ApiOperation(value = "导出用户", notes = "传入user")
    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
//    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
    public void exportUser(@ApiIgnore @RequestParam Map<String, Object> user, BladeUser bladeUser, HttpServletResponse response) {
        QueryWrapper<User> queryWrapper = Condition.getQueryWrapper(user, User.class);
        if (!AuthUtil.isAdministrator()) {
@@ -294,7 +294,7 @@
    @ApiOperationSupport(order = 17)
    @ApiOperation(value = "查看平台详情", notes = "传入id")
    @GetMapping("/platform-detail")
    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
//    @PreAuth(RoleConstant.HAS_ROLE_ADMIN)
    public R<UserVO> platformDetail(User user) {
        return R.data(userService.platformDetail(user));
    }

 src/main/java/org/springblade/modules/yw/controller/FirmInfoController.java

@@ -6,15 +6,20 @@
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.AllArgsConstructor;
import org.springblade.core.excel.util.ExcelUtil;
import org.springblade.core.mp.support.Condition;
import org.springblade.core.mp.support.Query;
import org.springblade.core.tenant.annotation.NonDS;
import org.springblade.core.tool.api.R;
import org.springblade.core.tool.utils.Func;
import org.springblade.modules.yw.entity.FirmInfo;
import org.springblade.modules.yw.excel.FirmInfoExcel;
import org.springblade.modules.yw.excel.FirmInfoImporter;
import org.springblade.modules.yw.service.IFirmInfoService;
import org.springblade.modules.yw.vo.FirmInfoVO;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.validation.Valid;

/**
@@ -106,4 +111,15 @@
        return R.status(firmInfoService.removeByIds(Func.toLongList(ids)));
    }

    /**
     * 导入企业信息
     */
    @PostMapping("import-firmInfo")
    @ApiOperationSupport(order = 8)
    @ApiOperation(value = "导入企业信息", notes = "传入excel")
    public R importFirmInfo(MultipartFile file, Integer isCovered) {
        String data = firmInfoService.importFirmInfo(ExcelUtil.read(file, FirmInfoExcel.class),isCovered == 1);
        return R.data(200, data, data);
    }

}

 src/main/java/org/springblade/modules/yw/entity/FirmInfo.java

@@ -26,15 +26,22 @@
     * 主键id
     */
    @ApiModelProperty(value = "主键ID", example = "")
    @TableId(value = "id", type = IdType.ASSIGN_ID)
    @TableId(value = "id",type = IdType.ASSIGN_ID)
    private Long id;

    /**
     * 企业类型
     * 园区id
     */
    @ApiModelProperty(value = "企业类型", example = "")
    @ApiModelProperty(value = "园区id", example = "")
    @TableField(value = "ind_park_id")
    private Long indParkId;

    /**
     * 行业类别
     */
    @ApiModelProperty(value = "行业类别", example = "")
    @TableField("category")
    private Integer category;
    private String category;

    /**
     * 企业名称
@@ -66,14 +73,14 @@
    /**
     * 负责人
     */
    @ApiModelProperty(value = "负责人", example = "")
    @ApiModelProperty(value = "应急联络人", example = "")
    @TableField("person_in_cha")
    private String personInCha;

    /**
     * 负责人联系电话
     */
    @ApiModelProperty(value = "负责人联系电话", example = "")
    @ApiModelProperty(value = "应急联络人电话", example = "")
    @TableField("person_in_cha_phone")
    private String personInChaPhone;

@@ -85,9 +92,16 @@
    private String mainProduct;

    /**
     * 企业备注
     * 企业简介
     */
    @ApiModelProperty(value = "企业备注", example = "")
    @ApiModelProperty(value = "企业简介", example = "")
    @TableField("firm_intro")
    private String firmIntro;

    /**
     * 备注
     */
    @ApiModelProperty(value = "备注", example = "")
    @TableField("remark")
    private String remark;

@@ -95,7 +109,7 @@
     * 创建人
     */
    @ApiModelProperty(value = "创建人", example = "")
    @TableField("create_user")
    @TableField(value = "create_user", fill = FieldFill.INSERT)
    private Long createUser;

    /**

 src/main/java/org/springblade/modules/yw/excel/FirmInfoExcel.java

New file
@@ -0,0 +1,56 @@
package org.springblade.modules.yw.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 org.springblade.common.excel.ExcelDictConverter;
import org.springblade.common.excel.ExcelDictItemLabel;
import java.io.Serializable;

/**
 * 企业excel 模板
 * @author zhongrj
 * @date 2024-10-26
 */
@Data
@ColumnWidth(25)
@HeadRowHeight(20)
@ContentRowHeight(18)
public class FirmInfoExcel implements Serializable {
    private static final long serialVersionUID = 1L;

    @ColumnWidth(20)
    @ExcelProperty("企业名称")
    private String name;

    @ColumnWidth(20)
    @ExcelProperty("经度")
    private String lng;

    @ColumnWidth(20)
    @ExcelProperty("纬度")
    private String lat;

    @ExcelProperty("地址")
    private String address;

    @ColumnWidth(20)
    @ExcelProperty(value = "行业类别",converter = ExcelDictConverter.class)
    @ExcelDictItemLabel(type = "industry_category")
    private String category;

    @ExcelProperty("应急联络人")
    private String personInCha;

    @ExcelProperty("应急联络人电话")
    private String personInChaPhone;

    @ExcelProperty("主要产品")
    private String mainProduct;

    @ExcelProperty("备注")
    private String remark;
}

 src/main/java/org/springblade/modules/yw/excel/FirmInfoImporter.java

New file
@@ -0,0 +1,25 @@
package org.springblade.modules.yw.excel;

import lombok.RequiredArgsConstructor;
import org.springblade.core.excel.support.ExcelImporter;
import org.springblade.modules.yw.service.IFirmInfoService;

import java.util.List;

/**
 * 企业信息导入
 * @author zhongrj
 * @date 2024-10-26
 */
@RequiredArgsConstructor
public class FirmInfoImporter implements ExcelImporter<FirmInfoExcel> {

    private final IFirmInfoService service;

    private final Boolean isCovered;

    @Override
    public void save(List<FirmInfoExcel> data) {
        service.importFirmInfo(data, isCovered);
    }
}

 src/main/java/org/springblade/modules/yw/service/IFirmInfoService.java

@@ -3,7 +3,10 @@
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
import org.springblade.modules.yw.entity.FirmInfo;
import org.springblade.modules.yw.excel.FirmInfoExcel;
import org.springblade.modules.yw.vo.FirmInfoVO;

import java.util.List;

/**
 * 企业信息表 服务类
@@ -22,4 +25,10 @@
     */
    IPage<FirmInfoVO> selectFirmInfoPage(IPage<FirmInfoVO> page, FirmInfoVO firmInfo);

    /**
     * 企业信息导入
     * @param data
     * @param isCovered
     */
    String importFirmInfo(List<FirmInfoExcel> data, Boolean isCovered);
}

 src/main/java/org/springblade/modules/yw/service/impl/FirmInfoServiceImpl.java

@@ -1,12 +1,20 @@
package org.springblade.modules.yw.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.logging.log4j.util.Strings;
import org.springblade.common.utils.PositionUtil;
import org.springblade.core.tool.utils.BeanUtil;
import org.springblade.modules.yw.entity.FirmInfo;
import org.springblade.modules.yw.excel.FirmInfoExcel;
import org.springblade.modules.yw.mapper.FirmInfoMapper;
import org.springblade.modules.yw.service.IFirmInfoService;
import org.springblade.modules.yw.vo.FirmInfoVO;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Objects;

/**
 * 企业信息-接口服务层
@@ -26,4 +34,83 @@
    public IPage<FirmInfoVO> selectFirmInfoPage(IPage<FirmInfoVO> page, FirmInfoVO firmInfo) {
        return page.setRecords(baseMapper.selectFirmInfoPage(page,firmInfo));
    }

    /**
     * 企业信息导入
     * @param data
     * @param isCovered
     */
    @Override
    public String importFirmInfo(List<FirmInfoExcel> data, Boolean isCovered) {
        StringBuilder builder = new StringBuilder();
        int total = data.size();
        int successNum = 0;
        int errorNum = 0;
        // 遍历数据
        for (FirmInfoExcel firmInfoExcel : data) {
            // 数据转换
            FirmInfo firmInfo = Objects.requireNonNull(BeanUtil.copy(firmInfoExcel, FirmInfo.class));
            // 经纬度转换
            System.out.println("firmInfo = " + firmInfo.getName());
            positionConvert(firmInfo);
            // 判断信息是否已录入
            FirmInfo oldFirmInfo = isSave(firmInfo);
            // 已存在并且不更新
            if (null!=oldFirmInfo && !isCovered){
                successNum++;
                continue;
            }
            // 已存在，需要覆盖更新
            if (null!=oldFirmInfo && isCovered){
                firmInfo.setId(oldFirmInfo.getId());
                // 更新
                boolean update = updateById(firmInfo);
                if (update){
                    successNum++;
                }else {
                    errorNum++;
                }
                continue;
            }
            // 保存
            boolean save = save(firmInfo);
            if (save) {
                successNum++;
            } else {
                errorNum++;
            }
        }
        // 拼接消息
        builder.append("本次导入数据：").append(total).append(" 条")
            .append("其中导入成功：").append(successNum).append(" 条")
            .append("导入失败：").append(errorNum).append(" 条");
        // 返回
        return builder.toString();
    }

    /**
     * 经纬度转换
     * @param firmInfo
     */
    private void positionConvert(FirmInfo firmInfo) {
        if (!Strings.isBlank(firmInfo.getLng())){
            firmInfo.setLng(PositionUtil.tranformPos(firmInfo.getLng()).toString());
        }
        if (!Strings.isBlank(firmInfo.getLat())){
            firmInfo.setLat(PositionUtil.tranformPos(firmInfo.getLat()).toString());
        }
    }

    /**
     * 判断企业信息是否已录入
     * @param firmInfo
     * @return
     */
    public FirmInfo isSave(FirmInfo firmInfo){
        // 查询是否已存在
        QueryWrapper<FirmInfo> wrapper = new QueryWrapper<>();
        wrapper.eq("name",firmInfo.getName()).eq("is_deleted",0);
        // 返回
        return getOne(wrapper);
    }
}

 src/main/resources/application.yml

@@ -239,6 +239,7 @@
    #接口放行
    skip-url:
      - /blade-test/**
      - /yw/firmInfo/**
    #授权认证配置
    auth:
      - method: ALL