ExcelUtil通用的excel处理类

       经过收集整理将excel操作写成util类，通过反射方式，进行导入时候的实例创建，同事还在里面加入了标题的映射注解，导出的时候可以直接将第一行填入映射的注解信息，导入的时候要求第一行必须是属性名，依赖poi，请自行引入依赖

package com.xxx.utils;

import lombok.extern.slf4j.Slf4j;

import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * <B>文件名称：</B>ExcelUtil.java<BR>
 * <B>文件描述：</B><BR>
 *
 * <B>版权声明：</B>(C)2014-2015<BR>
 * <B>公司部门：</B><BR>
 * <B>创建时间：</B>2016年11月23日<BR>
 *
 * @author 刘飞
 */
@Slf4j
public class ExcelUtil {


  /**
   * 读取到excel到List<T>从第一个sheet页的第一行开始读取.
   *
   * @param file 要读取的文件.
   * @return 返回一个List<T>对象.
   */
  public static <T> List<T> getExcelObj(final File file, Class<T> clazz) throws Exception {
    return getExcelObj(file, 0, 1, clazz);
  }

  /**
   * 读取到excel到List<T>.
   *
   * @param filepath Excel文件路径.
   * @return 返回一个List<T>对象.
   */
  public static <T> List<T> getExcelObj(final String filepath, Class<T> clazz) throws Exception {
    final File file = new File(filepath);
    return getExcelObj(file, 0, 1, clazz);
  }

  /**
   * 读取到excel到List<T>.
   *
   * @param filepath Excel文件路径.
   * @return 返回一个List<T>对象.
   */
  public static <T> List<T> getExcelObj(final String filepath, final int sheetNum, final int rowNnm, Class<T> clazz)
      throws Exception {
    final File file = new File(filepath);
    return getExcelObj(file, sheetNum, rowNnm, clazz);
  }

  /**
   * 读取到excel到List<T>.
   *
   * @param file     要读取的文件.
   * @param sheetNum 读取那个sheet页,从0算起.
   * @param rowNum   从那行开始读取,从0算起.
   * @return 返回一个List<T>对象
   */
  @SuppressWarnings("unchecked")
  public static <T> List<T> getExcelObj(final File file, final int sheetNum, final int rowNum, Class<T> clazz) throws
      Exception {
    if (FilenameUtils.getExtension(file.getName()).equals("xls")) {
      return getExcelObj2003(file, sheetNum, rowNum, clazz);
    } else if (FilenameUtils.getExtension(file.getName()).equals("xlsx")) {
      return getExcelObj2007(file, sheetNum, rowNum, clazz);
    } else {
      return null;
    }
  }


  public static <T> List<T> getExcelObj2003(final File file, final int sheetNum, final int rowNum, Class<T> clazz)
      throws Exception {
    final List<T> list = new ArrayList<>();
    // 传入路径
    final FileInputStream is = new FileInputStream(file);
    final HSSFWorkbook wbs = new HSSFWorkbook(is);
    final HSSFSheet childSheet = wbs.getSheetAt(sheetNum);

    List<String> title = new ArrayList<>();
    if (rowNum != 0) {

      for (int i = 0; i < childSheet.getRow(0).getPhysicalNumberOfCells(); i++) {
        title.add(childSheet.getRow(0).getCell(i).toString());
      }
    }
    for (int j = rowNum; j <= childSheet.getLastRowNum(); j++) {
      //读取行元素
      final HSSFRow row = childSheet.getRow(j);
      if (null != row) {
        Object dto = clazz.newInstance();
        for (int k = 0; k < row.getLastCellNum(); k++) {
          //读取单元格
          final HSSFCell cell = row.getCell(k);

          //进行单元格的类型检查和转换
          decideCellType(dto, cell, clazz, title.get(k));

        }
        list.add((T) dto);
      }
    }
    return list;
  }

  public static <T> List<T> getExcelObj2007(final File file, final int sheetNum, final int rowNum, Class<T> clazz)
      throws Exception {
    List<T> list = new ArrayList<>();
    // 传入路径
    final FileInputStream is = new FileInputStream(file);
    final XSSFWorkbook wbs = new XSSFWorkbook(is);
    final XSSFSheet childSheet = wbs.getSheetAt(sheetNum);

    List<String> title = new ArrayList<>();
    if (rowNum != 0) {

      for (int i = 0; i < childSheet.getRow(0).getPhysicalNumberOfCells(); i++) {
        title.add(childSheet.getRow(0).getCell(i).toString());
      }
    }

    for (int j = rowNum; j <= childSheet.getLastRowNum(); j++) {
      //读取行元素
      XSSFRow row = childSheet.getRow(j);
      if (null != row) {
        Object dto = clazz.newInstance();
        for (int k = 0; k < row.getLastCellNum(); k++) {
          //读取单元格
          final XSSFCell cell = row.getCell(k);

          decideCellType(dto, cell, clazz, title.get(k));

        }
        list.add((T) dto);
      }
    }
    return list;
  }


