SSM整合POI导出excel .xlsx文件

最新推荐文章于 2023-02-12 23:09:47 发布

宁漂打工仔

最新推荐文章于 2023-02-12 23:09:47 发布

阅读量297

点赞数

分类专栏： Java

本文链接：https://blog.csdn.net/SpringHASh/article/details/105074932

版权

Java 专栏收录该内容

69 篇文章 0 订阅

订阅专栏

依赖

 <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可以下载无法打开

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

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

宁漂打工仔

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
SSM整合POI导出excel .xlsx文件

依赖 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dep...
复制链接

扫一扫