摘要:在这篇文章中,我们将看到如何使用Apache POI示例在java中读取excel。Apache POI项目的任务是创建和维护Java api,用于根据Office Open XML标准(OOXML)和微软的OLE 2复合文档格式(OLE2)来操作各种文件格式。简而言之,您可以使用Java读写MS Excel文件。
关于Apache POI的一些基本知识:
在java中读/写excel时,会遇到两个前缀
HSSF: 用于处理excel 2003或更早(.xls)的文件。一些具有HSSF前缀的类是HSSFWorkbook、HSSFSheet、HSSFRow和HSSFCell。
XSSF:用于处理excel 2007或稍后(.xlsx)的文件。一些带有XSSF前缀的类是XSSFWorkbook、XSSFSheet、XSSFRow和XSSFCell。
这里有一些你需要注意的类:
Workbook :这是代表excel工作簿的高级类
Sheet :这是代表excel表格的高级类
Row : 这是代表excel行的高级类。它有与行相关的方法
Cell:这是代表单个excel单元的高级类。它有与cell相关的方法,例如:getDataType()。
项目结构:
Dependency:
如果您正在使用maven,那么您需要在pomxml中添加以下相依性。
<!-- poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
使用poi读取excel文件:
Java Program:
我们要读countries.xlsx。它的内容是:
创建ReadWriteExcelMain.java如下
package com.micai.poi;
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;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
/**
* 描述:
* <p>
*
* @author: 赵新国
* @date: 2018/6/7 14:53
*/
public class ReadWriteExcelMain {
public static void main(String [] args) throws IOException {
readFileUsingPOI();
}
public static void readFileUsingPOI() throws IOException {
ClassLoader classLoader = ReadWriteExcelMain.class.getClassLoader();
String excelFilePath = "Countries.xlsx";
FileInputStream inputStream = new FileInputStream(new File(classLoader.getResource(excelFilePath).getFile()));
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> iterator = sheet.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_NUMERIC:
System.out.print(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue());
break;
}
System.out.print(" | ");
}
System.out.println();
}
workbook.close();
inputStream.close();
}
}
当您运行在程序之上时,您将得到以下输出:
让我们通过面向对象的方式实现读取excel文件:
我们将读取每一行并创建国家对象。显然我们会跳过标题行。
创建一个名为Country的类。java在包com.micai.poi
package com.micai.poi;
/**
* 描述:
* <p>
*
* @author: 赵新国
* @date: 2018/6/7 15:17
*/
public class Country {
String name;
String capital;
double population;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getCapital() {
return capital;
}
public void setCapital(String capital) {
this.capital = capital;
}
public double getPopulation() {
return population;
}
public void setPopulation(double population) {
this.population = population;
}
@Override
public String toString() {
return "Country{" +
"name='" + name + '\'' +
", capital='" + capital + '\'' +
", population=" + population +
'}';
}
}
ReadWriteExcelWithCountryMain.java
package com.micai.poi;
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;
import org.omg.PortableInterceptor.INACTIVE;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
/**
* 描述:
* <p>
*
* @author: 赵新国
* @date: 2018/6/7 15:17
*/
public class ReadWriteExcelWithCountryMain {
public static void main(String [] args) throws IOException {
List<Country> countryList = readFileUsingPOI();
for (Country country : countryList) {
System.out.println(country.toString());
}
}
public static List<Country> readFileUsingPOI() throws IOException {
List<Country> countryList = new ArrayList<Country>();
ClassLoader classLoader = ReadWriteExcelWithCountryMain.class.getClassLoader();
String excelFilePath = "Countries.xlsx";
FileInputStream inputStream = new FileInputStream(new File(classLoader.getResource(excelFilePath).getFile()));
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> iterator = sheet.iterator();
while (iterator.hasNext()) {
Row nextRow = iterator.next();
// Not creating country object for header
if (nextRow.getRowNum() == 0) {
continue;
}
Country country = new Country();
Iterator<Cell> cellIterator = nextRow.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
int columnIndex = cell.getColumnIndex();
switch (columnIndex+1) {
case 1:
country.setName(cell.getStringCellValue());
break;
case 2:
country.setCapital(cell.getStringCellValue());
break;
case 3:
country.setPopulation(cell.getNumericCellValue());
break;
}
}
countryList.add(country);
}
workbook.close();
inputStream.close();
return countryList;
}
}
当您运行在程序之上时,您将得到以下输出: