SSM整合POI导出excel .xlsx文件

SSM框架结合Apache POI实现Excel文件导入导出示例 SSM框架,即Spring、SpringMVC和MyBatis的集成,是JavaWeb开发领域的经典组合。它将企业级应用开发中的依赖注入、Web层处理、和数据持久化三大核心功能整合在一起,形成了一个简洁且强大的开发体系。在此节中,我们可以分享一个实际的案例,描述在特定项目中,如何通过整合SSM框架和Apache POI实现Excel数据导入导出的功能。具体包括:项目背景和业务需求采用的整合方案和实现步骤整合过程中遇到的问题和解决方案功能实现后的测试结果和性能评估。 阅读详情

依赖

 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

controller

package com.zz.common.controller;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import com.alibaba.fastjson.JSONObject;
import com.zz.bigscreen.hefei.data.ResultVo;
import com.zz.common.enumData.AreaAuthorityEnum;
import com.zz.common.exception.CommonException;
import com.zz.common.model.TSyCourtInfo;
import com.zz.common.model.UUser;
import com.zz.common.service.UploadService;
import com.zz.common.utils.StringUtils;
import com.zz.common.viewdata.UserQo;
import com.zz.core.aop.annotation.QueryAnnotation;
import com.zz.qx.helper.SDFFactory;
import com.zz.user.controller.UserLoginController;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.zz.common.utils.ImageCommonUtils;
import com.zz.common.utils.L;
import com.zz.common.viewdata.UploadInfo;

import javax.servlet.http.HttpServletResponse;

/**
 * 
 * @author Wang
 * 
 */
@Controller
@RequestMapping({"/upload"})
public class UploadController{
	private static Logger log = L.uploadImg();

	@Autowired
	private UploadService uploadService;


	@RequestMapping(value = "/importRecord")
	@ResponseBody
	@QueryAnnotation()
	public ResultVo importRecord(TSyCourtInfo info, UploadInfo item) {
		ResultVo vo = null;
		try {
			if(validateAuthority(UserLoginController.token,info)){
				return new ResultVo(403,"权限不足:NO_AUTHORITY");
			}
			if(item.getFile() != null){
				vo = uploadService.importRecord(item);
			}else{
				vo = new ResultVo(404,"上传文件不存在!");
			}
		}
		catch (CommonException e) {
			vo = new ResultVo(e.getCode(),"上传失败,信息如下:" + e.getMessage());
			e.printStackTrace();
		}
		catch (Exception e) {
			vo = new ResultVo(500,"上传失败,信息如下:" + e.getMessage());
			e.printStackTrace();
		}
		return vo;
	}


	@RequestMapping(value = "/exportRecord")
	@QueryAnnotation()
	public void exportRecord(TSyCourtInfo info, HttpServletResponse response) {
		try {
			if(validateAuthority(UserLoginController.token,info)){
				response.getWriter().print("权限不足:NO_AUTHORITY");
				return;
			}
			Map<String,String> parMap = new HashMap<>();
			setParameters(info, parMap);
			Workbook wb = uploadService.exportRecord(parMap);
			String filename = "设备详细信息";
			String fileName = new String(filename.getBytes("UTF-8"), "iso8859-1") + SDFFactory.DATETIME_DASH.format(new Date()) + ".xlsx";
			response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
			response.setHeader("Content-disposition", "attachment;filename=" + fileName);
			OutputStream outputStream = response.getOutputStream();
			wb.write(outputStream);
			outputStream.flush();
			outputStream.close();
		}
		catch (Exception e) {
			try {
				response.getWriter().print(e.getMessage());
			} catch (IOException ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
		}
	}

	private void setParameters(TSyCourtInfo info, Map<String, String> parMap) {
		if (StringUtils.isNotEmpty(info.getProvinceId())) {
			parMap.put("provinceId", info.getProvinceId());
		}
		if (StringUtils.isNotEmpty(info.getCityId())) {
			parMap.put("cityId", info.getCityId());
		}
		if (StringUtils.isNotEmpty(info.getDistrictId())) {
			parMap.put("districtId", info.getDistrictId());
		}
		if (StringUtils.isNotEmpty(info.getCourtId())) {
			parMap.put("courtId", info.getCourtId());
		}
	}

	@RequestMapping(value = "/getDeviceCount")
	@ResponseBody
	@QueryAnnotation()
	public ResultVo getDeviceCount(UserQo qo ,TSyCourtInfo info) {
		ResultVo vo = null;
		if(validateAuthority(UserLoginController.token,info)){
			return new ResultVo(403,"权限不足:NO_AUTHORITY");
		}
		try {
			Map<String,String> parMap = new HashMap<>();
			setParameters(info, parMap);
			vo = uploadService.getDeviceCount(parMap);
		}
		catch (Exception e) {
			vo = new ResultVo(500,"导出失败,信息如下:" + e.getMessage());
			e.printStackTrace();
		}
		return vo;
	}

	@RequestMapping(value = "/removeRecord")
	@ResponseBody
	@QueryAnnotation()
	public ResultVo removeRecord(UserQo qo ,TSyCourtInfo info) {
		ResultVo vo = null;
		if(validateAuthority(UserLoginController.token,info)){
			return new ResultVo(403,"权限不足:NO_AUTHORITY");
		}
		try {
			Map<String,String> parMap = new HashMap<>();
			setParameters(info, parMap);
			vo = uploadService.removeRecord(parMap);
		}
		catch (Exception e) {
			vo = new ResultVo(500,"清除失败,信息如下:" + e.getMessage());
			e.printStackTrace();
		}
		return vo;
	}

	/**
	 * 判断当前用户是否具有操作选中小区的权限
	 * @param qo
	 * @param info
	 * @return
	 */
	private boolean validateAuthority(UUser qo, TSyCourtInfo info) {
		boolean flag = false;
		/**
		 * 0 全国
		 * 1 省
		 * 2 市
		 * 3 区县
		 * 4 小区
		 */
		String areatype = qo.getAreatype();
		String provinceId = qo.getProvinceId();
		String cityId = qo.getCityId();
		String districtId = qo.getDistrictId();
		String courtId = qo.getCourtId();
		if(AreaAuthorityEnum.PROVINCE.type.equals(areatype)){
			if(!info.getProvinceId().equals(provinceId)){
				flag = true;
			}
		}else if(AreaAuthorityEnum.CITY.type.equals(areatype)){
			if(!info.getProvinceId().equals(provinceId) || !info.getCityId().equals(cityId)){
				flag = true;
			}
		}else if(AreaAuthorityEnum.DISTRICT.type.equals(areatype)){
			if(!info.getProvinceId().equals(provinceId) || !info.getCityId().equals(cityId) || !info.getDistrictId().equals(districtId)){
				flag = true;
			}
		}else if(AreaAuthorityEnum.COURT.type.equals(areatype)){
			if(!info.getProvinceId().equals(provinceId) || !info.getCityId().equals(cityId) || !info.getDistrictId().equals(districtId) || !info.getCourtId().equals(courtId)){
				flag = true;
			}
		}
		return flag;
	}

}

service

 

package com.zz.common.impl;

import com.zz.bigscreen.hefei.data.ResultVo;
import com.zz.common.dao.*;
import com.zz.common.data.TopoExcelInfo;
import com.zz.common.enumData.DtuTypeEnum;
import com.zz.common.enumData.EpuTypeEnum;
import com.zz.common.enumData.ErrorDataEnum;
import com.zz.common.enumData.OperationInfoEnum;
import com.zz.common.exception.CommonException;
import com.zz.common.model.*;
import com.zz.common.service.UploadService;
import com.zz.common.viewdata.UploadInfo;
import com.zz.qx.service.QxThemeResultService;
import com.zz.user.controller.UserLoginController;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.awt.Color;

/**
 * description: UploadServiceImpl
 * date: 2020/3/16 18:17
 *
 * @author: Wang
 * version: 1.0
 */
@Service
public class UploadServiceImpl implements UploadService {

    /**
     * 批处理最大的size
     */
    private static final Integer BATCH_MAX_NUM = 10;

    /**
     * bdtu设备excel的title
     */
    private static final String [] BDTU_TITLES = {"省份","城市","区县","小区名","小区地址","箱变名","箱变位置","箱变容量","出线柜名","出线柜位置","BDTU区域号","BDTU地址号","BDTU通道数","分支箱名","分支箱位置","分支箱容量","BDTU通道号1","BDTU通道号2","BDTU通道号3","线缆号","表箱号","表箱位置","设备位置对应编码","MDTU区域号","MDTU地址号","MDTU通道数","电表名","电表号","门牌号","相别","NDTU终端通道号","漏电流通道号"};

    /**
     * 区域对应索引位置
     */
    private static final int [] AREA_IDX = {0,4};

    /**
     * 箱变对应索引位置
     */
    private static final int [] SUBSTAIN_IDX = {5,7};

