若依 3.0

RuoYi

2020-07-19 cee572f237cb293e4a9070ef458356bc6daa2105

 ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java

ÎļþÃû´Ó ruoyi/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java ÐÞ¸Ä
@@ -1,875 +1,875 @@
package com.ruoyi.common.utils.poi;

import java.io.File;
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.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.stream.Collectors;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.BorderStyle;
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.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
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.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.ruoyi.common.core.text.Convert;
import com.ruoyi.common.exception.CustomException;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.reflect.ReflectUtils;
import com.ruoyi.framework.aspectj.lang.annotation.Excel;
import com.ruoyi.framework.aspectj.lang.annotation.Excel.ColumnType;
import com.ruoyi.framework.aspectj.lang.annotation.Excel.Type;
import com.ruoyi.framework.aspectj.lang.annotation.Excels;
import com.ruoyi.framework.config.RuoYiConfig;
import com.ruoyi.framework.web.domain.AjaxResult;

/**
 * Excel相关处理
 * 
 * @author ruoyi
 */
public class ExcelUtil<T>
{
    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * Excel sheet最大行数，默认65536
     */
    public static final int sheetSize = 65536;

    /**
     * å·¥ä½œè¡¨åç§°
     */
    private String sheetName;

    /**
     * å¯¼å‡ºç±»åž‹ï¼ˆEXPORT:导出数据；IMPORT：导入模板）
     */
    private Type type;

    /**
     * å·¥ä½œè–„对象
     */
    private Workbook wb;

    /**
     * å·¥ä½œè¡¨å¯¹è±¡
     */
    private Sheet sheet;

    /**
     * æ ·å¼åˆ—表
     */
    private Map<String, CellStyle> styles;

    /**
     * å¯¼å…¥å¯¼å‡ºæ•°æ®åˆ—表
     */
    private List<T> list;

    /**
     * æ³¨è§£åˆ—表
     */
    private List<Object[]> fields;

    /**
     * å®žä½“对象
     */
    public Class<T> clazz;

    public ExcelUtil(Class<T> clazz)
    {
        this.clazz = clazz;
    }

    public void init(List<T> list, String sheetName, Type type)
    {
        if (list == null)
        {
            list = new ArrayList<T>();
        }
        this.list = list;
        this.sheetName = sheetName;
        this.type = type;
        createExcelField();
        createWorkbook();
    }

    /**
     * å¯¹excel表单默认第一个索引名转换成list
     * 
     * @param is è¾“入流
     * @return è½¬æ¢åŽé›†åˆ
     */
    public List<T> importExcel(InputStream is) throws Exception
    {
        return importExcel(StringUtils.EMPTY, is);
    }

