工具类---poi:操作Excel文件(生成与读取)

红太狼 2023-07-13 13:43 11阅读 0赞

**pom.xml加入poi坐标**

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

**生成Excel文件到本地**

public static void getHSSFWorkbook() throws Exception {
    		// 一个HSSFWorkbook代表一个Excel文件
    		HSSFWorkbook workbook = new HSSFWorkbook(); 			
    
    		// 一个HSSFSheet代表一个sheet
    		HSSFSheet sheet = workbook.createSheet(); 				
    		
    		// 第一行(从0开始) 表头
    		HSSFRow row0 = sheet.createRow(0); 						
    		for(int i=0;i<5;i++) {
    			row0.createCell(i).setCellValue("Title" + i);
    		}
    		
    		// 创建5行(从1开始) 表内容
    		for(int i=1;i<=5;i++) {
    			HSSFRow row = sheet.createRow(i); 					
    			for(int k=0;k<5;k++) {								//每行5列
    				row.createCell(k).setCellValue("content" + i + "-" + k);
    			}
    		}
    		
    		// 命名Excel文件 并保存到到指定文件夹
    		FileOutputStream out = new FileOutputStream("D:/G(other)/temp/Excel测试.xls");
    		workbook.write(out);
    		out.close();
    	}

**读取Excel文件**

import java.io.File;
    import java.io.FileInputStream;
    
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellType;
    import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    public class ExcelUtil {
    	
    	
    	public static void readExcel(String filePath) throws Exception {
    		String fileType = filePath.substring(filePath.lastIndexOf('.') + 1); 	//文件后缀
    		if(fileType.equalsIgnoreCase("xls")){
    			File file = new File(filePath); 
    			HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new FileInputStream(file));	// 1.Excel文档对象
    			HSSFSheet sheet0 = hssfWorkbook.getSheetAt(0);								// 2.sheet0
    			int lastRowNum = sheet0.getLastRowNum();									// 3.获取sheet0最后一行的行号
    			for (int i = 0; i <= lastRowNum; i++) {										// 4.从第0行开始向下遍历
    				HSSFRow row = sheet0.getRow(i);											//获取行
    				int n=5;																//定义每行单元格的个数 
    				for(int k=0;k<n;k++) {													// 遍历每行的单元格
    					//防止单元格里面为空，报空指针异常
    					Cell cell =  row.getCell(k,MissingCellPolicy.CREATE_NULL_AS_BLANK);
    					//单元格数据格式统一设置成字符串
    					cell.setCellType(CellType.STRING);
    					String cellValue = cell.getStringCellValue();				
    					System.out.println(cellValue);
    				}
    			}
    		}else if(fileType.equalsIgnoreCase("xlsx")){
    			File file = new File(filePath); 
    			XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));	// 1.Excel文档对象
    			XSSFSheet sheet0 = workbook.getSheetAt(0);								// 2.sheet0
    			int lastRowNum = sheet0.getLastRowNum();								// 3.获取sheet0最后一行的行号
    			for (int i = 0; i <= lastRowNum; i++) {									// 4.从第0行开始向下遍历
    				XSSFRow row = sheet0.getRow(i);										// 获取行
    				int n=3;															// 定义每行单元格的个数  
    				for(int k=0;k<n;k++) {												// 遍历每行的单元格
    					//防止单元格里面为空，报空指针异常
    					Cell cell =  row.getCell(k,MissingCellPolicy.CREATE_NULL_AS_BLANK);		
    					//单元格数据格式统一设置成字符串
    					cell.setCellType(CellType.STRING);		
    					String cellValue = cell.getStringCellValue();					
    					System.out.println(cellValue);
    				}
    			}
    		}else{
    			System.out.println("读取excel文件失败！");
    		}
    
    	}
    
    	
    	public static void main(String[] args) throws Exception {
    		readExcel("C:/Users/lkzhang/Desktop/xxxx.xlsx");
    	}
    	
    }