Excel数据的导入与导出

Excel数据的导入与导出

加入jar包

<!-- Excel导出的jar包 -->
		<dependency>
			<groupId>commons-net</groupId>
			<artifactId>commons-net</artifactId>
			<version>3.3</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>4.0.1</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>4.0.1</version>
		</dependency>

一、导出Excel到本地

Ajax发送请求到Controller
//导出Excel

function outExcel(){
	$.ajax({
		   url:'downAmountMedicine.act',
		   type:'Post',
		   data:{startTime:$("#startTime").val(),endTime:$("#endTime").val(),medName:$("#medName").val()},
		   dataType:'JSON',
		   success:function(msg){
			if(msg.id==1){
				window.location.href = "downLoadFile.act?savePath=" + msg.datas.savePath+"&nowDate="+new Date();
			}
		   },
		   error:function(msg){
			   layer.alert("请联系管理员!");
			}
		  })

//导出Excel到本地磁盘

@PostMapping("downAmountMedicine.act")
	public JsonMsg downAmountMedicine(HttpServletResponse response,String startTime,String endTime,String medName) throws IOException {
 //创建HSSFWorkbook对象(excel的文档对象)
		
        HSSFWorkbook wb = new HSSFWorkbook();
        //换行配置
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);
        
//建立新的sheet对象(excel的表单)
        HSSFSheet sheet=wb.createSheet("药房库存表");
//在sheet里创建第一行,参数为行索引(excel的行),可以是0~65535之间的任何一个
        HSSFRow row1=sheet.createRow(0);
//创建单元格(excel的单元格,参数为列索引,可以是0~255之间的任何一个
        HSSFCell cell=row1.createCell(0);
        //设置单元格内容
        cell.setCellValue("药房库存表");
        //强制换行
        cell.setCellStyle(cellStyle);
        
        
//合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,4));
//在sheet里创建第二行
        
        HSSFRow row2=sheet.createRow(1);
        //创建单元格并设置单元格内容
        row2.createCell(0).setCellValue("药品名称");
        row2.createCell(1).setCellValue("有效期");
        row2.createCell(2).setCellValue("批次");
        row2.createCell(3).setCellValue("数量");
        row2.createCell(4).setCellValue("价格");
        //在sheet里创建第三行
//获取需要写入的数据
        List<MedicineParamacyInfo> medicineInfo= medicineInventoryCheckService.selectMedicineParamacy(null,null, medName, startTime, endTime);
//for循环一行行写入
        for(int i=0;i<medicineInfo.size();i++) {
        	HSSFRow row=sheet.createRow(2+i);
       	 row.createCell(0).setCellValue(medicineInfo.get(i).getMedName());
            row.createCell(1).setCellValue(medicineInfo.get(i).getPhaEffdate());
            row.createCell(2).setCellValue(medicineInfo.get(i).getPhaBatch());
            row.createCell(3).setCellValue(medicineInfo.get(i).getPhaAmount()+" "+medicineInfo.get(i).getMedRoomUnit());
            row.createCell(4).setCellValue(medicineInfo.get(i).getPrice()+"元");
        }
        	
        
        


//输出Excel文件
        String savePath = "D://药房库存表.xls";//输出路径
        OutputStream output=new FileOutputStream(savePath);
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=details.xls");
        response.setContentType("application/msexcel");
        wb.write(output);
        output.close();
        //Json返回值
        JsonMsg msg = new JsonMsg();
        msg.setId(1);
        msg.getDatas().put("savePath", savePath);
        return msg;
	}

//调取本地服务器路径,浏览器下载文件

@GetMapping("storeIoStatisticDownLoadFile.act")
	public ResponseEntity<byte[]> downLoadFile(String savePath) throws IOException{
		HttpHeaders headers = new HttpHeaders(); 
        File file = new File(savePath);
        String[] name = savePath.split("//");//路径切割
		headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);    
		headers.setContentDispositionFormData("attachment",  new String(name[name.length-1].getBytes("UTF-8"), "iso-8859-1"));  
		
		return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file), headers, HttpStatus.CREATED);
	}

