How to read excel files in java using Apache POI

摘要：在这篇文章中，我们将看到如何使用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;
    }

}

当您运行在程序之上时，您将得到以下输出：