java导出excel

代码

1.工具类ExportUtil

	/**
	 * 实体类导出到excel
	 * <br/>
	 * headers: 导出excel的标头，标题需要按照T的属性定义顺序编写 <br/>
	 * dataList: 要被导出的数据<br/>
	 * ****************************<br/>
	 * eg:
	 * class T{<br/>
	 * private int id;<br/>
	 * private String name;<br/>
	 * private String age;<br/>
	 * }<br/>
	 * headers[]{编号, 姓名， 年龄}<br/>
	 * *****************************<br/>
	 * @param path
	 * @param dataList
	 * @throws IOException
	 */
	public static <T> void exportExcel(String[] headers, OutputStream out, List<T> dataList) throws IOException {
		// 实例化Excel表格
		SXSSFWorkbook workbook = new SXSSFWorkbook();

		// 导出到Excel
		exportToExcel2(headers, dataList, workbook, out);
	}
	
	/**
	 * 自定义类型导出excel
	 * @param out<br/>
	 * @param dataList<br/>
	 * @param keys-Map数据的key集合<br/>
	 * @param headers-excel的标题<br/>
	 * keys的顺序需要和headers顺序保持一致<br/>
	 * *******************************<br/>
	 * eg:<br/>
	 * headers[]{编号，名称，创建日期}<br/>
	 * keys[]{id,name,createTime}<br/>
	 * ******************************<br/>
	 * @throws IOException
	 */
	public static void exportExcel(ServletOutputStream out, List<Map<String, Object>> dataList, String[] keys,
			String[] headers) throws IOException {
		// 实例化Excel表格
		SXSSFWorkbook workbook = new SXSSFWorkbook();
		// 导出到Excel
		exportToExcel2(headers, keys, dataList, workbook, out);
	}
	/**
	 * @param headers
	 * @param list
	 * @param wb
	 * @param out
	 * @throws IOException
	 */
	private static <T> void exportToExcel2(String[] headers, List<T> list, SXSSFWorkbook wb, OutputStream out)
			throws IOException {
		// 创建一个sheet表单
		SXSSFSheet sheet = wb.createSheet();
		sheet.setDefaultColumnWidth(25);
		sheet.setDefaultRowHeight((short) 10);
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);
		SXSSFRow row = sheet.createRow(0);
		SXSSFCell cell = null;
		XSSFRichTextString txt = null;

		// 设置标头
		for (int i = 0; i < headers.length; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(style);
			txt = new XSSFRichTextString(headers[i]);
			cell.setCellValue(txt);
		}
		Iterator<T> it = list.iterator();
		int index = 0;
		Object tmp = null;
		SXSSFCell cl;
		String MethodName;
		Class clz;
		String name;
		Method me;
		int index2;
		try {
			while (it.hasNext()) {
				if (index > 29999) { // 大于30000行创建新的sheet
					sheet = wb.createSheet();
					sheet.setDefaultColumnWidth(25);
					sheet.setDefaultRowHeight((short) 10);
					row = sheet.createRow(0);
					// 设置标头
					for (int i = 0; i < headers.length; i++) {
						cell = row.createCell(i);
						cell.setCellStyle(style);
						txt = new XSSFRichTextString(headers[i]);
						cell.setCellValue(txt);
					}
					index = 0;
				}
				index++;
				row = sheet.createRow(index);
				T t = it.next();
				// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
				Field[] fields = t.getClass().getDeclaredFields();
				index2 = 0;
				for (int j = 0; j < fields.length; j++) {
					name = fields[j].getName();
					MethodName = "get".concat(name.substring(0, 1).toUpperCase().concat(name.substring(1)));
					clz = t.getClass();
					me = clz.getMethod(MethodName, new Class[] {});
					tmp = me.invoke(t, new Object[] {});
					cl = row.createCell(index2);
					cl.setCellStyle(style);
					if (StrUtil.isBlankOrNull(String.valueOf(tmp))) {
						tmp = "-";
					}
					if (tmp instanceof Date) {
						Date date = (Date) tmp;
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						tmp = sdf.format(date);
					}
					cl.setCellValue(String.valueOf(tmp));
					index2++;
					if (j == headers.length - 1) {
						break;
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		// 将文件输出到客户端浏览器
		try {
			wb.write(out);
			out.flush();
			out.close();
			wb.dispose();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * @param headers
	 * @param keys
	 * @param list
	 * @param wb
	 * @param out
	 * @throws IOException
	 */
	private static void exportToExcel2(String[] headers, String[] keys, List<Map<String, Object>> list, SXSSFWorkbook wb,
			ServletOutputStream out) throws IOException {
		// 创建一个sheet表单
		SXSSFSheet sheet = wb.createSheet();
		sheet.setDefaultColumnWidth(25);
		sheet.setDefaultRowHeight((short) 10);
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);
		SXSSFRow row = sheet.createRow(0);
		SXSSFCell cell = null;
		XSSFRichTextString txt = null;
		// 设置标头
		for (int i = 0; i < headers.length; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(style);
			txt = new XSSFRichTextString(headers[i]);
			cell.setCellValue(txt);
		}
		Iterator<Map<String, Object>> it = list.iterator();
		int index = 0;
		Object tmp = null;
		Map<String, Object> map;
		SXSSFCell cl;
		try {
			while (it.hasNext()) {
				if (index > 29999) { // 大于30000行创建新的sheet
					sheet = wb.createSheet();
					sheet.setDefaultColumnWidth(25);
					sheet.setDefaultRowHeight((short) 10);
					row = sheet.createRow(0);
					// 设置标头
					for (int i = 0; i < headers.length; i++) {
						cell = row.createCell(i);
						cell.setCellStyle(style);
						txt = new XSSFRichTextString(headers[i]);
						cell.setCellValue(txt);
					}
					index = 0;
				}
				index++;
				row = sheet.createRow(index);
				map = it.next();
				for (int i = 0; i < keys.length; i++) {
					cl = row.createCell(i);
					cl.setCellStyle(style);
					tmp = map.get(keys[i]);
					if (StringUtil.isBlankOrNull(String.valueOf(tmp))) {
						tmp = "-";
					}
					if (tmp instanceof Date) {
						Date date = (Date) tmp;
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						tmp = sdf.format(date);
					}
					cl.setCellValue(String.valueOf(tmp));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		// 将文件输出到客户端浏览器
		try {
			wb.write(out);
			out.flush();
			out.close();
			wb.dispose();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}


}

2. controller调用代码

@ResponseBody
	@RequestMapping(value = "/exportOrderInfo", produces = "application/json;charset=utf-8;")
	public String exportInfo(HttpServletRequest request, HttpServletResponse response) {
		response.setContentType("application/binary;charset=UTF-8");
			List<OrderInfo> List = orderService.getOrderInfo(paramMap);
	//		String path = "C:\\Users\\Administrator\\Desktop\\work_temp_store";
//		SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
//		String filename = "table_" + format.format(new Date()) + ".xlsx";
//		File saveDir = new File(path);
//		if (!saveDir.exists()) {
//			saveDir.mkdirs();// 如果文件不存在则创建文件夹
//		}
		// 文件路径
//		path = path + File.separator + filename;
//		File tempFile = new File(path); // 初始化临时文件;
//		OutputStream out = new FileOutputStream(tempFile);
//		ServletOutputStream out = new FileOutputStream(tempFile);
			ServletOutputStream out = response.getOutputStream();
			// 时间格式化
			SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
			// 要保存的文件名
			String fileName = new String(("OrderInfo" + format.format(new Date())).getBytes(), "UTF-8");
			response.setContentType("application/vnd.ms-excel");
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Type", "application/force-download");
			response.setHeader("Content-Length", "chunked");
			response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");
			String[] headers = { "订单编号","订单人","订单时间"... };//需与属性顺序保持一致
			ExcelUtil.exportExcel(headers, out, list);
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
		}
		return res.toJsonString();
	}

3.OrderInfo

public class OrderInfo{
	private String orderId;
	private String userName;
	private String createTime;
	....
	//excel更具上面定义顺序遍历属性
}

4. 缺陷是当在web项目中导出时如果数据量过大会造成等待时间过久而超时的问题，解决方案是多线程，异步导出，或者分页分批处理打包成zip或最后合并。