struts2与jxl整合导出Excel文件
1、导入相应的Jar包
jxl.jar
2、下载请求
<a href="Complain_exportExcel.action" >导出为Excel文件</a>
<a href="Complain_exportExcelPage.action?pageNumber=${ page.pageNumber }" >导出当前页为Excel文件</a>
3、struts.xml的配置
<action name="Complain_exportExcel" class="cn.hpu.action.ComplainAction" method="exportExcel"> <result></result> </action> <action name="Complain_exportExcelPage" class="cn.hpu.action.ComplainAction" method="exportExcelPage"> <result></result> </action>
4、Action类的处理
package cn.hpu.action;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import cn.hpu.po.Complain;
import cn.hpu.service.ComplainService;
import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionSupport;
public class ComplainAction extends ActionSupport {
private ComplainService complainService;
private List<Complain> complains;
// 分页
private Integer pageSize;
private Integer pageNumber;
/**导出Excel文件
* @author qinrui
* @see cn.hpu.service.ComplainService complainService
* @param response
* @return java.lang.String
* @throws IOException
*/
public void exportExcel() throws IOException {
List<Complain> complains = complainService.loadComplains();
HttpServletResponse response = ServletActionContext.getResponse();
response.reset();
// 禁止数据缓存
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.addHeader("Content-Disposition","attachment;filename=Complain.xls");
OutputStream os = response.getOutputStream();
printExcels(complains, os);
os.flush();
os.close();
}
/**将当前页输出为Excel
* @author qinrui
* @see cn.hpu.service.ComplainService complainService
* @throws IOException
*/
public void exportExcelPage() throws IOException {
Integer sessionPageSize = (Integer) ActionContext.getContext().getSession().get("pageSize");
if(sessionPageSize != null) {
pageSize = sessionPageSize;
} else {
pageSize = 10;
}
complains = complainService.loadComplains((pageNumber - 1) * pageSize, pageSize);
HttpServletResponse response = ServletActionContext.getResponse();
response.reset();
// 禁止数据缓存
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.addHeader("Content-Disposition","attachment;filename=Complain.xls");
OutputStream os = response.getOutputStream();
printExcels(complains, os);
os.flush();
os.close();
}
/**以二进制形式下载Excel
* @author qinrui
* @param complains
* @param os
*/
public void printExcels(List<Complain> complains , OutputStream os) {
try {
// 创建新的Excel工作簿
WritableWorkbook book = Workbook.createWorkbook(os);
// 生成名为"投诉信息表"的工作表,参数0表示这是第一页
WritableSheet sheet = book.createSheet("投诉信息表" , 0);
// 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
// 更改日期格式
DateFormat df = new DateFormat("yyyy-MM-dd");
WritableCellFormat wcf = new WritableCellFormat(df);
String[] labels = new String[]{"投诉主题",
"投诉客户","投诉人","受理人","投诉日期","处理状态",
"客户满意度","投诉内容","投诉处理办法","投诉备注信息"};
// 设定单元格信息
for(int i = 0; i < labels.length; i++) {
Label label = new Label(i , 0, labels[i]);
sheet.addCell(label);
}
for(int x = 0; x < complains.size(); x++) {
Label theme = new Label(0, x + 1, complains.get(x).getTheme());
Label customer = new Label(1, x + 1, complains.get(x).getCustomerInfo().getName());
Label complainant = new Label(2, x + 1, complains.get(x).getComplainant());
Label dealer = new Label(3, x + 1, complains.get(x).getUser().getName());
DateTime time = new DateTime(4, x + 1, complains.get(x).getTime() , wcf);
Label state = new Label(5 , x + 1, complains.get(x).getState().getName());
Label satisfication = new Label(6 , x + 1, complains.get(x).getSatisfication().getName());
Label content = new Label(7, x + 1, complains.get(x).getContent());
Label method = new Label(8, x + 1, complains.get(x).getMethod());
Label remark = new Label(9, x + 1, complains.get(x).getRemark());
sheet.addCell(theme);
sheet.addCell(customer);
sheet.addCell(complainant);
sheet.addCell(dealer);
sheet.addCell(time);
sheet.addCell(state);
sheet.addCell(satisfication);
sheet.addCell(content);
sheet.addCell(method);
sheet.addCell(remark);
}
// 写入数据并关闭文件
book.write();
book.close();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public ComplainService getComplainService() {
return complainService;
}
public void setComplainService(ComplainService complainService) {
this.complainService = complainService;
}
public List<Complain> getComplains() {
return complains;
}
public void setComplains(List<Complain> complains) {
this.complains = complains;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public Integer getPageNumber() {
return pageNumber;
}
public void setPageNumber(Integer pageNumber) {
this.pageNumber = pageNumber;
}
}