摘要:在这篇文章中,我们将看到如何使用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:
<!-- 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; } }当您运行在程序之上时,您将得到以下输出: