EXCEL文件导出小工具

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();
            }
        }
    }