struts2 导出excel

左手的ㄟ右手 2021-09-13 15:16 435阅读 0赞

程序里面将一个表格的数据导出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 {
        
        // 这里是注册业务逻辑biz
    	InterfaceLogBiz logBiz = (InterfaceLogBiz) getBean("interfaceLogBiz");
    	
      // 查询数据集,其实这里是再点击导出按钮的时候将之前的查询条件在次传到action
    
    
    
    
    <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" οnchange="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');
    
    	}
    
    
    
    
    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>
                   <param name="bufferSize">1024</param>
                </result> 
    		</action>
    			
    	</package>