excel导出工具, 接口已经简化到最优

特点描述: 接口已经简化到最优,  导出一个数据源的list, 只需要传入一个List参数,  即可导出与之对应的excel

结构如下
    -adapter 单元格样式
        -CellSimpleStyleHandlerAdapter   数据单元格样式
        -CellStyleHandlerAdapter   样式接口
        -CellTitleStyleHandlerAdapter   标题单元格样式
    -annotation   注解,描述Excel的表头名称,  在Excel中该字段所处的位置
        -FieldMeta   
    -client
        -ExcelClient   导出excel的util入口
        -ExcelManager   
        -SortableField   
    -handler
        -MetaHandler   获取字段上的注解
    -resolver   真正的创建excel, 根据数据源解析Excel的工作簿，以及表头部分
        -DefaultWorkBookResolver   
        -HSSFWorkbookResolver   



1. adapter 单元格样式
CellStyleHandlerAdapter 样式接口

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * Created by zonor on 17/3/31.
 */
public interface CellStyleHandlerAdapter {

    /**
     * 设置单元格样式
     *
     * @param wb
     * @return
     */
    HSSFCellStyle styleSettings(HSSFWorkbook wb);
}

CellTitleStyleHandlerAdapter   标题单元格样式

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;

/**
 * Created by zonor on 17/3/31.
 */
public class CellTitleStyleHandlerAdapter implements CellStyleHandlerAdapter {

    /**
     * 设置单元格样式
     *
     * @param wb
     * @return
     */
    public HSSFCellStyle styleSettings(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        //设置字体
        HSSFFont font = wb.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }
}

CellSimpleStyleHandlerAdapter   数据单元格样式

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * Created by zonor on 17/3/31.
 */
public class CellSimpleStyleHandlerAdapter implements CellStyleHandlerAdapter {

    /**
     * 设置单元格样式
     *
     * @param wb
     * @return
     */
    public HSSFCellStyle styleSettings(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        return style;
    }
}

2. annotation   
FieldMeta注解, 描述Excel的表头名称,  在Excel中该字段所处的位置

import java.lang.annotation.*;

/**
 * Created by zonor on 17/3/30.
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface FieldMeta {

    /**
     * Excel的表头
     * @return
     */
    String title() default "";

    /**
     * 在Excel中该字段所处的位置
     * @return
     */
    int order() default 0;
}

3.handler 
   MetaHandler   获取字段上的注解

import org.mogujie.iip.commons.excel.annotation.FieldMeta;
import org.mogujie.iip.commons.excel.client.SortableField;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

/**
 * Created by zonor on 17/3/30.
 *
 */
public class MetaHandler<T> {

    private Class<T> entity;

    public MetaHandler(Class<T> entity) {
        this.entity = entity;
        init();
    }

    public List<SortableField> init() {
        List<SortableField> list = new ArrayList<SortableField>();

        if (null != entity) {
            Field[] fields = entity.getDeclaredFields();

            for (Field field : fields) {
                //获取字段上的注解
                FieldMeta fieldMeta = field.getAnnotation(FieldMeta.class);
                if (null != fieldMeta) {
                    SortableField sf = new SortableField(fieldMeta, field);
                    list.add(sf);
                }
            }

            //对设置的字段的元数据中的order进行排序
            Collections.sort(list, new Comparator<SortableField>() {
                public int compare(SortableField o1, SortableField o2) {
                    return o1.getMeta().order() - o2.getMeta().order();
                }
            });
        }

        return list;
    }
}

4.client 
        -ExcelClient   导出excel的util入口
        -ExcelManager   默认属性管理
        -SortableField     注解信息管理

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.mogujie.iip.commons.excel.handler.MetaHandler;
import org.mogujie.iip.commons.excel.resolver.HSSFWorkbookResolver;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.List;

/**
 * Created by zonor on 17/3/30.
 */
public class ExcelClient {

    private ExcelManager manager;

    public ExcelManager getManager() {
        return manager;
    }

    public void setManager(ExcelManager manager) {
        this.manager = manager;
    }

    /**
     * 加载管理器，实例化还是注入都必须要优先调用
     */
    public void init() {
        if (null == this.manager) {
            this.manager = new ExcelManager();
            this.manager.init();
        }
    }