    /**
     * 出线柜对应索引位置
     */
    private static final int [] OUT_IDX = {8,12};

    /**
     * 分支箱对应索引位置
     */
    private static final int [] BRANCH_IDX = {13,19};

    /**
     * 表箱对应索引位置
     */
    private static final int [] METERBOX_IDX = {20,25};

    /**
     * 电表对应索引位置
     */
    private static final int [] METER_IDX = {26,31};

    @Autowired
    private TSyCourtInfoMapper tSyCourtInfoMapper;

    @Autowired
    private TSubstainMapper tSubstainMapper;

    @Autowired
    private TMeterboxMapper tMeterboxMapper;

    @Autowired
    private TMeterMapper tMeterMapper;

    @Autowired
    private TOutgoingcabinetMapper tOutgoingcabinetMapper;

    @Autowired
    private TBranchboxMapper tBranchboxMapper;

    @Autowired
    private DeviceinfoMapper deviceinfoMapper;

    @Autowired
    private T_FROZENTOPO_FINALMapper frozentopo_finalMapper;

    @Autowired
    private T_FROZENTOPO_FINAL_STATUSMapper frozentopoFinalStatusMapper;

    @Autowired
    private QxThemeResultService qxThemeResultService;


    /**
     * 进度紧迫。只对表箱、电表、topo_final做批量处理
     * @param uploadInfo
     * @return
     * @throws IOException
     * @throws CommonException
     */
    @Override
    @Transactional
    public ResultVo importRecord(UploadInfo uploadInfo) throws IOException, CommonException {
        // 权限校验暂时未作
        // 当前操作用户id,  用于createId
        String uId = UserLoginController.userId;
        MultipartFile file = uploadInfo.getFile();
        InputStream in = file.getInputStream();
        //创建工作簿
        XSSFWorkbook wb = new XSSFWorkbook(in);
        //读取第一个sheet
        XSSFSheet sheet = wb.getSheetAt(0);
        Map<String, Map<String,List<TSyCourtInfo>>> courtMap = new HashMap<>();
        Map<String, Map<String,List<TSubstain>>> subMap = new HashMap<>();
        Map<String, Map<String,List<TOutgoingcabinet>>> outMap = new HashMap<>();
        Map<String, Map<String,List<TBranchbox>>> braMap = new HashMap<>();
        Map<String, Map<String,List<TMeterbox>>> mbMap = new HashMap<>();
        Map<String, Map<String,List<TMeter>>> mMap = new HashMap<>();
        Map<String, Dtu> deviceMap = new HashMap<>();
        Map<String,List<T_FROZENTOPO_FINAL>> topoMap = new HashMap<>();

        // key 为 电表号,value 门牌号集合,超过两个或三个做处理
        Map<String, List<String>> meterNos = new HashMap<>();
        String subId = null;
        String outId = null;
        String braId = null;
        String mbId = null;
        String mId = null;
        TSyCourtInfo areaIds = null;
        // 循环行,跳过第一行
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {

            Row row = sheet.getRow(i);
            // 小区
            String province = row.getCell(0).toString();
            String city = row.getCell(1).toString();
            String district = row.getCell(2).toString();
            String courtName = row.getCell(3).toString();
            String address = row.getCell(4).toString();

            Map<String,String> courtPar = new HashMap<>();
            courtPar.put("provinceName",province);
            courtPar.put("cityName",city);
            courtPar.put("districtName",district);


            /**
             *  档案入库前,检查记录是否已经存在,如果存在则执行update,如果不存在执行insert。
             * 1.     检查数据库中小区信息是否存在(省、市、区、小区已经存在);
             * 2.     检查数据库中箱变信息是否存在(省、市、区、小区、终端区域号、终端地址号已经存在);
             * 3.     检查数据库中出线柜信息是否存在(省、市、区、小区、终端区域号、终端地址号已经存在);
             * 4.     检查数据库中分支箱信息是否存在(省、市、区、小区、分支箱名称、终端区域号、终端地址号已经存在);
             * 5.     检查数据库中表箱信息是否存在(省、市、区、小区、终端区域号、终端地址号已经存在);
             * 6.     检查数据库中电表信息是否存在(省、市、区、小区、终端区域号、终端地址号、终端通道号已经存在);
             *
             */
            Map<String,String> par = new HashMap<>();
            Map<String, List<TSyCourtInfo>> courts = courtMap.get(province + "-" + city + "-" + district + "-" + courtName);
            if(courts == null){
                courts = new HashMap<>();

                areaIds = tSyCourtInfoMapper.getAreaInfo(courtPar);
                if(areaIds == null){
                    throw new CommonException(ErrorDataEnum.ERR_120008.error,ErrorDataEnum.ERR_120008.code);
                }

                par.put("provinceId",areaIds.getProvinceId());
                par.put("cityId",areaIds.getCityId());
                par.put("districtId",areaIds.getDistrictId());
                par.put("courtName",courtName);
//                par.put("courtAddress",address);
                TSyCourtInfo courtInfo = tSyCourtInfoMapper.selectByParameters(par);
                if(courtInfo != null) {
                    // 存在更新
                    List<TSyCourtInfo> tSyCourtInfos = courts.get(OperationInfoEnum.UPDATE.getValue());
                    if(tSyCourtInfos == null){
                        tSyCourtInfos = new ArrayList<>();
                    }

                    courtInfo.setCourtNameCn(courtName);
                    courtInfo.setCourtAddress(address);
                    tSyCourtInfos.add(courtInfo);
                    courts.put(OperationInfoEnum.UPDATE.getValue(),tSyCourtInfos);

                }else{
                    // 不存在标识为插入
                    List<TSyCourtInfo> tSyCourtInfos = courts.get(OperationInfoEnum.INSERT.getValue());
                    if(tSyCourtInfos == null){
                        tSyCourtInfos = new ArrayList<>();
                    }
                    courtInfo = new TSyCourtInfo();
                    courtInfo.setCourtId(UUID.randomUUID().toString());
                    areaIds.setCourtId(courtInfo.getCourtId());
                    courtInfo.setCourtNameCn(courtName);
                    courtInfo.setCourtAddress(address);
                    courtInfo.setProvinceId(areaIds.getProvinceId());
                    courtInfo.setCityId(areaIds.getCityId());
                    courtInfo.setDistrictId(areaIds.getDistrictId());
                    courtInfo.setCreateDate(new Date());
                    tSyCourtInfos.add(courtInfo);
                    courts.put(OperationInfoEnum.INSERT.getValue(),tSyCourtInfos);
                }
                areaIds.setCourtId(courtInfo.getCourtId());
                courtMap.put(province + "-" + city + "-" + district + "-" + courtName,courts);
            }

            // 箱变
            String subName = row.getCell(5).toString();
            String subLocal = row.getCell(6).toString();
            String subCapacity = replaceStr(row.getCell(7).toString());

            // 出线柜
            String outName = row.getCell(8).toString();
            String outLocal = row.getCell(9).toString();
            String bdtuDistrict = replaceStr(row.getCell(10).toString());
            String bdtuAddr = replaceStr(row.getCell(11).toString());
            String bdtuChannelNum = replaceStr(row.getCell(12).toString());

            // 分支箱
            String branchName = row.getCell(13).toString();
            String branchLocal = row.getCell(14).toString();
            String branchCapacity = replaceStr(row.getCell(15).toString());
            String btduChannelOne = replaceStr(row.getCell(16).toString());
            String btduChannelTwo = replaceStr(row.getCell(17).toString());
            String btduChannelThree = replaceStr(row.getCell(18).toString());
            // 线缆号   默认给1
            String lineNum = replaceStr(row.getCell(19) == null ? "1" : row.getCell(19).toString());

            // 终端设备 BDTU
            Dtu bdtu = deviceMap.get(bdtuDistrict + "-" + bdtuAddr);
            if(bdtu == null){
                Map<String,String> parameter = new HashMap<>();
                parameter.put("bdtuDistrict",bdtuDistrict);
                parameter.put("bdtuAddr",bdtuAddr);
                par.put("devType","1");
                bdtu = deviceinfoMapper.selectByParameters(parameter);
                if(bdtu == null){
                    bdtu = new Dtu();
                    bdtu.setId(deviceinfoMapper.getNextval());
                    bdtu.setcDistrictbcdid(bdtuDistrict);
                    bdtu.setcAddressid(bdtuAddr);
                    bdtu.setcChannelnum(Integer.parseInt(bdtuChannelNum));
                    bdtu.setDevType(1);
                    deviceMap.put(bdtuDistrict + "-" + bdtuAddr,bdtu);
                }
            }

            Map<String, List<TSubstain>> substains = subMap.get(province + "-" + city + "-" + district + "-" + courtName + "-" + subName + "-" + subLocal);
            TSubstain tSubstain = null;
            if(substains == null){
                substains = new HashMap<>();
                par = new HashMap<>();
                par.put("subName",subName);
//                par.put("bdtuDistrict",bdtuDistrict);
//                par.put("bdtuAddr",bdtuAddr);
                par.put("provinceId",areaIds.getProvinceId());
                par.put("cityId",areaIds.getCityId());
                par.put("districtId",areaIds.getDistrictId());
                par.put("courtId",areaIds.getCourtId());
                tSubstain = tSubstainMapper.selectByParameters(par);
                if(tSubstain != null){
                    // 存在更新
                    List<TSubstain> tSubstains = substains.get(OperationInfoEnum.UPDATE.getValue());
                    if(tSubstains == null){
                        tSubstains = new ArrayList<>();
                    }
                    setSubstainData(subName, subLocal, subCapacity, bdtuDistrict, bdtuAddr, btduChannelOne, btduChannelTwo, btduChannelThree, tSubstain);
                    tSubstain.setUPDATE_ID(uId);
                    tSubstain.setUPDATE_TIME(new Date());
                    tSubstains.add(tSubstain);
                    substains.put(OperationInfoEnum.UPDATE.getValue(),tSubstains);
                }else{
                    // 不存在标识为插入
                    List<TSubstain> tSubstains = substains.get(OperationInfoEnum.UPDATE.getValue());
                    if(tSubstains == null){
                        tSubstains = new ArrayList<>();
                    }
                    tSubstain = new TSubstain();
                    /**
                     * 0为BDTU,1为TDTU
                     * 目前只考虑BDTU设备
                     */
                    tSubstain.setTdtuFlag(0);
                    tSubstain.setEpu_province(areaIds.getProvinceId());
                    tSubstain.setEpu_city(areaIds.getCityId());
                    tSubstain.setEpu_district(areaIds.getDistrictId());
                    tSubstain.setEpuCourt(areaIds.getCourtId());
                    tSubstain.setSubstainId(UUID.randomUUID().toString());
                    tSubstain.setCREATE_TIME(new Date());
                    tSubstain.setCREATE_ID(uId);

                    setSubstainData(subName, subLocal, subCapacity, bdtuDistrict, bdtuAddr, btduChannelOne, btduChannelTwo, btduChannelThree, tSubstain);
                    tSubstains.add(tSubstain);
                    substains.put(OperationInfoEnum.INSERT.getValue(),tSubstains);
                }
                subId = tSubstain.getSubstainId();
                subMap.put(province + "-" + city + "-" + district + "-" + courtName + "-" + subName + "-" + subLocal,substains);
            }

            Map<String, List<TOutgoingcabinet>> outs = outMap.get(outName + "-" + outLocal + "-" + bdtuDistrict + "-" + bdtuAddr);
            TOutgoingcabinet outgoingcabinet = null;
            if(outs == null){
                outs = new HashMap<>();
                par = new HashMap<>();
//                par.put("outName",outName);
//                par.put("outLocal",outLocal);
                par.put("bdtuDistrict",bdtuDistrict);
                par.put("bdtuAddr",bdtuAddr);
                par.put("provinceId",areaIds.getProvinceId());
                par.put("cityId",areaIds.getCityId());
                par.put("districtId",areaIds.getDistrictId());
                par.put("courtId",areaIds.getCourtId());
                outgoingcabinet = tOutgoingcabinetMapper.selectByParameters(par);
                if(outgoingcabinet != null){
                    // 存在更新
                    List<TOutgoingcabinet> tOutgoingcabinets = outs.get(OperationInfoEnum.UPDATE.getValue());
                    if(tOutgoingcabinets == null){
                        tOutgoingcabinets = new ArrayList<>();
                    }
                    setOutgoingData(outName, outLocal, bdtuDistrict, bdtuAddr, bdtuChannelNum, outgoingcabinet);
                    outgoingcabinet.setUPDATE_ID(uId);
                    outgoingcabinet.setUPDATE_TIME(new Date());
                    tOutgoingcabinets.add(outgoingcabinet);
                    outs.put(OperationInfoEnum.UPDATE.getValue(),tOutgoingcabinets);

                }else{
                    // 不存在标识为插入
                    List<TOutgoingcabinet> tOutgoingcabinets = outs.get(OperationInfoEnum.INSERT.getValue());
                    if(tOutgoingcabinets == null){
                        tOutgoingcabinets = new ArrayList<>();
                    }
                    outgoingcabinet = new TOutgoingcabinet();
                    outgoingcabinet.setOutgoingCabinetId(UUID.randomUUID().toString());
                    outgoingcabinet.setSubstainId(subId);
                    setOutgoingData(outName, outLocal, bdtuDistrict, bdtuAddr, bdtuChannelNum, outgoingcabinet);
                    outgoingcabinet.setCREATE_TIME(new Date());
                    outgoingcabinet.setCREATE_ID(uId);
                    outgoingcabinet.setDEL_FLAG("0");
                    outgoingcabinet.setEpu_province(areaIds.getProvinceId());
                    outgoingcabinet.setEpu_city(areaIds.getCityId());
                    outgoingcabinet.setEpu_district(areaIds.getDistrictId());
                    outgoingcabinet.setEPU_COURT(areaIds.getCourtId());
                    setOutgoingData(outName, outLocal, bdtuDistrict, bdtuAddr, bdtuChannelNum, outgoingcabinet);
                    tOutgoingcabinets.add(outgoingcabinet);
                    outs.put(OperationInfoEnum.INSERT.getValue(),tOutgoingcabinets);
                }
                outId = outgoingcabinet.getOutgoingCabinetId();
                outMap.put(outName + "-" + outLocal + "-" + bdtuDistrict + "-" + bdtuAddr,outs);
            }

            Map<String, List<TBranchbox>> branchs = braMap.get(branchName + "-" + branchLocal + "-" + bdtuDistrict + "-" + bdtuAddr);
            TBranchbox branchbox = null;
            if(branchs == null){
                branchs = new HashMap<>();
                par = new HashMap<>();
//                par.put("branchName",branchName);
//                par.put("branchLocal",branchLocal);
                par.put("bdtuDistrict",bdtuDistrict);
                par.put("bdtuAddr",bdtuAddr);
                par.put("btduChannelOne",btduChannelOne);
                par.put("btduChannelTwo",btduChannelTwo);
                par.put("btduChannelThree",btduChannelThree);
                par.put("provinceId",areaIds.getProvinceId());
                par.put("cityId",areaIds.getCityId());
                par.put("districtId",areaIds.getDistrictId());
                par.put("courtId",areaIds.getCourtId());
                branchbox = tBranchboxMapper.selectByParameters(par);
                if(branchbox != null){
                    // 存在更新
                    List<TBranchbox> tBranchboxes = branchs.get(OperationInfoEnum.UPDATE.getValue());
                    if(tBranchboxes == null){
                        tBranchboxes = new ArrayList<>();
                    }
                    setBranchboxData(bdtuDistrict, bdtuAddr, branchName, branchLocal, branchCapacity, btduChannelOne, btduChannelTwo, btduChannelThree, lineNum, branchbox);

                    branchbox.setUPDATE_ID(uId);
                    branchbox.setUPDATE_TIME(new Date());
                    tBranchboxes.add(branchbox);
                    branchs.put(OperationInfoEnum.UPDATE.getValue(),tBranchboxes);

                }else{
                    // 不存在标识为插入
                    List<TBranchbox> tBranchboxes = branchs.get(OperationInfoEnum.INSERT.getValue());
                    if(tBranchboxes == null){
                        tBranchboxes = new ArrayList<>();
                    }
                    branchbox = new TBranchbox();
                    branchbox.setBranchBoxId(UUID.randomUUID().toString());
                    branchbox.setOutgoingCabinetId(outId);
                    setBranchboxData(bdtuDistrict, bdtuAddr, branchName, branchLocal, branchCapacity, btduChannelOne, btduChannelTwo, btduChannelThree, lineNum, branchbox);
                    branchbox.setCREATE_TIME(new Date());
                    branchbox.setCREATE_ID(uId);
                    branchbox.setDEL_FLAG("0");
                    branchbox.setEpu_province(areaIds.getProvinceId());
                    branchbox.setEpu_city(areaIds.getCityId());
                    branchbox.setEpu_district(areaIds.getDistrictId());
                    branchbox.setEpu_court(areaIds.getCourtId());
                    tBranchboxes.add(branchbox);
                    branchs.put(OperationInfoEnum.INSERT.getValue(),tBranchboxes);
                }
                braId = branchbox.getBranchBoxId();
                braMap.put(branchName + "-" + branchLocal + "-" + bdtuDistrict + "-" + bdtuAddr,branchs);
            }

            // 表箱
            String meterboxName = row.getCell(20).toString();
            // 表箱位置
            String mbLocal = row.getCell(21).toString();
            // 设备对应编码
            String idx = row.getCell(22).toString();
            String mdtuDistrict = replaceStr(row.getCell(23).toString());
            String mdtuAddress = replaceStr(row.getCell(24).toString());
            String mdtuChannelNum = replaceStr(row.getCell(25).toString());

            // 终端设备 MDTU
            Dtu mdtu = deviceMap.get(mdtuDistrict + "-" + mdtuAddress);
            if(mdtu == null){
                par = new HashMap<>();
                par.put("bdtuDistrict",mdtuDistrict);
                par.put("bdtuAddr",mdtuAddress);
                par.put("devType","0");
                mdtu = deviceinfoMapper.selectByParameters(par);
                if(mdtu == null){
                    mdtu = new Dtu();
                    mdtu.setId(deviceinfoMapper.getNextval());
                    mdtu.setcDistrictbcdid(mdtuDistrict);
                    mdtu.setcAddressid(mdtuAddress);
                    mdtu.setcChannelnum(Integer.parseInt(mdtuChannelNum));
                    mdtu.setDevType(0);
                    deviceMap.put(mdtuDistrict + "-" + mdtuAddress,mdtu);
                }
            }

            Map<String, List<TMeterbox>> meterboxes = mbMap.get(meterboxName + "-" + mbLocal + "-" + mdtuDistrict + "-" + mdtuAddress);
            TMeterbox meterbox = null;
            if(meterboxes == null){
                meterboxes = new HashMap<>();
                par = new HashMap<>();
//                par.put("meterboxName",meterboxName);
//                par.put("mbLocal",mbLocal);
                par.put("mdtuDistrict",mdtuDistrict);
                par.put("mdtuAddress",mdtuAddress);
                par.put("provinceId",areaIds.getProvinceId());
                par.put("cityId",areaIds.getCityId());
                par.put("districtId",areaIds.getDistrictId());
                par.put("courtId",areaIds.getCourtId());
                meterbox = tMeterboxMapper.selectByParameters(par);
                if(meterbox != null){
                    // 存在更新
                    List<TMeterbox> tMeterboxes = meterboxes.get(OperationInfoEnum.UPDATE.getValue());
                    if(tMeterboxes == null){
                        tMeterboxes = new ArrayList<>();
                    }
                    setMeterboxData(idx, meterboxName, mbLocal, mdtuDistrict, mdtuAddress, mdtuChannelNum, meterbox);
                    meterbox.setUPDATE_ID(uId);
                    meterbox.setUPDATE_TIME(new Date());
                    tMeterboxes.add(meterbox);
                    meterboxes.put(OperationInfoEnum.UPDATE.getValue(),tMeterboxes);

                }else{
                    // 不存在标识为插入
                    List<TMeterbox> tMeterboxes = meterboxes.get(OperationInfoEnum.INSERT.getValue());
                    if(tMeterboxes == null){
                        tMeterboxes = new ArrayList<>();
                    }
                    meterbox = new TMeterbox();
                    meterbox.setMeterBoxId(UUID.randomUUID().toString());
                    meterbox.setBranchBoxId(braId);
                    setMeterboxData(idx, meterboxName, mbLocal, mdtuDistrict, mdtuAddress, mdtuChannelNum, meterbox);
                    meterbox.setCREATE_TIME(new Date());
                    meterbox.setCREATE_ID(uId);
                    meterbox.setDEL_FLAG("0");
                    meterbox.setEpu_province(areaIds.getProvinceId());
                    meterbox.setEpu_city(areaIds.getCityId());
                    meterbox.setEpu_district(areaIds.getDistrictId());
                    meterbox.setEPU_COURT(areaIds.getCourtId());
                    tMeterboxes.add(meterbox);
                    meterboxes.put(OperationInfoEnum.INSERT.getValue(),tMeterboxes);
                }
                mbId = meterbox.getMeterBoxId();
                mbMap.put(meterboxName + "-" + mbLocal + "-" + mdtuDistrict + "-" + mdtuAddress,meterboxes);

            }

            // 电表
            String meterName = row.getCell(26).toString();
            String meterNo = row.getCell(27).toString();
            String houseId = row.getCell(28).toString();
            // 相别 ABC(1,2,3)
            String phase = row.getCell(29).toString();
            // (ndtu通道号)电流通道号
            String cChannelId = replaceStr(row.getCell(30).toString());
            // 漏电流通道号  默认值给1
            String leakChannelId = replaceStr(row.getCell(31) == null ? "1" : row.getCell(31).toString());

            List<String> houseIds = meterNos.get(meterNo);
            if(houseIds == null){
                houseIds = new ArrayList<>();
            }
            // 此处不用房间号,使用电表唯一标识
            houseIds.add(mdtuDistrict + "-" + mdtuAddress + "-" + cChannelId);
            meterNos.put(meterNo,houseIds);


            Map<String, List<TMeter>> meters = mMap.get(mdtuDistrict + "-" + mdtuAddress + "-" + cChannelId);
            TMeter meter = null;
            if(meters == null){
                meters = new HashMap<>();
                par = new HashMap<>();
                par.put("mdtuDistrict",mdtuDistrict);
                par.put("mdtuAddress",mdtuAddress);
                par.put("cChannelId",cChannelId);
                par.put("courtId",areaIds.getCourtId());
                meter = tMeterMapper.selectByParameters(par);
                if(meter != null){
                    // 存在更新
                    List<TMeter> tMeters = meters.get(OperationInfoEnum.UPDATE.getValue());
                    if(tMeters == null){
                        tMeters = new ArrayList<>();
                    }
                    setMeterData(mdtuDistrict, mdtuAddress, meterName, meterNo, houseId, phase, cChannelId, leakChannelId, meter);

                    meter.setUPDATE_ID(uId);
                    meter.setUPDATE_TIME(new Date());
                    tMeters.add(meter);
                    meters.put(OperationInfoEnum.UPDATE.getValue(),tMeters);

                }else{
                    // 不存在标识为插入
                    List<TMeter> tMeters = meters.get(OperationInfoEnum.INSERT.getValue());
                    if(tMeters == null){
                        tMeters = new ArrayList<>();
                    }
                    meter = new TMeter();
                    meter.setMeterId(UUID.randomUUID().toString());
                    meter.setMeterBoxId(mbId);
                    setMeterData(mdtuDistrict, mdtuAddress, meterName, meterNo, houseId, phase, cChannelId, leakChannelId, meter);
                    meter.setCREATE_TIME(new Date());
                    meter.setCREATE_ID(uId);
                    meter.setDEL_FLAG("0");
                    meter.setEpuCourt(areaIds.getCourtId());
                    tMeters.add(meter);
                    meters.put(OperationInfoEnum.INSERT.getValue(),tMeters);
                }
                mId = meter.getMeterId();
                mMap.put(mdtuDistrict + "-" + mdtuAddress + "-" + cChannelId,meters);
            }

            // frozentopo_final
            T_FROZENTOPO_FINAL topo = new T_FROZENTOPO_FINAL();

            topo.setSubstainid(subId);
            topo.setOutgoingcabinetid(outId);
            topo.setBranchboxid(braId);
            topo.setMeterboxid(mbId);
            topo.setMeterid(mId);
            topo.setSubstainName(subName);
            topo.setOutgoingcabinetName(outName);
            topo.setBranchboxName(branchName);
            topo.setMeterboxName(meterboxName);
            topo.setMeterName(meterName);
            topo.setEpuLocal(address);
            topo.setEpuProvince(areaIds.getProvinceId());
            topo.setEpuCity(areaIds.getCityId());
            topo.setEpuDistrict(areaIds.getDistrictId());
            topo.setEpuCourt(areaIds.getCourtId());
            topo.setInsertTime(new Date());
            //tdtu 设备表示	0为BDTU,1为TDTU
            topo.setTdtuFlag(0);
            // 先插入建档(3),再插入正在使用(2)
            if(meter != null){
                topo.setPhase(Integer.parseInt(meter.getPhase_remark() == null ? "1" :meter.getPhase_remark()));
            }

            List<T_FROZENTOPO_FINAL> finals = topoMap.get(subId);
            if(finals == null){
                finals = new ArrayList<>();
            }
            finals.add(topo);
            topoMap.put(subId,finals);

        }

        // 判断是否存在相同电表
        if(!meterNos.isEmpty()){
            for (String key : meterNos.keySet()){
                List<String> houseIds = meterNos.get(key);
                if(houseIds.size() == 2 || houseIds.size() == 3){
                    if(houseIds.get(0).equals(houseIds.get(1))){
                        throw new CommonException(ErrorDataEnum.ERR_120010.error + ":" + key,ErrorDataEnum.ERR_120010.code);
                    }else if(houseIds.get(0).equals(houseIds.get(1)) && houseIds.get(1).equals(houseIds.get(2))){
                        throw new CommonException(ErrorDataEnum.ERR_120011.error + ":" + key,ErrorDataEnum.ERR_120011.code);
                    }
                }
            }
        }

        Map<String,Object> retMap = new HashMap<>();
        Integer courtInsertNum = 0;
        Integer courtUpdateNum = 0;
        if(!courtMap.isEmpty()){
            for(String key : courtMap.keySet()){
                Map<String, List<TSyCourtInfo>> courts = courtMap.get(key);
                if(courts != null && !courts.isEmpty()){
                    for(String operation : courts.keySet()){
                        List<TSyCourtInfo> tSyCourtInfos = courts.get(operation);
                        if(OperationInfoEnum.INSERT.getValue().equals(operation)){
                            courtInsertNum += tSyCourtInfos.size();
                            tSyCourtInfos.forEach(item -> tSyCourtInfoMapper.insertSelective(item));
                        }else{
                            // 修改
                            courtUpdateNum += tSyCourtInfos.size();
                            tSyCourtInfos.forEach(item -> tSyCourtInfoMapper.updateByPrimaryKeySelective(item));
                        }
                    }
                }
            }
        }
        retMap.put("courtInsertNum",courtInsertNum);
        retMap.put("courtUpdateNum",courtUpdateNum);
        Integer subInsertNum = 0;
        Integer subUpdateNum = 0;
        if(!subMap.isEmpty()){
            for(String key : subMap.keySet()){
                Map<String, List<TSubstain>> subs = subMap.get(key);
                if(subs != null && !subs.isEmpty()){
                    for(String operation : subs.keySet()){
                        List<TSubstain> tSubstains = subs.get(operation);
                        if(OperationInfoEnum.INSERT.getValue().equals(operation)){
                            subInsertNum += tSubstains.size();
                            tSubstains.forEach(item -> tSubstainMapper.insertSelective(item));
                        }else{
                            // 修改
                            subUpdateNum += tSubstains.size();
                            tSubstains.forEach(item -> tSubstainMapper.updateByPrimaryKeySelective(item));
                        }
                    }
                }
            }
        }
        retMap.put("subInsertNum",subInsertNum);
        retMap.put("subUpdateNum",subUpdateNum);
        Integer outUpdateNum = 0;
        Integer outInsertNum = 0;
        if(!outMap.isEmpty()){
            for(String key : outMap.keySet()){
                Map<String, List<TOutgoingcabinet>> outs = outMap.get(key);
                if(outs != null && !outs.isEmpty()){
                    for(String operation : outs.keySet()){
                        List<TOutgoingcabinet> tOutgoingcabinets = outs.get(operation);
                        if(OperationInfoEnum.INSERT.getValue().equals(operation)){
                            outInsertNum += tOutgoingcabinets.size();
                            tOutgoingcabinets.forEach(item -> tOutgoingcabinetMapper.insertSelective(item));
                        }else{
                            // 修改
                            outUpdateNum += tOutgoingcabinets.size();
                            tOutgoingcabinets.forEach(item -> tOutgoingcabinetMapper.updateByPrimaryKeySelective(item));
                        }
                    }
                }
            }

        }
        retMap.put("outInsertNum",outInsertNum);
        retMap.put("outUpdateNum",outUpdateNum);

        Integer braInsertNum = 0;
        Integer braUpdateNum = 0;
        if(!braMap.isEmpty()){

            for(String key : braMap.keySet()){
                Map<String, List<TBranchbox>> bras = braMap.get(key);
                if(bras != null && !bras.isEmpty()){
                    for(String operation : bras.keySet()){
                        List<TBranchbox> tBranchboxes = bras.get(operation);
                        if(OperationInfoEnum.INSERT.getValue().equals(operation)){
                            braInsertNum += tBranchboxes.size();
                            tBranchboxes.forEach(item -> tBranchboxMapper.insertSelective(item));
                        }else{
                            // 修改
                            braUpdateNum += tBranchboxes.size();
                            tBranchboxes.forEach(item -> tBranchboxMapper.updateByPrimaryKeySelective(item));
                        }
                    }
                }
            }

        }
        retMap.put("braInsertNum",braInsertNum);
        retMap.put("braUpdateNum",braUpdateNum);

        List<TMeterbox> batchInsertMb = new ArrayList<>();
        List<TMeterbox> batchUpdateMb = new ArrayList<>();
        Integer mbInsertNum = 0;
        Integer mbUpdateNum = 0;
        if(!mbMap.isEmpty()){

            for(String key : mbMap.keySet()){
                Map<String, List<TMeterbox>> bras = mbMap.get(key);
                if(bras != null && !bras.isEmpty()){
                    for(String operation : bras.keySet()){
                        List<TMeterbox> tMeterboxes = bras.get(operation);
                        if(OperationInfoEnum.INSERT.getValue().equals(operation)){
                            mbInsertNum += tMeterboxes.size();
//                            tMeterboxes.forEach(item -> tMeterboxMapper.insertSelective(item));
                            batchInsertMb.addAll(tMeterboxes);
                        }else{
                            // 修改
                            mbUpdateNum += tMeterboxes.size();
//                            tMeterboxes.forEach(item -> tMeterboxMapper.updateByPrimaryKeySelective(item));
                            batchUpdateMb.addAll(tMeterboxes);
                        }
                    }
                }
            }

        }
        retMap.put("mbInsertNum",mbInsertNum);
        retMap.put("mbUpdateNum",mbUpdateNum);
        batchMeterboxData(batchInsertMb,batchUpdateMb);

        List<TMeter> batchInsertMeter = new ArrayList<>();
        List<TMeter> batchUpdateMeter = new ArrayList<>();
        Integer meterInsertNum = 0;
        Integer meterUpdateNum = 0;
        if(!mMap.isEmpty()){

            for(String key : mMap.keySet()){
                Map<String, List<TMeter>> meters = mMap.get(key);
                if(meters != null && !meters.isEmpty()){
                    for(String operation : meters.keySet()){
                        List<TMeter> tMeters = meters.get(operation);
                        if(OperationInfoEnum.INSERT.getValue().equals(operation)){
                            meterInsertNum += tMeters.size();
//                            tMeters.forEach(item -> tMeterMapper.insertSelective(item));
                            batchInsertMeter.addAll(tMeters);
                        }else{
                            // 修改
                            meterUpdateNum += tMeters.size();
//                            tMeters.forEach(item -> tMeterMapper.updateByPrimaryKeySelective(item));
                            batchUpdateMeter.addAll(tMeters);
                        }
                    }
                }
            }

        }
        retMap.put("meterInsertNum",meterInsertNum);
        retMap.put("meterUpdateNum",meterUpdateNum);

        // batchUpdate not validate
        batchMeterData(batchInsertMeter,batchUpdateMeter);

        Integer dtuInsertNum = 0;
        if(!deviceMap.isEmpty()){

            for(String key : deviceMap.keySet()){
                dtuInsertNum ++;
                Dtu dtu = deviceMap.get(key);
                deviceinfoMapper.insert2(dtu);
            }
        }
        retMap.put("dtuInsertNum",dtuInsertNum);
        if(!topoMap.isEmpty()){
            for(String substainId : topoMap.keySet()){
                List<T_FROZENTOPO_FINAL> finals = topoMap.get(substainId);
                if(!finals.isEmpty()){
                    // 插入切面为3(建档)的数据,
                    long newFrozenNo = frozentopo_finalMapper.getNewFrozenNo();
                    for (T_FROZENTOPO_FINAL aFinal : finals) {
                        aFinal.setFrozenType(3);
                        aFinal.setFrozenNo(newFrozenNo);
                    }
                    frozentopo_finalMapper.batchInsertData(finals);
                    T_FROZENTOPO_FINAL_STATUS status = new T_FROZENTOPO_FINAL_STATUS();
                    status.setSubstainid(substainId);
                    status.setInsertTime(new Date());
                    status.setType(3);
                    status.setFrozenNo(newFrozenNo);
                    frozentopoFinalStatusMapper.insertSelective(status);
                    // 插入切面为2(正在使用)的数据,
                    newFrozenNo = frozentopo_finalMapper.getNewFrozenNo();
                    for (T_FROZENTOPO_FINAL aFinal : finals) {
                        aFinal.setFrozenType(2);
                        aFinal.setFrozenNo(newFrozenNo);
                    }
                    frozentopo_finalMapper.batchInsertData(finals);
                    status = new T_FROZENTOPO_FINAL_STATUS();
                    status.setSubstainid(substainId);
                    status.setInsertTime(new Date());
                    status.setType(2);
                    status.setFrozenNo(newFrozenNo);
                    frozentopoFinalStatusMapper.insertSelective(status);
                }
            }
            // 快速更新v_using_topo
             qxThemeResultService.handOperUsingTopo();
        }


        return new ResultVo(200,"导入成功",retMap);
    }

