java读取xls和xlsx文件

最新推荐文章于 2024-07-13 03:14:42 发布

nich_olas

最新推荐文章于 2024-07-13 03:14:42 发布

阅读量2k

点赞数

分类专栏： java

本文链接：https://blog.csdn.net/nich_olas/article/details/101203934

版权

java 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

导入依赖

		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-ooxml</artifactId>
		    <version>3.9</version>
		</dependency>

java实现代码

public class ExcelUtils {
	
	public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
	public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
	

	/**
	 * 
	 * <p>Title: readExecl</p>  
	 * <p>Description:读取Execl</p>  
	 * @author zhouaokai  
	 */
	public static Object readExecl() throws IOException {
		File file=new File(path); 
		Object count = null;
		if(file.exists()){
			String[] split = path.split("\\.");
			if(OFFICE_EXCEL_2003_POSTFIX.equals(split[split.length-1])){
				count = readXls(path);
			}else if(OFFICE_EXCEL_2010_POSTFIX.equals(split[split.length-1])){
				count = readXlsx(path);
			}
		}
		return count;
	}
	
	/**
	 * 
	 * <p>Title: readExecl</p>  
	 * <p>Description:读取Execl</p>  
	 * @author zhouaokai  
	 */
	public static Object readXlsx(String path) throws IOException {
        InputStream is = new FileInputStream(path);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        int numberOfSheets = xssfWorkbook.getNumberOfSheets();
        int count = 0;
        try {
        	for (int s = 0; s < numberOfSheets; s++) {
				try {
					XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(s);
					for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
						xssfRow = xssfSheet.getRow(rowNum);
						if (xssfRow != null) {							
							for (int i = 0; i < list.size(); i++) {
								XSSFCell cell = xssfRow.getCell(i);
								if(cell == null){
									continue;
								}
								String value = getValue(cell);
								String th_value = list.get(i);								
							}
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
					logger.info("读取第"+(s+1)+"个sheet异常");
				}
			}
		} catch (NumberFormatException e) {
			logger.info("读取Xlsx文件异常");
			e.printStackTrace();
		}finally {
			is.close();
		}
        return count;
    }
	
	
	/**
	 * 
	 * <p>Title: readExecl</p>  
	 * <p>Description:读取Execl</p>  
	 * @author zhouaokai  
	 */
	public static Object readXls(String path) throws IOException {
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        int numberOfSheets = hssfWorkbook.getNumberOfSheets();
        try {
        	for (int s = 0; s < numberOfSheets; s++) {
				try {
					HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(s);
					List<String> list = new ArrayList<>();
					HSSFRow hssfRow = hssfSheet.getRow(0);		
					for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
						hssfRow = hssfSheet.getRow(rowNum);
						if (hssfRow != null) {							
							for (int i = 0; i < list.size(); i++) {
								HSSFCell cell = hssfRow.getCell(i);
								if(cell == null){
									continue;
								}
								String value = getValue(cell);
								String th_value = list.get(i);								
							}
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
					logger.info("读取第"+(s+1)+"个sheet异常");
				} 
			}
		} catch (NumberFormatException e) {
			logger.info("读取Xls文件异常");
			e.printStackTrace();
		}finally {
			is.close();
		}
        return count;
    }
	
	
	@SuppressWarnings({ "static-access" })
    private static String getValue(XSSFCell xssfRow) {
		//将数字当成字符串来读，防止将1读取成1.0
    	if(xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC){  
    		xssfRow.setCellType(xssfRow.CELL_TYPE_STRING);  
        }  
        if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfRow.getBooleanCellValue());
        } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
            return String.valueOf(xssfRow.getNumericCellValue());
        } else {
            return String.valueOf(xssfRow.getStringCellValue());
        }
    }

    @SuppressWarnings({ "static-access" })
    private static String getValue(HSSFCell hssfCell) {
    	//将数字当成字符串来读，防止将1读取成1.0
    	if(hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC){  
    		hssfCell.setCellType(hssfCell.CELL_TYPE_STRING);  
        }  
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }




}