程序里面将一个表格的数据导出EXCEL是再常规不过的需求了,可之前一直都没怎么注意过这个问题,这次自己查看了几篇博文,针对Struts2的导出 其中一种使用java反射的方式来实现数据的导出 很方便,也很快捷 就针对现在Struts2开发时的属性getter/setter注解到jsp上 的实现方式,为了图方便,我就直接拿我做的项目里面pojo和service以及action来说事了,Service中有一个查询结果集的接口 Pojo 实体对象Action 控制jsp的展现使用了poi-3.7-20101029这个包Action中的代码:public class ExcelAction extends BaseAction { private static final SimpleDateFormat yyyy_MM_dd = new SimpleDateFormat("yyyy-MM-dd"); private static final long serialVersionUID = 1L; private InterfaceLog interfaceLog; // 这是pojo对象InputStream excelStream; // 这个输入流对应上面struts.xml中配置的那个excelStream，两者必须一致 String fileName; // 这个名称就是用来传给上面struts.xml中的${fileName}的 public String exportExcel() throws Exception { // 这里是注册业务逻辑bizInterfaceLogBiz logBiz = (InterfaceLogBiz) getBean("interfaceLogBiz"); // 查询数据集,其实这里是再点击导出按钮的时候将之前的查询条件在次传到action<!--------------------jsp 简要代码开始---------------><s:form id="findListForm" name="findListForm" action="findInterfaceLogList"method="POST" theme="simple" namespace="/interfaces/log"><input type="hidden" id="isQuery" name="isQuery" value="1" /><input type="hidden" id="currentPage" name="currentPage" value="1" /><input type="hidden" id="interfaceId" name="interfaceId"value="${interfaceConfig.id }" /><table width="100%" cellpadding="0" cellspacing="1"> <tr><td class="tdr" width="10%">接口名称</td><td class="tdl"><select id="interfaceName" name="interfaceLog.interfaceName" onchange="selectedVal()"><option value="">--请选择--</option><s:iterator value="#request.nameList" id="interfaceList" status="st"> <option value="${interfaceList.name}">${interfaceList.name}</option></s:iterator></select></td></td><td class="tdr">操作名称</td><td class="tdl"><select id="operatorName" name="interfaceLog.operatorName"><option value="">--请选择--</option> <option value="修改账号" <s:if test="interfaceLog.operatorName=='修改账号'">selected="selected"</s:if>>修改账号</option> <option value="删除账号" <s:if test="interfaceLog.operatorName=='删除账号'">selected="selected"</s:if>>删除账号</option></select></td><td class="tdr">操作结果</td><td class="tdl"> <select id="operrtorCode" name="interfaceLog.operrtorCode"><option value="">--请选择--</option> <option value="0" <s:if test="interfaceLog.operrtorCode==0">selected="selected"</s:if>>成功</option> <option value="1" <s:if test="interfaceLog.operrtorCode==1">selected="selected"</s:if>>失败</option> <option value="2" <s:if test="interfaceLog.operrtorCode==2">selected="selected"</s:if>>警告</option></select></td></tr><tr><td class="tdr" width="10%">操作信息</td><td class="tdl"><input class="inputtext" name="interfaceLog.info" type="text" value="${interfaceLog.info}" size="29" /></td><td class="tdr" >操作对象</td><td class="tdl"> <select id="operationObj" name="interfaceLog.operationObj"><option value="">--请选择--</option> <option value="cc" <s:if test="interfaceLog.operationObj=='cc'">selected="selected"</s:if>>cc</option> <option value="ccw" <s:if test="interfaceLog.operationObj=='ccw'">selected="selected"</s:if>>ccw</option> <option value="wwww" <s:if test="interfaceLog.operationObj=='wwww'">selected="selected"</s:if>>wwww</option></select></td><td class="tdr" width="10%">调用者</td><td class="tdl"> <select id="caller" name="interfaceLog.caller"><option value="">--请选择--</option> <option value="zfd" <s:if test="interfaceLog.caller=='zfd'">selected="selected"</s:if>>zfd</option> <option value="xx" <s:if test="interfaceLog.caller=='xx'">selected="selected"</s:if>>xx</option> <option value="eeee" <s:if test="interfaceLog.caller=='eeee'">selected="selected"</s:if>>eeee</option></select></td></tr><tr><td class="tdr" width="10%">记录时间</td><td class="tdl">从<input id="startTime" class="inputtext" name="interfaceLog.filterStartTime" type="text" size="26"/>到<input id="endTime" class="inputtext" name="interfaceLog.filterEndTime" type="text" size="26" /></td></tr><tr><td colspan="6" class="tdbut"><sim:a actionName="findInterfaceLogListAction" hrefUrl="#"onClick="queryInterfaceLog();"><img src="<sim:stylepath/>/images/button_03.jpg" /></sim:a><sim:a actionName="excelAction" hrefUrl="#"onClick="exportInterfaceLog();"><img src="<sim:stylepath/>/images/button_21.jpg" /></sim:a></td></tr></table></s:form></div>这是我jsp form中的写法插图是代码的样式结构Js:function exportInterfaceLog(){$('#findListForm').attr('action','exportExcel.action');$('#findListForm').submit(); $('#findListForm').attr('action','findInterfaceLogList.action');}<!--------------------jsp 简要代码结束--------------->Pager<InterfaceLog> dataList = logBiz.getPageList(interfaceLog, Pager.pageSize, -1); // 做过struts2开发的人都会用到直接传pojo对象为参数查询数据这里是 if (dataList == null) { return ERROR; } else { HSSFWorkbook workbook = getWorkbook(dataList.getList()); if (workbook != null) { try { Calendar c = Calendar.getInstance(); int year = c.get(Calendar.YEAR); int month = c.get(Calendar.MONTH) + 1; String month_ = new String("" + month); if (month < 10) { month_ = "0" + month; } int day = c.get(Calendar.DAY_OF_MONTH); String day_ = new String("" + day); if (day < 10) { day_ = "0" + day; } // 第四步：将工作簿写入最上面定义的InputStream流——名称为excelStream，这个名字对应struts.xml中配置的inputName参数 this.workbook2InputStream(workbook, year + "-" + month_ + "-" + day_ + ""); return SUCCESS; } catch (IOException e) { return ERROR; } } else { return ERROR; } } } public InputStream getExcelStream() { return excelStream; } public void setExcelStream(InputStream excelStream) { this.excelStream = excelStream; } public String getFileName() { return fileName; } public void setFileName(String fileName) { this.fileName = fileName; } // 将Workbook写入到InputStream private void workbook2InputStream(HSSFWorkbook workbook, String fileName) throws Exception { this.fileName = fileName; // 设置fileName ByteArrayOutputStream baos = new ByteArrayOutputStream(); workbook.write(baos); baos.flush(); byte[] aa = baos.toByteArray(); excelStream = new ByteArrayInputStream(aa, 0, aa.length); baos.close(); } private HSSFWorkbook getWorkbook(List<InterfaceLog> list) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("sheet1"); // 这里的columnMethods中的值就是pojo里面的getter方法名,是用来取值 String[] columnMethods = new String[] { "getId", "getOperationObj", "getRecordTime", "getOperatorName", "getCaller", "getOperrtorCode", "getInfo", "getInterfaceName" }; // 这里的columnNames 中的值就是导出的excel里面的标题String[] columnNames = new String[] { "ID", "操作对象", "时间", "操作名称", "调用者", "操作结果", "操作信息", "接口名称" }; InterfaceLog exp = list.get(0); HSSFRow row = sheet.createRow(0); // 创建第1行，也就是输出表头 HSSFCell cell; for (int i = 0; i < columnNames.length; i++) { cell = row.createCell(i); // 创建第i列 cell.setCellValue(new HSSFRichTextString(columnNames[i])); } // 下面是输出各行的数据 for (int i = 0; i < list.size(); i++) { exp = (InterfaceLog) list.get(i); row = sheet.createRow(i + 1);// 创建第i+1行 for (int j = 0; j < columnMethods.length; j++) { cell = row.createCell(j);// 创建第j列 Method method; method = exp.getClass().getMethod(columnMethods[j]); // 这里用到了反射机制，通过方法名来取得对应方法返回的结果对象 Object obj = method.invoke(exp); String operationCode =""; if ("getOperrtorCode".equals(method .getName())) { if ("0".equals(obj.toString())) { operationCode="成功"; } else if ("1".equals(obj.toString())) { operationCode="失败"; } else if ("2".equals(obj.toString())) { operationCode="警告"; } cell.setCellValue(operationCode); } else if("getRecordTime".equals(method.getName())){ cell.setCellValue(yyyy_MM_dd.format(obj)); } else{ cell.setCellValue(obj.toString()); // 往excel写数据 } } } return workbook; } public InterfaceLog getInterfaceLog() { return interfaceLog; } public void setInterfaceLog(InterfaceLog interfaceLog) { this.interfaceLog = interfaceLog; } public Long getId() { return id; } public void setId(Long id) { this.id = id; }}代码中标蓝的是你的数据处理的,根据自己需要来修改String[] columnMethods = new String[] { "getId", "getOperationObj", "getRecordTime", "getOperatorName", "getCaller", "getOperrtorCode", "getInfo", "getInterfaceName" }; // 这里的columnNames 中的值就是导出的excel里面的标题String[] columnNames = new String[] { "ID", "操作对象", "时间", "操作名称", "调用者", "操作结果", "操作信息", "接口名称" }; 这个columnMethods,columnNames 是根据自己导出的数据需求修改的,前者对应的是pojo里setter方法名,后者是对象excel中的每列的标题 // 这里是struts.xml配置文件 <package name="log" namespace="/interfaces/log" extends="sim-default"><action name="exportExcel" class="com.sense.sim.action.interfaces.ExcelAction" method="exportExcel"> <result name="success" type="stream"> <param name="contentType">application/vnd.ms-excel</param> <param name="inputName">excelStream</param> <param name="contentDisposition">attachment;filename="${fileName}.xls"</param><!--这里配置的是excel最终下载的名称 --> <param name="bufferSize">1024</param> </result> </action></package>
