How to read excel files in java using Apache POI

绝地灬酷狼 2024-02-18 20:59 168阅读 0赞

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

**项目结构：**

**![20180607153800189][]**

##### Dependency: #####

如果您正在使用maven，那么您需要在pomxml中添加以下相依性。

<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。它的内容是:

![20180607153515160][]

创建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();
        }
    }

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

![20180607153712572][]

**让我们通过面向对象的方式实现读取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;
        }
    
    }

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

**![20180607154210172][]**

[20180607153800189]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/b5c2eaa5a6db448bbd05f444f74b4c55.png
[20180607153515160]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/ce0a5cf4f626408b82f3ff145fa96f59.png
[20180607153712572]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/27a8c1b1518741e79b8bc95ee4c19f73.png
[20180607154210172]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/840d9b5166f54a05a828fa20eb2d91ad.png