package cn.com.goldenwater.dcproj.service.impl;

import cn.com.goldenwater.core.service.AbstractCrudService;
import cn.com.goldenwater.dcproj.dao.BisInspTaskDthDayDao;
import cn.com.goldenwater.dcproj.dao.TaskDthDayCountDao;
import cn.com.goldenwater.dcproj.dto.BisInspTaskDthDayDto;
import cn.com.goldenwater.dcproj.model.BisInspTaskDthDay;
import cn.com.goldenwater.dcproj.param.BisInspTaskDthDayParam;
import cn.com.goldenwater.dcproj.service.BisInspTaskDthDayService;
import cn.com.goldenwater.dcproj.service.OlBisInspOrgService;
import cn.com.goldenwater.dcproj.service.TacProvincePlanDictService;
import cn.com.goldenwater.dcproj.utils.AdLevelUtil;
import cn.com.goldenwater.dcproj.utils.TaskDateUtils;
import cn.com.goldenwater.id.util.UuidUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * @author lhc
 * @date 2020-10-13
 */
@Service
@Transactional
public class BisInspTaskDthDayServiceImpl extends AbstractCrudService<BisInspTaskDthDay, BisInspTaskDthDayParam> implements BisInspTaskDthDayService {

    private Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
    private BisInspTaskDthDayDao bisInspTaskDthDayDao;

    @Autowired
    private TaskDthDayCountDao taskDthDayCountDao;

    @Autowired
    private OlBisInspOrgService olBisInspOrgService;

    @Autowired
    private TacProvincePlanDictService tacProvincePlanDictService;

    public BisInspTaskDthDayServiceImpl(BisInspTaskDthDayDao bisInspTaskDthDayDao) {
        super(bisInspTaskDthDayDao);
        this.bisInspTaskDthDayDao = bisInspTaskDthDayDao;
    }

    @Override
    public int insert(BisInspTaskDthDay bisInspTaskDthDay) {
        // 插入前判断是否存在 存在则更新，不存在则插入
        BisInspTaskDthDayParam bisInspTaskDthDayParam = new BisInspTaskDthDayParam();
        bisInspTaskDthDayParam.setAdCode(bisInspTaskDthDay.getAdCode());
        bisInspTaskDthDayParam.setObjType(bisInspTaskDthDay.getObjType());
        bisInspTaskDthDayParam.setTm(bisInspTaskDthDay.getTm());
        List<BisInspTaskDthDay> bisInspTaskDthDays = bisInspTaskDthDayDao.findList(bisInspTaskDthDayParam);

        bisInspTaskDthDay.setIntm(new Date());
        bisInspTaskDthDay.setUptm(new Date());
        bisInspTaskDthDay.setDataStat("0");
        if (bisInspTaskDthDays.size() == 1) {
            bisInspTaskDthDay.setId(bisInspTaskDthDays.get(0).getId());
            logger.info("更新{}", bisInspTaskDthDay.toString());
            return this.bisInspTaskDthDayDao.update(bisInspTaskDthDay);
        } else {
            // 生成uuid
            String uuid = UuidUtil.uuid();
            bisInspTaskDthDay.setId(uuid);
//            logger.info("新增{}", bisInspTaskDthDay.toString());
            return this.bisInspTaskDthDayDao.insert(bisInspTaskDthDay);
        }
    }

    @Override
    public int update(BisInspTaskDthDay bisInspTaskDthDay) {
        bisInspTaskDthDay.setUptm(new Date());
        return this.bisInspTaskDthDayDao.update(bisInspTaskDthDay);
    }

    @Override
    public int delete(String id) {
        return this.bisInspTaskDthDayDao.delete(id);
    }


