java实现文件导入导出

文件导入导出必须代码

ExportExcel.java

/**
 * Copyright © 2012-2014 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
 */
package com.thinkgem.jeesite.common.utils.excel;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
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.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.google.common.collect.Lists;
import com.thinkgem.jeesite.common.utils.DateUtils;
import com.thinkgem.jeesite.common.utils.Encodes;
import com.thinkgem.jeesite.common.utils.Reflections;
import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;
import com.thinkgem.jeesite.modules.sys.utils.DictUtils;

/**
 * 导出Excel文件（导出“XLSX”格式，支持大数据量导出  @see org.apache.poi.ss.SpreadsheetVersion）
 * @author ThinkGem
 * @version 2013-04-21
 */
public class ExportExcel {

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

  /**
   * 工作薄对象
   */
  private SXSSFWorkbook wb;

  /**
   * 工作表对象
   */
  private Sheet sheet;

  /**
   * 样式列表
   */
  private Map<String, CellStyle> styles;

  /**
   * 当前行号
   */
  private int rownum;

  /**
   * 注解列表（Object[]{ ExcelField, Field/Method }）
   */
  List<Object[]> annotationList = Lists.newArrayList();

  /**
   * 字段map
   */
  LinkedHashMap<String,String> fieldMap;

  HashMap<String, String> dictTypes;

  /**
   * 构造函数
   * @param title 表格标题，传“空值”，表示无标题
   * @param cls 实体对象，通过annotation.ExportField获取标题
   */
  public ExportExcel(String title, Class<?> cls){
    this(title, cls, 1);
  }

  /**
   * 构造函数
   * @param title 表格标题，传“空值”，表示无标题
   * @param fieldMap，获取要导出的字段和字段标题
   */
  public ExportExcel(String title, LinkedHashMap<String,String> fieldMap){
    this.fieldMap = fieldMap;
    dictTypes = new HashMap<String,String>();
    // Initialize
    int colunm = 0;
    List<String> headerList = Lists.newArrayList();
    for (String key : fieldMap.keySet()){
      String t = fieldMap.get(key);
      HashMap<String, String> map = com.thinkgem.jeesite.common.utils.StringUtils.toMap(t, ";", "=", false);
      if(map.get("name") != null){
        t = map.get("name");
      }

      if(map.get("dictType") != null){
        dictTypes.put(""+(colunm), map.get("dictType"));
      }
      colunm++;
      headerList.add(t);
    }
    initialize(title, headerList);
  }

  /**
   * 构造函数
   * @param title 表格标题，传“空值”，表示无标题
   * @param cls 实体对象，通过annotation.ExportField获取标题
   * @param type 导出类型（1:导出数据；2：导出模板）
   * @param groups 导入分组
   */
  public ExportExcel(String title, Class<?> cls, int type, int... groups){
    // Get annotation field
    Field[] fs = cls.getDeclaredFields();
    for (Field f : fs){
      ExcelField ef = f.getAnnotation(ExcelField.class);
      if (ef != null && (ef.type()==0 || ef.type()==type)){
        if (groups!=null && groups.length>0){
          boolean inGroup = false;
          for (int g : groups){
            if (inGroup){
              break;
            }
            for (int efg : ef.groups()){
              if (g == efg){
                inGroup = true;
                annotationList.add(new Object[]{ef, f});
                break;
              }
            }
          }
        }else{
          annotationList.add(new Object[]{ef, f});
        }
      }
    }
    // Get annotation method
    Method[] ms = cls.getDeclaredMethods();
    for (Method m : ms){
      ExcelField ef = m.getAnnotation(ExcelField.class);
      if (ef != null && (ef.type()==0 || ef.type()==type)){
        if (groups!=null && groups.length>0){
          boolean inGroup = false;
          for (int g : groups){
            if (inGroup){
              break;
            }
            for (int efg : ef.groups()){
              if (g == efg){
                inGroup = true;
                annotationList.add(new Object[]{ef, m});
                break;
              }
            }
          }
        }else{
          annotationList.add(new Object[]{ef, m});
        }
      }
    }
    // Field sorting
    Collections.sort(annotationList, new Comparator<Object[]>() {
      public int compare(Object[] o1, Object[] o2) {
        return new Integer(((ExcelField)o1[0]).sort()).compareTo(
            new Integer(((ExcelField)o2[0]).sort()));
      };
    });
    // Initialize
    List<String> headerList = Lists.newArrayList();
    for (Object[] os : annotationList){
      String t = ((ExcelField)os[0]).title();
      // 如果是导出，则去掉注释
      if (type==1){
        String[] ss = StringUtils.split(t, "**", 2);
        if (ss.length==2){
          t = ss[0];
        }
      }
      headerList.add(t);
    }
    initialize(title, headerList);
  }