    private void batchMeterData(List<TMeter> batchInsertMeter, List<TMeter> batchUpdateMeter) {
        if(batchInsertMeter.size() > 0 && batchInsertMeter.size() > BATCH_MAX_NUM){
            tMeterMapper.batchInsertData(batchInsertMeter);
        }else{
            // 数量不大正常处理
            batchInsertMeter.forEach(item -> tMeterMapper.insertSelective(item));
        }

        if(batchUpdateMeter.size() > 0 && batchUpdateMeter.size() > BATCH_MAX_NUM){
            tMeterMapper.batchUpdateData(batchUpdateMeter);
        }else{
            // 数量不大正常处理
            batchUpdateMeter.forEach(item -> tMeterMapper.updateByPrimaryKey(item));
        }


    }

    private void batchMeterboxData(List<TMeterbox> batchInsertMb, List<TMeterbox> batchUpdateMb) {
        if(batchInsertMb.size() > 0 && batchInsertMb.size() > BATCH_MAX_NUM){
            tMeterboxMapper.batchInsertData(batchInsertMb);
        }else{
            // 数量不大正常处理
            batchInsertMb.forEach(item -> tMeterboxMapper.insertSelective(item));
        }

        if(batchUpdateMb.size() > 0 && batchUpdateMb.size() > BATCH_MAX_NUM){
            tMeterboxMapper.batchUpdateData(batchUpdateMb);
        }else{
            // 数量不大正常处理
            batchUpdateMb.forEach(item -> tMeterboxMapper.updateByPrimaryKey(item));
        }

    }

