POI 实现excel 导出

• maven 依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>

• controller 导出方法

@RequestMapping("/recordExcel")
public void recordExcel(HttpServletResponse response,LbsLocRecord lbsLocRecord) {
    
    List<LbsLocRecord> recordList = lbsLocRecordService.getList(lbsLocRecord);

    HSSFWorkbook workbook = new HSSFWorkbook();
    String sheetName = "定位记录";
    HSSFSheet sheet = workbook.createSheet(sheetName);
    sheet.setDefaultRowHeightInPoints(1F);
    sheet.setDefaultColumnWidth(20);
    sheet.setColumnWidth(7,50*256);

    // 字体
    HSSFFont font = workbook.createFont();
    font.setFontHeightInPoints((short) 13);

    // 单元格内容样式
    HSSFCellStyle normalStyle = workbook.createCellStyle();
    normalStyle.setLocked(true);
    normalStyle.setWrapText(true);
    normalStyle.setFont(font);

    HSSFRow firstRow = sheet.createRow(0);

    ArrayList<String> headCellNames = Lists.newArrayList("姓名", "员工编号", "手机号", "部门",
            "经度", "纬度", "定位时间", "位置描述",
            "错误码", "错误信息","创建时间");

    ExcelUtil.setHeadCellNames(firstRow,normalStyle,headCellNames);

    ArrayList<String> cellValues;
    for (int i=0;i< recordList.size();i++){
        LbsLocRecord record = recordList.get(i);
        
        cellValues = Lists.newArrayList(
            record.getPersonName(), record.getPersonCode(), record.getDeviceId(), record.getDeptName(),
            record.getLon(), record.getLat(), record.getTime(), record.getPosition(),
            record.getErrcode(), record.getErrmsg(),
                DateUtil.DateToString(record.getCreateAt()));

        HSSFRow hssfRow = sheet.createRow( i + 1);

        ExcelUtil.setCellValue(hssfRow,normalStyle,cellValues);
    }
    try {
        String filename = "record.xls";
        // 自定义输出文件名
        String typeName = new String(filename.getBytes("UTF-8"), "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename="
                + typeName);
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    
    
    } catch (IOException e) {
        e.printStackTrace();
    }
}

• excel 工具类

public class ExcelUtil {
public static void setHeadCellNames(HSSFRow firstRow,HSSFCellStyle cellStyle, List<String> headCellNames){
	
	// 添加表头内容
	for (int i = 0; i < headCellNames.size(); i++) {
		HSSFCell headCell = firstRow.createCell(i);
		headCell.setCellValue(headCellNames.get(i));
		headCell.setCellStyle(cellStyle);
	}
}

public static void setCellValue(HSSFRow hssfRow,HSSFCellStyle cellStyle,List<String> cellValues){
	
	// 添加数据内容
	for (int i = 0; i < cellValues.size(); i++) {
		// 创建单元格，并设置值
		HSSFCell cell = hssfRow.createCell(i);
		cell.setCellValue(cellValues.get(i));
		cell.setCellStyle(cellStyle);
	}
}

public static <T> void createExcel(String fileName, List<String> headCellNames
		, List<T> dataList,HttpServletResponse response) throws InvocationTargetException {
	// 创建一个Excel文件
	HSSFWorkbook workbook = new HSSFWorkbook();
	// 创建一个工作表
	HSSFSheet sheet = workbook.createSheet();
	sheet.setDefaultRowHeightInPoints(1F);
	sheet.setDefaultColumnWidth(20);
	
	// 设置单元格格式
	HSSFCellStyle cellStyle = workbook.createCellStyle();
	cellStyle.setAlignment(HorizontalAlignment.LEFT);
	// 单元格内容样式
	cellStyle.setLocked(true);
	cellStyle.setWrapText(true);
	// 字体
	HSSFFont font = workbook.createFont();
	font.setFontHeightInPoints((short) 13);
	cellStyle.setFont(font);
	// 添加表头行
	HSSFRow firstRow = sheet.createRow(0);
	// 添加表头内容
	setHeadCellNames(firstRow,cellStyle,headCellNames);
	
	for (int i=0;i< dataList.size();i++){
    	T data = dataList.get(i);
    	Method[] methods = data.getClass().getMethods();
    	HSSFRow hssfRow = sheet.createRow( i + 1);
    	List<String> cellValues = Lists.newArrayList();
    	for (Method temp : methods)
        	try {
        	    if (temp.getName().contains("get")){
        		    Object object = temp.invoke(data);
        		    if (object==null){
        			    cellValues.add("");
        		    }else {
        			    cellValues.add(object.toString());
        		    }
        	    }
        	} catch (IllegalAccessException e) {
        		e.printStackTrace();
        	}
    
    	ExcelUtil.setCellValue(hssfRow,cellStyle,cellValues);
    }
	
	try {
    	// 自定义输出文件名
    	String typeName = new String(fileName.getBytes("UTF-8"), "UTF-8");
    	response.setContentType("application/vnd.ms-excel");
    	response.setHeader("Content-disposition", "attachment;filename="
    			+ typeName);
    	OutputStream outputStream = response.getOutputStream();
    	workbook.write(outputStream);
    	outputStream.flush();
    	outputStream.close();
	} catch (IOException e) {
    	e.printStackTrace();
	}
	
    }
}