  /**
   * 构造函数
   * @param title 表格标题，传“空值”，表示无标题
   * @param headers 表头数组
   */
  public ExportExcel(String title, String[] headers) {
    initialize(title, Lists.newArrayList(headers));
  }

  /**
   * 构造函数
   * @param title 表格标题，传“空值”，表示无标题
   * @param headerList 表头列表
   */
  public ExportExcel(String title, List<String> headerList) {
    initialize(title, headerList);
  }

  /**
   * 初始化函数
   * @param title 表格标题，传“空值”，表示无标题
   * @param headerList 表头列表
   */
  private void initialize(String title, List<String> headerList) {
    this.wb = new SXSSFWorkbook(500);
    this.sheet = wb.createSheet("Export");
    this.styles = createStyles(wb);
    // Create title
    if (StringUtils.isNotBlank(title)){
      Row titleRow = sheet.createRow(rownum++);
      titleRow.setHeightInPoints(30);
      Cell titleCell = titleRow.createCell(0);
      titleCell.setCellStyle(styles.get("title"));
      titleCell.setCellValue(title);
      sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
          titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));
    }
    // Create header
    if (headerList == null){
      throw new RuntimeException("headerList not null!");
    }
    Row headerRow = sheet.createRow(rownum++);
    headerRow.setHeightInPoints(16);
    for (int i = 0; i < headerList.size(); i++) {
      Cell cell = headerRow.createCell(i);
      cell.setCellStyle(styles.get("header"));
      String[] ss = StringUtils.split(headerList.get(i), "**", 2);
      if (ss.length==2){
        cell.setCellValue(ss[0]);
        Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
            new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
        comment.setString(new XSSFRichTextString(ss[1]));
        cell.setCellComment(comment);
      }else{
        cell.setCellValue(headerList.get(i));
      }
      sheet.autoSizeColumn(i);
    }
    for (int i = 0; i < headerList.size(); i++) {
      int colWidth = sheet.getColumnWidth(i)*2;
      sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
    }
    log.debug("Initialize success.");
  }

  /**
   * 创建表格样式
   * @param wb 工作薄对象
   * @return 样式列表
   */
  private Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

    CellStyle style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    Font titleFont = wb.createFont();
    titleFont.setFontName("Arial");
    titleFont.setFontHeightInPoints((short) 16);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style.setFont(titleFont);
    styles.put("title", style);

    style = wb.createCellStyle();
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderBottom(CellStyle.BORDER_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(CellStyle.ALIGN_LEFT);
    styles.put("data1", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(CellStyle.ALIGN_CENTER);
    styles.put("data2", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    styles.put("data3", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
//   style.setWrapText(true);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    Font headerFont = wb.createFont();
    headerFont.setFontName("Arial");
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    style.setFont(headerFont);
    styles.put("header", style);

    return styles;
  }

  /**
   * 添加一行
   * @return 行对象
   */
  public Row addRow(){
    return sheet.createRow(rownum++);
  }


  /**
   * 添加一个单元格
   * @param row 添加的行
   * @param column 添加列号
   * @param val 添加值
   * @return 单元格对象
   */
  public Cell addCell(Row row, int column, Object val){
    return this.addCell(row, column, val, 0, Class.class);
  }

  /**
   * 添加一个单元格
   * @param row 添加的行
   * @param column 添加列号
   * @param val 添加值
   * @param align 对齐方式（1：靠左；2：居中；3：靠右）
   * @return 单元格对象
   */
  public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType){

    Cell cell = row.createCell(column);
    CellStyle style = styles.get("data"+(align>=1&&align<=3?align:""));
    try {
      if (val == null){
        cell.setCellValue("");
      } else if (val instanceof String) {
        cell.setCellValue((String) val);
      } else if (val instanceof Integer) {
        cell.setCellValue((Integer) val);
      } else if (val instanceof Long) {
        cell.setCellValue((Long) val);
      } else if (val instanceof Double) {
        cell.setCellValue(new DecimalFormat(".#####").format(val));
      } else if (val instanceof Float) {
        cell.setCellValue((Float) val);
      } else if (val instanceof Date) {
        DataFormat format = wb.createDataFormat();
        style.setDataFormat(format.getFormat("yyyy-MM-dd"));
        cell.setCellValue(DateUtils.formatDateTime((Date)val));
      } else {
        if (fieldType != Class.class){
          cell.setCellValue((String)fieldType.getMethod("setValue", Object.class).invoke(null, val));
        }else{
          cell.setCellValue((String)Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
            "fieldtype."+val.getClass().getSimpleName()+"Type")).getMethod("setValue", Object.class).invoke(null, val));
        }
      }
    } catch (Exception ex) {
      log.info("Set cell value ["+row.getRowNum()+","+column+"] error: " + ex.toString());
      cell.setCellValue(val.toString());
    }
    cell.setCellStyle(style);
    return cell;
  }
  /**
   * 添加数据（通过annotation.ExportField添加数据）
   * @return list 数据列表
   */
  public <E> ExportExcel setDataList(List<E> list){
    for (E e : list){
      int colunm = 0;
      Row row = this.addRow();
      StringBuilder sb = new StringBuilder();

      if(e instanceof Map){
        @SuppressWarnings("unchecked")
        Map<String,Object> map = (Map<String,Object>)e;
        for(String key : fieldMap.keySet()){
          Object value = map.get(key);
          String columnDictType = dictTypes.get(colunm+"");
          if (StringUtils.isNotBlank(columnDictType)){
            value = DictUtils.getDictLabel(value==null?"":value.toString(), columnDictType, "");
          }
          this.addCell(row, colunm++, value == null ? "" : value.toString(), 0, String.class);
          sb.append(value + ", ");
        }

      }
      else{

        for (Object[] os : annotationList){
          ExcelField ef = (ExcelField)os[0];
          Object val = null;
          // Get entity value
          try{
            if (StringUtils.isNotBlank(ef.value())){
              val = Reflections.invokeGetter(e, ef.value());
            }else{
              if (os[1] instanceof Field){
                val = Reflections.invokeGetter(e, ((Field)os[1]).getName());
              }else if (os[1] instanceof Method){
                val = Reflections.invokeMethod(e, ((Method)os[1]).getName(), new Class[] {}, new Object[] {});
              }
            }
            // If is dict, get dict label
            if (StringUtils.isNotBlank(ef.dictType())){
              val = DictUtils.getDictLabel(val==null?"":val.toString(), ef.dictType(), "");
            }
          }catch(Exception ex) {
            // Failure to ignore
            log.info(ex.toString());
            val = "";
          }
          this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
          sb.append(val + ", ");
        }
        log.debug("Write success: ["+row.getRowNum()+"] "+sb.toString());
      }

    }
    return this;
  }

  /**
   * 输出数据流
   * @param os 输出数据流
   */
  public ExportExcel write(OutputStream os) throws IOException{
    wb.write(os);
    return this;
  }

  /**
   * 输出到客户端
   * @param fileName 输出文件名
   */
  public ExportExcel write(HttpServletResponse response, String fileName) throws IOException{
    response.reset();
    response.setContentType("application/octet-stream; charset=utf-8");
    response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName));
    write(response.getOutputStream());
    return this;
  }

  /**
   * 输出到文件
   * @param fileName 输出文件名
   */
  public ExportExcel writeFile(String name) throws FileNotFoundException, IOException{
    FileOutputStream os = new FileOutputStream(name);
    this.write(os);
    return this;
  }

  /**
   * 清理临时文件
   */
  public ExportExcel dispose(){
    wb.dispose();
    return this;
  }
}

