1.自定义注解
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Excel {
/**
* 列名
* @return
*/
public String name () default "";
/**
* 数字类型格式
* @return
*/
public String numberFormat() default "0.00";
/**
* 日期格式化
* @return
*/
public String dateFormat() default "yyyy-MM-dd HH:mm:ss";
}
2.EXCEL生成
public static <T> XSSFWorkbook writeExcle(List<T> data, Class T) throws IllegalAccessException {
//获取所有属性
Field[] declaredFields = T.getDeclaredFields();
//先遍历获取列名
List<String> heads = new ArrayList<String>();
for (Field field : declaredFields) {
Excel annotation = field.getAnnotation(Excel.class);
heads.add(annotation.name());
}
//申明一个工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//生成一个表格
XSSFSheet sheet = workbook.createSheet();
// 产生表格标题行
XSSFRow row = sheet.createRow(0);
//新建表格头信息
for (short i = 0; i < heads.size(); i++) {
XSSFCell cell = row.createCell(i);
XSSFRichTextString text = new XSSFRichTextString(heads.get(i));
cell.setCellValue(text);
}
//设置表格体
Iterator<T> iterator = data.iterator();
Integer index = 0;
//遍历集合信息
while (iterator.hasNext()) {
index++;
XSSFRow rowNext = sheet.createRow(index);
T u = iterator.next();
//获取属性
Field[] fields = u.getClass().getDeclaredFields();
//遍历属性excel插入值
for (short i = 0; i < fields.length; i++) {
XSSFCell cell = rowNext.createCell(i);
fields[i].setAccessible(true);
Object o = fields[i].get(u);
if (null == o) {
cell.setCellValue(new XSSFRichTextString(""));
continue;
}
//判断类型
XSSFRichTextString text;
if (o instanceof Number) {
Excel annotation = fields[i].getAnnotation(Excel.class);
text = new XSSFRichTextString(new DecimalFormat(annotation.numberFormat()).format(o));
} else if (o instanceof Date) {
Excel annotation = fields[i].getAnnotation(Excel.class);
text = new XSSFRichTextString(new SimpleDateFormat(annotation.dateFormat()).format(o));
} else {
text = new XSSFRichTextString(String.valueOf(o));
}
cell.setCellValue(text);
}
}
return workbook;
}
关于一些其他的字段类型,或者特殊的转化格式可以用注解进行扩展。
3.流输出
/**
* 写出excel
* @param list
* @param name
* @param response
*/
public static void getExportedFile(List<User> list, String name, HttpServletResponse response) throws IOException, IllegalAccessException {
XSSFWorkbook workbook = EasyExcelUtil.writeExcle(list, User.class);
BufferedOutputStream fos = null;
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment;filename=" + new String( name.getBytes("UTF-8"), "ISO8859-1" ));
try {
fos = new BufferedOutputStream(response.getOutputStream());
workbook.write(fos);
} finally {
if(null != fos){
fos.close();
}
}
}