Excel相关处理

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.suzhuichang.common.core.text.Convert;
import com.suzhuichang.common.exception.CustomException;
import com.suzhuichang.common.utils.DateUtils;
import com.suzhuichang.common.utils.StringUtils;
import com.suzhuichang.common.utils.reflect.ReflectUtils;
import com.suzhuichang.framework.aspectj.lang.annotation.Excel;
import com.suzhuichang.framework.aspectj.lang.annotation.Excel.ColumnType;
import com.suzhuichang.framework.aspectj.lang.annotation.Excel.Type;
import com.suzhuichang.framework.aspectj.lang.annotation.Excels;
import com.suzhuichang.framework.config.RuoYiConfig;
import com.suzhuichang.framework.web.domain.AjaxResult;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
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 java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 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("文件"+sheetName+"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 != null)) {
          String value = this.getCellValue(heard, i).toString().replaceAll("\n", "");
          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());
          if (column != null) {
            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