导出测试

public static void main(String[] args) throws Throwable {

  List<String> headerList = Lists.newArrayList();
  for (int i = 1; i <= 10; i++) {
    headerList.add("表头"+i);
  }

  List<String> dataRowList = Lists.newArrayList();
  for (int i = 1; i <= headerList.size(); i++) {
    dataRowList.add("数据"+i);
  }

  List<List<String>> dataList = Lists.newArrayList();
  for (int i = 1; i <=1000000; i++) {
    dataList.add(dataRowList);
  }

  ExportExcel ee = new ExportExcel("表格标题", headerList);

  for (int i = 0; i < dataList.size(); i++) {
    Row row = ee.addRow();
    for (int j = 0; j < dataList.get(i).size(); j++) {
      ee.addCell(row, j, dataList.get(i).get(j));
    }
  }

  ee.writeFile("target/export.xlsx");

  ee.dispose();

  log.debug("Export success.");

}

ImportExcel.java

/**
 * Copyright © 2012-2014 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
 */
package com.thinkgem.jeesite.common.utils.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import com.google.common.collect.Lists;
import com.thinkgem.jeesite.common.utils.Reflections;
import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;
import com.thinkgem.jeesite.modules.sys.utils.DictUtils;

/**
 * 导入Excel文件（支持“XLS”和“XLSX”格式）
 * @author ThinkGem
 * @version 2013-03-10
 */
