apache POI技术

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

①下载开发包并解压缩：

②在项目引入POI的依赖

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.11</version>
		</dependency>

③POI的使用

@Test
	public void test1() throws FileNotFoundException, IOException{
		String filePath = "C:\\Users\\区域导入测试数据.xls";
		//包装一个Excel文件对象
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File(filePath)));
		//读取文件中第一个Sheet标签页
		HSSFSheet hssfSheet = workbook.getSheetAt(0);
		//遍历标签页中所有的行
		for (Row row : hssfSheet) {
			System.out.println();
			for (Cell cell : row) {
				String value = cell.getStringCellValue();
				System.out.print(value + " ");
			}
		}
	}

案例：导出，页面中有一个导出功能，点击之后会去数据库中查所有的数据，最后以下载的方式来实现数据库

          数据表格xls导出

              

导入：OCUpload插件实现上传（看其他的笔记），上传后实现数据库信息的导入

	public String doImport() throws IOException{
		List<Region> regionList=new ArrayList<>();
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(regionFile));
		//读取文件中第一个Sheet标签页
		HSSFSheet hssfSheet = workbook.getSheetAt(0);
		//遍历标签页中所有的行
		for (Row row : hssfSheet) {
			Region region = new Region();
			String regionId = row.getCell(0).getStringCellValue();
			if(regionId.contains("区域编号")){
				continue;
			}
			String province = row.getCell(1).getStringCellValue();
			String city = row.getCell(2).getStringCellValue();
			String district = row.getCell(3).getStringCellValue();
			String postcode = row.getCell(4).getStringCellValue();
			region.setId(regionId);
			region.setProvince(province);
			region.setCity(city);
			region.setDistrict(district);
			region.setPostcode(postcode);
			
			province=province.substring(0, province.length()-1);
			city=city.substring(0, city.length()-1);
			district=district.substring(0, district.length()-1);
			
			String info=province+city+district;
			String[] headByString = PinYin4jUtils.getHeadByString(info);  省市区首字母大写
			region.setShortcode(StringUtils.join(headByString));
			
			String citycode = PinYin4jUtils.hanziToPinyin(city);
			region.setCitycode(citycode);
			
			regionList.add(region);
		}
		regionService.save(regionList);
		return HOME;
	}

导出数据：可以实现类似下载的功能将数据库中的信息，转换成表格。

注：如果tomcat启动内存不够，可以扩展一下，或者clean

注：发送请求的时候千万不要使用ajax,用location.href就可以了

	public String doExport() throws Exception{
		List<Region> regionList=regionService.findAll();
		HSSFWorkbook workbook = new HSSFWorkbook();
		//创建一个标签页
		HSSFSheet sheet = workbook.createSheet("分区数据");
		//创建标题行
		HSSFRow headRow = sheet.createRow(0);
		headRow.createCell(0).setCellValue("区域编号");
		headRow.createCell(1).setCellValue("省份");
		headRow.createCell(2).setCellValue("城市");
		headRow.createCell(3).setCellValue("区域");
		headRow.createCell(4).setCellValue("邮编");
		
		for (Region region : regionList) {
			HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
			dataRow.createCell(0).setCellValue(region.getId());
			dataRow.createCell(1).setCellValue(region.getProvince());
			dataRow.createCell(2).setCellValue(region.getCity());
			dataRow.createCell(3).setCellValue(region.getDistrict());
			dataRow.createCell(4).setCellValue(region.getPostcode());
		}
		
		HttpServletResponse response = ServletActionContext.getResponse();
		OutputStream out = response.getOutputStream();
		response.reset();
		String filename = "区域数据.xls";
		String contentType = ServletActionContext.getServletContext().getMimeType(filename);
		response.setContentType(contentType);
		
		//获取客户端浏览器类型
		String agent = ServletActionContext.getRequest().getHeader("User-Agent");
		filename = FileUtils.encodeDownloadFilename(filename, agent);
		response.setHeader("content-disposition", "attachment;filename="+filename);

		workbook.write(out);
		out.flush();
		out.close();
		return HOME;
	}