POI学习笔记（一）之——Excel文件读取、创建、设置日期格式

桃扇骨 2022-05-16 10:57 88阅读 0赞

准备POIjar包：[https://pan.baidu.com/s/1szYH3162hJ5f6ZW4Z5h3Ew][https_pan.baidu.com_s_1szYH3162hJ5f6ZW4Z5h3Ew]

--------------------

**创建Excel对象**

给单元格存如值：

package com.java1234.poi;
    
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    
    public class Demo3 {	
    	public static void main(String[] args) throws Exception {
    		
    		Workbook wb = new HSSFWorkbook();//创建工作簿
    		Sheet sheet = wb.createSheet("创建第一个Sheet页");
    		Row row = sheet.createRow(0);//创建一个行
    		Cell cell = row.createCell(0);//创建一个单元格  第一列
    		cell.setCellValue(1);//给单元格设值
    		
    		row.createCell(1).setCellValue(1.2);//创建一个单元格第二列值是1.2
    		row.createCell(2).setCellValue("第一行第三列");//创建一个单元格第三列为字符串
    		
    		sheet.createRow(2).createCell(4).setCellValue("第三行第五列");
    		
    		FileOutputStream fileOut = new FileOutputStream("D:\\用POI高出来的单元格.xls");
    		wb.write(fileOut);
    		fileOut.close();
    		System.out.println("创建单元格成功");
    	}
    }

设置日期格式：

package com.java1234.poi;
    
    import java.io.FileOutputStream;
    import java.util.Date;
    
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.CreationHelper;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    
    public class Demo5 {
    	
    	public static void main(String[] args) throws Exception {
    		
    		Workbook wb = new HSSFWorkbook();//定义一个新工作簿
    		Sheet sheet = wb.createSheet("Sheet第一页");//工作簿创建一个sheet页
    		Row row = sheet.createRow(0);//创建第一行
    		Cell cell = row.createCell(0);
    		cell.setCellValue(new Date());
    		
    		CreationHelper creationHelper = wb.getCreationHelper();
    		CellStyle cellStyle = wb.createCellStyle();//新建单元格样式
    		cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));//单元格样式模板
    		cell.setCellStyle(cellStyle);//设置单元格样式
    		
    		FileOutputStream fileOut = new FileOutputStream("D:\\测试工作表.xls");
    		wb.write(fileOut);
    		fileOut.close();
    	}
    	
    }

--------------------

**读取Excel工作表：**

遍历工作簿的行和列，并获取单元格内容

package com.java1234.poi;
    
    import java.io.FileInputStream;
    import java.io.InputStream;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    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.poifs.filesystem.POIFSFileSystem;
    
    public class Demo6 {
    	
    	public static void main(String[] args) throws Exception {
    		InputStream is = new FileInputStream("D:\\测试工作表.xls");//创建输入流对象
    		POIFSFileSystem fs = new POIFSFileSystem(is);
    		HSSFWorkbook wb = new HSSFWorkbook(fs);//创建一个工作簿对象
    		HSSFSheet hssfSheet = wb.getSheetAt(0);//获取第一个Sheet页
    		if(hssfSheet==null) {
    			return;
    		}
    		//遍历Row
    		for(int rowNum=0;rowNum<=hssfSheet.getLastRowNum();rowNum++) {
    			HSSFRow hssfRow = hssfSheet.getRow(rowNum);  //获取行
    			if(hssfRow == null) {
    				continue;
    			}
    			//遍历列Cell
    			for(int cellNum=0;cellNum<=hssfRow.getLastCellNum();cellNum++) {
    				HSSFCell hssfCell = hssfRow.getCell(cellNum);//获取列
    				if(hssfCell == null) {
    					continue;
    				}
    				System.out.print(" "+getValue(hssfCell));
    			}
    			System.out.println();
    			
    		}
    		
    		is.close();
    		
    	}
    	
    	private static String getValue(HSSFCell hssfCell) {
    		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());
    		}
    	}
    	
    }

文本提取：

package com.java1234.poi;
    
    import java.io.FileInputStream;
    import java.io.InputStream;
    
    import org.apache.poi.hssf.extractor.ExcelExtractor;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    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.poifs.filesystem.POIFSFileSystem;
    
    public class Demo7 {
    	
    	public static void main(String[] args) throws Exception {
    		InputStream is = new FileInputStream("D:\\测试工作表.xls");//创建输入流对象
    		POIFSFileSystem fs = new POIFSFileSystem(is);
    		HSSFWorkbook wb = new HSSFWorkbook(fs);
    		
    		ExcelExtractor excetExtractor = new ExcelExtractor(wb);
    		excetExtractor.setIncludeSheetNames(false);//false:不需要sheet页的名字  true：需要sheet页的内容
    		System.out.println(excetExtractor.getText());
    	}
    }

[https_pan.baidu.com_s_1szYH3162hJ5f6ZW4Z5h3Ew]: https://pan.baidu.com/s/1szYH3162hJ5f6ZW4Z5h3Ew