    private String replaceStr(String str) {
        if(str.contains(".0")){
            str = str.replace(".0","");
        }
        return str;
    }

    private void setMeterData(String mdtuDistrict, String mdtuAddress, String meterName, String meterNo, String houseId, String phase, String cChannelId, String leakChannelId, TMeter meter) {
        meter.setMeter_name(meterName);
        meter.setMeter_no(meterNo);
        meter.setHouse_id(houseId);
        meter.setPhase_remark("A".equals(phase) ? "1" : "B".equals(phase) ? "2" : "C".equals(phase) ? "3" : "4");
        meter.setC_DistrictBCDId(mdtuDistrict);
        meter.setC_AddressId(Integer.parseInt(mdtuAddress));
        meter.setC_ChannelId(Integer.parseInt(cChannelId));
        meter.setLeak_channel_id(Integer.parseInt(leakChannelId));
    }

    private void setMeterboxData(String idx, String meterboxName, String mbLocal, String mdtuDistrict, String mdtuAddress, String mdtuChannelNum, TMeterbox meterbox) {
        meterbox.setEpu_name(meterboxName);
        meterbox.setIdx(idx);
        meterbox.setEpu_local(mbLocal);
        meterbox.setC_DistrictBCDId(mdtuDistrict);
        meterbox.setC_AddressId(Integer.parseInt(mdtuAddress));
        meterbox.setC_ChannelNum(Integer.parseInt(mdtuChannelNum));
    }

