报表excel导出实现代码

末蓝、 2022-08-06 15:13 348阅读 0赞

最近好几张报表的excel导出功能，两个系统实现方法不一样，先把这两种方法记下来，方便以后复用^\_^

1.使用JSP导出excel

导出按钮实现代码：

<img src="<%=path%>/Img/button/button_excel_enabled.gif" οnclick="exportExcel()" />

function exportExcel(){
                    document.form1.action="pactReportAjax_getPactScoresReportExcel.action ";
                    document.form1.target="_blank";
                    document.form1.submit();
                }

导出excel的页面实现代码：

<%@ page language="java"  pageEncoding="utf-8"%>
    <%@ taglib prefix="s" uri="/struts-tags"%>
    <%@page contentType="application/msexcel" %> 
    <%String path = request.getContextPath();
    request.setCharacterEncoding( "UTF-8");   
    String fileName = new String("报表名称".getBytes("GBK"),"ISO-8859-1");
    response.setHeader("Content-disposition","attachment; filename="+fileName+".xls" );  
    %> 
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html> 
    <head>
    <style type="text/css">
    table{font-size:12px;}
    th{height:115px}
    .xlsText{mso-number-format:"\@";}    
    .xlsNum{mso-number-format:"0\.00" 2 Decimals;}
    .xlsNum2{mso-number-format:"$\#\,\#\#0\.00";}    
    </style>
    </head>
    	<body  >  
    				<table border=1px width="100%"  >
    					<tr> 
    						<th>内部编号</th>
    						<th>合同编码</th>
    						<th>售后项目(8位)</th>
    						<th>售后项目(5位)</th>
    						<th>售前PA号</th> 
    						<th>合同名称</th> 
    						<th>签约日期</th>
    				    </tr>
    					 
    					<s:iterator value="reportList" id="pactBaseinfo" status="pactInfo">
    						<tr  >
    							<td class='xlsText'> 
    								<s:property value="pact_id"/>
    							</td>
    							<td >
    							   <div class='xlsText'> 
    								<s:property value="pact_code"/>
    							   </div>
    							</td>
    							<td class='xlsText'>
    								 <s:property value="project_code"/> 
    							</td>
    							<td class='xlsText'> 
    							  <s:property value="project_code2"/> 
    							</td>
    							<td class='xlsText'>
    								<s:property value="p_project_code"/>
    							</td>
    							<td class='xlsText'>
    								<s:property value="pact_name"/>
    							</td>
    							<td>
    								<s:date name="sign_date" format="yyyy-MM-dd"/>
    							</td>
    							<td class='xlsNum2'> 
    							    <s:if test='exist_freetax == "1"'>
    									是
    								</s:if>
    								<s:else>
    								           否
    								</s:else> 
    							</td>
    					   </tr>
    					</s:iterator>
    				 
    				</table>
    				 
    	</body>
    </html>

2.使用Java导出excel

导出按钮实现：

<a href='#' class="g_a"> <span class="g_b" id="a_excel"><span
    								class="g_c"><font color="black">导出到excel</font> </span> </span> </a>

//导出excle
        function doExcel(){
    	var form = $('#listForm');
    	var url=getRootPath() + "/mreport/buReportForm_doExcel.action?method=sumarry";
    	form.attr("action",url);
    	form.submit();
    }

java导出实现代码：

