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

import cn.com.goldenwater.core.service.AbstractCrudService;
import cn.com.goldenwater.dcproj.dao.BisInspPlanYearPrgDao;
import cn.com.goldenwater.dcproj.model.BisInspPlanYearPrg;
import cn.com.goldenwater.dcproj.param.BisInspPlanYearPrgParam;
import cn.com.goldenwater.dcproj.service.BisInspPlanYearPrgService;
import cn.com.goldenwater.dcproj.service.OlBisInspOrgService;
import cn.com.goldenwater.dcproj.utils.AdLevelUtil;
import cn.com.goldenwater.dcproj.utils.StringUtils;
import cn.com.goldenwater.id.util.UuidUtil;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.List;
/**
 * @author lhc
 * @date 2022-3-3
 */
@Service
@Transactional
public class BisInspPlanYearPrgServiceImpl extends AbstractCrudService<BisInspPlanYearPrg, BisInspPlanYearPrgParam> implements BisInspPlanYearPrgService {

    @Autowired
    private BisInspPlanYearPrgDao bisInspPlanYearPrgDao;

    @Value("${export.templatePath}")
    private String templatePath;

    @Autowired
    private OlBisInspOrgService olBisInspOrgService;

    private final SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");

    private static final SimpleDateFormat monthFormat = new SimpleDateFormat("yyyy-mm") ;
    public BisInspPlanYearPrgServiceImpl(BisInspPlanYearPrgDao bisInspPlanYearPrgDao) {
        super(bisInspPlanYearPrgDao);
        this.bisInspPlanYearPrgDao = bisInspPlanYearPrgDao;
    }
    @Override
    public int insert(BisInspPlanYearPrg bisInspPlanYearPrg) {

        String uuid = UuidUtil.uuid();  // 生成uuid
        bisInspPlanYearPrg.setId(uuid);
        bisInspPlanYearPrg.setIntm(new Date());
        bisInspPlanYearPrg.setUptm(new Date());
        bisInspPlanYearPrg.setDataStat("0");
        return this.bisInspPlanYearPrgDao.insert(bisInspPlanYearPrg);
    }

    @Override
    public int update(BisInspPlanYearPrg bisInspPlanYearPrg) {
        bisInspPlanYearPrg.setUptm(new Date());
        return this.bisInspPlanYearPrgDao.update(bisInspPlanYearPrg);
    }

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


    @Override
    public List<BisInspPlanYearPrg> getPlanByYearId(String yearId) {
        return this.bisInspPlanYearPrgDao.getPlanByYearId(yearId);
    }

    @Override
    public List<BisInspPlanYearPrg> findPageList(BisInspPlanYearPrgParam bisInspPlanYearPrgParam) {
        List<BisInspPlanYearPrg> list = bisInspPlanYearPrgDao.findList(bisInspPlanYearPrgParam);
        return list;
    }

//    @Override
//    public void export(HttpServletResponse response, BisInspPlanYearPrgParam bisInspPlanYearPrgParam,String type) {
//        String templateFilePath =  templatePath+ File.separator +"planCheckSdMonth.xls";
//        String addvcd = AdLevelUtil.getAddvcd(olBisInspOrgService.getProvince(bisInspPlanYearPrgParam.getOrgId()));
//        if(StringUtils.equals(addvcd, "37")){
//            // 若是 山东省
//            templateFilePath =  templatePath+ File.separator +"planCheckSdMonth37.xls";
//        }
//        File file = new File(templateFilePath);
//        if(file.exists()){
//            try{
//                InputStream inputStream = new FileInputStream(file);
//                Workbook workbook = WorkbookFactory.create(inputStream);
//                exportFile(workbook,bisInspPlanYearPrgParam,type,addvcd);
//                writeToResponse(response,workbook,"监督进度月报.xls");
//            }catch(Exception e){
//                e.printStackTrace();
//            }
//        }
//    }

    @Override
    public void export(HttpServletResponse response, BisInspPlanYearPrgParam bisInspPlanYearPrgParam,String type) {
        String templateFilePath =  templatePath+ File.separator +"planCheckSdMonth.xls";
        String addvcd = AdLevelUtil.getAddvcd(olBisInspOrgService.getProvince(bisInspPlanYearPrgParam.getOrgId()));
        if(StringUtils.equals(addvcd, "37")){
            // 若是 山东省
            templateFilePath =  templatePath+ File.separator +"监督检查开展情况表.xls";
        }
        File file = new File(templateFilePath);
        if(file.exists()){
            try{
                InputStream inputStream = new FileInputStream(file);
                Workbook workbook = WorkbookFactory.create(inputStream);
                exportFile(workbook,bisInspPlanYearPrgParam,type,addvcd);
                writeToResponse(response,workbook,"监督检查开展情况表.xls");
            }catch(Exception e){
                e.printStackTrace();
            }
        }
    }