    private void setBranchboxData(String bdtuDistrict, String bdtuAddr, String branchName, String branchLocal, String branchCapacity, String btduChannelOne, String btduChannelTwo, String btduChannelThree, String lineNum, TBranchbox branchbox) {
        branchbox.setEpu_name(branchName);
        branchbox.setEpu_local(branchLocal);
        branchbox.setBoxCapacity(Float.parseFloat(branchCapacity));
        branchbox.setC_DistrictBCDId(bdtuDistrict);
        branchbox.setC_AddressId(Integer.parseInt(bdtuAddr));
        branchbox.setC_ChannelId(Integer.parseInt(btduChannelOne));
        branchbox.setC_ChannelId_b(Integer.parseInt(btduChannelTwo));
        branchbox.setC_ChannelId_c(Integer.parseInt(btduChannelThree));
        branchbox.setLine_id(lineNum);
    }

    private void setOutgoingData(String outName, String outLocal, String bdtuDistrict, String bdtuAddr, String bdtuChannelNum, TOutgoingcabinet outgoingcabinet) {
        outgoingcabinet.setEpu_name(outName);
        outgoingcabinet.setEpu_local(outLocal);
        outgoingcabinet.setC_DistrictBCDId(bdtuDistrict);
        outgoingcabinet.setC_AddressId(Integer.parseInt(bdtuAddr));
        outgoingcabinet.setC_ChannelNum(Integer.parseInt(bdtuChannelNum));
    }