public class ImportExcel {

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

  /**
   * 工作薄对象
   */
  private Workbook wb;

  /**
   * 工作表对象
   */
  private Sheet sheet;

  /**
   * 标题行号
   */
  private int headerNum;

  /**
   * 构造函数
   * @param path 导入文件，读取第一个工作表
   * @param headerNum 标题行号，数据行号=标题行号+1
   * @throws InvalidFormatException
   * @throws IOException
   */
  public ImportExcel(String fileName, int headerNum)
      throws InvalidFormatException, IOException {
    this(new File(fileName), headerNum);
  }

  /**
   * 构造函数
   * @param path 导入文件对象，读取第一个工作表
   * @param headerNum 标题行号，数据行号=标题行号+1
   * @throws InvalidFormatException
   * @throws IOException
   */
  public ImportExcel(File file, int headerNum)
      throws InvalidFormatException, IOException {
    this(file, headerNum, 0);
  }

  /**
   * 构造函数
   * @param path 导入文件
   * @param headerNum 标题行号，数据行号=标题行号+1
   * @param sheetIndex 工作表编号
   * @throws InvalidFormatException
   * @throws IOException
   */
  public ImportExcel(String fileName, int headerNum, int sheetIndex)
      throws InvalidFormatException, IOException {
    this(new File(fileName), headerNum, sheetIndex);
  }

  /**
   * 构造函数
   * @param path 导入文件对象
   * @param headerNum 标题行号，数据行号=标题行号+1
   * @param sheetIndex 工作表编号
   * @throws InvalidFormatException
   * @throws IOException
   */
  public ImportExcel(File file, int headerNum, int sheetIndex)
      throws InvalidFormatException, IOException {
    this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
  }