/**
    	 * 导出到excel
    	 * 
    	 * @return
    	 * @throws Exception
    	 */
    	public String doExcel() throws Exception {
    		HttpServletRequest request = ServletActionContext.getRequest();
    		HttpSession session = request.getSession();
    		HttpServletResponse response = ServletActionContext.getResponse();
    		AiiEmployeeInfoCurV userLoginInfo = (AiiEmployeeInfoCurV) session.getAttribute("USERINFO");
    		String loginAlert = "";
    		if (userLoginInfo == null) {
    			loginAlert = "alert('登陆失败！')";
    			return "loginError";
    		}
    		Long pid = userLoginInfo.getPersonID();
    		BUReportFormVO buvo = new BUReportFormVO();
    		buvo.setProjectCode(projectCode);
    		buvo.setProjectName(projectName);
    		buvo.setManagerID(managerID);
    		buvo.setAreaID(areaID);
    		buvo.setProvinceID(provinceID);
    		buvo.setOrgID(orgID);
    		buvo.setPersonID(pid.toString());
    		log.debug("parameter is projectCode=" + projectCode + "projectName=" + projectName + "managerID" + managerID + "areaID=" + areaID + "provinceID=" + provinceID + "orgID="
    				+ orgID + "pid=" + pid);
    		String method = request.getParameter("method");
    		String date = new SimpleDateFormat("yyyyMMdd").format(new Date());
    		if (StringUtils.isNotEmpty(method) && "projectManager".equals(method)) {
    			buvo.setTitle("XX报表" + (date));
    			buvo.setReportType("manager");
    		} else {
    			buvo.setTitle("XXX报表" + (date));
    			buvo.setReportType("bu");
    		}
    		buvo.setCompanyID(companyId);
    		buvo.setBuID(sbuId);
    		resultList = buReportFormService.resultSetToExcel(buvo);
    		log.debug("resultList size is " + resultList.size());
    		ExcelBean eb = new ExcelBean();
    
    		String FIELDS = "projectCode-XXX编码|labbudget-XX预算-money|laboractual-截止到当前累计发生人工日-money|leavelabor-截止到当前剩余人工日-money|laborpercent_new-截止到当前累计发生人工日占总预算比例-percent|"
    				+ "ssfbudget-XXX费-money|wbfbudget-外包费-money|pxfbudget-培训费-money|ssfactual-实施费-money|wbfactual-外包费-money|"
    				+ "pxfactual-XXX费-money|leavessf-实施费-money|leavewbf-外包费-money|leavepxf-培训费-money|costpercent_new-截止到当前累计发生成本 占总直接费用预算的比例-percent|pocNew-项目截止到当前POC(供参考)-percent|"
    				+ "mgrApproveTran-项目经理审核通过-money|mgrUnApproveTran-XX审核-money|accruedactual-XXX预提/冲预提-money|thisMonthUnAcc-当月冲预提-money|actualMgrApproveZTAndCYTAndThisMonth-实际+在途（项目经理审核通过） +当月冲预提-money|"
    				+ "costpercent1_new-（XXX当月冲预提） /总预算-percent";
    
    		eb.setTITLE(buvo.getTitle());
    		eb.setResultList(resultList);
    		eb.setFIELDS(FIELDS.split("\\|"));
    		HttpServletResponse resp = ServletActionContext.getResponse();
    		response.setContentType("application/octet-stream;");
    
    		String filename = null;
    		try {
    			filename = java.net.URLEncoder.encode(buvo.getTitle().toString(), "UTF-8") + ".xls";
    		} catch (UnsupportedEncodingException e) {
    			log.debug("export excel fail", e);
    			e.printStackTrace();
    			return "error";
    		}
    
    		response.addHeader("Content-Disposition", "attachment;filename=" + filename);
    		eb.setOut(response.getOutputStream());
    		// eb.setFileUrl("F:/BudgetNew/WebRoot/excelModel/BU报表查询.xls");
    		String saveUrl = "";
    		if (StringUtils.isNotEmpty(method) && "projectManager".equals(method)) {
    			saveUrl = request.getRealPath("/") + "excelModel" + File.separator + "报表查询.xls";
    		} else {
    			saveUrl = request.getRealPath("/") + "excelModel" + File.separator + "XX报表查询.xls";
    		}
    
    		eb.setFileUrl(saveUrl);
    		setEB(eb);
    		doExportByTemplet();
    		return "blank";
    	}
    
    	// 根据模板文件导出Excle(为了导出合计)
    	public void doExportByTemplet() throws Exception {
    		splitFields();
    		prepareHeadByTemplet();
    		writeDataTwo();
    		wb.write(EB.getOut());
    		EB.getOut().flush();
    		EB.getOut().close();
    	}
    
    	private void splitFields() throws Exception {
    		String[] fields = EB.getFIELDS();
    		String[] tempfied;
    		HEADS = new ArrayList<String>();
    		CELLS = new ArrayList<String>();
    		CELLDATATYPES = new ArrayList<Integer>();
    		CELLDATAFORMATS = new ArrayList<Short>();
    		for (String field : fields) {
    			tempfied = field.split("-");
    			HEADS.add(tempfied[1]);
    			CELLS.add(tempfied[0]);
    			if (3 == tempfied.length) {
    
    				if (tempfied[2].equalsIgnoreCase("percent")) {
    					CELLDATAFORMATS.add(HSSFDataFormat.getBuiltinFormat(PERCENT_FORMAT));
    				} else if (tempfied[2].equalsIgnoreCase("money")) {
    					CELLDATAFORMATS.add(HSSFDataFormat.getBuiltinFormat(MONEY_FORMAT));
    				} else {
    					CELLDATAFORMATS.add((short) 0);
    				}
    
    				CELLDATATYPES.add(HSSFCell.CELL_TYPE_NUMERIC);
    			} else {
    				CELLDATATYPES.add(HSSFCell.CELL_TYPE_STRING);
    				CELLDATAFORMATS.add((short) 0);
    			}
    		}
    	}
    
    	private void prepareHeadByTemplet() throws Exception {
    		try {
    			String fileUrl = EB.getFileUrl();
    			File f = new File(fileUrl);
    			InputStream in = new FileInputStream(f);
    			wb = new HSSFWorkbook(in);
    			HSSFSheet sheet = wb.getSheetAt(0);
    			beginRowNum = sheet.getLastRowNum() + 1;
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    
    	}
    
    	private void writeDataTwo() throws Exception {
    		// 货币格式
    		HSSFCellStyle styleMoney = null;
    
    		// 百分比格式
    		HSSFCellStyle stylePercent = null;
    		HSSFCellStyle style = wb.createCellStyle();
    		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    		// 设置这些样式
    		// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    		// 生成一个字体
    
    		HSSFFont fontH = wb.createFont();
    		fontH.setFontName("宋体");
    		fontH.setFontHeightInPoints((short) 9);
    		style.setFont(fontH);
    		int rows = beginRowNum;
    		int cursheet = 0;
    		HttpServletRequest request = ServletActionContext.getRequest();
    		HSSFSheet sheet = wb.getSheetAt(cursheet);
    		String method = request.getParameter("method");
    
    		List<Map<String, Object>> rslist = EB.getResultList();
    		for (Map<String, Object> rs : rslist) {
    			if (rows > SINGLESHEET_MAXROWS) {
    				cursheet++;
    				sheet = wb.getSheetAt(cursheet);
    				rows = beginRowNum; // 
    
    			}
    			HSSFRow row = sheet.createRow(rows);
    			for (int j = 0; j < CELLS.size(); j++) {
    				HSSFCell cell = row.createCell(j);
    
    				Object cellValue = rs.get(CELLS.get(j));
    
    				cell.setCellType(CELLDATATYPES.get(j));
    				if (CELLDATAFORMATS.get(j) == HSSFDataFormat.getBuiltinFormat(MONEY_FORMAT)) {
    					if (styleMoney == null) {
    						styleMoney = wb.createCellStyle();
    						styleMoney.cloneStyleFrom(style);
    						styleMoney.setDataFormat(HSSFDataFormat.getBuiltinFormat(MONEY_FORMAT));
    					}
    					cell.setCellStyle(styleMoney);
    				} else if (CELLDATAFORMATS.get(j) == HSSFDataFormat.getBuiltinFormat(PERCENT_FORMAT)) {
    					if (stylePercent == null) {
    						stylePercent = wb.createCellStyle();
    						stylePercent.cloneStyleFrom(style);
    						stylePercent.setDataFormat(HSSFDataFormat.getBuiltinFormat(PERCENT_FORMAT));
    					}
    					if (cellValue != null && StringUtils.isNotEmpty(String.valueOf(cellValue))) {
    						cellValue = com.linkage.framework.pub.utils.StringUtils.formatDou(cellValue) * 0.01;
    					}
    					cell.setCellStyle(stylePercent);
    				} else {
    					cell.setCellStyle(style);
    				}
    
    				if (null == cellValue || StringUtils.isBlank(String.valueOf(cellValue))) {
    					continue;
    				}
    				if (HSSFCell.CELL_TYPE_NUMERIC == CELLDATATYPES.get(j).intValue()) {
    					cell.setCellValue(Double.parseDouble(String.valueOf(cellValue)));
    				} else {
    					if (null != rs.get(CELLS.get(j))) {
    						cell.setCellValue(objToStr(cellValue));
    					}
    				}
    
    				style.setDataFormat(CELLDATAFORMATS.get(j));
    
    			}
    			rows++;
    
    			if (rows == rslist.size() + 2) {
    				HSSFRow row2 = sheet.createRow(rows + 1);
    				for (int j = 0; j < CELLS.size(); j++) {
    					HSSFCell cell = row2.createCell(j);
    					cell.setCellStyle(style);
    					cell.setCellType(CELLDATATYPES.get(j));
    					styleMoney = wb.createCellStyle();
    					styleMoney.cloneStyleFrom(style);
    					styleMoney.setDataFormat(HSSFDataFormat.getBuiltinFormat(MONEY_FORMAT));
    					cell.setCellStyle(styleMoney);
    				}
    
    				if ("sumarry".equals(method) || "projectManager".equals(method)) {
    					HSSFRow rowTit = sheet.getRow(rows + 1);
    					if (resultList.size() > 0) {
    						rowTit.getCell(9).setCellValue("合计"); 
    						rowTit.getCell(25).setCellValue(Double.parseDouble(((Map) resultList.get(0)).get("allMgrApproveTran").toString()));
    						rowTit.getCell(26).setCellValue(Double.parseDouble(((Map) resultList.get(0)).get("allMgrUnApproveTran").toString()));
    						rowTit.getCell(27).setCellValue(Double.parseDouble(((Map) resultList.get(0)).get("allaccruedactual").toString()));
    						rowTit.getCell(28).setCellValue(Double.parseDouble(((Map) resultList.get(0)).get("allThisMonthUnAcc").toString()));
    						rowTit.getCell(29).setCellValue(Double.parseDouble(((Map) resultList.get(0)).get("allActualMgrApproveZTAndCYTAndThisMonth").toString()));
    						rowTit.getCell(30).setCellValue("");
    
    					}
    				}
    
    			}
    		}
    
    	}
    
    	/**
    	 * 对象转字符串
    	 * 
    	 * @param obj
    	 * @return
    	 */
    	private String objToStr(Object obj) {
    		try {
    			if (obj.getClass().getName().equals("java.util.Date")) {
    				String str = DateUtil.format((java.util.Date) obj, "yyyy-MM-dd");
    				if (str.endsWith(" 00:00:00")) {
    					return str.substring(0, str.length() - 9);
    				}
    				return str;
    			} else {
    				return String.valueOf(obj);
    			}
    		} catch (Exception e) {
    			System.out.println(e.toString());
    			e.printStackTrace();
    
    			return String.valueOf(obj);
    		}
    	}

3.存储过程调用：

public List queryBUReportForm(BUReportFormVO vo) {
    		List resultList = new ArrayList();
    		DecimalFormat df = new DecimalFormat("#,###,###,##0.00");
    		try {
    			String sql = "{call proc_get_XXX_report(?,?,?,?,?,?,?,?,?,?)}";
    			ResultSet rs = FrameworkApplication.getBaseJdbcDAO().queryForResultSet(
    					sql,
    					new String[] { vo.getPersonID(), vo.getProjectCode(), vo.getProjectName(), vo.getManagerID(), vo.getAreaID(), vo.getProvinceID(), vo.getOrgID(),
    							vo.getReportType(), vo.getCompanyID(), vo.getBuID() });
    			int i = 0;
    			if (null != rs) {
    				while (rs.next()) {
    					Map map = new HashMap();
    					map.put("projectID", defaultIfEmpty(rs.getString("PROJECT_ID"), "")); 
    					map.put("costpercent1_new", costpercent1.replaceAll("%", ""));
    
    					String poc = rs.getString("poc");
    					map.put("poc", poc);
    					// map.put("pocNew", poc.replaceAll("%", ""));
    					map.put("pocNew", poc.substring(0, poc.indexOf("%")));
    					
    					//项目本位币种
    					map.put("projfuncCurrencyCode", rs.getString("projfunc_currency_code"));
    					i++;
    
    					if (i == 1) {
    						// 合计
    						map.put("alllabbudget", rs.getDouble("alllabbudget"));
    						map.put("alllabactual", rs.getDouble("alllabactual")); 
    						map.put("allActualMgrApproveZTAndCYTAndThisMonth", rs.getDouble("allactual_mgr_approve_zt_cyt_this_month"));
    
    						map.put("allaccruedactual", rs.getDouble("allaccruedactual"));
    					}
    
    					resultList.add(map);
    				}
    
    			}
    
    		} catch (Exception e) {
    			log.debug("query Budget.dbo.proc_get_project_report error", e);
    			e.printStackTrace();
    		}
    
    		return resultList;
    	}