struts2 导出excel

程序里面将一个表格的数据导出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


<!--------------------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>