    @Override
    public List<BisInspTaskDthDay> find(BisInspTaskDthDayDto bisInspTaskDthDayDto) {
        // 设置时间
        TaskDateUtils.setDate(bisInspTaskDthDayDto);
        bisInspTaskDthDayDto.setProvince(olBisInspOrgService.getRlProvince(bisInspTaskDthDayDto.getOrgId()));
        String adCode = AdLevelUtil.getAddvcd(bisInspTaskDthDayDto.getProvince());
        // 如果有传AD_CODE，判断市县级别，默认市级数据
        bisInspTaskDthDayDto.setAdGrad("3");
        String sourceAdCode = bisInspTaskDthDayDto.getAdCode() ;
        if (StringUtils.isNotBlank(bisInspTaskDthDayDto.getAdCode())) {
            String addvcd = AdLevelUtil.getAddvcd(bisInspTaskDthDayDto.getAdCode());
            if (addvcd.contains(adCode)) {
                adCode = addvcd;
            }
        }
        // 如果传的是市级adCode, 要获取该市下所有县级数据
        if (adCode.length() == 4 || adCode.length() == 6) {
            // 市级编码
            bisInspTaskDthDayDto.setAdGrad("4");
        }
        bisInspTaskDthDayDto.setAdCode(adCode);
        if("359900000000".equals(sourceAdCode)){
            // 福建省 省本级
            bisInspTaskDthDayDto.setAdGrad("3");
            bisInspTaskDthDayDto.setAdCode("3500");
        }
        if (StringUtils.isNotBlank(bisInspTaskDthDayDto.getObjType())) {
            bisInspTaskDthDayDto.setObjType(String.valueOf(Integer.parseInt(bisInspTaskDthDayDto.getObjType())));
            if ("8".equals(bisInspTaskDthDayDto.getObjType())) {
                // 如果是稽察， 查询最后一步ID
                bisInspTaskDthDayDto.setLastStepId(tacProvincePlanDictService.getJcStepId(bisInspTaskDthDayDto.getOrgId()));
            }
        }

        BisInspTaskDthDayParam bisInspTaskDthDayParam = new BisInspTaskDthDayParam();
        BeanUtils.copyProperties(bisInspTaskDthDayDto, bisInspTaskDthDayParam);
        List<BisInspTaskDthDay> bisInspTaskDthDayList = bisInspTaskDthDayDao.findForCity(bisInspTaskDthDayParam);

        // 加上 整改率
        if (bisInspTaskDthDayList != null && bisInspTaskDthDayList.size() > 0) {
            BisInspTaskDthDay total = new BisInspTaskDthDay();
            total.setAdCode(bisInspTaskDthDayList.get(0).getAdCode().substring(0, 2) + "0000000000");
            total.setAdName("合计");
            int objSize = 0;
            int pblmSize = 0;
            int mendSize = 0;
            int pblmLogSize = 0;
            int submitPblm = 0;
            int infoPlbm = 0;
            int warmPlbm = 0;
            int errorPlbm = 0;
            int errorsPlbm = 0;
            for (BisInspTaskDthDay bisInspTaskDthDay : bisInspTaskDthDayList) {
                if ("8".equals(bisInspTaskDthDayDto.getObjType())) {
                    BigDecimal pblmAvgSize = bisInspTaskDthDay.getObjSize() == 0 ?
                            new BigDecimal(0) :
                            BigDecimal.valueOf((double) bisInspTaskDthDay.getPblmSize() / bisInspTaskDthDay.getObjSize())
                                    .setScale(2, BigDecimal.ROUND_HALF_UP);
                    bisInspTaskDthDay.setPblmAvgSize(pblmAvgSize);
                }

                bisInspTaskDthDay.setMendSizeRant(bisInspTaskDthDay.getPblmSize() == 0 ?
                        BigDecimal.valueOf(0) : BigDecimal.valueOf((double) bisInspTaskDthDay.getMendSize() / bisInspTaskDthDay.getPblmSize() * 100).setScale(2,BigDecimal.ROUND_HALF_UP));
                // 问题整改完成率 完成销号数量/总数量 * 100% 百分比
                bisInspTaskDthDay.setMendAccomplishRant(bisInspTaskDthDay.getPblmSize() == 0 ?
                        BigDecimal.valueOf(0) : BigDecimal.valueOf(Double.valueOf(String.valueOf(bisInspTaskDthDay.getPblmLogSize()))).divide(new BigDecimal(bisInspTaskDthDay.getPblmSize()),4,BigDecimal.ROUND_HALF_UP).multiply(BigDecimal.valueOf(100)).setScale(2,BigDecimal.ROUND_HALF_UP));
                objSize += bisInspTaskDthDay.getObjSize();
                pblmSize += bisInspTaskDthDay.getPblmSize();
                submitPblm += bisInspTaskDthDay.getSubmitPlbm();
                mendSize += bisInspTaskDthDay.getMendSize();
                pblmLogSize += bisInspTaskDthDay.getPblmLogSize();
                infoPlbm += bisInspTaskDthDay.getInfoPlbm();
                warmPlbm += bisInspTaskDthDay.getWarmPlbm();
                errorPlbm += bisInspTaskDthDay.getErrorPlbm();
                errorsPlbm += bisInspTaskDthDay.getErrorsPlbm();
            }
            // 排除 市辖区
            bisInspTaskDthDayList = bisInspTaskDthDayList.stream().filter(f->!f.getAdName().contains("市辖区")).collect(Collectors.toList());
            // 按问题数量倒序排列
            bisInspTaskDthDayList = bisInspTaskDthDayList.stream().sorted(Comparator.comparing(BisInspTaskDthDay::getPblmSize).reversed()).collect(Collectors.toList());
            total.setObjSize(objSize);
            total.setPblmSize(pblmSize);
            total.setSubmitPlbm(submitPblm);
            total.setMendSize(mendSize);
            total.setPblmLogSize(pblmLogSize);
            total.setInfoPlbm(infoPlbm);
            total.setWarmPlbm(warmPlbm);
            total.setErrorPlbm(errorPlbm);
            total.setErrorsPlbm(errorsPlbm);
            bisInspTaskDthDayList.add(total);
        }
        return bisInspTaskDthDayList;
    }

