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

准备POIjar包：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());
	}
}