java 将数据写入excel工具类

开发过程中常有数据统计功能，要求统计某条件下的系统数据并导出excel下载；

以下为获取数据后的写出工具类和调用说明。

package com.xdja.pki.ra.core.util.file;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.IndexedColors;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.Date;
import java.util.List;

/**
 * Excel操作工具类
 */
public class ExcelUtil {
	
	/**
	 * 创建excel
	 * @param output	OutputStream 输出流
	 * @param sheetName		表格名称
	 * @param dates			List 数据
	 * @param hasIndexColumn 是否包含序号列
	 * @throws Exception 生成异常
	 */
	public static void getExcel(OutputStream output, String sheetName, List<?> dates, boolean hasIndexColumn) throws Exception {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(sheetName);
		CreationHelper createHelper = wb.getCreationHelper();
		HSSFRow row = sheet.createRow((short) 0);
		row.setHeightInPoints(30);		//设置表头高度30
		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);
		
		Field[] fields = dates.get(0).getClass().getDeclaredFields();
		
		int x = 0;
		if(hasIndexColumn){
			HSSFCell cell = row.createCell(x);	//设置表格第一列为序号
			cell.setCellStyle(cellStyleHead);
			cell.setCellValue("序号");
			sheet.setColumnWidth(x, 10 * 256);
			x++;
		}
		
		
		for(Field field : fields) {			//遍历有ExcelCell注解的属性，并根据注解参数设置表头值和列的宽度
			ExcelCell excelCell = field.getAnnotation(ExcelCell.class);
			if(null != excelCell) {
				HSSFCell cellHead = row.createCell(x);
				cellHead.setCellStyle(cellStyleHead);
				cellHead.setCellValue(excelCell.title());
				
				sheet.setColumnWidth(x, 256 * excelCell.width());
				x++;
			}
		}

		for (int i = 0; i < dates.size(); i++) { 		//循环数据list
			row = sheet.createRow((short) 1 + i);		//创建一个行
			row.setHeightInPoints(20);					//设置内容高度为20
			Object object = dates.get(i);
			int n = 0;
			if(hasIndexColumn){
				HSSFCell xh = row.createCell(0);			//每行的第一个单元格为序号
				xh.setCellStyle(cellStyle);
				xh.setCellValue(i+1);
				n++;
			}
			
			
			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);
			//output.close();
		} catch (IOException e) {
			e.printStackTrace();
			System.out.println("Output is closed");
		}
	}

}

package com.xdja.pki.core.utils.file;

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

@Retention(RetentionPolicy.RUNTIME)
@Target({java.lang.annotation.ElementType.FIELD})
public @interface ExcelCell {
    public abstract String title();
    public abstract int width();
    public abstract boolean isDate();
}

调用说明：

    /**
     * XXXX统计
     * @param resp
     * @return
     */
    @GetMapping(value = "/v1/**")
    public Object statisticsExcel(HttpServletResponse resp) {
         // TODO 入参可以有筛选条件 实现类略
         try {
			//获取待写入excel数据 
            List<StatisticsVO> datas =  ;
            if(datas.size() == 0){
                return null;
            }
			//写入excel filename=是生成的文件名
            resp.setContentType("application/force-download");
            resp.setHeader("Content-Disposition", "attachment; filename=excel_name.xls");
            ExcelUtil.getExcel(resp.getOutputStream(), "表格名称", datas, true);
            return null;
        } catch (Exception e) {
            logger.error("XXXX统计失败！", e);
            return ErrorEnum.SERVER_INTERNAL_EXCEPTION.resp(resp);
        }

    }