Java新建excel文件

本随笔包含创建excel文件、保存在本地，或通过超链接和form表单下载两种写法，为个人笔记

· 新建excel文件，并写入数据，同时保存在本地

/**
 * 将数据存入临时的excel文件并保存在临时文件夹中
 * @param response
 * @param paramList
 */
private void generateTempExcel(HttpServletRequest request, List<Map<String, Object>> paramList, Map<String, Object> resultMap) {
	FileOutputStream fos = null;
	WritableWorkbook wbook = null;
	try {
		// 文件名称
		String templatePath = request.getSession().getServletContext().getRealPath("/template/");
		long nanoTime = System.nanoTime();
    	　　　　 String fileName = templatePath + "电子卡单批量查询结果-" + nanoTime + ".xls";
    	　　　　 String downloadUrl = "/template/电子卡单批量查询结果-" + nanoTime + ".xls";　　// 域名加该地址即可访问到文件
    	　　　　 logger.info("电子卡单批量查询结果文件名称:" + fileName);
		/*StringBuffer fileName = new StringBuffer();
		fileName.append("电子卡单批量查询结果");*/
		fos = new FileOutputStream(fileName);// 取得输出流

		wbook = Workbook.createWorkbook(fos); // 建立excel文件
		WritableSheet wsheet = wbook.createSheet("卡单激活结果", 0); // sheet名称

		// 设置excel标题
		WritableFont wfont = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE);

		WritableCellFormat wcfFC = new WritableCellFormat(wfont);
		wcfFC.setWrap(true);
		wcfFC.setAlignment(Alignment.CENTRE);// 把水平对齐方式指定为居中
		wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);// 把垂直对齐方式指定为居中
		wcfFC.setBackground(Colour.GRAY_25); // 设置单元格的颜色为蓝灰色
		wcfFC.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框

		wsheet.setRowView(0, 500, false); // 设置行高
		wsheet.setColumnView(0, 20);// 设置列宽(25个字符宽)
		wsheet.setColumnView(1, 30);
		wsheet.setColumnView(2, 20);
		wsheet.addCell(new Label(0, 0, "卡单激活结果", wcfFC));
		wsheet.addCell(new Label(0, 1, "agentCode", wcfFC));
		wsheet.addCell(new Label(1, 1, "卡号", wcfFC));
		wsheet.addCell(new Label(2, 1, "激活状态", wcfFC));
		// 合并单元格
		wsheet.mergeCells(0, 0, 2, 0);
		wfont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE);
		wcfFC = new WritableCellFormat(wfont);
		wcfFC.setAlignment(Alignment.CENTRE);// 把水平对齐方式指定为居中
		wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);// 把垂直对齐方式指定为居中
		wcfFC.setWrap(true);
		wcfFC.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框

		jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.0##"); // 设置数字格式
		jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(
				nf); // 设置表单格式
		wcfN.setWrap(true);
		wcfN.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框
		wcfN.setFont(wfont);

		// 开始生成主体内容
		for (int i = 0; i < paramList.size(); i++) {
			EcBatchDetailPaid ecBatchDetailPaid = (EcBatchDetailPaid)paramList.get(i);
			if (ecBatchDetailPaid == null) {
				continue;
			}
			wsheet.addCell(new Label(0, i + 2, ecBatchDetailPaid.getAgentCode() != null ? ecBatchDetailPaid.getAgentCode() : "-", wcfFC));
			wsheet.addCell(new Label(1, i + 2, ecBatchDetailPaid.getCertificationPrintNo() != null ? ecBatchDetailPaid.getCertificationPrintNo() : "-", wcfFC));
			String activeFlag = ecBatchDetailPaid.getActiveFlag();
			if ("0".equals(activeFlag)) {
				activeFlag = "未激活";
			} else if ("1".equals(activeFlag)) {
				activeFlag = "已激活";
			} else {
				activeFlag = "-";
			}
			wsheet.addCell(new Label(2, i + 2, activeFlag, wcfFC));
		}
		
		// 主体内容生成结束
		wbook.write(); // 写入文件
		resultMap.put("downloadUrl", downloadUrl);
	} catch (Exception ex) {
		ex.printStackTrace();
	} finally {
		try {
			if (wbook != null) {
				wbook.close();
			}
			if (fos != null) {
				fos.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

　　

· 新建excel文件，并写入数据，同时支持前端直接通过超链接或者form表单下载

/**
 * 导出的excel文件
 */
private void ecardBatchSearchResultExportExcel(HttpServletResponse response, List<Map<String, String>> paramList) {
	BufferedOutputStream out = null;
	WritableWorkbook wbook = null;
	try {
		// 文件名称
		StringBuffer fileName = new StringBuffer();
		fileName.append("电子卡单批量查询结果");
		out = new BufferedOutputStream(response.getOutputStream());// 取得输出流
		response.reset();// 清空输出流

		response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName.toString() + ".xls", "UTF8"));// 这里设置一下让浏览器弹出下载提示框，而不是直接在浏览器中打开

		wbook = Workbook.createWorkbook(out); // 建立excel文件
		WritableSheet wsheet = wbook.createSheet("卡单激活结果", 0); // sheet名称

		// 设置excel标题
		WritableFont wfont = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE);

		WritableCellFormat wcfFC = new WritableCellFormat(wfont);
		wcfFC.setWrap(true);
		wcfFC.setAlignment(Alignment.CENTRE);// 把水平对齐方式指定为居中
		wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);// 把垂直对齐方式指定为居中
		wcfFC.setBackground(Colour.GRAY_25);// 设置单元格的颜色为蓝灰色
		wcfFC.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框
		
		// 设置表头
		wsheet.setRowView(0, 500, false); // 设置行高
		wsheet.setColumnView(0, 20);// 设置列宽(25个字符宽)
		wsheet.setColumnView(1, 30);
		wsheet.setColumnView(2, 20);
		wsheet.addCell(new Label(0, 0, "卡单激活结果", wcfFC));
		wsheet.addCell(new Label(0, 1, "agentCode", wcfFC));
		wsheet.addCell(new Label(1, 1, "卡号", wcfFC));
		wsheet.addCell(new Label(2, 1, "激活状态", wcfFC));
		
		// 合并单元格
		wsheet.mergeCells(0, 0, 2, 0);
		wfont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE);
		wcfFC = new WritableCellFormat(wfont);
		wcfFC.setAlignment(Alignment.CENTRE);// 把水平对齐方式指定为居中
		wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);// 把垂直对齐方式指定为居中
		wcfFC.setWrap(true);
		wcfFC.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框

		jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.0##"); // 设置数字格式
		jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式
		wcfN.setWrap(true);
		wcfN.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框
		wcfN.setFont(wfont);

		// 开始生成主体内容
		for (int i = 0; i < paramList.size(); i++) {
			Map<String, String> childMap = paramList.get(i);
			if (null == childMap || childMap.size() < 1) {
				continue;
			}
			String agentCode = childMap.get("agentCode");
			String certificationPrintNo = childMap.get("certificationPrintNo");
			String activeFlag = childMap.get("activeFlag");
			if ("0".equals(activeFlag)) {
				activeFlag = "未激活";
			} else if ("1".equals(activeFlag)) {
				activeFlag = "已激活";
			} else {
				activeFlag = "-";
			}
			wsheet.addCell(new Label(0, i + 2, agentCode != null ? agentCode : "-", wcfFC));
			wsheet.addCell(new Label(1, i + 2, certificationPrintNo, wcfFC));
			wsheet.addCell(new Label(2, i + 2, activeFlag, wcfFC));
		}
		
		// 主体内容生成结束
		wbook.write(); // 写入文件
	} catch (Exception e) {
		logger.error(e);
	} finally {
		try {
			if (wbook != null) {
				wbook.close();
			}
			if (out != null) {
				out.flush();
			    out.close();
			}
		} catch (Exception e) {
			logger.error(e);
		}
	}
}