基础POI的ExportExcelUtils工具类的Excel表格导出十分方便

 一个controller的完整的流程：
    1 new一个输出流 
    2 构造属性列与头部的对应关系 
    3 拿到list集合用ExportExcelUtils.createWorkbook 方法返回workbook对象；
    4 setRoponseHead设置返回头部，与文件名；
    5 关闭流

        对应的代码方法：如下

        OutputStream out= response.getOutputStream();      
String[] field=        {"orderNo","createTime","username","tel","orderStatus","payAmount","totalIntegral","remark"};
String[] heads={"订单号" ,"下单时间"   ,"下单人", "联系电话","订单状态","订单总金额", "订单积分","备注"};
List<OrderDetailItem> list =  page.getResult();
        Workbook workbook = ExportExcelUtils.createWorkbook(list,field,heads);        

           
        this.setRoponseHead("test.xlsx",request,response);
        workbook.write(out);
        out.flush();
        out.close();






    /**
     * 设置返回反馈头
     * @param fileName 需要保存的excel的文件名字
     * @param request 用来兼容使用
     * @param response 对应反馈头
     * @throws UnsupportedEncodingException UTF-8解析异常
     */
    private void setRoponseHead(String fileName,HttpServletRequest request,HttpServletResponse response) throws UnsupportedEncodingException {
        response.setContentType("application/octet-stream; charset=utf-8");

        if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0){

            // firefox浏览器
            response.setHeader("Content-Disposition", "attachment; filename=" +  new String(fileName.getBytes("UTF-8"), "ISO8859-1"));

        } else if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0){

            // IE浏览器
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));

        }else{

            //其他浏览器
            response.setHeader("Content-disposition", "attachment;filename="+fileName);
        }


    }

 

 一个controller的完整的流程：
    1 new一个输出流 
    2 构造属性列与头部的对应关系 
    3 拿到list集合用ExportExcelUtils.createWorkbook 方法返回workbook对象；
    4 setRoponseHead设置返回头部，与文件名；
    5 关闭流

 

 

下面给出ExcelUtils工具类、以及映射实体的注解，或者表头与注解对应的工具类；

 

import com.iori.oms.common.util.TagUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.util.List;

/**
 * description:这个主要的工作是将list转化为Excel对象
 * @author BlingSun 
 * @version 1.0 2018/10/31 16:00 by BlingSun  创建
 **/
public class ExportExcelUtils {
    /**
     *  生成excel工作表格
     *  格式为
     *
     *  heads[0]                    heads[1]                heads[2]                heads[3]                heads[4]    ...
     *  data[0].fieldNames[0]       data[0].fieldNames[1]   data[0].fieldNames[2]   data[0].fieldNames[3]   data[0].fieldNames[4]   ....
     *  data[1].fieldNames[0]       data[1].fieldNames[1]   data[1].fieldNames[2]   data[1].fieldNames[3]   data[1].fieldNames[4]   ....
     *  ......
     *
     * @param data 这个需要装的数据
     * @param fieldNames 这个指对应的的数据对象的字段名
     * @param heads excel的表头
     * @param <T>  插入表格数据的值
     * @return 完整的Excel表格
     */
  static public<T>  Workbook createWorkbook(List<T> data, String[] fieldNames, String[] heads){

       //创建一个excel表
       Workbook workbook = new SXSSFWorkbook(100);
       Sheet sheet = workbook.createSheet();

       //创建表头
       Row row = sheet.createRow(0);
       for (int cn = 0; cn < heads.length; cn++) {
           Cell cell = row.createCell(cn);
           cell.setCellValue(heads[cn]);
       }

       //这个用表标注当前是第几行
       int line=1;
       //添加数据
       for(T item:data){
           //创建一行的row数据
            row=sheet.createRow(line++);
            //填充row
           for(int i=0;i<fieldNames.length;i++){
               Cell cell = row.createCell(i);
               cell.setCellValue(TagUtil.getFieldsByName(item,fieldNames[i])+"");
           }
       }


       return workbook;

    }
}

import com.iori.oms.common.tag.Column;

import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * description: 对注解进行解析的工具类
 * @author BlingSun 
 * @version  2018/9/20 11:26
 * @since 2018/9/20 11:26
 **/
public class TagUtil {

    /**
     *
     * @param clazz 需要获取的属性-列名映射的类
     * @return  映射的关系
     * @throws Exception 处理异常
     */
    public static Map<String,String> getFieldToColumnMap(Class<?> clazz) throws Exception {


        //参数的列表
        Map<String,String> keyValue=new HashMap<String,String>(8);

        //处理属性的注解
        List<Field> list=Arrays.asList(clazz.getDeclaredFields());

        //规则:
        //如果没有Column注解就不用映射到列表在，如果value没有值则为属性的名字
        for(int i=0;i<list.size();i++){

            //1.判断有没有Column注解
            if(list.get(i).isAnnotationPresent(Column.class)){

                //2.判断Value的值，遍历注解
                for(Annotation annotation:list.get(i).getAnnotations()){

                    //Column注解的对象
                    if(annotation.annotationType()==Column.class){
                        //转化类型
                        Column temp=(Column) annotation;

                        //判断是不是为空
                        if("".equals(temp.value())){
                            //是空就是属性名为列名
                            keyValue.put(list.get(i).getName(),list.get(i).getName());
                        }else{
                            //value不是空的
                            keyValue.put(list.get(i).getName(),temp.value());
                        }

                    }

                }
            }
        }


        return keyValue;
    }