    private void setSubstainData(String subName, String subLocal, String subCapacity, String bdtuDistrict, String bdtuAddr, String btduChannelOne, String btduChannelTwo, String btduChannelThree, TSubstain tSubstain) {
        tSubstain.setEpu_name(subName);
        tSubstain.setEpu_local(subLocal);
        tSubstain.setBoxCapacity(Float.parseFloat(subCapacity));
        tSubstain.setDistrictId(bdtuDistrict);
        tSubstain.setAddressId(Integer.parseInt(bdtuAddr));
        tSubstain.setChannelId(Integer.parseInt(btduChannelOne));
        tSubstain.setChannelIdTwo(Integer.parseInt(btduChannelTwo));
        tSubstain.setChannelIdThree(Integer.parseInt(btduChannelThree));
    }

    @Override
    public Workbook exportRecord(Map<String, String> par) throws Exception {
        List<TopoExcelInfo> info = frozentopo_finalMapper.getTopoExcelInfo(par);
        if(info == null || info.size() == 0){
            throw new CommonException(ErrorDataEnum.ERR_120012.error,ErrorDataEnum.ERR_120012.code);
        }
        return createExcel(info);
    }

    /**
     * 创建excel,循环查询到的数据,第一个row写入对应标题信息,并且不同列设置不同的样式
     * 剩下的row循环结果集,往对应列写入数据
     * @param info
     * @return
     */
    private Workbook createExcel(List<TopoExcelInfo> info) {
        XSSFWorkbook wbk = new XSSFWorkbook();
        XSSFSheet sheet = wbk.createSheet("sheet1");
        Row titleRow = sheet.createRow(0);
        XSSFCellStyle cellStyle = null;
        for(int i = 0; i < BDTU_TITLES.length; i++){
            Cell cell = titleRow.createCell(i);
            cellStyle = wbk.createCellStyle();
            //设置填充方案
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            //设置填充颜色
            // 不同设备设置不同背景色
            if(i >= AREA_IDX[0] && i <= AREA_IDX[1]){
                cellStyle.setFillForegroundColor(new XSSFColor(new Color(0, 176, 80)));
            }
            if(i >= SUBSTAIN_IDX[0] && i <= SUBSTAIN_IDX[1]){
                cellStyle.setFillForegroundColor(new XSSFColor(new Color(146, 208, 80)));
            }
            if(i >= OUT_IDX[0] && i <= OUT_IDX[1]){
                cellStyle.setFillForegroundColor(new XSSFColor(new Color(0,176,240)));
            }
            if(i >= BRANCH_IDX[0] && i <= BRANCH_IDX[1]){
                cellStyle.setFillForegroundColor(new XSSFColor(new Color(155,194,230)));
            }
            if(i >= METERBOX_IDX[0] && i <= METERBOX_IDX[1]){
                cellStyle.setFillForegroundColor(new XSSFColor(new Color(248,203,173)));
            }
            if(i >= METER_IDX[0] && i <= METER_IDX[1]){
                cellStyle.setFillForegroundColor(new XSSFColor(new Color(255,217,102)));
            }
            cell.setCellValue(BDTU_TITLES[i]);
            cell.setCellStyle(cellStyle);
        }

        for (int i = 0; i < info.size(); i++) {
            TopoExcelInfo item = info.get(i);
            Row itemRow = sheet.createRow(i + 1);
            Cell cell0 = itemRow.createCell(0);
            cell0.setCellValue(item.getProvinceName() == null ? "null" : item.getProvinceName());
            cell0 = itemRow.createCell(1);
            cell0.setCellValue(item.getCityName() == null ? "null" : item.getCityName());
            cell0 = itemRow.createCell(2);
            cell0.setCellValue(item.getDistrictName() == null ? "null" : item.getDistrictName());
            cell0 = itemRow.createCell(3);
            cell0.setCellValue(item.getCourtName() == null ? "null" : item.getCourtName());
            cell0 = itemRow.createCell(4);
            cell0.setCellValue(item.getCourtAddress() == null ? "null" : item.getCourtAddress());

            cell0 = itemRow.createCell(5);
            cell0.setCellValue(item.getSubName() == null ? "null" : item.getSubName());
            cell0 = itemRow.createCell(6);
            cell0.setCellValue(item.getSubLocal() == null ? "null" : item.getSubLocal());
            cell0 = itemRow.createCell(7);
            cell0.setCellValue(item.getSubBoxCapacity() == null ? 0 : item.getSubBoxCapacity());

            cell0 = itemRow.createCell(8);
            cell0.setCellValue(item.getOutName() == null ? "null" : item.getOutName());
            cell0 = itemRow.createCell(9);
            cell0.setCellValue(item.getOutLocal() == null ? "null" : item.getOutLocal());
            cell0 = itemRow.createCell(10);
            cell0.setCellValue(item.getBdtuDistrictId() == null ? 0 : item.getBdtuDistrictId());
            cell0 = itemRow.createCell(11);
            cell0.setCellValue(item.getBdtuAddressId() == null ? 0 : item.getBdtuAddressId());
            cell0 = itemRow.createCell(12);
            cell0.setCellValue(item.getBtduChannelNum() == null ? 0 : item.getBtduChannelNum());

            cell0 = itemRow.createCell(13);
            cell0.setCellValue(item.getBranName() == null ? "null" : item.getBranName());
            cell0 = itemRow.createCell(14);
            cell0.setCellValue(item.getBranLocal() == null ? "null" : item.getBranLocal());
            cell0 = itemRow.createCell(15);
            cell0.setCellValue(item.getBranBoxCapacity() == null ? 0 : item.getBranBoxCapacity());
            cell0 = itemRow.createCell(16);
            cell0.setCellValue(item.getBtduChannelId() == null ? 0 : item.getBtduChannelId());
            cell0 = itemRow.createCell(17);
            cell0.setCellValue(item.getBdtuChannelB() == null ? 0 : item.getBdtuChannelB());
            cell0 = itemRow.createCell(18);
            cell0.setCellValue(item.getBdtuChannelC() == null ? 0 : item.getBdtuChannelC());
            cell0 = itemRow.createCell(19);
            cell0.setCellValue(item.getLineId() == null ? "null" : item.getLineId());

            cell0 = itemRow.createCell(20);
            cell0.setCellValue(item.getMeterboxName() == null ? "null" : item.getMeterboxName());
            cell0 = itemRow.createCell(21);
            cell0.setCellValue(item.getMeterboxLocal() == null ? "null" : item.getMeterboxLocal());
            cell0 = itemRow.createCell(22);
            cell0.setCellValue(item.getIdx() == null ? "null" : item.getIdx());
            cell0 = itemRow.createCell(23);
            cell0.setCellValue(item.getMdtuDistrictId() == null ? 0 : item.getMdtuDistrictId());
            cell0 = itemRow.createCell(24);
            cell0.setCellValue(item.getMdtuAddressId() == null ? 0 : item.getMdtuAddressId());
            cell0 = itemRow.createCell(25);
            cell0.setCellValue(item.getMdtuChannelNum() == null ? 0 : item.getMdtuChannelNum());

            cell0 = itemRow.createCell(26);
            cell0.setCellValue(item.getMeterName() == null ? "null" : item.getMeterName());
            cell0 = itemRow.createCell(27);
            cell0.setCellValue(item.getMeterNo() == null ? "null" : item.getMeterNo());
            cell0 = itemRow.createCell(28);
            cell0.setCellValue(item.getHouseId() == null ? "null" : item.getHouseId());
            cell0 = itemRow.createCell(29);
            Integer phaseRemark = item.getPhaseRemark() == null ? 1 : item.getPhaseRemark();
            String phase = phaseRemark == 1 ? "A" : phaseRemark == 2 ? "B" : phaseRemark == 3 ? "C" : "";
            cell0.setCellValue(phase);
            cell0 = itemRow.createCell(30);
            cell0.setCellValue(item.getMdtuChannelId() == null ? 0 : item.getMdtuChannelId());
            cell0 = itemRow.createCell(31);
            cell0.setCellValue(item.getLeakChannelId() == null ? 0 : item.getLeakChannelId());

        }
        return wbk;
    }

