数据导出Excel表

package com.lenovo.officecloud.action;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;
import com.lenovo.common.util.SelectTreeUtils;
import com.lenovo.core.JomapCoreAction;
import com.lenovo.core.entity.Operator;
import com.lenovo.core.entity.TdDepartment;
import com.lenovo.core.manager.DepartmentMng;
import com.lenovo.officecloud.entity.TdSignIn;
import com.lenovo.officecloud.entity.vo.CommObj;
import com.lenovo.officecloud.entity.vo.SigninVO;
import com.lenovo.officecloud.manager.SignInMng;
@Scope("prototype")
@Controller("officecloud.signinAct")
public class SignInAct extends JomapCoreAction {

	@Autowired
	private SignInMng signInMng;
	@Autowired
	private DepartmentMng departmentMng;
	private List<CommObj> department_list;
	private String departmentid;
	
	private static String[] titles = new String[]{"签到日期","签到星期","签到人名","部门名称","是否迟到",
		"是否早退","是否漏签","首次签到时间","最后签到时间","签到次数"};
	private static String[] title = new String[]{"签到人名","签到内容","签到类型","部门名称","签到时间"};

	/**
	 * 查询所有签到
	 * 
	 * */
	public String doList() {
		String orgid=(String)contextPvd.getSessionAttr(Operator.ORGID_KEY);
		this.list = SelectTreeUtils.webTree(departmentMng.getRoots(orgid));
		department_list=this.departmentMng.getDepartmentpid();
		this.pagination = this.signInMng.getListByOrgId(orgid, pageNo, getCookieCount());
		return LIST;
	}
	
	public String doQuery() {
		String orgid=(String)contextPvd.getSessionAttr(Operator.ORGID_KEY);
		this.list = SelectTreeUtils.webTree(departmentMng.getRoots(orgid));
		department_list=this.departmentMng.getDepartmentpid();
		if(StringUtils.isNotBlank(departmentid)){
			this.pagination = this.signInMng.getListByDepartmentId(departmentid, pageNo, getCookieCount());
		}else{
			this.pagination = this.signInMng.getListByOrgId(orgid, pageNo, getCookieCount());
		}
		return LIST;
	}
	
	/**
	 * 转到每日签到详情列表
	 * @return
	 */
	public String doDetailDaily(){
		String orgId = (String)contextPvd.getSessionAttr(Operator.ORGID_KEY);
		this.list = SelectTreeUtils.webTree(departmentMng.getRoots(orgId));
		department_list=this.departmentMng.getDepartmentpid();
		HttpServletRequest request = this.contextPvd.getRequest();
		String startDate = request.getParameter("startDate");
		String endDate = request.getParameter("endDate");
		String lateAttr = request.getParameter("lateAttr");
		String earlyAttr = request.getParameter("earlyAttr");
		String missAttr = request.getParameter("missAttr");
		String deptId = request.getParameter("deptId");
		//如果开始和结束日期都没有，则默认查询当月首日到当前
		if(null==startDate && null==endDate){
			Date now = new Date();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			startDate = sdf.format(now).substring(0, 8)+"01 00:00:00";
			endDate = sdf.format(now);
		}
		this.pagination = this.signInMng.querySigninDetailDaily(orgId, deptId, startDate, endDate,
				null==lateAttr?null:Integer.parseInt(lateAttr),
				null==earlyAttr?null:Integer.parseInt(earlyAttr),
				null==missAttr?null:Integer.parseInt(missAttr), pageNo, getCookieCount());
		request.setAttribute("startDate", startDate);
		request.setAttribute("endDate", endDate);
		request.setAttribute("lateAttr", lateAttr);
		request.setAttribute("earlyAttr", earlyAttr);
		request.setAttribute("missAttr", missAttr);
		request.setAttribute("deptId", deptId);
		return "daily";
	}
	