二、导入Excel到数据库中

/**
	 * Excel导入
	 * 
	 * @param filePath
	 * @return
	 * @throws IOException
	 */
	public Long importExcel(String filePath) throws Exception {

		// 查询高校信息
		List<CollegesInfo> collegeList = collegesInfoMapper.selectAllColleges();
		// 参训参数信息
		List<ParamInfo> paramInfo = paramInfoMapper.selectAllParam();

		System.out.println("readAndCheckInsuranceData");
		Long result = 0L;
		InputStream stream = null;
		// 读EXCEL文件内容
		// 读取文件流并转化为workbook对象
		stream = new FileInputStream(filePath);
		Workbook workbook = null;
		if (filePath.endsWith(".xlsx")) {
			workbook = new XSSFWorkbook(stream);
		} else if (filePath.endsWith(".xls") || filePath.endsWith(".et")) {
			workbook = new HSSFWorkbook(stream);
		}
		stream.close();
		// 获取第一个sheet表,也可使用sheet表名获取
		Sheet sheet = workbook.getSheetAt(0);
		// 获取行
		Iterator<Row> rows = sheet.rowIterator();
		Row row;
		Cell cell;

		// 获取表头
		ArrayList<String> userTitleList = new ArrayList<String>();
		ArrayList<String> ResumeTitleList = new ArrayList<String>();
		ArrayList<String> educationalBackgroundTitleList = new ArrayList<String>();
		ArrayList<String> socialExperienceTitleList = new ArrayList<String>();

		// 存取对应数据
		UserInfo userInfo = new UserInfo();
		ResumeInfo resumeInfo = new ResumeInfo();
		EducationalBackground educationalBackground = new EducationalBackground();
		SocialExperience socialExperience = new SocialExperience();

		System.out.println("开始读取");
		// 边读取边核对
		while (rows.hasNext()) {
			// 获取每一行
			row = rows.next();
			// 获取用户表头
			Long userNumber = 0L;
			if (PoiUtil.getCellValue(row.getCell(0)).equals("用户个人信息")) {
				Iterator<Cell> cells = row.cellIterator();
				while (cells.hasNext()) {
					cell = cells.next();
					String cellValue = PoiUtil.getCellValue(cell);
					userTitleList.add(cellValue);
				}
				continue;
			}
			if (PoiUtil.getCellValue(row.getCell(0)).equals("USER_INFO")) {
				for (int i = 0; i < userTitleList.size(); i++) {
					cell = row.getCell(i);
					String cellValue = PoiUtil.getCellValue(cell);
					PoiUtil.setUserInfoCheckData(cellValue.trim(), userTitleList.get(i), userInfo);
				}
				userInfo.setUserState(1L);
				// 用户数据插入完毕
				result = userInfoMapper.insertExcelUser(userInfo);
			}
			if (PoiUtil.getCellValue(row.getCell(0)).equals("简历信息")) {
				Iterator<Cell> cells = row.cellIterator();
				while (cells.hasNext()) {
					cell = cells.next();
					String cellValue = PoiUtil.getCellValue(cell);
					ResumeTitleList.add(cellValue);
				}
				continue;
			}

			if (PoiUtil.getCellValue(row.getCell(0)).equals("RESUME_INFO")) {
				for (int i = 0; i < ResumeTitleList.size(); i++) {
					cell = row.getCell(i);
					String cellValue = PoiUtil.getCellValue(cell);
					PoiUtil.setResumeCheckData(cellValue.trim(), ResumeTitleList.get(i), resumeInfo, collegeList,
							paramInfo);
				}
				resumeInfo.setUserId(userInfo.getUserId());
				resumeInfo.setResumeState(1L);
				// 简历数据插入完毕
				result = resumeInfoMapper.insertExcelResumeInfo(resumeInfo);
				System.out.println(resumeInfo);
			}

			if (PoiUtil.getCellValue(row.getCell(0)).equals("教育背景")) {
				Iterator<Cell> cells = row.cellIterator();
				while (cells.hasNext()) {
					cell = cells.next();
					String cellValue = PoiUtil.getCellValue(cell);
					educationalBackgroundTitleList.add(cellValue);
				}
				continue;
			}
			if (PoiUtil.getCellValue(row.getCell(0)).equals("EDUCATIONAL_BACKGROUND")) {
				for (int i = 0; i < educationalBackgroundTitleList.size(); i++) {
					cell = row.getCell(i);
					String cellValue = PoiUtil.getCellValue(cell);
					PoiUtil.setEducationalBackgroundCheckData(cellValue.trim(), educationalBackgroundTitleList.get(i),
							educationalBackground);
				}
				// 教育经历数据插入完毕
				educationalBackground.setResumeId(resumeInfo.getResumeId());
				result = educationalBackgroundMapper.insertExcelEducationalBackground(educationalBackground);
			}

			if (PoiUtil.getCellValue(row.getCell(0)).equals("社会经历")) {
				Iterator<Cell> cells = row.cellIterator();
				while (cells.hasNext()) {
					cell = cells.next();
					String cellValue = PoiUtil.getCellValue(cell);
					socialExperienceTitleList.add(cellValue);
				}
				continue;
			}
			if (PoiUtil.getCellValue(row.getCell(0)).equals("SOCIAL_EXPERIENCE")) {
				for (int i = 0; i < socialExperienceTitleList.size(); i++) {
					cell = row.getCell(i);
					String cellValue = PoiUtil.getCellValue(cell);
					PoiUtil.setSocialExperienceCheckData(cellValue.trim(), socialExperienceTitleList.get(i),
							socialExperience);
				}
				// 教育经历数据插入完毕
				socialExperience.setResumeId(resumeInfo.getResumeId());
				result = socialExperienceMapper.insertExcelsocialExperience(socialExperience);
			}

		}
		stream.close();
		if (result <= 0) {
			throw new RuntimeException("发生异常"); 
		}
		return result;

	}

