java 导出 Excel 封装

 

 

 

 

 

    导出列表, 表头及主体内容. 可以对某列进行二次处理

	public void exportExcelA(RoomModel rrm, HttpServletRequest request, HttpServletResponse response) throws Exception {

		List<RoomModel> resultListA = 

		if (resultListA != null && !resultListA.isEmpty()) {

			String fileName = String.format("%#X.excel", System.currentTimeMillis());

			int[] widths = { 15, 30, 20, 20, 40, 10, 15 };
			String[] titles = { "预约人", "预约单位", "预约日期", "审核结果", "活动内容", "参与人数", "批准人" };
			String[] columns = { "name", "dept", "date", "result", "content", "sum", "personName" };

			String[] special = { "result" };
			final String[] status_approved = { "待审核", "审核通过", "未通过", "待取消", "已取消" };
			IStatusValue[] vs = { new IStatusValue() {
				public Object get(Object obj) {
					if (obj != null && Pattern.matches("\\d+", obj.toString())) {
						return status_approved[Integer.parseInt(obj.toString()) % status_approved.length];
					}
					return "";
				}
			} };

			this.export2excel(response, widths, titles, columns, resultListA, request.getCharacterEncoding(), fileName, special, vs);

		}

	}

	interface IStatusValue {
		Object get(Object obj);
	}

	private void export2excel(HttpServletResponse response, int[] widths, String[] titles, String[] columns, List<?> list, String encode, String fn) throws Exception {
		this.export2excel(response, widths, titles, columns, resultListA, request.getCharacterEncoding(), fileName, null, null);
	}

	private void export2excel(HttpServletResponse response, int[] widths, String[] titles, String[] columns, List<?> list, String encode, String fn, String[] special, IStatusValue[] vs) throws Exception {

		OutputStream os = response.getOutputStream();
		response.reset();
		response.setCharacterEncoding(encode);
		response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fn + ".xls", "UTF-8"));
		response.setContentType("application/msexcel");
		WritableWorkbook workbook = Workbook.createWorkbook(os);
		WritableSheet sheet = workbook.createSheet(fn, 0);

		WritableFont font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
		WritableCellFormat ffmt = new WritableCellFormat(font);
		ffmt.setAlignment(Alignment.CENTRE);
		ffmt.setVerticalAlignment(VerticalAlignment.CENTRE);
		ffmt.setWrap(false);

		int line = 0;
		{
			for (int i = 0, l = titles.length; l > i; ++i) {
				Label lbl = new Label(i, line, titles[i], ffmt);
				sheet.addCell(lbl);
				sheet.setColumnView(i, widths[i]);
			}
		}
		{

			for (Object itm : list) {
				line++;

				for (int i = 0, l = columns.length; l > i; ++i) {
					String col = columns[i];
					Field field = itm.getClass().getDeclaredField(col);
					field.setAccessible(true);
					{
						Object obj = field.get(itm);
						if (obj == null) {
							obj = new String("");
						}
						else {
							
							int idx = 0;
							boolean exists = false;
							for (String c : special) {
								if (c.equals(col)) {
									exists = true;
									break;
								}
								idx++;
							}
							if (exists)
								obj = vs[idx].get(obj);
							
						}

						Label lbl = new Label(i, line, obj.toString(), ffmt);
						sheet.addCell(lbl);
					}
					field.setAccessible(false);
				}
			}
		}

		workbook.write();
		workbook.close();
		os.close();
	}