    /**
     * 导出
     *
     * @param fileName
     * @param datasource
     * @param response
     * @param workbookName
     * @param <T>
     * @throws Exception
     */
    public <T> void export(String fileName, List<T> datasource, HttpServletResponse response, String workbookName) throws Exception {
        HSSFWorkbookResolver resolver = this.manager.getResolver();
        MetaHandler<T> metaHandler = new MetaHandler<T>((Class<T>) datasource.get(0).getClass());
        HSSFWorkbook wb = resolver.resolverWorkBook(datasource,
                metaHandler,
                workbookName,
                this.manager.getTitleStyleAdapter(),
                this.manager.getSimpleStyleAdapter());

        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "ISO8859-1") + ".xls");
        OutputStream ouputStream = response.getOutputStream();
        wb.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();
    }

    /**
     * 导出
     *
     * @param fileName
     * @param datasource
     * @param response
     * @param <T>
     * @throws Exception
     */
    public <T> void export(String fileName, List<T> datasource, HttpServletResponse response) throws Exception {
        HSSFWorkbookResolver resolver = this.manager.getResolver();
        MetaHandler<T> metaHandler = new MetaHandler<T>((Class<T>) datasource.get(0).getClass());
        HSSFWorkbook wb = resolver.resolverWorkBook(datasource,
                metaHandler,
                this.manager.getTitleStyleAdapter(),
                this.manager.getSimpleStyleAdapter());

        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "ISO8859-1") + ".xls");
        OutputStream ouputStream = response.getOutputStream();
        wb.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();
    }

    /**
     * 导出
     * @param datasource
     * @param <T>
     * @return
     * @throws Exception
     */
    public <T> HSSFWorkbook export(List<T> datasource) throws Exception {
        HSSFWorkbookResolver resolver = this.manager.getResolver();
        MetaHandler<T> metaHandler = new MetaHandler<T>((Class<T>) datasource.get(0).getClass());
        HSSFWorkbook wb = resolver.resolverWorkBook(datasource,
                metaHandler,
                this.manager.getTitleStyleAdapter(),
                this.manager.getSimpleStyleAdapter());

      return wb;
    }
}

import org.mogujie.iip.commons.excel.adapter.CellSimpleStyleHandlerAdapter;
import org.mogujie.iip.commons.excel.adapter.CellStyleHandlerAdapter;
import org.mogujie.iip.commons.excel.adapter.CellTitleStyleHandlerAdapter;
import org.mogujie.iip.commons.excel.handler.MetaHandler;
import org.mogujie.iip.commons.excel.resolver.DefaultWorkBookResolver;
import org.mogujie.iip.commons.excel.resolver.HSSFWorkbookResolver;

/**
 * Created by zonor on 17/3/30.
 */
public class ExcelManager {

    /**
     * workbook解析器
     */
    private HSSFWorkbookResolver resolver;

    /**
     * 标题样式
     */
    private CellStyleHandlerAdapter titleStyleAdapter;

    /**
     * 数据样式
     */
    private CellStyleHandlerAdapter simpleStyleAdapter;

    public HSSFWorkbookResolver getResolver() {
        return resolver;
    }

    public void setResolver(HSSFWorkbookResolver resolver) {
        this.resolver = resolver;
    }

    public CellStyleHandlerAdapter getTitleStyleAdapter() {
        return titleStyleAdapter;
    }

    public void setTitleStyleAdapter(CellStyleHandlerAdapter titleStyleAdapter) {
        this.titleStyleAdapter = titleStyleAdapter;
    }

    public CellStyleHandlerAdapter getSimpleStyleAdapter() {
        return simpleStyleAdapter;
    }

    public void setSimpleStyleAdapter(CellStyleHandlerAdapter simpleStyleAdapter) {
        this.simpleStyleAdapter = simpleStyleAdapter;
    }

    public void init() {
        this.resolver = new DefaultWorkBookResolver();
        this.titleStyleAdapter = new CellTitleStyleHandlerAdapter();
        this.simpleStyleAdapter = new CellSimpleStyleHandlerAdapter();
    }
}

import org.mogujie.iip.commons.excel.annotation.FieldMeta;

import java.lang.reflect.Field;

/**
 * 字段以及meta信息的帮助类
 * Created by zonor on 17/3/30.
 */
public class SortableField {

    public SortableField() {
        super();
    }

    public SortableField(FieldMeta meta, Field field) {
        super();
        this.meta = meta;
        this.field = field;
        this.name = field.getName();
        this.type = field.getType();
    }

    public SortableField(FieldMeta meta, String name, Class<?> type) {
        super();
        this.meta = meta;
        this.name = name;
        this.type = type;
    }

    private FieldMeta meta;
    private Field field;
    private String name;
    private Class<?> type;

    public FieldMeta getMeta() {
        return meta;
    }

    public void setMeta(FieldMeta meta) {
        this.meta = meta;
    }

    public Field getField() {
        return field;
    }

    public void setField(Field field) {
        this.field = field;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Class<?> getType() {
        return type;
    }

    public void setType(Class<?> type) {
        this.type = type;
    }
}

5. resolver   真正的创建excel, 根据数据源解析Excel的工作簿，以及表头部分
        -DefaultWorkBookResolver   
        -HSSFWorkbookResolver   

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.mogujie.iip.commons.excel.adapter.CellStyleHandlerAdapter;
import org.mogujie.iip.commons.excel.handler.MetaHandler;

import java.util.List;

/**
 * Created by zonor on 17/3/30.
 */
public interface HSSFWorkbookResolver {

