【POI】的导入与导出功能

一、Apache POI介绍

Apache POI是Apache软件基金会的开源项目，POI提供API给Java程序对Microsoft Office格式档案读和写的功能 .NET的开发人员则可以利用NPOI (POI for .NET) 来存取 Microsoft Office文档的功能。

二、导入功能

//导入
@RequestMapping("/importExcel")
public void importExcel(MultipartFile uploadFile) throws IOException {
	// 获取到一个工作簿，也就是从上传的这个文件中读取内容
	XSSFWorkbook workbook = new XSSFWorkbook(uploadFile.getInputStream());
	// 获取工作表
	XSSFSheet sheet = workbook.getSheetAt(0);
	// 获取最后一行的行号
	int lastRowNum = sheet.getLastRowNum();
	//创建集合
	List<Emp> list = new ArrayList<>();
	// 循环读取Excel，第二行之后的数据
	for (int i = 1; i <= lastRowNum; i++) {
		// 获取一个row
		XSSFRow row = sheet.getRow(i);
		// 获取第一个单元格的编号值
		String ename = row.getCell(1).getStringCellValue();
		String sex   = row.getCell(2).getStringCellValue();
		String age   = row.getCell(3).getStringCellValue();
		String phone = row.getCell(4).getStringCellValue();
		String jointime = row.getCell(5).getStringCellValue();
		Integer deptid  = (int) row.getCell(6).getNumericCellValue();
		//创建对象
		Emp school = new Emp();
		school.setEname(ename);
		school.setSex(sex);
		school.setAge(age);
		school.setPhone(phone);
		school.setJointime(jointime);
		school.setDeptid(deptid);
		//赋值
		list.add(school);
	}

	// 将list中的数据批量插入到数据库中
	empService.addBatch(list);
	
}

三、导出功能

1. 导出功能的私有方法

	// 导出到硬盘
	@SuppressWarnings("resource")
	private void ExportExcel(HttpServletRequest request,HttpServletResponse response,
			String[] handers, List<EmpExtend> list, String excleName) throws Exception {
		
		try {
			response.setContentType("application/vnd.ms-excel");
			String condedFileName = new String(excleName.getBytes("gb2312"), "ISO8859-1");
			response.setHeader("content-disposition", "attachment;filename=" + condedFileName+".xls");

			} catch (UnsupportedEncodingException e) {
				this.logger.error(e.getMessage(), e);
			}
		
		try {
			XSSFWorkbook wb = new XSSFWorkbook();//创建工作簿
			XSSFSheet sheet = wb.createSheet("操作记录导出");//第一个sheet
			XSSFRow rowFirst = sheet.createRow(0);//第一个sheet第一行为标题
			rowFirst.setHeight((short) 500);
			for (int i = 0; i < handers.length; i++) {
				sheet.setColumnWidth((short) i, (short) 4000);// 设置列宽
			}
			//写标题了
			for (int i = 0; i < handers.length; i++) {
			    //获取第一行的每一个单元格
			    XSSFCell cell = rowFirst.createCell(i);
			    //往单元格里面写入值
			    cell.setCellValue(handers[i]);
			}
			for (int i = 0;i < list.size(); i++) {
			    //获取list里面存在是数据集对象
				EmpExtend stu = list.get(i);
			    //创建数据行
			    HSSFRow row = sheet.createRow(i+1);
			    //设置对应单元格的值
			    row.setHeight((short)400);   // 设置每行的高度
			    //"序号","学生名称","所属班级","性别","年龄","联系电话","家庭住址"
			    row.createCell(0).setCellValue(i+1);
			    row.createCell(1).setCellValue(stu.getEname());
			    row.createCell(2).setCellValue(stu.getSex());
			    row.createCell(3).setCellValue(stu.getAge());
			    row.createCell(4).setCellValue(stu.getPhone());
			    row.createCell(5).setCellValue(stu.getJointime());
			    row.createCell(6).setCellValue(stu.getDname());
			    
			}
				OutputStream fOut = null;
				fOut = response.getOutputStream();
				wb.write(fOut);
				fOut.flush();
				fOut.close();
			
			} catch (Exception e) {
				e.printStackTrace();
				throw e;
			}
	}

2. 导出功能的代码块

	//导出
	@RequestMapping("export")
	public void export(HttpServletRequest request,HttpServletResponse response){
		JSONObject result = new JSONObject();
		try {
			String time = TimeUtil.formatTime(new Date(), "yyyyMMddHHmmss");
		    String excelName = "导出"+time;
			//创建对象
		    EmpExtend emp = new EmpExtend();
		    //查询所有数据
		    List<EmpExtend> list =  empService.findEmpPage(emp);
		    //设置标题题目
			String[] handers = {"编号","姓名","性别","年龄","手机号","入职日期","所属部门"};
			// 导出到硬盘 调用上面的方法
			ExportExcel(request,response,handers,list, excelName);
			
			
		} catch (Exception e) {
			e.printStackTrace();
			result.put("", "对不起，导出失败");
		}

	}