package com.unis.base.common.util;


import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.unis.base.common.vo.TyHlyDr;


public class ExcelUtils {

	
	private static void setMaterielStorageTotalColumnWidthOnSheet(Sheet sheet) {
        for (int i = 0; i < 8; i++) {// 设置宽高
        	if( i == 3 || i == 4){
        		sheet.setColumnWidth(i, 4500);
        	}else if(i == 0){
        		sheet.setColumnWidth(i, 11000);
        	}else{
        		sheet.setColumnWidth(i, 7000);
        	}
            Row row = sheet.createRow(i);
    		row.setHeightInPoints(50);
        }
    }

	private static void setBorderOnCell(CellStyle cellStyle) {
        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); //上边框
    }
	
	private static void setDataOnCell(Cell cell, Object cellData) {
        // 如果为空,就不做设值处理
        if (cellData == null) {
            return;
        }

        // String类型数值
        if (cellData.getClass() == String.class) {
            cell.setCellValue((String) cellData);
        }
        // double类型数值
        else if (cellData.getClass() == double.class || cellData.getClass() == Double.class) {
            cell.setCellValue((Double) cellData);
        }
        // float类型数值
        else if (cellData.getClass() == float.class || cellData.getClass() == Float.class) {
            cell.setCellValue((Float) cellData);
        }
        // integer类型数值
        else if (cellData.getClass() == int.class || cellData.getClass() == Integer.class) {
            cell.setCellValue((Integer) cellData);
        }
    }
	
	
	 private static void setDataAndStyleOnRow(CellStyle titleStyle, Row row, List<Object> rowData) {
	        for (int j = 0; j < rowData.size(); j++) {
	            //给单元格设置样式
	            Cell cell = row.createCell(j);
	            cell.setCellStyle(titleStyle);
	            //给单元格赋值
	            Object cellData = rowData.get(j);
	            setDataOnCell(cell, cellData);
	        }
	  }
	 
	 private static void setFontStylePlus(Workbook workbook, CellStyle cellStyle, String fontName, int fontSize, Boolean isBold) {
	        Font font = workbook.createFont();
	        font.setColor((short) 42); // 设置字体颜色
	        font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getColor().getIndex()); // XSSFColor中未找到颜色和short数值的映射,使用HSSFColor来定位颜色的short值
	        font.setFontName(fontName); // 设置字体
	        font.setFontHeightInPoints((short) fontSize);// 设置字体大小
	        font.setBold(isBold);//设置粗体显示
	        cellStyle.setFont(font);
	  }
	 
	 private static void insertColumnName(int rowNum, HSSFSheet sheet, Map<String, String> alias,CellStyle contentFirstStyle) {
			HSSFRow row = sheet.createRow(rowNum);
			

			Set<Entry<String, String>> entrySet = alias.entrySet();
			
			if(rowNum == 0){
				// 列的数量
				int columnCount = 0;
				for (Entry<String, String> entry : entrySet) {
					// 创建第一行的第columnCount个格子
					
						HSSFCell cell = row.createCell(columnCount++);
						// 将此格子的值设置为alias中的键名
						cell.setCellValue(isNull(entry.getValue()).toString());
						cell.setCellStyle(contentFirstStyle);//内容第一行样式
					
					
					
				}
			}else if(rowNum == 1){
				// 列的数量
				int columnCount = 3;
				for (Entry<String, String> entry : entrySet) {
					// 创建第一行的第columnCount个格子
					HSSFCell cell = row.createCell(columnCount++);
					// 将此格子的值设置为alias中的键名
					cell.setCellValue(isNull(entry.getValue()).toString());
					cell.setCellStyle(contentFirstStyle);//内容第一行样式
				}
				//设置前两行的样式
				for (int i = 0; i < 3; i++) {
					HSSFCell cell = row.createCell(i);
					cell.setCellStyle(contentFirstStyle);//内容第一行样式
				}
				
				//设置后两行的样式
				for (int i = 5; i < 7; i++) {
					HSSFCell cell = row.createCell(i);
					cell.setCellStyle(contentFirstStyle);//内容第一行样式
				}
				
			}
			
			 
		}
	    
	    private static Object isNull(Object object) {
			if (object != null) {
				return object;
			} else {
				return "0";
			}
		}
	    
	    public static <T> void pojo3Excel(List<T> pojoList, OutputStream out, LinkedHashMap<String, String> alias,
				String fileName) throws Exception {
			// 创建一个工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();

			// 创建一个表
			HSSFSheet sheet = workbook.createSheet(fileName);
			
			// 设置列宽
	        setMaterielStorageTotalColumnWidthOnSheet(sheet);
			
	        CellStyle contentFirstStyle = workbook.createCellStyle();//内容第一行
	        CellStyle contentStyle = workbook.createCellStyle();//内容


	        //contentFirstStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
	        contentFirstStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

	        // 设置单元格边框
	        setBorderOnCell(contentFirstStyle);
	        setBorderOnCell(contentStyle);

	        // 设置单元格字体
	        setFontStylePlus(workbook, contentFirstStyle, "等线", 14, true);
	        setFontStylePlus(workbook, contentStyle, "等线", 11, false);


	        // 设置单元格居中
	        contentFirstStyle.setAlignment(HorizontalAlignment.CENTER);
	        contentStyle.setAlignment(HorizontalAlignment.CENTER);
	        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
	        
	        /**
		        * 合并
		        */
		         sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));//企业名称
				 sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));//企业类型
				 sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));//企业信用代码
				 sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 4));//产品
				 sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));//联系人
				 sheet.addMergedRegion(new CellRangeAddress(0, 1, 6, 6));//联系方式
				 
	        // 在第一行插入列名
	       insertColumnName(0, sheet, alias,contentFirstStyle);
	       
	      // 在第二行插入列名
	       LinkedHashMap<String, String> columnNameer = new LinkedHashMap<>();
	       columnNameer.put("pp","品牌");
	       columnNameer.put("cpmc","产品名称");
	       insertColumnName(1, sheet, columnNameer,contentFirstStyle);
	       
	       
	             
			// *从第3行开始插入数据
	       LinkedHashMap<String, String> columnName = new LinkedHashMap<>();
	        
	        columnName.put("qymc","企业名称");
	        columnName.put("qylx","企业类型");
	        columnName.put("qyxydm","企业信用代码");
	        columnName.put("pp","品牌");
	        columnName.put("cpmc","产品名称");
	        columnName.put("lxr","联系人");
	        columnName.put("lxfs","联系方式");
			insertColumnDate(2, pojoList, sheet, columnName,contentStyle);
			// 输出表格文件
			try {
				workbook.write(out);
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				//wb.close();
				
			}
		}
	    
	    
	    

		/**
		 * 从指定行开始插入数据
		 * 
		 * @param beginRowNum
		 *            开始行
		 * @param models
		 *            对象数组
		 * @param sheet
		 *            表
		 * @param alias
		 *            列别名
		 * @throws Exception
		 */
		private static <T> void insertColumnDate(int beginRowNum, List<T> models, HSSFSheet sheet,
				Map<String, String> alias,CellStyle contentStyle) throws Exception {
			for (T model : models) {
				// 创建新的一行
				HSSFRow rowTemp = sheet.createRow(beginRowNum++);

				// 获取列的迭代
				Set<Entry<String, String>> entrySet = alias.entrySet();

				// 从第0个格子开始创建
				int columnNum = 0;
				for (Entry<String, String> entry : entrySet) {
					// 获取属性值
					String property = BeanUtils.getProperty(model, entry.getKey());
					// 创建一个格子
					HSSFCell cell = rowTemp.createCell(columnNum++);
					cell.setCellValue(property);
					cell.setCellStyle(contentStyle);// 内容样式
				}
			}
			
			
			//上边循环存值。
			//String[] lie = {"qymc","qylx","qyxydm","lxr","lxfs","pp"}; //需要合并的列，也用作取数据
			String[] lie = {"qymc","qylx","qyxydm","pp","cpmc","lxr","lxfs"}; //需要合并的列，也用作取数据
					        for (int i = 0; i < lie.length; i++) { //循环列
					        	Map<String,Integer> mm = new HashMap<>();
								for (int j = 0; j < models.size()-1; j++) {//循环行  list 是数据库查的 list<Map>集合
									
										T t1 =models.get(j);
										TyHlyDr t11 = new TyHlyDr();
										BeanUtils.copyProperties(t11, t1);
										
										T t2 =models.get(j+1);
										TyHlyDr t22 = new TyHlyDr();
										BeanUtils.copyProperties(t22, t2);
									
									
									String cellup = null;
									String celldown =null;
									if(i != 3 && i != 4){
										 cellup = t11.getQymc();
										 celldown =t22.getQymc();
									}else if(i == 3){
										 cellup = t11.getPp();
										 celldown =t22.getPp();
									}else if(i == 4){
										 cellup = t11.getCpmc();
										 celldown =t22.getCpmc();
									}
									
									if(cellup.equals(celldown)) { 
										
										if(t11.getQymc().equals(t22.getQymc())){
											if(i!=0) { //第一行不判断上一列的值
												if(!mm.containsKey("Q")) {
													mm.put("Q", j+2);//如果一样存起始的行数
												}
											}else {
												if(!mm.containsKey("Q")) {
													mm.put("Q", j+2);//如果一样存起始的行数  这个只用于第一列
												}
											}
											if(j+1>=models.size()-1) {//如果倒数第一列与倒数第二列一样  就执行合并操作
												sheet.addMergedRegion(new CellRangeAddress(mm.get("Q"), j+3, i, i));
												mm.clear();
											}
										}else{
											/*sheet.addMergedRegion(new CellRangeAddress(mm.get("Q"), j+1, i, i));
											mm.clear();*/
										}
												
												
									}else {
										if(mm.containsKey("Q")) {//如果当前行数据与下一行数据不一样就查看是否存在起始值，如果存在就合并
											//sheet.getRow(j+4).getCell(i).setCellValue("");
											sheet.addMergedRegion(new CellRangeAddress(mm.get("Q"), j+2, i, i));
											//sheet.getRow(j+3).getCell(i).setCellStyle(cs);
											mm.clear();
										}

									}
								}
							}

		}

}
