有没有导出网页数据至Excel的通用方法？

Question

如题
相信很多做Web开发的朋友都和我一样要经常写一些代码导出必要的数据至Excel中
但是很多代码都是重复在写，请问有没有一种通用模板的方法实现即在想导出Excel的页面中调用对应Action的对应方法即可
具体实现交由模板类

Answer 1

我自己写了个 exportToExcelTemplet.jsp 代码如下：<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<%@ page language="java" contentType="text/xml; charset=UTF-8"%>
<%@include file="tlds.jsp"%>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Created>1996-12-17T01:32:42Z</Created>
  <LastSaved>2000-11-18T06:53:49Z</LastSaved>
  <Version>11.5606</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <RemovePersonalInformation/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>4530</WindowHeight>
  <WindowWidth>8505</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>120</WindowTopY>
  <AcceptLabelsInFormulas/>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s21">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center"  ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Interior ss:Color="#99CCFF" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="s22">
   <Alignment ss:Horizontal="Center" ss:Vertical="Center"  ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="50" ss:ExpandedRowCount="65536" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="100" ss:DefaultRowHeight="14.25">
   <Row ss:AutoFitHeight="0">
    <c:forEach items="${columnNames}" var="names">
     <Cell ss:StyleID="s21"><Data ss:Type="String">${names}</Data></Cell>
   </c:forEach>
   </Row>
   <logic:notEmpty name="resultSet" >
<logic:iterate id="record" name="resultSet" indexId="id">
<Row ss:AutoFitHeight="0">
   <Cell ss:StyleID="s22"><Data ss:Type="Number">${id+1}</Data></Cell>
    <c:forEach items="${fields}" var="field">
    <Cell ss:StyleID="s22"><Data ss:Type="String"><bean:write name="record" property="${field}"/></Data></Cell>
    </c:forEach>
   </Row>
</logic:iterate>
  </logic:notEmpty>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Unsynced/>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>10</ActiveRow>
     <ActiveCol>1</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>/*****Action代码********/
SysManager mgrRoadZone = ManageHelper.getManageHelper().getMgrSysManager();

public ActionForward exportToExcel(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
if (log.isDebugEnabled()) {
log.debug("Entering search method");
}
if (isCancelled(request)) {
return mapping.findForward("indexGo");
}
HttpSession session = request.getSession();
List roadZones = null;
// 所有数据
if ("allData".equalsIgnoreCase(request.getParameter("dataScope"))) {
roadZones = mgrRoadZone.getAllRoadZones();
}
// 当前数据
if ("currentPageData".equalsIgnoreCase(request
.getParameter("dataScope"))) {
Map<String, String> mapSearch = (Map<String, String>) session
.getAttribute("map_Search");
if (mapSearch != null) {
roadZones = mgrRoadZone.getRoadZonesByCon(mapSearch);
}
}
// 设置表头数组
String[] columnNames = { "序号", "路线代码", "路线名称", "路段序号", "起点桩号", "终点桩号" };
// 设置字段数组
String[] fields = { "roadcode", "roadname", "ldnum", "startzh", "endzh" };
// 文件名
String fileName="调查路段列表.xls";
// 传送数据
ExportToExcelUtil.sendDataToExcel(request, columnNames, roadZones, fields, response, fileName);
return mapping.findForward("exportToExcel");
}这样子的话表格的格式还是无法控制

Answer 2

你可以通过提交Form表单，把数据提交到Servlet或者Action或者Jsp之类的，
然后在服务器端获得数据，通过jxl写入到一个Excel文件中
关于把数据写入Excel，你可以看这个帖子http://www.blogjava.net/dong/archive/2008/02/01/178854.html

Answer 3

Form表单？？
整个table数据提交过去？
数据量太大怎么办？

Answer 4

整个table可以放到一个Form表单里提交
如果觉得数据太大提交方式设为POST
反正数据肯定是要提交到服务器端，再处理到Excel里的吧
我最近也在研究怎么用jxl处理Excel
大家共同进步~~~呵呵~~

Answer 5

具体的说明：http://hi.baidu.com/sunjsp/blog/item/46df0eea178b4ad5d539c9d2.html示例代码：<%--
contentType="application/msword;charset=GBK",这是设置页面为WORD格式
<%@ page language="java" contentType="application/msword;charset=GBK" %>
--%>
<%-- contentType="application/vnd.ms-excel;charset=GBK",这是设置页面为EXCEL格式 --%>
<%@ page language="java" contentType="application/vnd.ms-excel;charset=GBK" %>
<%
response.setHeader("Content-disposition","inline; filename=excel.xls");//线上浏览方式
//response.setHeader("Content-disposition","attachment; filename=excel.xls");//下载方式
%>
<html>
<head> 
<title></title>
</head><body>
<table width="100%" border="1">
<tr>
    <td align="center" valign="middle">项目</td>
    <td colspan="5" align="center" valign="middle">费用</td>
    <td colspan="2" align="center" valign="middle">考核2</td>
</tr>
<tr>
    <td rowspan="5" align="center" valign="middle">项目1</td>
    <td colspan="2" align="center" valign="middle">1</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
</tr>
<tr>
    <td colspan="2" align="center" valign="middle">2</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
</tr>
<tr>
    <td align="center" valign="middle">31</td>
    <td align="center" valign="middle">32</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
</tr>
<tr>
    <td align="center" valign="middle">41</td>
    <td align="center" valign="middle">42</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
</tr>
<tr>
    <td align="center" valign="middle">51</td>
    <td align="center" valign="middle">52</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
</tr>
<tr>
    <td rowspan="8" align="center" valign="middle">项目2</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
</tr>
<tr>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
</tr>
<tr>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
</tr>
<tr>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
</tr>
<tr>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
</tr>
<tr>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
</tr>
<tr>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
</tr>
<tr>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
</tr>
<tr>
    <td align="center" valign="middle">合计</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
    <td align="center" valign="middle">&nbsp;</td>
</tr>
</table>
</body>
</html>

Answer 6

核心功能由一个js方法完成
xlApp = new ActiveXObject("Excel.Application");
sheet将模板EXCEL读取进来
使用EVALUATE方法填数据

Answer 7

这样子就不叫通用方法了
每个要导出的都两个JSP文件
还不如用XML方式导出