最新基于注解Excel导入

直接上代码

package com.siwill.util;

import com.google.common.base.Strings;
import com.siwill.domain.enums.ExcelImport;
import org.apache.commons.lang.time.DateUtils;
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 java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by panweifu on 2019/4/12.
 */
public class MyExcelImportUtils<T> {

    //保证线程安全的
    private static final ThreadLocal<MyExcelImportUtils> UTILS_THREAD_LOCAL = new ThreadLocal<>();

    public static final MyExcelImportUtils initialization() {
        MyExcelImportUtils excelUtils = UTILS_THREAD_LOCAL.get();
        if (excelUtils == null) {
            excelUtils = new MyExcelImportUtils();
            UTILS_THREAD_LOCAL.set(excelUtils);
        }
        return excelUtils;
    }

    public List<T> getExcelData(MultipartFile file, Class<T> clazz) throws Exception {
        Field[] declaredFields = clazz.getDeclaredFields();
        List<T> result = new LinkedList<>();
        Map<String, Field> columnNames = new LinkedHashMap<>();
        ExcelImport exportFieldAnnotation;
        String columnName;
        for (Field declaredField : declaredFields) {
            if (declaredField.isAnnotationPresent(ExcelImport.class)) {
                exportFieldAnnotation = declaredField.getAnnotation(ExcelImport.class);
                columnName = exportFieldAnnotation.columnName();
                if (!Strings.isNullOrEmpty(columnName)) {
                    columnNames.put(columnName, declaredField);
                }
            }
        }

        Workbook book = getWorkBook(file);
        int sheetNums = book.getNumberOfSheets();
        Sheet eachSheet;
        int lastRowNum;
        Row row;
        Cell cell;
        short lastCellNum;
        String stringCellValue;
        Field currentFiled;
        Class<?> type;
        T t;
        Map<Integer, Field> fieldHashMap = new HashMap<>();

        String dateFormat;
        for (int i = 0; i < sheetNums; i++) {
            //遍历每一个sheet
            eachSheet = book.getSheetAt(i);
            lastRowNum = eachSheet.getLastRowNum();
            //遍历每一行
            for (int j = 0; j <= lastRowNum; j++) {
                row = eachSheet.getRow(j);
                lastCellNum = row.getLastCellNum();

                if (i == 0 && j == 0) {
                    //第一个sheet的第一行数据
                    for (int k = 0; k < lastCellNum; k++) {
                        row.getCell(k).setCellType(CellType.STRING);
                        stringCellValue = row.getCell(k).getStringCellValue();
                        if (columnNames.get(stringCellValue) != null) {
                            fieldHashMap.put(k, columnNames.get(stringCellValue));
                        }
                    }
                    continue;
                }
                //判断是否为空行
                if (row == null || row.getCell(0) == null || Strings.isNullOrEmpty(row.getCell(0).getStringCellValue())) {
                    continue;
                }
                t = clazz.newInstance();
                for (int h = 0; h < lastCellNum; h++) {

                    currentFiled = fieldHashMap.get(h);
                    if (currentFiled != null) {
                        type = currentFiled.getType();
                        currentFiled.setAccessible(true);

                    } else {
                        continue;
                    }
                    cell = row.getCell(h);
                    if (null == cell) {
                        stringCellValue = null;
                    } else {
                        cell.setCellType(CellType.STRING);
                        stringCellValue = cell.getStringCellValue();
                    }
                    if (Strings.isNullOrEmpty(stringCellValue)) {
                        continue;
                    }
                    if (type.getName().contains("String")) {
                        currentFiled.set(t, stringCellValue);
                    } else if (type.getName().contains("Date")) {
                        if (Strings.isNullOrEmpty(stringCellValue)) {
                            currentFiled.set(t, null);
                        } else {
                            dateFormat = currentFiled.getAnnotation(ExcelImport.class).dateFormat();

                            //日期格式化
                            Calendar calendar = new GregorianCalendar(1900, 0, 0);
                            Date d = calendar.getTime();

                            Date date = DateUtils.addDays(d, Integer.valueOf(stringCellValue));
                            currentFiled.set(t, new SimpleDateFormat(dateFormat).parse(date.toInstant().toString()));
                        }

                    } else if (type.getName().contains("Integer")) {
                        if (Strings.isNullOrEmpty(stringCellValue)) {
                            currentFiled.set(t, null);
                        } else {
                            currentFiled.set(t, Integer.valueOf(stringCellValue));
                        }
                    } else if (type.getName().contains("Double")) {
                        if (Strings.isNullOrEmpty(stringCellValue)) {
                            currentFiled.set(t, null);
                        } else {
                            currentFiled.set(t, Double.valueOf(stringCellValue));
                        }
                    } else if(type.getName().contains("Long")){
                        if (Strings.isNullOrEmpty(stringCellValue)) {
                            currentFiled.set(t, null);
                        } else {
                            currentFiled.set(t, Long.valueOf(stringCellValue));
                        }
                    }else {
                        currentFiled.set(t, stringCellValue);
                    }

                }
                result.add(t);
            }

        }
        return result;
    }

    public Workbook getWorkBook(MultipartFile file) throws Exception {
        String originalFilename = file.getOriginalFilename();
        String suffix = originalFilename.substring(originalFilename.indexOf(".") + 1);
        Workbook workbook;

        InputStream is = file.getInputStream();
        if ("xls".equals(suffix)) {
            workbook = new HSSFWorkbook(is);
        } else {
            workbook = new XSSFWorkbook(is);
        }
        return workbook;
    }
}



//注解类
package com.siwill.domain.enums;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * Created by panweifu on 2019/4/12.
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelImport {
    /**
     * excel列名称
     *
     * @return
     */
    String columnName();

    /**
     * 日期格式 默认 yyyy-MM-dd HH:mm:ss
     *
     * @return
     */
    String dateFormat() default "yyyy-MM-dd HH:mm:ss";
}