struts2与jxl整合导出Excel文件

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;
	}

}