package com.artfess.poi.util;


import com.artfess.base.annotation.ExcelColumn;
import com.artfess.base.util.BeanUtils;
import com.artfess.base.util.FileUtil;
import com.artfess.base.util.StringUtil;
import com.artfess.poi.Excel;
import com.artfess.poi.editor.IFontEditor;
import com.artfess.poi.reader.TableHeaderDef;
import com.artfess.poi.style.Align;
import com.artfess.poi.style.BorderStyle;
import com.artfess.poi.style.Color;
import com.artfess.poi.style.font.BoldWeight;
import com.artfess.poi.style.font.Font;
import org.apache.commons.lang.BooleanUtils;
import org.apache.commons.lang.CharUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.ExcelStyleDateFormatter;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.RichTextString;
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.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedWriter;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * Excel工具方法
 *
 * @company 广州宏天软件股份有限公司
 * @author heyifan
 * @email heyf@jee-soft.cn
 * @date 2018年7月5日
 */
public class ExcelUtil {
	private final static Logger log = LoggerFactory.getLogger(ExcelUtil.class);

	public final static String EXCEL2003 = "xls";
	public final static String EXCEL2007 = "xlsx";

	/**
	 * 获取工作表的行数
	 *
	 * @param sheet
	 *            HSSFSheet表对象
	 * @return 表行数
	 */
	public static int getLastRowNum(HSSFSheet sheet) {
		int lastRowNum = sheet.getLastRowNum();
		if (lastRowNum == 0) {
			lastRowNum = sheet.getPhysicalNumberOfRows() - 1;
		}
		return lastRowNum;
	}

	/**
	 * 获取该行第一个单元格的下标
	 *
	 * @param row
	 *            行对象
	 * @return 第一个单元格下标，从0开始
	 */
	public static int getFirstCellNum(HSSFRow row) {
		return row.getFirstCellNum();
	}

	/**
	 * 获取该行最后一个单元格的下标
	 *
	 * @param row
	 *            行对象
	 * @return 最后一个单元格下标，从0开始
	 */
	public static int getLastCellNum(HSSFRow row) {
		return row.getLastCellNum();
	}

	/**
	 * 获取POI的行对象
	 *
	 * @param sheet
	 *            表对象
	 * @param row
	 *            行号，从0开始
	 * @return
	 */
	public static HSSFRow getHSSFRow(HSSFSheet sheet, int row) {
		if (row < 0) {
			row = 0;
		}
		HSSFRow r = sheet.getRow(row);
		if (r == null) {
			r = sheet.createRow(row);
		}
		return r;
	}

	/**
	 * 获取单元格对象
	 *
	 * @param sheet
	 *            表对象
	 * @param row
	 *            行，从0开始
	 * @param col
	 *            列，从0开始
	 * @return row行col列的单元格对象
	 */
	public static HSSFCell getHSSFCell(HSSFSheet sheet, int row, int col) {
		HSSFRow r = getHSSFRow(sheet, row);
		return getHSSFCell(r, col);
	}

	/**
	 * 获取单元格对象
	 *
	 * @param row
	 *            行，从0开始
	 * @param col
	 *            列，从0开始
	 * @return 指定行对象上第col行的单元格
	 */
	public static HSSFCell getHSSFCell(HSSFRow row, int col) {
		if (col < 0) {
			col = 0;
		}
		HSSFCell c = row.getCell(col);
		c = c == null ? row.createCell(col) : c;
		return c;
	}

	/**
	 * 获取工作表对象
	 *
	 * @param workbook
	 *            工作簿对象
	 * @param index
	 *            表下标，从0开始
	 * @return
	 */
	public static HSSFSheet getHSSFSheet(HSSFWorkbook workbook, int index) {
		if (index < 0) {
			index = 0;
		}
		if (index > workbook.getNumberOfSheets() - 1) {
			workbook.createSheet();
			return workbook.getSheetAt(workbook.getNumberOfSheets() - 1);
		} else {
			return workbook.getSheetAt(index);
		}
	}

	/**
	 * 下载文件
	 *
	 * @param workBook
	 * @param fileName
	 * @param response
	 * @throws IOException
	 */