    /**
     * å¯¹excel表单指定表格索引名转换成list
     * 
     * @param sheetName è¡¨æ ¼ç´¢å¼•å
     * @param is è¾“入流
     * @return è½¬æ¢åŽé›†åˆ
     */
    public List<T> importExcel(String sheetName, InputStream is) throws Exception
    {
        this.type = Type.IMPORT;
        this.wb = WorkbookFactory.create(is);
        List<T> list = new ArrayList<T>();
        Sheet sheet = null;
        if (StringUtils.isNotEmpty(sheetName))
        {
            // å¦‚果指定sheet名,则取指定sheet中的内容.
            sheet = wb.getSheet(sheetName);
        }
        else
        {
            // å¦‚果传入的sheet名不存在则默认指向第1个sheet.
            sheet = wb.getSheetAt(0);
        }

        if (sheet == null)
        {
            throw new IOException("文件sheet不存在");
        }

        int rows = sheet.getPhysicalNumberOfRows();

        if (rows > 0)
        {
            // å®šä¹‰ä¸€ä¸ªmap用于存放excel列的序号和field.
            Map<String, Integer> cellMap = new HashMap<String, Integer>();
            // èŽ·å–表头
            Row heard = sheet.getRow(0);
            for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++)
            {
                Cell cell = heard.getCell(i);
                if (StringUtils.isNotNull(cell))
                {
                    String value = this.getCellValue(heard, i).toString();
                    cellMap.put(value, i);
                }
                else
                {
                    cellMap.put(null, i);
                }
            }
            // æœ‰æ•°æ®æ—¶æ‰å¤„理 å¾—到类的所有field.
            Field[] allFields = clazz.getDeclaredFields();
            // å®šä¹‰ä¸€ä¸ªmap用于存放列的序号和field.
            Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();
            for (int col = 0; col < allFields.length; col++)
            {
                Field field = allFields[col];
                Excel attr = field.getAnnotation(Excel.class);
                if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
                {
                    // è®¾ç½®ç±»çš„私有字段属性可访问.
                    field.setAccessible(true);
                    Integer column = cellMap.get(attr.name());
                    fieldsMap.put(column, field);
                }
            }
            for (int i = 1; i < rows; i++)
            {
                // ä»Žç¬¬2行开始取数据,默认第一行是表头.
                Row row = sheet.getRow(i);
                T entity = null;
                for (Map.Entry<Integer, Field> entry : fieldsMap.entrySet())
                {
                    Object val = this.getCellValue(row, entry.getKey());

                    // å¦‚果不存在实例则新建.
                    entity = (entity == null ? clazz.newInstance() : entity);
                    // ä»Žmap中得到对应列的field.
                    Field field = fieldsMap.get(entry.getKey());
                    // å–得类型,并根据对象类型设置值.
                    Class<?> fieldType = field.getType();
                    if (String.class == fieldType)
                    {
                        String s = Convert.toStr(val);
                        if (StringUtils.endsWith(s, ".0"))
                        {
                            val = StringUtils.substringBefore(s, ".0");
                        }
                        else
                        {
                            val = Convert.toStr(val);
                        }
                    }
                    else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType))
                    {
                        val = Convert.toInt(val);
                    }
                    else if ((Long.TYPE == fieldType) || (Long.class == fieldType))
                    {
                        val = Convert.toLong(val);
                    }
                    else if ((Double.TYPE == fieldType) || (Double.class == fieldType))
                    {
                        val = Convert.toDouble(val);
                    }
                    else if ((Float.TYPE == fieldType) || (Float.class == fieldType))
                    {
                        val = Convert.toFloat(val);
                    }
                    else if (BigDecimal.class == fieldType)
                    {
                        val = Convert.toBigDecimal(val);
                    }
                    else if (Date.class == fieldType)
                    {
                        if (val instanceof String)
                        {
                            val = DateUtils.parseDate(val);
                        }
                        else if (val instanceof Double)
                        {
                            val = DateUtil.getJavaDate((Double) val);
                        }
                    }
                    if (StringUtils.isNotNull(fieldType))
                    {
                        Excel attr = field.getAnnotation(Excel.class);
                        String propertyName = field.getName();
                        if (StringUtils.isNotEmpty(attr.targetAttr()))
                        {
                            propertyName = field.getName() + "." + attr.targetAttr();
                        }
                        else if (StringUtils.isNotEmpty(attr.readConverterExp()))
                        {
                            val = reverseByExp(String.valueOf(val), attr.readConverterExp());
                        }
                        ReflectUtils.invokeSetter(entity, propertyName, val);
                    }
                }
                list.add(entity);
            }
        }
        return list;
    }

    /**
     * å¯¹list数据源将其里面的数据导入到excel表单
     * 
     * @param list å¯¼å‡ºæ•°æ®é›†åˆ
     * @param sheetName å·¥ä½œè¡¨çš„名称
     * @return ç»“æžœ
     */
    public AjaxResult exportExcel(List<T> list, String sheetName)
    {
        this.init(list, sheetName, Type.EXPORT);
        return exportExcel();
    }

    /**
     * å¯¹list数据源将其里面的数据导入到excel表单
     * 
     * @param sheetName å·¥ä½œè¡¨çš„名称
     * @return ç»“æžœ
     */
    public AjaxResult importTemplateExcel(String sheetName)
    {
        this.init(null, sheetName, Type.IMPORT);
        return exportExcel();
    }

    /**
     * å¯¹list数据源将其里面的数据导入到excel表单
     * 
     * @return ç»“æžœ
     */
    public AjaxResult exportExcel()
    {
        OutputStream out = null;
        try
        {
            // å–出一共有多少个sheet.
            double sheetNo = Math.ceil(list.size() / sheetSize);
            for (int index = 0; index <= sheetNo; index++)
            {
                createSheet(sheetNo, index);

                // äº§ç”Ÿä¸€è¡Œ
                Row row = sheet.createRow(0);
                int column = 0;
                // å†™å…¥å„个字段的列头名称
                for (Object[] os : fields)
                {
                    Excel excel = (Excel) os[1];
                    this.createCell(excel, row, column++);
                }
                if (Type.EXPORT.equals(type))
                {
                    fillExcelData(index, row);
                }
            }
            String filename = encodingFilename(sheetName);
            out = new FileOutputStream(getAbsoluteFile(filename));
            wb.write(out);
            return AjaxResult.success(filename);
        }
        catch (Exception e)
        {
            log.error("导出Excel异常{}", e.getMessage());
            throw new CustomException("导出Excel失败，请联系网站管理员！");
        }
        finally
        {
            if (wb != null)
            {
                try
                {
                    wb.close();
                }
                catch (IOException e1)
                {
                    e1.printStackTrace();
                }
            }
            if (out != null)
            {
                try
                {
                    out.close();
                }
                catch (IOException e1)
                {
                    e1.printStackTrace();
                }
            }
        }
    }

    /**
     * å¡«å……excel数据
     * 
     * @param index åºå·
     * @param row å•å…ƒæ ¼è¡Œ
     */
    public void fillExcelData(int index, Row row)
    {
        int startNo = index * sheetSize;
        int endNo = Math.min(startNo + sheetSize, list.size());
        for (int i = startNo; i < endNo; i++)
        {
            row = sheet.createRow(i + 1 - startNo);
            // å¾—到导出对象.
            T vo = (T) list.get(i);
            int column = 0;
            for (Object[] os : fields)
            {
                Field field = (Field) os[0];
                Excel excel = (Excel) os[1];
                // è®¾ç½®å®žä½“类私有属性可访问
                field.setAccessible(true);
                this.addCell(excel, row, vo, field, column++);
            }
        }
    }

    /**
     * åˆ›å»ºè¡¨æ ¼æ ·å¼
     * 
     * @param wb å·¥ä½œè–„对象
     * @return æ ·å¼åˆ—表
     */
    private Map<String, CellStyle> createStyles(Workbook wb)
    {
        // å†™å…¥å„条记录,每条记录对应excel表中的一行
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);

        return styles;
    }

    /**
     * åˆ›å»ºå•å…ƒæ ¼
     */
    public Cell createCell(Excel attr, Row row, int column)
    {
        // åˆ›å»ºåˆ—
        Cell cell = row.createCell(column);
        // å†™å…¥åˆ—信息
        cell.setCellValue(attr.name());
        setDataValidation(attr, row, column);
        cell.setCellStyle(styles.get("header"));
        return cell;
    }

    /**
     * è®¾ç½®å•å…ƒæ ¼ä¿¡æ¯
     * 
     * @param value å•å…ƒæ ¼å€¼
     * @param attr æ³¨è§£ç›¸å…³
     * @param cell å•å…ƒæ ¼ä¿¡æ¯
     */
    public void setCellVo(Object value, Excel attr, Cell cell)
    {
        if (ColumnType.STRING == attr.cellType())
        {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix());
        }
        else if (ColumnType.NUMERIC == attr.cellType())
        {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(Integer.parseInt(value + ""));
        }
    }

    /**
     * åˆ›å»ºè¡¨æ ¼æ ·å¼
     */
    public void setDataValidation(Excel attr, Row row, int column)
    {
        if (attr.name().indexOf("注：") >= 0)
        {
            sheet.setColumnWidth(column, 6000);
        }
        else
        {
            // è®¾ç½®åˆ—宽
            sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
            row.setHeight((short) (attr.height() * 20));
        }
        // å¦‚果设置了提示信息则鼠标放上去提示.
        if (StringUtils.isNotEmpty(attr.prompt()))
        {
            // è¿™é‡Œé»˜è®¤è®¾äº†2-101列提示.
            setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column);
        }
        // å¦‚果设置了combo属性则本列只能选择不能输入
        if (attr.combo().length > 0)
        {
            // è¿™é‡Œé»˜è®¤è®¾äº†2-101列只能选择不能输入.
            setXSSFValidation(sheet, attr.combo(), 1, 100, column, column);
        }
    }

    /**
     * æ·»åŠ å•å…ƒæ ¼
     */
    public Cell addCell(Excel attr, Row row, T vo, Field field, int column)
    {
        Cell cell = null;
        try
        {
            // è®¾ç½®è¡Œé«˜
            row.setHeight((short) (attr.height() * 20));
            // æ ¹æ®Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
            if (attr.isExport())
            {
                // åˆ›å»ºcell
                cell = row.createCell(column);
                cell.setCellStyle(styles.get("data"));

                // ç”¨äºŽè¯»å–对象中的属性
                Object value = getTargetValue(vo, field, attr);
                String dateFormat = attr.dateFormat();
                String readConverterExp = attr.readConverterExp();
                if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value))
                {
                    cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
                }
                else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value))
                {
                    cell.setCellValue(convertByExp(String.valueOf(value), readConverterExp));
                }
                else
                {
                    // è®¾ç½®åˆ—类型
                    setCellVo(value, attr, cell);
                }
            }
        }
        catch (Exception e)
        {
            log.error("导出Excel失败{}", e);
        }
        return cell;
    }

    /**
     * è®¾ç½® POI XSSFSheet å•å…ƒæ ¼æç¤º
     * 
     * @param sheet è¡¨å•
     * @param promptTitle æç¤ºæ ‡é¢˜
     * @param promptContent æç¤ºå†…容
     * @param firstRow å¼€å§‹è¡Œ
     * @param endRow ç»“束行
     * @param firstCol å¼€å§‹åˆ—
     * @param endCol ç»“束列
     */
    public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
            int firstCol, int endCol)
    {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        dataValidation.createPromptBox(promptTitle, promptContent);
        dataValidation.setShowPromptBox(true);
        sheet.addValidationData(dataValidation);
    }

    /**
     * è®¾ç½®æŸäº›åˆ—的值只能输入预制的数据,显示下拉框.
     * 
     * @param sheet è¦è®¾ç½®çš„sheet.
     * @param textlist ä¸‹æ‹‰æ¡†æ˜¾ç¤ºçš„内容
     * @param firstRow å¼€å§‹è¡Œ
     * @param endRow ç»“束行
     * @param firstCol å¼€å§‹åˆ—
     * @param endCol ç»“束列
     * @return è®¾ç½®å¥½çš„sheet.
     */
    public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol)
    {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // åŠ è½½ä¸‹æ‹‰åˆ—表内容
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
        // è®¾ç½®æ•°æ®æœ‰æ•ˆæ€§åŠ è½½åœ¨å“ªä¸ªå•å…ƒæ ¼ä¸Š,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // æ•°æ®æœ‰æ•ˆæ€§å¯¹è±¡
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        // å¤„理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation)
        {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        }
        else
        {
            dataValidation.setSuppressDropDownArrow(false);
        }

        sheet.addValidationData(dataValidation);
    }

    /**
     * è§£æžå¯¼å‡ºå€¼ 0=男,1=女,2=未知
     * 
     * @param propertyValue å‚数值
     * @param converterExp ç¿»è¯‘注解
     * @return è§£æžåŽå€¼
     * @throws Exception
     */
    public static String convertByExp(String propertyValue, String converterExp) throws Exception
    {
        try
        {
            String[] convertSource = converterExp.split(",");
            for (String item : convertSource)
            {
                String[] itemArray = item.split("=");
                if (itemArray[0].equals(propertyValue))
                {
                    return itemArray[1];
                }
            }
        }
        catch (Exception e)
        {
            throw e;
        }
        return propertyValue;
    }

    /**
     * åå‘解析值 ç”·=0,女=1,未知=2
     * 
     * @param propertyValue å‚数值
     * @param converterExp ç¿»è¯‘注解
     * @return è§£æžåŽå€¼
     * @throws Exception
     */
    public static String reverseByExp(String propertyValue, String converterExp) throws Exception
    {
        try
        {
            String[] convertSource = converterExp.split(",");
            for (String item : convertSource)
            {
                String[] itemArray = item.split("=");
                if (itemArray[1].equals(propertyValue))
                {
                    return itemArray[0];
                }
            }
        }
        catch (Exception e)
        {
            throw e;
        }
        return propertyValue;
    }

    /**
     * ç¼–码文件名
     */
    public String encodingFilename(String filename)
    {
        filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
        return filename;
    }

    /**
     * èŽ·å–下载路径
     * 
     * @param filename æ–‡ä»¶åç§°
     */
    public String getAbsoluteFile(String filename)
    {
        String downloadPath = RuoYiConfig.getDownloadPath() + filename;
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists())
        {
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }

    /**
     * èŽ·å–bean中的属性值
     * 
     * @param vo å®žä½“对象
     * @param field å­—段
     * @param excel æ³¨è§£
     * @return æœ€ç»ˆçš„属性值
     * @throws Exception
     */
    private Object getTargetValue(T vo, Field field, Excel excel) throws Exception
    {
        Object o = field.get(vo);
        if (StringUtils.isNotEmpty(excel.targetAttr()))
        {
            String target = excel.targetAttr();
            if (target.indexOf(".") > -1)
            {
                String[] targets = target.split("[.]");
                for (String name : targets)
                {
                    o = getValue(o, name);
                }
            }
            else
            {
                o = getValue(o, target);
            }
        }
        return o;
    }

    /**
     * ä»¥ç±»çš„属性的get方法方法形式获取值
     * 
     * @param o
     * @param name
     * @return value
     * @throws Exception
     */
    private Object getValue(Object o, String name) throws Exception
    {
        if (StringUtils.isNotEmpty(name))
        {
            Class<?> clazz = o.getClass();
            String methodName = "get" + name.substring(0, 1).toUpperCase() + name.substring(1);
            Method method = clazz.getMethod(methodName);
            o = method.invoke(o);
        }
        return o;
    }

    /**
     * å¾—到所有定义字段
     */
    private void createExcelField()
    {
        this.fields = new ArrayList<Object[]>();
        List<Field> tempFields = new ArrayList<>();
        tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
        tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
        for (Field field : tempFields)
        {
            // å•æ³¨è§£
            if (field.isAnnotationPresent(Excel.class))
            {
                putToField(field, field.getAnnotation(Excel.class));
            }

            // å¤šæ³¨è§£
            if (field.isAnnotationPresent(Excels.class))
            {
                Excels attrs = field.getAnnotation(Excels.class);
                Excel[] excels = attrs.value();
                for (Excel excel : excels)
                {
                    putToField(field, excel);
                }
            }
        }
        this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());
    }

    /**
     * æ”¾åˆ°å­—段集合中
     */
    private void putToField(Field field, Excel attr)
    {
        if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
        {
            this.fields.add(new Object[] { field, attr });
        }
    }

    /**
     * åˆ›å»ºä¸€ä¸ªå·¥ä½œç°¿
     */
    public void createWorkbook()
    {
        this.wb = new SXSSFWorkbook(500);
    }

    /**
     * åˆ›å»ºå·¥ä½œè¡¨
     * 
     * @param sheetNo sheet数量
     * @param index åºå·
     */
    public void createSheet(double sheetNo, int index)
    {
        this.sheet = wb.createSheet();
        this.styles = createStyles(wb);
        // è®¾ç½®å·¥ä½œè¡¨çš„名称.
        if (sheetNo == 0)
        {
            wb.setSheetName(index, sheetName);
        }
        else
        {
            wb.setSheetName(index, sheetName + index);
        }
    }

    /**
     * èŽ·å–单元格值
     * 
     * @param row èŽ·å–çš„è¡Œ
     * @param column èŽ·å–单元格列号
     * @return å•å…ƒæ ¼å€¼
     */
    public Object getCellValue(Row row, int column)
    {
        if (row == null)
        {
            return row;
        }
        Object val = "";
        try
        {
            Cell cell = row.getCell(column);
            if (StringUtils.isNotNull(cell))
            {
                if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA)
                {
                    val = cell.getNumericCellValue();
                    if (HSSFDateUtil.isCellDateFormatted(cell))
                    {
                        val = DateUtil.getJavaDate((Double) val); // POI Excel æ—¥æœŸæ ¼å¼è½¬æ¢
                    }
                    else
                    {
                        if ((Double) val % 1 > 0)
                        {
                            val = new DecimalFormat("0.00").format(val);
                        }
                        else
                        {
                            val = new DecimalFormat("0").format(val);
                        }
                    }
                }
                else if (cell.getCellTypeEnum() == CellType.STRING)
                {
                    val = cell.getStringCellValue();
                }
                else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
                {
                    val = cell.getBooleanCellValue();
                }
                else if (cell.getCellTypeEnum() == CellType.ERROR)
                {
                    val = cell.getErrorCellValue();
                }

            }
        }
        catch (Exception e)
        {
            return val;
        }
        return val;
    }
