POI的基本操作

1.创建03版的EXCEL文件
在maven项目中pom.xml文件导入jar包设置

	<!-- POI 开始 -->
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>3.16</version>
	</dependency>
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml-schemas</artifactId>
		<version>3.16</version>
	</dependency>
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-excelant</artifactId>
		<version>3.16</version>
	</dependency>

	<!-- POI 结束 -->

测试类
package com.senqi.poi;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

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;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Test1 {

public static void main(String[] args) throws Exception {
	
	// HSSFWorkbook：操作2003版
	// XSSFWorkbook: 操作2007版的excel
	Workbook wb = new XSSFWorkbook();
	
	Sheet sheet =  wb.createSheet("森淇1");
	
	// 参数： 行的索引，从0开始
	Row r = sheet.createRow(0);
	
	// 单元格
	Cell cell = r.createCell(1);
	cell.setCellValue("我是第一个单元格");
	OutputStream os = new FileOutputStream("d:\\创建aa.xlsx");
	wb.write(os);
	
	System.out.println("创建成功");				
}

}

2.读取一个03版或07版的EXCEL文件
package com.senqi.poi;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

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;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Test2 {

public static void main(String[] args) throws Exception {
	
	// 读取一个excel
	
	// 2003版
	// 2007版
	
	// WorkbookFactory: 工厂模式
	Workbook wb = WorkbookFactory.create(new File("d:\\创建222.xlsx"));
	
	Sheet sheet = wb.getSheet("森淇1");

	/*
	Row row = sheet.getRow(1);
	
	Cell cell = row.getCell(2);
	
	String str = cell.getStringCellValue();
	
	System.out.println(str);
	*/
	
	
	// 遍历读取
	// 获取有数据的所有的行
	int rows = sheet.getPhysicalNumberOfRows();
	
	//System.out.println("行数是：" + rows);
	
	for (int i = 0; i < rows; i++) {
		// 遍历每一行
		Row r = sheet.getRow(i);
		
		//System.out.println("行对象是：" + r);
		
		// 每一行单元格的数量
		// getPhysicalNumberOfCells: 获取的是非空的单元格的数量
		//int cells = r.getPhysicalNumberOfCells();
		
		int cells = 4;
		
		System.out.println("cells的个数是：" + cells);
		
		for (int j = 0; j < cells; j++) {
			
			Cell c = r.getCell(j);
			
			if(c != null) {
				System.out.println(c.getStringCellValue());
			}
			
			//System.out.println(c.getStringCellValue());
		}
	}
	
}

}

3.不管EXCEL表格是什么类型数据都可以读取的方法
package com.senqi.poi;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

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;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Test3 {

public static void main(String[] args) throws Exception {
	
	// 读取一个excel
	
	// 2003版
	// 2007版
	
	// WorkbookFactory: 工厂模式
	Workbook wb = WorkbookFactory.create(new File("d:\\aabb.xlsx"));
	
	Sheet sheet = wb.getSheet("Sheet1");
	
	// 遍历读取
	// 获取有数据的所有的行
	int rows = sheet.getPhysicalNumberOfRows();
	
	for (int i = 0; i < rows; i++) {
		// 遍历每一行
		Row r = sheet.getRow(i);
		
		int cells = r.getPhysicalNumberOfCells();
		
		System.out.println(cells);
		
		for (int j = 0; j < cells; j++) {
			Cell c = r.getCell(j);
			
			if(c.getCellTypeEnum() == CellType.NUMERIC) {
				System.out.println(c.getNumericCellValue());
			} else if(c.getCellTypeEnum() == CellType.STRING) {
				System.out.println(c.getStringCellValue());
			}
			
		}
	}
	
}

}