package com.vci.starter.poi.util;

import com.vci.starter.poi.annotation.ExcelColumn;
import com.vci.starter.poi.annotation.ExcelTitle;
import com.vci.starter.poi.bo.*;
import com.vci.starter.web.annotation.VciFieldType;
import com.vci.starter.web.enumpck.BooleanEnum;
import com.vci.starter.web.enumpck.VciFieldTypeEnum;
import com.vci.starter.web.exception.VciBaseException;
import com.vci.starter.web.util.LangBaseUtil;
import com.vci.starter.web.util.LocalFileUtil;
import com.vci.starter.web.util.VciBaseUtil;
import com.vci.starter.web.util.VciDateUtil;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.nio.channels.FileChannel;
import java.util.*;
import java.util.stream.Collectors;

import static com.vci.starter.poi.constant.ExcelLangCodeConstant.*;

/**
 * excel操作类
 * 2020/5/8，引入4.1版本的poi，全部的接口发生变化
 * @author weidy
 * @date 2020/2/19
 */
public  class ExcelUtil {

	/**
	 * 日志
	 */
	private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

	/**
	 * 在读取文件的内容的时候，poi会自动判断文件是否为excel，所以不需要单独的方法，另外07还是03也不需要推断
	 * 提供写入到excel的方法，包括指明单元格和对象的两种方式
	 * 提供读取excel的方法，包括返回对象和map数据的两种方式
	 */