package com.ruoyi.common.utils.poi;



import java.io.File;

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.Method;

import java.math.BigDecimal;

import java.text.DecimalFormat;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.Comparator;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.UUID;

import java.util.stream.Collectors;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.ss.usermodel.BorderStyle;

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.DataValidation;

import org.apache.poi.ss.usermodel.DataValidationConstraint;

import org.apache.poi.ss.usermodel.DataValidationHelper;

import org.apache.poi.ss.usermodel.DateUtil;

import org.apache.poi.ss.usermodel.FillPatternType;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.ss.usermodel.HorizontalAlignment;

import org.apache.poi.ss.usermodel.IndexedColors;

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.CellRangeAddressList;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFDataValidation;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import com.ruoyi.common.annotation.Excel;

import com.ruoyi.common.annotation.Excel.ColumnType;

import com.ruoyi.common.annotation.Excel.Type;

import com.ruoyi.common.annotation.Excels;

import com.ruoyi.common.config.RuoYiConfig;

import com.ruoyi.common.core.domain.AjaxResult;

import com.ruoyi.common.core.text.Convert;

import com.ruoyi.common.exception.CustomException;

import com.ruoyi.common.utils.DateUtils;

import com.ruoyi.common.utils.StringUtils;

import com.ruoyi.common.utils.reflect.ReflectUtils;



