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 com.google.common.collect.Lists;
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(12).warpText(true).align(Align.LEFT); // 设置水平对齐方式

            excel.cell(headerRowIndex, i).value(name).align(Align.CENTER) // 设置水平对齐方式
                    .bgColor(Color.GREY_25_PERCENT) // 设置背景色
                    .fontHeightInPoint(14).width(256 * 40)// 增加宽度
                    .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.THIN, Color.BLACK) // 设置外边框样式
                        .fontHeightInPoint(12).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 * 40)// 增加宽度
                    .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.THIN, Color.BLACK) // 设置外边框样式
                        .fontHeightInPoint(12).warpText(true).align(Align.LEFT); // 设置水平对齐方式
                col++;
            }
            rows++;
        }

        return excel.getWorkBook();
    }

    public static HSSFWorkbook exportTemplateExcel(String title,  Map<String, String> fieldMap, List data,int headerRowIndex,String templatePath)throws Exception {
        return exportExcel(title,fieldMap,data,headerRowIndex,templatePath);
    }

    public static HSSFWorkbook exportExcel(String title, Map<String, String> fieldMap, List data,int headerRowIndex,String templatePath)
            throws Exception {

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

        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(12).warpText(true).align(Align.LEFT); // 设置水平对齐方式

            excel.cell(headerRowIndex, i).value(name).align(Align.CENTER) // 设置水平对齐方式
                    .bgColor(Color.GREY_25_PERCENT) // 设置背景色
                    .fontHeightInPoint(14).width(256 * 40)// 增加宽度
                    .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.THIN, Color.BLACK) // 设置外边框样式
                        .fontHeightInPoint(12).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();
    }

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