POI 入门-----读取 Excel 07以上版本

1.  用for循环读取Excel

package com.csii.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;

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

public class TestReadExcel {
	public static void main(String[] args) throws IOException {
		String filePath = "C:\\Users\\ljj\\Desktop\\workDay\\我的事件流\\公共交易试.xlsx";
		File file = new File(filePath);
		FileInputStream fileInputStream = null;
		try {
			fileInputStream = new FileInputStream(file);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}

		// 07版本以后的excel 对象
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileInputStream);
		// 获取第一个工作表sheet
		XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
		// 获取非空行
		int rowNum = xssfSheet.getLastRowNum();
		// 用于计数
		int i = 1;
		for (int indexRow = 0; indexRow < rowNum; indexRow++) {// 循环每一行
			XSSFRow xssfRow = xssfSheet.getRow(indexRow);
			int colNum = xssfRow.getLastCellNum();

			// 循环每一列
			for (int colIndex = 0; colIndex < colNum; colIndex++) {// 循环每一列
				// 创建一个 单元格（Cell） 对象
				Cell cell = xssfRow.getCell(colIndex);

				if (cell != null) {// 单元格非 null，才输出
					String value = "";
					switch (cell.getCellType()) { // 单元格的三种类型：字符(STRING)、数值                    
                                                      （NUMERIC）、空白（BLANK）
					case STRING:
						value = cell.getStringCellValue();
						break;
					case NUMERIC:
						// 转换成string类型
						value = String.valueOf(cell.getNumericCellValue());
						break;
					case BLANK:
						break;
					default:
						break;
					}
					System.out.println(i++ + ":" + value);

				} else {// 单元格为null,继续寻找下一列的单元格
					continue;
				}
				
			}
		}

	}

}

测试数据如下： 

 

---

2. 用 Iterater 来循环，此方法比for循环更方便

public class ExcelRead{
	private static XSSFRow row;

	@SuppressWarnings("unused")
	public static void main(String[] args) throws IOException {
		String fileName = "C:\\Users\\ljj\\Desktop\\testExcel\\wc.xlsx";
		String[] arrExcel = readExcel(fileName);
		
		int i = 0;
		for (String string : arrExcel) {
			System.out.println(i + ":" + string);
		}

	}


	/**
	 * 返回一个字段数组
	 * @param fileName
	 * @return String[]
	 * @throws IOException
	 */
	@SuppressWarnings("unused")
	public static String[] readExcel(String fileName) throws IOException {

		File file = new File(fileName);
		if (file == null) {
			System.out.println("读取文件失败！");
		}

		FileInputStream fileInputStream = new FileInputStream(file);
		// 创建excel对象
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileInputStream);
		if (file.exists() && file.isFile()) {
			System.out.println("open file succeed!");
		} else {
			System.out.println("Error to open openworkbook.xlsx file!");
		}
		// 获取excel表对象
		XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
        // 创建行的迭代器对象
		Iterator<Row> rowIterator = xssfSheet.iterator();
		// 字段名
		String strName = "";
		// 级字段数
		int i = 0;
		// 存字段数组
		String[] arr = new String[24];
		while (rowIterator.hasNext()) {
			row = (XSSFRow) rowIterator.next();
			Iterator<Cell> cellIterator = row.cellIterator();
			while (cellIterator.hasNext()) {
                // 创建单元格对象
				Cell cell = cellIterator.next();
				switch (cell.getCellType()) {// 根据单元格类型分别获取获取值
				// string 类型
				case STRING:
                    // 获取单元格值
					strName = cell.getStringCellValue();
					strName = strName.trim();
                    // 存入数组中输出
					arr[i++] = strName;
					break;
				// number类型
				case NUMERIC:
                    // 获取单元格值
					strName = String.valueOf(cell.getNumericCellValue());
					strName = strName.trim();
                    // 存入数组中输出
					arr[i++] = strName;
					break;
				default:
					break;
				}
			}
		}
		return arr;
	}
}

测试的表格：