	/**
	 * 拷贝文件
	 *
	 * @param source 源文件
	 * @param target 目标文件
	 * @throws VciBaseException 拷贝文件出现错误或者文件不存在的时候会报错
	 */
	public static void copyFile(File source, File target) throws VciBaseException {
		VciBaseUtil.alertNotNull(source, "来源文件", target, "目标文件");
		if (!source.exists()) {
			throw new VciBaseException(FILE_NOT_FOUND, new String[]{source.getName()});
		}
		if (!target.exists()) {
			File folder = target.getParentFile();
			folder.mkdirs();
			try {
				target.createNewFile();
			} catch (IOException e) {
				throw new VciBaseException(FILE_IO_EXCEPTION, new String[]{target.getName()});
			}
		}
		FileInputStream is = null;
		FileOutputStream os = null;
		try {
			is = new FileInputStream(source);
		} catch (FileNotFoundException e) {
			throw new VciBaseException(FILE_NOT_FOUND, new String[]{source.getName()});
		}
		try {
			os = new FileOutputStream(target);
		} catch (FileNotFoundException e) {
			IOUtils.closeQuietly(is);
			throw new VciBaseException(FILE_NOT_FOUND, new String[]{target.getName()});
		}
		try {
			copyFile(is, os);
		} catch (VciBaseException e) {
			throw e;
		} catch (Throwable e) {
			throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{source.getName(), target.getName()}, e);
		} finally {
			//防止因为异常没有关闭
			IOUtils.closeQuietly(is);
			IOUtils.closeQuietly(os);
		}
	}

	/**
	 * 拷贝文件
	 *
	 * @param in 文件输入流
	 * @param os 文件输出流
	 * @throws VciBaseException 文件不存在或者写入错误的时候会抛出异常
	 */
	public static void copyFile(FileInputStream in, FileOutputStream os) throws VciBaseException {
		try {
			FileChannel sourceChannel = in.getChannel();
			FileChannel targetChannel = os.getChannel();
			int i = 0;
			int length = 2097152;
			while (true) {
				if (sourceChannel.position() == sourceChannel.size()) {
					sourceChannel.close();
					targetChannel.close();
					break;
				}
				if ((sourceChannel.size() - sourceChannel.position()) < 20971520) {
					length = (int) (sourceChannel.size() - sourceChannel.position());
				} else {
					length = 20971520;
				}
				sourceChannel.transferTo(sourceChannel.position(), length, targetChannel);
				sourceChannel.position(sourceChannel.position() + length);
				i++;
			}
		} catch (IOException e) {
			if (logger.isErrorEnabled()) {
				logger.error("拷贝源文件到目标文件出现了错误", e);
			}
			throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{}, e);
		} catch (Throwable e) {
			if (logger.isErrorEnabled()) {
				logger.error("拷贝源文件到目标文件出现了错误", e);
			}
			throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{}, e);
		} finally {
			IOUtils.closeQuietly(in);
			IOUtils.closeQuietly(os);
		}
	}

	/**
	 * 将jar中的文件拷贝到目标文件
	 *
	 * @param filePathInJar 在jar中的相对路径
	 * @param targetFile    目标文件
	 * @throws VciBaseException 拷贝出错的时候会抛出异常
	 */
	public static void copyFileFromJar(String filePathInJar, File targetFile) throws VciBaseException {
		VciBaseUtil.alertNotNull(filePathInJar, "来源文件", targetFile, "目标文件");
		if (!targetFile.exists()) {
			File folder = targetFile.getParentFile();
			folder.mkdirs();
			try {
				targetFile.createNewFile();
			} catch (IOException e) {
				throw new VciBaseException(FILE_IO_EXCEPTION, new String[]{targetFile.getName()});
			}
		}
		FileOutputStream os = null;
		InputStream ins = null;
		try {
			os = new FileOutputStream(targetFile);
		} catch (FileNotFoundException e) {
			throw new VciBaseException(FILE_NOT_FOUND, new String[]{targetFile.getName()});
		}
		try {
			ins = ExcelUtil.class.getResourceAsStream(filePathInJar);
			IOUtils.copy(ins, os);
		} catch (IOException e) {
			if (logger.isErrorEnabled()) {
				logger.error("拷贝源文件到目标文件出现了错误", e);
			}
			throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{}, e);
		} catch (Throwable e) {
			if (logger.isErrorEnabled()) {
				logger.error("拷贝源文件到目标文件出现了错误", e);
			}
			throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{}, e);
		} finally {
			IOUtils.closeQuietly(ins);
			IOUtils.closeQuietly(os);
		}
	}

	/**
	 * 写入数据到excel文件中
	 *
	 * @param fileName      文件的名称，不能是jar中的文件，要包括绝对路径
	 * @param writeExcelOption 要写入的数据和配置
	 * @throws VciBaseException 文件未找到,写入数据出现了错误
	 */
	public static void writeDataToFile(String fileName, WriteExcelOption writeExcelOption) throws VciBaseException {
		writeDataToFile(new File(fileName),  writeExcelOption);
	}
	/**
	 * 写入数据到excel文件中
	 *
	 * @param file          文件对象
	 * @param writeExcelOption 要写入的数据和配置
	 * @param writeExcelOption 要写入的数据和配置
	 * @throws VciBaseException 文件未找到,写入数据出现了错误
	 */
	public static void writeDataToFile(File file, WriteExcelOption writeExcelOption) throws VciBaseException {
		if (file == null) {
			throw new VciBaseException("文件为空，无法找到文件");
		}
		if (!file.exists()) {
			File folder = file.getParentFile();
			if (!folder.exists()) {
				folder.mkdirs();
			}
			try {
				file.createNewFile();
			} catch (IOException e) {
				throw new VciBaseException(FILE_NOT_FOUND, new String[]{file.getName()});
			}
		}
		Workbook workbook = null;
		if(writeExcelOption!=null && writeExcelOption.isAppend()){
			try {
				if (file.length() == 0) {
					workbook = createWorkbook(false);
				}else {
					workbook = getWorkbookByInputStream(new FileInputStream(file));
				}
			} catch (FileNotFoundException e) {
				throw new VciBaseException("fileNotExist",new String[]{file.getName()},e);
			}
		}
		OutputStream os = null;
		try {
			os = new FileOutputStream(file);
		} catch (FileNotFoundException e) {
			throw new VciBaseException(FILE_NOT_FOUND, new String[]{e.getMessage()}, e);
		}
		try {
			writeDataToFile(os, writeExcelOption,workbook);
		} catch (VciBaseException e) {
			if (logger.isErrorEnabled()) {
				logger.error("写入数据到excel出错", e);
			}
			throw e;
		} catch (Throwable e) {
			if (logger.isErrorEnabled()) {
				logger.error("写入数据到excel出错", e);
			}
			throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{}, e);
		} finally {
			//防止之前因为异常没有关闭
			IOUtils.closeQuietly(os);
		}
	}

	/**
	 * 写入数据到excel文件中
	 *
	 * @param os            文件输出流，必须是excel文件的输出流
	 * @param writeExcelOption 要写入的数据和配置
	 * @throws VciBaseException 文件未找到,写入数据出现了错误
	 */
	public static void writeDataToFile(OutputStream os, WriteExcelOption writeExcelOption) throws VciBaseException {
		writeDataToFile(os,writeExcelOption,null);
	}
	/**
	 * 写入数据到excel文件中
	 *
	 * @param os            文件输出流，必须是excel文件的输出流
	 * @param writeExcelOption 要写入的数据和配置
	 * @param workbook 工作簿对象，是追加数据的时候先获取workbook
	 * @throws VciBaseException 文件未找到,写入数据出现了错误
	 */
	public static void writeDataToFile(OutputStream os, WriteExcelOption writeExcelOption,Workbook workbook) throws VciBaseException {
		if (os == null) {
			throw new VciBaseException(OUTPUT_STREAM_IS_NULL);
		}
		if (writeExcelOption == null ) {
			IOUtils.closeQuietly(os);
		} else {
			//有数据才执行写入
			Workbook wb = null;
			if(writeExcelOption.isAppend() && workbook !=null){
				wb = workbook;
			}else{
				wb = createWorkbook(writeExcelOption.isRevision07());
			}
			Workbook finalWb = wb;
			writeExcelOption.getWriteDataMap().forEach((sheetName, excelDataList) ->{
				Sheet sheet = getSheetByWorkbook(finalWb, sheetName);
				try {
					writeDataToCell(sheet, excelDataList);
				} catch (VciBaseException e) {
					IOUtils.closeQuietly(os);
					throw e;
				} catch (Throwable e) {
					if (logger.isErrorEnabled()) {
						logger.error("写入数据到工作簿出错", e);
					}
					IOUtils.closeQuietly(os);
					throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{}, e);
				}
			});
			//是否有隐藏工作表
			if(!CollectionUtils.isEmpty(writeExcelOption.getHideSheetList())){
				writeExcelOption.getHideSheetList().stream().forEach(sheetName ->{
					Sheet sheet = getSheetByWorkbook(finalWb, sheetName);
					finalWb.setSheetHidden(finalWb.getSheetIndex(sheet),true);
				});
			}

			//到这里说明已经写到工作簿
			try {
				wb.write(os);
			} catch (IOException e) {
				if (logger.isErrorEnabled()) {
					logger.error("把工作簿上的数据写入到文件出错", e);
				}
				throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{}, e);
			} finally {
				IOUtils.closeQuietly(os);
				try {
					wb.close();
				} catch (Throwable e) {
					if (logger.isErrorEnabled()) {
						logger.error("关闭工作簿", e);
					}
				}
			}
		}
	}

	/**
	 * 获取excel某个属性所在的列索引
	 * @param excelFileName excel文件的名称
	 * @param poClass poi导入导出对象所属的类
	 * @param poFieldName 要查询的属性的名称
	 * @param sheetName 工作表的名称
	 * @return 列的索引，不存在的时候返回-1
	 */
	public static int getCellIndexForPoField(String excelFileName,String sheetName,Class<?> poClass,String poFieldName) throws VciBaseException{
		return getCellIndexForPoField(new File(excelFileName),sheetName,poClass,poFieldName);
	}

	/**
	 * 获取excel某个属性所在的列索引
	 * @param excelFile excel文件的名称
	 * @param poClass poi导入导出对象所属的类
	 * @param poFieldName 要查询的属性的名称
	 * @param sheetName 工作表的名称
	 * @return 列的索引，不存在的时候返回-1
	 */
	public static int getCellIndexForPoField(File excelFile,String sheetName,Class<?> poClass,String poFieldName) throws VciBaseException{
		VciBaseUtil.alertNotNull(excelFile,"excel的文件",poClass,"poi导入导出对象所属的类",poFieldName,"属性的名称");
		if(!excelFile.exists()){
			throw new VciBaseException("fileNotExist",new String[]{excelFile.getAbsolutePath()});
		}
		if(StringUtils.isBlank(sheetName)){
			sheetName = "Sheet1";
		}
		Field field = VciBaseUtil.getFieldForObject(poFieldName, poClass);
		if(field == null){
			throw new VciBaseException("对象中不存在此属性，{0}，{1}",new String[]{poFieldName,poClass.getName()});
		}
		if(field.isAnnotationPresent(ExcelColumn.class)){
			ExcelColumn ec = getExcelColumnAnnotation(field);
			String columnName = ec.value();
			Workbook workbook = null;
			try {
				workbook = getWorkbookByInputStream(new FileInputStream(excelFile));
			} catch (FileNotFoundException e) {
				throw new VciBaseException("fileNotExist",new String[]{excelFile.getAbsolutePath()});
			}
			if(workbook!=null){
				Sheet sheet = workbook.getSheet(sheetName);
				if(sheet!=null){
					Row row = sheet.getRow(getRowIndexForTitle(poClass));
					if(row!=null){
						short lastCellNum = row.getLastCellNum();
						for(short i = 0;i<lastCellNum; i ++){
							Cell cell = row.getCell(i);
							if(columnName.equals(cell.getStringCellValue())){
								try {
									workbook.close();
								} catch (IOException e) {
									logger.error("关闭工作表出现错误",e);
								}
								return  i;
							}
						}
					}
				}
			}
		}
		return -1;
	}

	/**
	 * 读取po对象里的各个属性的所在列的位置
	 * @param excelFile excel文件
	 * @param sheetName 列的名称
	 * @param poClass 导入导出的对象
	 * @return key是属性名称，value是列所在的位置
	 * @throws VciBaseException 文件不存在，读取出错的时候会抛出异常
	 */
	public static Map<String,Integer> listCellIndexForPO(File excelFile,String sheetName,Class<?> poClass) throws VciBaseException{
		VciBaseUtil.alertNotNull(excelFile,"excel的文件",poClass,"poi导入导出对象所属的类");
		if(!excelFile.exists()){
			throw new VciBaseException("fileNotExist",new String[]{excelFile.getAbsolutePath()});
		}
		if(StringUtils.isBlank(sheetName)){
			sheetName = "Sheet1";
		}
		List<Field> fields = VciBaseUtil.getAllFieldForObj(poClass);
		//列索引映射，key是属性的名称，value是列索引
		Map<String,Integer> cellIndexMap = new HashMap<>();
		if(!CollectionUtils.isEmpty(fields)){
			Workbook workbook = null;
			try {
				workbook = getWorkbookByInputStream(new FileInputStream(excelFile));
			} catch (FileNotFoundException e) {
				throw new VciBaseException("fileNotExist",new String[]{excelFile.getAbsolutePath()});
			}
			if(workbook!=null) {
				Sheet sheet = workbook.getSheet(sheetName);
				if (sheet != null) {
					Row row = sheet.getRow(getRowIndexForTitle(poClass));
					if (row != null) {
						Map<String,String> columnNamesMap = new HashMap<>();
						fields.stream().forEach(field -> {
							if(field.isAnnotationPresent(ExcelColumn.class)) {
								ExcelColumn ec = getExcelColumnAnnotation(field);
								String columnName = ec.value();
								columnNamesMap.put(columnName,field.getName());
							}
						});
						short lastCellNum = row.getLastCellNum();
						for(short i = 0;i<lastCellNum; i ++){
							Cell cell = row.getCell(i);
							String cellName = cell.getStringCellValue();
							if(columnNamesMap.containsKey(cellName)){
								cellIndexMap.put(columnNamesMap.get(cellName),Integer.valueOf(i));
							}
						}
					}
				}
				try {
					workbook.close();
				} catch (IOException e) {
					logger.error("关闭工作表出现错误",e);
				}
			}
		}
		return cellIndexMap;
	}

	/**
	 * 获取标题所在的行
	 * @param poClass poi导出导入对象
	 * @return 行索引
	 */
	public static int getRowIndexForTitle(Class<?> poClass){
		if(poClass.isAnnotationPresent(ExcelTitle.class)){
			ExcelTitle ec = poClass.getAnnotation(ExcelTitle.class);
			if (ec == null) {
				ec = poClass.getDeclaredAnnotation(ExcelTitle.class);
			}
			if(ec !=null){
				return ec.rowIndexForTitle();
			}
		}
		return 0;
	}

	/**
	 * 根据列序号获取列的名称
	 * @param colIndex 列序号
	 * @return 列的名称
	 */
	public static String getCellNameByIndex(int colIndex){
		//A~Z
		//AA~AZ
		//BA~BZ
		if(colIndex<26){
			return String.valueOf((char)('A' + colIndex));
		}else{
			//
			//大于等于26，取倍数和余数
			int balance = colIndex%26;
			int multiple = (colIndex-balance)/26;
			String prefix = String.valueOf((char)('A'  + (multiple-1)));
			String suffix = String.valueOf((char)('A'  + balance));
			return prefix+suffix;
		}
	}

	/**
	 * 创建工作簿，注意都是03版本的，为了兼容
	 *
	 * @return 工作簿对象
	 */
	public static Workbook createWorkbook(boolean is07) {
		Workbook wb = null;
		if(is07){
			wb = new XSSFWorkbook();
		}else{
			wb = new HSSFWorkbook();
		}
		//导出都是些2003版本的，用于兼容
		return wb;
	}


	/**
	 * 获取工作簿对象
	 *
	 * @param ins 文件输入流
	 * @return 工作簿，poi3.15以上版本不区分03还07
	 * @throws VciBaseException 如果不是excel文件，会直接报错
	 */
	public static Workbook getWorkbookByInputStream(InputStream ins) throws VciBaseException {
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(ins);
		} catch (IOException e) {
			IOUtils.closeQuietly(ins);
			throw new VciBaseException(WORKBOOK_INIT_FAIL, new String[]{LangBaseUtil.getErrorMsg(e)});
		}
		return wb;
	}

	/**
	 * 获取工作表，如果工作表名称不存在，则会创建一个
	 *
	 * @param wb        工作簿
	 * @param sheetName 工作表的名称
	 * @return 工作表对象
	 */
	private static Sheet getSheetByWorkbook(Workbook wb, String sheetName) {
		if (StringUtils.isBlank(sheetName)) {
			sheetName = "Sheet1";
		}
		Sheet sheet = wb.getSheet(sheetName);
		if (sheet == null) {
			sheet = wb.createSheet(sheetName);
		}
		return sheet;
	}

	/**
	 * 获取行的对象
	 *
	 * @param sheet    工作表对象
	 * @param rowIndex 行号，从0开始
	 * @return 行的对象，不存在会创建一行
	 */
	private static Row getRowBySheet(Sheet sheet, int rowIndex) {
		Row row = sheet.getRow(rowIndex);
		if (row == null) {
			//说明这个时候还没有创建这行--即没有值
			row = sheet.createRow(rowIndex);
		}
		return row;
	}

	/**
	 * 根据行获取单元格对象
	 *
	 * @param row       行对象
	 * @param cellIndex 列序号，从0开始
	 * @return 单元格的对象，不存在会创建
	 */
	private static Cell getCellByRow(Row row, int cellIndex) {
		Cell cell = row.getCell(cellIndex);
		if (cell == null) {
			//说明还没有创建这个单元格
			cell = row.createCell(cellIndex);
		}
		return cell;
	}

	/**
	 * 写入数据
	 *
	 * @param sheet         表格对象
	 * @param excelDataList 要写入的数据对象
	 */
	private static void writeDataToCell(Sheet sheet, List<WriteExcelData> excelDataList) throws VciBaseException {
		if (sheet != null && !CollectionUtils.isEmpty(excelDataList)) {
			List<WriteExcelData> mergedDataList = new ArrayList<>();
			excelDataList.stream().forEach(ed -> {
				Row row = getRowBySheet(sheet, ed.getRow());
				Cell cell = getCellByRow(row, ed.getCol());
				//合并单元格
				if (ed.isMerged() && (ed.getRowTo() > ed.getRow() || ed.getColTo() > ed.getCol())) {
					mergedDataList.add(ed);
				}
				copyStyle(sheet, cell, ed);
				if(ed.isReadOnly() && ed.getObj() !=null && !(ed.getObj() instanceof RichTextString) && CollectionUtils.isEmpty(ed.getValidationDataList())){
					//ed.setValidation(true);
					List<String> list = new ArrayList<>();
					list.add(ed.getObj().toString());
					ed.setValidationDataList(list );
					setValidation(sheet,ed);
				}
				centerCell(cell, ed);
				Object value = ed.getObj();
				if(StringUtils.isNotBlank(ed.getFontColor())){
					String rtsValue = value ==null?"":value.toString();
					RichTextString ts = (cell instanceof HSSFCell)?new HSSFRichTextString(rtsValue):new XSSFRichTextString(rtsValue);
					Font font = sheet.getWorkbook().createFont();
					font.setColor(VciBaseUtil.getShort(ed.getFontColor()));
					ts.applyFont(font);
					value = ts;
				}
				if(ed.isFormula()){
					//设置公式，如果即有值，还要设置公式则设置为两个对象
					setFormula(cell,ed);
				}else if(ed.isNameRefer()){
					//设置名称管理器，一般和有效性配合使用
					setNameRefer(sheet,ed);
				}else if(ed.isValidation()){
					//说明是直接写有效性的值
					setValidation(sheet,ed);
				}else{
					//写值
					setCellValue(cell, value);
				}
				if(ed.getWidth()!=null && ed.getWidth()>0){
					sheet.setColumnWidth(cell.getColumnIndex(),(int)(ed.getWidth() + 0.72)*256);
				}
			});
			mergedRegion(sheet, mergedDataList);
		}
	}

	/**
	 * 为单元格设置公式
	 * @param cell 单元格的对象
	 * @param ed 数据对象
	 */
	private static void setFormula(Cell cell ,WriteExcelData ed){
		if (ed.getObj() != null) {
			cell.setCellFormula(ed.getObj().toString());
		}
	}

	/**
	 * 设置名称管理器
	 * @param sheet 工作表
	 * @param ed 数据对象
	 */
	private static void setNameRefer(Sheet sheet,WriteExcelData ed){
		if(ed.getObj() !=null){
			Name name = sheet.getWorkbook().createName();
			name.setNameName(ed.getObj().toString());
			name.setRefersToFormula(sheet.getSheetName() + "!"+getRange(ed));
		}
	}

	/**
	 * 设置数据有效性
	 * @param sheet 工作表
	 * @param ed 数据对象
	 */
	private static void setValidation(Sheet sheet,WriteExcelData ed){
		if(ed.getRowTo()<ed.getRow()){
			ed.setRowTo(ed.getRow());
		}
		if(ed.getColTo()<ed.getCol()){
			ed.setColTo(ed.getCol());
		}
		CellRangeAddressList regions=new CellRangeAddressList(ed.getRow(),ed.getRowTo(),ed.getCol(),ed.getColTo());
		DataValidation validation = null;
		if (sheet instanceof XSSFSheet) {
			XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
			XSSFDataValidationConstraint dvConstraint = null;
			if(!CollectionUtils.isEmpty(ed.getValidationDataList())){
				//说明是直接写内容的
				dvConstraint = (XSSFDataValidationConstraint)dvHelper.createExplicitListConstraint(ed.getValidationDataList().toArray(new String[0]));
			}else{
				VciBaseUtil.alertNotNull(ed.getObj(),"写入有效性的时候，没有设置有效性的公式表达式");
				dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(ed.getObj().toString());
			}
			validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
		}else{
			HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper((HSSFSheet)sheet);
			DataValidationConstraint dvConstraint = null;
			if(!CollectionUtils.isEmpty(ed.getValidationDataList())){
				//说明是直接写内容的
				dvConstraint = dvHelper.createExplicitListConstraint(ed.getValidationDataList().toArray(new String[0]));
			}else{
				VciBaseUtil.alertNotNull(ed.getObj(),"写入有效性的时候，没有设置有效性的公式表达式");
				dvConstraint = dvHelper.createFormulaListConstraint(ed.getObj().toString());
			}
			validation = (HSSFDataValidation)(new HSSFDataValidation(regions,dvConstraint));
		}
		if(validation instanceof XSSFDataValidation) {
			validation.setSuppressDropDownArrow(true);
			validation.setShowErrorBox(true);
		}else {
			validation.setSuppressDropDownArrow(false);
		}
		if(StringUtils.isNotBlank(ed.getValidationErrorMsg())){
			validation.createErrorBox("error",ed.getValidationErrorMsg());
		}
		sheet.addValidationData(validation);
	}

	/**
	 * 获取范围的表达式
	 * @param excelData 数据对象
	 * @return 范围的表达式
	 */
	private static String getRange(WriteExcelData excelData){
		//列是从A开始的
		char start = (char)('A' + excelData.getCol());
		int rowId = excelData.getRow() + 1;
		int endRowId = excelData.getRowTo() +1;
		if (excelData.getColTo() <= 25) {
			char end = (char)('A' +excelData.getColTo());
			return "$" + start + "$" + rowId + ":$" + end + "$" + endRowId;
		} else {
			char endPrefix = 'A';
			char endSuffix = 'A';
			if ((excelData.getColTo() - 25) / 26 == 0 || excelData.getColTo() == 51) {// 26-51之间，包括边界（仅两次字母表计算）
				if ((excelData.getColTo() - 25) % 26 == 0) {// 边界值
					endSuffix = (char)('A' + 25);
				} else {
					endSuffix = (char)('A' + (excelData.getColTo() - 25) % 26 - 1);
				}
			} else {// 51以上
				if ((excelData.getColTo() - 25) % 26 == 0) {
					endSuffix = (char)('A' + 25);
					endPrefix = (char)(endPrefix + (excelData.getColTo() - 25) / 26 - 1);
				} else {
					endSuffix = (char)('A' + (excelData.getColTo() - 25) % 26 - 1);
					endPrefix = (char)(endPrefix + (excelData.getColTo() - 25) / 26);
				}
			}
			return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + endRowId;
		}
	}

	/**
	 * 往单元格里写入值
	 *
	 * @param cell  单元格对象
	 * @param value 要写入的值
	 */
	private static void setCellValue(Cell cell, Object value) {
		if (value != null) {
			if (value instanceof Date) {
				cell.setCellValue((Date) value);
			} else if (value instanceof String) {
				cell.setCellValue(value.toString());
			} else if (value instanceof Boolean) {
				cell.setCellValue((Boolean)value);
			} else if (value instanceof Integer) {
				cell.setCellValue((Integer) value);
			} else if (value instanceof Long) {
				cell.setCellValue((Long) value);
			} else if (value instanceof BigDecimal) {
				cell.setCellValue(((BigDecimal) value).doubleValue());
			} else if (value instanceof Double) {
				cell.setCellValue((Double) value);
			} else if (value instanceof RichTextString) {
				cell.setCellValue((RichTextString) value);
			} else {
				cell.setCellValue(value.toString());
			}
		}
	}

	/**
	 * 拷贝样式
	 *
	 * @param sheet     工作表
	 * @param cell      单元格
	 * @param excelData 要拷贝的信息
	 */
	private static void copyStyle(Sheet sheet, Cell cell, WriteExcelData excelData) {
		if (excelData.isCopyStyle()) {
			//拷贝样式
			Row copyStyleRow = sheet.getRow(excelData.getCopyStyleRow());
			if (copyStyleRow != null) {
				Cell copyStyleCell = copyStyleRow.getCell(excelData.getCopyStyleCol());
				copyStyleForCell(copyStyleCell,cell);
				if(StringUtils.isNotBlank(excelData.getDateFormat())){
					HSSFDataFormat format= (HSSFDataFormat) sheet.getWorkbook().createDataFormat();
					cell.getCellStyle().setDataFormat(format.getFormat(excelData.getDateFormat()));
				}
			}
		}
	}

	/**
	 * 拷贝单元格的样式
	 * @param fromCell 来源端单元格
	 * @param toCell 目标端单元格
	 */
	private static void copyStyleForCell(Cell fromCell,Cell toCell){
		// 样式
		CellStyle cellStyle = toCell.getCellStyle();
		CellStyle fromStyle = fromCell.getCellStyle();
		cellStyle.cloneStyleFrom(fromStyle);
		cellStyle.setAlignment(fromStyle.getAlignment());
		cellStyle.setBorderBottom(fromStyle.getBorderBottom());
		cellStyle.setBorderLeft(fromStyle.getBorderLeft());
		cellStyle.setBorderRight(fromStyle.getBorderRight());
		cellStyle.setBorderTop(fromStyle.getBorderTop());
		cellStyle.setDataFormat(fromStyle.getDataFormat());
		cellStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
		cellStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());
		cellStyle.setFillPattern(fromStyle.getFillPattern());
		cellStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
		cellStyle.setWrapText(fromStyle.getWrapText());
		cellStyle.setLocked(fromStyle.getLocked());
		cellStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
		cellStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());
		cellStyle.setTopBorderColor(fromStyle.getTopBorderColor());
		cellStyle.setRightBorderColor(fromStyle.getRightBorderColor());
	}

	/**
	 * 居中数据
	 *
	 * @param cell      单元格
	 * @param excelData 要居中的内容
	 */
	private static void centerCell(Cell cell, WriteExcelData excelData) {
		if (excelData.isCenter()) {
			CellStyle cellStyle = cell.getCellStyle();
			cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
			cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
		}
	}

	/**
	 * 合并单元格
	 *
	 * @param sheet          工作表
	 * @param mergedDataList 要合并的数据信息
	 */
	private static void mergedRegion(Sheet sheet, Collection<WriteExcelData> mergedDataList) {
		if (!CollectionUtils.isEmpty(mergedDataList)) {
			mergedDataList.stream().forEach(ed -> {
				if (ed.getRowTo() < ed.getRow()) {
					ed.setRowTo(ed.getRow());
				}
				if (ed.getColTo() < ed.getCol()) {
					ed.setColTo(ed.getCol());
				}
				sheet.addMergedRegion(new CellRangeAddress(ed.getRow(), ed.getRowTo(), ed.getCol(), ed.getColTo()));
			});
		}
	}

	/**
	 * 从excel上读取数据
	 *
	 * @param file 文件
	 * @return 数据列表
	 * @throws VciBaseException 执行出错的时候会抛出异常
	 */
	public static  List<SheetDataSet> readDataObjectFromExcel(File file) throws VciBaseException {
		return readDataObjectFromExcel(file, SheetDataSet.class);
	}

	/**
	 * 从excel上读取数据
	 *
	 * @param file        文件
	 * @param doClass 数据对象的类型
	 * @param <T>         数据对象的类型
	 * @return 数据列表
	 * @throws VciBaseException 执行出错的时候会抛出异常
	 */
	public static <T> List<T> readDataObjectFromExcel(File file, Class<T> doClass) throws VciBaseException {
		return readDataObjectFromExcel(file, doClass, null);
	}

	/**
	 * 从excel上读取数据
	 *
	 * @param file        文件
	 * @param excelOption 读取的类型，包括数据的首行，
	 * @param doClass     数据对象的类
	 * @param <T>         数据对象的类型
	 * @return 数据列表
	 * @throws VciBaseException 执行出错的时候会抛出异常
	 */
	public static <T> List<T> readDataObjectFromExcel(File file, Class<T> doClass, ReadExcelOption excelOption) throws VciBaseException {
		return readDataObjectFromExcel(file, doClass,excelOption, null);
	}

	/**
	 * 从excel上读取数据
	 *
	 * @param file        文件
	 * @param excelOption 读取的类型，包括数据的首行，
	 * @param doClass     数据对象的类
	 * @param <T>         数据对象的类型
	 * @param processer   转换器
	 * @return 数据列表
	 * @throws VciBaseException 执行出错的时候会抛出异常
	 */
	public static <T> List<T> readDataObjectFromExcel(File file, Class<T> doClass,ReadExcelOption excelOption,  PoiImportProcesser<T> processer) throws VciBaseException {
		InputStream ins = null;
		try {
			ins = new FileInputStream(file);
		} catch (Throwable e) {
			String msg = READ_IMPORT_FILE_FAIL;
			if (logger.isErrorEnabled()) {
				logger.error(msg, e);
			}
			throw new VciBaseException(msg + LangBaseUtil.getErrorMsg(e), new String[]{}, e);
		}
		try {
			return readDataObjectFromExcel(ins, doClass, excelOption,processer);
		} catch (VciBaseException e) {
			throw e;
		} catch (Throwable e) {
			throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{}, e);
		} finally {
			IOUtils.closeQuietly(ins);
		}
	}

	/**
	 * 从excel上读取数据
	 *
	 * @param ins         文件流
	 * @param excelOption 读取的类型，包括数据的首行（默认1），工作表（默认0）和是否全部工作表（默认否）
	 * @param doClass     数据的对象
	 * @param <T>         数据对象所属的类型
	 * @param processer   转换器
	 * @return 数据列表
	 * @throws VciBaseException 执行出错的时候会抛出异常
	 */
	public static <T> List<T> readDataObjectFromExcel(InputStream ins, Class<T> doClass,ReadExcelOption excelOption,  PoiImportProcesser<T> processer) throws VciBaseException {
		if (ins == null) {
			throw new VciBaseException(INPUT_STREAM_IS_NULL);
		}
		if (doClass == null) {
			doClass = (Class<T>) SheetDataSet.class;
		}
		boolean isDataSet = false;
		if (doClass.equals(SheetDataSet.class)) {
			isDataSet = true;
		}
		//获取对象上的列的名称
		Map<Field, ExcelColumn> excelColumnSet = getExcelColumnAnnotations(doClass);
		Map<String/**标题**/, ExcelColumn> excelTitleMap = Optional.of(excelColumnSet).get().values().stream().collect(Collectors.toMap(s -> s.value(), t -> t));
		Map<String/**标题**/,Field> excelTitleFieldMap = new HashMap<>();
		Map<String/**标题**/,ExcelColumnMap> extendAttrTitleMap = new HashMap<>();
		final Field[] rowIndexFields = {null};
		ReadExcelOption finalExcelOption = excelOption;
		excelColumnSet.forEach(((field, excelColumn) -> {
			excelTitleFieldMap.put(excelColumn.value(),field);
			if (excelColumn.rowIndexColumn()) {
				rowIndexFields[0] = field;
			}
			field.setAccessible(true);
			if((field.getType().equals(Map.class) || field.getType().getSuperclass().equals(Map.class))
					&& !CollectionUtils.isEmpty(finalExcelOption.getExtendAttrMap())
					&&  finalExcelOption.getExtendAttrMap().containsKey(excelColumn.value())){
				//说明是map类型,这种一般是扩展属性，将一堆属性转换为map里的值
				List<ExcelColumnMap> excelColumnMaps = finalExcelOption.getExtendAttrMap().get(excelColumn.value());
				if(!CollectionUtils.isEmpty(excelColumnMaps)){
					excelColumnMaps.stream().forEach(ss->{
						excelTitleFieldMap.put(ss.getTitle(),field);
						extendAttrTitleMap.put(ss.getTitle(),ss);
					});
				}
			}
		}));
		Field rowIndexField = null;
		if (rowIndexFields[0] != null) {
			rowIndexField = rowIndexFields[0];
		}

		Workbook workbook = getWorkbookByInputStream(ins);
		if (excelOption == null) {
			excelOption = new ReadExcelOption();
		}
		FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
		List<T> dataSet = new ArrayList<>();
		try {
			for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
				Sheet sheet = workbook.getSheetAt(sheetIndex);
				if(StringUtils.isNotBlank(excelOption.getSheetName())){
					if(!sheet.getSheetName().equalsIgnoreCase(excelOption.getSheetName())){
						continue;
					}
				}else {
					if (!excelOption.isReadAllSheet() && sheetIndex != excelOption.getSheetIndex()) {
						continue;
					}
				}
				SheetDataSet sheetDataSet = new SheetDataSet();
				sheetDataSet.setSheetName(sheet.getSheetName());
				if (isDataSet) {
					dataSet.add((T) sheetDataSet);
				}
				//处理标题
				Map<Integer, String> colsNameIndexMap = new HashMap<>();
				if (excelOption.getFristRow() > 0) {
					//等于0表示没有总标题
					try {
						Row titleRow = sheet.getRow(excelOption.getFristRow()-1);
						int colsCount = titleRow.getLastCellNum();
						List<String> titleList = new LinkedList<>();
						for (int i = 0; i < colsCount; i++) {
							String title = VciBaseUtil.getStringValueFromObject(getCellValue(titleRow.getCell(i),evaluator));
							if (StringUtils.isNotBlank(title)) {
								title= title.replace("*","").replace("☆","");
								colsNameIndexMap.put(i, title);
								titleList.add(title);
							}
						}
						if (isDataSet) {
							sheetDataSet.setColName(titleList);
						}
					} catch (Exception e) {
						if (logger.isErrorEnabled()) {
							logger.error("读取excel文件中的标题信息出现了错误", e);
						}
						throw e;
					}
				}

				//读取值
				List<SheetRowData> rowDataList = new LinkedList<SheetRowData>();
				if (sheet.getLastRowNum() > 0) {
					for (int rowIndex = excelOption.getFristRow(); rowIndex <= sheet.getLastRowNum(); rowIndex++) {
						Row rowDataSet = sheet.getRow(rowIndex);
						if (rowDataSet == null) {
							continue;
						}
						boolean notValue = false;
						for (int i = 0; i < rowDataSet.getLastCellNum(); i++) {
							Cell cellTemp = rowDataSet.getCell(i);
							Object valueTemp = getCellValue(cellTemp, evaluator);
							if((valueTemp != null && !(valueTemp instanceof String) )
									|| (valueTemp instanceof String && valueTemp !=null && StringUtils.isNotBlank(valueTemp.toString())) ){
								notValue = true;
								break;
							}
						}
						if(!notValue){
							//整行都是空，直接跳过
							continue;
						}
						//实例化数据对象
						SheetRowData rd = (isDataSet ? new SheetRowData() : null);
						T obj = null;
						if (!isDataSet) {
							try {
								obj = doClass.newInstance();
							} catch (Throwable e) {
								if (logger.isErrorEnabled()) {
									logger.error("实例化数据对象", e);
								}
								throw new VciBaseException("实例化数据对象," + LangBaseUtil.getErrorMsg(e));
							}
						}

						//处理行号
						if (isDataSet) {
							rd.setRowIndex(String.valueOf(rowIndex));
						}
						if (!isDataSet && rowIndexField != null) {
							VciBaseUtil.setValueForField(rowIndexField, obj, String.valueOf(rowIndex));
						}

						//每行的数据
						Map<Integer, String> rowDataMap = new HashMap<>();
						//遍历这行的每一个单元格的数据
						for (int columnIndex = 0; columnIndex < rowDataSet.getLastCellNum(); columnIndex++) {
							Cell cell = rowDataSet.getCell(columnIndex);
							Object value = getCellValue(cell, evaluator);
							//说明是对象
							String title = colsNameIndexMap.containsKey(columnIndex)?colsNameIndexMap.get(columnIndex):"";
							//属性是否为map形式
							boolean isMapField = false;
							Field thisField = null;
							if (StringUtils.isNotBlank(title) && excelTitleFieldMap.containsKey(title)) {
								thisField = excelTitleFieldMap.get(title);
							}
							if(thisField!=null){
								thisField.setAccessible(true);
								if(thisField.getType().equals(Map.class) || thisField.getType().getSuperclass().equals(Map.class)){
									isMapField = true;
								}
							}

							ExcelColumn excelColumn = null;
							if (StringUtils.isNotBlank(title) && excelTitleMap.containsKey(title)) {
								excelColumn = excelTitleMap.get(title);
							}
							ExcelColumnMap columnMap = null;
							if(isMapField){
								columnMap = extendAttrTitleMap.get(title);
							}
							boolean isNull = false;
							if(value == null || (value instanceof String && StringUtils.isBlank(value.toString()))){
								isNull = true;
							}
							if (!isNull) {
								if (!isDataSet) {
									if (thisField != null && (excelColumn != null || columnMap!=null)) {
										if(!isMapField) {
											if (value.getClass().equals(thisField.getType())) {
												try {
													thisField.set(obj, value);
												} catch (IllegalAccessException e) {
													if (logger.isErrorEnabled()) {
														logger.error("读取excel的单元格的值后，为对象赋值出现了错误", e);
													}
												}
											} else {
												VciBaseUtil.setValueForField(thisField, obj, VciBaseUtil.getStringValueFromObject(value));
											}
											if (thisField.getType().equals(Date.class) && thisField.get(obj) == null) {
												throw new VciBaseException(CELL_NOT_DATE_FORMATE, new String[]{title, (rowIndex + 1) + ""});
											}
											if (StringUtils.isNotBlank(excelColumn.regExg()) && !VciBaseUtil.getStringValueFromObject(value).matches(excelColumn.regExg())) {
												throw new VciBaseException(CELL_VALUE_NOT_EQUAL_REG, new String[]{title, (rowIndex + 1) + "", excelColumn.regExgTitle()});
											}
										}else{
											//说明是map
											Object fieldValue = thisField.get(obj);
											Map<String,String> data ;
											if(fieldValue==null){
												data = new HashMap<>();
											}else{
												data = (Map<String,String>)fieldValue;
											}
											data.put(columnMap.getColumnName(),value==null?"":value.toString());
											try {
												thisField.set(obj, data);
											} catch (IllegalAccessException e) {
												if (logger.isErrorEnabled()) {
													logger.error("设置map类型的数据时候出现了错误", e);
												}
											}
										}
									}
									if (processer != null && thisField != null) {
										processer.process(value, obj, thisField);
									}
								} else {
									//说明只是读取数据
									rowDataMap.put(columnIndex, VciBaseUtil.getStringValueFromObject(value));
								}
							}else{
								//需要判断一下，是不是整行都是空的
								if (!isDataSet && thisField != null && excelColumn != null ) {
									if(!excelColumn.nullable() ) {
										throw new VciBaseException(CELL_VALUE_CAN_NOT_NULL, new String[]{title, (rowIndex + 1) + ""});
									}
									if(StringUtils.isNotBlank(excelColumn.regExg())){
										throw new VciBaseException(CELL_VALUE_CAN_NOT_NULL_FOR_REG, new String[]{title, (rowIndex + 1) + ""});
									}
								}
							}
						}
						if (isDataSet) {
							rd.setData(rowDataMap);
							rowDataList.add(rd);
						} else {
							dataSet.add(obj);
						}
					}
				}
				if(isDataSet){
					sheetDataSet.setRowData(rowDataList);
				}
			}
		}catch (Throwable e){
			throw new VciBaseException(LangBaseUtil.getErrorMsg(e),new String[0],e);
		}finally {
			IOUtils.closeQuietly(ins);
			try {
				workbook.close();
			} catch (IOException e) {
				if(logger.isErrorEnabled()){
					logger.error("关闭工作簿出现了错误",e);
				}
			}
		}

		return  dataSet;
	}

	/**
	 * 从cell单元格上获取值
	 * @param cell 单元格对象
	 * @param evaluator 公式转换
	 * @return 单元格的值，没有转换为字符串
	 */
	private static Object getCellValue(Cell cell,FormulaEvaluator evaluator){
		if(cell !=null ){
			Object cellValue = null;
			CellType cellTypeEnum = cell.getCellType();
			switch (cellTypeEnum){
				case STRING:
					String thisCellValue = cell.getStringCellValue();
					cellValue = StringUtils.isEmpty(thisCellValue)?"":thisCellValue.trim();
					break;
				case BOOLEAN:
					boolean boolValue = cell.getBooleanCellValue();
					cellValue = boolValue;
					break;
				case FORMULA:
					cellValue = getCellValue(evaluator.evaluate(cell));
					break;
				case NUMERIC:
					if(DateUtil.isCellDateFormatted(cell)){
						cellValue = cell.getDateCellValue();
					}else{
						cellValue = cell.getNumericCellValue();
					}
					break;
				default:
					cellValue = "";
					break;
			}
			return cellValue;
		}else{
			return null;
		}
	}

	/**
	 * 针对公式的计算值
	 * @param cell 公式转换后的单元格对象
	 * @return 公式的实际值
	 */
	private static Object getCellValue(CellValue cell) {
		Object cellValue = null;
		switch (cell.getCellTypeEnum()) {
			case STRING:
				String thisCellValue = cell.getStringValue();
				cellValue = StringUtils.isEmpty(thisCellValue)?"":thisCellValue.trim();
				break;
			case NUMERIC:
				//公式计算不会有日期型
				cellValue=cell.getNumberValue();
				break;
			default:
				break;
		}

		return cellValue;
	}


	/**
	 * 获取电子表格相关的注解
	 *
	 * @param doClass 返回的类
	 * @return 不是SheetDataSet的时候需要返回所有包含了excelcolumn注解的属性
	 */
	public static <T> Map<Field, ExcelColumn> getExcelColumnAnnotations(Class<T> doClass) {
		Map<Field, ExcelColumn> excelColumnMap = new HashMap();
		if (!doClass.equals(SheetDataSet.class)) {
			List<Field> allFields = VciBaseUtil.getAllFieldForObj(doClass);
			List<Field> hasExcelColumnFields = allFields.stream().filter(field -> field.isAnnotationPresent(ExcelColumn.class)).collect(Collectors.toList());
			if (!CollectionUtils.isEmpty(hasExcelColumnFields)) {
				hasExcelColumnFields.stream().forEach(field -> {
					ExcelColumn ec = getExcelColumnAnnotation(field);
					excelColumnMap.put(field, ec);
				});
			}
		}
		return excelColumnMap;
	}

	/**
	 * 获取属性上的excelColumn的注解
	 * @param field 属性对象
	 * @return excelColumn
	 */
	public static ExcelColumn getExcelColumnAnnotation(Field field){
		ExcelColumn ec = field.getAnnotation(ExcelColumn.class);
		if (ec == null) {
			ec = field.getDeclaredAnnotation(ExcelColumn.class);
		}
		return ec;
	}

	/**
	 * 根据PO对象，导出模板
	 * @param doClass 导出的对象
	 * @param downloadTempOption 下载的选项
	 * @param processer 调用函数，获取枚举的值
	 * @param <T> 类型
	 * @return 文件的路径
	 */
	public static <T> String downloadImportTempByClass(Class<T> doClass,DownloadTempOption downloadTempOption,PoiTemplateProcesser<T> processer){
		if(downloadTempOption == null){
			downloadTempOption = new DownloadTempOption("");
		}
		//ok,写excel
		String excelName = LocalFileUtil.getDefaultTempFolder() + File.separator + (StringUtils.isNotBlank(downloadTempOption.getExcelName())?downloadTempOption.getExcelName():"导入模板.xls");
		try {
			new File(excelName).createNewFile();
		} catch (Throwable e) {
			throw new VciBaseException(LangBaseUtil.getErrorMsg(e), new String[]{excelName}, e);
		}
		List<WriteExcelData> excelDataList = new ArrayList<>();
		//先处理标题
		Map<Field, ExcelColumn> fieldExcelColumnMap = getExcelColumnAnnotations(doClass);
		Map<String,ExcelColumn> fieldNameExcelColumnMap = new HashMap<>();
		fieldExcelColumnMap.forEach((field,ec)->{
			field.setAccessible(true);
			fieldNameExcelColumnMap.put(field.getName(),ec);
		});
		//todo 后面处理标题的内容
		//获取顺序，如果是继承上级的，就放后面随意处理
		Field fields[] = doClass.getDeclaredFields();
		Set<String> finedFields = new HashSet<>();
		Workbook workbook = new HSSFWorkbook();
		int index = 0;
		for (int i = 0; i < fields.length; i++) {
			//TODO, 继承的还没处理
			Field field = fields[i];
			field.setAccessible(true);
			String fieldName = field.getName();
			if(fieldNameExcelColumnMap.containsKey(fieldName)){
				finedFields.add(fieldName);
				ExcelColumn column = fieldNameExcelColumnMap.get(fieldName);
				if(column.rowIndexColumn()){
					continue;
				}
				WriteExcelData excelData = new WriteExcelData(0,index,StringUtils.isNotBlank(column.value())?column.value():fieldName);
				if(!column.nullable()){
					excelData.setFontColor(String.valueOf(HSSFColor.HSSFColorPredefined.RED.getIndex()));
					excelData.setObj(excelData.getObj() +"*");
				}
				//标题都不让改
				excelData.setReadOnly(true);
				excelData.setCenter(true);
				if(column.width()>0){
					excelData.setWidth(column.width());
				}
				/**
				 * 下面的内容,时间格式，下拉菜单，是否开关
				 */
				List<WriteExcelData> thisRowNextDatas = new ArrayList<>();
				if(downloadTempOption.getDefaultRowSize() ==null || downloadTempOption.getDefaultRowSize()<0){
					downloadTempOption.setDefaultRowSize(1);
				}
				Integer defaultRowSize = downloadTempOption.getDefaultRowSize();
				for (int j = 0; j < defaultRowSize; j++) {
					thisRowNextDatas.add(new WriteExcelData(1+j,index,""));
				}
				//看看是否有枚举
				if(StringUtils.isNotBlank(column.enumId()) && processer!=null){
					Map<String, String> enumMap = processer.wrapperEnumMap(column.enumId(), doClass, field);
					if(!CollectionUtils.isEmpty(enumMap)){
						WriteExcelData thisRowNext = thisRowNextDatas.get(0);
						thisRowNext.setValidation(true);
						thisRowNext.setRowTo(defaultRowSize);
						thisRowNext.setColTo(thisRowNext.getCol());
						thisRowNext.setValidationDataList(enumMap.values().stream().collect(Collectors.toList()));
					}
				}
				if(field.isAnnotationPresent(VciFieldType.class)){
					VciFieldType fieldType = field.getDeclaredAnnotation(VciFieldType.class);
					if(fieldType ==null){
						fieldType = field.getAnnotation(VciFieldType.class);
					}
					if(fieldType!=null){
						String dateFormat = "";
						if(VciFieldTypeEnum.VTDateTime.equals(fieldType.value())){
							dateFormat = VciDateUtil.DateTimeFormat;
						}else if(VciFieldTypeEnum.VTDate.equals(fieldType.value())){
							dateFormat = VciDateUtil.DateFormat;
						}else if(VciFieldTypeEnum.VTTime.equals(fieldType.value())){
							dateFormat = VciDateUtil.TimeFormat;
						}
						if(StringUtils.isNotBlank(dateFormat)){
							String finalDateFormat = dateFormat;
							thisRowNextDatas.stream().forEach(thisRowNext->{
								thisRowNext.setDateFormat(finalDateFormat);
							});
						}
						//boolean类型
						if(VciFieldTypeEnum.VTBoolean.equals(fieldType.value())){
							List<String> trueFlase = new ArrayList<>();
							trueFlase.add("是");
							trueFlase.add("否");
							WriteExcelData thisRowNext = thisRowNextDatas.get(0);
							thisRowNext.setValidation(true);
							thisRowNext.setRowTo(defaultRowSize);
							thisRowNext.setColTo(thisRowNext.getCol());
							thisRowNext.setValidationDataList(trueFlase);
						}
						//枚举因为是固定的，所以可以逐个去处理，参照导出的时候，不能把数据导出
					}
				}
				excelDataList.addAll(thisRowNextDatas);
				excelDataList.add(excelData);
				index++;
			}
		}
		WriteExcelOption excelOption = new WriteExcelOption();
		excelOption.addSheetDataList(StringUtils.isNotBlank(downloadTempOption.getSheetName())?downloadTempOption.getSheetName():"Sheet1",excelDataList);
		writeDataToFile(excelName, excelOption);
		return excelName;
	}

	/**
	 * * 合并多个ExcelSheet
	 * https://blog.csdn.net/lingerlan510/article/details/121943788
	 * @param files 文件字符串(file.toString)集合,按顺序进行合并，合并的Excel中Sheet名称不可重复
	 * @param excelName 合并后Excel名称(包含后缀和路径)
	 * @Date: 2020/9/18 15:31
	 */
	public static void mergeExcel(List<String> files, String excelName) {
		Workbook newExcelCreat = excelName.toLowerCase(Locale.ROOT).endsWith(".xlsx")? new XSSFWorkbook():new HSSFWorkbook();
		// 遍历每个源excel文件，TmpList为源文件的名称集合
		for (int i = 0 ; i < files.size(); i ++) {
			String fromExcelName = files.get(i);
			try (InputStream in = new FileInputStream(fromExcelName)) {
				Workbook fromExcel = null;
				if(fromExcelName.toLowerCase(Locale.ROOT).endsWith(".xlsx")){
					fromExcel = new XSSFWorkbook(in);
				}else{
					fromExcel = new HSSFWorkbook(in);
				}
				int length = fromExcel.getNumberOfSheets();
				if (length <= 1) {       //长度为1时
					Sheet oldSheet = fromExcel.getSheetAt(0);
					Sheet existSheet = newExcelCreat.getSheet(oldSheet.getSheetName());
					String newSheetName = oldSheet.getSheetName();
					if(existSheet!=null){
						newSheetName += "_" +i;
					}
					Sheet newSheet = newExcelCreat.createSheet(newSheetName);
					copySheet(newExcelCreat, oldSheet, newSheet);
				} else {
					for (int j = 0; j < length; j++) {// 遍历每个sheet
						Sheet oldSheet = fromExcel.getSheetAt(j);
						Sheet existSheet = newExcelCreat.getSheet(oldSheet.getSheetName());
						String newSheetName = oldSheet.getSheetName();
						if(existSheet!=null){
							newSheetName += "_" +i + "_" + j;
						}
						Sheet newSheet = newExcelCreat.createSheet(newSheetName);
						copySheet(newExcelCreat, oldSheet, newSheet);
					}
				}

				//名称管理器
				List<? extends Name> allNames = fromExcel.getAllNames();
				if(!CollectionUtils.isEmpty(allNames)){
					allNames.stream().forEach(name->{
						Name name1 = newExcelCreat.createName();
						name1.setNameName(name.getNameName());
						name1.setRefersToFormula(name.getRefersToFormula());
					});
				}
			} catch (IOException e) {
				throw new VciBaseException("合并excel出错的了",new String[]{},e);
			}
		}
		// 定义新生成的xlxs表格文件
		String allFileName = excelName;
		try (FileOutputStream fileOut = new FileOutputStream(allFileName)) {
			newExcelCreat.write(fileOut);
			fileOut.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				newExcelCreat.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 合并单元格
	 *
	 * @param fromSheet 来源
	 * @param toSheet 目标
	 */
	private static void mergeSheetAllRegion(Sheet fromSheet, Sheet toSheet) {
		int num = fromSheet.getNumMergedRegions();
		CellRangeAddress cellR = null;
		for (int i = 0; i < num; i++) {
			cellR = fromSheet.getMergedRegion(i);
			toSheet.addMergedRegion(cellR);
		}
	}

	/**
	 * 复制单元格
	 *
	 * @param fromCell 来源
	 * @param toCell 目标
	 */
	private static void copyCell(Cell fromCell, Cell toCell) {

		copyStyleForCell(fromCell,toCell);
		if (fromCell.getCellComment() != null) {
			toCell.setCellComment(fromCell.getCellComment());
		}
		// 不同数据类型处理
		CellType fromCellType = fromCell.getCellType();
		toCell.setCellType(fromCellType);
		if (fromCellType == CellType.NUMERIC) {
			if (DateUtil.isCellDateFormatted(fromCell)) {
				toCell.setCellValue(fromCell.getDateCellValue());
			} else {
				toCell.setCellValue(fromCell.getNumericCellValue());
			}
		} else if (fromCellType == CellType.STRING) {
			RichTextString value = fromCell.getRichStringCellValue();
			if(value !=null && StringUtils.isNotBlank(value.toString())){
				Font fontAt = fromCell.getSheet().getWorkbook().getFontAt(fromCell.getCellStyle().getFontIndexAsInt());
				value.applyFont(fontAt);
			}
			toCell.setCellValue(value);
		} else if (fromCellType == CellType.BLANK) {
			// nothing21
		} else if (fromCellType == CellType.BOOLEAN) {
			toCell.setCellValue(fromCell.getBooleanCellValue());
		} else if (fromCellType == CellType.ERROR) {
			toCell.setCellErrorValue(fromCell.getErrorCellValue());
		} else if (fromCellType == CellType.FORMULA) {
			toCell.setCellFormula(fromCell.getCellFormula());
		} else {
			// nothing29
		}
	}

	/**
	 * 行复制功能
	 *
	 * @param wb 工作表
	 * @param oldRow 来源
	 * @param toRow 目标
	 */
	private static void copyRow(Workbook wb, Row oldRow, Row toRow) {
		toRow.setHeight(oldRow.getHeight());
		for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext(); ) {
			Cell tmpCell = (Cell)cellIt.next();
			Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
			copyCell(tmpCell, newCell);
		}
	}

	/**
	 * Sheet复制
	 *
	 * @param wb 工作表
	 * @param fromSheet 来源
	 * @param toSheet 目标
	 */
	private static void copySheet(Workbook wb, Sheet fromSheet, Sheet toSheet) {
		mergeSheetAllRegion(fromSheet, toSheet);
		// 设置列宽
		int length = fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum();
		for (int i = 0; i <= length; i++) {
			toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
		}
		for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext(); ) {
			Row oldRow = (Row) rowIt.next();
			Row newRow = toSheet.createRow(oldRow.getRowNum());
			copyRow(wb, oldRow, newRow);
		}
		//下拉框
		List<? extends DataValidation> dataValidations = fromSheet.getDataValidations();
		if(!CollectionUtils.isEmpty(dataValidations)){
			dataValidations.stream().forEach(dv->{
				toSheet.addValidationData(dv);
			});
		}
	}
	/**
	 * 关键属性
	 */
	public static final String KEY_ATTR_CHAR = "☆";

	/**
	 * 必输
	 */
	public static final String REQUIRED_CHAR = "*";

	/**
	 * 追加数据到excel中，
	 * @param data 数据，必须是po对象
	 * @param excelName 目标的文件名称
	 * @param excelOption 写入的相关配置
	 * @param <T> 对象类型
	 */
	public static <T> void appendDataToExcel(Collection<T> data,String excelName,AppendDataExcelOption excelOption){
		if(!CollectionUtils.isEmpty(data)){
			T t1 = data.stream().findFirst().get();
			//获取对象上的列的名称
			Map<Field, ExcelColumn> excelColumnSet = getExcelColumnAnnotations(t1.getClass());
			Map<String/**标题**/,String> excelTitleFieldNameMap =new HashMap<>();
			Set<String> readOnlyFields = new HashSet<>();
			excelColumnSet.forEach((field,excelColumn)->{
				excelTitleFieldNameMap.put(excelColumn.value(),field.getName());
				if(excelColumn.readOnly()){
					readOnlyFields.add(field.getName());
				}
			});

			//读取内容
			ReadExcelOption readExcelOption = new ReadExcelOption();
			readExcelOption.setSheetName(excelOption.getSheetName());
			readExcelOption.setFristRow(excelOption.getTitleRowIndex()+1);
			List<SheetDataSet> sheetDataSets = ExcelUtil.readDataObjectFromExcel(new File(excelName),SheetDataSet.class,readExcelOption);
			if(!CollectionUtils.isEmpty(sheetDataSets)) {
				SheetDataSet sheetDataSet = sheetDataSets.get(0);
				Map<String/**字段**/, Integer> fieldColumnMap = new HashMap<>();
				List<String> colName = sheetDataSet.getColName();
				if (!CollectionUtils.isEmpty(colName)) {
					for (int i = 0; i < colName.size(); i++) {
						String col= colName.get(i).replace(REQUIRED_CHAR,"").replace(KEY_ATTR_CHAR,"");
						if(excelTitleFieldNameMap.containsKey(col)){
							fieldColumnMap.put(excelTitleFieldNameMap.get(col),i);
						}
					}
					//我们封装数据
					List<WriteExcelData> writeExcelDataList = new ArrayList<>();
					final Integer[] index = {excelOption.getTitleRowIndex()+1};
					data.stream().forEach(d->{
						Map<String, String> dataMap = VciBaseUtil.objectToMapString(d);
						fieldColumnMap.forEach((field,col)->{
							if(CollectionUtils.isEmpty(excelOption.getWriteFields()) || excelOption.getWriteFields().contains(field)) {
								WriteExcelData excelData = new WriteExcelData(index[0], col, dataMap.getOrDefault(field, ""));
								excelData.setReadOnly((excelOption.isReadOnlyFromField() && readOnlyFields.contains(field))
								|| (!CollectionUtils.isEmpty(excelOption.getReadOnlyFields()) && excelOption.getReadOnlyFields().contains(field)));
								writeExcelDataList.add(excelData);
							}
						});
						index[0]++;
					});
					WriteExcelOption writeExcelOption = new WriteExcelOption();
					writeExcelOption.addSheetDataList(excelOption.getSheetName(),writeExcelDataList);
					writeExcelOption.setAppend(true);
					writeDataToFile(excelName,writeExcelOption);
				}
			}
		}
	}


}