EasyExcel使用

EasyExcel使用–导入导出服务器为例

上传文件读取

@Operation(summary = "物理机导入上传文件")
    @PostMapping(value = "/import/machine")
    public String importFile(@RequestBody MultipartFile multipartFile) {
        return serverUploadService.upload(multipartFile);
    }

private static final String SEPARATOR = "/";
    private static final Logger logger = LoggerFactory.getLogger(ServerUploadServiceImpl.class);

@Override
    public String upload(MultipartFile multipartFile) {
        String fileName = Objects.requireNonNull(multipartFile.getOriginalFilename())
                .substring(0, multipartFile.getOriginalFilename().lastIndexOf("."));
        String fileSuffix = Objects.requireNonNull(multipartFile.getOriginalFilename())
                .substring(multipartFile.getOriginalFilename().lastIndexOf("."));
        String newFileName = fileName + UUID.randomUUID() + fileSuffix;
        String filePath = String.join(SEPARATOR, "D:\\upload", newFileName);
        File file = new File(filePath);

        try {
            multipartFile.transferTo(file);
        } catch (Exception e) {
            logger.error("文件上传异常", e);
            throw new SystemException("文件上传异常");
        }

        String uploadUid = UUID.randomUUID().toString();

        ServerExcelImportBo temp = new ServerExcelImportBo();
        temp.setUploadUid(uploadUid);
        temp.setStatus(ServerImportStatus.WAIT);
        temp.setFilePath(filePath);
        metaControllerProcessMap.put(uploadUid, temp);

        String tenantId = LoginUserUtil.getTenantId();
        String userUid = LoginUserUtil.getUserUid();

        //异步执行
        CompletableFuture.runAsync(() -> getCheck(uploadUid,tenantId,userUid));

        return uploadUid;
    }

//并发HashMap
private final ConcurrentHashMap<String, ServerExcelImportBo> metaControllerProcessMap = new ConcurrentHashMap<String, ServerExcelImportBo>(8){
        @Override
        public ServerExcelImportBo get(Object key) {
            ServerExcelImportBo temp = super.get(key);
            if(ServerImportStatus.SUCCESS.equals(temp.getStatus())){
                super.remove(key);
            }
            clearMap();
            return temp;
        }

        private void clearMap(){
            if(super.size() > 5){
                super.forEach((key, value) -> {
                    if(ServerImportStatus.SUCCESS.equals(value.getStatus())){
                        logger.info(String.format("remove key:%s",key));
                        super.remove(key);
                    }
                });
            }
        }

        @Override
        public ServerExcelImportBo put(String key, ServerExcelImportBo value) {
            clearMap();
            return super.put(key, value);
        }

    };