  /**
   * 这里获取的值是输入正确,但是单元格属性设置错误导致类型错误,需要矫正的值.
   *
   * @return 经过类型矫正的值.
   */
  @SuppressWarnings("unchecked")
  private static Object getRightTypeValue(Object localObject, String fieldName, Class<?> clazz) throws Exception {
    String type = clazz.getDeclaredField(fieldName).getType().getTypeName();
    switch (type) {
      case "java.util.Date":
        if (!(localObject instanceof Date)) {
          if (localObject instanceof String) {
            //按照格式yyyy-MM-dd转换
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            localObject = sdf.parse(localObject.toString());
          } else if (localObject instanceof Double) {
            int day = ((Double) localObject).intValue();
            Calendar calendar = Calendar.getInstance();
            calendar.set(1900, 0, 1);
            calendar.add(Calendar.DAY_OF_YEAR, day);
            localObject = calendar.getTime();
          } else {
            log.error("excel cell date format error");
            throw new Exception("excel日期格式错误");
          }
        }
        break;
      case "java.lang.Double":
        if (!(localObject instanceof Double)) {
          if (localObject instanceof String) {
            localObject = Double.parseDouble(localObject.toString());
          }
        }
        break;
      case "java.lang.Float":
        if (localObject instanceof Double) {
          localObject = ((Double) localObject).floatValue();
        }
        break;
      case "java.lang.Integer":
        if (localObject instanceof Double) {
          localObject = ((Double) localObject).intValue();
        }
        break;
      case "java.lang.Long":
        if (localObject instanceof Double) {
          localObject = ((Double) localObject).longValue();
        }
        break;
      case "java.lang.String":
        if (localObject instanceof Double) {
          java.text.DecimalFormat formatter = new java.text.DecimalFormat("######## ");
          localObject = formatter.format(localObject);
        } else {
          localObject = localObject.toString();
        }
        break;
      default:
        localObject = localObject.toString();
    }

    return localObject;
  }


  /**
   * 检查excel单元格类型，进行转换
   */
  private static void decideCellType(Object dto, final Cell cell, Class<?> clazz, String fieldName) {
    Object localObject = null;
    if (cell != null) {
      // 判断获取类型
      switch (cell.getCellTypeEnum()) {
        case NUMERIC:
          localObject = cell.getNumericCellValue();
          break;
        case STRING:
          localObject = cell.getStringCellValue();
          break;
        case BOOLEAN:
          localObject = new Boolean(cell.getBooleanCellValue());
          break;
        case BLANK:
          localObject = "";
          break;
        case FORMULA:
          int a = (cell.getCellFormula().indexOf("+") + 1) + (cell.getCellFormula().indexOf('/') + 1)
              + (cell.getCellFormula().indexOf('*') + 1)
              + (cell.getCellFormula().indexOf('-') + 1);
          if (a <= 0) {
            localObject = cell.getCellFormula();
          } else if (a > 0) {
            localObject = cell.getNumericCellValue();
          }
          break;
        case ERROR:
          localObject = new Byte(cell.getErrorCellValue());
          break;
        default:
          log.error("excel deal error:未知类型单元格");
          break;
      }
      try {
        BeanUtils.invokeSetMethod(dto, fieldName, getRightTypeValue(localObject, fieldName, clazz));
      } catch (Exception e) {
        log.error("excel operate error:" + e.getMessage());
      }

    }
  }


  /**
   * excel写入本地文件.
   */
  public static <T> Boolean listToFile(final List<T> infoList, final String path, final String fileName) {
    FileUtils.createFolder(path);
    final File file = new File(path + fileName);
    Workbook wb = null;
    if (FilenameUtils.getExtension(file.getName()).equals("xls")) {
      wb = writeToExcel2003(infoList);
    } else if (FilenameUtils.getExtension(file.getName()).equals("xlsx")) {
      wb = writeToExcel2007(infoList);
    }
    // 输出流
    OutputStream os = null;
    try {
      os = new FileOutputStream(file);
      wb.write(os);
    } catch (IOException ex) {
      log.error("excel operate error, msg: {}", ex.getMessage());
      return false;
    } finally {
      // 关闭输出流
      try {
        wb.close();
        os.close();
      } catch (IOException ex) {
        log.error("excel operate error, msg: {}", ex.getMessage());
        return false;
      }
    }
    return true;
  }