    @Override
    public List<BisInspTaskDthDay> findForObjType(BisInspTaskDthDayDto bisInspTaskDthDayDto) {
        // 设置时间
        TaskDateUtils.setDate(bisInspTaskDthDayDto);
        String adCode = AdLevelUtil.getAddvcd(olBisInspOrgService.getRlProvince(bisInspTaskDthDayDto.getOrgId()));
        // 如果有传AD_CODE，判断市县级别，默认市级数据
        bisInspTaskDthDayDto.setAdGrad("3");
        String sourceAdCode = bisInspTaskDthDayDto.getAdCode() ;
        if (StringUtils.isNotBlank(bisInspTaskDthDayDto.getAdCode())) {
            String addvcd = AdLevelUtil.getAddvcd(bisInspTaskDthDayDto.getAdCode());
            if (addvcd.contains(adCode)) {
                adCode = addvcd;
            }
        }
        // 如果传的是市级adCode, 要获取该市下所有县级数据
        if (adCode.length() == 4 || adCode.length() == 6) {
            // 市级编码
            bisInspTaskDthDayDto.setAdGrad("4");
        }
        bisInspTaskDthDayDto.setAdCode(adCode);
        if("359900000000".equals(sourceAdCode)){
            // 福建省 省本级
            bisInspTaskDthDayDto.setAdGrad("3");
            bisInspTaskDthDayDto.setAdCode("3500");
        }
        if (StringUtils.isNotBlank(bisInspTaskDthDayDto.getObjType())) {
            bisInspTaskDthDayDto.setObjType(String.valueOf(Integer.parseInt(bisInspTaskDthDayDto.getObjType())));
        }

        BisInspTaskDthDayParam bisInspTaskDthDayParam = new BisInspTaskDthDayParam();
        BeanUtils.copyProperties(bisInspTaskDthDayDto, bisInspTaskDthDayParam);
        List<BisInspTaskDthDay> bisInspTaskDthDayList = bisInspTaskDthDayDao.findForObjType(bisInspTaskDthDayParam);
        // 按问题数倒序排序
        bisInspTaskDthDayList = bisInspTaskDthDayList.stream().sorted(Comparator.comparing(BisInspTaskDthDay::getPblmSize).reversed()).collect(Collectors.toList());

        return bisInspTaskDthDayList;
    }
    @Override
    public Map<String,List> findForCityObjType(BisInspTaskDthDayDto bisInspTaskDthDayDto) {
        // 设置时间
        TaskDateUtils.setDate(bisInspTaskDthDayDto);
        bisInspTaskDthDayDto.setProvince(olBisInspOrgService.getRlProvince(bisInspTaskDthDayDto.getOrgId()));
        String adCode = AdLevelUtil.getAddvcd(bisInspTaskDthDayDto.getProvince());
        // 如果有传AD_CODE，判断市县级别，默认市级数据
        bisInspTaskDthDayDto.setAdGrad("3");
        String sourceAdCode = bisInspTaskDthDayDto.getAdCode() ;
        if (StringUtils.isNotBlank(bisInspTaskDthDayDto.getAdCode())) {
            String addvcd = AdLevelUtil.getAddvcd(bisInspTaskDthDayDto.getAdCode());
            if (addvcd.contains(adCode)) {
                adCode = addvcd;
            }
        }
        // 如果传的是市级adCode, 要获取该市下所有县级数据
        if (adCode.length() == 4 || adCode.length() == 6) {
            // 县级编码
            bisInspTaskDthDayDto.setAdGrad("4");
        }
        bisInspTaskDthDayDto.setAdCode(adCode);
        if("359900000000".equals(sourceAdCode)){
            // 福建省 省本级
            bisInspTaskDthDayDto.setAdGrad("3");
            bisInspTaskDthDayDto.setAdCode("3500");
        }
        if (StringUtils.isNotBlank(bisInspTaskDthDayDto.getObjType())) {
            bisInspTaskDthDayDto.setObjType(String.valueOf(Integer.parseInt(bisInspTaskDthDayDto.getObjType())));
        }

        BisInspTaskDthDayParam bisInspTaskDthDayParam = new BisInspTaskDthDayParam();
        BeanUtils.copyProperties(bisInspTaskDthDayDto, bisInspTaskDthDayParam);
        List<BisInspTaskDthDay> bisInspTaskDthDayList = bisInspTaskDthDayDao.findForCityObjType(bisInspTaskDthDayParam);
        Map<String, List<BisInspTaskDthDay>> adListMap = bisInspTaskDthDayList.stream().collect(Collectors.groupingBy(e -> e.getAdName()));
        // 按问题数量的倒序排列 行政区
        List<BisInspTaskDthDay> xAxisList = new ArrayList<>(adListMap.size());
        Set<String> keySet = adListMap.keySet();
        for(String key : keySet){
            BisInspTaskDthDay  item = new BisInspTaskDthDay();
            List<BisInspTaskDthDay> itemList = adListMap.get(key);
            int sum = itemList.stream().mapToInt(m -> m.getPblmSize()).sum();
            item.setAdName(key);
            item.setPblmSize(sum);
            xAxisList.add(item);
        }
        xAxisList = xAxisList.stream().sorted(Comparator.comparing(BisInspTaskDthDay::getPblmSize).reversed()).collect(Collectors.toList());

        List<String> xAxis = xAxisList.stream().map(m->m.getAdName()).collect(Collectors.toList());

        Map<String,List> resultMap = new LinkedHashMap<>();
        resultMap.put("xAxis" ,xAxis);

        List<BisInspTaskDthDay> specialtyTypeList = bisInspTaskDthDayDao.selectCurObjTypeList(bisInspTaskDthDayParam);
        List<String> typeCodeList = specialtyTypeList.stream().map(m -> m.getObjType()).collect(Collectors.toList());
        resultMap.put("yTypeCode" ,typeCodeList);
        for(String pType :typeCodeList){
            String objName = specialtyTypeList.stream().filter(f->pType.equals(f.getObjType())).map(m->m.getObjName()).collect(Collectors.joining());
            List<Integer> sizeList = new ArrayList<>(xAxis.size());
            for(String adName: xAxis){
                Optional<BisInspTaskDthDay> first = bisInspTaskDthDayList.stream().filter(f -> adName.equals(f.getAdName()) && pType.equals(f.getObjType())).findFirst();
                if(first.isPresent()){
                    sizeList.add(first.get().getPblmSize());
                }else {
                    sizeList.add(0);
                }
            }
            resultMap.put(objName ,sizeList);
        }
        return resultMap;
    }



