根据Bean对象自动生成Excel并下载

效果展示

• 调用 (生成一个20行的Excel表格)

// 创建对象列表
List<ShipmentOrder> list = new ArrayList<>();
for (int i = 0; i < 20; i++) {
    ShipmentOrder shipmentOrder = new ShipmentOrder();
    shipmentOrder.setDriver("driver_name_" + i);
    shipmentOrder.setPlate("plate_12356");
    shipmentOrder.setMobile("mobile_13312341234");
    shipmentOrder.setStatus(200+i);
    list.add(shipmentOrder);
}
Map model = new HashMap();
model.put("list", list);
// 生成Excel表格--订单追踪s.xls
return new ModelAndView(new dreamtec.vm.starter.core.base.ExcelExporter("订单追踪s.xls"), model);

• 生成的Excel效果图
  

实现思路

要实现根据javabean对象直接生成Excel并下载，主要是给定义bean对象将会用的注解来标记Excel中用到的属性，然后获取到该注解来确定该对象的值在表格中如果使用（赋值到哪里），至于下载由框架处理即可，我们主要处理bean解析到Excel的过程。

总结：
先定义一个做注解使用的Excel对象，并定义两个属性来标记所在的列和列名
再根据反射获取到对象的属性，然后根据属性Excel相关的注解来将属性的值填到表格中对应的列（不用关心所在的行，因为每个对象占一行，第几个对象就将解析到第几行）

实现步骤

• 定义Excel注解

Excel.java

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target({ElementType.METHOD, ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface Excel {
    String title() default  "" ;	// 对象所在列的列名
    int seq()  default  0 ;		// 对象所在的列
}

• bean对象使用@Excel注解标记

ShipmentOrder.java

import dreamtec.vm.starter.core.base.Excel;

@JsonInclude(JsonInclude.Include.NON_NULL)
@Table(name = "shipment_order")
public class ShipmentOrder extends BaseDTO {

    public static long getSerialVersionUID() {
        return serialVersionUID;
    }

    private static final long serialVersionUID = 8745606121123749230L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @ApiModelProperty(value = "数据库ID", example = "")
    private Long id;

    private String warehouse; // 一般对象, 不会生成到Excel中

	// 使用Excel注解的属性, 属性的值将赋给名为'司机'的列, 该列是第一列,
    @Excel( title = "司机",seq = 0)	
    private String driver;

    @Excel( title = "车牌",seq = 1)
    private String plate;

    @Excel( title = "手机号",seq = 2)
    private String mobile;

    @Excel( title = "状态",seq = 3)
    private int status;

    public String getDriver() {
        return driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }
	
	...setter getter不再赘述
}

• 解析注解并在Excel中赋值

ExcelExporter.java

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.servlet.view.document.AbstractXlsView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

/*
 *  该对象为具体生成Excel的对象, Cotroller中使用以下代码即可:
    @RequestMapping("exportDetails")
    public ModelAndView downloadExcel() {
        List<ShipmentOrder> list = new ArrayList<>();
        for (int i = 0; i < 20; i++) {
            ShipmentOrder shipmentOrder = new ShipmentOrder();
            shipmentOrder.setDriver("driver_name_" + i);
            shipmentOrder.setPlate("plate_12356");
            shipmentOrder.setMobile("mobile_13312341234");
            shipmentOrder.setStatus(200+i);
            list.add(shipmentOrder);
        }
        Map model = new HashMap();
        model.put("list", list);
        return new ModelAndView(new dreamtec.vm.starter.core.base.ExcelExporter("订单追踪s.xls"), model);
    }
    * */
public class ExcelExporter extends AbstractXlsView {

    String excelName;	// 文件名
    String sheetTitle;	// 表名

    public ExcelExporter(String excelName) {
        this(excelName, excelName);
    }

    public ExcelExporter(String excelName, String sheetTitle) {
        this.excelName = excelName;
        this.sheetTitle = sheetTitle;
    }

    @Override
    protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, 
    	HttpServletRequest httpServletRequest, 
    	HttpServletResponse httpServletResponse) throws Exception {

        // 设置response方式,使执行导出时自动出现下载页面,而非直接使用excel打开
        httpServletResponse.setContentType("APPLICATION/OCTET-STREAM");
        httpServletResponse.setHeader("Content-Disposition", "attachment; filename=" 
        	+ URLEncoder.encode(excelName, "UTF-8"));

        // 产生Excel表头
        HSSFSheet sheet = (HSSFSheet) workbook.createSheet(sheetTitle);
        //设置单元格宽度
        for (int i = 0; i <= 20; i++) {
            sheet.setColumnWidth(i, 5000);
        }
        HSSFCellStyle cellStyle = (HSSFCellStyle) workbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置单元格样式为字体居中
        HSSFFont font = (HSSFFont) workbook.createFont();
        font.setFontName("仿宋_GB2312");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示;
        font.setFontHeightInPoints((short) 16);//设置字体大小
        cellStyle.setFont(font);

        HSSFRow header = sheet.createRow(0); // 第0行
        header.setHeight((short) 400);//设置首行行高

		// 得到对象列表
        List<?> list = (List<?>) model.get("list");
        if (list != null && list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {
                Object obj = list.get(i);	// 得到具体的对象
                // 遍历该对象的属性
                Field[] fields = obj.getClass().getDeclaredFields();
                for (Field field : fields) {
                    // 查看该属性的注解
                    for (Annotation annotation : field.getAnnotations()) {
                        // 存在Excel注释, 则需要将该对象导入Excel
                        if (annotation instanceof Excel) {  
                            Excel excel = (Excel) annotation;
                            int columnIdx = excel.seq();
                            if (i == 0) {   // 第一次找到, 需要先设置列名
                                String columnTitle = excel.title();
                                header.createCell(columnIdx).setCellValue(columnTitle);
                                header.getCell(columnIdx).setCellStyle(cellStyle);
                            }
                            // 赋值到该行(i+1)
                            HSSFRow row = sheet.getRow(i + 1);
                            if (row == null) {
                                row = sheet.createRow(i + 1);
                                row.setHeight((short) 300);
                            }
                            row.createCell(columnIdx).setCellValue(
                            	getFieldValueByFieldName(field.getName(), obj).toString());
                        }
                    }
                }
            }
        }
    }

    /**
     * 根据属性名获取属性值
     *
     * @param fieldName
     * @param object
     * @return
     */
    private Object getFieldValueByFieldName(String fieldName, Object object) {
        try {
            Field field = object.getClass().getDeclaredField(fieldName);
            //设置对象的访问权限，保证对private的属性的访问
            field.setAccessible(true);
            return field.get(object);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}