Java excel 报表导出，spring注解动态注入列

作为日常记录，Excel导出后台操作，作为最普通一种方式，直接上代码：

1.定义Excel列字段

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

/**
 * excel字段标记
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelCell {
    /**
     * 该字段对应的表头
     */
    String title() default "";
    /**
     * 该字段单元格宽度
     */
    int width() default 20;
    /**
     * long属性是否转换为date
     */
    boolean isDate() default false;
}

2.bean类定义

用于业务数据封装，导出字段加上@ExcelCell 注解。

3.后台逻辑代码

 /**
     * 创建带标题的excel自定义表头名称
     * @param output  输出
     * @param sheetName excelsheet名称
     * @param title 标题
     * @param dates 数据

* @param titleList 表头

     * @throws Exception
     */
    public static void getExcel(OutputStream output, String sheetName, String title, List<?> dates, List<String> titleList) throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(sheetName);
        CreationHelper createHelper = wb.getCreationHelper();
         CellStyle cellStyle = wb.createCellStyle();                         //内容单元格样式
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

         CellStyle cellStyleWithDate = wb.createCellStyle();                         //内容单元格样式
        cellStyleWithDate.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyleWithDate.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cellStyleWithDate.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyleWithDate.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleWithDate.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyleWithDate.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleWithDate.setBorderRight(CellStyle.BORDER_THIN);
        cellStyleWithDate.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleWithDate.setBorderTop(CellStyle.BORDER_THIN);
        cellStyleWithDate.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleWithDate.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd hh:mm:ss"));

          CellStyle cellStyleHead = wb.createCellStyle();                     //表头单元格样式
        cellStyleHead.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyleHead.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cellStyleHead.setBorderBottom(CellStyle.BORDER_DOUBLE);
        cellStyleHead.setBottomBorderColor(IndexedColors.BLACK.getIndex());                 //设置表头下边框为双线
        cellStyleHead.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyleHead.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleHead.setBorderRight(CellStyle.BORDER_THIN);
        cellStyleHead.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleHead.setBorderTop(CellStyle.BORDER_THIN);
        cellStyleHead.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleHead.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());     //设置表头颜色为25%灰色
        cellStyleHead.setFillPattern(CellStyle.SOLID_FOREGROUND);

         CellStyle cellStyleTitle = wb.createCellStyle();                        //标题单元格样式
        cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyleTitle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cellStyleTitle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyleTitle.setBottomBorderColor(IndexedColors.BLACK.getIndex());                    
        cellStyleTitle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyleTitle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleTitle.setBorderRight(CellStyle.BORDER_THIN);
        cellStyleTitle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleTitle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyleTitle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        Font font = wb.createFont();                        //设置标题字体样式
        font.setFontHeightInPoints((short)13);              //设置字体为13像素
        cellStyleTitle.setFont(font);

         HSSFRow row = sheet.createRow((short) 0);
       
       /**
         * 首先设置表头行，根据表头行可以得出总共的列数x
         * 然后根据x可以设置标题行合并单元格的其实位置
         */
        //设置表头行
        row = sheet.createRow((short) 1);   
        row.setHeightInPoints(30);      //设置表头高度30
        int x = 0;
        HSSFCell cell = row.createCell(0);  //设置表格第一列为序号
        cell.setCellStyle(cellStyleHead);
        cell.setCellValue("序号");
        sheet.setColumnWidth(x, 10 * 256);
        x++;

       Field[] fields = dates.get(0).getClass().getDeclaredFields();
        int j = 0;
        for(Field field : fields) {         //遍历有ExcelCell注解的属性，并根据注解参数设置表头值和列的宽度
            ExcelCell excelCell = (ExcelCell)field.getAnnotation(ExcelCell.class);
            if(null != excelCell) {
                HSSFCell cellHead = row.createCell(x);
                cellHead.setCellStyle(cellStyleHead);
                cellHead.setCellValue(titleList.get(j));
                j++;
                sheet.setColumnWidth(x, 256 * excelCell.width());
                x++;
            }
        }

      //设置标题行
        row = sheet.createRow((short) 0);
        row.setHeightInPoints(30);      //设置标题高度30
        HSSFCell titleCell = row.createCell(0);         //设置标题单元格
        titleCell.setCellValue(title);
        titleCell.setCellStyle(cellStyleTitle);
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,x-1)); //合并标题单元格

 //设置数据内容单元格
        for (int i = 0; i < dates.size(); i++) {        //循环数据list
            row = sheet.createRow((short) 2 + i);       //创建一个行
            row.setHeightInPoints(20);                  //设置内容高度为20
            Object object = dates.get(i);
            
            HSSFCell xh = row.createCell(0);            //每行的第一个单元格为序号
            xh.setCellStyle(cellStyle);
            xh.setCellValue(i+1);
            
            int n = 1;

           for (Field field : object.getClass().getDeclaredFields()) {         //根据反射取得由ExcelCell注解的属性并创建单元格
                ExcelCell excelCell = (ExcelCell)field.getAnnotation(ExcelCell.class);
                if(null != excelCell) {
                    field.setAccessible(true);
                    HSSFCell data = row.createCell(n);
                    if(excelCell.isDate()) {
                        if(null != field.get(object)) {
                            Date date = new Date(Long.parseLong(field.get(object).toString()));
                            data.setCellValue(date);
                            data.setCellStyle(cellStyleWithDate);
                        } else {
                            data.setCellValue("");
                            data.setCellStyle(cellStyleWithDate);
                        }

                  } else {
                        if(field.get(object)!=null)
                            data.setCellValue(field.get(object).toString());
                        data.setCellStyle(cellStyle);
                    }
                                        
                    field.setAccessible(false);
                    n++;
                }
                
            }
        }
try {
            output.flush();
            wb.write(output);
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }
    }