Java读取Excel文件

系统管理员 2022-04-11 10:30 388阅读 0赞

简介：总结Java读取Excel文件

1.  添加maven依赖

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

1.  本例的excel，FirstTests.xlsx如下图：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2dhbGVuMjAxNg_size_16_color_FFFFFF_t_70]
2.  代码如下：  
    ExcelData.java

package test.java.utils;
    
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import java.io.FileInputStream;
    /**
     * Author: 灵枢
     * Date: 2018/12/05
     * Time: 17:21
     * Description:读取Excel数据
     */
    public class ExcelData {
    	private XSSFSheet sheet;
    
    	/**
    	 * 构造函数，初始化excel数据
    	 * @param filePath  excel路径
    	 * @param sheetName sheet表名
    	 */
    	ExcelData(String filePath,String sheetName){
    		FileInputStream fileInputStream = null;
    		try {
    			fileInputStream = new FileInputStream(filePath);
    			XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);
    			//获取sheet
    			sheet = sheets.getSheet(sheetName);
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    	}
    	
    	/**
    	 * 根据行和列的索引获取单元格的数据
    	 * @param row
    	 * @param column
    	 * @return
    	 */
    	public String getExcelDateByIndex(int row,int column){
    		XSSFRow row1 = sheet.getRow(row);
    		String cell = row1.getCell(column).toString();
    		return cell;
    	}
    
    	/**
    	 * 根据某一列值为“******”的这一行，来获取该行第x列的值
    	 * @param caseName
    	 * @param currentColumn 当前单元格列的索引
    	 * @param targetColumn 目标单元格列的索引
    	 * @return
    	 */
    	public String getCellByCaseName(String caseName,int currentColumn,int targetColumn){
    		String operateSteps="";
    			//获取行数
    			int rows = sheet.getPhysicalNumberOfRows();
    			for(int i=0;i<rows;i++){
    				XSSFRow row = sheet.getRow(i);
    				String cell = row.getCell(currentColumn).toString();
    				if(cell.equals(caseName)){
    					operateSteps = row.getCell(targetColumn).toString();
    					break;
    				}
    			}
    		return operateSteps;
    	}
    
    	//打印excel数据
    	public void readExcelData(){
    		//获取行数
    		int rows = sheet.getPhysicalNumberOfRows();
    		for(int i=0;i<rows;i++){
    			//获取列数
    			XSSFRow row = sheet.getRow(i);
    			int columns = row.getPhysicalNumberOfCells();
    			for(int j=0;j<columns;j++){
    				String cell = row.getCell(j).toString();
    				System.out.println(cell);
    			}
    		}
    	}
    
    	//测试方法
    	public static void main(String[] args){
    		ExcelData sheet1 = new ExcelData("resource/FirstTests.xlsx", "username");
    		//获取第二行第4列
    		String cell2 = sheet1.getExcelDateByIndex(1, 3);
    		//根据第3列值为“customer23”的这一行，来获取该行第2列的值
    		String cell3 = sheet1.getCellByCaseName("customer23", 2,1);
    		System.out.println(cell2);
    		System.out.println(cell3);
    	}
    }

1.  输出结果如下图：  
    ![在这里插入图片描述][20181205175514767.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2dhbGVuMjAxNg_size_16_color_FFFFFF_t_70]: /images/20220411/c9baf1c88a48445db45f10809bda4f8a.png
[20181205175514767.png]: /images/20220411/9c8e39903c124db48ed83b89a30d8957.png