导入判断

public class PoiUtil {

	public static String getCellValue(Cell cell) {
		String cellValue = "";
		if (null != cell) {
			// 以下是判断数据的类型
			switch (cell.getCellType()) {
			case NUMERIC: // 数字
			{
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					// 判断是否为日期类型
					Date date = cell.getDateCellValue();
					// DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm");
					DateFormat formater = new SimpleDateFormat("yyyy/MM/dd");
					cellValue = formater.format(date);
				} else {
					// 有些数字过大,直接输出使用的是科学计数法: 2.67458622E8 要进行处理
					DecimalFormat df = new DecimalFormat("####.####");
					cellValue = df.format(cell.getNumericCellValue());
					// cellValue = cell.getNumericCellValue() + "";
				}
				break;
			}
			case STRING: // 字符串
			{
				cellValue = cell.getStringCellValue();
				break;
			}
			case BOOLEAN: // Boolean
			{
				cellValue = cell.getBooleanCellValue() + "";
				break;
			}
			case FORMULA: // 公式
			{
				try {
					// 如果公式结果为字符串
					cellValue = String.valueOf(cell.getStringCellValue());
				} catch (IllegalStateException e) {
					if (HSSFDateUtil.isCellDateFormatted(cell)) {// 判断是否为日期类型
						Date date = cell.getDateCellValue();
						// DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm");
						DateFormat formater = new SimpleDateFormat("yyyy/MM/dd");
						cellValue = formater.format(date);
					} else {
						FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper()
								.createFormulaEvaluator();
						evaluator.evaluateFormulaCell(cell);
						// 有些数字过大,直接输出使用的是科学计数法: 如2.67458622E8 要进行处理
						DecimalFormat df = new DecimalFormat("####.####");
						cellValue = df.format(cell.getNumericCellValue());
						// cellValue = cell.getNumericCellValue() + "";
					}
				}
				break;
			}
			case BLANK: // 空值
				cellValue = "";
				break;
			case ERROR: // 故障
				cellValue = "非法字符";
				break;
			default:
				cellValue = "未知类型";
				break;
			}
		}
		return cellValue;
	}

	/**
	 * 用户
	 * 
	 * @param value
	 * @param title
	 * @param target
	 */
	public static void setUserInfoCheckData(String value, String title, UserInfo target) {
		switch (title) {
		case "用户账号": {
			target.setUserAcc(value);
			break;
		}
		case "用户密码": {
			String md5DigestAsHex = DigestUtils.md5DigestAsHex(value.getBytes());
			target.setUserPwd(md5DigestAsHex);
			break;
		}
		case "用户昵称": {
			target.setUserNickname(value);
			break;
		}
		case "用户真实姓名": {
			target.setUserTruename(value);
			break;
		}
		case "性别": {
			Long sex = 0L;
			if (value.equals("男")) {
				sex = 1L;
			} else {
				sex = 2L;
			}
			target.setUserSex(sex);
			break;
		}
		case "年龄": {
			try {
				target.setUserAge(Long.parseLong(value));
			} catch (Exception e) {
				System.out.println("年龄数据错误");
				target.setUserAge(null);
			}
			break;
		}
		case "手机号": {
			try {
				target.setUserPhone(value);
			} catch (Exception e) {
				System.out.println("手机数据错误");
				target.setUserAge(null);
			}
			break;
		}
		case "通讯地址": {
			try {
				target.setUserAddress(value);
			} catch (Exception e) {
				System.out.println("通讯地址数据错误");
				target.setUserAge(null);
			}
			break;
		}
		case "就业情况": {
			try {
				Long userEmploymentStatus = 0L;
				if (value.equals("是")) {
					userEmploymentStatus = 1L;
				} else {
					userEmploymentStatus = 2L;
				}
				target.setUserEmploymentStatus(userEmploymentStatus);
			} catch (Exception e) {
				System.out.println("就业情况数据错误");
				target.setUserAge(null);
			}
			break;
		}
		case "所在公司": {
			try {
				target.setUserEmploymentCompany(value);
			} catch (Exception e) {
				System.out.println("所在公司数据错误");
				target.setUserAge(null);
			}
			break;
		}
		default: {
			System.out.println(title);
			break;
		}
		}
	}

	/**
	 * 简历
	 * 
	 * @param value
	 * @param title
	 * @param target
	 */
	public static void setResumeCheckData(String value, String title, ResumeInfo target, List<CollegesInfo> collegeList,
			List<ParamInfo> paramInfo) {
		switch (title) {
		case "姓名": {
			target.setResumeName(value);
			break;
		}
		case "性别": {
			try {
				Long sex = 0L;
				for (int i = 0; i < paramInfo.size(); i++) {
					if (paramInfo.get(i).getParamCode().equals("SEX") && paramInfo.get(i).getKeyName().equals(value)) {
						sex = Long.parseLong(paramInfo.get(i).getKeyValue());
						break;
					}
				}
				target.setResumeSex(sex);
			} catch (Exception e) {
				System.out.println("性别数据错误");
				target.setResumeSex(null);
			}
			break;
		}
		case "出生年份": {
			target.setResumeBirth(value);
			break;
		}
		case "政治面貌": {

			try {
				Long politicsStatus = 0L;
				for (int i = 0; i < paramInfo.size(); i++) {
					if (paramInfo.get(i).getParamCode().equals("POLITICS_STATUS")
							&& paramInfo.get(i).getKeyName().equals(value)) {
						politicsStatus = Long.parseLong(paramInfo.get(i).getKeyValue());
						break;
					}
				}
				target.setPoliticsStatus(politicsStatus);
			} catch (Exception e) {
				System.out.println("政治面貌数据错误");
				target.setPoliticsStatus(null);
			}
			break;
		}
		case "专业": {
			try {
				target.setProfessionName(value);
			} catch (Exception e) {
				System.out.println("专业数据错误");
				target.setProfessionName(null);
			}
			break;
		}
		case "住址": {
			try {
				target.setPoliticsAdress(value);
			} catch (Exception e) {
				System.out.println("住址数据错误");
				target.setPoliticsAdress(null);
			}
			break;
		}
		case "最高学历": {

			try {
				Long maxEducation = 0L;
				System.out.println(value);
				for (int i = 0; i < paramInfo.size(); i++) {
					if (paramInfo.get(i).getParamCode().equals("DEGREE")
							&& paramInfo.get(i).getKeyName().equals(value)) {
						maxEducation = Long.parseLong(paramInfo.get(i).getKeyValue());
						break;
					}
				}
				target.setMaxEducation(maxEducation);
			} catch (Exception e) {
				System.out.println("最高学历数据错误");
				target.setMaxEducation(null);
			}
			break;
		}
		case "工作经验/年": {
			try {
				target.setWorkExperience(Long.parseLong(value));
			} catch (Exception e) {
				System.out.println("工作经验/年数据错误");
				target.setWorkExperience(null);
			}
			break;
		}
		case "手机号": {
			try {
				target.setPoliticsPhone(value);
			} catch (Exception e) {
				System.out.println("手机号数据错误");
				target.setPoliticsPhone(null);
			}
			break;
		}
		case "简历隐私状态": {
			try {

				Long privacyState = null;

				if (value.equals("公开")) {
					privacyState = 1L;
				} else if (value.equals("仅投递方")) {
					privacyState = 2L;
				}
				target.setPrivacyState(privacyState);
			} catch (Exception e) {
				System.out.println("简历隐私状态数据错误");
				target.setPrivacyState(null);
			}
			break;
		}
		case "技能证书": {
			try {
				target.setSkillCertificate(value);
			} catch (Exception e) {
				System.out.println("技能证书数据错误");
				target.setSkillCertificate(null);
			}
			break;
		}
		case "自我评价": {
			try {
				target.setSeleAssessment(value);
			} catch (Exception e) {
				System.out.println("自我评价数据错误");
				target.setSeleAssessment(null);
			}
			break;
		}
		case "高校名称": {
			try {
				Long collegesId = null;
				for (int i = 0; i < collegeList.size(); i++) {
					if (collegeList.get(i).getCollegesName().equals(value)) {
						collegesId = collegeList.get(i).getCollegesId();
						break;
					}
				}
				target.setCollegesId(collegesId);
			} catch (Exception e) {
				System.out.println("高校名称数据错误");
				target.setCollegesId(null);
			}
			break;
		}
		default: {
			System.out.println(title);
			break;
		}
		}
	}

	/**
	 * 教育背景
	 * 
	 * @param value
	 * @param title
	 * @param target
	 */
	public static void setEducationalBackgroundCheckData(String value, String title, EducationalBackground target) {
		switch (title) {
		case "教育背景时间": {
			target.setEducationalBackgroundTime(value);
			break;
		}
		case "学校名称": {
			target.setSchoolName(value);
			break;
		}
		case "专业名称": {
			target.setProfessionName(value);
			break;
		}
		default: {
			System.out.println(title);
			break;
		}
		}
	}
	
	
	
	/**
	 * 教育背景
	 * 
	 * @param value
	 * @param title
	 * @param target
	 */
	public static void setSocialExperienceCheckData(String value, String title, SocialExperience target) {
		switch (title) {
		case "经历时间": {
			target.setSocialExperienceTime(value);
			break;
		}
		case "公司名称": {
			target.setCompanyName(value);
			break;
		}
		case "工作内容": {
			target.setJobContent(value);
			break;
		}
		default: {
			System.out.println(title);
			break;
		}
		}
	}
	
}
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值