@Async
    public void getCheck(String uploadUid,String tenantId,String userUid){

        ServerExcelImportBo currentImport = metaControllerProcessMap.get(uploadUid);
        if(currentImport == null){
            logger.warn("未找到任务:"+uploadUid);
            return;
        }

        currentImport.setStatus(ServerImportStatus.RUNNING);
        metaControllerProcessMap.put(uploadUid, currentImport);

        DataReadListener<MachineBasicInfoSheet> dataReadListener = (DataReadListener<MachineBasicInfoSheet>) machineBasicInfoDataReader;
        dataReadListener.setUploadUid(uploadUid);
        ((MachineBasicInfoDataReader) dataReadListener).setTenantId(tenantId);
        ((MachineBasicInfoDataReader) dataReadListener).setUserUid(userUid);
        ReadSheet readSheet = EasyExcel.readSheet(dataReadListener.sheetName())
                .head(dataReadListener.getSheetClass())
                .headRowNumber(dataReadListener.getHeadRowNumber())
                .registerReadListener(dataReadListener)
                .build();

        ExcelReader excelReader = EasyExcel.read(currentImport.getFilePath())
                .registerConverter(new StringToListConverter())
                .registerConverter(new StringToListNameRelValueConverter())
                .build();

        try {
            excelReader.read(readSheet);
        } catch (Exception e) {
            logger.error("读取 Excel 文件时发生错误：" + e.getMessage());
        } finally {
            if(excelReader != null){
                excelReader.finish();
            }
        }

        boolean error = false;
        File errorExcel = new File("D:\\errorExcel.xlsx");
        if(!errorExcel.exists()){
            try {
                errorExcel.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        List<?> errorData = dataReadListener.getErrorData();
        currentImport.setErrorNum(errorData.size());
        currentImport.setTotal(dataReadListener.getTotal());
        if(!ObjectUtils.isEmpty(errorData)){
            error = true;
        }

        if(!ObjectUtils.isEmpty(errorData)){
            WriteSheet writeSheet = EasyExcelFactory.writerSheet(0,dataReadListener.getSheetName())
                    .head(dataReadListener.getSheetClass())
                    .build();
            ExcelWriter excelWritter = EasyExcel.write(errorExcel)
                    .excelType(ExcelTypeEnum.XLSX)
                    .registerConverter(new StringToListConverter())
                    .registerConverter(new StringToListNameRelValueConverter())
                    .build();
            excelWritter.write(errorData,writeSheet);
            excelWritter.finish();
        }

        if(error){
            FileInfo errorFile = new FileInfo(errorExcel.getName(), errorExcel.getPath());
            errorFile.setFileType(FileType.EXCEL);
            errorFile.setFileSize(String.valueOf(currentImport.getErrorNum()));
            currentImport.setErrorDataFile(errorFile);
        }
        currentImport.setStatus(ServerImportStatus.SUCCESS);
        metaControllerProcessMap.put(uploadUid, currentImport);

    }

监听器

package com.sugon.rest.application.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.util.ObjectUtils;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import java.util.concurrent.TimeUnit;

/**
 * @author zhang.wp
 * @date 2024/4/17 16:05
 */
public abstract class DataReadListener<T> extends AnalysisEventListener<T> {

    protected final List<T> errorData = new ArrayList<>();
    private final List<T> dataList = new ArrayList<>(100);
    private Integer total = 0;
    private Integer currentRow = 0;
    private String uploadUid;

    @Resource
    private RedisTemplate<String, String> redisTemplate;

    public String getUploadUid() {
        return uploadUid;
    }

    public void setUploadUid(String uploadUid) {
        this.uploadUid = uploadUid;
    }

    public Integer getTotal() {
        return total;
    }

    public List<T> getErrorData() {
        return errorData;
    }

    public String getSheetName() {
        return sheetName();
    }

    public Class<?> getSheetClass() {
        return sheetClass();
    }

    public Integer getHeadRowNumber() {
        return headRowNumber();
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        if (total == 0) {
            this.total = context.readSheetHolder().getApproximateTotalRowNumber() - getHeadRowNumber();
        }
        if (dataList.size() < 100) {
            dataList.add(data);
        } else {
            read(dataList);
            this.currentRow = this.currentRow + dataList.size();
            dataList.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        if (!ObjectUtils.isEmpty(dataList)) {
            read(dataList);
            this.currentRow = this.currentRow + dataList.size();
            String finishNum = Optional.ofNullable(redisTemplate.opsForValue().get(getUploadUid())).orElse("0");
            int temp = Integer.parseInt(finishNum) + 1;
            redisTemplate.opsForValue().set(getUploadUid(), Integer.toString(temp), 30, TimeUnit.MINUTES);
        }
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        dataList.clear();
        super.onException(exception, context);
    }

    public abstract void read(List<T> data);

    public abstract String sheetName();

    public abstract Class<?> sheetClass();

    public abstract Integer headRowNumber();
}

package com.sugon.rest.application.listener;

import com.sugon.core.application.enums.LabelGroup;
import com.sugon.core.application.manager.PhysicalMachineManager;
import com.sugon.core.application.manager.ServerClusterManager;
import com.sugon.core.application.pojo.po.PhysicalMachine;
import com.sugon.core.application.pojo.po.ServerCluster;
import com.sugon.core.auth.pojo.po.Label;
import com.sugon.core.auth.manager.LabelManager;
import com.sugon.core.auth.util.LoginUserUtil;
import com.sugon.core.common.util.UuidUtil;
import com.sugon.rest.application.entity.excel.server.MachineBasicInfoSheet;
import com.sugon.rest.application.entity.form.MachineSaveForm;
import com.sugon.rest.application.service.ServerService;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;
import java.util.HashSet;
import java.util.Set;
import java.util.stream.Collectors;
import java.util.Map;

@Log4j2
@Component
public class MachineBasicInfoDataReader extends DataReadListener<MachineBasicInfoSheet> {
    private String tenantId;
    private String userUid;

    private static LabelManager labelManager;
    private static ServerService serverService;
    private static ServerClusterManager serverClusterManager;
    private static PhysicalMachineManager physicalMachineManager;

    public void setTenantId(String tenantId) {
        this.tenantId = tenantId;
    }
    public void setUserUid(String userUid) {
        this.userUid = userUid;
    }

    @Autowired
    private void init(LabelManager labelManager,
                      ServerService serverService,
                      ServerClusterManager serverClusterManager,
                      PhysicalMachineManager physicalMachineManager) {
        MachineBasicInfoDataReader.labelManager = labelManager;
        MachineBasicInfoDataReader.serverService = serverService;
        MachineBasicInfoDataReader.serverClusterManager = serverClusterManager;
        MachineBasicInfoDataReader.physicalMachineManager = physicalMachineManager;
    }

    @Override
    public void read(List<MachineBasicInfoSheet> dataList) {

        Set<String> machineNameSet = physicalMachineManager.lambdaQuery()
                .eq(PhysicalMachine::getTenantId, tenantId)
                .eq(PhysicalMachine::getDelete,false)
                .list().stream()
                .map(PhysicalMachine::getName)
                .collect(Collectors.toSet());
        Set<String> serverIpSet = physicalMachineManager.lambdaQuery()
                .eq(PhysicalMachine::getTenantId, tenantId)
                .eq(PhysicalMachine::getDelete,false)
                .list().stream()
                .map(PhysicalMachine::getServerIp)
                .collect(Collectors.toSet());
        Map<String,String> clusterSet = serverClusterManager.lambdaQuery()
                .eq(ServerCluster::getTenantId, tenantId)
                .eq(ServerCluster::getDelete,false)
                .list().stream()
                .collect(Collectors.toMap(ServerCluster::getName, ServerCluster::getUid));
        Map<String,String> brandSet = labelManager.lambdaQuery()
                .eq(Label::getTenantId, tenantId)
                .eq(Label::getDelete,false)
                .eq(Label::getGroup, LabelGroup.BRAND)
                .list().stream()
                .collect(Collectors.toMap(Label::getName, Label::getUid));
        Map<String,String> osSet = labelManager.lambdaQuery()
                .eq(Label::getTenantId, tenantId)
                .eq(Label::getDelete,false)
                .eq(Label::getGroup, LabelGroup.OPERATING_SYSTEM)
                .list().stream()
                .collect(Collectors.toMap(Label::getName, Label::getUid));
        Map<String,String> networkSet = labelManager.lambdaQuery()
                .eq(Label::getTenantId, tenantId)
                .eq(Label::getDelete,false)
                .eq(Label::getGroup, LabelGroup.NETWORK)
                .list().stream()
                .collect(Collectors.toMap(Label::getName, Label::getUid));


        List<MachineSaveForm> successData = new ArrayList<>();
        HashSet<String> machineName = new HashSet<>();
        HashSet<String> serverIp = new HashSet<>();

        for(MachineBasicInfoSheet data : dataList) {
            MachineSaveForm temp = new MachineSaveForm();
            boolean error = false;

            //必填项
            if(StringUtils.isBlank(data.getMachineName())) {
                error = true;
                data.setMachineName("不可为空");
            }
            if(StringUtils.isBlank(data.getServerIp())) {
                error = true;
                data.setServerIp("不可为空");
            }
            if(StringUtils.isBlank(data.getCluster())) {
                error = true;
                data.setCluster("不可为空");
            }

            //物理机名称唯一性校验
            if(machineName.contains(data.getMachineName()) || machineNameSet.contains((data.getMachineName()))){
                error = true;
                data.setMachineName("(重复)"+data.getMachineName());
            } else {
                machineName.add(data.getMachineName());
                temp.setMachineName(data.getMachineName());
            }

            //IP唯一性校验
            if(serverIp.contains(data.getServerIp()) || serverIpSet.contains(data.getServerIp())){
                error = true;
                data.setServerIp("(重复)"+data.getServerIp());
            } else {
                serverIp.add(data.getServerIp());
                temp.setServerIp(data.getServerIp());
            }
            temp.setClusterUid(clusterSet.get(data.getCluster()));
            temp.setBrandUid(brandSet.get(data.getBrand()));
            temp.setModel(data.getModel());
            temp.setCpuNum(data.getCpu());
            temp.setMemory(data.getMemory());
            temp.setStorage(data.getStorage());
            temp.setOperatingSystem(osSet.get(data.getOperatingSystem()));
            List<String> networkUidSet = data.getNetwork().stream()
                    .map(networkSet::get)
                    .collect(Collectors.toList());
            temp.setNetwork(networkUidSet);
            temp.setDomainName(data.getDomain());
            temp.setLabelList(data.getLabel());
            temp.setCabinet(data.getCabinet());
            temp.setUseTo(data.getUseTo());
            temp.setMachineUid(UuidUtil.create());

            if(error){
                errorData.add(data);
            } else {
                successData.add(temp);
            }

        }

        if(!ObjectUtils.isEmpty(successData)){
            serverService.saveMachineInfo(successData,tenantId,userUid);
        }
    }

    @Override
    public String sheetName() {
        return "物理机基本信息";
    }

    @Override
    public Class<?> sheetClass() {
        return MachineBasicInfoSheet.class;
    }

    @Override
    public Integer headRowNumber() {
        return 1;
    }
}

批量存储

if (!ObjectUtils.isEmpty(machineInfoSaveForm.getNetwork())) {
                List<PhysicalMachineNetwork> machineNetworks = machineInfoSaveForm.getNetwork().stream()
                        .map(item -> {
                            PhysicalMachineNetwork temp = new PhysicalMachineNetwork();
                            temp.setUid(UuidUtil.create());
                            temp.setMachineUid(machineInfoSaveForm.getMachineUid());
                            temp.setNetwork(item);
                            temp.setTenantId(tenantId);
                            temp.setDelete(false);
                            temp.setCreatedBy(userUid);
                            temp.setCreatedTime(new Date());
                            return temp;
                        }).collect(Collectors.toList());
                physicalMachineNetworkManager.saveBatch(machineNetworks);
            }

@Override
    public void addServerSaveLabel(List<NameRelValue<String>> form, ObjectType objectType, String objectUid,String tenantId,String userUid) {
        if (!ObjectUtils.isEmpty(form)) {
            List<ServerLabelRelation> serverLabelRelations = new ArrayList<>();
            List<ServerLabel> serverLabels = new ArrayList<>();
            Map<String, String> exitsLabelMap = new HashMap<>();
            List<ServerLabel> exitsLabel = serverLabelManager.lambdaQuery()
                    .eq(ServerLabel::getTenantId, tenantId)
                    .eq(ServerLabel::getDelete, false)
                    .list();
            if (!ObjectUtils.isEmpty(exitsLabel)) {
                exitsLabelMap.putAll(exitsLabel.stream()
                        .collect(Collectors.toMap(ServerLabel::getContent, ServerLabel::getUid)));
            }
            form.forEach(item -> {
                if (StringUtils.isBlank(item.getValue())
                        && !exitsLabelMap.containsKey(item.getName())) {
                    ServerLabel temp = new ServerLabel();
                    temp.setUid(UuidUtil.create());
                    item.setValue(temp.getUid());
                    temp.setContent(item.getName());
                    temp.setTenantId(tenantId);
                    temp.setDelete(false);
                    temp.setCreatedBy(userUid);
                    temp.setCreatedTime(new Date());
                    serverLabels.add(temp);

                    exitsLabelMap.put(temp.getContent(), temp.getUid());
                } else if (exitsLabelMap.containsKey(item.getName())) {
                    item.setValue(exitsLabelMap.get(item.getName()));
                }
                ServerLabelRelation temp = new ServerLabelRelation();
                temp.setUid(UuidUtil.create());
                temp.setObjectType(objectType);
                temp.setObjectUid(objectUid);
                temp.setLabelUid(item.getValue());
                temp.setTenantId(tenantId);
                temp.setDelete(false);
                temp.setCreatedBy(userUid);
                temp.setCreatedTime(new Date());
                serverLabelRelations.add(temp);
            });
            serverLabelRelationManager.saveBatch(serverLabelRelations);
            serverLabelManager.saveBatch(serverLabels);

        }
    }

EasyExcel

ReadSheet readSheet = EasyExcel.readSheet(dataReadListener.sheetName())
                .head(dataReadListener.getSheetClass())
                .headRowNumber(dataReadListener.getHeadRowNumber())
                .registerReadListener(dataReadListener)
                .build();

        ExcelReader excelReader = EasyExcel.read(currentImport.getFilePath())
                .registerConverter(new StringToListConverter())
                .registerConverter(new StringToListNameRelValueConverter())
                .build();

转换器

package com.sugon.rest.application.utils;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.enums.CellDataTypeEnum;
import lombok.extern.log4j.Log4j2;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@Log4j2
public class StringToListConverter implements Converter<List<String>> {

    @Override
    public Class<?> supportJavaTypeKey() {
        return List.class;
    }

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

    @Override
    public List<String> convertToJavaData(ReadCellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        String cellValue = cellData.getStringValue();

        log.info("转换单元格数据cellValue: {}", cellValue);

        if(cellValue == null || cellValue.isEmpty()){
            return new ArrayList<>();
        }else if (!cellValue.contains(",")) {
            return Arrays.asList(cellValue.trim());
        } else {
            return Arrays.asList(cellValue.split(","));
        }
    }

    @Override
    public WriteCellData<String> convertToExcelData(List<String> value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        if (value == null || value.isEmpty()) {
            return new WriteCellData<>("");
        }
        String joinedString = String.join(",", value);
        log.info("转换 List<String> 为字符串: {}", joinedString);
        return new WriteCellData<>(joinedString);
    }


}

package com.sugon.rest.application.utils;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.sugon.core.common.entity.NameRelValue;
import lombok.extern.java.Log;
import lombok.extern.log4j.Log4j2;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@Log4j2
public class StringToListNameRelValueConverter implements Converter<List<NameRelValue<String>>> {

    @Override
    public Class<?> supportJavaTypeKey() {
        return null;
    }

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

    @Override
    public List<NameRelValue<String>> convertToJavaData(ReadCellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        String cellValue = cellData.getStringValue();
        List<NameRelValue<String>> result = new ArrayList<>();

        log.info("转换单元格数据cellValue: {}", cellValue);

        if (cellValue != null && !cellValue.isEmpty()) {
            String[] pairs = cellValue.split(",");
            for (String pair : pairs) {
                String[] nameValue = pair.split(":");
                if (nameValue.length == 2) {
                    NameRelValue<String> nameRelValue = new NameRelValue<>();
                    nameRelValue.setName(nameValue[0].trim());
                    nameRelValue.setValue(nameValue[1].trim());
                    result.add(nameRelValue);
                } else {
                    throw new IllegalArgumentException("Invalid format. Expected format 'a:b'");
                }
            }
        }

        return result;
    }


    @Override
    public WriteCellData<String> convertToExcelData(List<NameRelValue<String>> value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        if (value == null || value.isEmpty()) {
            return new WriteCellData<>("");
        }

        // 将 List<NameRelValue<String>> 转换为 "name1:value1,name2:value2" 格式的字符串
        StringBuilder joinedString = new StringBuilder();
        for (NameRelValue<String> nameRelValue : value) {
            if (joinedString.length() > 0) {
                joinedString.append(",");
            }
            joinedString.append(nameRelValue.getName()).append(":").append(nameRelValue.getValue());
        }

        log.info("转换 List<NameRelValue<String>> 为字符串: {}", joinedString.toString());
        return new WriteCellData<>(joinedString.toString());
    }
}

记得在对象也要加converter

try {
            excelReader.read(readSheet);
        } catch (Exception e) {
            logger.error("读取 Excel 文件时发生错误：" + e.getMessage());
        } finally {
            if(excelReader != null){
                excelReader.finish();
            }
        }

写出错误文件

boolean error = false;
        File errorExcel = new File("D:\\errorExcel.xlsx");
        if(!errorExcel.exists()){
            try {
                errorExcel.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }


        List<?> errorData = dataReadListener.getErrorData();
        currentImport.setErrorNum(errorData.size());
        currentImport.setTotal(dataReadListener.getTotal());
        if(!ObjectUtils.isEmpty(errorData)){
            error = true;
        }

        if(!ObjectUtils.isEmpty(errorData)){
            WriteSheet writeSheet = EasyExcel.writerSheet(0,dataReadListener.getSheetName())
                    .head(dataReadListener.getSheetClass())
                    .build();
            ExcelWriter excelWritter = EasyExcel.write(errorExcel)
                    .excelType(ExcelTypeEnum.XLSX)
                    .registerConverter(new StringToListConverter())
                    .registerConverter(new StringToListNameRelValueConverter())
                    .build();
            excelWritter.write(errorData,writeSheet);
            excelWritter.finish();
        }

        if(error){
            FileInfo errorFile = new FileInfo(errorExcel.getName(), errorExcel.getPath());
            errorFile.setFileType(FileType.EXCEL);
            errorFile.setFileSize(String.valueOf(currentImport.getErrorNum()));
            currentImport.setErrorDataFile(errorFile);
        }
        currentImport.setStatus(ServerImportStatus.SUCCESS);
        metaControllerProcessMap.put(uploadUid, currentImport);

获取结果

@Operation(summary = "物理机导入结果")
    @PostMapping(value = "/import/getMachineResult")
    public ServerExcelDetailVo getResult(@RequestParam(value = "uploadUid") String uploadUid) {
        return serverUploadService.getReuslt(uploadUid);
    }

@Override
    public ServerExcelDetailVo getReuslt(String uploadUid) {
        ServerExcelImportBo currentImport = metaControllerProcessMap.get(uploadUid);
        ServerExcelDetailVo result = new ServerExcelDetailVo();
        result.setResult(currentImport.getStatus().equals(ServerImportStatus.SUCCESS));
        result.setSuccess(currentImport.getTotal() - currentImport.getErrorNum());
        result.setFail(currentImport.getErrorNum());
        result.setErrorDataFile(currentImport.getErrorDataFile());
        return result;
    }

生成模板

@Operation(summary = "物理机导入-模板下载")
    @PostMapping(value = "/import/machineFile")
    public void downloadMachineFile(HttpServletRequest request, HttpServletResponse response) {
        serverUploadService.downloadMachineFile(request, response);
    }

@Override
    public void downloadMachineFile(HttpServletRequest request, HttpServletResponse response) {
        try {
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=template_machine.xlsx");

            DataReadListener<MachineBasicInfoSheet> dataReadListener = (DataReadListener<MachineBasicInfoSheet>)machineBasicInfoDataReader;
            WriteSheet writeSheet = EasyExcelFactory.writerSheet(0,dataReadListener.getSheetName())
                    .head(dataReadListener.getSheetClass())
                    .build();
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .registerWriteHandler(new MachineExcelStyleHandler(labelManager,serverClusterManager))
                    .build();

            excelWriter.write(new ArrayList<>(),writeSheet);
            excelWriter.finish();

        } catch (IOException exception){
            logger.error("文件生成异常", exception);
            throw new SystemException("文件生成异常");
        }
    }

处理器

package com.sugon.rest.application.utils;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.sugon.core.application.enums.LabelGroup;
import com.sugon.core.application.manager.ServerClusterManager;
import com.sugon.core.auth.manager.LabelManager;
import com.sugon.core.auth.pojo.po.Label;
import com.sugon.core.application.pojo.po.ServerCluster;
import com.sugon.core.auth.util.LoginUserUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;
import java.util.stream.Collectors;

//物理机生成模板样式
@Component
public class MachineExcelStyleHandler implements SheetWriteHandler {

    private final LabelManager labelManager;
    private final ServerClusterManager serverClusterManager;

    public MachineExcelStyleHandler(LabelManager labelManager, ServerClusterManager serverClusterManager) {
        this.labelManager = labelManager;
        this.serverClusterManager = serverClusterManager;
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder,WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();

        // 物理机名称
        addComment(sheet,"必填",0,0);
        // 服务器ip
        addComment(sheet,"必填",0,1);
        // 集群
        addComment(sheet,"必填",0,2);
        String[] clusterOptions = getClusterOptions();
        setDropdownValidation(sheet, dataValidationHelper, clusterOptions,2);
        // 物理机品牌
        String[] brandOptions = getBrandOptions();
        setDropdownValidation(sheet, dataValidationHelper, brandOptions,3);
        // 物理机型号

        // CPU(核)
        addComment(sheet,"要求填写整数",0,5);
        setIntegerValidation(sheet, dataValidationHelper, 5);
        // 内存(GB)
        addComment(sheet,"要求填写小数",0,6);
        setDecimalValidation(sheet, dataValidationHelper, 6);
        // 存储(GB)
        addComment(sheet,"要求填写小数",0,7);
        setDecimalValidation(sheet, dataValidationHelper, 7);
        // 操作系统版本
        String[] osOptions = getOsOptions();
        setDropdownValidation(sheet, dataValidationHelper, osOptions,8);
        // 网络情况
        addComment(sheet,"多项网络情况请以 , 进行分隔",0,9);
        String[] networkOptions = getNetworkOptions();
        setDropdownValidation(sheet, dataValidationHelper, networkOptions,9);
        // 域名

        // 标签
        addComment(sheet,"请按照填写格式，如：a:b,c:d",0,11);
        // 机柜信息

        // 用途


    }

    private void setIntegerValidation(Sheet sheet, DataValidationHelper validationHelper, int columnIndex) {
        CellRangeAddressList addressList = new CellRangeAddressList(1, 1000, columnIndex, columnIndex);
        DataValidationConstraint constraint = validationHelper.createIntegerConstraint(
                DataValidationConstraint.OperatorType.GREATER_THAN, "0", null);
        DataValidation validation = validationHelper.createValidation(constraint, addressList);

        validation.setEmptyCellAllowed(true);
        validation.createErrorBox("输入错误", "请输入大于0的整数");
        validation.setShowErrorBox(true);

        sheet.addValidationData(validation);
    }

    private void setDecimalValidation(Sheet sheet, DataValidationHelper validationHelper, int columnIndex) {
        CellRangeAddressList addressList = new CellRangeAddressList(1, 1000, columnIndex, columnIndex);
        DataValidationConstraint constraint = validationHelper.createDecimalConstraint(
                DataValidationConstraint.OperatorType.GREATER_THAN, "0", null);
        DataValidation validation = validationHelper.createValidation(constraint, addressList);

        validation.setEmptyCellAllowed(true);
        validation.createErrorBox("输入错误", "请输入大于0的小数");
        validation.setShowErrorBox(true);

        sheet.addValidationData(validation);
    }

    private void setDropdownValidation(Sheet sheet, DataValidationHelper validationHelper, String[] options, int columnIndex) {
        CellRangeAddressList addressList = new CellRangeAddressList(1, 1000, columnIndex, columnIndex);
        DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(options);
        DataValidation validation = validationHelper.createValidation(constraint, addressList);
        sheet.addValidationData(validation);
    }

    private void addComment(Sheet sheet, String commentText, int row, int column) {
        Row targetRow = sheet.getRow(row);
        if(targetRow == null) {
            targetRow = sheet.createRow(row);
        }
        Cell targetCell = targetRow.getCell(column);
        if(targetCell == null) {
            targetCell = targetRow.createCell(column);
        }

        Drawing<?> drawing = sheet.createDrawingPatriarch();
        CreationHelper factory = sheet.getWorkbook().getCreationHelper();
        ClientAnchor anchor = factory.createClientAnchor();
        anchor.setCol1(column);
        anchor.setRow1(row);
        Comment comment = drawing.createCellComment(anchor);
        RichTextString str = factory.createRichTextString(commentText);
        comment.setString(str);
        targetCell.setCellComment(comment);
    }

    //获取品牌选项
    private String[] getBrandOptions() {
        List<String> brandOptions = labelManager.lambdaQuery()
                .eq(Label::getTenantId, LoginUserUtil.getTenantId())
                .eq(Label::getDelete, false)
                .eq(Label::getGroup, LabelGroup.BRAND)
                .list()
                .stream()
                .map(Label::getName)
                .collect(Collectors.toList());
        return brandOptions.toArray(new String[0]);
    }


    //获取网络选项
    private String[] getNetworkOptions() {
        List<String> networkOptions = labelManager.lambdaQuery()
                .eq(Label::getTenantId, LoginUserUtil.getTenantId())
                .eq(Label::getDelete, false)
                .eq(Label::getGroup, LabelGroup.NETWORK)
                .list()
                .stream()
                .map(Label::getName)
                .collect(Collectors.toList());
        return networkOptions.toArray(new String[0]);
    }

    //获取操作系统选项
    private String[] getOsOptions() {
        List<String> osOptions = labelManager.lambdaQuery()
                .eq(Label::getTenantId, LoginUserUtil.getTenantId())
                .eq(Label::getDelete, false)
                .eq(Label::getGroup, LabelGroup.OPERATING_SYSTEM)
                .list()
                .stream()
                .map(Label::getName)
                .collect(Collectors.toList());
        return osOptions.toArray(new String[0]);
    }

    //获取集群选项
    private String[] getClusterOptions() {
        List<String> clusterOptions = serverClusterManager.lambdaQuery()
                .eq(ServerCluster::getTenantId, LoginUserUtil.getTenantId())
                .eq(ServerCluster::getDelete, false)
                .list()
                .stream()
                .map(ServerCluster::getName)
                .collect(Collectors.toList());
        return clusterOptions.toArray(new String[0]);
    }
}