    @Override
    @Transactional
    public ResultVo removeRecord(Map<String,String> par) {
        TSubstain tSubstain = tSubstainMapper.selectByParameters(par);
        if(tSubstain == null){
            return new ResultVo(404,"档案信息不存在!");
        }

        // 删除MDTU
        par.put("devType", DtuTypeEnum.ZERO.getValue().toString());
        Integer mdtu  = deviceinfoMapper.deleteByParameters(par);

        // 删除BDTU
        par.put("devType", DtuTypeEnum.ONE.getValue().toString());
        Integer bdtu  = deviceinfoMapper.deleteByParameters(par);

        Integer sub = tSubstainMapper.deleteByParameters(par);
        Integer out = tOutgoingcabinetMapper.deleteByParameters(par);
        Integer branch = tBranchboxMapper.deleteByParameters(par);
        Integer meterbox = tMeterboxMapper.deleteByParameters(par);
        Integer meter = tMeterMapper.deleteByParameters(par);



        Integer court = tSyCourtInfoMapper.deleteByParameters(par);

        par.put("substainId",tSubstain.getSubstainId());
        Integer num1 = frozentopo_finalMapper.deleteByParameters(par);
//        Integer num2 = frozentopoFinalStatusMapper.deleteByParameters(par);
        Map<String,Object> data = new HashMap<>();
        data.put("sub",sub);
        data.put("out",out);
        data.put("branch",branch);
        data.put("meterbox",meterbox);
        data.put("meter",meter);
        data.put("mdtu",mdtu);
        data.put("bdtu",bdtu);
        data.put("court",court);
        return new ResultVo(200,"清除成功!",data);
    }

