deptmonthexlservlet.java

java高级引用管理综合朝鲜经java高级引用管理综合朝鲜经java高级引用管理综合朝鲜经java高级引用管理综合朝鲜经

package com.selectpoint;

import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;

import com.login.UserInforBean;

public class DeptMonthExlServlet extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws IOException, ServletException {

		request.setCharacterEncoding("UTF-8");
		HttpSession session = request.getSession();
		request.setCharacterEncoding("UTF-8");
		Collection deptmonthpoints = (Collection) session
				.getAttribute("deptmonthpoints");
		// String year = request.getParameter("year");
		// String month = request.getParameter("month");
		DeptMonthPoint deptMonth = new DeptMonthPoint();
		Collection deptPerson=(Collection)session.getAttribute("deptPerson");
		try {
			int bigen = 0;
			// 创建新的Excel 工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();
			// 在Excel 工作簿中建一工作表
			HSSFSheet sheet = workbook.createSheet("Sheet1");
			// 设置行宽度
			HSSFRow row = sheet.createRow((short) bigen);
			HSSFCell cell1 = row.createCell((short) 0);// 第一列
			// 定义单元格为字符串类型
			cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
			// 在单元格中输入数据
			cell1.setCellValue("  ");
			int a;

			for (a = 0; a < deptMonth.assesPersons1.length * 2; a++) {
				if (a % 2 == 1) {
					sheet.addMergedRegion(new Region(bigen, (short) a, bigen,
							(short) (a + 1)));
				}
			}
			for (a = 0; a < deptMonth.assesPersons1.length; a++) {
				cell1 = row.createCell((short) (2 * a + 1));
				// 定义单元格为字符串类型
				cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell1.setCellValue(deptMonth.assesPersons1[a]);
			}
			sheet.addMergedRegion(new Region(bigen, (short) (2 * a + 1), bigen,
					(short) (2 * a + 2)));
			cell1 = row.createCell((short) (2 * a + 1));
			// 定义单元格为字符串类型
			cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell1.setCellValue("小计");

			cell1 = row.createCell((short) (2 * a + 3));
			// 定义单元格为字符串类型
			cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell1.setCellValue("总计");

			bigen = 1;// //////////////////
			row = sheet.createRow((short) bigen);
			for (a = 0; a < deptMonth.assesPersons1.length; a++) {
				if (a == 0 || a == 5 || a == 6 || a == 7) {// //////////////////////////////////

				} else {
					sheet.addMergedRegion(new Region(bigen,
							(short) (2 * a + 1), bigen, (short) (2 * a + 2)));
				}
			}
			for (a = 0; a < deptMonth.assesPersons1.length; a++) {
				if (a == 0 || a == 5 || a == 6 || a == 7) {// ///////////////////////////////////

					cell1 = row.createCell((short) (2 * a + 1));
					// 定义单元格为字符串类型
					cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell1.setCellValue("考核分");

					cell1 = row.createCell((short) (2 * a + 2));
					// 定义单元格为字符串类型
					cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell1.setCellValue("加减分");
				} else {
					cell1 = row.createCell((short) (2 * a + 1));
					// 定义单元格为字符串类型
					cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell1.setCellValue("加减分");
				}
			}

			a = deptMonth.assesPersons1.length;

			cell1 = row.createCell((short) (2 * a + 1));
			// 定义单元格为字符串类型
			cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell1.setCellValue("考核");
			cell1 = row.createCell((short) (2 * a + 2));
			// 定义单元格为字符串类型
			cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell1.setCellValue("加减");
			cell1 = row.createCell((short) (2 * a + 3));
			// 定义单元格为字符串类型
			cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell1.setCellValue("  ");

			bigen = bigen + 1;
			Iterator it = deptmonthpoints.iterator();
			int i = bigen;
			int j = 0;
			while (it.hasNext()) {
				// bigen = bigen + i;
				DeptPointBean deptMonthPoints[] = (DeptPointBean[]) it.next();

				row = sheet.createRow((short) i);
				cell1 = row.createCell((short) (0));
				// 定义单元格为字符串类型
				cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell1.setCellValue(deptMonth.deptNames[j]);

				
				for (int d = 0; d < deptMonthPoints.length; d++) {
					DeptPointBean deptMonthPoint = deptMonthPoints[d];
					if (deptMonthPoint != null) {
						if (deptMonthPoint.asses_point == null
								|| "".equals(deptMonthPoint.asses_point)) {

							sheet.addMergedRegion(new Region(i, (short) (2 * d + 1), i,
									(short) (2 * d + 2)));
							cell1 = row.createCell((short) (2 * d + 1));
							// 定义单元格为字符串类型
							cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
							cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
							cell1.setCellValue(deptMonthPoint.ss_point);
							
						} else {
							cell1 = row.createCell((short) (2 * d + 1));
							// 定义单元格为字符串类型
							cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
							cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
							cell1.setCellValue(deptMonthPoint.asses_point);
							
							cell1 = row.createCell((short) (2 * d + 2));
							// 定义单元格为字符串类型
							cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
							cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
							cell1.setCellValue(deptMonthPoint.ss_point);
						}

					} else {

					}
				}
				
				int h= deptMonth.assesPersons1.length;
				
				cell1 = row.createCell((short) (2 * h + 1));
				// 定义单元格为字符串类型
				cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell1.setCellValue(deptMonth.getAssesPoints(deptMonthPoints));
				cell1 = row.createCell((short) (2 * h + 2));
				// 定义单元格为字符串类型
				cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell1.setCellValue(deptMonth.getSsPoints(deptMonthPoints));
				cell1 = row.createCell((short) (2 * h + 3));
				// 定义单元格为字符串类型
				cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell1.setCellValue(deptMonth.getPoints(deptMonthPoints));
				i++;
				j++;
				
				
			}
			if (deptPerson != null) {
				bigen = bigen+i;
				row = sheet.createRow((short) bigen);
				cell1 = row.createCell((short) (0));
				// 定义单元格为字符串类型
				cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell1.setCellValue("人员");
				
				cell1 = row.createCell((short) (1));
				// 定义单元格为字符串类型
				cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell1.setCellValue("考核分");
				
				cell1 = row.createCell((short) (2));
				// 定义单元格为字符串类型
				cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell1.setCellValue("加减分");
				
				cell1 = row.createCell((short) (3));
				// 定义单元格为字符串类型
				cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell1.setCellValue("总计");
				
				it = deptPerson.iterator();
				bigen = bigen+1;
				while (it.hasNext()) {
					
					UserInforBean assesuser = (UserInforBean) it
							.next();
					row = sheet.createRow((short) bigen);
					
					cell1 = row.createCell((short) (0));
					// 定义单元格为字符串类型
					cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell1.setCellValue(assesuser.person_name);
					
					cell1 = row.createCell((short) (1));
					// 定义单元格为字符串类型
					cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell1.setCellValue(deptMonth.getPersonAssesPoints(assesuser.asses_dept,(List)deptmonthpoints));
					
					cell1 = row.createCell((short) (2));
					// 定义单元格为字符串类型
					cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell1.setCellValue(deptMonth.getPersonSsPoints(assesuser.asses_dept,(List)deptmonthpoints));
					
					cell1 = row.createCell((short) (3));
					// 定义单元格为字符串类型
					cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell1.setCellValue(deptMonth.getPersonPoints(assesuser.asses_dept,(List)deptmonthpoints) );
					bigen = bigen+ 1;
				}
			}
			
			OutputStream os = response.getOutputStream();// 取得输出流
			response.reset();// 清空输出流
			response.setHeader("Content-disposition", "attachment; filename="
					+ new String("/excel.xls".getBytes(), "iso-8859-1"));// 设定输出文件头
			response.setContentType("application/msexcel");

			// 新建一输出文件流
			// FileOutputStream fOut = new FileOutputStream(outputFile);
			// 把相应的Excel 工作簿存盘
			workbook.write(os);
			// 操作结束，关闭文件
			// fOut.flush();
			// fOut.close();
			// 该处如果Excel过大会影响效率，谁有好的想法可以提出来参考（不过从页面下载完后就会清空）
			// request.getSession().setAttribute("Download", outputFile);
			os.close();
		} catch (Exception ioexception) {
			request.setAttribute("message", "创建文件失败！");
		}

	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws IOException, ServletException {

	}

	public String dateToString(Date time) {
		SimpleDateFormat formatter;
		formatter = new SimpleDateFormat("yyyy-MM");
		String ctime = formatter.format(time);
		return ctime;
	}
}