首先需要导入 jxl.jar
jsp页面代码:
<a href="downToExcel.action">导出到Excel</a>
就为了跳转到action。
action代码:
这里我有一个用户实体集合userList
public String downToExcel() throws Exception {
HttpServletResponse response = ServletActionContext.getResponse();
response.reset();
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment;filename=user.xls"); filename 为导出的excel的名称
OutputStream out = response.getOutputStream();
//从数据库读取数据
userList = new ArrayList<User>();
userList = userService.getAllUser();
try{
WritableWorkbook workbook = Workbook.createWorkbook(out);//构建工作薄对象
WritableSheet ws = workbook.createSheet("SheetName",0);//构建工作表
WritableFont wf = new WritableFont(WritableFont.TIMES,10,WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);//设置字体格式
WritableCellFormat wcf = new WritableCellFormat(wf);//创建格式化对象实例
wcf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//垂直居中
wcf.setAlignment(Alignment.CENTRE);//水平居中
//工作表标题行(new Label(列,行,内容,格式))
ws.addCell(new Label(0,0,"userId",wcf));
ws.addCell(new Label(1,0,"userAccount",wcf));
ws.addCell(new Label(2,0,"password",wcf));
ws.addCell(new Label(3,0,"userName",wcf));
ws.addCell(new Label(4,0,"telephone",wcf));
ws.addCell(new Label(5,0,"email",wcf));
ws.addCell(new Label(6,0,"photoUrl",wcf));
//设置单元格宽度
ws.setColumnView(0,20);
ws.setColumnView(1,40);
ws.setColumnView(2,40);
ws.setColumnView(3,40);
ws.setColumnView(4,40);
ws.setColumnView(5,40);
ws.setColumnView(6,60);
List<Object[]> dataList = new ArrayList<Object[]>();
//List<User> userList = userDAO.findAll();
for(User temp:userList){
Object[] user = {temp.getUserId(),temp.getUserAccount(),temp.getPassword(),temp.getUserName(),temp.getTelephone(),temp.getEmail(),temp.getPhotoUrl()};
dataList.add(user);
}
//向工作表添加数据
for(int i=0;i<dataList.size();i++){
for(int j=0;j<dataList.get(i).length;j++){
Object o = dataList.get(i)[j];
if(o != null){
Label l = new Label(j,i+1,o.toString(),wcf);
ws.addCell(l);
}
}
}
workbook.write();
workbook.close();
out.flush();
out.close();
}catch(IOException ioe){
ioe.printStackTrace();
}catch(WriteException we){
we.printStackTrace();
}
return null;
}
struts.xml代码:
<action name="downToExcel" class="UserAction" method="downToExcel"></action>