	public static void downloadExcel(HSSFWorkbook workBook, String fileName, HttpServletResponse response) throws IOException {

		String filedisplay = URLEncoder.encode(fileName+ ".xls", "utf-8");
		response.setContentType("APPLICATION/OCTET-STREAM");
		response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
		response.addHeader("Content-Disposition", "attachment;filename=" + filedisplay);
		response.addHeader("filename", filedisplay);
		OutputStream os = null;
		try {
			os = response.getOutputStream();
			workBook.write(os);
			os.flush();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (os != null)
				os.close();
		}
	}

	/**
	 * 导出excel文件。

	 *
	 * @param title
	 *            excel表格名称
	 * @param rowHeight
	 *            行高
	 * @param fieldMap
	 *            字段名映射 为一个LinkedHashMap
	 * @param data
	 *            行数据
	 *<pre>
	 * fieldMap的键要与data集合里map的键对应
	 * Map<String, String> fieldMap = new LinkedHashMap<String, String>();
	 *	fieldMap.put("A1", "标题1");
	 *	fieldMap.put("A2", "标题2");
	 *	fieldMap.put("A3", "标题3");
	 *	fieldMap.put("A4", "标题4");
	 *	fieldMap.put("A5", "标题5");
	 *	fieldMap.put("A6", "标题6");
	 *	List<Map<String, String>> data=new ArrayList<Map<String, String>>();
	 *	Map<String, String> m1=new HashMap<String, String>();
	 *	m1.put("A1", "LJ1");
	 *	m1.put("A2", "LJ2");
	 *	m1.put("A3", "LJ3");
	 *	m1.put("A4", "LJ4");
	 *	m1.put("A5", "LJ5");
	 *	m1.put("A6", "LJ6");
	 *	data.add(m1);
	 * </pre>
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("rawtypes")
	public static HSSFWorkbook exportExcel(String title, int rowHeight, Map<String, String> fieldMap, List data)throws Exception {
		return exportExcel(title,rowHeight,fieldMap,data,0);
	}

	@SuppressWarnings("rawtypes")
	public static HSSFWorkbook exportExcel(String title, int rowHeight, Map<String, String> fieldMap, List data,int headerRowIndex)
			throws Exception {

		int size = fieldMap.size();
		Excel excel = new Excel();

		int titleCols = size; // 列数

		if (titleCols == 0) {
			throw new Exception("请设置列！");
		}

		// 设置页名
		excel.sheet().sheetName(title);// 重命名当前处于工作状态的表的名称

		int i = 0;
		// 设置表头，第二行开始
		for (String name : fieldMap.values()) { // 表头已经排序过了

			excel.cell(headerRowIndex, i).value("").border(BorderStyle.MEDIUM, Color.BLACK) // 设置外边框样式
					.fontHeightInPoint(14).warpText(true).align(Align.LEFT); // 设置水平对齐方式

			excel.cell(headerRowIndex, i).value(name).align(Align.CENTER) // 设置水平对齐方式
					.bgColor(Color.GREY_25_PERCENT) // 设置背景色
					.fontHeightInPoint(14).width(256 * 50)// 增加宽度
					.border(BorderStyle.THIN, Color.BLACK) // 设置外边框样式
					.font(new IFontEditor() { // 设置字体
						@Override
						public void updateFont(Font font) {
							font.boldweight(BoldWeight.BOLD);// 粗体
							font.color(Color.BLACK);// 字体颜色
						}
					});
			i++;
		}

		// 插入数据，第三行开始
		int rows = headerRowIndex+1;
		for (Object obj : data) {
			Map rowObj = (Map) obj;
			int col = 0;
			for (String key : fieldMap.keySet()) {
				String val = rowObj.get(key) == null ? "" : rowObj.get(key).toString();
				excel.cell(rows, col).value(val).border(BorderStyle.MEDIUM, Color.BLACK) // 设置外边框样式
						.fontHeightInPoint(14).warpText(true).align(Align.LEFT); // 设置水平对齐方式
				col++;
			}
			rows++;
		}

		return excel.getWorkBook();
	}

	@SuppressWarnings("rawtypes")
	public static HSSFWorkbook exportExcel(String sheetName, int rowHeight, List<TableHeaderDef> headerDefList, List data) throws Exception {

		int size = headerDefList.size();
		Excel excel = new Excel();

		int titleCols = size; // 列数

		if (titleCols == 0) {
			throw new Exception("请设置列！");
		}

		// 设置页名
		excel.sheet().sheetName(sheetName);// 重命名当前处于工作状态的表的名称

		int i = 0;
		// 设置表头，第二行开始
		for (TableHeaderDef headerDef : headerDefList) { // 表头已经排序过了
			String name = headerDef.getName();
			String comment = headerDef.getComment();

			excel.cell(0, i).value(name).comment(comment).align(Align.CENTER) // 设置水平对齐方式
					.bgColor(Color.GREY_25_PERCENT) // 设置背景色
					.fontHeightInPoint(14).width(256 * 30)// 增加宽度
					.border(BorderStyle.THIN, Color.BLACK) // 设置外边框样式
					.font(new IFontEditor() { // 设置字体
						@Override
						public void updateFont(Font font) {
							font.boldweight(BoldWeight.BOLD);// 粗体
							font.color(Color.BLACK);// 字体颜色
						}
					});
			i++;
		}

		// 插入数据，第三行开始
		int rows = 1;
		for (Object obj : data) {
			Map rowObj = (Map) obj;
			int col = 0;
			for (TableHeaderDef headerDef : headerDefList) {
				String key = headerDef.getKey();
				String val = rowObj.get(key) == null ? "" : rowObj.get(key).toString();
				excel.cell(rows, col).value(val).border(BorderStyle.MEDIUM, Color.BLACK) // 设置外边框样式
						.fontHeightInPoint(14).warpText(true).align(Align.LEFT); // 设置水平对齐方式
				col++;
			}
			rows++;
		}

		return excel.getWorkBook();
	}

	/**
	 * 导入excel文件。
	 * @param firstFile 文件流对象
	 * @return
	 */
	public static List<Map<String, String>> ImportDate(MultipartFile firstFile){
		return ImportDate(firstFile,false);
	}