  /**
   * 构造函数
   * @param file 导入文件对象
   * @param headerNum 标题行号，数据行号=标题行号+1
   * @param sheetIndex 工作表编号
   * @throws InvalidFormatException
   * @throws IOException
   */
  public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex)
      throws InvalidFormatException, IOException {
    this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
  }

  /**
   * 构造函数
   * @param path 导入文件对象
   * @param headerNum 标题行号，数据行号=标题行号+1
   * @param sheetIndex 工作表编号
   * @throws InvalidFormatException
   * @throws IOException
   */
  public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex)
      throws InvalidFormatException, IOException {
    if (StringUtils.isBlank(fileName)){
      throw new RuntimeException("导入文档为空!");
    }else if(fileName.toLowerCase().endsWith("xls")){
      this.wb = new HSSFWorkbook(is);
    }else if(fileName.toLowerCase().endsWith("xlsx")){
      this.wb = new XSSFWorkbook(is);
    }else{
      throw new RuntimeException("文档格式不正确!");
    }
    if (this.wb.getNumberOfSheets()<sheetIndex){
      throw new RuntimeException("文档中没有工作表!");
    }
    this.sheet = this.wb.getSheetAt(sheetIndex);
    this.headerNum = headerNum;
    log.debug("Initialize success.");
  }

  /**
   * 获取行对象
   * @param rownum
   * @return
   */
  public Row getRow(int rownum){
    return this.sheet.getRow(rownum);
  }

  /**
   * 获取数据行号
   * @return
   */
  public int getDataRowNum(){
    return headerNum+1;
  }

  /**
   * 获取最后一个数据行号
   * @return
   */
  public int getLastDataRowNum(){
    return this.sheet.getLastRowNum()+headerNum;
  }

  /**
   * 获取最后一个列号
   * @return
   */
  public int getLastCellNum(){
    return this.getRow(headerNum).getLastCellNum();
  }

  /**
   * 获取单元格值
   * @param row 获取的行
   * @param column 获取单元格列号
   * @return 单元格值
   */
  public Object getCellValue(Row row, int column){
    Object val = "";
    try{
      Cell cell = row.getCell(column);
      if (cell != null){
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
          val = cell.getNumericCellValue();
        }else if (cell.getCellType() == Cell.CELL_TYPE_STRING){
          val = cell.getStringCellValue();
        }else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){
          val = cell.getCellFormula();
        }else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
          val = cell.getBooleanCellValue();
        }else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){
          val = cell.getErrorCellValue();
        }
      }
    }catch (Exception e) {
      return val;
    }
    return val;
  }

  /**
   * 获取导入数据列表
   * @param cls 导入对象类型
   * @param groups 导入分组
   */
  public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException{
    List<Object[]> annotationList = Lists.newArrayList();
    // Get annotation field
    Field[] fs = cls.getDeclaredFields();
    for (Field f : fs){
      ExcelField ef = f.getAnnotation(ExcelField.class);
      if (ef != null && (ef.type()==0 || ef.type()==2)){
        if (groups!=null && groups.length>0){
          boolean inGroup = false;
          for (int g : groups){
            if (inGroup){
              break;
            }
            for (int efg : ef.groups()){
              if (g == efg){
                inGroup = true;
                annotationList.add(new Object[]{ef, f});
                break;
              }
            }
          }
        }else{
          annotationList.add(new Object[]{ef, f});
        }
      }
    }
    // Get annotation method
    Method[] ms = cls.getDeclaredMethods();
    for (Method m : ms){
      ExcelField ef = m.getAnnotation(ExcelField.class);
      if (ef != null && (ef.type()==0 || ef.type()==2)){
        if (groups!=null && groups.length>0){
          boolean inGroup = false;
          for (int g : groups){
            if (inGroup){
              break;
            }
            for (int efg : ef.groups()){
              if (g == efg){
                inGroup = true;
                annotationList.add(new Object[]{ef, m});
                break;
              }
            }
          }
        }else{
          annotationList.add(new Object[]{ef, m});
        }
      }
    }
    // Field sorting
    Collections.sort(annotationList, new Comparator<Object[]>() {
      public int compare(Object[] o1, Object[] o2) {
        return new Integer(((ExcelField)o1[0]).sort()).compareTo(
            new Integer(((ExcelField)o2[0]).sort()));
      };
    });
    //log.debug("Import column count:"+annotationList.size());
    // Get excel data
    List<E> dataList = Lists.newArrayList();
    for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) {
      E e = (E)cls.newInstance();
      int column = 0;
      Row row = this.getRow(i);
      StringBuilder sb = new StringBuilder();
      for (Object[] os : annotationList){
        Object val = this.getCellValue(row, column++);
        if (val != null){
          ExcelField ef = (ExcelField)os[0];
          // If is dict type, get dict value
          if (StringUtils.isNotBlank(ef.dictType())){
            val = DictUtils.getDictValue(val.toString(), ef.dictType(), "");
            //log.debug("Dictionary type value: ["+i+","+colunm+"] " + val);
          }
          // Get param type and type cast
          Class<?> valType = Class.class;
          if (os[1] instanceof Field){
            valType = ((Field)os[1]).getType();
          }else if (os[1] instanceof Method){
            Method method = ((Method)os[1]);
            if ("get".equals(method.getName().substring(0, 3))){
              valType = method.getReturnType();
            }else if("set".equals(method.getName().substring(0, 3))){
              valType = ((Method)os[1]).getParameterTypes()[0];
            }
          }
          //log.debug("Import value type: ["+i+","+column+"] " + valType);
          try {
            if (valType == String.class){
              String s = String.valueOf(val.toString());
              if(StringUtils.endsWith(s, ".0")){
                val = StringUtils.substringBefore(s, ".0");
              }else{
                val = String.valueOf(val.toString());
              }
            }else if (valType == Integer.class){
              val = Double.valueOf(val.toString()).intValue();
            }else if (valType == Long.class){
              val = Double.valueOf(val.toString()).longValue();
            }else if (valType == Double.class){
              val = Double.valueOf(val.toString());
            }else if (valType == Float.class){
              val = Float.valueOf(val.toString());
            }else if (valType == Date.class){
              val = DateUtil.getJavaDate((Double)val);
            }else{
              if (ef.fieldType() != Class.class){
                val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());
              }else{
                val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
                    "fieldtype."+valType.getSimpleName()+"Type")).getMethod("getValue", String.class).invoke(null, val.toString());
              }
            }
          } catch (Exception ex) {
            log.info("Get cell value ["+i+","+column+"] error: " + ex.toString());
            val = null;
          }
          // set entity value
          if (os[1] instanceof Field){
            Reflections.invokeSetter(e, ((Field)os[1]).getName(), val);
          }else if (os[1] instanceof Method){
            String mthodName = ((Method)os[1]).getName();
            if ("get".equals(mthodName.substring(0, 3))){
              mthodName = "set"+StringUtils.substringAfter(mthodName, "get");
            }
            Reflections.invokeMethod(e, mthodName, new Class[] {valType}, new Object[] {val});
          }
        }
        sb.append(val+", ");
      }
      dataList.add(e);
      log.debug("Read success: ["+i+"] "+sb.toString());
    }
    return dataList;
  }

}

导入测试

public static void main(String[] args) throws Throwable {

  ImportExcel ei = new ImportExcel("target/export.xlsx", 1);

  for (int i = ei.getDataRowNum(); i < ei.getLastDataRowNum(); i++) {
    Row row = ei.getRow(i);
    for (int j = 0; j < ei.getLastCellNum(); j++) {
      Object val = ei.getCellValue(row, j);
      System.out.print(val+", ");
    }
    System.out.print("\n");
  }

}