    /**
     * 通过调用get方法来获取对象值
     * @param o 需要获取对象
     * @param fieldName 对象的列名
     * @return o.fieldName的值
     */
    public static Object getFieldsByName(Object o,String fieldName){
        Object result=null;

        //将属性名字大写大写
        fieldName=fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);

        Method getMethod;

        try {
            //获取get方法
            getMethod=o.getClass().getMethod("get"+fieldName);

            result=getMethod.invoke(o);

        }catch (Exception e){
                e.printStackTrace();
        }
        return result;
    }

    /**
     * 通过调用set方法来设置对象值
     * @param o 需要获取对象
     * @param fieldName 对象的列名
     */
    public static void setFieldsByName(Object o,String fieldName,Object value){

        //将属性名字大写大写
        fieldName=fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);

        Method getMethod;

        try {
            //获取get方法
            getMethod=o.getClass().getMethod("set"+fieldName);

            getMethod.invoke(o,value);

        }catch (Exception e){
            e.printStackTrace();
        }

    }
}

package com.iori.oms.order.vo;

import com.iori.oms.common.tag.Column;
import com.ycc.base.common.BaseDomain;

import java.util.Date;

/**
 * description:
 * 这个是一个VO向前端传送的数据,这个会包括join in的数据
 * @author BlingSun
 * @version 1.0 2018/10/27 9:39 by BlingSun (liang.zhou01@ucarinc.com) 创建
 **/
public class OrderViewVO extends BaseDomain {
    /**
     * 自增主键
     */
    @Column("t_iori_order.id")
    private Long id;

    /**
     * 订单编号
     */
    @Column("order_no")
    private String orderNo;

    /**
     * 下单人的电话号码
     */
    @Column("t_iori_customer.tel")
    private String tel;


    @Column("t_iori_customer.username")
    private String username;

    /**
     * 提交订单的客户id
     */
    @Column("customer_id")
    private Long customerId;

    /**
     * 收货地址id
     */
    @Column("address_id")
    private Long addressId;

    /**
     * 付款类型:1-货到付款，2-在线付款
     */
    @Column("payment_type")
    private Integer paymentType;

    /**
     * 订单状态:0-已取消-1-未付款，2-已付款，3-已发货，4-交易成功，5-交易关闭 （默认值为：1）
     */
    @Column("order_status")
    private Integer orderStatus;

    /**
     * 实际付款总金额
     */
    @Column("pay_amount")
    private Integer payAmount;

    /**
     * 此次订单赠送的总积分
     */
    @Column("total_integral")
    private Integer totalIntegral;

    /**
     * 备注
     */
    @Column("remark")
    private String remark;

    /**
     * 交易关闭时间
     */
    @Column("close_time")
    private Date closeTime;

    /**
     * 交易完成时间
     */
    @Column("end_time")
    private Date endTime;

    /**
     * 付款时间
     */
    @Column("payment_time")
    private Date paymentTime;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getOrderNo() {
        return orderNo;
    }

    public void setOrderNo(String orderNo) {
        this.orderNo = orderNo;
    }

    public String getTel() {
        return tel;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Long getCustomerId() {
        return customerId;
    }

    public void setCustomerId(Long customerId) {
        this.customerId = customerId;
    }

    public Long getAddressId() {
        return addressId;
    }

    public void setAddressId(Long addressId) {
        this.addressId = addressId;
    }

    public Integer getPaymentType() {
        return paymentType;
    }

    public void setPaymentType(Integer paymentType) {
        this.paymentType = paymentType;
    }

    public Integer getOrderStatus() {
        return orderStatus;
    }

    public void setOrderStatus(Integer orderStatus) {
        this.orderStatus = orderStatus;
    }

    public Integer getPayAmount() {
        return payAmount;
    }

    public void setPayAmount(Integer payAmount) {
        this.payAmount = payAmount;
    }

    public Integer getTotalIntegral() {
        return totalIntegral;
    }

    public void setTotalIntegral(Integer totalIntegral) {
        this.totalIntegral = totalIntegral;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }

    public Date getCloseTime() {
        return closeTime;
    }

    public void setCloseTime(Date closeTime) {
        this.closeTime = closeTime;
    }

    public Date getEndTime() {
        return endTime;
    }

    public void setEndTime(Date endTime) {
        this.endTime = endTime;
    }

    public Date getPaymentTime() {
        return paymentTime;
    }

    public void setPaymentTime(Date paymentTime) {
        this.paymentTime = paymentTime;
    }

    @Override
    public String toString() {
        return "OrderViewVO{" +
                "id=" + id +
                ", orderNo='" + orderNo + '\'' +
                ", tel='" + tel + '\'' +
                ", username='" + username + '\'' +
                ", customerId=" + customerId +
                ", addressId=" + addressId +
                ", paymentType=" + paymentType +
                ", orderStatus=" + orderStatus +
                ", payAmount=" + payAmount +
                ", totalIntegral=" + totalIntegral +
                ", remark='" + remark + '\'' +
                ", closeTime=" + closeTime +
                ", endTime=" + endTime +
                ", paymentTime=" + paymentTime +
                ", createTime=" + createTime +
                '}';
    }
}