	/**
	 * 导入excel文件。
	 * @param firstFile 文件流对象
	 * @param commentAsKey 是否把表头的批注作为key
	 * @return
	 */
	public static List<Map<String, String>> ImportDate(MultipartFile firstFile,boolean commentAsKey) {
		Workbook wb =null;
		Sheet sheet = null;
		Row row = null;
		List<Map<String,String>> list = null;
		String cellData = null;
		List<String> columns=new ArrayList<String>();
		wb = readExcel(firstFile);
		if(wb != null){
			//用来存放表中数据
			list = new ArrayList<Map<String,String>>();
			//获取第一个sheet
			sheet = wb.getSheetAt(0);
			//获取最大行数
			int rownum = sheet.getPhysicalNumberOfRows();
			//获取第一行
			row = sheet.getRow(0);
			//获取最大列数
			int colnum = row.getPhysicalNumberOfCells();
			for (int i = 0; i<rownum; i++) {
				Map<String,String> map = new LinkedHashMap<String,String>();
				row = sheet.getRow(i);
				if(i==0){
					for (int j=0;j<colnum;j++){
						if(commentAsKey) {
							cellData = getCellComment(row.getCell(j));
						}else {
							cellData = (String) getCellFormatValue(row.getCell(j));
						}
						columns.add(cellData);
					}
				}else{
					if(row !=null){
						for (int j=0;j<colnum;j++){
							cellData = (String) getCellFormatValue(row.getCell(j));
							map.put(columns.get(j), cellData);
						}
					}else{
						break;
					}
					list.add(map);
				}
			}
		}
		return list;
	}

	/**
	 * 获取单元格的批注
	 * @param cell
	 * @return
	 */
	public static String getCellComment(Cell cell) {
		String comment = "";
		Comment cellComment = cell.getCellComment();
		if(cellComment!=null) {
			RichTextString richTextString = cellComment.getString();
			if(richTextString!=null) {
				comment = richTextString.getString();
			}
		}
		return comment;
	}

