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

结果:

wyh1791

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