报表excel导出实现代码

来源：互联网发布：ubuntu撤销命令编辑：程序博客网时间：2024/05/01 06:39

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

1.使用JSP导出excel

导出按钮实现代码：

       <img src="<%=path%>/Img/button/button_excel_enabled.gif" onclick="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"><spanclass="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;}

0 0