    /**
     *me
     *
    * */
    @Override
    public List<BisInspTaskDthDay> findForObjType1(BisInspTaskDthDayDto bisInspTaskDthDayDto) {
        // 设置时间
        TaskDateUtils.setDate(bisInspTaskDthDayDto);
        String adCode = AdLevelUtil.getAddvcd(olBisInspOrgService.getRlProvince(bisInspTaskDthDayDto.getOrgId()));
        // 如果有传AD_CODE，判断市县级别，默认市级数据
        bisInspTaskDthDayDto.setAdGrad("3");
        String sourceAdCode = bisInspTaskDthDayDto.getAdCode() ;
        if (StringUtils.isNotBlank(bisInspTaskDthDayDto.getAdCode())) {
            String addvcd = AdLevelUtil.getAddvcd(bisInspTaskDthDayDto.getAdCode());
            if (addvcd.contains(adCode)) {
                adCode = addvcd;
            }
        }
        // 如果传的是市级adCode, 要获取该市下所有县级数据
        if (adCode.length() == 4 || adCode.length() == 6) {
            // 市级编码
            bisInspTaskDthDayDto.setAdGrad("4");
        }
        bisInspTaskDthDayDto.setAdCode(adCode);
        if("359900000000".equals(sourceAdCode)){
            // 福建省 省本级
            bisInspTaskDthDayDto.setAdGrad("3");
            bisInspTaskDthDayDto.setAdCode("3500");
        }
        if (StringUtils.isNotBlank(bisInspTaskDthDayDto.getObjType())) {
            bisInspTaskDthDayDto.setObjType(String.valueOf(Integer.parseInt(bisInspTaskDthDayDto.getObjType())));
        }

        BisInspTaskDthDayParam bisInspTaskDthDayParam = new BisInspTaskDthDayParam();
        BeanUtils.copyProperties(bisInspTaskDthDayDto, bisInspTaskDthDayParam);
        List<BisInspTaskDthDay> bisInspTaskDthDayList1 = bisInspTaskDthDayDao.findForObjType1(bisInspTaskDthDayParam);
        // 按问题数倒序排序
        bisInspTaskDthDayList1 = bisInspTaskDthDayList1.stream().sorted(Comparator.comparing(BisInspTaskDthDay::getPblmSize).reversed()).collect(Collectors.toList());

        return bisInspTaskDthDayList1;
    }
    /**
     * 查询多年的、行政区划、督查类型 查询 督查对象数量，督查问题数量 督查问题整改数量 督查问题整改率
     * @param bisInspTaskDthDayDto
     * @return
     */
    @Override
    public Map<String,List> findForCityObjTypeYearsCompare(BisInspTaskDthDayDto bisInspTaskDthDayDto) {
        // 设置时间
        TaskDateUtils.setDate(bisInspTaskDthDayDto);
        // 没有选择行政区划 查询 当前用户所属行政区划的所有
        String adCode =  AdLevelUtil.getAddvcd(olBisInspOrgService.getRlProvince(bisInspTaskDthDayDto.getOrgId()));
        // 设置行政区划
        String queryAdCode = bisInspTaskDthDayDto.getAdCode();
        if(StringUtils.isNotBlank(queryAdCode)){
            // 有选择的行政区划 查询 选择的 行政区划
            String addvcd = AdLevelUtil.getAddvcd(queryAdCode) ;
            if (addvcd.contains(adCode)) {
                adCode = addvcd;
            }
            bisInspTaskDthDayDto.setAdCode(adCode);
            // 省本级的特殊处理
            if("359900000000".equals(queryAdCode)){
                // 福建省 省本级
                bisInspTaskDthDayDto.setAdCode("3500");
            }
        }
        // 处理督查类型
        if (StringUtils.isNotBlank(bisInspTaskDthDayDto.getObjType())) {
            bisInspTaskDthDayDto.setObjType(String.valueOf(Integer.parseInt(bisInspTaskDthDayDto.getObjType())));
        }

        BisInspTaskDthDayParam bisInspTaskDthDayParam = new BisInspTaskDthDayParam();
        BeanUtils.copyProperties(bisInspTaskDthDayDto, bisInspTaskDthDayParam);
        List<BisInspTaskDthDay> bisInspTaskDthDayList = bisInspTaskDthDayDao.findForCityObjTypeYearsCompare(bisInspTaskDthDayParam);
        Map<String, List<BisInspTaskDthDay>> adListMap = bisInspTaskDthDayList.stream().collect(Collectors.groupingBy(e -> e.getCheckYear()));
        Set<String> keySet = adListMap.keySet();
        String join = StringUtils.join(keySet, ",");
        List<String> xAxis = Arrays.asList(join.split(","));

        Map<String,List> resultMap = new LinkedHashMap<>();
        xAxis = xAxis.stream().sorted().collect(Collectors.toList());
        resultMap.put("xAxis" ,xAxis);
        List<Map<String,Object>>  yAxis= new ArrayList<>();

        List<Map<String, String>> specialtyType = taskDthDayCountDao.statisticsFjIndexBySpecialtyType(bisInspTaskDthDayDto);
        List<Integer> typeCodeList = specialtyType.stream().map(m -> Integer.valueOf(m.get("PTYPE"))).collect(Collectors.toList());
        // 1 督查对象数量 2 发现问题数量 3确认问题数量  4 整改问题数量
        String compareType = bisInspTaskDthDayDto.getCompareType();
        for(Integer pType :typeCodeList){
            Map<String,Object> typeMap = new HashMap<>();
            String objName = specialtyType.stream().filter(f->pType.toString().equals(f.get("PTYPE"))).map(m->m.get("NAME")).collect(Collectors.joining());
            List<Integer> sizeList = new ArrayList<>(xAxis.size());
            for(String curYear: xAxis){
                Optional<BisInspTaskDthDay> first = bisInspTaskDthDayList.stream().filter(f -> curYear.equals(f.getCheckYear()) && pType.toString().equals(f.getObjType())).findFirst();
                if(first.isPresent()){
                    if("1".equals(compareType)){
                        // 督查对象数
                        sizeList.add(first.get().getObjSize());
                    }else if("3".equals(compareType)){
                        // 确认问题数
                        sizeList.add(first.get().getSubmitPlbm());
                    }else if("4".equals(compareType)){
                        // 整改问题数
                        sizeList.add(first.get().getMendSize());
                    }else {
                        // 发现问题数
                        sizeList.add(first.get().getPblmSize());
                    }

                }else {
                    sizeList.add(0);
                }
            }
            typeMap.put("typeName",objName);
            typeMap.put("typeValue" ,sizeList);
            yAxis.add(typeMap);
        }
        resultMap.put("yAxis" ,yAxis);
        return resultMap;
    }

