1.页面调用js function:
function exportAllPage(url){
alert("数据量可能较大,请等待");
window.location.href = url+"?exportAllFlag=true";
}
2.action代码(使用struts 2.0)
public String list() {
//pager = memberService.findByPager(pager);
pager = userService.findByPager(pager);
HttpServletRequest request = ServletActionContext.getRequest();
String exportAllFlag = StaticMethod.nullObject2String(request.getParameter("exportAllFlag"));// 导出全部标示符
String templateFileName = "member.xls";//模板名称
ExcelTool excelTool = new ExcelTool();
if("true".equals(exportAllFlag)){
List allList = userService.getAll();
String filePath = excelTool.createExcel(allList,templateFileName);
try {
in = new FileInputStream(new File(filePath));
} catch (FileNotFoundException e) {
e.printStackTrace();
}
filename=templateFileName; //保存文件时的名称
return EXCELDOWN;
}
return LIST;
}
3.jxl工具类
package net.shopxx.common;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import net.shopxx.bean.SystemConfig;
import net.shopxx.util.StaticMethod;
import net.shopxx.util.SystemConfigUtil;
/**
*
* 项目名称:shopxx
* 类名称:ExcelTool
* 类描述:
* excel工具类,主要用于excel导出显示列表
* 创建时间:2011-6-10 上午09:47:42
* @version 1.0
* @author jiazhihui
*
*/
public class ExcelTool {
private List<String> titleList = new ArrayList<String>();
private List<String> variableList = new ArrayList<String>();
private String sheetName = "";//模板表格名称
/**
* 生成excle文件
* @param list
* @param templateFileName
* @return String
* @author jiazhihui
* @Create Date 2011 下午02:34:20
*/
public String createExcel(List list,String templateFileName){
this.readTemplate(templateFileName);
String excelPath = this.writeExcel(list,templateFileName);
return excelPath;
}
/**
* 读取模板
* void
* @author jiazhihui
* @Create Date 2011 上午10:11:10
*/
private void readTemplate(String templateFileName){
try {
Workbook wb = Workbook.getWorkbook(new File(StaticMethod.getExcelTemBasePath()+"template/excelTemplate/"+templateFileName));
Sheet st = wb.getSheet(0);//取得第1个表格
sheetName = st.getName();
//取出第一行
for(int i=0;i<st.getColumns();i++){
//取得所有标题
Cell cell = st.getCell(i,0);//第一行
titleList.add(cell.getContents());
}
for(int i=0;i<st.getColumns();i++){
//取得内容变量名称
Cell cell = st.getCell(i,1);//第二行
if(!cell.getContents().equals("")){//如果为空,不写入
variableList.add(cell.getContents());
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 生成excel文件
* @return String
* @author jiazhihui
* @Create Date 2011 上午11:08:06
*/
public String writeExcel(List list,String templateFileName){
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
String fileName = sdf.format(new Date())+templateFileName;
String filePath = getSystemConfig().getExcelTempPath()+"/"+fileName;
try {
WritableWorkbook wb = Workbook.createWorkbook(new File(filePath));
WritableSheet sheet = wb.createSheet(sheetName, 0);
//设置excel标题
for(int i=0;i<titleList.size();i++){
Label titleLab = new Label(i, 0, titleList.get(i));
sheet.addCell(titleLab);
}
//设置excel内容
for(int i=0;i<variableList.size();i++){
for(int j=0;j<list.size();j++){
Object o = list.get(j);
PropertyDescriptor pd = new PropertyDescriptor(variableList.get(i),o.getClass());
Method md = pd.getReadMethod();
Object fieldValue = md.invoke(o,new Object[0]);
if(fieldValue != null){//为空,则该单元格不写入数据
Label valueLab = new Label(i, j+1, fieldValue.toString());
sheet.addCell(valueLab);
}
}
}
wb.write();
wb.close();
} catch (Exception e) {
e.printStackTrace();
}
return filePath;
}
// 获取系统配置信息
private SystemConfig getSystemConfig() {
return SystemConfigUtil.getSystemConfig();
}
}