	//读取excel
	public static Workbook readExcel(MultipartFile multipartFile){
		String filePath=multipartFile.getOriginalFilename();
		Workbook wb = null;
		if(filePath==null){
			return null;
		}
		String extString = filePath.substring(filePath.lastIndexOf("."));
		InputStream is = null;
		try {
			is = multipartFile.getInputStream();
			if(".xls".equals(extString)){
				return wb = new HSSFWorkbook(is);
			}else if(".xlsx".equals(extString)){
				return wb = new XSSFWorkbook(is);
			}else{
				return wb = null;
			}

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return wb;
	}

	/**
	 * 获取单元格的值，数字类型返回字符串
	 * @param cell
	 * @return
	 */
	public static Object getCellFormatValue(Cell cell) {
		Object cellValue = null;
		if (cell != null) {
			if (cell.getCellType() == CellType.FORMULA) {
				if (DateUtil.isCellDateFormatted(cell)) {
					// 转换为日期格式yyyy-MM-dd
					ExcelStyleDateFormatter formatter = new ExcelStyleDateFormatter("yyyy-MM-dd");
					cellValue = formatter.format(cell.getDateCellValue());
				} else {
					return getCellValueOfCellType(cell, cell.getCachedFormulaResultType());
				}
			} else {
				return getCellValueOfCellType(cell, cell.getCellType());
			}
		} else {
			cellValue = "";
		}
		return cellValue;
	}

	private static Object getCellValueOfCellType(Cell cell, CellType cellType) {
		Object cellValue = "";
		// 判断cell类型
		switch (cellType) {
			case NUMERIC: {
				cellValue = StringUtil.format(cell.getNumericCellValue());
				break;
			}
			case BOOLEAN: {
				cellValue = cell.getBooleanCellValue();
				break;
			}
			case STRING: {
				cellValue = cell.getRichStringCellValue().getString();
				break;
			}
			default:
				cellValue = "";
		}
		return cellValue;
	}

	/**
	 * 下载文件
	 *
	 * @param workBook
	 * @param fileName
	 * @param path
	 * @throws IOException
	 */

	public static void saveExcel(HSSFWorkbook workBook, String fileName,String path) throws IOException {
		FileUtil.createFolder(path, true);
		String excelName = fileName + ".xls";
		String filePath = path + File.separator + excelName;
		FileOutputStream fout = new FileOutputStream(filePath);
		workBook.write(fout);
		fout.flush();
		fout.close();
	}

	/**
	 * 合并指定列的相同取值单元格
	 * @param sheet
	 * @param startRow
	 * @param mergeCol
	 */
	public static void mergeSameColumnCell(Sheet sheet, int startRow, int mergeCol) {
		String startValue = getCellContentAsString(getCell(sheet, startRow, mergeCol));
		int totalRows = sheet.getPhysicalNumberOfRows();
		int startMergeRow = startRow - 1, compareRow = startRow + 1;
		while(totalRows > compareRow) {
			Cell cell = getCell(sheet, compareRow, mergeCol);
			String rowCellValue = getCellContentAsString(cell);
			if (!startValue.equals(rowCellValue)) {
				if (compareRow - startMergeRow > 1) {
					if(getCellContentAsString(getCell(sheet, startMergeRow, mergeCol)).equals(getCellContentAsString(getCell(sheet, compareRow-1, mergeCol)))){
						addMergeCellReign(sheet, startMergeRow, mergeCol, compareRow - 1, mergeCol);
					}
				}
				startMergeRow = compareRow;
				startValue = rowCellValue;
			}
			compareRow ++;
		}
	}

	/**
	 * 合并合计单元格
	 * @param sheet
	 * @param startRow
	 * @param startCol
	 * @param totalName
	 */
	public static void mergeRowTotal(Sheet sheet, int startRow, int startCol, String totalName) {
		int totalRows = sheet.getPhysicalNumberOfRows();
		int totalCols = sheet.getRow(0).getPhysicalNumberOfCells();
		while(totalRows > startRow) {
			Cell cell = getCell(sheet, startRow, startCol);
			String rowCellValue = getCellContentAsString(cell);
			if (totalName.equals(rowCellValue)) {
				int currentCol = startCol + 1;
				while(totalCols > currentCol) {
					cell = getCell(sheet, startRow, currentCol);
					rowCellValue = getCellContentAsString(cell);
					if (!totalName.equals(rowCellValue)) {
						addMergeCellReign(sheet, startRow, startCol, startRow, currentCol -1);
						break;
					}
					currentCol ++;
				}
			}
			startRow ++;
		}
	}
	//设置单元格背色
	public static void setRowFillForegroundColor(Sheet sheet, int startRow, int startCol, String totalName,short indexedColors) {
		int totalRows = sheet.getPhysicalNumberOfRows();
		while(totalRows > startRow) {
			Cell cell = getCell(sheet, startRow, startCol);
			String rowCellValue = getCellContentAsString(cell);
			if (totalName.equals(rowCellValue)) {
				Row styleRow=sheet.getRow(startRow);
				for (Cell oldCell : styleRow) {
					CellStyle cellStyle=oldCell.getCellStyle();
					cellStyle.setFillForegroundColor(indexedColors);
					oldCell.setCellStyle(cellStyle);
				}
			}
			startRow =startRow+1;
		}
	}

	/**
	 * 添加单元格合并
	 *
	 * @param sheet
	 * @param startRow
	 * @param startCol
	 * @param endRow
	 * @param endCol
	 */
	public static void addMergeCellReign(Sheet sheet, int startRow, int startCol, int endRow, int endCol) {
		CellRangeAddress region = new CellRangeAddress(startRow, endRow, startCol, endCol);
		sheet.addMergedRegion(region);
		Cell cell = getCell(sheet, startRow, startCol);
		CellStyle cellStyle = cell.getCellStyle();
		cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直
	}

	/**
	 * 获取指定单元格
	 * @param sheet
	 * @param row
	 * @param col
	 * @return
	 */
	private static Cell getCell(Sheet sheet, int row, int col) {
		Row row2 = sheet.getRow(row);
		if(BeanUtils.isEmpty(row2)){
			return null;
		}
		Cell cell = row2.getCell(col);
		return cell;
	}

	/**
	 * 根据行内容重新计算行高
	 *
	 * @param sourceRow
	 */
	public static void calcAndSetRowHeigt(HSSFRow sourceRow) {
		for (int cellIndex = sourceRow.getFirstCellNum(); cellIndex <= sourceRow
				.getPhysicalNumberOfCells(); cellIndex++) {
			// 行高
			double maxHeight = sourceRow.getHeight();
			HSSFCell sourceCell = sourceRow.getCell(cellIndex);
			// 单元格的内容
			String cellContent = getCellContentAsString(sourceCell);
			if (null == cellContent || "".equals(cellContent)) {
				continue;
			}
			// 单元格的宽高及单元格信息
			Map<String, Object> cellInfoMap = getCellInfo(sourceCell);
			Integer cellWidth = (Integer) cellInfoMap.get("width");
			Integer cellHeight = (Integer) cellInfoMap.get("height");
			if (cellHeight > maxHeight) {
				maxHeight = cellHeight;
			}
			HSSFCellStyle cellStyle = sourceCell.getCellStyle();
			HSSFFont font = cellStyle.getFont(sourceRow.getSheet().getWorkbook());
			// 字体的高度
			short fontHeight = font.getFontHeight();

			// cell内容字符串总宽度
			double cellContentWidth = cellContent.getBytes().length * 2 * 256;

			// 字符串需要的行数 不做四舍五入之类的操作
			double stringNeedsRows = (double) cellContentWidth / cellWidth;
			// 小于一行补足一行
			if (stringNeedsRows < 1.0) {
				stringNeedsRows = 1.0;
			}

			// 需要的高度 (Math.floor(stringNeedsRows) - 1) * 40 为两行之间空白高度
			double stringNeedsHeight = (double) fontHeight * stringNeedsRows;
			// 需要重设行高
			if (stringNeedsHeight > maxHeight) {
				maxHeight = stringNeedsHeight;
				// 超过原行高三倍 则为5倍 实际应用中可做参数配置
				if (maxHeight / cellHeight > 5) {
					maxHeight = 5 * cellHeight;
				}
				// 最后取天花板防止高度不够
				maxHeight = Math.ceil(maxHeight);
				// 重新设置行高 同时处理多行合并单元格的情况
				Boolean isPartOfRowsRegion = (Boolean) cellInfoMap.get("isPartOfRowsRegion");
				if (isPartOfRowsRegion) {
					Integer firstRow = (Integer) cellInfoMap.get("firstRow");
					Integer lastRow = (Integer) cellInfoMap.get("lastRow");
					// 平均每行需要增加的行高
					double addHeight = (maxHeight - cellHeight) / (lastRow - firstRow + 1);
					for (int i = firstRow; i <= lastRow; i++) {
						double rowsRegionHeight = sourceRow.getSheet().getRow(i).getHeight() + addHeight;
						sourceRow.getSheet().getRow(i).setHeight((short) rowsRegionHeight);
					}
				} else {
					sourceRow.setHeight((short) maxHeight);
				}
			}
		}
	}

	/**
	 * 解析一个单元格得到数据
	 *
	 * @param cell
	 * @return
	 */
	private static String getCellContentAsString(Cell cell) {
		if (null == cell) {
			return "";
		}
		return getCellFormatValue(cell).toString();
	}

	/**
	 * 获取单元格及合并单元格的宽度
	 *
	 * @param cell
	 * @return
	 */
	private static Map<String, Object> getCellInfo(HSSFCell cell) {
		HSSFSheet sheet = cell.getSheet();
		int rowIndex = cell.getRowIndex();
		int columnIndex = cell.getColumnIndex();

		boolean isPartOfRegion = false;
		int firstColumn = 0;
		int lastColumn = 0;
		int firstRow = 0;
		int lastRow = 0;
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			firstColumn = ca.getFirstColumn();
			lastColumn = ca.getLastColumn();
			firstRow = ca.getFirstRow();
			lastRow = ca.getLastRow();
			if (rowIndex >= firstRow && rowIndex <= lastRow) {
				if (columnIndex >= firstColumn && columnIndex <= lastColumn) {
					isPartOfRegion = true;
					break;
				}
			}
		}
		Map<String, Object> map = new HashMap<String, Object>();
		Integer width = 0;
		Integer height = 0;
		boolean isPartOfRowsRegion = false;
		if (isPartOfRegion) {
			for (int i = firstColumn; i <= lastColumn; i++) {
				width += sheet.getColumnWidth(i);
			}
			for (int i = firstRow; i <= lastRow; i++) {
				height += sheet.getRow(i).getHeight();
			}
			if (lastRow > firstRow) {
				isPartOfRowsRegion = true;
			}
		} else {
			width = sheet.getColumnWidth(columnIndex);
			height += cell.getRow().getHeight();
		}
		map.put("isPartOfRowsRegion", isPartOfRowsRegion);
		map.put("firstRow", firstRow);
		map.put("lastRow", lastRow);
		map.put("width", width);
		map.put("height", height);
		return map;
	}

