SpringMVC使用AbstractExcelView导出Excel

poi

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.10-FINAL</version>
</dependency>

---

controller

@RequestMapping(value = "/exportExcel", method = RequestMethod.POST)  
public String exportExcel(HttpServletRequest request, Model model) {  
    try {  
        //查询导出数据
        List<Order> orderList = orderService.findListByParam(map);
        //设置名称、标题、数据一一对应
        DtExcelMap excel = DtExcelMap.getExcelMap("订单列表", 
            new String[] { "ID","编号","序列", "匿名", "状态", "姓名","手机号"},
            orderList,  
            new String[] { "id","orderNo","serialNumber", "anonymous", "statusView", "realName", "mobile"});  
        model.addAttribute("map", excel);
        //返回视图(xml中配置)
        return "excelView";
    } catch (Exception e) {  
        log.error("导出excel失败：" + e);  
    }  
    return null;  
}

---

ExcelView

import java.util.Date;  
import java.util.List;  
import java.util.Map;  
import javax.servlet.http.HttpServletRequest;  
import javax.servlet.http.HttpServletResponse;  
import org.apache.poi.hssf.usermodel.HSSFRow;  
import org.apache.poi.hssf.usermodel.HSSFSheet;  
import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
import org.springframework.web.servlet.view.document.AbstractExcelView;  

public class ExcelView extends AbstractExcelView {  

    @Override  
    protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook,  
            HttpServletRequest request, HttpServletResponse response) throws Exception {  
        // 从model对象中获取excel所需数据  
        DtExcelMap map = (DtExcelMap) model.get("map");  
        // 创建Excel的sheet  
        String name = map.getExcelName();  
        HSSFSheet sheet = workbook.createSheet(name);  
        // 创建标题行  
        HSSFRow header = sheet.createRow(0);  
        String[] titles = map.getTitles();  
        int cell = 0;  
        for (String title : titles) {  
            header.createCell(cell).setCellValue(title);  
            cell++;  
        }  
        // 填充数据  
        int rowNum = 1;  
        List<?> models = map.getModels();  
        String[] fileds = map.getFields();  
        for (Object model2 : models) {  
            HSSFRow row = sheet.createRow(rowNum);  
            String cellValue = null;  
            for (int i = 0; i < fileds.length; i++) {  
                String fieldName = fileds[i];  
                Object result = ReflectUtil.invokeGetter(model2, fieldName);  
                cellValue = ReflectUtil.toString(result);  
                // 如果是日期类型则进行格式化处理  
                if (ReflectUtil.isDateType(model2.getClass(), fieldName)) {  
                    cellValue = DateUtil.Date2Stirng2Second((Date) result);  
                }  
                row.createCell(i).setCellValue(cellValue);  
            }  
            rowNum++;  
        }  
        response.setHeader("Content-Disposition",  
                "attachment;filename=" + new String((name + ".xls").getBytes(), "ISO-8859-1"));  
    }  
}

---

spring xml

controller里面return的就是该视图，也就是ExcelView

<bean name="excelView" class="com.yzx.view.ExcelView"/>

---

ExcelView会使用到以下几个工具类

ReflectUtil

import java.lang.reflect.Constructor;  
import java.lang.reflect.Field;  
import java.lang.reflect.InvocationTargetException;  
import java.lang.reflect.Method;  
import java.util.Date;  

//反射工具类 
public class ReflectUtil {  

    public static <T> Object invokeGetter(T target, String fieldName) throws NoSuchMethodException,  
            SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {  
        // 如果属性名为xxx，则方法名为getXxx  
        String methodName = "get" + StringUtil.firstCharUpperCase(fieldName);  
        Method method = target.getClass().getMethod(methodName);  
        return method.invoke(target);  
    }  

    @SuppressWarnings("unchecked")  
    public static <T> boolean isDateType(Class<T> clazz, String fieldName) {  
        boolean flag = false;  
        Field field = null;  
        boolean noSuchFiled = true;  
        do {  
            try {  
                field = clazz.getDeclaredField(fieldName);  
                Object typeObj = field.getType().newInstance();  
                flag = typeObj instanceof Date;  
                noSuchFiled = false;  
            } catch (NoSuchFieldException e) {  
                clazz = (Class<T>) clazz.getSuperclass();  
            } catch (Exception e) {  
                // 除了NoSuchFieldException这个异常，其他直接跳出循环  
                noSuchFiled = false;  
            }  

        } while (noSuchFiled && clazz != Object.class);  
        return flag;  
    }  

    public static String toString(Object object) {  
        StringBuffer buffer = new StringBuffer();  
        if (object != null) {  
            buffer.append(object);  
        }  
        return buffer.toString();  
    }  

}

---

DateUtil

import java.text.SimpleDateFormat;
import java.util.Date;


public class DateUtil {

    public static final String DEFAULT_DATETIME_FORMAT = "yyyy-MM-dd HH:mm:ss";
    public static String toString(Date date, String format) {
        SimpleDateFormat formatter;
        if ((date == null) || (format == null) || (format.length() == 0)) {
            return null;
        }
        formatter = new SimpleDateFormat(format);
        return formatter.format(date);
    }

    /**
     * 日期字符串转换，页面显示格式为：yyyy-MM-dd HH:mm:dd
     * @param 参数为Date类型
     */
    public static String Date2Stirng2Second(Date date) {
        return toString(date, DEFAULT_DATETIME_FORMAT);
    } 
}

---

DtExcelMap

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DtExcelMap {

    private final static String EXCELNAME = "name";
    private final static String MODELS = "models";
    private final static String FILEDS = "fileds";
    private final static String TITLES = "titles";

    private Map<String, Object> map;

    private DtExcelMap() {
        super();
    }

    private DtExcelMap(Map<String, Object> map) {
        super();
        this.map = map;
    }

    private Map<String, Object> getMap() {
        return map;
    }

    /**
     * 
     * @param name
     *            导出excel表名字
     * @param titles
     *            excel表标题
     * @param models
     *            表内容代表的实体类
     * @param fields
     *            每列标题对应实体类的属性，titles和fields长度必须相等，且顺序一致
     * @return
     * @throws Exception
     */
    public static <T> DtExcelMap getExcelMap(String name, String[] titles, List<T> models, String[] fields)
            throws Exception {
        if (titles == null || fields == null) {
            return null;
        }
        if (titles.length != fields.length) {
            throw new Exception("标题对应的实体属性长度不一致");
        }
        Map<String, Object> map = new HashMap<>();
        map.put(EXCELNAME, name);
        map.put(TITLES, titles);
        map.put(MODELS, models);
        map.put(FILEDS, fields);
        return new DtExcelMap(map);
    }

    public String getExcelName() {
        return (String) this.getMap().get(EXCELNAME);
    }

    public String[] getTitles() {
        return (String[]) this.getMap().get(TITLES);
    }

    public String[] getFields() {
        return (String[]) this.getMap().get(FILEDS);
    }

    @SuppressWarnings("unchecked")
    public <T> List<T> getModels() {
        return (List<T>) this.getMap().get(MODELS);
    }
}

以上代码开箱即用。