excel表格的导入导出

关于excel表格的导入和导出，相信大家在网上已经看了不少了，我这里主要是对我使用这个poi时做个总结！
首先就导入吧，具体实现代码如下

@ResponseBody
	@RequestMapping(value = "/import")
	public String  fileUpload2(@RequestParam(value = "test", required = false) MultipartFile test) throws Exception {
		Map<Integer, String> map = new HashMap<Integer,String>();
		String testname=test.getOriginalFilename();	//得到文件名 
		System.err.println();
		List<User> users = new ArrayList<User>();
		//获取文件输入流
		InputStream in = test.getInputStream();
		String hz = testname . substring(testname.lastIndexOf("."));
		System.out.println(hz);
		if((!".xls".equals(hz))&&(!".xlsx".equals(hz))){
			return "请放入.xls文件或者.xlsx文件";
		}
		//System. . out . println(OriginalFilename);
		Workbook sheets= null;
		if(".xls".equals(hz)){
			sheets=xls(in);
		}else{
			sheets=xlsx(in);
		}
		Sheet sheet = sheets . getSheetAt(0) ;
		for (int i = 1; i<=sheet . getLastRowNum() ; i++) {
			Row row = sheet.getRow(i);
			DataFormatter dataFormat = new DataFormatter( );
			//获取单元格取值
			String value1 = dataFormat.formatCellValue(row. getCell(0));
			String value2 = dataFormat. formatCellValue(row.getCell(1));
			String value3 = dataFormat . formatCellValue( row. getCell(2));
			String value4 = dataFormat . formatCellValue(row. getCell(3));
			String value5 = dataFormat . formatCellValue(row. getCell(4));
			String value6 = dataFormat . formatCellValue( row.getCell(5));
			String value7 = dataFormat. formatCellValue(row.getCell(6));
			String value8 = dataFormat . formatCellValue(row. getCell(7));
			if(!Util.isChinese(value2)){
                String str = "名字格式错误";
                map.put(i, str);
				continue;
			}else if(!Util.IdCard(value5)){
				 String str = "身份证号码格式错误";
	                map.put(i, str);
				continue;
			}else if(!Util.phone(value7)){
				 String str = "手机格式错误";
	              map.put(i, str);
				continue;
			}else if(!Util.isEmail(value8)){
				 String str = "邮箱格式错误";
	              map.put(i, str);
				continue;
			}else if(!Util.identityCard(value4)){
				String str = "没有相应的证件类型";
	              map.put(i, str);
				continue;
			}
			User user = new User(UUID. randomUUID(). toString().replace("-", ""), value1, value2, value3, value4, value5, value6, value7, value8);
			userService.insert(user);
			users. add(user) ；

			sheets.close();
			in.close();
		}
	    String str = "添加成功                              ";
	    Set<Integer> keys = map.keySet();   //此行可省略，直接将map.keySet()写在for-each循环的条件中
        for (Integer key : keys) {
            String errStr = "第"+(key+1)+"行的"+map.get(key)+"!  !";
            str =str +errStr;
        }
	 	return str ;
	}

其实就是循环表格，读取内容，将格式错误的的字段不导入数据库！
接下来就是导出功能，说白了就是将数据库里的数据导成excel表格

@RequestMapping("/export") 
	@ResponseBody
	public String batchExport(HttpServletResponse response) throws Exception {
		//创建对象
response.setCharacterEncoding("utf-8");// 处理编码问题
        
        Cookie fileDownload=new Cookie("fileDownload", "true");
        fileDownload.setPath("/");
        response.addCookie(fileDownload);
		System.err.println("开始导入");
		@SuppressWarnings("resource")
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
		//第一页的名称
		HSSFSheet sheet = hssfWorkbook.createSheet("人员素质信息表");
		 HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
	        //设置水平居中
	        cellStyle.setAlignment(HorizontalAlignment.CENTER);
	        //设置垂直居中
	        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
	        //设置下边框
	        cellStyle.setBorderBottom(BorderStyle.THIN);
	        //设置上边框
	        cellStyle.setBorderTop(BorderStyle.THIN);
	        //设置走边框
	        cellStyle.setBorderLeft(BorderStyle.THIN);
	        //设置右边框
	        cellStyle.setBorderRight(BorderStyle.THIN);

	
		//查询出所有的区域数据
		
		List<User> users = userService.selectAllUser();
		String fileName = "people" + ".xls";//设置要导出的文件的名字
		//新增数据行，并且设置单元格数据
		sheet.setColumnWidth(0, 20 * 256);
		sheet.setColumnWidth(1, 20 * 256);
		sheet.setColumnWidth(2,  10* 256);
		sheet.setColumnWidth(3, 20 * 256);
		sheet.setColumnWidth(4, 20 * 256);
		sheet.setColumnWidth(5, 20 * 256);
		sheet.setColumnWidth(6, 20 * 256);
		sheet.setColumnWidth(7, 20 * 256);
	
		int rowNum = 1;

		String[] headers = {"姓名", "姓名全拼", "性别", "身份证类型", "身份证号码", "出生日期","手机号码","电子邮箱"};
		//headers表示excel表中第一行的表头

		HSSFRow row = sheet.createRow(0);
		//在excel表中添加表
		for (int i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell(i);
			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text);
		}
		//在表中存放查询到的数据放入对应的列
		for (User user : users) {
			HSSFRow row1 = sheet.createRow(rowNum);
			row1.createCell(0).setCellValue(user.getColumn2());
			row1.createCell(1).setCellValue(user.getColumn3());
			row1.createCell(2).setCellValue(user.getColumn4());
			row1.createCell(3).setCellValue(user.getColumn5());
			row1.createCell(4).setCellValue(user.getColumn6());
			row1.createCell(5).setCellValue(user.getColumn7());
			row1.createCell(6).setCellValue(user.getColumn8());
			row1.createCell(7).setCellValue(user.getColumn9());

			rowNum++;
		}

		response.setContentType("application/octet-stream");
		response.setHeader("Content-disposition", "attachment;filename=" + fileName);
		response.flushBuffer();
		hssfWorkbook.write(response.getOutputStream());
		System.err.println("导入成功");
		return "导出成功";
	}

参考https://blog.csdn.net/zxs281/article/details/80856251