package com.sankuai.meituan.fund.biz.common.util;

import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URL;
import java.net.URLConnection;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.FastDateFormat;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.sankuai.meituan.fund.biz.common.util.annotations.ExcelExportAnnotation;

/**
 * Created by zhualiang on 16/10/20.
 */
public class ExcelUtil {
    public static final int XLS_FLAG = 0x01;
    public static final int XLSX_FLAG = 0x02;

    private static final int READER_FILE_FLAG = 0x01;
    private static final int WRITE_FILE_FLAG = 0x02;

    private static final String DEFAULT_SHEET_PREFIX = "sheet";

    private static final List<String> FILE_EXTENSIONS = Arrays.asList("xls", "xlsx");

    private static FastDateFormat format = FastDateFormat.getInstance("yyyy-MM-dd");
    private static DecimalFormat df1 = new DecimalFormat("##,##0.00");

    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
    private String string;

    private static boolean checkFile(String fileName, int readWriteflag) {
        String extension = FileUtil.getFileExtension(fileName);
        if (!FILE_EXTENSIONS.contains(extension.toLowerCase())) {
            logger.warn("ExcelUtil文件校验异常,文件类型不支持(仅支持文件后缀为{}格式),文件名:{}", FILE_EXTENSIONS, fileName);
            return false;
        }
        if ((readWriteflag & READER_FILE_FLAG) == READER_FILE_FLAG && !FileUtil.isFile(fileName)) {
            logger.warn("ExcelUtil 读取目标文件{}不存在", fileName);
            return false;
        }
        if ((readWriteflag & WRITE_FILE_FLAG) == WRITE_FILE_FLAG && !FileUtil.isFile(fileName)) {
            FileUtil.createFilPath(fileName, true);
        }
        return true;
    }

    private static Workbook createWriteWorkbook(int excelTypeflag) {
        return (excelTypeflag & XLSX_FLAG) == XLSX_FLAG ? new XSSFWorkbook() : new HSSFWorkbook();
    }

    private static Workbook createReadWorkBook(InputStream inputStream) {
        try {
            return WorkbookFactory.create(inputStream);
        } catch (InvalidFormatException e) {
            logger.warn("ExcelUtil解析输入流异常,异常信息:", e);
        } catch (IOException e) {
            logger.warn("ExcelUtil解析输入流异常,异常信息:", e);
        }
        return null;
    }

    private static String getCellContent(FormulaEvaluator evaluator, Cell cell) {
        CellType cellType = cell.getCellTypeEnum();
        if (cellType.equals(CellType.STRING)) {
            return cell.getStringCellValue();
        } else if (cellType.equals(CellType.NUMERIC)) {
            cell.setCellType(CellType.STRING);
            return cell.getStringCellValue();
        } else if (cellType.equals(CellType.BOOLEAN)) {
            return new Boolean(cell.getBooleanCellValue()).toString();
        } else if (cellType.equals(CellType.FORMULA)) {
            CellValue cellValue = evaluator.evaluate(cell);
            cellType = cellValue.getCellTypeEnum();
            if (cellType.equals(CellType.STRING)) {
                return cellValue.getStringValue();
            } else if (cellType.equals(CellType.NUMERIC)) {
                return new Double(cellValue.getNumberValue()).toString();
            } else if (cellType.equals(CellType.BOOLEAN)) {
                return new Boolean(cellValue.getBooleanValue()).toString();
            } else {
                return "";
            }
        } else {
            return "";
        }
    }