    @Override
    public ResultVo getDeviceCount(Map<String, String> parMap) {
        List<Integer> list = frozentopo_finalMapper.getDeviceCount(parMap);
        // 有一个不为0,则为有档案信息
        boolean b = list.stream().anyMatch(i -> i != 0);
        if(b){
            return new ResultVo(200,"查询成功",list);
        }else{
            return new ResultVo(404,"未查询到档案信息!",list);
        }

    }


}

之前写了一个response过滤器,导致出现了一些问题,导致excel可以下载无法打开

后来把上传的路径处理掉就可以了

坑了自己一天,还浪费同事一个多小时。  害!

蓝桥杯单片机学习13——NE555方波发生器&频率测量 NE555详解、引脚图介绍、原理图及工作原理讲解、方波产生的原理、定时器测量频率的原理及使用方法、代码实现 阅读详情

相关推荐

SFR算法实现详解:从边缘检测到MTF计算的完整流程

本文详细解析了SFR算法从边缘检测到MTF计算的完整流程,包括边缘检测、ESF超采样、LSF差分等关键步骤。通过代码示例和实战经验,帮助开发者高效实现成像系统解析力评估,优化MTF测量精度。适用于摄像头模组调试、工业检测等场景。

Black的专栏 1010

java poi 设置单元格颜色_POI 设置导出表中单元格颜色

import java.io.File;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import org.apache.poi.ss.usermodel.FillPatternType;import org.apache.poi.xssf.usermo...

weixin_42299888的博客 870

Debian 11 之使用hostapd与dnsmasq进行AP设置

本文介绍debian 11 系统下,使用hostapd 和 dnsmasq配置无线热点的方法。

xinghaitao2005的博客 1698

POI遇到的问题

做信息采集时,读取EXCEL遇到的问题。 出现下面错误的原因是:本来excel模板为xls结尾,结果别人使用后改成了xlsx结尾。 解决方法:把POI的版本由4.0以上改为3.17 解决问题 Exception in thread "pool-1-thread-1" org.apache.poi.ooxml.POIXMLException: org.apache.xmlbeans.Xm...

qq_41490843的博客 4021

硬件工程师入门进阶,一文搞懂,看这篇就对了

每家公司的datasheet都有严格的规范,及独特的风格,有时候还会穿插一些原理性的介绍,比如ADI公司的一些ADC/DAC器件资料里面,还会包含AD/DA转换的原理性讲解内容,Datasheet既可以是我们设计上的好帮手,也是学习的好资料。在出货量大的产品中,尽量减少物料种类,比如开头说的,1%的电阻不得不用,那么5%的替换成1%的,物料种类就减少一种,量就大了,能不能争取更优的价格呢?如果说前面学的是内功,那这一关,是招式、是工具的使用,原理图和PCB设计都有对应的工具,教程也很多,且比较容易上手。

一路带飞的博客 5万+

【雷达信号处理实战】从原理到代码:脉冲多普勒(PD)处理全流程解析与MATLAB仿真

本文深入解析了脉冲多普勒(PD)雷达信号处理的核心原理与全流程。通过剖析多普勒效应、脉冲压缩与慢时间FFT等关键技术,并结合详细的MATLAB仿真代码,手把手指导如何从信号建模、回波模拟到最终生成距离-多普勒谱,实现运动目标的精确测距与测速,直观展示了相参积累带来的显著性能增益。

pca5navigator的博客 964

JAVA POI库操作 Excel之 获取单元格的自定义颜色--踩坑记录

首先我们需要知道 POI库是有内置的颜色的,有64种基础颜色,但是在日常使用是不能够满足我们花里胡哨的需求所以就有很多自定义的颜色不在POI库里面。好那么问题来了,如我我们Excel颜色是自定义的,并且在POI设定的颜色之外,那么就会出现读取不到返回的索引就是 0(BLACK1 黑色)关键点来了 ,将获取到的 返回值强转成 XSSFColor对象,这样就可以获取到里面的RGB色值,从而根据RGB色值去设置底色。但是有些情况下面获取到的RBG值是错误的,这可能是获取的前填充色和背景色有关系。

m0_72440880的博客 3491

Kylin-Desktop-V10-SP1海光版虚拟机安装全流程(含桥接网络配置)

本文详细介绍了在VMware虚拟机中安装Kylin-Desktop-V10-SP1海光版操作系统的完整流程,重点解决了基于海光(Hygon C86)平台的网络配置难题。内容涵盖从镜像准备、虚拟机创建、系统安装到桥接网络配置的三种实战方法,并提供了安装后的优化与必备软件配置指南,帮助用户高效搭建国产化测试与开发环境。

weixin_29323493的博客 357

java poi解析excel报错处理

org.apache.xmlbeans.SchemaTypeLoaderException: XML-BEANS compiled schema: Incompatible minor version - expecting up to 23, got 24 (schemaorg_apache_xmlbeans.system.sE130CAA0A01A7CDE5A2B4FEB8B31

yin4302008的专栏 4282

FM20S用户手册--Linux系统启动卡制作及系统固化

评估板出厂时,默认提供一张配套的Micro SD卡,此卡为可正常使用的Linux系统 启动卡。评估板断电,将Linux系统启动卡插入评估板Micro SD卡槽,根据评估底板丝印将 启动方式选择拨码开关拨为1010(1-4).此档位为Micro SD启动模式。使用Linux 系统启动卡启动系统时,将使用此目录的BOOTbn文件启动FSBL和U-Boot。Linux系统启动卡制作时,已将固化系统的脚本文件mkemmcboot.sh拷贝至Linux系 统启动卡文件系统的"/opt/tools/"目录下。

创龙,嵌入式一体化解决方案商 303

ESP32与BH1750光照传感器结合使用的Web服务器案例

请注意,为了使代码正常工作,你需要将代码中的"你的WiFi网络名称"和"你的WiFi网络密码"替换为你自己的WiFi网络凭据。在代码中,我们首先导入了所需的库文件,包括Wire库用于I2C通信,BH1750库用于与光照传感器通信,WiFi库和WebServer库用于创建Web服务器。接下来,我们将编写一个Arduino代码来控制ESP32与BH1750的通信,并将读取到的光照强度值显示在Web服务器上。函数用于处理根路径的请求,其中我们读取光照传感器的值,并将其显示在返回的HTML页面中。

server_api196的博客 619

多线程发送xlsx

value *= 2;// 价格或数量乘以2。// 检查是否是价格或数量。// 假设价格在第2列,数量在第3列。// 创建输出流,将文件写到excel中。

m0_57984526的博客 380

学Simulink——基于Simulink的AUTOSAR架构下电机控制软件组件建模

本文详细介绍了在Simulink中基于AUTOSAR架构开发电机控制软件组件(SWC)的全流程。主要内容包括:首先阐述了AUTOSAR架构对汽车电子系统开发的价值,包括提高软件复用率、降低集成成本和改善验证环境。然后重点讲解了在Simulink中构建电机控制SWC的具体步骤:从创建AUTOSAR组件模型、定义数据端口和服务端口,到搭建FOC控制算法、配置运行实体,最后生成AUTOSAR描述文件和验证RTE接口。文章还提供了与基础软件(BSW)的集成方案、工程实践要点和常见问题解决方案,并以大众MEB平台等实

amy_mhd的博客 68

Maven整合SSM 框架,POI插件导出EXCEL文件

Maven+SSM+poi导出Excel文件片头背景描述环境正片1part2part3part(主要)4part5part结果片尾花絮xls和xlsx[导出文件名乱码](https://blog.csdn.net/qq_28869233/article/details/87979552)日期转化文字符串输出调整列宽加批注鸣谢 片头 背景描述 最近被提出需求,要求网站实现对数据库的数据进行查询,并要...

Coo~的博客 1319

Camera2Demo

在Camera2 的基础上进行封装,使得Camera2一堆初始化逻辑与Activity分离,降低耦合度,完成预览拍照并保存的操作

IDEA的使用指南

IDEA基于java的使用手册,可以最快速的上手,在使用中不断的丰富自己的使用技巧.

在SSM下基于POI实现Excel表的导入/导出

对于批量数据的操作,在项目中引进Excel的导入和导出功能是个不错的选择。对于Excel表的结构,简单理解可以把它分成三部分(Sheet,Cell,Row),这三部分可以理解为excel表中的页,列,行。因此,我们想要获取到某一个单元的内容,可以通过获取该单元所在的页数、对应所在的行和对应的列数从而定位到该单位,继而便可执行操作从而获取其中的内容。本文在SSM环境下基于Java的POI实现对exc...

|张超|的博客 2744

Statistics and Machine Learning Toolbox

Statistics and Machine Learning Toolbox(带书签),MATLAB统计学习工具

使用POI批量导出Excel文件(SSM)

使用POI批量导入Excel文件(SSM)

weixin_44812756的博客 1214

使用POI选择导出Excel文件(SSM)

使用POI选择导出Excel文件(SSM)

weixin_44812756的博客 327

SSM框架利用POI把数据库文件导出成.xlsx表格文件

一、搭建背景及准备条件 idea,maven3.6.0,jdk1.8,tomcat8,mysql5.6 本文章主要是把数据库文件导出成.xlsx表格文件,搭建SSM框架可以参考上一篇文章,本文章只给出数据库文件导出成.xlsx表格文件所需要的新依赖 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <ve

it1880456的博客 328

报表Excel的POI概述和实现导出SSM框架结合代码案例

报表进行POI的导出实现

S7439012的博客 601

Excel大批量数据的导入和导出,如何做优化?

点击上方蓝色“方志朋”,选择“设为星标”回复“666”获取独家整理的学习资料!作者:Albenhttps://albenw.github.io/posts/d093ca4e概要 Java对...

方志朋的博客 268

SSM框架利用POI把.xlsx表格文件导出成数据库文件

**一、搭建背景及准备条件 idea,maven3.6.0,jdk1.8,tomcat8,mysql5.6 本文章主要是把.xlsx表格文件导出成数据库文件,搭建SSM框架可以参考上一篇文章,本文章只给出数据库文件导出成.xlsx表格文件所需要的新依赖** 把下面配置文件放入Spring-mvc配置文件下 <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartReso

it1880456的博客 247

java生成excel文件,以及导出

java实现导入导出excel

weixin_44922423的博客 7403

POI概述 结合ssm框架实现对Excel报表的操作

POI简单的来说就是用来操作报表的工具。一般用于对数据的导入导出操作上传数据(导入):把报表中的数据导入到数据库中生成报表(导出):把数据库中的数据,导出并写入到报表(work,excel, pdf )存储数据。...

m0_67819606的博客 278

java导出excel内容有问题_poi导出excel打开文件时部分内容有问题

问题描述:JavaWeb项目,ssm框架,从tomcat9.0.30换到weblogic10.3.6.0后,poi导出excel,打开文件时部分内容有问题;解决:response.addHeader("Content-Length", "" + outfile.length());替换成:response.setHeader("Content-Length", "" + outfile.lengt...

weixin_32512933的博客 923

POI如何高效导出百万级Excel数据?

阅读原文:POI如何高效导出百万级Excel数据? 在一个具有统计功能的系统中,导出excel功能几乎是一定的,如何导出excel?导出的数据有多少?如何高效的导出? Excel简介 什么是excel就不用介绍了,这里主要说明不同版本下每个sheet下的行列限制。 由上面可知 Exc...

chenzongjiong1168的博客 218

SSM 使用 POI 实现EXCEL的上传与下载

导入包 import org.apache.commons.beanutils.BeanUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.NumberToTextConverter; impor...

hahaowenGG的博客 603

EXCEL上传POI

Java SpringMVC POI上传excel并读取文件内容 2017年11月27日 15:26:56强人锁男。阅读数:15329 用的SSM框架,所需要的jar包如图所示:,链接地址:jar包下载 ,下面直接上代码。 1、ExcelUtil工具类 [java]view plaincopy &lt...

weixin_30437847的博客 141
上一篇: SSM整合logback引入外部配置文件
下一篇: centos-7.7 无法通过ifconfig查看ip
宁漂打工仔
博客等级 码龄9年 1万+粉丝 153原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

宁漂打工仔

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值