1.定义jsp
<%@ page language="java" import="java.util.*" pageEncoding="gbk" isELIgnored="false"%> <%@taglib uri="/struts-tags" prefix="s"%> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>报表</title> <SCRIPT type=text/javascript src="<%=path%>/script/My97DatePicker/WdatePicker.js"></SCRIPT> <SCRIPT type=text/javascript src="<%=path%>/script/jquery-1.3.2.min.js"></SCRIPT> </head> <script type="text/javascript"> function gotosearch(){ if($("#beginDate").val()=="" || $("#endDate").val()==""){ alert("请选择开始时间和结束时间,不能为空！"); return false; } document.forms[0].submit(); } function exportToExcel(){ if($("#beginDate").val()=="" || $("#endDate").val()==""){ alert("请选择开始时间和结束时间,不能为空！"); return false; } document.forms[0].action="acvsExpensesReport_exportToExcel.action"; document.forms[0].submit(); } </script> <body scroll="yes"> <h2 align="center">费用化辅料报表</h2> <div id="main"> <form id="searchForm" name="searchForm" method="post" action="acvsExpensesReport_searchData.action"> <table cellspacing="1"> <tr> <td width="10%">流程名称: </td> <td > <select name="flowname"> <option value ="WGG-SC 生产用费用化辅料管理流程">WGG-SC 生产用费用化辅料管理流程</option> <option value ="MB1A Y01辅料投成本中心不产生库存二期优化流程">MB1A Y01辅料投成本中心不产生库存二期优化流程</option> <option value="IWMS Y01费用化辅料流程">IWMS Y01费用化辅料流程</option> <option value="Y01费用化辅料（发货）">Y01费用化辅料（发货）</option> <option value="Y01 费用化辅料(预算)">Y01 费用化辅料(预算)</option> </select> </td> </tr> <tr> <td >开始时间：</td> <td> <input name="beginDate" id="beginDate" type="text" οnclick="WdatePicker({skin:'ext',isShowClear:false,readOnly:true})" > </td> </tr> <tr> <td >结束时间：</td> <td> <input name="endDate" id="endDate" type="text" οnclick="WdatePicker({skin:'ext',isShowClear:false,readOnly:true})" > </td> </tr> <tr> <!-- <td><input type="button" style="color:blue; text-align:center ;width: 100px;height: 30px" name="search" value="查询" οnclick="gotosearch()"/> </td> --> <td><input type="button" style="color:blue; text-align:center ;width: 100px;height: 30px" name="search" value="导出" οnclick="exportToExcel()"/> </td> </tr> </table> <table cellpadding="0" cellspacing="0" border="0" width="100%" align="center"> <tr> <td class="ta_01" align="center" bgcolor="#f5fafe"> <table cellspacing="0" cellpadding="1" rules="all" bordercolor="gray" border="1" id="DataGrid1"> <tr style="FONT-WEIGHT:bold;HEIGHT:15px;BACKGROUND-COLOR:#afd1f3"> <td>流程实例编号</td> <td>提报人工号</td> <td>提报人姓名</td> <td>SAP返回的内容</td> <td>工厂</td> <td align="center">评估类型</td> <td align="center">发货库存地点</td> <td align="center">物料号</td> <td align="center">基本计量单位</td> <td align="center">数量</td> <td align="center">成本中心</td> <td align="center">供应商代码</td> <td align="center">特殊库存标示</td> <td align="center">物料描述</td> <td align="center">移动类型</td> <td align="center">凭证抬头文本</td> <td align="center">线体</td> <td align="center">小微代码</td> </tr> <s:if test="#request.formDatas!=null"> <s:iterator value="#request.formDatas" var="formData"> <tr οnmοuseοver="this.style.backgroundColor = 'white'" οnmοuseοut="this.style.backgroundColor = '#F5FAFE';"> <td align="center"><s:property value="#formData.flowid"/></td> <td align="center"><s:property value="#formData.userno"/></td> <td align="center"> <s:property value="#formData.username"/> </td> <td align="center" > <s:property value="#formData.message"/> </td> <td align="center" width="8%"> <s:property value="#formData.werks"/> </td> <td align="center" width="10%"> <s:property value="#formData.charg"/> </td> <td align="center" width="10%"> <s:property value="#formData.lgort"/> </td> <td align="center" width="9%" style="HEIGHT: 22px"> <s:property value="#formData.matnr"/> </td> <td align="center" width="9%" style="HEIGHT: 22px"> <s:property value="#formData.erfme"/> </td> <td align="center" width="9%" style="HEIGHT: 22px"> <s:property value="#formData.erfmg"/> </td> <td align="center" width="9%" style="HEIGHT: 22px"> <s:property value="#formData.kostl"/> </td> <td align="center" width="9%" style="HEIGHT: 22px"> <s:property value="#formData.lifnr"/> </td> <td align="center" width="9%" style="HEIGHT: 22px"> <s:property value="#formData.sobkz"/> </td> <td align="center" width="9%" style="HEIGHT: 22px"> <s:property value="#formData.maktx"/> </td> <td align="center" width="9%" style="HEIGHT: 22px"> <s:property value="#formData.bwartwa"/> </td> <td align="center" width="9%" style="HEIGHT: 22px"> <s:property value="#formData.bktxt"/> </td> <td align="center" width="9%" style="HEIGHT: 22px"> <s:property value="#formData.line"/> </td> <td align="center" width="9%" style="HEIGHT: 22px"> <s:property value="#formData.sybdm"/> </td> </tr> </s:iterator> </s:if> </table> </td> </tr> </table> </form> </div> </body> </html>
2.配置struts2配置文件
<action name="acvsExpensesReport_*" class="expensesReport.action.AcvsExpensensReportSearch" method="{1}"> <result name="home">/Reports/ACVS/acvsExpenseReport.jsp</result> <result name="SUCCESS">/Reports/ACVS/acvsExpenseReport.jsp</result> <result name="excel" type="stream"> <param name="contentType">application/vnd.ms-excel</param>
<!--需要action当中返回一个输入流--> <param name="inputName">excelStream</param>
<!--为excel设置标题 需要通过ognl从action当中获取 当然在这写死也是没关系的--> <param name="contentDisposition">filename="${excelFileName}"</param> <param name="bufferSize">1024</param> </result> </action>
3.编写action。。。
/** * 点击导出后 导出到excel当中 * @return */ public String exportToExcel(){
//从页面中获取参数 flowname=request.getParameter("flowname"); beginDate=request.getParameter("beginDate"); endDate=request.getParameter("endDate"); String tableName=iac.findTableNameByFlowName(flowname);
//构造excel当中的数据和标头 ArrayList fieldName=iac.getExcelTitleByflowName(flowname); ArrayList fieldData=iac.searchExpenseValueByDateAndFlowName(tableName,flowname,beginDate,endDate); try {
//标题名 String excelName=flowname+"("+beginDate+"到"+endDate+")数据"; excelFileName=new String(excelName.getBytes("gb2312"), "iso8859-1")+".xls"; } catch (UnsupportedEncodingException e) { e.printStackTrace(); }
//构造输入流 excelStream=iac.getExcelInputStreamExpensesReport(fieldName,fieldData); return "excel"; }
4.iac调用的service层的方法代码
/** * 获取文件输入流来将数据写入到excel当中 */ public InputStream getExcelInputStreamExpensesReport(ArrayList fieldName, ArrayList fieldData) { ByteArrayOutputStream out = new ByteArrayOutputStream(); HSSFWorkbook workBook = new HSSFWorkbook(); int rows = fieldData.size(); int sheetNum=0; /* * 2016/9/9修改 当数据集合没有数据的时候也会将表头导出 下载模板功能也用到此过程 * begin */ if (rows!=0 && rows % SPLIT_COUNT == 0) { sheetNum = rows / SPLIT_COUNT; } else if(rows!=0 && rows % SPLIT_COUNT != 0){ sheetNum = rows / SPLIT_COUNT + 1; }else{ sheetNum=1; } //end for (int i = 1; i <= sheetNum; i++) { HSSFSheet sheet = workBook.createSheet("Page " + i); HSSFRow headRow = sheet.createRow((short) 0); for (int j = 0; j < fieldName.size(); j++) { HSSFCell cell = headRow.createCell((short) j); //添加样式 cell.setCellType(HSSFCell.CELL_TYPE_STRING); //设置所有单元格的宽度 sheet.setColumnWidth((short)j, (short)6000); //创建样式 HSSFCellStyle cellStyle=workBook.createCellStyle(); //创建字体样式 HSSFFont font=workBook.createFont(); //将字体加粗 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //设置字体颜色 font.setColor(HSSFColor.RED.index); //将新设置的字体属性放置到样式当中 cellStyle.setFont(font); if(fieldName.get(j) != null){ cell.setCellStyle(cellStyle); cell.setCellValue((String) fieldName.get(j)); }else{ cell.setCellStyle(cellStyle); cell.setCellValue("-"); } } for (int k = 0; k < (rows < SPLIT_COUNT ? rows : SPLIT_COUNT); k++) { HSSFRow row = sheet.createRow((short) (k + 1)); //将数据内容放入excel单元格 ArrayList rowList = (ArrayList) fieldData.get((i - 1) * SPLIT_COUNT + k); for (int n = 0; n < rowList.size(); n++) { HSSFCell cell = row.createCell((short) n); if(rowList.get(n) != null){ cell.setCellValue((String) rowList.get(n).toString()); }else{ cell.setCellValue(""); } } } } try { workBook.write(out); } catch (Exception e) { e.printStackTrace(); } return new ByteArrayInputStream(out.toByteArray()); }
完成excel的导出 相关 poi jar包自己导入 这只是个范本。。一般的excel导出都是通过这种方式实现的。。。