/**

 * Excel相关处理

 * 
 * @author ruoyi

 */

public class ExcelUtil<T>

{

    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);



    /**

     * Excel sheet最大行数，默认65536

     */

    public static final int sheetSize = 65536;



    /**

     * å·¥ä½œè¡¨åç§°

     */

    private String sheetName;



    /**

     * å¯¼å‡ºç±»åž‹ï¼ˆEXPORT:导出数据；IMPORT：导入模板）

     */

    private Type type;



    /**

     * å·¥ä½œè–„对象

     */

    private Workbook wb;



    /**

     * å·¥ä½œè¡¨å¯¹è±¡

     */

    private Sheet sheet;



    /**

     * æ ·å¼åˆ—表

     */

    private Map<String, CellStyle> styles;



    /**

     * å¯¼å…¥å¯¼å‡ºæ•°æ®åˆ—表

     */

    private List<T> list;



    /**

     * æ³¨è§£åˆ—表

     */

    private List<Object[]> fields;



    /**

     * å®žä½“对象

     */

    public Class<T> clazz;



    public ExcelUtil(Class<T> clazz)

    {

        this.clazz = clazz;

    }



    public void init(List<T> list, String sheetName, Type type)

    {

        if (list == null)

        {

            list = new ArrayList<T>();

        }

        this.list = list;

        this.sheetName = sheetName;

        this.type = type;

        createExcelField();

        createWorkbook();

    }



    /**

     * å¯¹excel表单默认第一个索引名转换成list

     * 
     * @param is è¾“入流

     * @return è½¬æ¢åŽé›†åˆ

     */

    public List<T> importExcel(InputStream is) throws Exception

    {

        return importExcel(StringUtils.EMPTY, is);

    }



    /**

     * å¯¹excel表单指定表格索引名转换成list

     * 
     * @param sheetName è¡¨æ ¼ç´¢å¼•å

     * @param is è¾“入流

     * @return è½¬æ¢åŽé›†åˆ

     */

    public List<T> importExcel(String sheetName, InputStream is) throws Exception

    {

        this.type = Type.IMPORT;

        this.wb = WorkbookFactory.create(is);

        List<T> list = new ArrayList<T>();

        Sheet sheet = null;

        if (StringUtils.isNotEmpty(sheetName))

        {

            // å¦‚果指定sheet名,则取指定sheet中的内容.

            sheet = wb.getSheet(sheetName);

        }

        else

        {

            // å¦‚果传入的sheet名不存在则默认指向第1个sheet.

            sheet = wb.getSheetAt(0);

        }



        if (sheet == null)

        {

            throw new IOException("文件sheet不存在");

        }



        int rows = sheet.getPhysicalNumberOfRows();



        if (rows > 0)

        {

            // å®šä¹‰ä¸€ä¸ªmap用于存放excel列的序号和field.

            Map<String, Integer> cellMap = new HashMap<String, Integer>();

            // èŽ·å–表头

            Row heard = sheet.getRow(0);

            for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++)

            {

                Cell cell = heard.getCell(i);

                if (StringUtils.isNotNull(cell))

                {

                    String value = this.getCellValue(heard, i).toString();

                    cellMap.put(value, i);

                }

                else

                {

                    cellMap.put(null, i);

                }

            }

            // æœ‰æ•°æ®æ—¶æ‰å¤„理 å¾—到类的所有field.

            Field[] allFields = clazz.getDeclaredFields();

            // å®šä¹‰ä¸€ä¸ªmap用于存放列的序号和field.

            Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();

            for (int col = 0; col < allFields.length; col++)

            {

                Field field = allFields[col];

                Excel attr = field.getAnnotation(Excel.class);

                if (attr != null && (attr.type() == Type.ALL || attr.type() == type))

                {

                    // è®¾ç½®ç±»çš„私有字段属性可访问.

                    field.setAccessible(true);

                    Integer column = cellMap.get(attr.name());

                    fieldsMap.put(column, field);

                }

            }

            for (int i = 1; i < rows; i++)

            {

                // ä»Žç¬¬2行开始取数据,默认第一行是表头.

                Row row = sheet.getRow(i);

                T entity = null;

                for (Map.Entry<Integer, Field> entry : fieldsMap.entrySet())

                {

                    Object val = this.getCellValue(row, entry.getKey());



                    // å¦‚果不存在实例则新建.

                    entity = (entity == null ? clazz.newInstance() : entity);

                    // ä»Žmap中得到对应列的field.

                    Field field = fieldsMap.get(entry.getKey());

                    // å–得类型,并根据对象类型设置值.

                    Class<?> fieldType = field.getType();

                    if (String.class == fieldType)

                    {

                        String s = Convert.toStr(val);

                        if (StringUtils.endsWith(s, ".0"))

                        {

                            val = StringUtils.substringBefore(s, ".0");

                        }

                        else

                        {

                            val = Convert.toStr(val);

                        }

                    }

                    else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType))

                    {

                        val = Convert.toInt(val);

                    }

                    else if ((Long.TYPE == fieldType) || (Long.class == fieldType))

                    {

                        val = Convert.toLong(val);

                    }

                    else if ((Double.TYPE == fieldType) || (Double.class == fieldType))

                    {

                        val = Convert.toDouble(val);

                    }

                    else if ((Float.TYPE == fieldType) || (Float.class == fieldType))

                    {

                        val = Convert.toFloat(val);

                    }

                    else if (BigDecimal.class == fieldType)

                    {

                        val = Convert.toBigDecimal(val);

                    }

                    else if (Date.class == fieldType)

                    {

                        if (val instanceof String)

                        {

                            val = DateUtils.parseDate(val);

                        }

                        else if (val instanceof Double)

                        {

                            val = DateUtil.getJavaDate((Double) val);

                        }

                    }

                    if (StringUtils.isNotNull(fieldType))

                    {

                        Excel attr = field.getAnnotation(Excel.class);

                        String propertyName = field.getName();

                        if (StringUtils.isNotEmpty(attr.targetAttr()))

                        {

                            propertyName = field.getName() + "." + attr.targetAttr();

                        }

                        else if (StringUtils.isNotEmpty(attr.readConverterExp()))

                        {

                            val = reverseByExp(String.valueOf(val), attr.readConverterExp());

                        }

                        ReflectUtils.invokeSetter(entity, propertyName, val);

                    }

                }

                list.add(entity);

            }

        }

        return list;

    }



    /**

     * å¯¹list数据源将其里面的数据导入到excel表单

     * 
     * @param list å¯¼å‡ºæ•°æ®é›†åˆ

     * @param sheetName å·¥ä½œè¡¨çš„名称

     * @return ç»“æžœ

     */

    public AjaxResult exportExcel(List<T> list, String sheetName)

    {

        this.init(list, sheetName, Type.EXPORT);

        return exportExcel();

    }



    /**

     * å¯¹list数据源将其里面的数据导入到excel表单

     * 
     * @param sheetName å·¥ä½œè¡¨çš„名称

     * @return ç»“æžœ

     */

    public AjaxResult importTemplateExcel(String sheetName)

    {

        this.init(null, sheetName, Type.IMPORT);

        return exportExcel();

    }



    /**

     * å¯¹list数据源将其里面的数据导入到excel表单

     * 
     * @return ç»“æžœ

     */

    public AjaxResult exportExcel()

    {

        OutputStream out = null;

        try

        {

            // å–出一共有多少个sheet.

            double sheetNo = Math.ceil(list.size() / sheetSize);

            for (int index = 0; index <= sheetNo; index++)

            {

                createSheet(sheetNo, index);



                // äº§ç”Ÿä¸€è¡Œ

                Row row = sheet.createRow(0);

                int column = 0;

                // å†™å…¥å„个字段的列头名称

                for (Object[] os : fields)

                {

                    Excel excel = (Excel) os[1];

                    this.createCell(excel, row, column++);

                }

                if (Type.EXPORT.equals(type))

                {

                    fillExcelData(index, row);

                }

            }

            String filename = encodingFilename(sheetName);

            out = new FileOutputStream(getAbsoluteFile(filename));

            wb.write(out);

            return AjaxResult.success(filename);

        }

        catch (Exception e)

        {

            log.error("导出Excel异常{}", e.getMessage());

            throw new CustomException("导出Excel失败，请联系网站管理员！");

        }

        finally

        {

            if (wb != null)

            {

                try

                {

                    wb.close();

                }

                catch (IOException e1)

                {

                    e1.printStackTrace();

                }

            }

            if (out != null)

            {

                try

                {

                    out.close();

                }

                catch (IOException e1)

                {

                    e1.printStackTrace();

                }

            }

        }

    }



    /**

     * å¡«å……excel数据

     * 
     * @param index åºå·

     * @param row å•å…ƒæ ¼è¡Œ

     */

    public void fillExcelData(int index, Row row)

    {

        int startNo = index * sheetSize;

        int endNo = Math.min(startNo + sheetSize, list.size());

        for (int i = startNo; i < endNo; i++)

        {

            row = sheet.createRow(i + 1 - startNo);

            // å¾—到导出对象.

            T vo = (T) list.get(i);

            int column = 0;

            for (Object[] os : fields)

            {

                Field field = (Field) os[0];

                Excel excel = (Excel) os[1];

                // è®¾ç½®å®žä½“类私有属性可访问

                field.setAccessible(true);

                this.addCell(excel, row, vo, field, column++);

            }

        }

    }



    /**

     * åˆ›å»ºè¡¨æ ¼æ ·å¼

     * 
     * @param wb å·¥ä½œè–„对象

     * @return æ ·å¼åˆ—表

     */

    private Map<String, CellStyle> createStyles(Workbook wb)

    {

        // å†™å…¥å„条记录,每条记录对应excel表中的一行

        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

        CellStyle style = wb.createCellStyle();

        style.setAlignment(HorizontalAlignment.CENTER);

        style.setVerticalAlignment(VerticalAlignment.CENTER);

        style.setBorderRight(BorderStyle.THIN);

        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

        style.setBorderLeft(BorderStyle.THIN);

        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

        style.setBorderTop(BorderStyle.THIN);

        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

        style.setBorderBottom(BorderStyle.THIN);

        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

        Font dataFont = wb.createFont();

        dataFont.setFontName("Arial");

        dataFont.setFontHeightInPoints((short) 10);

        style.setFont(dataFont);

        styles.put("data", style);



        style = wb.createCellStyle();

        style.cloneStyleFrom(styles.get("data"));

        style.setAlignment(HorizontalAlignment.CENTER);

        style.setVerticalAlignment(VerticalAlignment.CENTER);

        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());

        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        Font headerFont = wb.createFont();

        headerFont.setFontName("Arial");

        headerFont.setFontHeightInPoints((short) 10);

        headerFont.setBold(true);

        headerFont.setColor(IndexedColors.WHITE.getIndex());

        style.setFont(headerFont);

        styles.put("header", style);



        return styles;

    }



    /**

     * åˆ›å»ºå•å…ƒæ ¼

     */

    public Cell createCell(Excel attr, Row row, int column)

    {

        // åˆ›å»ºåˆ—

        Cell cell = row.createCell(column);

        // å†™å…¥åˆ—信息

        cell.setCellValue(attr.name());

        setDataValidation(attr, row, column);

        cell.setCellStyle(styles.get("header"));

        return cell;

    }



    /**

     * è®¾ç½®å•å…ƒæ ¼ä¿¡æ¯

     * 
     * @param value å•å…ƒæ ¼å€¼

     * @param attr æ³¨è§£ç›¸å…³

     * @param cell å•å…ƒæ ¼ä¿¡æ¯

     */

    public void setCellVo(Object value, Excel attr, Cell cell)

    {

        if (ColumnType.STRING == attr.cellType())

        {

            cell.setCellType(CellType.NUMERIC);

            cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix());

        }

        else if (ColumnType.NUMERIC == attr.cellType())

        {

            cell.setCellType(CellType.NUMERIC);

            cell.setCellValue(Integer.parseInt(value + ""));

        }

    }



    /**

     * åˆ›å»ºè¡¨æ ¼æ ·å¼

     */

    public void setDataValidation(Excel attr, Row row, int column)

    {

        if (attr.name().indexOf("注：") >= 0)

        {

            sheet.setColumnWidth(column, 6000);

        }

        else

        {

            // è®¾ç½®åˆ—宽

            sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));

            row.setHeight((short) (attr.height() * 20));

        }

        // å¦‚果设置了提示信息则鼠标放上去提示.

        if (StringUtils.isNotEmpty(attr.prompt()))

        {

            // è¿™é‡Œé»˜è®¤è®¾äº†2-101列提示.

            setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column);

        }

        // å¦‚果设置了combo属性则本列只能选择不能输入

        if (attr.combo().length > 0)

        {

            // è¿™é‡Œé»˜è®¤è®¾äº†2-101列只能选择不能输入.

            setXSSFValidation(sheet, attr.combo(), 1, 100, column, column);

        }

    }



    /**

     * æ·»åŠ å•å…ƒæ ¼

     */

    public Cell addCell(Excel attr, Row row, T vo, Field field, int column)

    {

        Cell cell = null;

        try

        {

            // è®¾ç½®è¡Œé«˜

            row.setHeight((short) (attr.height() * 20));

            // æ ¹æ®Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.

            if (attr.isExport())

            {

                // åˆ›å»ºcell

                cell = row.createCell(column);

                cell.setCellStyle(styles.get("data"));



                // ç”¨äºŽè¯»å–对象中的属性

                Object value = getTargetValue(vo, field, attr);

                String dateFormat = attr.dateFormat();

                String readConverterExp = attr.readConverterExp();

                if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value))

                {

                    cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));

                }

                else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value))

                {

                    cell.setCellValue(convertByExp(String.valueOf(value), readConverterExp));

                }

                else

                {

                    // è®¾ç½®åˆ—类型

                    setCellVo(value, attr, cell);

                }

            }

        }

        catch (Exception e)

        {

            log.error("导出Excel失败{}", e);

        }

        return cell;

    }



    /**

     * è®¾ç½® POI XSSFSheet å•å…ƒæ ¼æç¤º

     * 
     * @param sheet è¡¨å•

     * @param promptTitle æç¤ºæ ‡é¢˜

     * @param promptContent æç¤ºå†…容

     * @param firstRow å¼€å§‹è¡Œ

     * @param endRow ç»“束行

     * @param firstCol å¼€å§‹åˆ—

     * @param endCol ç»“束列

     */

    public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,

            int firstCol, int endCol)

    {

        DataValidationHelper helper = sheet.getDataValidationHelper();

        DataValidationConstraint constraint = helper.createCustomConstraint("DD1");

        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);

        DataValidation dataValidation = helper.createValidation(constraint, regions);

        dataValidation.createPromptBox(promptTitle, promptContent);

        dataValidation.setShowPromptBox(true);

        sheet.addValidationData(dataValidation);

    }



    /**

     * è®¾ç½®æŸäº›åˆ—的值只能输入预制的数据,显示下拉框.

     * 
     * @param sheet è¦è®¾ç½®çš„sheet.

     * @param textlist ä¸‹æ‹‰æ¡†æ˜¾ç¤ºçš„内容

     * @param firstRow å¼€å§‹è¡Œ

     * @param endRow ç»“束行

     * @param firstCol å¼€å§‹åˆ—

     * @param endCol ç»“束列

     * @return è®¾ç½®å¥½çš„sheet.

     */

    public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol)

    {

        DataValidationHelper helper = sheet.getDataValidationHelper();

        // åŠ è½½ä¸‹æ‹‰åˆ—表内容

        DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);

        // è®¾ç½®æ•°æ®æœ‰æ•ˆæ€§åŠ è½½åœ¨å“ªä¸ªå•å…ƒæ ¼ä¸Š,四个参数分别是：起始行、终止行、起始列、终止列

        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);

        // æ•°æ®æœ‰æ•ˆæ€§å¯¹è±¡

        DataValidation dataValidation = helper.createValidation(constraint, regions);

        // å¤„理Excel兼容性问题

        if (dataValidation instanceof XSSFDataValidation)

        {

            dataValidation.setSuppressDropDownArrow(true);

            dataValidation.setShowErrorBox(true);

        }

        else

        {

            dataValidation.setSuppressDropDownArrow(false);

        }



        sheet.addValidationData(dataValidation);

    }



    /**

     * è§£æžå¯¼å‡ºå€¼ 0=男,1=女,2=未知

     * 
     * @param propertyValue å‚数值

     * @param converterExp ç¿»è¯‘注解

     * @return è§£æžåŽå€¼

     * @throws Exception

     */

    public static String convertByExp(String propertyValue, String converterExp) throws Exception

    {

        try

        {

            String[] convertSource = converterExp.split(",");

            for (String item : convertSource)

            {

                String[] itemArray = item.split("=");

                if (itemArray[0].equals(propertyValue))

                {

                    return itemArray[1];

                }

            }

        }

        catch (Exception e)

        {

            throw e;

        }

        return propertyValue;

    }



    /**

     * åå‘解析值 ç”·=0,女=1,未知=2

     * 
     * @param propertyValue å‚数值

     * @param converterExp ç¿»è¯‘注解

     * @return è§£æžåŽå€¼

     * @throws Exception

     */

    public static String reverseByExp(String propertyValue, String converterExp) throws Exception

    {

        try

        {

            String[] convertSource = converterExp.split(",");

            for (String item : convertSource)

            {

                String[] itemArray = item.split("=");

                if (itemArray[1].equals(propertyValue))

                {

                    return itemArray[0];

                }

            }

        }

        catch (Exception e)

        {

            throw e;

        }

        return propertyValue;

    }



    /**

     * ç¼–码文件名

     */

    public String encodingFilename(String filename)

    {

        filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";

        return filename;

    }



    /**

     * èŽ·å–下载路径

     * 
     * @param filename æ–‡ä»¶åç§°

     */

    public String getAbsoluteFile(String filename)

    {

        String downloadPath = RuoYiConfig.getDownloadPath() + filename;

        File desc = new File(downloadPath);

        if (!desc.getParentFile().exists())

        {

            desc.getParentFile().mkdirs();

        }

        return downloadPath;

    }



    /**

     * èŽ·å–bean中的属性值

     * 
     * @param vo å®žä½“对象

     * @param field å­—段

     * @param excel æ³¨è§£

     * @return æœ€ç»ˆçš„属性值

     * @throws Exception

     */

    private Object getTargetValue(T vo, Field field, Excel excel) throws Exception

    {

        Object o = field.get(vo);

        if (StringUtils.isNotEmpty(excel.targetAttr()))

        {

            String target = excel.targetAttr();

            if (target.indexOf(".") > -1)

            {

                String[] targets = target.split("[.]");

                for (String name : targets)

                {

                    o = getValue(o, name);

                }

            }

            else

            {

                o = getValue(o, target);

            }

        }

        return o;

    }



    /**

     * ä»¥ç±»çš„属性的get方法方法形式获取值

     * 
     * @param o

     * @param name

     * @return value

     * @throws Exception

     */

    private Object getValue(Object o, String name) throws Exception

    {

        if (StringUtils.isNotEmpty(name))

        {

            Class<?> clazz = o.getClass();

            String methodName = "get" + name.substring(0, 1).toUpperCase() + name.substring(1);

            Method method = clazz.getMethod(methodName);

            o = method.invoke(o);

        }

        return o;

    }



    /**

     * å¾—到所有定义字段

     */

    private void createExcelField()

    {

        this.fields = new ArrayList<Object[]>();

        List<Field> tempFields = new ArrayList<>();

        tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));

        tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));

        for (Field field : tempFields)

        {

            // å•æ³¨è§£

            if (field.isAnnotationPresent(Excel.class))

            {

                putToField(field, field.getAnnotation(Excel.class));

            }



            // å¤šæ³¨è§£

            if (field.isAnnotationPresent(Excels.class))

            {

                Excels attrs = field.getAnnotation(Excels.class);

                Excel[] excels = attrs.value();

                for (Excel excel : excels)

                {

                    putToField(field, excel);

                }

            }

        }

        this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());

    }



    /**

     * æ”¾åˆ°å­—段集合中

     */

    private void putToField(Field field, Excel attr)

    {

        if (attr != null && (attr.type() == Type.ALL || attr.type() == type))

        {

            this.fields.add(new Object[] { field, attr });

        }

    }



    /**

     * åˆ›å»ºä¸€ä¸ªå·¥ä½œç°¿

     */

    public void createWorkbook()

    {

        this.wb = new SXSSFWorkbook(500);

    }



    /**

     * åˆ›å»ºå·¥ä½œè¡¨

     * 
     * @param sheetNo sheet数量

     * @param index åºå·

     */

    public void createSheet(double sheetNo, int index)

    {

        this.sheet = wb.createSheet();

        this.styles = createStyles(wb);

        // è®¾ç½®å·¥ä½œè¡¨çš„名称.

        if (sheetNo == 0)

        {

            wb.setSheetName(index, sheetName);

        }

        else

        {

            wb.setSheetName(index, sheetName + index);

        }

    }



    /**

     * èŽ·å–单元格值

     * 
     * @param row èŽ·å–çš„è¡Œ

     * @param column èŽ·å–单元格列号

     * @return å•å…ƒæ ¼å€¼

     */

    public Object getCellValue(Row row, int column)

    {

        if (row == null)

        {

            return row;

        }

        Object val = "";

        try

        {

            Cell cell = row.getCell(column);

            if (StringUtils.isNotNull(cell))

            {

                if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA)

                {

                    val = cell.getNumericCellValue();

                    if (HSSFDateUtil.isCellDateFormatted(cell))

                    {

                        val = DateUtil.getJavaDate((Double) val); // POI Excel æ—¥æœŸæ ¼å¼è½¬æ¢

                    }

                    else

                    {

                        if ((Double) val % 1 > 0)

                        {

                            val = new DecimalFormat("0.00").format(val);

                        }

                        else

                        {

                            val = new DecimalFormat("0").format(val);

                        }

                    }

                }

                else if (cell.getCellTypeEnum() == CellType.STRING)

                {

                    val = cell.getStringCellValue();

                }

                else if (cell.getCellTypeEnum() == CellType.BOOLEAN)

                {

                    val = cell.getBooleanCellValue();

                }

                else if (cell.getCellTypeEnum() == CellType.ERROR)

                {

                    val = cell.getErrorCellValue();

                }



            }

        }

        catch (Exception e)

        {

            return val;

        }

        return val;

    }

}