    public static List<List<String>> readDataList(String fileName) {
        if (!checkFile(fileName, READER_FILE_FLAG)) {
            return null;
        }
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(fileName);
            List<List<String>> result = readDataList(inputStream);
            return result;
        } catch (FileNotFoundException e) {
            logger.warn("读取excel文件{}不存在", fileName, e);
        } finally {
            try {
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                logger.warn("关闭读取excel文件{}异常", fileName, e);
            }
        }
        return null;
    }


    public static List<List<String>> readDataList(InputStream inputStream) {
        Workbook workbook = createReadWorkBook(inputStream);
        if (workbook == null) {
            return null;
        }
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        List<List<String>> result = new ArrayList<List<String>>();
        Sheet sheet = workbook.getSheetAt(0);
        for (Row row : sheet) {
            List<String> itemList = new ArrayList<String>();
            for (Cell cell : row) {
                itemList.add(getCellContent(evaluator, cell));
            }
            result.add(itemList);
        }
        return result;
    }

    public static <T> List<T> readObjectList(InputStream inputStream, Class<? extends T> cls, List<String> propList,List<String> error) {
        List<T> result = new ArrayList<T>();
        Workbook workbook = createReadWorkBook(inputStream);
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        if (workbook == null)
            return null;
        Sheet sheet = workbook.getSheetAt(0);
        //校验模板格式
        List<Field> fieldsList = new ArrayList<>();
        if(cls!=null){
            Arrays.stream(cls.getDeclaredFields()).forEach(fieldsList::add);
        }
        checkExcel(sheet,fieldsList,error);

        if(CollectionUtils.isNotEmpty(error)){
           return null;
        }
        //空行标识
        int column = 0;
        int rowNum=2;

        try {
            for (Row row : sheet) {
                if (row.getRowNum() == 0
                        || row.getRowNum() == 1) {
                    continue;
                }
                if(row.getRowNum()!=rowNum){
                    error.add("此EXCEL存在空行信息，请先删除空行后重新上传");
                    break;
                }

                boolean bool=true;

                T object = cls.newInstance();
                long start=System.currentTimeMillis();
                for (Cell cell : row) {
                    column = cell.getColumnIndex();

                    String columnValue=getCellContent(evaluator, cell);

                    if(StringUtils.isNotBlank(columnValue)){
                        bool=false;
                    }
                    //写入返回对象
                    if (StringUtils.isNotBlank(columnValue)) {
                        BeanUtils.setProperty(object, propList.get(column), columnValue);
                    }
                }
                if(bool){
                    error.add("此ECXCEL文件内存在清空数据的空行，建议执行EXCEL删除行操作或重新下载模版。");
                    break;
                }
                rowNum++;
                result.add(object);
            }
            //存在异常信息，清空写入的对象
            if(CollectionUtils.isNotEmpty(error)){
                result.clear();
            }

        } catch (InstantiationException e) {
            logger.warn("ExcelUtil调用readObjectList异常,实例化{}对象失败,异常信息:", cls.getName(), e);
        } catch (IllegalAccessException e) {
            logger.warn("ExcelUtil调用readObjectList异常,调用{}对象设置属性{}失败,异常信息:", cls.getName(), propList.get(column), e);
        } catch (InvocationTargetException e) {
            logger.warn("ExcelUtil调用readObjectList异常,调用{}对象设置属性{}失败,异常信息:", cls.getName(), propList.get(column), e);
        }
        return result;
    }

    public static boolean isRowEmpty(Row row){
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK){
                return false;
            }
        }
        return true;
    }


    public static void checkExcel(Sheet sheet, List<Field> fieldsList, List<String> errorList){
        //判断模板是否少列
        if(sheet.getRow(0).getPhysicalNumberOfCells()!=fieldsList.size()){
            errorList.add("上传文件的格式不正确，请确认后重新上传");
        }else{
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                if (row.getRowNum() == 0) {
                    Iterator<Cell> iterator = row.cellIterator();
                    while (iterator.hasNext()) {
                        Cell cell = iterator.next();
                        String stringCellValue = cell.getStringCellValue();
                        int cellColumnIndex = cell.getColumnIndex();
                        String headName = "";
                        for (Field fields : fieldsList) {
                            fields.setAccessible(true);
                            if (fields.isAnnotationPresent(ExcelExportAnnotation.class)) {
                                ExcelExportAnnotation excelAnnotation = fields.getAnnotation(ExcelExportAnnotation.class);
                                int annotationColumn = excelAnnotation.column();
                                if (annotationColumn == cellColumnIndex) {
                                    headName = excelAnnotation.excelHeadName();
                                    break;
                                } else {
                                    continue;
                                }
                            }
                        }
                        if (StringUtils.isBlank(headName) || !headName.equals(stringCellValue)) {
                            logger.info("#第" + (row.getRowNum() + 1) + "行第" + (cell.getColumnIndex() + 1) + "列头内容:" + cell.getStringCellValue() +
                                    "错误!应该是:" + headName + "或者导入Bean 没有column=" + cell.getColumnIndex() + "的配置！");
                            errorList.add("第" + (row.getRowNum() + 1) + "行第" + (cell.getColumnIndex() + 1) + "列头内容:" + cell.getStringCellValue()
                                    + "错误!应该是:" + headName);
                        }

                    }
                }
            }
        }
        //空模板判断
        if(CollectionUtils.isEmpty(errorList)&&sheet.getLastRowNum()<2){
            errorList.add("上传的模板为空模板，文件上传至少要有一条数据，请写入数据重新上传");
        }
        if(CollectionUtils.isEmpty(errorList)&&sheet.getLastRowNum()>20001){
            errorList.add("上传的模板条数不能超过20000条");
        }
    }


    public static <T> List<T> readObjectList(String file, Class<? extends T> cls, List<String> propertyNameList) {
        if (!checkFile(file, READER_FILE_FLAG)) {
            return null;
        }
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(file);
            List<T> result = readObjectList(inputStream, cls, propertyNameList,null);
            return result;
        } catch (FileNotFoundException e) {
            logger.warn("读取excel文件{}不存在", file, e);
        } finally {
            try {
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                logger.warn("关闭读取excel文件{}异常", file, e);
            }
        }
        return null;

    }

    public static boolean writeDataList(OutputStream outputStream, int excelTypeFlag, List<List<String>> dataList) {
        Workbook workbook = createWriteWorkbook(excelTypeFlag);
        Sheet sheet = workbook.createSheet(DEFAULT_SHEET_PREFIX + 1);
        for (int rowIndex = 0; rowIndex < dataList.size(); rowIndex++) {
            Row row = sheet.createRow(rowIndex);
            for (int columnIndex = 0; columnIndex < dataList.get(rowIndex).size(); columnIndex++) {
                Cell cell = row.createCell(columnIndex, CellType.STRING);
                cell.setCellValue(dataList.get(rowIndex).get(columnIndex));
            }
        }
        try {
            workbook.write(outputStream);
            return true;
        } catch (IOException e) {
            logger.warn("excelUtil写入数据异常", e);
        }
        return false;
    }

    public static boolean writeDataList(String file, List<List<String>> dataList) {
        if (!checkFile(file, WRITE_FILE_FLAG)) {
            return false;
        }
        OutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(file);
            boolean result;
            if (FileUtil.getFileExtension(file).toLowerCase().equals("xls")) {
                result = writeDataList(outputStream, XLS_FLAG, dataList);
            } else {
                result = writeDataList(outputStream, XLSX_FLAG, dataList);
            }

            return result;
        } catch (FileNotFoundException e) {
            logger.warn("写入excel文件{}不存在", file, e);
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                logger.warn("关闭写入excel文件{}流异常", file, e);
            }
        }
        return false;
    }

    public static <T> boolean writeObjectList(OutputStream outputStream, List<T> objectList, List<String> propertyNameList, boolean writeHeader) {
        T curObject = null;
        String curPropertyName = null;
        try {
            List<List<String>> dataList = new ArrayList<>();
            if (writeHeader) {
                dataList.add(propertyNameList);
            }
            for (T object : objectList) {
                curObject = object;
                List<String> propertyValueList = new ArrayList<>();
                for (String propertyName : propertyNameList) {
                    curPropertyName = propertyName;
                    propertyValueList.add(BeanUtils.getProperty(object, propertyName));
                }
                dataList.add(propertyValueList);
            }
            return writeDataList(outputStream, XLS_FLAG, dataList);
        } catch (IllegalAccessException e) {
            logger.warn("ExcelUtil调用writeObjectList 获取对象属性异常,对象:{},属性:{}异常信息:", curObject, curPropertyName, e);
        } catch (InvocationTargetException e) {
            logger.warn("ExcelUtil调用writeObjectList 获取对象属性异常,对象:{},属性:{}异常信息:", curObject, curPropertyName, e);
        } catch (NoSuchMethodException e) {
            logger.warn("ExcelUtil调用writeObjectList 获取对象属性异常,对象:{},属性:{}异常信息:", curObject, curPropertyName, e);
        }
        return false;
    }

    public static <T> boolean writeObjectList(String file, List<T> objectList, List<String> propertyNameList, boolean writeHeader) {
        if (!checkFile(file, WRITE_FILE_FLAG)) {
            return false;
        }
        OutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(file);
            boolean result = writeObjectList(outputStream, objectList, propertyNameList, writeHeader);
            return result;
        } catch (FileNotFoundException e) {
            logger.warn("写入excel文件{}不存在", file, e);
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                logger.warn("关闭写入excel文件{}流异常", file, e);
            }
        }
        return false;
    }
    private static String setFieldValueByIndex(Field field,String fieldValue,Object object) throws Exception{
        String fieldValueStr = String.valueOf(fieldValue).trim();
        if (field != null) {
            field.setAccessible(true);
            // 获取字段类型
            Class<?> fieldType = field.getType();

            // 根据字段类型给字段赋值
            try {
                if (String.class == fieldType) {
                    if (StringUtils.isNotBlank(fieldValueStr)) {
                        field.set(object, String.valueOf(fieldValue));
                    }
                } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
                    if(StringUtils.isNotBlank(fieldValueStr)){
                        field.set(object, Integer.parseInt(fieldValueStr));
                    }
                } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
                    if (StringUtils.isNotBlank(fieldValueStr)) {
                        field.set(object, Long.valueOf(fieldValueStr));
                    }
                } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
                    if (StringUtils.isNotBlank(fieldValueStr)) {
                        field.set(object, Float.valueOf(fieldValueStr));
                    }
                } else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
                    if (StringUtils.isNotBlank(fieldValueStr)) {
                        field.set(object, Short.valueOf(fieldValueStr));
                    }
                } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
                    if (StringUtils.isNotBlank(fieldValueStr)) {
                        field.set(object, Double.valueOf(fieldValueStr));
                    }
                } else if (Date.class == fieldType) {
                    field.set(object, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValueStr.toString()));
                } else if(BigDecimal.class == fieldType){
                    if (StringUtils.isNotBlank(fieldValueStr)) {
                        field.set(object, new BigDecimal(fieldValueStr.replaceAll(",", "")));
                    }
                } else {
                    field.set(object, fieldValueStr);
                }
            } catch (Exception e) {
                logger.info("#列值错误={}",e);
                return "列值错误,";
            }
        }
        return "";
    }
    public static List<List<Object>> read(String fileUrl) throws IOException {
        List<List<Object>> allRows = new ArrayList<List<Object>>();
        InputStream is = null;
        Workbook wb = null;
        try {
            URL url = new URL(fileUrl);
            URLConnection conn = url.openConnection();
            conn.setConnectTimeout(3000);
            conn.setReadTimeout(3 * 60 * 1000);
            is = conn.getInputStream();
            wb = WorkbookFactory.create(is);
            Sheet sheet = wb.getSheetAt(0);
            int maxRowNum = sheet.getLastRowNum();
            int minRowNum = sheet.getFirstRowNum();

            // 跳过头，从第二行开始读取
            for (int i = minRowNum + 1; i <= maxRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                List<Object> rowData = readLine(row);
                allRows.add(rowData);
            }

        } catch (Exception e) {
            throw new IOException(e);
        } finally {
            if (is != null) {
                is.close();
            }
            if (wb != null && wb instanceof SXSSFWorkbook) {
                SXSSFWorkbook xssfwb = (SXSSFWorkbook) wb;
                xssfwb.dispose();
            }
        }
        return allRows;
    }
    //读取每行数据
    private static List<Object> readLine(Row row){
        short minColNum = row.getFirstCellNum();
        short maxColNum = row.getLastCellNum();
        List<Object> dataList = new ArrayList<Object>();
        for (short colIndex = minColNum; colIndex < maxColNum; colIndex++) {
            Cell cell = row.getCell(colIndex);
            if (cell == null) {
                continue;
            }
            int cellType = cell.getCellType();
            Object value = null;
            if (Cell.CELL_TYPE_NUMERIC == cellType) {
                value = cell.getNumericCellValue();
            } else if (Cell.CELL_TYPE_STRING == cellType) {
                value = cell.getStringCellValue();
            } else {
                value = cell.getStringCellValue();
            }
            dataList.add(value);
        }
        return dataList;
    }

    /**
     * 将装有对象到list集合解析为我可以读取的list集合
     * @param clazz
     * @param list
     * @param name
     * @return
     * @throws Exception
     */
    public static List<Map> parseList(Class clazz, List<? extends Object> list, String name) throws Exception {

        List wlist = new ArrayList<Map<String, Object>>();
        if (list != null && list.size() > 0) {

            //设置页到名字
            Map sheetName = new HashMap<String, String>();
            sheetName.put("sheetName", name);
            wlist.add(sheetName);

            //反射得到该类对象类的所有属性，并封装成对象
            BeanInfo beanInfo = Introspector.getBeanInfo(clazz);
            PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
            for (int i = 0; i < list.size(); i++) {
                Map map = new HashMap<String, String>();
                for (PropertyDescriptor property : propertyDescriptors) {
                    String key = property.getName();
                    if (key.compareToIgnoreCase("class") == 0) {
                        continue;
                    }
                    String propertyType = property.getPropertyType().getName();
                    Method getter = property.getReadMethod();
                    Object value = getter != null ? getter.invoke(list.get(i)) : null;
                    map.put(key, value);
                }
                wlist.add(map);

            }
        }

        return wlist;
    }

    /**
     * 创建导出文档,当对象中属性为普通类型或者为对象时调用
     *
     * @param list
     * @param keys
     * @param columnNames
     * @return
     */
    public static void createWorkBook2down(List<Map<String, Object>> list,
                                           String[] keys, String columnNames[], HttpServletResponse response, String fileName) throws IOException {
        createWorkBook2down(list,keys,columnNames,response,fileName,1);
    }

    /**
     * 创建导出文档
     *
     * @param list
     * @param keys
     * @param columnNames
     * @return
     */
    private static void createWorkBook2down(List<Map<String, Object>> list,
                                            String[] keys, String columnNames[], HttpServletResponse response, String fileName, int flag) throws IOException {

        if (fileName != null && fileName.trim() == "") {
            fileName = "文件下载.xlsx";
        } else {
            fileName = fileName + ".xlsx";
        }
        Workbook workBook = null;
        if (flag == 1) {
            workBook = ExcelUtil.createWorkBook(list, keys, columnNames);
        } else if (flag == 2) {
            workBook = ExcelUtil.createWorkBook2(list, keys, columnNames);
        }
        logger.info("执行excel下载文件名{}开始写入",fileName);
        //提供下载
        response.setHeader("Content-Disposition", "attachment;filename="
                + URLEncoder.encode(fileName, "UTF-8"));//设置为下载，
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setCharacterEncoding("UTF-8");
        ServletOutputStream outputStream = response.getOutputStream();//得到响应的流
        workBook.write(outputStream);
        if (outputStream != null) {
        	    workBook.close();
            outputStream.flush();
//            outputStream.close();
        }
    }
    public static void downExcelFile(List<Map<String, Object>> list,
                                            String[] keys, String columnNames[], OutputStream output) throws IOException {
        Workbook workBook = ExcelUtil.createWorkBook(list, keys, columnNames);
        //提供下载
        workBook.write(output);
        if (output != null) {
            workBook.close();
        }
    }

    /**
     * 创建导出文档
     *
     * @param list
     * @param keys
     * @param columnNames
     * @return
     */
    public static Workbook createWorkBook(List<Map<String, Object>> list,
                                          String[] keys, String columnNames[]) {
        // 创建excel工作簿
        String sheetName = "";
        Workbook wb = new SXSSFWorkbook(100);

        if (null == list || list.isEmpty()) {
            sheetName = "sheet1";
        } else {
            Map<String, Object> map = list.get(0);
            if (null != map && null != map.get("sheetName")) {
                sheetName = map.get("sheetName").toString();
            }
        }

        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet(sheetName);
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for (int i = 0; i < keys.length; i++) {
            sheet.setColumnWidth( i, (short) (35.7 * 150));
        }

        // 创建第一行
        Row row = sheet.createRow(0);

        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();
        DataFormat dateformat = wb.createDataFormat();

        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());

        // 设置第一种单元格的样式（用于列名加粗）
        cs.setFont(f);
        cs.setBorderLeft(CellStyle.BORDER_THIN);
        cs.setBorderRight(CellStyle.BORDER_THIN);
        cs.setBorderTop(CellStyle.BORDER_THIN);
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setAlignment(CellStyle.ALIGN_CENTER);

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        cs2.setBorderLeft(CellStyle.BORDER_THIN);
        cs2.setBorderRight(CellStyle.BORDER_THIN);
        cs2.setBorderTop(CellStyle.BORDER_THIN);
        cs2.setBorderBottom(CellStyle.BORDER_THIN);
        cs2.setAlignment(CellStyle.ALIGN_CENTER);
        // 设置列名
        for (int i = 0; i < columnNames.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }

        for (Integer i = 1; i < list.size(); i++) {
            Row row1 = sheet.createRow(i);
            for (Integer j = 0; j < keys.length; j++) {
                Cell cell = row1.createCell(j);
                String colName = keys[j];
                String val = "";
                BigDecimal numbericVal=null;
                if(colName.contains(".")){
                    String[] strs = colName.split("\\.");
                    String alias = strs[0];
                    String subName = strs[1];
                    Object o = list.get(i).get(alias);
                    o = getValueByFieldName(subName,o) ;
                    if( o != null ){
                        //处理日期格式
                        if(o instanceof Date){
                            val = format.format(o);
                        }
                        else if(o instanceof BigDecimal){
                           numbericVal=(BigDecimal) o;
                           val=df1.format(o);
                        }
                        else{
                            val = o.toString();
                        }
                    }
                }else{
                    Object o = list.get(i).get(colName);
                    if( o != null ){
                        //处理日期格式
                        if(o instanceof Date){
                            val = format.format(o);
                        }
                        else if(o instanceof BigDecimal){
                            numbericVal=(BigDecimal) o;
                            val=df1.format(o);
                        }else{
                            val = o.toString();
                        }
                    }
                }
                cell.setCellValue(val);
                cell.setCellStyle(cs2);
                if(ColNameUtil.transferNumber(columnNames[j])) {
                    if(ColNameUtil.checkColTransString(columnNames[j])){
                        cell.setCellType(CellType.STRING);
                        cell.setCellValue(numbericVal==null?null:String.valueOf(numbericVal));
                    }else {
                        cell.setCellType(CellType.NUMERIC);
                        cell.setCellValue(numbericVal.doubleValue());
                    }
                }
            }
        }
        return wb;
    }

    /**
     * 创建导出文档
     *
     * @param list
     * @param keys
     * @param columnNames
     * @return
     */
    public static Workbook createWorkBook2(List<Map<String, Object>> list,
                                           String[] keys, String columnNames[]) {
        // 创建excel工作簿
        Workbook wb = new SXSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for (int i = 0; i < keys.length; i++) {
            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
        }

        // 创建第一行
        Row row = sheet.createRow((short) 0);

        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());

        // 设置第一种单元格的样式（用于列名加粗）
        cs.setFont(f);
        cs.setBorderLeft(CellStyle.BORDER_THIN);
        cs.setBorderRight(CellStyle.BORDER_THIN);
        cs.setBorderTop(CellStyle.BORDER_THIN);
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setAlignment(CellStyle.ALIGN_CENTER);

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        cs2.setBorderLeft(CellStyle.BORDER_THIN);
        cs2.setBorderRight(CellStyle.BORDER_THIN);
        cs2.setBorderTop(CellStyle.BORDER_THIN);
        cs2.setBorderBottom(CellStyle.BORDER_THIN);
        cs2.setAlignment(CellStyle.ALIGN_CENTER);
        // 设置列名
        for (int i = 0; i < columnNames.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }

        for (int i = 1, xz=1; xz < list.size(); i++,xz++) {
            Row row1 = sheet.createRow((int) i);
            int temp2=0;
            OK:for (int j = 0; j < keys.length; j++) {
                String colName = keys[j];
                String val = "";

                if(colName.contains(".")){
                    String[] strs = colName.split("\\.");
                    String alias = strs[0];
//                    String subName = strs[1];
                    List o = (List)list.get(xz).get(alias);
                    int tempk=0;
                    int tempx=0;
                    if(o!=null &&o.size()>0){
                        for(int temp=0;temp<o.size();temp++){
                            //表示开始进入list
                            tempx=temp;
                            Row row2 = temp==0?row1 :sheet.createRow(i+temp);

                            for(int k = j;k < keys.length;k++){
                                tempk=k;
                                String colName2 = keys[k];
                                if(colName2.contains(".")){
                                    String[] strs2 = colName2.split("\\.");
                                    String alias2 = strs2[0];
                                    String subName2 = strs2[1];
                                    if(!alias2.equals(alias)){
//                                    if(temp2 >0){
//                                        temp2= temp2>i+temp?temp2:i+temp;
//                                    }else{
//                                        temp2 =i+temp;
//                                    }
                                        break  ;
                                    }else{
                                        Cell cell2 = row2.createCell(k);
                                        val = getValueByFieldName(subName2,o.get(temp))+"";
                                        cell2.setCellValue(val);
                                        cell2.setCellStyle(cs2);
                                    }
                                }else{
//                                if(temp2 >0){
//                                    temp2= temp2>i+temp?temp2:i+temp;
//                                }else{
//                                    temp2 =i+temp;
//                                }
//
                                    break  ;
                                }
                            }
                        }
                        if(tempk==keys.length-1){
                            j=  tempk;
                        }else{
                            j=tempk-1;
                        }
                        if(temp2 >0){
                            temp2= temp2>i+tempx?temp2:i+tempx;
                        }else{
                            temp2 =i+tempx;
                        }
                        i=temp2;
                    }


                }else{
                    Cell cell = row1.createCell(j);
                    val = list.get(xz).get(keys[j]) == null ? " " : list
                            .get(xz).get(keys[j]).toString();
                    cell.setCellValue(val);
                    cell.setCellStyle(cs2);
                }

            }


        }
        return wb;
    }

    /**
     * 根据属性名获取该类此属性的值
     * @param fieldName
     * @param object
     * @return
     */
    private static Object getValueByFieldName(String fieldName,Object object){
        String firstLetter=fieldName.substring(0,1).toUpperCase();
        String getter = "get"+firstLetter+fieldName.substring(1);
        try {
            Method method = object.getClass().getMethod(getter, new Class[]{});
            Object value = method.invoke(object, new Object[] {});
            return value;
        } catch (Exception e) {
            return null;
        }

    }

}