package com.winsun.utils;

import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 根据TreeNode生成Exccel表
 *
 * @author ZERO
 *
 */
public class ExcelTreeNodeHelper {

	/**
	 * 获取excel对象
	 * @param treeNode 表头对象
	 * @param dataList 数据集
	 * @return
	 */
	public static XSSFWorkbook getWorkbook(TreeNode treeNode, List<Map<String, Object>> dataList) {
		XSSFWorkbook workBook = getWorkbook(treeNode);// 创建工作本
		Sheet sheet = workBook.getSheetAt(0);// 创建工作表
		int max = treeNode.getMaxLevel();// 获取最大的层次 n-1

		if(dataList==null||dataList.size()==0) {
			return workBook;
		}
		XSSFCellStyle style = workBook.createCellStyle(); // 样式对象
		style.setAlignment(HorizontalAlignment.CENTER);// 水平居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
		XSSFFont font = workBook.createFont();
		font.setFontName("Arial");
		style.setFont(font);

		System.out.println("========================================");
		List<String> keyList = treeNode.getKeyList();
		Map<String, String> desensitizationTypeMap = treeNode.getDesensitizationTypeMap(); //数据脱敏集
		Map<String, String> cellFormatMap = treeNode.getCellFormatMap(); //单元格式化
		System.out.println(cellFormatMap);
		Iterator<Map<String, Object>> it = dataList.iterator();
		for (int i = max + 1; it.hasNext(); i++) {
			Map dataMap = (Map) it.next();
			Iterator<String> it2 = keyList.iterator();
			Row row = sheet.createRow(i);
			for (int j = 0; it2.hasNext(); j++) {
				String key = it2.next();
				Object obj = dataMap.get(key);
				if (obj == null) {
					continue;
				}
				Cell cell = row.createCell(j);
//				style.setAlignment(XSSFCellStyle.ALIGN_LEFT);// 水平左对齐
				cell.setCellStyle(style);
				if ("null".equals(String.valueOf(obj))) {
					cell.setCellValue("");
					continue;
				}
				String dataValue = String.valueOf(obj);
				String type = desensitizationTypeMap.get(key);
				if(type != null){
					dataValue = DesensitizationRulesUtils.desensiteData(dataValue, type);
				}

				String cellFormat = (String) cellFormatMap.get(key);
				if(cellFormat != null){
					XSSFCellStyle style1 = workBook.createCellStyle(); // 样式对象
					style1.setAlignment(HorizontalAlignment.CENTER);// 水平居中
					style1.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
//					style1.setAlignment(HorizontalAlignment.ALIGN_LEFT);// 水平左对齐
					style1.setFont(font);
					XSSFDataFormat df = workBook.createDataFormat();
					style1.setDataFormat(df.getFormat(cellFormat));
					cell.setCellStyle(style1);

					obj = convertDecimal(dataValue);

					if(obj instanceof Integer){
						cell.setCellValue((Integer) obj);
					}else if(obj instanceof Double){
						cell.setCellValue((Double) obj);
					}else if(obj instanceof BigDecimal){
						BigDecimal b = (BigDecimal) obj;
						cell.setCellValue(b.doubleValue());
					}else {
						cell.setCellStyle(style);
						cell.setCellValue(dataValue);
					}
				}else{
					cell.setCellStyle(style);
					cell.setCellValue(dataValue);
				}


			}
		}
		return workBook;
	}


	/**
	 * 字符串数字类型转换
	 * @param dataValue
	 * @return
	 */
	public static Object convertDecimal( String dataValue){
		//判断整数
		String regex="^-?(\\d+)$|^0$";
		Pattern pattern= Pattern.compile(regex);
		Matcher matcher=pattern.matcher(dataValue);
		if(matcher.find()){
			return Integer.parseInt(dataValue);
		}
		//判断小数
		regex="^-?([1-9]\\d*\\.?\\d+)$|^-?(0\\.\\d*)$|^0\\.0+$";
		pattern= Pattern.compile(regex);
		matcher=pattern.matcher(dataValue);
		if(matcher.find()){
			return Double.parseDouble(dataValue);
		}
		return dataValue;
	}

	public static void createSheetData(XSSFWorkbook workBook, TreeNode treeNode, String sheetName, int sheetIndex){
		Sheet sheet = workBook.createSheet();// 创建工作表
		workBook.setSheetName(sheetIndex, sheetName);
		int max = treeNode.getMaxLevel();// 获取最大的层次 n-1
		int[] arr = new int[max + 1];// n层
		for (int i = 0; i < arr.length; i++) {// 创建行
			sheet.createRow(i);// 创建n行表头
			arr[i] = 0;
		}
		setTableHeader(treeNode.getChildList(), sheet, arr);

		List dataList = new ArrayList<Object>();
		XSSFCellStyle style = workBook.createCellStyle(); // 样式对象
		style.setAlignment(HorizontalAlignment.CENTER);// 水平居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
		XSSFFont font = workBook.createFont();
		font.setFontName("Arial");
		style.setFont(font);

		List keyList = treeNode.getKeyList();
		Iterator it = dataList.iterator();
		for (int i = max + 1; it.hasNext(); i++) {
			Map dataMap = (Map) it.next();
			Iterator it2 = keyList.iterator();
			Row row = sheet.createRow(i);
			for (int j = 0; it2.hasNext(); j++) {
				Object obj = dataMap.get(it.next());
				if (obj == null) {
					continue;
				}
				Cell cell = row.createCell(j);
				cell.setCellStyle(style);
				if (!"null".equals(String.valueOf(obj))) {
					cell.setCellValue(String.valueOf(obj));
				} else {
					cell.setCellValue("");
				}
			}
		}
	}

