java实现excl【自用】

long startTime = System.currentTimeMillis();    //获取开始时间
		UploadFile file = getFile("jhj.ZT");
		String year = getPara("year", time.getYear());// 年份
		// 下载文件临时存放地
		String path = PathKit.getWebRootPath() + File.separator + "temp" + File.separator;
		File f = new File(path);
		if (!f.exists()) {
			f.mkdirs();// 0
			// 如果创建目录成功，方法返回true，否则该方法返回false。
		}
		// 错误信息存放名称
		String rfile = time.getNowTime("yyyyMMddHHmmss").toString() + ".xls";
		int success = 0;// 成功条数
		int error = 0;// 失败条数
		String errormsg = ""; // 失败信息
		List<Grad> list = new ArrayList<Grad>();
		try {
			// 创建工作簿
			WritableWorkbook book = Workbook.createWorkbook(new File(path + rfile));
			WritableSheet sheet = book.createSheet("导入失败数据", 0);
			/*
			 * 合并行和列mergeCells(a,b,c,d)a：单元格的列号；b：单元格的行号；
			 * c：从单元格[a,b]起，向下合并的列数；d：从单元格[a,b]起，向下合并的行数。
			 */
			// 行表头开始
			// 表头单元格样式 垂直、水平居中 宋体,9号
			WritableFont cfont = new WritableFont(WritableFont.createFont("宋体"), 9);
			WritableCellFormat cell = new WritableCellFormat(cfont);
			// 水平居中
			cell.setAlignment(Alignment.CENTRE);
			// 垂直居中
			cell.setVerticalAlignment(VerticalAlignment.CENTRE);
			cell.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
			// 自动换行
			cell.setWrap(true);
			sheet.setColumnView(0, 15);
			sheet.setColumnView(1, 15);
			sheet.setColumnView(2, 20);
			sheet.setColumnView(3, 15);
			sheet.setColumnView(4, 15);
			sheet.setColumnView(5, 15);
			sheet.setColumnView(6, 25);
			sheet.setColumnView(7, 15);
			sheet.setColumnView(8, 15);
			sheet.setColumnView(9, 20);
			sheet.setColumnView(10, 15);
			sheet.setColumnView(11, 15);
			sheet.setColumnView(12, 15);
			sheet.setColumnView(13, 25);
			sheet.setColumnView(14, 15);
			sheet.setColumnView(15, 15);
			sheet.setColumnView(16, 20);
			sheet.setColumnView(17, 15);
			sheet.setColumnView(18, 15);
			sheet.setColumnView(19, 15);
			sheet.setColumnView(20, 25);
			sheet.setColumnView(21, 15);
			sheet.setColumnView(22, 15);
			sheet.setColumnView(23, 20);
			sheet.setColumnView(24, 15);
			sheet.setColumnView(25, 15);
			sheet.setColumnView(26, 15);
			sheet.setColumnView(27, 15);
			sheet.setColumnView(28, 15);
			WritableFont mfont = new WritableFont(WritableFont.createFont("宋体"), 9);
			mfont.setColour(Colour.RED);
			WritableCellFormat mcell = new WritableCellFormat(mfont);
			// 水平居中
			mcell.setAlignment(Alignment.CENTRE);
			// 垂直居中
			mcell.setVerticalAlignment(VerticalAlignment.CENTRE);
			mcell.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
			// 自动换行
			mcell.setWrap(true);
			// 第一列
			InputStream is = new FileInputStream(file.getFile());
			Workbook rwb = Workbook.getWorkbook(is);
			Sheet rst = rwb.getSheet(0);
			int rsRows = rst.getRows();
			// 遍历行
			for (int j = 1; j < rsRows; j++) {
				errormsg = ""; // 失败信息
				boolean t = true;// 保存
				String stuNum = rst.getCell(0, j).getContents().trim();// 考生号
				String IdCard = rst.getCell(1, j).getContents().trim();// 身份证号码
																		// 不允许为空
				String name = rst.getCell(2, j).getContents().trim();// 姓名
				String sexCode = rst.getCell(3, j).getContents().trim();// 性别代码
				String sexName = rst.getCell(4, j).getContents().trim();// 性别名称
				String nationCode = rst.getCell(5, j).getContents().trim();// 民族代码
				String nationName = rst.getCell(6, j).getContents().trim();// 民族名称
				String policyCode = rst.getCell(7, j).getContents().trim();// 政治面貌代码
				String policyName = rst.getCell(8, j).getContents().trim();// 政治面貌名称
				String schoolCode = rst.getCell(9, j).getContents().trim();// 院校代码
				String schoolName = rst.getCell(10, j).getContents().trim();// 院校名称
				String degreeCode = rst.getCell(11, j).getContents().trim();// 学历代码
				String degreeName = rst.getCell(12, j).getContents().trim();// 学历名称
				String majorCode = rst.getCell(13, j).getContents().trim();// 专业代码
				String majorName = rst.getCell(14, j).getContents().trim();// 专业名称
				String trainCode = rst.getCell(15, j).getContents().trim();// 培养方式代码
				String trainName = rst.getCell(16, j).getContents().trim();// 培养方式名称
				String collegeCode = rst.getCell(17, j).getContents().trim();// 所在院系代码
																				// 不允许为空
				String collegeName = rst.getCell(18, j).getContents().trim();// 所在院系名称
																				// 不允许为空
				String className = rst.getCell(19, j).getContents().trim();// 所在班级代码
																			// 不允许为空
				String fd = rst.getCell(20, j).getContents().trim();// 班级辅导员
																	// 不允许为空
				String stuNumber = rst.getCell(21, j).getContents().trim();// 学号
																			// 不允许为空
				// 验证不为空的字段
				if (StrKit.notBlank(IdCard) && StrKit.notBlank(className) && StrKit.notBlank(fd) && StrKit.notBlank(collegeCode)
						&& StrKit.notBlank(collegeName) && StrKit.notBlank(stuNumber)) {
					if (!(stuNum.getBytes().length < bysService.getColumnLength("GRAD", "EXAM_NUM").getBigDecimal("DATALENGTH").intValue())) {
						errormsg = "/考生号 过长";
						t = false;
					}
					if (!(IdCard.getBytes().length < bysService.getColumnLength("GRAD", "ID_CARD").getBigDecimal("DATALENGTH").intValue())) {
						errormsg += "/身份证号 过长";
						t = false;
					}
					if (!(name.getBytes().length < bysService.getColumnLength("GRAD", "NAME").getBigDecimal("DATALENGTH").intValue())) {
						errormsg += "/姓名过长";
						t = false;
					}
					if (!(schoolName.getBytes().length < bysService.getColumnLength("GRAD", "SCHOOL_NAME").getBigDecimal("DATALENGTH").intValue())) {
						errormsg += "/学校名称过长";
						t = false;
					}
					if (!(stuNumber.getBytes().length < bysService.getColumnLength("GRAD", "STUDENT_NUMBER").getBigDecimal("DATALENGTH").intValue())) {
						errormsg += "/学号过长";
						t = false;
					}

					// 性别效验并转换
					if (StrKit.isBlank(sexCode)) {
						sexCode = sexName.contains("女") ? "1" : sexName.contains("男") ? "2" : "9";
					}

//						if (bysService.checkClasses(className)) {//校验班级是否存在//TODO 不存在则创建
					if (!bysService.getCOLLEGECodeOrSave(collegeCode, collegeName, year)) {// 保存院系
						errormsg += "/院系代码保存失败";
						t = false;
					}
					if (t) {
						Record r = bysService.getGRADIdByStuNum(stuNum);// 判断学号是否存在,
						Grad grad = new Grad();
						grad.set("EXAM_NUM", stuNum);// 考生号
						grad.set("ID_CARD", IdCard);// 身份证号
						grad.set("NAME", name);// 学生姓名
						grad.set("SEX", sexCode);// 性别代码
						// 【就业信息册】民族代码
						grad.set("NATION", nationCode);
						// 政治面貌代码
						grad.set("POLITICAL_STATUS_CODE", policyCode);
						grad.set("SCHOOL_CODE", schoolCode);// 院校代码
						grad.set("SCHOOL_NAME", schoolName);// 院校名称
						grad.set("DEGREE", degreeCode);// 学历代码
						if (bysService.checkCollege(collegeCode, collegeName, year)) {
							grad.set("COLLEGE_CODE", collegeCode);// 学院代码
						}
						if(bysService.checkMajor(majorCode, majorName,year,collegeCode,stuNumber)){
							grad.set("MAJOR_CODE", majorCode);// 专业代码
							
						}
						grad.set("TRAIN_STYLE_ID", trainCode);// 培养方式代码
//						grad.set("TRAIN_FIRM", trainFirm);
						if (bysService.checkClasses(stuNumber, className,majorCode,collegeCode,year)) {
							grad.set("CLASSES_CODE", stuNumber.substring(0, 9));
							if (!bysService.saveFdByClassCode(stuNumber.substring(0, 9), fd)) {// 辅导员
								errormsg += "/系统中辅导员名称不存在";
								t = false;
							}
						}
						grad.set("PASSWORD", Encrypt.encrypt(IdCard.substring(IdCard.length() - 6, IdCard.length())));// 密码为身份证后6位
						grad.set("YEAR", year);
						grad.set("STUDENT_NUMBER", stuNumber);// 学号
						if (t) {
							if (r != null) {
								grad.set("ID", r.get("ID"));
								grad.update();
							} else {
								grad.save();
							}
							success++;
							t = false;
						} else {
							t = true;
						}
					} else {
						errormsg = "/此行数据中存在数据过长项，请校验后重新导入";
						t = true;
					}
				} else {
					errormsg = "必填项不能为空";
					t = true;
				}
				if (t) {// 没有保存
					error++;
					sheet.addCell(new Label(0, 0, "考生号", cell));
					sheet.addCell(new Label(1, 0, "身份证号码", cell));
					sheet.addCell(new Label(2, 0, "姓名", cell));
					sheet.addCell(new Label(3, 0, "性别代码", cell));
					sheet.addCell(new Label(4, 0, "性别", cell));
					sheet.addCell(new Label(5, 0, "民族代码", cell));
					sheet.addCell(new Label(6, 0, "民族", cell));
					sheet.addCell(new Label(7, 0, "政治面貌代码", cell));
					sheet.addCell(new Label(8, 0, "政治面貌", cell));
					sheet.addCell(new Label(9, 0, "院校代码", cell));
					sheet.addCell(new Label(10, 0, "院校名称", cell));
					sheet.addCell(new Label(11, 0, "学历代码", cell));
					sheet.addCell(new Label(12, 0, "学历", cell));
					sheet.addCell(new Label(13, 0, "专业代码", cell));
					sheet.addCell(new Label(14, 0, "专业", cell));
					sheet.addCell(new Label(15, 0, "培养方式代码", cell));
					sheet.addCell(new Label(16, 0, "培养方式", cell));
					sheet.addCell(new Label(17, 0, "院系代码", cell));
					sheet.addCell(new Label(18, 0, "院系名称", cell));
					sheet.addCell(new Label(19, 0, "所在班级", cell));
					sheet.addCell(new Label(20, 0, "班级辅导员", cell));
					sheet.addCell(new Label(21, 0, "学号", cell));
					sheet.addCell(new Label(22, 0, "错误信息", cell));
					sheet.addCell(new Label(0, error, stuNum + "", cell));
					sheet.addCell(new Label(1, error, IdCard, cell));
					sheet.addCell(new Label(2, error, name, cell));
					sheet.addCell(new Label(3, error, sexCode, cell));
					sheet.addCell(new Label(4, error, sexName, cell));
					sheet.addCell(new Label(5, error, nationCode, cell));
					sheet.addCell(new Label(6, error, nationName, cell));
					sheet.addCell(new Label(7, error, policyCode, cell));
					sheet.addCell(new Label(8, error, policyName, cell));
					sheet.addCell(new Label(9, error, schoolCode, cell));
					sheet.addCell(new Label(10, error, schoolName, cell));
					sheet.addCell(new Label(11, error, degreeCode, cell));
					sheet.addCell(new Label(12, error, degreeName, cell));
					sheet.addCell(new Label(13, error, majorCode, cell));
					sheet.addCell(new Label(14, error, majorName, cell));
					sheet.addCell(new Label(15, error, trainCode, cell));
					sheet.addCell(new Label(16, error, trainName, cell));
					sheet.addCell(new Label(17, error, collegeCode, cell));
					sheet.addCell(new Label(18, error, collegeName, cell));
					sheet.addCell(new Label(19, error, className, cell));
					sheet.addCell(new Label(20, error, fd, cell));
					sheet.addCell(new Label(21, error, stuNumber, cell));
					sheet.addCell(new Label(22, error, errormsg, mcell));
					Grad bys = new Grad();
					bys.set("STUDENT_NUMBER", stuNumber);
					bys.set("EXAM_NUM", stuNum);
					bys.set("ID_CARD", IdCard);
					bys.set("NAME", name);
					bys.set("SCHOOL_CODE", schoolCode);
					bys.set("SCHOOL_NAME", schoolName);
					list.add(bys);
				}
			}
			book.write();
			book.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			long endTime = System.currentTimeMillis();    //获取结束时间
			System.out.println("程序运行时间：" + (endTime - startTime) + "ms");    //输出程序运行时间
			setAttr("success", success);
			setAttr("error", error);
			setAttr("type", 1);
			setAttr("rfile", rfile);
			setAttr("list", list);
			setAttr("year", year);
			render("imp.jsp");
		}