• 介绍：对Apache POI 3.9的简单封装，实现Excel的导出导入功能。使用Annotation定义导出导入字段。
• 优点：1.简单易用，支持大数量导出，配置简单，代码量少。

2.支持Excel 2003、2007、2010（xls、xlsx）格式。

3.支持简单格式设置，对齐方式，排序等

4.可导出字典类型数据，自定义数据字段类型（例如：部门关联对象，部门名称与部门编号互转）。

5.无需建立导入模板，系统自动生成。

• 缺点：1.格式单一，无法导出格式比较复杂的表格。

2.不能使用模板进行导入，导出。

• pom.xml添加依赖

  <dependency>  
      <groupId>org.apache.poi</groupId>  
      <artifactId>poi</artifactId>  
      <version>3.9</version>  
  </dependency>  
  <dependency>  
      <groupId>org.apache.poi</groupId>  
      <artifactId>poi-ooxml</artifactId>  
      <version>3.9</version>  
  </dependency>  
  <dependency>  
      <groupId>org.apache.commons</groupId>  
      <artifactId>commons-lang3</artifactId>  
      <version>3.3.2</version>  
  </dependency>  
  <dependency>  
      <groupId>com.google.collections</groupId>  
      <artifactId>google-collections</artifactId>  
      <version>1.0</version>  
  </dependency>

一.实例

1.导出实例

/**
*
* @author yuzj
* @date 2017年3月3日 下午2:59:05
* @description 数据导出
* @param childBaseinfo
* @param request
* @param response
* @param redirectAttributes
* @return
* @throws ParseException
*
*/
@RequestMapping(value = "export", method = RequestMethod.POST)
public String exportFile(ChildBaseinfo childBaseinfo,
HttpServletRequest request, HttpServletResponse response,
RedirectAttributes redirectAttributes,
@RequestParam String beginTime, @RequestParam String endTime,
@RequestParam String birthbeginTime,
@RequestParam String birthendTime) throws ParseException {
// 判断出生日期的格式
if (StringUtils.isNoneBlank(birthbeginTime)) {
Date birthbegin = childBaseinfoService.convert(birthbeginTime);
Date birthend = childBaseinfoService.convert(birthendTime);
if (birthbegin == null || birthend == null) {
addMessage(redirectAttributes, "你输入的出生日期格式不正确");
return "redirect:" + Global.getAdminPath() + "/child_baseinfo/documentStatistics/?repage";
}
Date birthd[] = childVaccinaterecordService.date(birthbegin,birthend);
if (birthd != null) {
childBaseinfo.setBirthbeginTime(birthd[0]);
childBaseinfo.setBirthendTime(birthd[1]);
} else {
addMessage(redirectAttributes, "你输入的出生日期起时间晚于止时间");
return "redirect:" + Global.getAdminPath() + "/child_baseinfo/documentStatistics/?repage";
}
}
// 起止时间
if (StringUtils.isNoneBlank(beginTime)) {
Date begin = childBaseinfoService.convert(beginTime);
Date end = childBaseinfoService.convert(endTime);
Date d[] = childVaccinaterecordService.date(begin, end);
if (d != null) {
childBaseinfo.setBeginTime(d[0]);
childBaseinfo.setEndTime(d[1]);
}
}

    if (StringUtils.isNoneBlank(childBaseinfo.getOfficeinfo())   && "1".equals(childBaseinfo.getOfficeinfo())) {  
        childBaseinfo.setOfficeinfo(OfficeService.getFirstOffice() .getCode());  
    } else {  
        childBaseinfo.setOfficeinfo("");  
    }

    Page<ChildBaseinfo> page = new Page<ChildBaseinfo>();  
    // 根据名字和时间排序  
    page.setOrderBy("A.createdate ASC");  
    page = childBaseinfoService.findPage(page, childBaseinfo);  
    childBaseinfo.setPage(page);  
    List<ChildBaseinfo> list = childBaseinfoService.findList\_exp(childBaseinfo);  
    if (list.size() > 0) {  
        for (ChildBaseinfo childBaseinfo2 : list) {  
            // 转换地址信息  
            childBaseinfo2 = childBaseinfoService.updateAddr(childBaseinfo2);  
        }  
    }  
    try {  
        String fileName = "档案数据" + DateUtils.getDate("yyyyMMddHHmmss")+ ".xlsx";  
        new ExportExcel("档案数据", ChildBaseinfo.class).setDataList(list).write(response, fileName).dispose();  
        return null;  
    } catch (Exception e) {  
        addMessage(redirectAttributes, "导出档案失败！失败信息：" + e.getMessage());  
    }  
    return "redirect:" + Global.getAdminPath()+ "/child\_baseinfo/childBaseinfo/?repage";  
}

2.导入实例

public String importFile(MultipartFile file) {
try {
int successNum = 0;
int failureNum = 0;
StringBuilder failureMsg = new StringBuilder();
// 创建导入Excel对象
ImportExcel ei = new ImportExcel(file, 1, 0);
// 获取传入Excel文件的数据，根据传入参数类型，自动转换为对象
List list = ei.getDataList(User.class);
// 遍历数据，保存数据
for (User user : list){
try{
if ("true".equals(checkLoginName("", user.getLoginName()))){
user.setPassword(SystemService.entryptPassword("123456"));
BeanValidators.validateWithException(validator, user);
systemService.saveUser(user);
successNum++;
}else{
failureMsg.append("
登录名 "+user.getLoginName()+" 已存在; ");
failureNum++;
}
}catch(ConstraintViolationException ex){
failureMsg.append("
登录名 "+user.getLoginName()+" 导入失败：");
List messageList = BeanValidators.extractPropertyAndMessageAsList(ex, ": ");
for (String message : messageList){
failureMsg.append(message+"; ");
failureNum++;
}
}catch (Exception ex) {
failureMsg.append("
登录名 "+user.getLoginName()+" 导入失败："+ex.getMessage());
}
}
if (failureNum>0){
failureMsg.insert(0, "，失败 "+failureNum+" 条用户，导入信息如下：");
}
addFlashMessage("已成功导入 "+successNum+" 条用户"+failureMsg);
} catch (Exception e) {
addFlashMessage("导入用户失败！失败信息："+e.getMessage());
}
return "redirect:"+BaseController.ADMIN_PATH+"/sys/user/?repage";
}

3.模板下载

public String importFileTemplate() {
try {
String fileName = "用户数据导入模板.xlsx";
List list = Lists.newArrayList(); list.add(UserUtils.getUser(true));
// 第三个参数设置为“2”表示输出为导入模板（1:导出数据；2：导入模板）
new ExportExcel("用户数据", User.class, 2).setDataList(list).write(response, fileName).dispose();
return null;
} catch (Exception e) {
addFlashMessage("导出用户失败！失败信息："+e.getMessage());
}
return "redirect:"+BaseController.ADMIN_PATH+"/sys/user/?repage";
}

二、.工具类ExportExcel.java

/**
* Copyright © 2012-2014 JeeSite 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.SimpleDateFormat;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.Cookie;
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.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();

/\*\*  
 \* @author QianHW  
 \* @param sheetName  
 \* @return   根据sheet名称获取sheet  
 \*/  
public Sheet getSheet(String sheetName){  
    Sheet sheet = wb.getSheet(sheetName);  
    return sheet;  
}

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

public ExportExcel(String title, Class<?> cls, int type){  
    this("",title,cls,type,0);  
}

public ExportExcel(String\[\] sheetnames,String\[\] titles, List<Class<?>> cls){  
    this("",titles\[0\],cls.get(0),1,0);  
}

/\*\*  
 \* 构造函数  导出单个sheet,带筛选条件  
 \* @author QianHW  
 \* @date 2018年7月19日 下午7:03:48  
 \* @param title  
 \* @param cls 实体对象，通过annotation.ExportField获取标题  
 \* @param conMap 筛选条件  
 \*/  
public ExportExcel(String title, Class<?> cls,Map<String, Object> conMap){  
    this("",title,cls,conMap,1);  
}

// /**
// * @author QianHW
// * @date 2018年10月18日
// * @param title
// * @param cls
// * @param conMap
// * @param dataList
// */
// public ExportExcel(String title,Class cls,Map conMap,Map> dataList){
//
// }

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

/\*\*  
 \* 构造函数  导出单个sheet,带筛选条件  
 \* @author QianHW  
 \* @date 2018年7月19日 下午7:03:48  
 \* @param title 表格标题，传“空值”，表示无标题  
 \* @param cls 实体对象，通过annotation.ExportField获取标题  
 \* @param conMap 筛选条件  
 \* @param type 导出类型（1:导出数据；2：导出模板）  
 \* @param groups 导入分组  
 \*/  
public ExportExcel(String sheetname, String title, Class<?> cls,Map<String, Object> conMap, 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(sheetname, title, headerList,conMap);  
}

/\*\*  
 \* 构造函数  导出单个sheet  
 \* @param title 表格标题，传“空值”，表示无标题  
 \* @param cls 实体对象，通过annotation.ExportField获取标题  
 \* @param type 导出类型（1:导出数据；2：导出模板）  
 \* @param groups 导入分组  
 \* @param sht  第几个工作  sheet  
 \*/  
public ExportExcel(String sheetname, String title, Class<?> cls, int type,int sht, 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(sheetname, title, headerList,sht);  
}

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

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

/\*\*  
 \* 初始化函数  导出单个sheet,带筛选条件  
 \* @author QianHW  
 \* @date 2018年7月19日 下午7:03:48  
 \* @param sheet 工作表名称  
 \* @param title 表格标题，传“空值”，表示无标题  
 \* @param headerList 表头列表  
 \* @param conMap 筛选条件  
 \*/  
private void initialize(String sheetname, String title, List<String> headerList,Map<String, Object> conMap) {  
    this.wb = new SXSSFWorkbook(500);  
    this.sheet = wb.createSheet(StringUtils.isBlank(sheetname)?"Export":sheetname);  
    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 ScreeCondition  
    if(conMap.size() != 0){  
        String condiStr = "";  
        for(String key : conMap.keySet()){  
            condiStr += key + ":" + conMap.get(key).toString() + ";  ";  
        }  
        condiStr = condiStr + "导出时间"+ ":" + DateUtils.getDate();  
        Row conditionRow = sheet.createRow(rownum++);  
        conditionRow.setHeightInPoints(16);  
        Cell condiCell = conditionRow.createCell(0);  
        condiCell.setCellStyle(styles.get("condition"));  
        condiCell.setCellValue(condiStr);  
        sheet.addMergedRegion(new CellRangeAddress(conditionRow.getRowNum(),  
                conditionRow.getRowNum(), conditionRow.getRowNum()-1, 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.");  
}

/\*\*  
 \* 初始化函数   导出单个sheet  
 \* @param sheet 工作表名称  
 \* @param title 表格标题，传“空值”，表示无标题  
 \* @param headerList 表头列表  
 \* @param sht 第几个工作  sheet  
 \*/  
private void initialize(String sheetname, String title, List<String> headerList,int sht) {  
    this.wb = new SXSSFWorkbook(500);  
    this.sheet = wb.createSheet();  
    this.wb.setSheetName(sht,StringUtils.isBlank(sheetname)?"Export":sheetname);  
    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();  
    CellStyle style2 = 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);

    style2.setAlignment(CellStyle.ALIGN\_LEFT);  
    style2.setVerticalAlignment(CellStyle.VERTICAL\_CENTER);  
    Font condiFont = wb.createFont();  
    condiFont.setFontName("Arial");  
    condiFont.setFontHeightInPoints((short) 10);  

// condiFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style2.setFont(condiFont);
styles.put("condition", style2);

    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) {  
            //地址信息  
            /\*if (val.toString().split("\\\\s").length > 1){  
                cell.setCellValue(ChildBaseinfoService.updateAddr(val.toString()));  
            }else{\*/  
                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((Double) 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((Date) val);\*/  
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
            cell.setCellValue(sdf.format(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();  
        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();  
    Cookie status = new Cookie("expupdateStatus","success");  
    status.setPath("/vaccinate");  
    status.setMaxAge(10);  
    response.addCookie(status);  
    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 headerList = Lists.newArrayList();
// for (int i = 1; i <= 10; i++) { // headerList.add("表头"+i); // } // // List dataRowList = Lists.newArrayList();
// for (int i = 1; i <= headerList.size(); i++) { // dataRowList.add("数据"+i); // } // // List> 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.");
//
// }

}

2.工具类ImportExcel.java

/**
* Copyright © 2012-2014 JeeSite 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.text.SimpleDateFormat;
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();  
                 //处理自定义日期格式  

// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
// double value = cell.getNumericCellValue();
// Date date = org.apache.poi.ss.usermodel.DateUtil
// .getJavaDate(value);
// val = sdf.format(date);
}else if (cell.getCellType() == Cell.CELL_TYPE_STRING){
val = cell.getStringCellValue();
if(val != null && val instanceof String){
val = ((String)val).trim();
}
}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");
// }
//
// }

}

3.实体类注解

@ExcelField(align = 2, title = "接种单位名称", sort = 140)