java读取xls和xlsx文件

系统管理员 2023-10-11 12:13 47阅读 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";
 	
 
 	/** * * Title: readExecl * Description:读取Execl * @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;
 	}
 	
 	/** * * Title: readExecl * Description:读取Execl * @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;
 }
 	
 	
 	/** * * Title: readExecl * Description:读取Execl * @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());
 }
 }
 
 
 
 
 }