导出数据到excel
两种方法：一种是用js的方法，这种方法简单，直接导出页面数据，但是此方法不支持分页，一种是在后台写，支持分页一：js的方法：
<%@ page contentType="text/html;charset=UTF-8"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>EB页面导出为EXCEL文档的方法</title>
<SCRIPT LANGUAGE="JavaScript">
<!--
function AutomateExcel() {
// Start Excel and get Application object.
var oXL = new ActiveXObject("Excel.Application");
// Get a new workbook.
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
var table = document.all.data;
var hang = table.rows.length;
var lie = table.rows(0).cells.length;
// Add table headers going cell by cell.
for (i=0;i<hang;i++){
for (j=0;j<lie;j++){
oSheet.Cells(i+1,j+1).Value = table.rows(i).cells(j).innerText;
}
}
oXL.Visible = true;
oXL.UserControl = true;
}
//-->
</SCRIPT>
</head>
<body>
<table id="data" width="200" border="1">
<tr>
<td>11</td>
<td>11</td>
</tr>
<tr>
<td>22</td>
<td>22</td>
</tr>
<tr>
<td>33</td>
<td>33</td>
</tr>
<tr>
<td>44 </td>
<td>44</td>
</tr>
</table>
<input type="button" name="out_word1"value="导出到excel" class="notPrint">
</html>
二是在后台写java代码：
jsp页面的jQuery：
<script>
$(document).ready(function(){
//导出信息
$("#menuExport").click(function(){
$("#f1").attr("action","case!export.action");
$("#f1").submit();
$("#f1").attr("action","case!doquery.action");
});
});
</script>
导出按钮：
<a href="javascript:void(0);" id="menuExport">案件导出</a>
Action中的export方法：
private Page<AllEntity> pageExcel = new Page<AllEntity>(10000, true);
public void export() throws Exception {
try {
StringBuffer sqlBuffer = new StringBuffer("select t.case_id,t.title,t.case_type,t.relation_range from zft_case_general t where 1=1 ");
String sql = sqlBuffer.toString();
pageExcel = manager.getZft(pageExcel, sql);
List<ZftAllEntity> caseList = pageExcel.getResult();
String fname = "案件数据";// Excel文件名
fname = new String(fname.getBytes("GBK"), "ISO-8859-1");// 解决文件中文名称问题
OutputStream os = Struts2Utils.getResponse().getOutputStream();// 取得输出流
Struts2Utils.getResponse().reset();// 清空输出流
Struts2Utils.getResponse().setHeader("Content-disposition",
"attachment; filename=" + fname + ".xls");// 设定输出文件头
Struts2Utils.getResponse().setContentType("application/msexcel");// 定义输出类型
CaseExcel eb = new CaseExcel();
eb.exportExcel(os, caseList);// 调用生成excel文件bean
} catch (Exception e) {
System.out.println(e);
}
}
CaseExcel 的代码：
package com.sdcncsi.web.datainterface;
import java.io.OutputStream;
import java.util.List;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import com.sdcncsi.common.util.DictUtil;
import com.sdcncsi.entity.AllEntity;
import com.sdcncsi.entity.zftsys.Dict;
public class CaseExcel {
private AllEntity entity;
public AllEntity getEntity() {
return entity;
}
public void setEntity(AllEntity entity) {
this.entity = entity;
}
public String exportExcel(OutputStream os, List<AllEntity> caseList) throws Exception {
WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件
String tmptitle = "案件记录"; // 标题
WritableSheet wsheet = wbook.createSheet("第一页", 0); // sheet名称
// 设置excel标题
WritableFont wfont = new WritableFont(WritableFont.ARIAL, 10,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
Colour.BLACK);
WritableCellFormat wcfFC = new WritableCellFormat(wfont);
wsheet.addCell(new Label(3, 0, tmptitle, wcfFC));
// 开始生成主体内容
wsheet.addCell(new Label(0, 1, "标 题"));
wsheet.addCell(new Label(1, 1, "执法案件来源"));
wsheet.addCell(new Label(2, 1, "执法案件性质"));
entity = new AllEntity();
for (int i = 0; i < caseList.size(); i++) {
wsheet.addCell(new Label(0, i + 2, ( caseList.get(i)).getTitle()));
wsheet.addCell(new Label(1, i + 2, ( caseList.get(i)).getCase_type()));
}
// 主体内容生成结束
wbook.write(); // 写入文件
wbook.close();
os.close();
return "success";
}
}
页:
[1]