JAVA查数据库导出数据到Excel

底层工具

public static Object getFieldValueByName(String fieldName, Object o,
			Class<?> clazz) {
		Object value = null;
		try {
			Field field = clazz.getDeclaredField(fieldName);
			field.setAccessible(true); // 设置私有属性范围
			value = field.get(o);
		} catch (Exception e) {
			if (clazz.getSuperclass() != null) {
				value = getFieldValueByName(fieldName, o, clazz.getSuperclass());
			}
		}
		return value;
	}

Excel工具 方法

	public static <T> void createExcel2007(XSSFWorkbook wb, String title, String[] headers, String[] filedNames,Collection<T> dataset) {
		if(dataset == null) {
			return;
		}
		// 输出流
		// 工作区
		// 工作区
		// 创建第一个sheet
		XSSFSheet sheet1 = wb.createSheet(title);
		// 生成第一行
		XSSFRow row1 = sheet1.createRow(0);
		// row1.setHeightInPoints((short) 30);
		// 给这一行赋值
		for (short i = 0; i < headers.length; i++) {
			row1.createCell(i).setCellValue(headers[i]);
		}
		XSSFRow row = null;
		// 遍历集合数据，产生数据行
		int index = 0;
		Iterator<T> it = dataset.iterator();
		while (it.hasNext()) {
			index++;
			row = sheet1.createRow(index);
			T t = (T) it.next();
			for (int i = 0; i < filedNames.length; i++) {
				Object value = RefectUtils.getFieldValueByName(filedNames[i], t, t.getClass());
				if (null != value) {
					row.createCell(i).setCellValue(value.toString());
				}
			}
		}

	}

业务实现

public void downExcelAll(){
		//PO为实体对象
		List<PO> models = bbtCustomerMonthSalesStatisticsService.selectPO(new POExample());

    	List<PO> content1 = models;
    	String filename = "_商家销售成绩信息表_" + new Date().getTime() + ".xlsx";
        String path = filename;
        InputStream inputStream = null;
        ByteArrayOutputStream out = null;
        File file = null;
        
		//为实体对象的属性，请正确填写
        String[] title = new String[]{"name", "sex", "age", "d", "e", "f", "g","h"};
        XSSFWorkbook wb = new XSSFWorkbook();
        ExcelReader.createExcel2007(wb,"活动管理", new String[] {"姓名", "性别", "年龄", "店铺", "商品数", "销售额", "电话","类目"}, title, content1);
        response.reset();
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename="
                    + DateUtil.format(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS)
                    + ".xlsx");
        OutputStream os = response.getOutputStream();
        wb.write(os);
        os.flush();
            
}