package com.buba.util;

import com.buba.annotation.ExcelCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.IOException;
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.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * Excel工具类
 */
public class MyExcel<T> {

    private Workbook workbook;
    private Sheet sheet;
    private Row row;
    private Cell cell;

    private T t;
    private List<T> tList;
    private List<Field> fieldList;
    private OutputStream outputStream;

    /**
     * 导出Excel
     *
     * @param excelName 导出页名字
     * @param dataList  导出的数据
     * @param c         要导出的对象类
     * @param response  下载需要的响应对象
     * @throws Exception
     */
    public void exportExcel(String excelName, List<T> dataList, Class<T> c, HttpServletResponse response) throws Exception {
        workbook = new XSSFWorkbook();
        sheet = workbook.createSheet(excelName);
        fieldList = filterField(c.getDeclaredFields());
        Row firstRow = sheet.createRow(0);
        int cellLength = fieldList.size();
        for (int j = 0; j < cellLength; j++) {
            cell = firstRow.createCell(j);
            cell.setCellValue(fieldList.get(j).getAnnotation(ExcelCell.class).value());
        }

        for (int i = 0; i < dataList.size(); i++) {
            row = sheet.createRow(i + 1);
            T t = dataList.get(i);
            for (int j = 0; j < cellLength; j++) {
                cell = row.createCell(j);
                cell.setCellValue(getGetMethod(t, fieldList.get(j).getName()).toString());
            }
        }

        outputStream = response.getOutputStream();
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=" + c.getSimpleName() + ".xlsx");
        response.setContentType("application/msexcel");
        workbook.write(outputStream);
        outputStream.close();
    }

    private int index = 0;
    private int count = 0;

    /**
     * 导入功能（导入的excel单元格要求设置为文本格式）
     *
     * @param excel
     * @param c
     * @return
     * @throws IOException
     * @throws IllegalAccessException
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws InstantiationException
     */
    public List<T> importExcel(MultipartFile excel, Class<T> c) throws IOException, IllegalAccessException, NoSuchMethodException, InvocationTargetException, InstantiationException {
        String excelName = excel.getOriginalFilename();
        workbook = null;
        if (excelName.endsWith("xlsx")) {
            workbook = new XSSFWorkbook(excel.getInputStream());
        } else if (excelName.endsWith("xls")) {
            workbook = new HSSFWorkbook(excel.getInputStream());
        } else {
            throw new FileNotFoundException("文件类型错误");
        }
        tList = new ArrayList<>();
        fieldList = filterField(c.getDeclaredFields());

        Iterator<Sheet> sheetIterator = workbook.sheetIterator();
        while (sheetIterator.hasNext()) {
            sheet = sheetIterator.next();
            if (sheet.getLastRowNum() == -1) return tList;
            Iterator<Row> rowIterator = sheet.rowIterator();
            rowIterator.next();// 排除第一行
            while (rowIterator.hasNext()) {
                row = rowIterator.next();
                if (row.getLastCellNum() == -1) return tList;
                Iterator<Cell> cellIterator = row.cellIterator();
                t = c.getConstructor().newInstance();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    fieldList.get(index).setAccessible(true);
                    MyExcelCellType type = fieldList.get(index).getAnnotation(ExcelCell.class).type();
                    if (cell.getCellType() == CellType.STRING) {
                        String stringCellValue = cell.getStringCellValue();
                        if ("".equals(stringCellValue.trim()))
                            count++;
                        if (MyExcelCellType.INTEGER == type) {
                            fieldList.get(index++).set(t, Integer.valueOf(stringCellValue));
                        } else if (MyExcelCellType.DOUBLE == type) {
                            fieldList.get(index++).set(t, Double.valueOf(stringCellValue));
                        } else if (MyExcelCellType.BIG_DECIMAL == type) {
                            fieldList.get(index++).set(t, new BigDecimal(stringCellValue));
                        } else {
                            fieldList.get(index++).set(t, stringCellValue);
                        }
                    } else {
                        count++;
                        index++;
                    }
                }
                if (index != count)
                    tList.add(t);
                index = 0;
                count = 0;
            }
        }
        return tList;
    }

    /**
     * 根据属性，获取get方法
     *
     * @param obj  对象
     * @param name 属性名
     * @return
     * @throws Exception
     */
    private Object getGetMethod(Object obj, String name) throws Exception {
        Method[] m = obj.getClass().getMethods();
        for (int i = 0; i < m.length; i++) {
            if (("get" + name).equalsIgnoreCase(m[i].getName())) {
                return m[i].invoke(obj);
            }
        }
        return null;
    }

    /**
     * 字段过滤
     *
     * @param fields
     * @return
     */
    private List<Field> filterField(Field[] fields) {
        fieldList = new ArrayList<>();
        for (Field field : fields)
            if (field.isAnnotationPresent(ExcelCell.class))
                fieldList.add(field);
        return fieldList;
    }
}

自定义注解类

package com.buba.annotation;

import com.buba.util.MyExcelCellType;

import java.lang.annotation.*;

/**
 * Excel单元格注解
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelCell {

    String value() default "";

    // 用户描述该字段的类型
    MyExcelCellType type() default MyExcelCellType.STRING;
}

自定义枚举类

package com.buba.util;

public enum MyExcelCellType {
    INTEGER, DOUBLE, BIG_DECIMAL, STRING;
}