	/**
	 * 导出签到清单
	 * @return
	 */
	public String doExportList(){
		String orgId = (String)contextPvd.getSessionAttr(Operator.ORGID_KEY);
		this.list = SelectTreeUtils.webTree(departmentMng.getRoots(orgId));
		department_list=this.departmentMng.getDepartmentpid();
		if(StringUtils.isNotBlank(departmentid)){
			this.pagination = this.signInMng.getListByDepartmentId(departmentid, 1, 65536);
		}else{
			this.pagination = this.signInMng.getListByOrgId(orgId, 1, 65536);
		}
		List<TdSignIn> list = null;
		if(this.pagination!=null && this.pagination.getList()!=null){
			list = this.pagination.getList();
		}else{
			list = new ArrayList<TdSignIn>();
		}
		HttpServletResponse response = this.contextPvd.getResponse();
		OutputStream os = null;
		try {
			response.reset();
			response.setHeader("Content-Type",
					"application/vnd.ms-excel;charset=GBK");
			response.setHeader("Content-Disposition", "attachment;filename="
					+ new String("签到清单列表.xls".getBytes(), "iso-8859-1"));
			os = response.getOutputStream();
			this.exportList(list, title, os);
		} catch (RowsExceededException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			this.addActionMessage("导出失败!(发生行数据操作异常)");
			return doList();
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			this.addActionMessage("导出失败!(发生数据写入异常)");
			return doList();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			this.addActionMessage("导出失败!(发生输入输出异常)");
			return doList();
		} finally {
			if (null != os) {
				try {
					os.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}
	
	/**
	 * 导出签到清单
	 * @param dataList
	 * @param titles
	 * @param os
	 */
	private void exportList(List<TdSignIn> dataList,String[] title,OutputStream os) throws IOException,
			RowsExceededException, WriteException{
		// 创建Excel工作薄
		WritableWorkbook wwb;
		wwb = Workbook.createWorkbook(os);
		// 添加第一个工作表并设置第一个Sheet的名字
		WritableSheet sheet = wwb.createSheet("签到清单列表", 0);
		Label label;
		for (int i = 0; i < title.length; i++) {
			// Label(x,y,z)其中x代表单元格的第x+1列，第y+1行, 单元格的内容是y
			// 在Label对象的子对象中指明单元格的位置和内容
			label = new Label(i, 0, title[i]);
			// 将定义好的单元格添加到工作表中
			sheet.addCell(label);
		}
		TdSignIn vo = null;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		for(int i=0;i<dataList.size();i++){
			vo = dataList.get(i);
			label = new Label(0, i + 1, vo.getUsername());
			sheet.addCell(label);
			label = new Label(1, i + 1, vo.getSigincontent());
			sheet.addCell(label);
			label = new Label(2, i + 1, vo.getSigintypename());
			sheet.addCell(label);
			label = new Label(3, i + 1, vo.getDepartmentname());
			sheet.addCell(label);
			label = new Label(4, i + 1, (null==vo.getSigintime()?"":sdf.format(vo.getSigintime())));
			sheet.addCell(label);
		}
		wwb.write();
		wwb.close();
	}
	
	/**
	 * 导出每日签到详情
	 * @return
	 */
	public String doExportDailyDetail(){
		String orgId = (String)contextPvd.getSessionAttr(Operator.ORGID_KEY);
		this.list = SelectTreeUtils.webTree(departmentMng.getRoots(orgId));
		department_list=this.departmentMng.getDepartmentpid();
		HttpServletRequest request = this.contextPvd.getRequest();
		String startDate = request.getParameter("startDate");
		String endDate = request.getParameter("endDate");
		String lateAttr = request.getParameter("lateAttr");
		String earlyAttr = request.getParameter("earlyAttr");
		String missAttr = request.getParameter("missAttr");
		String deptId = request.getParameter("deptId");
		//如果开始和结束日期都没有，则默认查询当月首日到当前
		if(null==startDate && null==endDate){
			Date now = new Date();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			startDate = sdf.format(now).substring(0, 8)+"01 00:00:00";
			endDate = sdf.format(now);
		}
		this.pagination = this.signInMng.querySigninDetailDaily(orgId, deptId, startDate, endDate,
				null==lateAttr?null:Integer.parseInt(lateAttr),
				null==earlyAttr?null:Integer.parseInt(earlyAttr),
				null==missAttr?null:Integer.parseInt(missAttr), 1, 65536);
		List<SigninVO> list = null;
		if(this.pagination!=null && this.pagination.getList()!=null){
			list = this.pagination.getList();
		}else{
			list = new ArrayList<SigninVO>();
		}
		HttpServletResponse response = this.contextPvd.getResponse();
		OutputStream os = null;
		try {
			response.reset();
			response.setHeader("Content-Type",
					"application/vnd.ms-excel;charset=GBK");
			response.setHeader("Content-Disposition", "attachment;filename="
					+ new String("签到统计清单.xls".getBytes(), "iso-8859-1"));
			os = response.getOutputStream();
			this.exportDailyDetail(list, titles, os);
		} catch (RowsExceededException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			this.addActionMessage("导出失败!(发生行数据操作异常)");
			return doList();
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			this.addActionMessage("导出失败!(发生数据写入异常)");
			return doList();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			this.addActionMessage("导出失败!(发生输入输出异常)");
			return doList();
		} finally {
			if (null != os) {
				try {
					os.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}
	
	/**
	 * 导出签到每日详情
	 * @param dataList
	 * @param titles
	 * @param os
	 */
	private void exportDailyDetail(List<SigninVO> dataList,String[] titles,OutputStream os) throws IOException,
			RowsExceededException, WriteException{
		// 创建Excel工作薄
		WritableWorkbook wwb;
		wwb = Workbook.createWorkbook(os);
		// 添加第一个工作表并设置第一个Sheet的名字
		WritableSheet sheet = wwb.createSheet("签到统计清单", 0);
		Label label;
		for (int i = 0; i < titles.length; i++) {
			// Label(x,y,z)其中x代表单元格的第x+1列，第y+1行, 单元格的内容是y
			// 在Label对象的子对象中指明单元格的位置和内容
			label = new Label(i, 0, titles[i]);
			// 将定义好的单元格添加到工作表中
			sheet.addCell(label);
		}
		SigninVO vo = null;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		for(int i=0;i<dataList.size();i++){
			vo = dataList.get(i);
			label = new Label(0, i + 1, vo.getSignin_date());
			sheet.addCell(label);
			label = new Label(1, i + 1, vo.getSignin_day());
			sheet.addCell(label);
			label = new Label(2, i + 1, vo.getUser_name());
			sheet.addCell(label);
			label = new Label(3, i + 1, vo.getDept_name());
			sheet.addCell(label);
			label = new Label(4, i + 1, (vo.getLate_attr()==1)?"是":"否");
			sheet.addCell(label);
			label = new Label(5, i + 1, (vo.getEarly_attr()==1)?"是":"否");
			sheet.addCell(label);
			label = new Label(6, i + 1, (vo.getMiss_attr()==1)?"是":"否");
			sheet.addCell(label);
			label = new Label(7, i + 1, (null==vo.getMin_signin_time()?"":sdf.format(vo.getMin_signin_time())));
			sheet.addCell(label);
			label = new Label(8, i + 1, (null==vo.getMax_signin_time()?"":sdf.format(vo.getMax_signin_time())));
			sheet.addCell(label);
			label = new Label(9, i + 1, String.valueOf(vo.getSignin_count()));
			sheet.addCell(label);
		}
		wwb.write();
		wwb.close();
	}
	
	public SignInMng getSignInMng() {
		return signInMng;
	}

	public void setSignInMng(SignInMng signInMng) {
		this.signInMng = signInMng;
	}

	public DepartmentMng getDepartmentMng() {
		return departmentMng;
	}

	public void setDepartmentMng(DepartmentMng departmentMng) {
		this.departmentMng = departmentMng;
	}

	public List<CommObj> getDepartment_list() {
		return department_list;
	}

	public void setDepartment_list(List<CommObj> department_list) {
		this.department_list = department_list;
	}
	
	public String getDepartmentid() {
		return departmentid;
	}

	public void setDepartmentid(String departmentid) {
		this.departmentid = departmentid;
	}
}