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.");
   
 }

/**
 * 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");
   }
   
 }