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); } } }