    /**
     * 导出 excel
     * @param bisInspTaskDthDayDto
     * @param response
     */
    @Override
    public void exportBisInspTaskDthDay(BisInspTaskDthDayDto bisInspTaskDthDayDto, HttpServletResponse response){
        List<BisInspTaskDthDay>  news = find(bisInspTaskDthDayDto);
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("督查对象、问题区域分布汇和严重等级汇总表");
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 8列
        int columnSize = 9 ;
        int rowSize = (null == news)? 0: news.size();
        for(int r=0;r<rowSize+3 ; r++){
            HSSFRow row = sheet.createRow(r);
            for(int c=0; c<columnSize; c++){
                HSSFCell cell = row.createCell(c);
                cell.setCellStyle(cellStyle);
            }
        }
        // 合并 第一行 至 第二行 第1列
        addMergedRegion(sheet,0, 1, 0, 0,0,0,"地区");
        // 合并 第一行 至 第二行 第2列
        addMergedRegion(sheet,0, 1, 1, 1,0,1,"督查对象数");
        // 合并 第一行 至 第二行 第3列
        addMergedRegion(sheet,0, 1, 2, 2,0,2,"问题总数");
        // 合并 第一行  第4列 至 第7列
        addMergedRegion(sheet,0, 0, 3, 6,0,3,"问题分类");
        // 合并 第一行 至 第二行  第8列
        addMergedRegion(sheet,0, 1, 7, 7,0,7,"销号数");
        // 合并 第一行 至 第二行 第9列
        addMergedRegion(sheet,0, 1, 8, 8,0,8,"整改完成率");

        setCell(sheet,1,3,"比较严重",cellStyle);
        setCell(sheet,1,4,"严重",cellStyle);
        setCell(sheet,1,5,"较重",cellStyle);
        setCell(sheet,1,6,"一般",cellStyle);

        if(null != news && news.size()>0){
            // 从第3行开始
            for(int r=0;r<rowSize ;r++){
                BisInspTaskDthDay bisInspTaskDthDay = news.get(r);
                // 设置 单元格内容
                setCellValue(sheet,2+r,0,bisInspTaskDthDay.getAdName() );
                setCellValue(sheet,2+r,1,bisInspTaskDthDay.getObjSize() );
                setCellValue(sheet,2+r,2,bisInspTaskDthDay.getPblmSize());
                setCellValue(sheet,2+r,3,bisInspTaskDthDay.getErrorsPlbm() );
                setCellValue(sheet,2+r,4,bisInspTaskDthDay.getErrorPlbm() );
                setCellValue(sheet,2+r,5,bisInspTaskDthDay.getWarmPlbm() );
                setCellValue(sheet,2+r,6,bisInspTaskDthDay.getInfoPlbm() );
                setCellValue(sheet,2+r,7,bisInspTaskDthDay.getPblmLogSize() );
                if(null != bisInspTaskDthDay.getMendAccomplishRant()){
                    setCellValue(sheet,2+r,8,bisInspTaskDthDay.getMendAccomplishRant().toString().concat("%"));
                }

            }
        }

        try {
            response.setContentType("application/x-msdownload");
            String fileNameURL = URLEncoder.encode("督查对象、问题区域分布汇和严重等级汇总表.xls", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileNameURL + ";filename*=utf-8''" + fileNameURL);
            OutputStream out = response.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private void  addMergedRegion(HSSFSheet sheet,int firstRow, int lastRow, int firstCol, int lastCol , int curRow, int curCol, String value){
        CellRangeAddress region1 = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(region1);
        Cell mergeCell1 = sheet.getRow(curRow).getCell(curCol) ;
        mergeCell1.setCellValue(value);
        CellStyle mergeCellCellStyle = mergeCell1.getCellStyle();
        mergeCellCellStyle.setAlignment(HorizontalAlignment.CENTER);
        mergeCellCellStyle.setAlignment(HorizontalAlignment.CENTER);
        mergeCell1.setCellStyle(mergeCellCellStyle);
    }
    private void setCell(HSSFSheet sheet,int row ,int col ,String value, HSSFCellStyle cellStyle){
        HSSFCell cell1 = sheet.getRow(row).getCell(col);
        cell1.setCellValue(value);
        cell1.setCellStyle(cellStyle);
    }
    private void setCellValue(Sheet sheet, int rownum, int cellnum, Object value) {
        if(null != value){
            Cell cell = sheet.getRow(rownum).getCell(cellnum);
            if(value instanceof  String){
                cell.setCellValue(value.toString());
            }else if(value instanceof Long){
                cell.setCellValue(Double.valueOf(String.valueOf((Long)value)));
            }else if(value instanceof Integer){
                cell.setCellValue(Double.valueOf(String.valueOf((Integer)value)));
            }else if(value instanceof Number){
                cell.setCellValue(Double.valueOf(String.valueOf((Number)value)));
            }else if(value instanceof Date){
                cell.setCellValue((Date)value);
            }else if(value instanceof Boolean){
                cell.setCellValue((Boolean)value);
            }else {
                cell.setCellValue(String.valueOf(value));
            }
        }
    }
}