	public static XSSFWorkbook getWorkbook(TreeNode treeNode) {
		XSSFWorkbook workBook = new XSSFWorkbook();// 创建工作本
//		TreeNode treeNode = TestTree.getTreeNode();
		Sheet sheet = workBook.createSheet();// 创建工作表
		int max = treeNode.getMaxLevel();// 获取最大的层次 n-1
		int[] arr = new int[max + 1];// n层
		for (int i = 0; i < arr.length; i++) {// 创建行
			sheet.createRow(i);// 创建n行表头
			arr[i] = 0;
		}
		setTableHeader(treeNode.getChildList(), sheet, arr);
		return workBook;
	}


	/**
	 * 利用递归遍历子数据，向excel填入excel表头
	 *
	 * @param childList
	 * @param sheet
	 * @param arr
	 */
	private static void setTableHeader(List<TreeNode> childList, Sheet sheet, int[] arr) {
		for (TreeNode node : childList) {
			CellRangeAddress cra;
			if (node.isLeaf()) {
				cra = new CellRangeAddress(node.level, arr.length - 1, arr[node.level],
						arr[node.level] + node.leafSize - 1);
				if (node.level != arr.length - 1) {
					for (int i = node.level + 1; i < arr.length; i++) {
						arr[i] += 1;
					}
				}
			} else {// 不是叶子节点
				cra = new CellRangeAddress(node.level, node.level, arr[node.level],
						arr[node.level] + node.leafSize - 1);
			}
			if (node.level != arr.length - 1 || arr[node.level] != arr[node.level] + node.leafSize - 1)
				sheet.addMergedRegion(cra);// 合并单元格

			Row row = sheet.getRow(node.level);
			Cell cell = row.createCell(arr[node.level]);

			XSSFCellStyle cellStyle = (XSSFCellStyle) sheet.getWorkbook().createCellStyle();// 设置自动换行以及居中样式
			short color =  (short)node.getColor();
//			System.out.println("color:"+color);
			if(color != 0){
				cellStyle.setFillForegroundColor(color);
				cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			}
			cellStyle.setWrapText(true);
			cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
			cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
			cell.setCellStyle(cellStyle);
			cell.setCellValue(node.getNodeName());

			arr[node.level] += node.leafSize;// 更新X轴指针位置
			if (!node.isLeaf()) {
				setTableHeader(node.getChildList(), sheet, arr);
			}
		}
	}

	/**
	 * 遍历子数据
	 * @param childList
	 * @param sheet
	 * @param arr
	 */
	public  static void showChildNode(List<TreeNode> childList, XSSFSheet sheet, int [] arr){
		for (TreeNode node : childList) {
			CellRangeAddress cra;
			if(node.isLeaf()){
				cra = new CellRangeAddress(node.level, arr.length-1, arr[node.level], arr[node.level]+node.leafSize-1);
				if(node.level!=arr.length-1){
					for(int i=node.level+1;i<arr.length;i++){
						arr[i]+=1;
					}
				}
			}else{//不是叶子节点
				cra = new CellRangeAddress(node.level, node.level, arr[node.level], arr[node.level]+node.leafSize-1);
			}
			sheet.addMergedRegion(cra);
			XSSFRow row = sheet.getRow(node.level);
			XSSFCell cell = row.createCell(arr[node.level]);
			cell.setCellValue(node.getNodeName());
			XSSFCellStyle cellStyle = (XSSFCellStyle) sheet.getWorkbook().createCellStyle();
			cellStyle.setWrapText(true);
			cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
			cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
			cellStyle.setWrapText(true);//自动换行
			cell.setCellStyle(cellStyle);
			arr[node.level] += node.leafSize;
			if(node.getChildList() != null ){
				showChildNode(node.getChildList(),sheet,arr);
			}
		}
	}

	public static void mergeRow(List<String> keys, List<String> mergeKeys, List<Map<String, Object>> list, XSSFWorkbook workbook, int level){
		Sheet sheet = workbook.getSheetAt(0);
		if(mergeKeys==null||mergeKeys.isEmpty())
			return ;
		for(int j=0;j<keys.size();j++){
			String key = keys.get(j);
			if(!mergeKeys.contains(key)){
				continue;
			}
			mergeKeys.remove(key);
			int start = 0;
			int end = 0;
			int temp = 0;
			Map<String, Object> tempMap = list.get(temp);
			for(int i=0;i<list.size();i++){
				Map m = list.get(i);
				if(tempMap.get(key).equals(m.get(key))){
					end = i;
					if(i==list.size()-1 && end-start>0){
						CellRangeAddress ca = new CellRangeAddress(start+level+1, end+level+1, j, j);
						sheet.addMergedRegion(ca);
					}
				}else{
					if(end-start>0){
						CellRangeAddress ca = new CellRangeAddress(start+level+1, end+level+1, j, j);
						sheet.addMergedRegion(ca);
					}
					tempMap = list.get(i);
					start = i;
					end = i;
				}
			}
		}
	}

	public static void export(XSSFWorkbook workbook){
		String name = "f:/poi1_test"+Math.round(Math.random()*100)+".xlsx";
		File file = new File(name);
		try {
			file.createNewFile();
			// 将Excel内容存盘
			FileOutputStream stream = FileUtils.openOutputStream(file);
			workbook.write(stream);
			stream.close();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			System.out.println("完成");
			System.out.println(name);
		}
	}

	public static void export(XSSFWorkbook workbook, String name){
		String fileName = "f:/" + name + ".xlsx";
		File file = new File(fileName);
		try {
			file.createNewFile();
			// 将Excel内容存盘
			FileOutputStream stream = FileUtils.openOutputStream(file);
			workbook.write(stream);
			stream.close();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			System.out.println("完成");
			System.out.println(fileName);
		}
	}
}
