poi导入Excel工具类

矫情吗；* 2024-04-17 05:28 62阅读 0赞

package exportexcel;
    
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.InputStream;
    import java.math.BigDecimal;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.DateUtil;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.junit.Test;
    
    public class ImportExcelUtil {
    	/**
    	 * 
    	 * @param is 上传的文件，传入进来的的流
    	 * @param ExcelName 文件的名称，拿到后缀 xls创建HSSFWorkbook，xlsx创建 XSSFWorkbook
    	 * @throws Exception
    	 */
    	public void importExcel(InputStream is,String ExcelName) throws Exception {
    		
    		Workbook wb = null;//工作簿
    		Sheet sheet = null;//工作表
    		Row row = null;//行
    		
    		//1、创建一个工作簿对象，(面向对象思想,把读取的excel封装成对象，那么我们就可以进行操作它了)
    		String Exceln=ExcelName.substring(ExcelName.lastIndexOf("."));//获取上传文件的后缀
    		System.out.println("exceln："+Exceln);
    		if(Exceln.equals(".xls")){
    			 wb = new HSSFWorkbook(is);
    			
    		}else if(Exceln.equals(".xlsx")){
    			 wb = new XSSFWorkbook(is);
    		}
    		//2、获取sheet页
    		 	sheet = wb.getSheetAt(0);//获取第0个sheet页
    		//3、判断是否为空
    		if(sheet==null){
    			return;
    		}
    		//4、遍历行  sheet.getLastRowNum() 获取最后一行
    		for(int rowNum=2;rowNum<=sheet.getLastRowNum();rowNum++){
    			//5、获取行
    				row = sheet.getRow(rowNum);
    			if(row==null){//如果行为空，结束本次循环，继续下一次循环
    				continue;
    			}
    			/**
    			 * 这里根据你单元格的多少来创建多少个cell对象
    			 */
    			Cell cell0 = row.getCell(0, Row.RETURN_BLANK_AS_NULL);//当Excel单元格为空的时候，getCell是为空的，是不对创建cell对象的，会出现空指针异常	Row.RETURN_BLANK_AS_NULL 
    			Cell cell1 = row.getCell(1, Row.RETURN_BLANK_AS_NULL);	
    			Cell cell2 = row.getCell(2, Row.RETURN_BLANK_AS_NULL);	
    			Cell cell3 = row.getCell(3, Row.RETURN_BLANK_AS_NULL);	
    			Cell cell4 = row.getCell(4, Row.RETURN_BLANK_AS_NULL);	
    			Cell cell5 = row.getCell(5, Row.RETURN_BLANK_AS_NULL);	
    			Cell cell6 = row.getCell(6, Row.RETURN_BLANK_AS_NULL);	
    			Cell cell7 = row.getCell(7, Row.RETURN_BLANK_AS_NULL);	
    			Cell cell8 = row.getCell(8, Row.RETURN_BLANK_AS_NULL);	
    			Cell cell9 = row.getCell(9, Row.RETURN_BLANK_AS_NULL);	
    			Cell cell10 = row.getCell(10, Row.RETURN_BLANK_AS_NULL);	
    			Cell cell11 = row.getCell(11, Row.RETURN_BLANK_AS_NULL);	
    			Cell cell12 = row.getCell(12, Row.RETURN_BLANK_AS_NULL);	
    			Cell cell13 = row.getCell(13, Row.RETURN_BLANK_AS_NULL);	
    			Cell cell14 = row.getCell(14, Row.RETURN_BLANK_AS_NULL);	
    			Cell cell15 = row.getCell(15, Row.RETURN_BLANK_AS_NULL);	
    			Cell cell16 = row.getCell(16, Row.RETURN_BLANK_AS_NULL);	
    			Cell cell17 = row.getCell(17, Row.RETURN_BLANK_AS_NULL);	
    			Cell cell18 = row.getCell(18, Row.RETURN_BLANK_AS_NULL);
    			
    			System.out.print("序号："+getValue(cell0)+" ");
    			System.out.print("姓名："+getValue(cell1)+" ");
    			System.out.print("参加工作时间："+getValue(cell2)+" ");
    			System.out.print("XXX："+getValue(cell3)+" ");//提供模板的人，为了计算工龄而有的一行，数据没意义，因此不插入。
    			System.out.print("工龄："+getValue(cell4)+" ");
    			System.out.print("公休假："+getValue(cell5)+" ");
    			System.out.print("实休："+getValue(cell6)+" ");
    			System.out.print("1次："+getValue(cell7)+" ");
    			System.out.print("2次："+getValue(cell8)+" ");
    			System.out.print("3次："+getValue(cell9)+" ");
    			System.out.print("4次："+getValue(cell10)+" ");
    			System.out.print("5次："+getValue(cell11)+" ");
    			System.out.print("6次："+getValue(cell12)+" ");
    			System.out.print("7次："+getValue(cell13)+" ");
    			System.out.print("8次："+getValue(cell14)+" ");
    			System.out.print("9次："+getValue(cell15)+" ");
    			System.out.print("10次："+getValue(cell16)+" ");
    			System.out.print("11次："+getValue(cell17)+" ");
    			System.out.print("12次："+getValue(cell18)+" ");
    		}
    	}
    	/**
    	 * 根据单元格不同属性返回字符串数值
    	 * @param cell
    	 * @return
    	 */
    	public String getValue(Cell cell) {
    	    String value = "";
    	    if (cell != null) {
    	        switch (cell.getCellType()) {
    	        case Cell.CELL_TYPE_FORMULA://公式处理 
    	            try {
    	                value = String.valueOf(cell.getNumericCellValue());
    	            } catch (IllegalStateException e) {
    	                value = String.valueOf(cell.getRichStringCellValue());
    	            }
    	            break;
    	        case Cell.CELL_TYPE_NUMERIC://数值处理
    	        		if(DateUtil.isCellDateFormatted(cell)){
    	        			value =  String.valueOf(cell.getDateCellValue());
    	        		}else{
    	        			cell.setCellType(cell.CELL_TYPE_STRING);
    	        			String temp = cell.getStringCellValue();
    	        			/*
    	        			 * 判断是否包含小数点，如果不含小数点，则以字符串读取，
    	        			 * 如果含小数点，则转换为Double类型的字符串
    	        			 */
    	        			if (temp.indexOf(".") > -1) {
    							value = String.valueOf(new Double(temp)).trim();
    						} else {
    							value = temp.trim();
    						}
    	        		}
    	            break;
    	        case Cell.CELL_TYPE_STRING://字符处理
    	            value = String.valueOf(cell.getRichStringCellValue());
    	            break;
    	        case HSSFCell.CELL_TYPE_BLANK://空值处理      
    	            value="";      
    	            break;      
    	        case Cell.CELL_TYPE_BOOLEAN://boolean值处理    
    	        	 value = String.valueOf(cell.getBooleanCellValue());
    	            break;      
    	        case Cell.CELL_TYPE_ERROR://错误处理      
    	            break;      
    	        }
    	    }
    	    return value;
    	}
    	
    	@Test
    	public void fun() throws Exception{
    		
    		InputStream input = new FileInputStream("C:\\Users\\Administrator\\Desktop\\章贡区OA需求\\章贡区OA需求20190816\\章贡区OA需求\\区工信局休假汇总表.xls");
    		String excelName="区工信局休假汇总表.xls";
    		importExcel(input, excelName);
    	}
    }

效果图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NTU1NDkw_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NTU1NDkw_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/7390b786c79b4191b4feb43623665d69.png