    /**
     * 根据数据源解析Excel的工作簿，以及表头部分
     * @param <T>
     * @param dataSource
     * @param handler
     * @param workbookName
     * @param titleStyleAdapter
     *@param simpleStyleAdapter @return
     */
    <T> HSSFWorkbook resolverWorkBook(List<T> dataSource, MetaHandler<T> handler, String workbookName, CellStyleHandlerAdapter titleStyleAdapter, CellStyleHandlerAdapter simpleStyleAdapter) throws Exception;

    /**
     * 根据数据源解析Excel的工作簿，以及表头部分
     * @param <T>
     * @param dataSource
     * @param handler
     * @param titleStyleAdapter
     *@param simpleStyleAdapter @return
     */
    <T> HSSFWorkbook resolverWorkBook(List<T> dataSource, MetaHandler<T> handler, CellStyleHandlerAdapter titleStyleAdapter, CellStyleHandlerAdapter simpleStyleAdapter) throws Exception;
}

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.mogujie.iip.commons.excel.adapter.CellStyleHandlerAdapter;
import org.mogujie.iip.commons.excel.handler.MetaHandler;
import org.mogujie.iip.commons.excel.client.SortableField;

import java.util.List;

/**
 * Created by zonor on 17/3/30.
 */
public class DefaultWorkBookResolver implements HSSFWorkbookResolver {

    /**
     * 根据数据源解析Excel的工作簿，以及表头部分
     *
     * @param dataSource
     * @param handler
     * @param workbookName
     * @param titleStyleAdapter
     * @param simpleStyleAdapter @return
     */
    public <T> HSSFWorkbook resolverWorkBook(List<T> dataSource,
                                             MetaHandler<T> handler,
                                             String workbookName,
                                             CellStyleHandlerAdapter titleStyleAdapter,
                                             CellStyleHandlerAdapter simpleStyleAdapter) throws Exception {
        //获取字段的元数据信息
        List<SortableField> sortableFieldList = handler.init();

        if (StringUtils.isEmpty(workbookName)) {
            workbookName = "sheet1";
        }

        //创建工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(workbookName);
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = titleStyleAdapter.styleSettings(wb);

        //绘制标题行
        for (int i = 0; i < sortableFieldList.size(); i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(sortableFieldList.get(i).getMeta().title());
        }

        //绘制数据行
        HSSFCellStyle hssfCellStyle = simpleStyleAdapter.styleSettings(wb);
        for (int i = 0; i < dataSource.size(); i++) {
            HSSFRow dataRow = sheet.createRow(i + 1);
            T data = dataSource.get(i);

            for (int j = 0; j < sortableFieldList.size(); j++) {
                SortableField sortableField = sortableFieldList.get(j);
                String property = BeanUtils.getProperty(data, sortableField.getName());

                HSSFCell cell = dataRow.createCell(j);
                cell.setCellStyle(hssfCellStyle);
                cell.setCellValue(property);
            }

        }

        return wb;
    }

    /**
     * 根据数据源解析Excel的工作簿，以及表头部分
     *
     * @param dataSource
     * @param handler
     * @param titleStyleAdapter
     * @param simpleStyleAdapter @return
     */
    public <T> HSSFWorkbook resolverWorkBook(List<T> dataSource,
                                             MetaHandler<T> handler,
                                             CellStyleHandlerAdapter titleStyleAdapter,
                                             CellStyleHandlerAdapter simpleStyleAdapter) throws Exception {
        return resolverWorkBook(dataSource, handler, "", titleStyleAdapter, simpleStyleAdapter);
    }
}

测试, 看着确实非常简洁了
导出的实体对象

@Data
public class Student {
    @FieldMeta(title = "id", order = 0)
    private int id;

    @FieldMeta(title = "姓名", order = 2)
    private String name;

    @FieldMeta(title = "性别", order = 1)
    private String sex;

    public Student(int id, String name, String sex) {
        this.id = id;
        this.name = name;
        this.sex = sex;
    }
}

导出代码

/**
 */
public class ExcelClientTest {
    public static void main(String [] args) throws Exception {

        ExcelClient excelClient = new ExcelClient();
        excelClient.init();

        List<Student> list = new ArrayList();
        list.add(new Student(111, "张三asdf", "男"));
        list.add(new Student(111, "李四asd", "男"));
        list.add(new Student(111, "王五bhasdcfvbhujidsaub", "女"));

        HSSFWorkbook workbook = excelClient.export(list);
        FileOutputStream exportXls = new FileOutputStream("/Users/excel/工单信息表.xls");
        workbook.write(exportXls);
    }

}

结果: