excle导出、导入、下载_jeesite注解@ExcelField

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

介绍：对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<User> 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("<br/>登录名 "+user.getLoginName()+" 已存在; ");
failureNum++;
}
}catch(ConstraintViolationException ex){
failureMsg.append("<br/>登录名 "+user.getLoginName()+" 导入失败：");
List<String> messageList = BeanValidators.extractPropertyAndMessageAsList(ex, ": ");
for (String message : messageList){
failureMsg.append(message+"; ");
failureNum++;
}
}catch (Exception ex) {
failureMsg.append("<br/>登录名 "+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<User> 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 <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.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<String, Object> conMap,Map<String, List<?>> 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<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.");
//
// }}