	public static <T> List<T> readExcel( Class<T> cls,MultipartFile file){

		String fileName = file.getOriginalFilename();
		if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
			log.error("上传文件格式不正确");
		}
		List<T> dataList = new ArrayList<>();
		Workbook workbook = null;
		try {
			workbook = WorkbookFactory.create(file.getInputStream());
			if (workbook != null) {
				//类映射  注解 value-->bean columns
				Map<String, List<Field>> classMap = new HashMap<>();
				List<Field> fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());
				fields.forEach(
						field -> {
							ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
							if (annotation != null) {
								String value = annotation.col()+"";
								annotation.col();
								if (StringUtils.isBlank(value)) {
									return;//return起到的作用和continue是相同的 语法
								}
								if (!classMap.containsKey(value)) {
									classMap.put(value, new ArrayList<>());
								}
								field.setAccessible(true);
								classMap.get(value).add(field);
							}
						}
				);
				//索引-->columns
				Map<Integer, List<Field>> reflectionMap = new HashMap<>(16);
				//默认读取第一个sheet
				Sheet sheet = workbook.getSheetAt(0);
				boolean firstRow = true;
				for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
					Row row = sheet.getRow(i);
					//首行  提取注解
					if (firstRow) {
						for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
							if (classMap.containsKey(j+"")) {
								reflectionMap.put(j, classMap.get(j+""));
							}
						}
						firstRow = false;
					} else {
						//忽略空白行
						if (row == null) {
							continue;
						}
						try {
							T t = cls.newInstance();
							//判断是否为空白行
							boolean allBlank = true;
							for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
								if (reflectionMap.containsKey(j)) {
									Cell cell = row.getCell(j);
									String cellValue = getCellContentAsString(cell);
									if (StringUtils.isNotBlank(cellValue)) {
										allBlank = false;
									}
									List<Field> fieldList = reflectionMap.get(j);
									fieldList.forEach(
											x -> {
												try {
													handleField(t, cellValue, x);
												} catch (Exception e) {
													log.error(String.format("reflect field:%s value:%s exception!", x.getName(), cellValue), e);
												}
											}
									);
								}
							}
							if (!allBlank) {
								dataList.add(t);
							} else {
								log.warn(String.format("row:%s is blank ignore!", i));
							}
						} catch (Exception e) {
							log.error(String.format("parse row:%s exception!", i), e);
						}
					}
				}
			}
		} catch (Exception e) {
			log.error(String.format("parse excel exception!"), e);
		}
		return dataList;
	}
	private static <T> void handleField(T t, String value, Field field) throws Exception {
		Class<?> type = field.getType();
		if (type == null || type == void.class || StringUtils.isBlank(value)) {
			return;
		}
		if (type == Object.class) {
			field.set(t, value);
			//数字类型
		} else if (type.getSuperclass() == null || type.getSuperclass() == Number.class) {
			if (type == int.class || type == Integer.class) {
				field.set(t, NumberUtils.toInt(value));
			} else if (type == long.class || type == Long.class) {
				field.set(t, NumberUtils.toLong(value));
			} else if (type == byte.class || type == Byte.class) {
				field.set(t, NumberUtils.toByte(value));
			} else if (type == short.class || type == Short.class) {
				field.set(t, NumberUtils.toShort(value));
			} else if (type == double.class || type == Double.class) {
				field.set(t, NumberUtils.toDouble(value));
			} else if (type == float.class || type == Float.class) {
				field.set(t, NumberUtils.toFloat(value));
			} else if (type == char.class || type == Character.class) {
				field.set(t, CharUtils.toChar(value));
			} else if (type == boolean.class) {
				field.set(t, BooleanUtils.toBoolean(value));
			} else if (type == BigDecimal.class) {
				field.set(t, new BigDecimal(value));
			}
		} else if (type == Boolean.class) {
			field.set(t, BooleanUtils.toBoolean(value));
		} else if (type == Date.class) {
			//
			field.set(t, value);
		} else if (type == String.class) {
			field.set(t, value);
		} else {
			Constructor<?> constructor = type.getConstructor(String.class);
			field.set(t, constructor.newInstance(value));
		}
	}

	/**
	 * 设置Excel的所有列为自动调整列宽
	 * @param workbook
	 */
	public static void setAutoResizeColumn(Workbook workbook) {
		int sheets = workbook.getNumberOfSheets();
		int index = 0;
		while (index < sheets) {
			Sheet sheet = workbook.getSheetAt(index);
			int cols = sheet.getRow(0).getPhysicalNumberOfCells();
			for (int i = 0 ; i  < cols ; i ++) {
				sheet.autoSizeColumn(i);
			}
			index++;
		}
	}

	/**
	 * 将数据以csv格式导出
	 * @param exportData
	 * @return
	 */
	public static void exportCSV(String fileName, LinkedHashMap<String, String> headerMap, List<Map<String, Object>> exportData, HttpServletResponse response) throws IOException {
		String filedisplay = URLEncoder.encode(fileName + ".csv", "utf-8");
		response.setContentType("APPLICATION/OCTET-STREAM");
		response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
		response.addHeader("Content-Disposition", "attachment;filename=" + filedisplay);
		response.addHeader("filename", filedisplay);
		try (OutputStream out = response.getOutputStream();
			 ByteArrayOutputStream byteStream = new ByteArrayOutputStream();
			 BufferedWriter buffCvsWriter = new BufferedWriter(new OutputStreamWriter(byteStream, StandardCharsets.UTF_8));){
			fillDataToCsv(buffCvsWriter, headerMap, headerMap);
			// 将body数据写入表格
			for (Iterator<Map<String, Object>> iterator = exportData.iterator(); iterator.hasNext(); ) {
				fillDataToCsv(buffCvsWriter, headerMap, iterator.next());
			}
			// 刷新缓冲
			buffCvsWriter.flush();
			out.write(byteStream.toByteArray());
			out.flush();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	@SuppressWarnings("rawtypes")
	private static void fillDataToCsv(BufferedWriter buffCvsWriter, LinkedHashMap<String, String> header, Map row) throws IOException {
		for (Iterator<String> keyIterator = header.keySet().iterator(); keyIterator.hasNext(); ) {
			String key = keyIterator.next();
			String propertyValue = "";
			if (BeanUtils.isNotEmpty(row.get(key))) {
				propertyValue = row.get(key).toString();
			}
			buffCvsWriter.write("\t" + propertyValue + " ");
			if (keyIterator.hasNext()) {
				buffCvsWriter.write(",");
			}
		}
		buffCvsWriter.newLine();
	}
}