  /**
   * 生成EXCEL实体.
   */
  private static <T> HSSFWorkbook writeToExcel2003(final List<T> infoList) {
    final HSSFWorkbook wb = new HSSFWorkbook();
    if (infoList.isEmpty()) {
      return wb;
    }

    final HSSFSheet sheet = wb.createSheet();

    createCell(infoList, sheet);

    return wb;
  }

  private static <T> XSSFWorkbook writeToExcel2007(final List<T> infoList) {
    final XSSFWorkbook wb = new XSSFWorkbook();
    if (infoList.isEmpty()) {
      return wb;
    }

    final XSSFSheet sheet = wb.createSheet();

    createCell(infoList, sheet);

    return wb;
  }

  private static <T> void createCell(List<T> infoList, Sheet sheet) {

    if (infoList == null) {
      return;
    }

    final Object obj = infoList.get(0);
    final Field[] fields = obj.getClass().getDeclaredFields();


    final int rowNum = infoList.size();
    final int cellNum = fields.length;
    Row row = sheet.createRow(0);

    for (int j = 0; j < cellNum; j++) {
      // 只取被注解的字段
      if (fields[j].isAnnotationPresent(ExcelTitle.class)) {
        row.createCell(j).setCellValue(fields[j].getAnnotation(ExcelTitle.class).value());
      }
    }

    for (int i = 0; i < rowNum; i++) {
      row = sheet.createRow(i + 1);
      for (int j = 0; j < cellNum; j++) {
        if (fields[j].isAnnotationPresent(ExcelTitle.class)) {
          final Object value = BeanUtils.invokeGetMethod(infoList.get(i), fields[j].getName());
          if (null != value) {
            row.createCell(j).setCellValue(value.toString());
          }
        }
      }
    }
  }
}

package com.xxx.utils;

import lombok.extern.slf4j.Slf4j;

import java.lang.reflect.Field;
import java.lang.reflect.Method;

/**
 * @author yang.yu
 * @date 2017年6月23日
 */
@Slf4j
public class BeanUtils extends org.springframework.beans.BeanUtils {

  /**
   * 对象拷贝，相同属性并且目标字段值为空才拷贝.
   * @param from    用于赋值的实体类
   * @param to      被赋值的实体类
   */
  public static void copy(final Object from, final Object to) {
    Class<?> clazz1 = null;
    Class<?> clazz2 = null;
    try {
      clazz1 = Class.forName(from.getClass().getName());
      clazz2 = Class.forName(to.getClass().getName());
    } catch (ClassNotFoundException ex) {
      log.error("failed getClass by Object, error: {}", ex.toString());
    }

    final Field[] fields1 = clazz1.getDeclaredFields();
    final Field[] fields2 = clazz2.getDeclaredFields();

    for (final Field f1 : fields1) {
      final Object value = invokeGetMethod(from, f1.getName());
      if (null == value) {
        continue;
      }
      for (final Field f2 : fields2) {
        if (f1.getName().equals(f2.getName())) {

          invokeSetMethod(to, f2.getName(), value);
        }
      }
    }
  }

  /**
   * 反射执行Get方法.
   */
  public static Object invokeGetMethod(final Object clazz, final String fieldName) {
    final StringBuilder sb = new StringBuilder(fieldName);
    sb.setCharAt(0, Character.toUpperCase(sb.charAt(0)));
    final String methodName = sb.toString();
    Method method = null;
    Object value = null;
    try {
      method = clazz.getClass().getDeclaredMethod("get" + methodName);
      value = method.invoke(clazz);
    } catch (Exception ex) {
      log.error("failed invokeGetMethod, error: {}", ex.toString());
    }
    return value;
  }

  /**
   * 反射执行Set方法.
   */
  public static Object invokeSetMethod(final Object clazz, final String fieldName, final Object... args) {
    final StringBuilder sb = new StringBuilder(fieldName);
    sb.setCharAt(0, Character.toUpperCase(sb.charAt(0)));
    final String methodName = sb.toString();
    Method method = null;
    Object value = null;
    try {
      Class<?>[] parameterTypes = new Class[1];
      final Class<?> cl = clazz.getClass();
      final Field field = cl.getDeclaredField(fieldName);
      parameterTypes[0] = field.getType();
      method = cl.getDeclaredMethod("set" + methodName, parameterTypes);
      value = method.invoke(clazz, args);
    } catch (Exception ex) {
      log.error("failed invokeSetMethod, error: {}", StringUtil.getStackTrace(ex));
    }
    return value;
  }
}

package com.xxx.utils;

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

/**
 * @author yang.yu
 * @date 2017年6月29日
 */
@Documented
@Target(ElementType.FIELD)
@Inherited
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelTitle {
  String value() default "";
}