一、前言

Apache POI 是用Java編寫的免費開源的跨平台的 Java API，Apache POI提供API給Java程式對Microsoft Office（Excel、WORD、PowerPoint、Visio等）格式檔案讀和寫的功能。本次文章只針對Excel讀取時做討論。

二、實作步驟

1.首先在pom.xml導入POI library。

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId> poi </artifactId>
  <version>4.1.1</version>
</dependency>

2.測試檔案test.xlsx

表一(男)

表二(女)

2.建立讀取Excel的Bean

public class ExcelDataBean {

	public ExcelDataBean() {

	}

	/** 姓名 */
	private String name;
	/** 年齡 */
	private Integer age;
	/** 居住地 */
	private String location;
	/** 職業 */
	private String job;
	/** 性別 */
	private String gender;

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}

	public String getLocation() {
		return location;
	}

	public void setLocation(String location) {
		this.location = location;
	}

	public String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public String getGender() {
		return gender;
	}

	public void setGender(String gender) {
		this.gender = gender;
	}
}

3.讀取Excel檔案

public static Workbook getWorkbook(String path) {
		Workbook wb = null;
		if (path == null)
			return null;
		String extString = path.substring(path.lastIndexOf("."));
		InputStream is;
		try {
			is = new FileInputStream(path);
			if (XLS.equals(extString)) {
				wb = new HSSFWorkbook(is);
			} else if (XLSX.equals(extString)) {
				wb = new XSSFWorkbook(is);
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return wb;
	}

4.解析Sheet

	public static List<ExcelDataBean> parseExcel(Workbook workbook) {
		List<ExcelDataBean> excelDataList = new ArrayList<>();
		//遍歷每一個sheet
		for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
			Sheet sheet = workbook.getSheetAt(sheetNum);

			if (sheet == null) {
				continue;
			}

			int firstRowNum = sheet.getFirstRowNum();
			Row firstRow = sheet.getRow(firstRowNum);
			if (null == firstRow) {
				System.out.println("解析Excel失敗");
			}

			int rowStart = firstRowNum + 1;
			int rowEnd = sheet.getPhysicalNumberOfRows();
			for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
				Row row = sheet.getRow(rowNum);
				if (null == row) {
					continue;
				}
				ExcelDataBean excelData = convertRowToData(row);
				if (null == excelData) {
					continue;
				}
				excelDataList.add(excelData);
			}
		}
		return excelDataList;
	}

5.解析Row

private static ExcelDataBean convertRowToData(Row row) {
		ExcelDataBean excelData = new ExcelDataBean();
		Cell cell;
		int cellNum = 0;
		// 讀取姓名
		cell = row.getCell(cellNum++);
		String name = convertCellValueToString(cell);
		excelData.setName(name);
		cell = row.getCell(cellNum++);
		// 讀取性別
		String gender = convertCellValueToString(cell);
		excelData.setGender(gender);
		cell = row.getCell(cellNum++);
		// 讀取年齡
		String ageStr = convertCellValueToString(cell);
		if (null == ageStr || "".equals(ageStr)) {
			excelData.setAge(null);
		} else {
			excelData.setAge(Integer.parseInt(ageStr));
		}
		// 獲取居住地
		cell = row.getCell(cellNum++);
		String location = convertCellValueToString(cell);
		excelData.setLocation(location);
		// 獲取職業
		cell = row.getCell(cellNum++);
		String job = convertCellValueToString(cell);
		excelData.setJob(job);
		return excelData;
	}

6.解析Cell

public static String convertCellValueToString(Cell cell) {
		if (cell == null) {
			return null;
		}
		String returnValue = null;
		switch (cell.getCellType()) {
		case NUMERIC:
			Double doubleValue = cell.getNumericCellValue();
			DecimalFormat df = new DecimalFormat("0");
			returnValue = df.format(doubleValue);
			break;
		case STRING:
			returnValue = cell.getStringCellValue();
			break;
		case BOOLEAN:
			Boolean booleanValue = cell.getBooleanCellValue();
			returnValue = booleanValue.toString();
			break;
		case BLANK:
			break;
		case FORMULA:
			returnValue = cell.getCellFormula();
			break;
		case ERROR:
			break;
		default:
			break;
		}
		return returnValue;
	}

7.讀取結果

8.結論

統整以上步驟大概就是

 1.先讀取Excel檔案

 2.遍歷每一個sheet

 3.從sheet取出每一個Row,並遍歷Row

 4.從每一個Row在取出Cell

希望透過本文章能夠讓大家更了解如何使用POI去讀取EXCEL

參考網站:https://poi.apache.org/