java 操作excel 的 POI技术

1.POI导入

1.首先从页面获取文件

   
   
   

    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      

       
       
       //定义一个File属性接收导入的xsl文件
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      

       
       
       private File regionFile;
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      

       
       
       public void setRegionFile(File regionFile) {
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      
		
       
       
       this.regionFile = regionFile;
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      

       
       
       	}
      
      
      
     
     
     
   
   
   
   

 2.使用POI读取xsl文件，并创建workbook

   
   
   

    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      

       
       
       public String importXsl() throws FileNotFoundException, IOException {
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      
		
       
       
       //使用POI读取xsl文件
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      

       
       
       		HSSFWorkbook hssfWorkbook = 
       
       
       new HSSFWorkbook(
       
       
       new FileInputStream(regionFile));
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      
		
       
       
       //当前标签页
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      

       
       
       		HSSFSheet sheetAt = hssfWorkbook.getSheetAt(
       
       
       0);
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      

       
       
       		List<Region> regionList = 
       
       
       new ArrayList();
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      
		
       
       
       //遍历得到row每行
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      
		
       
       
       for (Row row : sheetAt) {
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      
			
       
       
       //判断第一行并去掉它
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      
			
       
       
       int rowNum = row.getRowNum();
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      
			
       
       
       if(rowNum == 
       
       
       0) {
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      
				
       
       
       continue;
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      

       
       
       			}
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      

       
       
       			String id = row.getCell(
       
       
       0).getStringCellValue();
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      

       
       
       			String province = row.getCell(
       
       
       1).getStringCellValue();
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      

       
       
       			String city = row.getCell(
       
       
       2).getStringCellValue();
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      

       
       
       			String district = row.getCell(
       
       
       3).getStringCellValue();
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      

       
       
       			String postcode = row.getCell(
       
       
       4).getStringCellValue();
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      
			
       
       
       //将省份，城市，区域字符串合并
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      

       
       
       			String shortcode = province.substring(
       
       
       0, province.length() - 
       
       
       1) + city.substring(
       
       
       0, city.length() - 
       
       
       1) + district.substring(
       
       
       0, district.length() - 
       
       
       1);
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      
			
       
       
       //使用pingyin4j 将汉字转为拼音
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      

       
       
       			String[] headByString = PinYin4jUtils.getHeadByString(shortcode);
      
      
      
     
     
     
    

     
     
     

      
      
      

     
     
     
     
     
     

      
      
      

       
       
       			shortcode = StringUtils.join(headByS