Maven依赖
- Excel2003或更早的版本
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>VERSION</version>
</dependency>
- Excel2007或更新的版本
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>VERSION</version>
</dependency>
Apache POI里面的基本概念
HSSF开头的API在2003或跟老的版本使用
XSSF开头的API在2007或更新的版本使用
- workbook excel文件,分为 HSSFWorkbook 和XSSFWorkbook
- sheet 页,分为HSSFSheet和XSSFSheet
- row 行,分为HSSFRow和XSSFRow
- cell 单元格,分为HSSFCell和XSSFCell
下面给出一个基础的代码
读Excel,一行一行,一格一格读取,假设你有一个这样的文件,有3列,书名,作者,价格
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
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;
/**
* A dirty simple program that reads an Excel file.
* @author www.codejava.net
*
*/
public class SimpleExcelReaderExample {
public static void main(String[] args) throws IOException {
String excelFilePath = "Books.xlsx";
FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet firstSheet = workbook.getSheetAt(0);
Iterator<Row> iterator = firstSheet.iterator();
while (iterator.hasNext()) {
Row nextRow = iterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue());
break;
}
System.out.print(" - ");
}
System.out.println();
}
workbook.close();
inputStream.close();
}
}
输出结果
Head First Java - Kathy Serria - 79.0 -
Effective Java - Joshua Bloch - 36.0 -
Clean Code - Robert Martin - 42.0 -
Thinking in Java - Bruce Eckel - 35.0 -
优化代码,使用面向对象特性
创建一个Book对象
public class Book {
private String title;
private String author;
private float price;
public Book() {
}
public String toString() {
return String.format("%s - %s - %f", title, author, price);
}
// getters and setters
}
重构出一个单独的方法获取单元格的值
private Object getCellValue(Cell cell) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue();
case Cell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue();
case Cell.CELL_TYPE_NUMERIC:
return cell.getNumericCellValue();
}
return null;
}
读取excel,返回一个List<Book>
public List<Book> readBooksFromExcelFile(String excelFilePath) throws IOException {
List<Book> listBooks = new ArrayList<>();
FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet firstSheet = workbook.getSheetAt(0);
Iterator<Row> iterator = firstSheet.iterator();
while (iterator.hasNext()) {
Row nextRow = iterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();
Book aBook = new Book();
while (cellIterator.hasNext()) {
Cell nextCell = cellIterator.next();
int columnIndex = nextCell.getColumnIndex();
switch (columnIndex) {
case 1:
aBook.setTitle((String) getCellValue(nextCell));
break;
case 2:
aBook.setAuthor((String) getCellValue(nextCell));
break;
case 3:
aBook.setPrice((double) getCellValue(nextCell));
break;
}
}
listBooks.add(aBook);
}
workbook.close();
inputStream.close();
return listBooks;
}
测试代码
public static void main(String[] args) throws IOException {
String excelFilePath = "Books.xlsx";
ExcelReaderExample2 reader = new ExcelReaderExample2();
List<Book> listBooks = reader.readBooksFromExcelFile(excelFilePath);
System.out.println(listBooks);
}
输出结果
[Head First Java - Kathy Serria - 79.000000, Effective Java - Joshua Bloch - 36.000000,
Clean Code - Robert Martin - 42.000000, Thinking in Java - Bruce Eckel - 35.000000]
其他API方法
获取一个指定的sheet
Sheet thirdSheet = workbook.getSheetAt(2);
获取sheet的名字
String sheetName = sheet.getSheetName();
获取sheet的个数
int numberOfSheets = workbook.getNumberOfSheets();
网友评论