package com.unis.base.server.service.impl;


import com.unis.base.server.dao.TyChubeilyynbaoDao;
import com.unis.base.server.entity.TyChubeilyynbao;
import com.unis.base.server.example.TyChubeilyynbaoExample;
import com.unis.base.server.example.TyChubeilyynbaoExample.Criteria;
import com.unis.base.server.service.CblyBbService;
import com.unis.common.utils.ResponseWrapper;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;


@Service
public class CblyBbServiceImpl implements CblyBbService {
	

	@Resource
	private TyChubeilyynbaoDao chubeilyynbaoDao;
	
	@Override
	public List<TyChubeilyynbao> importByFile(MultipartFile file, String cjdw, Integer type, String baobiaoshijian,
			Integer yuebaoornianbao)throws IOException{
		// TODO Auto-generated method stub
		 //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(file);
        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<TyChubeilyynbao> list = new ArrayList<TyChubeilyynbao>();
        if (workbook != null) {
            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
                //获得当前sheet工作表
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if (sheet == null) {
                    continue;
                }
                
                /**
                 * 区分地方1 市级2  区级3
                 */
                if(type == null || (sheetNum != 0)){//每一个sheet页都要获取
                	//获取每一个sheet页的名称
                    String sheetName = sheet.getSheetName();
                    if(sheetName.contains("地方")){
                    	type = 1;
                    }else if(sheetName.contains("市级")){
                    	type = 2;
                    }else if(sheetName.contains("区级")){
                    	type = 3;
                    }else{//无效
                    	type = 0;
                    }
                }
                
                /**
                 * 报表时间 从表里获取 如果表里没有 则用前端传的
                 */
                /**
                 * 获取时间
                 * (从0开始)第一行  第一个格
                 */
                Row rowsj = sheet.getRow(1);
                Cell cellsj = rowsj.getCell(2);
                String baobiaoshijian1 = getCellValue(cellsj);
                //System.out.println(baobiaoshijian+"/////"+getCellValue(rowsj.getCell(0))+"****"+getCellValue(rowsj.getCell(2)));

                if(baobiaoshijian1 != null && baobiaoshijian1 != ""){
                	baobiaoshijian = baobiaoshijian1;
                }
                
                
                //获得当前sheet的开始行
                int firstRowNum = sheet.getFirstRowNum();
                //获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();
                //循环除了表头所在行的所有行
                for (int rowNum = firstRowNum + 5; rowNum <= lastRowNum; rowNum++) {
                    //获得当前行
                    Row row = sheet.getRow(rowNum);
                    if (row == null) {
                        continue;
                    }
                    //获得当前行的开始列
                    int firstCellNum = row.getFirstCellNum();
                    //获得当前行的列数
                    int lastCellNum = row.getPhysicalNumberOfCells();
                    String[] cells = new String[row.getPhysicalNumberOfCells()];
                    
                    //循环当前行
                    for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                        Cell cell = row.getCell(cellNum);
                        cells[cellNum] = getCellValue(cell);
                        System.out.println(cells[0]+"******");
                    }
                    //System.out.println(Arrays.toString(cells));
                    //把数组转为对象
                    TyChubeilyynbao chubeilyynbao = new TyChubeilyynbao();
                    //System.out.println(cells.length+"*****");
                    if(cells.length>=30){
                    	chubeilyynbao.setType(type);//类型
                    	
                    	chubeilyynbao.setPzzb(cells[0]);
                        chubeilyynbao.setQckc(cells[1]);
                        
                        chubeilyynbao.setSrhjxj(cells[2]);
                        chubeilyynbao.setSrhjcsczgjxj(cells[3]);
                        chubeilyynbao.setSrhjcsczgjsw(cells[4]);
                        chubeilyynbao.setSrhjcqygjxj(cells[5]);
                        chubeilyynbao.setSrhjcqygjsw(cells[6]);
                        chubeilyynbao.setSrhjsplyzr(cells[7]);
                        chubeilyynbao.setSrhjjk(cells[8]);
                        chubeilyynbao.setSrhjqtsr(cells[9]);
                        
                        chubeilyynbao.setZchjxj(cells[10]);
                        chubeilyynbao.setZchjxsxj(cells[11]);
                        chubeilyynbao.setZchjxssw(cells[12]);
                        chubeilyynbao.setZchjzzsply(cells[13]);
                        chubeilyynbao.setZchjck(cells[14]);
                        chubeilyynbao.setZchjqtzc(cells[15]);
                        
                        chubeilyynbao.setQmkcxj(cells[16]);
                        chubeilyynbao.setQmkcascndhfxj(cells[17]);
                        chubeilyynbao.setQmkcerlingyiwuq(cells[18]);
                        chubeilyynbao.setQmkcerlingyiliu(cells[19]);
                        chubeilyynbao.setQmkcerlingyiqi(cells[20]);
                        chubeilyynbao.setQmkcerlingyiba(cells[21]);
                        chubeilyynbao.setQmkcerlingyijiu(cells[22]);
                        chubeilyynbao.setQmkcerlingerling(cells[23]);
                        chubeilyynbao.setQmkcerlingeryi(cells[24]);
                        chubeilyynbao.setQmkcerlingerer(cells[25]);
                        chubeilyynbao.setQmkcerlingersan(cells[26]);
                    	if(cells.length == 30){//2023年
                    		chubeilyynbao.setQmkcxbz(cells[27]);
                            chubeilyynbao.setQmkcswcc(cells[28]);
                            chubeilyynbao.setDtcb(cells[29]);
                    	}
                    	if(cells.length == 31){//展示2024年
                    		
                            chubeilyynbao.setQmkcerlingersi(cells[27]);
                            chubeilyynbao.setQmkcxbz(cells[28]);
                            chubeilyynbao.setQmkcswcc(cells[29]);
                            chubeilyynbao.setDtcb(cells[30]);
                            
                    	}
                    	
                        String uuid = UUID.randomUUID().toString().replaceAll("-", "");
                        chubeilyynbao.setId(uuid);
                        chubeilyynbao.setCjdw(cjdw);
                        chubeilyynbao.setCjsj(new Date());
                        chubeilyynbao.setYuebaoornianbao(yuebaoornianbao);
                        chubeilyynbao.setBaobiaoshijian(baobiaoshijian);
                    }
                    
                    list.add(chubeilyynbao);
                }
                
            }
            workbook.close();
        }
		
		return list;
	}

	
	public  Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith("xls")) {
                //2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith("xlsx")) {
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }
	
	public String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        /*if (cell.getCellType() == CellType.NUMERIC) {
            cell.setCellType(CellType.STRING);
        }*/
        //判断数据的类型
        switch (cell.getCellType()) {
            case NUMERIC: //数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: //空值
                cellValue = "";
                break;
            case ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }



	@Override
	public ResponseWrapper importByEntityResult(MultipartFile file, String cjdw, Integer type, String baobiaoshijian,
			Integer yuebaoornianbao) throws IOException {
		// TODO Auto-generated method stub
		List<TyChubeilyynbao> list = importByFile(file,cjdw,type,baobiaoshijian,yuebaoornianbao);

		if(list.size()>0){
			
			int insertNum= 0;
			int failNum= 0;
			int size= list.size();
			
			
				/**
				 * 查询是否有当前年份或者当前月份的数据
				 * 有则删除 无则新增  以导入数据为准
				 */
				TyChubeilyynbaoExample chubeilyynbaoExample = new TyChubeilyynbaoExample();
				Criteria createCriteria = chubeilyynbaoExample.createCriteria();
				createCriteria.andBaobiaoshijianEqualTo(list.get(0).getBaobiaoshijian());
				List<TyChubeilyynbao> dataBaseList = chubeilyynbaoDao.selectByExample(chubeilyynbaoExample);
				if(dataBaseList.size()>0){
					//删除 新增
					 chubeilyynbaoDao.deleteByExample(chubeilyynbaoExample);
				}
				//新增  list是要新增的数据
				for (int i = 0; i < size; i++) {
					list.get(i).setInsertnumber(i);
					int res = chubeilyynbaoDao.insert(list.get(i));
					if(res>0){
						insertNum+= 1;
					} else{
						failNum+= 1;
					}
				}
				
			return ResponseWrapper.success("共"+size+",成功新增"+insertNum+",失败"+failNum);
		}else{
			return ResponseWrapper.fail("未读取到有效数据或存在无法解析的属性");
		}
		
	}


	@Override
	public List<TyChubeilyynbao> queryList(TyChubeilyynbao chubeilyynbao) {
		// TODO Auto-generated method stub
		
		TyChubeilyynbaoExample chubeilyynbaoExample = new TyChubeilyynbaoExample();
		Criteria createCriteria = chubeilyynbaoExample.createCriteria();
		if(chubeilyynbao.getType()!= null){
			createCriteria.andTypeEqualTo(chubeilyynbao.getType());
		}
		if(chubeilyynbao.getBaobiaoshijian() != null && chubeilyynbao.getBaobiaoshijian() != ""){
			createCriteria.andBaobiaoshijianEqualTo(chubeilyynbao.getBaobiaoshijian());
		}
		if(chubeilyynbao.getYuebaoornianbao() != null ){
			createCriteria.andYuebaoornianbaoEqualTo(chubeilyynbao.getYuebaoornianbao());
		}
		if(chubeilyynbao.getCjdw()!= null){
			createCriteria.andCjdwEqualTo(chubeilyynbao.getCjdw());
		}
		chubeilyynbaoExample.setOrderByClause("insertnumber asc");
		
		return chubeilyynbaoDao.selectByExample(chubeilyynbaoExample);
	}

	@Override
	public HSSFWorkbook getCells(Integer sumColumn,HSSFSheet sheet,HSSFWorkbook wb,String baobiaoshijian,Integer type,Integer yuebaoornianbao) {
		
		/** 第三步，设置样式以及字体样式*/
        HSSFCellStyle titleStyle = createTitleCellStyle(wb);
        HSSFCellStyle headerStyle = createHeadCellStyle(wb);
        HSSFCellStyle contentStyle = createContentCellStyle(wb);
		
        
        /** 第四步，创建标题 ,合并标题单元格 */
        // 行号
        int rowNum = 0;
        // 创建第一页的第一行，索引从0开始
        HSSFRow row0 = sheet.createRow(rowNum++);
        row0.setHeight((short) 500);// 设置行高
        
        String title = sheet.getSheetName();
        HSSFCell c00 = row0.createCell(0);
        c00.setCellValue(title);
        c00.setCellStyle(titleStyle);
        // 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, sumColumn-1));//标题合并单元格操作，29为总列数 
        
     // 第二行
        HSSFRow row1 = sheet.createRow(rowNum++);
        row1.setHeight((short) 500);
        //String[] row_first = {"填表单位：", "", "", "", "", " 2019年第2季度 ", ""};
        
        
        for (int i = 0; i < sumColumn; i++) {
            HSSFCell tempCell = row1.createCell(i);
            tempCell.setCellStyle(headerStyle);
            if (i == 0) {
                tempCell.setCellValue("");
            } else if (i == 2) {
                tempCell.setCellStyle(headerStyle);
                if(baobiaoshijian!=null){
                    tempCell.setCellValue(baobiaoshijian);
               }
            } else if(i == sumColumn-1){
                tempCell.setCellValue("计量单位：吨");
            }
        }
        
        // 合并
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 28));
        
        
      //第三行
        HSSFRow row2 = sheet.createRow(rowNum++);
        row2.setHeight((short) 500);
        String[] row_second = {"品种指标","期初库存","收入合计","","","","","","", "","支出合计", "","","","","","期末库存"};
        for (int i = 0; i < sumColumn; i++) {
        	HSSFCell tempCell = row2.createCell(i);
        	if(i<=16){
        		 tempCell.setCellValue(row_second[i]);
        	}
        	if(i == sumColumn-1){
        		tempCell.setCellValue("动态储备[S]");
        	}
            tempCell.setCellStyle(headerStyle);
        }

        // 合并
        
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 9));//收入合计
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 10, 15));//支出合计
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 16, 28));//期末库存
        
      //第四行
        HSSFRow row3 = sheet.createRow(rowNum++);
        row3.setHeight((short) 500);
        String[] row_third = {"","","小计","从生产者购进","","从企业购进","","商品粮油转入","进口","其他收入","小计","销售","","转作商品粮油",
        		"出口","其他支出","小计","按生产年度划分"};
        for (int i = 0; i < sumColumn; i++) {
        	HSSFCell tempCell = row3.createCell(i);
        	if(i<=17){
        		tempCell.setCellValue(row_third[i]);
        	}else if(i == 28){
                tempCell.setCellValue("小包装");
            }else if(i == 29){
                tempCell.setCellValue("省外储存");
            }
            tempCell.setCellStyle(headerStyle);
        }

        // 合并
        
        sheet.addMergedRegion(new CellRangeAddress(3, 3, 3, 4));//从生产者购进
        sheet.addMergedRegion(new CellRangeAddress(3, 3, 5, 6));//从企业购进
        sheet.addMergedRegion(new CellRangeAddress(3, 3, 11, 12));//支出合计 销售
        sheet.addMergedRegion(new CellRangeAddress(3, 3, 17, 27));//按生产制度划分
        
        
		
      //第五行
        HSSFRow row4 = sheet.createRow(rowNum++);
        row4.setHeight((short) 500);
        String[] row_fourth = {"","","","小计","省外","小计","省外","","","","","小计","省外","","","","","小计","2015年及以前","2016年","2017年",
        		"2018年","2019年","2020年","2021年","2022年","2023年","2024年","","",""};
        for (int i = 0; i < sumColumn; i++) {
        	HSSFCell tempCell = row4.createCell(i);
        	tempCell.setCellValue(row_fourth[i]);
            tempCell.setCellStyle(headerStyle);
        }

        // 合并
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 28, 28));//小包装
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 29, 29));//省外储存
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 16, 16));//小计
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 13, 13));//转作商品粮油
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 14, 14));//出口
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 15, 15));//其他支出
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 7, 7));//商品粮油转入
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 8, 8));//进口
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 9, 9));//其他收入
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 2, 2));//小计
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 10, 10));//支出合计小计
        
        sheet.addMergedRegion(new CellRangeAddress(2, 4, 1, 1));//期初库存
        sheet.addMergedRegion(new CellRangeAddress(2, 4, 0, 0));//品种指标
        sheet.addMergedRegion(new CellRangeAddress(2, 4, 30, 30));//动态储备[S]
        
       
        //放入数据
        TyChubeilyynbaoExample chubeilyynbaoExample = new TyChubeilyynbaoExample();
		Criteria createCriteria = chubeilyynbaoExample.createCriteria();
		if(type!= null){
			createCriteria.andTypeEqualTo(type);
		}
		if(baobiaoshijian != null && baobiaoshijian != ""){
			createCriteria.andBaobiaoshijianEqualTo(baobiaoshijian);
		}
		if(yuebaoornianbao != null ){
			createCriteria.andYuebaoornianbaoEqualTo(yuebaoornianbao);
		}
		chubeilyynbaoExample.setOrderByClause("insertnumber asc");
		List<TyChubeilyynbao> list = chubeilyynbaoDao.selectByExample(chubeilyynbaoExample);
		
		for (int j = 0; j < list.size()+5; j++) {
			sheet.setColumnWidth(j, 4500);
		}
		
        for (TyChubeilyynbao tyChubeilyynbao : list) {
        	HSSFRow tempRow = sheet.createRow(rowNum++);
            tempRow.setHeight((short) 350);
            
            for (int j = 0; j < sumColumn; j++) {//循环单元格
				//给每一个格子赋值
            	HSSFCell tempCell = tempRow.createCell(j);
                tempCell.setCellStyle(contentStyle);
                if(j==0){
                	if(tyChubeilyynbao.getPzzb().equals("折合原粮合计")){
                		tempCell.setCellValue("    折合原粮合计");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际粮合计[S]")){
                		tempCell.setCellValue("        实际粮合计[S]");
                	}else if(tyChubeilyynbao.getPzzb().equals("贸易粮合计")){
                		tempCell.setCellValue("    贸易粮合计");
                	}else if(tyChubeilyynbao.getPzzb().equals("谷物合计")){
                		tempCell.setCellValue("    谷物合计");
                	}else if(tyChubeilyynbao.getPzzb().equals("小麦及小麦粉")){
                		tempCell.setCellValue("        小麦及小麦粉");
                	}else if(tyChubeilyynbao.getPzzb().equals("折合小麦")){
                		tempCell.setCellValue("            折合小麦");
                	}else if(tyChubeilyynbao.getPzzb().equals("折合面粉")){
                		tempCell.setCellValue("            折合面粉");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际小麦")){
                		tempCell.setCellValue("            实际小麦");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际面粉")){
                		tempCell.setCellValue("            实际面粉");
                	}else if(tyChubeilyynbao.getPzzb().equals("稻谷及大米")){
                		tempCell.setCellValue("        稻谷及大米");
                	}else if(tyChubeilyynbao.getPzzb().equals("折合稻谷合计")){
                		tempCell.setCellValue("            折合稻谷合计");
                	}else if(tyChubeilyynbao.getPzzb().equals("折合早籼稻")){
                		tempCell.setCellValue("                折合早籼稻");
                	}else if(tyChubeilyynbao.getPzzb().equals("折合中晚籼稻")){
                		tempCell.setCellValue("                折合中晚籼稻");
                	}else if(tyChubeilyynbao.getPzzb().equals("折合粳稻")){
                		tempCell.setCellValue("                折合粳稻");
                	}else if(tyChubeilyynbao.getPzzb().equals("折合大米合计")){
                		tempCell.setCellValue("            折合大米合计");
                	}else if(tyChubeilyynbao.getPzzb().equals("折合早籼米")){
                		tempCell.setCellValue("                折合早籼米");
                	}else if(tyChubeilyynbao.getPzzb().equals("折合中晚籼米")){
                		tempCell.setCellValue("                折合中晚籼米");
                	}else if(tyChubeilyynbao.getPzzb().equals("折合粳米")){
                		tempCell.setCellValue("                折合粳米");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际稻谷合计")){
                		tempCell.setCellValue("            实际稻谷合计");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际早籼稻")){
                		tempCell.setCellValue("                实际早籼稻");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际中晚籼稻")){
                		tempCell.setCellValue("                实际中晚籼稻");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际粳稻")){
                		tempCell.setCellValue("                实际粳稻");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际大米合计")){
                		tempCell.setCellValue("            实际大米合计");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际早籼米")){
                		tempCell.setCellValue("                实际早籼米");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际中晚籼米")){
                		tempCell.setCellValue("                实际中晚籼米");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际粳米")){
                		tempCell.setCellValue("                实际粳米");
                	}else if(tyChubeilyynbao.getPzzb().equals("折合玉米")){
                		tempCell.setCellValue("        折合玉米");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际玉米")){
                		tempCell.setCellValue("            实际玉米");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际玉米面和玉米渣")){
                		tempCell.setCellValue("            实际玉米面和玉米渣");
                	}else if(tyChubeilyynbao.getPzzb().equals("高粱")){
                		tempCell.setCellValue("        高粱");
                	}else if(tyChubeilyynbao.getPzzb().equals("大麦")){
                		tempCell.setCellValue("        大麦");
                	}else if(tyChubeilyynbao.getPzzb().equals("谷子")){
                		tempCell.setCellValue("        谷子");
                	}else if(tyChubeilyynbao.getPzzb().equals("其他谷物")){
                		tempCell.setCellValue("        其他谷物");
                	}else if(tyChubeilyynbao.getPzzb().equals("豆类合计")){
                		tempCell.setCellValue("    豆类合计");
                	}else if(tyChubeilyynbao.getPzzb().equals("折合大豆")){
                		tempCell.setCellValue("        折合大豆");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际大豆")){
                		tempCell.setCellValue("            实际大豆");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际豆饼豆粕")){
                		tempCell.setCellValue("            实际豆饼豆粕");
                	}else if(tyChubeilyynbao.getPzzb().equals("其他豆类")){
                		tempCell.setCellValue("        其他豆类");
                	}else if(tyChubeilyynbao.getPzzb().equals("薯类折粮合计")){
                		tempCell.setCellValue("    薯类折粮合计");
                	}else if(tyChubeilyynbao.getPzzb().equals("马铃薯折粮")){
                		tempCell.setCellValue("        马铃薯折粮");
                	}else if(tyChubeilyynbao.getPzzb().equals("其他薯类折粮")){
                		tempCell.setCellValue("        其他薯类折粮");
                	}else if(tyChubeilyynbao.getPzzb().equals("其他粮合计")){
                		tempCell.setCellValue("    其他粮合计");
                	}else if(tyChubeilyynbao.getPzzb().equals("成品粮合计")){
                		tempCell.setCellValue("    成品粮合计");
                	}else if(tyChubeilyynbao.getPzzb().equals("油及料折油合计")){
                		tempCell.setCellValue("    油及料折油合计");
                	}else if(tyChubeilyynbao.getPzzb().equals("油脂合计")){
                		tempCell.setCellValue("    油脂合计");
                	}else if(tyChubeilyynbao.getPzzb().equals("油料合计")){
                		tempCell.setCellValue("    油料合计");
                	}else if(tyChubeilyynbao.getPzzb().equals("折合菜籽油")){
                		tempCell.setCellValue("    折合菜籽油");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际菜籽油")){
                		tempCell.setCellValue("        实际菜籽油");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际油菜籽")){
                		tempCell.setCellValue("        实际油菜籽");
                	}else if(tyChubeilyynbao.getPzzb().equals("折合花生油")){
                		tempCell.setCellValue("    折合花生油");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际花生油")){
                		tempCell.setCellValue("        实际花生油");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际花生果")){
                		tempCell.setCellValue("        实际花生果");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际大豆油")){
                		tempCell.setCellValue("    实际大豆油");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际棉籽油")){
                		tempCell.setCellValue("    实际棉籽油");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际棕榈油")){
                		tempCell.setCellValue("    实际棕榈油");
                	}else if(tyChubeilyynbao.getPzzb().equals("折合葵花油")){
                		tempCell.setCellValue("    折合葵花油");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际葵花油")){
                		tempCell.setCellValue("        实际葵花油");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际葵花籽")){
                		tempCell.setCellValue("        实际葵花籽");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际油茶籽油")){
                		tempCell.setCellValue("    实际油茶籽油");
                	}else if(tyChubeilyynbao.getPzzb().equals("折合芝麻油")){
                		tempCell.setCellValue("    折合芝麻油");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际芝麻油")){
                		tempCell.setCellValue("        实际芝麻油");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际芝麻")){
                		tempCell.setCellValue("        实际芝麻");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际玉米油")){
                		tempCell.setCellValue("    实际玉米油");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际米糠油")){
                		tempCell.setCellValue("    实际米糠油");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际调和油")){
                		tempCell.setCellValue("    实际调和油");
                	}else if(tyChubeilyynbao.getPzzb().equals("其他油及其他油料折油")){
                		tempCell.setCellValue("    其他油及其他油料折油");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际其他油")){
                		tempCell.setCellValue("        实际其他油");
                	}else if(tyChubeilyynbao.getPzzb().equals("实际其他油料")){
                		tempCell.setCellValue("        实际其他油料");
                	}else{
                		tempCell.setCellValue(tyChubeilyynbao.getPzzb());
                	}
                	
                }else if(j==1){
                	tempCell.setCellValue(tyChubeilyynbao.getQckc());
                }else if(j==2){
                	tempCell.setCellValue(tyChubeilyynbao.getSrhjxj());
                }else if(j==3){
                	tempCell.setCellValue(tyChubeilyynbao.getSrhjcsczgjxj());
                }else if(j==4){
                	tempCell.setCellValue(tyChubeilyynbao.getSrhjcsczgjsw());
                }else if(j==5){
                	 tempCell.setCellValue(tyChubeilyynbao.getSrhjcqygjxj());
                }else if(j==6){
                	tempCell.setCellValue(tyChubeilyynbao.getSrhjcqygjsw());
                }else if(j==7){
                	tempCell.setCellValue(tyChubeilyynbao.getSrhjsplyzr());
                }else if(j==8){
                	 tempCell.setCellValue(tyChubeilyynbao.getSrhjjk());
                }else if(j==9){
                	tempCell.setCellValue(tyChubeilyynbao.getSrhjqtsr());
                }else if(j==10){
                	tempCell.setCellValue(tyChubeilyynbao.getZchjxj());
                }else if(j==11){
                	tempCell.setCellValue(tyChubeilyynbao.getZchjxsxj());
                }else if(j==12){
                	 tempCell.setCellValue(tyChubeilyynbao.getZchjxssw());
                }else if(j==13){
                	 tempCell.setCellValue(tyChubeilyynbao.getZchjzzsply());
                }else if(j==14){
                	 tempCell.setCellValue(tyChubeilyynbao.getZchjck());
                }else if(j==15){
                	 tempCell.setCellValue(tyChubeilyynbao.getZchjqtzc());
                }else if(j==16){
                     tempCell.setCellValue(tyChubeilyynbao.getQmkcxj());
                }else if(j==17){
                	tempCell.setCellValue(tyChubeilyynbao.getQmkcascndhfxj());
                }else if(j==18){
                	tempCell.setCellValue(tyChubeilyynbao.getQmkcerlingyiwuq());
                }else if(j==19){
                	tempCell.setCellValue(tyChubeilyynbao.getQmkcerlingyiliu());
                }else if(j==20){
                	tempCell.setCellValue(tyChubeilyynbao.getQmkcerlingyiqi());
                }else if(j==21){
                	tempCell.setCellValue(tyChubeilyynbao.getQmkcerlingyiba());
                }else if(j==22){
                	tempCell.setCellValue(tyChubeilyynbao.getQmkcerlingyijiu());
                }else if(j==23){
                	 tempCell.setCellValue(tyChubeilyynbao.getQmkcerlingerling());
                }else if(j==24){
                	tempCell.setCellValue(tyChubeilyynbao.getQmkcerlingeryi());
                }else if(j==25){
                	 tempCell.setCellValue(tyChubeilyynbao.getQmkcerlingerer());
                }else if(j==26){
                	 tempCell.setCellValue(tyChubeilyynbao.getQmkcerlingersan());
                }else if(j==27){
                	tempCell.setCellValue(tyChubeilyynbao.getQmkcerlingersi());
                }else if(j==28){
                	tempCell.setCellValue(tyChubeilyynbao.getQmkcxbz());
                }else if(j==29){
                	tempCell.setCellValue(tyChubeilyynbao.getQmkcswcc());
                }else if(j==30){
                	tempCell.setCellValue(tyChubeilyynbao.getDtcb());
               }
			}
		}
        return wb;
		
	}

	@SuppressWarnings("resource")
	@Override
	public HSSFWorkbook exportByEntity(Integer type, String baobiaoshijian, Integer yuebaoornianbao) {
		// TODO Auto-generated method stub
		
		/**
		 * 变量
		 */
		int sumColumn = 31;//31为总列数 2024年
		/**
		 * 导出
		 */
		/** 第一步，创建一个Workbook，对应一个Excel文件  */
        HSSFWorkbook wb = new HSSFWorkbook();  
        
        /** 第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet 区分地方1 市级2  区级3 */
        //HSSFSheet sheet = wb.createSheet("测试");
        // 获取要删除的Sheet的索引
        //int sheetIndex = wb.getSheetIndex("测试");
        // 删除Sheet
        //wb.removeSheetAt(sheetIndex);
        if(type!= null){
        	if(type == 1){
        		HSSFSheet sheet = wb.createSheet("地方储备粮油合计");
        		 wb = this.getCells(sumColumn,sheet,wb,baobiaoshijian,type,yuebaoornianbao);
        	}else if(type == 2){
        		HSSFSheet sheet = wb.createSheet("市级储备粮油");
        		 wb = this.getCells(sumColumn,sheet,wb,baobiaoshijian,type,yuebaoornianbao);
        	}else if(type == 3){
        		HSSFSheet sheet = wb.createSheet("区级储备粮油");
        		 wb = this.getCells(sumColumn,sheet,wb,baobiaoshijian,type,yuebaoornianbao);
        	}
        	 
        }else{
        	/**
        	 * 全部导出
        	 */
        	for (int i = 1; i < 4; i++) {
        		if(i == 1){
        			HSSFSheet sheet = wb.createSheet("地方储备粮油合计");
        			wb = this.getCells(sumColumn,sheet,wb,baobiaoshijian,i,yuebaoornianbao);
            	}else if(i == 2){
            		HSSFSheet sheet = wb.createSheet("市级储备粮油");
            		wb = this.getCells(sumColumn,sheet,wb,baobiaoshijian,i,yuebaoornianbao);
            	}else if(i == 3){
            		HSSFSheet sheet = wb.createSheet("区级储备粮油");
            		wb = this.getCells(sumColumn,sheet,wb,baobiaoshijian,i,yuebaoornianbao);
            	}
			}
        }
        
       return wb;
        

	}
	
	
	/**
     * 创建标题样式
     * @param wb
     * @return
     */
    private static HSSFCellStyle createTitleCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中  
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); 
        cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());//背景颜色
        
        HSSFFont headerFont1 = (HSSFFont) wb.createFont(); // 创建字体样式  
        headerFont1.setBold(true); //字体加粗 
        headerFont1.setFontName("宋体"); // 设置字体类型  
        headerFont1.setFontHeightInPoints((short) 10); // 设置字体大小  
        cellStyle.setFont(headerFont1); // 为标题样式设置字体样式 

        return cellStyle;
    }
    
	/**
     * 创建表头样式
     * @param wb
     * @return
     */
    private static HSSFCellStyle createHeadCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);// 设置自动换行  
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
        cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中   
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); 
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);  
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式  
        headerFont.setBold(true); //字体加粗  
        headerFont.setFontName("宋体"); // 设置字体类型  
        headerFont.setFontHeightInPoints((short) 10); // 设置字体大小  
        cellStyle.setFont(headerFont); // 为标题样式设置字体样式  

        return cellStyle;
    }
    
	/**
     * 创建内容样式
     * @param wb
     * @return
     */
    private  HSSFCellStyle createContentCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        //cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        //cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        //cellStyle.setWrapText(true);// 设置自动换行  
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        
        // 生成12号字体
         HSSFFont font = wb.createFont();
         font.setColor((short)8);
         font.setFontHeightInPoints((short) 9);
        cellStyle.setFont(font);

        return cellStyle;
    }
    

	
}