    @Override
    public void export1(HttpServletResponse response, BisInspPlanYearPrgParam bisInspPlanYearPrgParam,String type) {
        String templateFilePath =  templatePath+ File.separator +"planCheckSdMonth.xls";
        String addvcd = AdLevelUtil.getAddvcd(olBisInspOrgService.getProvince(bisInspPlanYearPrgParam.getOrgId()));
        if(StringUtils.equals(addvcd, "37")){
            // 若是 山东省
            templateFilePath =  templatePath+ File.separator +"业务性监督检查计划表.xls";
        }
        File file = new File(templateFilePath);
        if(file.exists()){
            try{
                InputStream inputStream = new FileInputStream(file);
                Workbook workbook = WorkbookFactory.create(inputStream);
                exportFile1(workbook,bisInspPlanYearPrgParam,type,addvcd);
                writeToResponse(response,workbook,"业务性监督检查计划.xls");
            }catch(Exception e){
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<BisInspPlanYearPrg> findListByMonth(BisInspPlanYearPrgParam bisInspPlanYearPrgParam) {
        return bisInspPlanYearPrgDao.findListByMonth(bisInspPlanYearPrgParam);
    }


//    private void exportFile(Workbook xssfWorkbook, BisInspPlanYearPrgParam bisInspPlanYearPrgParam,String type, String addvcd) throws ParseException {
//        String title = "监督进度月报";
//        List<BisInspPlanYearPrg> list = null;
//        if(null != bisInspPlanYearPrgParam.getChkMnth()){
//            Date mnth = bisInspPlanYearPrgParam.getChkMnth();
//            Instant instant = mnth.toInstant();
//            ZoneId zoneId = ZoneId.systemDefault();
//            LocalDateTime mnLocalDateTime = instant.atZone(zoneId).toLocalDateTime();
//            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM");
//            bisInspPlanYearPrgParam.setChkMnth(monthFormat.parse(dtf.format(mnLocalDateTime)));
//        }
//        if(StringUtils.isNotBlank(type)){
//            title = "月进度汇总";
//            list = bisInspPlanYearPrgDao.findListByMonth(bisInspPlanYearPrgParam);
//        }else{
//            title = "监督检查实施情况月报";
//            list = bisInspPlanYearPrgDao.findList(bisInspPlanYearPrgParam);
//        }
//        int num[] = {0,0,0,0,0,0};
//        Sheet sheet = xssfWorkbook.getSheetAt(0);
//        if(StringUtils.isBlank(type) && StringUtils.isNotBlank(bisInspPlanYearPrgParam.getSttm()) && StringUtils.isNotBlank(bisInspPlanYearPrgParam.getEntm())){
//            title = title+"("+simpleDateFormat.format(simpleDateFormat.parse(bisInspPlanYearPrgParam.getSttm()))+"~"+simpleDateFormat.format(simpleDateFormat.parse(bisInspPlanYearPrgParam.getEntm()))+")";
//        }
//        if(StringUtils.isBlank(type) && StringUtils.isNotNull(bisInspPlanYearPrgParam.getChkMnth()) ){
//            // 设置sheet名称
//            xssfWorkbook.setSheetName(0, monthFormat.format(bisInspPlanYearPrgParam.getChkMnth()) );
//            title = title+"("+monthFormat.format(bisInspPlanYearPrgParam.getChkMnth())+")";
//        }
//        CellStyle styleColor = sheet.getRow(1).getCell(0).getCellStyle();
//        CellStyle styleContent = xssfWorkbook.createCellStyle();
//        styleColor.setAlignment(HorizontalAlignment.CENTER);
//        styleColor.setVerticalAlignment(VerticalAlignment.CENTER);
//        styleContent.setAlignment(HorizontalAlignment.CENTER);
//        styleContent.setVerticalAlignment(VerticalAlignment.CENTER);
//        if(!StringUtils.equals(addvcd, "37")){
//            setCellValue(sheet, 0, 1, title);
//        }
//        if(list!=null && list.size()>0){
//            CellStyle contentStyle = sheet.getRow(3).getCell(0).getCellStyle();
//            contentStyle.setAlignment(HorizontalAlignment.CENTER);
//            contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//            int size = list.size();
//            if(StringUtils.equals(addvcd, "37")){
//                size = size+1;
//            }
//            for(int i = 0;i<size;i++){
//                int cols = 14;
//                if(StringUtils.equals(addvcd, "37")){
//                    Row row = sheet.createRow(3+i);
//                    //山东省
//                    cols = 13;
//                    for(int j = 0;j<=cols;j++){
//                        Cell cell = row.createCell(j);
//                        if(1==j){
//                            contentStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
//                        }
//                        cell.setCellStyle(contentStyle);
//                    }
//                }else{
//                    Row row = sheet.createRow(2+i);
//                    for(int j = 0;j<=cols;j++){
//                        Cell cell = row.createCell(j);
//                        cell.setCellStyle(styleContent);
//                    }
//                }
//            }
//            for (int i = 0; i < list.size(); i++) {
//                BisInspPlanYearPrg bisInspPlanYearPrg = list.get(i);
//                if (StringUtils.equals(addvcd, "37")) {
//                    setAllCell37(sheet, i, bisInspPlanYearPrg);
//                } else {
//                    setAllCell(sheet, i, bisInspPlanYearPrg, num);
//                }
//            }
////            CellStyle style = sheet.getRow(1).getCell(0).getCellStyle();
////            style.setAlignment(HorizontalAlignment.CENTER);
////            style.setVerticalAlignment(VerticalAlignment.CENTER);
//
//            if(StringUtils.equals(addvcd, "37") && list.size()>0){
//                // 山东省 增加汇总行
//                int lastRowIndex = list.size()+3;
//                CellRangeAddress region = new CellRangeAddress(lastRowIndex, lastRowIndex, 0, 1);
//                sheet.addMergedRegion(region);
//                Cell mergeCell = sheet.getRow(lastRowIndex).getCell(0) ;
//                mergeCell.setCellValue("合计");
//                CellStyle mergeCellCellStyle = mergeCell.getCellStyle();
//                mergeCellCellStyle.setAlignment(HorizontalAlignment.CENTER);
//                mergeCellCellStyle.setAlignment(HorizontalAlignment.CENTER);
//                mergeCell.setCellStyle(mergeCellCellStyle);
//                // 从 第3列 到14列
//                for(int i=2 ;i<=13 ;i++){
//                    // 从第4行到最后一行 求和
//                    String colString = CellReference.convertNumToColString(i);
//                    if( 4==i || 5 ==i){
//                        // 求和公式
//                        String sumstring = "SUM(" ;
//                        for(int k= 4;k<=lastRowIndex;k++){
//                            sumstring += "IF(COUNTBLANK("+colString +k+"),0,1)," ;
//                        }
//                        sumstring = sumstring.substring(0,sumstring.length()-1);
//                        sumstring += ")";
//                        // 设置求和
//                        sheet.getRow(lastRowIndex).getCell(i).setCellFormula(sumstring);
//                        continue;
//                    }
//                    // 求和公式
//                    String sumstring = "SUM(" + colString + "4:" + colString + lastRowIndex + ")";
//                    // 设置求和
//                    sheet.getRow(lastRowIndex).getCell(i).setCellFormula(sumstring);
//                }
//            }
//            int lastRowIndex = list.size()+2;
//            setCellValue(sheet, lastRowIndex, 5, String.valueOf(num[0]));
//            setCellValue(sheet, lastRowIndex, 6, String.valueOf(num[1]));
//            setCellValue(sheet, lastRowIndex, 7, String.valueOf(num[2]));
//            setCellValue(sheet, lastRowIndex, 9, String.valueOf(num[3]));
//            setCellValue(sheet, lastRowIndex, 10, String.valueOf(num[4]));
//            setCellValue(sheet, lastRowIndex, 11, String.valueOf(num[5]));
//            Row row = sheet.getRow(lastRowIndex);
//            CellRangeAddress region = new CellRangeAddress(lastRowIndex, lastRowIndex, 0, 4);
//            sheet.addMergedRegion(region);
//            row.getCell(0).setCellValue("总计");
//            row.getCell(0).setCellStyle(style);

//        }
//
//    }
private void exportFile(Workbook xssfWorkbook, BisInspPlanYearPrgParam bisInspPlanYearPrgParam,String type, String addvcd) throws ParseException {
    String title = "月份业务性监督检查计划表";
    if(null != bisInspPlanYearPrgParam.getChkMnth()){
        Date mnth = bisInspPlanYearPrgParam.getChkMnth();
        Instant instant = mnth.toInstant();
        ZoneId zoneId = ZoneId.systemDefault();
        LocalDateTime mnLocalDateTime = instant.atZone(zoneId).toLocalDateTime();
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM");
        bisInspPlanYearPrgParam.setChkMnth(monthFormat.parse(dtf.format(mnLocalDateTime)));
    }
    if(StringUtils.isBlank(type) && StringUtils.isNotBlank(bisInspPlanYearPrgParam.getSttm()) && StringUtils.isNotBlank(bisInspPlanYearPrgParam.getEntm())){
        title = "("+monthFormat.format(bisInspPlanYearPrgParam.getSttm())+"~"+monthFormat.format(bisInspPlanYearPrgParam.getEntm())+")"+title;
    }
    if(StringUtils.isBlank(type) && StringUtils.isNotNull(bisInspPlanYearPrgParam.getChkMnth()) ){
        // 设置sheet名称
        xssfWorkbook.setSheetName(0, monthFormat.format(bisInspPlanYearPrgParam.getChkMnth()) );
        title = "("+monthFormat.format(bisInspPlanYearPrgParam.getChkMnth())+")"+title;
    }
    List<BisInspPlanYearPrg> list = bisInspPlanYearPrgDao.findListByMonth(bisInspPlanYearPrgParam);

    int num[] = {0,0,0,0,0,0};
    Sheet sheet = xssfWorkbook.getSheetAt(0);

    CellStyle styleColor = sheet.getRow(1).getCell(0).getCellStyle();
    CellStyle styleContent = xssfWorkbook.createCellStyle();
    styleColor.setAlignment(HorizontalAlignment.CENTER);
    styleColor.setVerticalAlignment(VerticalAlignment.CENTER);
    styleContent.setAlignment(HorizontalAlignment.CENTER);
    styleContent.setVerticalAlignment(VerticalAlignment.CENTER);
    if(!StringUtils.equals(addvcd, "37")){
        setCellValue(sheet, 0, 1, title);
    }
    if(list!=null && list.size()>0){
        CellStyle contentStyle = sheet.getRow(2).getCell(0).getCellStyle();
        contentStyle.setAlignment(HorizontalAlignment.CENTER);
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        int size = list.size();
        for(int i = 0;i<size+1;i++){
            //单元格列
            int cols = 10;
            Row row = sheet.createRow(2+i);
            //山东省
            for(int j = 0;j<cols;j++){
                Cell cell = row.createCell(j);
                cell.setCellStyle(contentStyle);
            }
        }
        for (int i = 0; i < list.size(); i++) {
            BisInspPlanYearPrg bisInspPlanYearPrg = list.get(i);
            setAllCell(sheet, i, bisInspPlanYearPrg, num);
        }
        CellStyle styleContent1 = xssfWorkbook.createCellStyle();
        styleContent1.setAlignment(HorizontalAlignment.LEFT);
        styleContent1.setVerticalAlignment(VerticalAlignment.CENTER);
        styleContent1.setBorderTop(BorderStyle.THIN); // 上边框
        styleContent1.setBorderBottom(BorderStyle.THIN); // 下边框
        styleContent1.setBorderLeft(BorderStyle.THIN); // 左边框
        styleContent1.setBorderRight(BorderStyle.THIN); // 右边框
        sheet.addMergedRegion(new CellRangeAddress(list.size()+2, list.size()+2, 0, 9));
        sheet.getRow(list.size()+2).getCell(0).setCellStyle(styleContent1);
        String note = "注：1.检查事项为厅业务性监督检查计划名称，具体内容见附件1-2024年度业务性监督检查计划表。\n" +
                "    2.检查形式分为暗访、明查、明查暗访相结合。";
        setCellValue(sheet, list.size()+2, 0, note);
    }
}
    private void exportFile1(Workbook xssfWorkbook, BisInspPlanYearPrgParam bisInspPlanYearPrgParam,String type, String addvcd) throws ParseException {
        String title = "月份业务性监督检查计划表";
        if(null != bisInspPlanYearPrgParam.getChkMnth()){
            Date mnth = bisInspPlanYearPrgParam.getChkMnth();
            Instant instant = mnth.toInstant();
            ZoneId zoneId = ZoneId.systemDefault();
            LocalDateTime mnLocalDateTime = instant.atZone(zoneId).toLocalDateTime();
            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM");
            bisInspPlanYearPrgParam.setChkMnth(monthFormat.parse(dtf.format(mnLocalDateTime)));
        }
        if(StringUtils.isBlank(type) && StringUtils.isNotBlank(bisInspPlanYearPrgParam.getSttm()) && StringUtils.isNotBlank(bisInspPlanYearPrgParam.getEntm())){
            title = "("+monthFormat.format(bisInspPlanYearPrgParam.getSttm())+"~"+monthFormat.format(bisInspPlanYearPrgParam.getEntm())+")"+title;
        }
        if(StringUtils.isBlank(type) && StringUtils.isNotNull(bisInspPlanYearPrgParam.getChkMnth()) ){
            // 设置sheet名称
            xssfWorkbook.setSheetName(0, monthFormat.format(bisInspPlanYearPrgParam.getChkMnth()) );
            title = "("+monthFormat.format(bisInspPlanYearPrgParam.getChkMnth())+")"+title;
        }
        List<BisInspPlanYearPrg> list = bisInspPlanYearPrgDao.findListByMonth(bisInspPlanYearPrgParam);

        int num[] = {0,0,0,0,0,0};
        Sheet sheet = xssfWorkbook.getSheetAt(0);

        CellStyle styleColor = sheet.getRow(1).getCell(0).getCellStyle();
        CellStyle styleContent = xssfWorkbook.createCellStyle();
        styleColor.setAlignment(HorizontalAlignment.CENTER);
        styleColor.setVerticalAlignment(VerticalAlignment.CENTER);
        styleContent.setAlignment(HorizontalAlignment.CENTER);
        styleContent.setVerticalAlignment(VerticalAlignment.CENTER);
        if(!StringUtils.equals(addvcd, "37")){
            setCellValue(sheet, 0, 1, title);
        }
        if(list!=null && list.size()>0){
            CellStyle contentStyle = sheet.getRow(2).getCell(0).getCellStyle();
            contentStyle.setAlignment(HorizontalAlignment.CENTER);
            contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            int size = list.size();
            for(int i = 0;i<size+1;i++){
                //单元格列
                int cols = 6;
                Row row = sheet.createRow(2+i);
                //山东省
                for(int j = 0;j<cols;j++){
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(contentStyle);
                }
            }
            for (int i = 0; i < list.size(); i++) {
                BisInspPlanYearPrg bisInspPlanYearPrg = list.get(i);
                setAllCell1(sheet, i, bisInspPlanYearPrg);
            }
            CellStyle styleContent1 = xssfWorkbook.createCellStyle();
            /*styleColor.setAlignment(HorizontalAlignment.CENTER);
            styleColor.setVerticalAlignment(VerticalAlignment.CENTER);*/
            styleContent1.setAlignment(HorizontalAlignment.LEFT);
            styleContent1.setVerticalAlignment(VerticalAlignment.CENTER);
            styleContent1.setBorderTop(BorderStyle.THIN); // 上边框
            styleContent1.setBorderBottom(BorderStyle.THIN); // 下边框
            styleContent1.setBorderLeft(BorderStyle.THIN); // 左边框
            styleContent1.setBorderRight(BorderStyle.THIN); // 右边框
            sheet.addMergedRegion(new CellRangeAddress(list.size()+2, list.size()+2, 0, 5));
            sheet.getRow(list.size()+2).getCell(0).setCellStyle(styleContent1);
            //String note = ;
            setCellValue(sheet, list.size()+2, 0, "注：1.检查事项为厅业务性监督检查计划名称，具体内容见附件1-2024年度业务性监督检查计划表。\n2.检查形式分为暗访、明查、明查暗访相结合。");
        }
    }

    private void setAllCell37(Sheet sheet, int i, BisInspPlanYearPrg bisInspPlanYearPrg) {
        setCellValue(sheet, 3 + i, 0, String.valueOf(i + 1));
        if(null == bisInspPlanYearPrg.getChkMnth()){
            if(null != bisInspPlanYearPrg.getSttm() && null != bisInspPlanYearPrg.getEntm()){
                setCellValue(sheet, 3 + i, 1, simpleDateFormat.format(bisInspPlanYearPrg.getSttm())+"~"+simpleDateFormat.format(bisInspPlanYearPrg.getEntm()));
            }
        }else{
            Date mnth = bisInspPlanYearPrg.getChkMnth();
            Instant instant = mnth.toInstant();
            ZoneId zoneId = ZoneId.systemDefault();
            LocalDateTime mnLocalDateTime = instant.atZone(zoneId).toLocalDateTime();
            setCellValue(sheet, 3 + i, 1, String.valueOf(mnLocalDateTime.getMonthValue()));
        }
        setCellValue(sheet, 3 + i, 2, bisInspPlanYearPrg.getTrainNum());
        setCellValue(sheet, 3 + i, 3, bisInspPlanYearPrg.getTrainPers());
        if("1".equals(bisInspPlanYearPrg.getIsPlanned()) || StringUtils.isBlank(bisInspPlanYearPrg.getIsPlanned())){
            // 计划内
            setCellValue(sheet, 3 + i, 5, bisInspPlanYearPrg.getChkName());
        }else {
            // 计划外
            setCellValue(sheet, 3 + i, 4, bisInspPlanYearPrg.getUnplannedChkNm());
        }
        if("1".equals(bisInspPlanYearPrg.getChkForm())){
            // 检查形式 暗访
            setCellValue(sheet, 3 + i, 6, bisInspPlanYearPrg.getChkGroup());
            setCellValue(sheet, 3 + i, 7, bisInspPlanYearPrg.getChkPers());
            setCellValue(sheet, 3 + i, 8, bisInspPlanYearPrg.getChkLeder());
            setCellValue(sheet, 3 + i, 9, bisInspPlanYearPrg.getChkPblm());
        }else{
            // 除 暗访 外的 其他检查形式
            setCellValue(sheet, 3 + i, 10, bisInspPlanYearPrg.getChkGroup());
            setCellValue(sheet, 3 + i, 11, bisInspPlanYearPrg.getChkPers());
            setCellValue(sheet, 3 + i, 12, bisInspPlanYearPrg.getChkLeder());
            setCellValue(sheet, 3 + i, 13, bisInspPlanYearPrg.getChkPblm());
        }
    }

//    private void setAllCell(Sheet sheet, int i, BisInspPlanYearPrg bisInspPlanYearPrg,int[] num) {
//        setCellValue(sheet, 2 + i, 0, String.valueOf(i + 1));
//        setCellValue(sheet, 2 + i, 1, bisInspPlanYearPrg.getObjTypeNm());
//        setCellValue(sheet, 2 + i, 2, bisInspPlanYearPrg.getLeadDep());
//        setCellValue(sheet, 2 + i, 3, StringUtils.isEmpty(bisInspPlanYearPrg.getChkName())?bisInspPlanYearPrg.getUnplannedChkNm():bisInspPlanYearPrg.getChkName());
//        setCellValue(sheet, 2 + i, 4, simpleDateFormat.format(bisInspPlanYearPrg.getSttm()) + "~" + simpleDateFormat.format(bisInspPlanYearPrg.getEntm()));
//        setCellValue(sheet, 2 + i, 5, bisInspPlanYearPrg.getChkGroup().toString());
//        setCellValue(sheet, 2 + i, 6, bisInspPlanYearPrg.getChkPers().toString());
//        setCellValue(sheet, 2 + i, 7, bisInspPlanYearPrg.getChkLeder().toString());
//        setCellValue(sheet, 2 + i, 8, bisInspPlanYearPrg.getChkNote());
//        setCellValue(sheet, 2 + i, 9, bisInspPlanYearPrg.getChkPblm().toString());
//        setCellValue(sheet, 2 + i, 10, bisInspPlanYearPrg.getTrainNum().toString());
//        setCellValue(sheet, 2 + i, 11, bisInspPlanYearPrg.getTrainPers().toString());
//        setCellValue(sheet, 2 + i, 12, bisInspPlanYearPrg.getIsFinish().equals("1") ? "是" : "否");
//        setCellValue(sheet, 2 + i, 13, bisInspPlanYearPrg.getIsFinish().equals("0") ? bisInspPlanYearPrg.getNextPlan() : "");
//        setCellValue(sheet, 2 + i, 14, bisInspPlanYearPrg.getNote());
////        num[0]+=bisInspPlanYearPrg.getChkGroup();
////        num[1]+=bisInspPlanYearPrg.getChkPers();
////        num[2]+=bisInspPlanYearPrg.getChkLeder();
////        num[3]+=bisInspPlanYearPrg.getChkPblm();
////        num[4]+=bisInspPlanYearPrg.getTrainNum();
////        num[5]+=bisInspPlanYearPrg.getTrainPers();
//    }
    private void setAllCell(Sheet sheet, int i, BisInspPlanYearPrg bisInspPlanYearPrg,int[] num) {
        setCellValue(sheet, 2 + i, 0, String.valueOf(i + 1));
        setCellValue(sheet, 2 + i, 1, bisInspPlanYearPrg.getLeadDep());
        setCellValue(sheet, 2 + i, 2, StringUtils.isEmpty(bisInspPlanYearPrg.getChkName())?bisInspPlanYearPrg.getUnplannedChkNm():bisInspPlanYearPrg.getChkName());
        setCellValue(sheet, 2 + i, 3, bisInspPlanYearPrg.getChkNote());
        setCellValue(sheet, 2 + i, 4, bisInspPlanYearPrg.getChkFormNm());
        setCellValue(sheet, 2 + i, 5, bisInspPlanYearPrg.getChkGroup());
        setCellValue(sheet, 2 + i, 6, bisInspPlanYearPrg.getChkPers());
        setCellValue(sheet, 2 + i, 7, bisInspPlanYearPrg.getChkPblm());
        setCellValue(sheet, 2 + i, 8, bisInspPlanYearPrg.getIsFinish().equals("1") ? "完成" : "否");
//        setCellValue(sheet, 2 + i, 9, bisInspPlanYearPrg.getIsFinish().equals("0") ? bisInspPlanYearPrg.getNextPlan() : "");
        setCellValue(sheet, 2 + i, 9, bisInspPlanYearPrg.getNote());

//        num[0]+=bisInspPlanYearPrg.getChkGroup();
//        num[1]+=bisInspPlanYearPrg.getChkPers();
//        num[2]+=bisInspPlanYearPrg.getChkLeder();
//        num[3]+=bisInspPlanYearPrg.getChkPblm();
//        num[4]+=bisInspPlanYearPrg.getTrainNum();
//        num[5]+=bisInspPlanYearPrg.getTrainPers();
    }
    private void setAllCell1(Sheet sheet, int i, BisInspPlanYearPrg bisInspPlanYearPrg) {
        setCellValue(sheet, 2 + i, 0, String.valueOf(i + 1));
        setCellValue(sheet, 2 + i, 1, bisInspPlanYearPrg.getLeadDep());
        //setCellValue(sheet, 2 + i, 2, bisInspPlanYearPrg.getUnplannedChkNm());
        setCellValue(sheet, 2 + i, 2, StringUtils.isEmpty(bisInspPlanYearPrg.getChkName())?bisInspPlanYearPrg.getUnplannedChkNm():bisInspPlanYearPrg.getChkName());
        setCellValue(sheet, 2 + i, 3, bisInspPlanYearPrg.getChkFormNm());
        String key = "";
        if(bisInspPlanYearPrg.getNextSttm() != null){
            key = simpleDateFormat.format(bisInspPlanYearPrg.getNextSttm());
        }
        if(bisInspPlanYearPrg.getNextEntm() != null){
            key = key + "~" +simpleDateFormat.format(bisInspPlanYearPrg.getNextEntm());
        }
        setCellValue(sheet, 2 + i, 4, key);
        setCellValue(sheet, 2 + i, 5, bisInspPlanYearPrg.getAddvnm());
    }

    private String getChkFormLabel(String v){
        switch (v){
            case "1":
                return "暗访";
            case "2":
                return "明查";
            case "3":
                return "书面";
            case "4":
                return "四不两直";
            case "5":
                return "明查暗访相结合";
            default:
                return "";
        }
    }
    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(Long.valueOf(value.toString()));
            }else if(value instanceof Integer){
                cell.setCellValue(Integer.valueOf(String.valueOf(value)));
            }else if(value instanceof Number){
                cell.setCellValue(Double.valueOf(String.valueOf(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));
            }
        }
    }

    private void writeToResponse(HttpServletResponse response, Workbook workbook, String name) throws IOException {
        // 修改模板内容导出新模板
        response.setContentType("application/x-msdownload");
        String fileNameURL = URLEncoder.encode(name, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileNameURL + ";filename*=utf-8''" + fileNameURL);
        OutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
        out.close();
    }
}