如何在 Java 中读取 .xlsx 和 .xls 文件？

How can I read .xlsx and .xls files in Java?

提问by Srinivas

hi i want to read xlsx file or xls file what ever it is. can XSSF support xls file ? or do i need to write the separate code for both kind of files ?

嗨，我想读取 xlsx 文件或 xls 文件，无论它是什么。XSSF 可以支持 xls 文件吗？还是我需要为这两种文件编写单独的代码？

回答by Murukesh

Yes, you can use Apache POI to read and write xlsx and xlsfiles.

是的，您可以使用 Apache POI 来读取和写入 xlsx 和 xls文件。

回答by Valentin Rocher

If you want your code to work for both, you'll have to use the org.apache.poi.sspackage. This package has been created to unify XSSF and HSSF.

如果您希望您的代码同时适用于两者，则必须使用该org.apache.poi.ss包。创建这个包是为了统一 XSSF 和 HSSF。

回答by jj2422

use this for xls and xlsx

将此用于 xls 和 xlsx

Workbook wb_xssf; //Declare XSSF WorkBook 
Workbook wb_hssf; //Declare HSSF WorkBook 
Sheet sheet=null; //sheet can be used as common for XSSF and HSSF WorkBook 

if(fileBean.getFileExt().equalsIgnoreCase("xls")){
    wb_hssf = new HSSFWorkbook();
    sheet = wb_hssf.getSheetAt(0);
}else if (fileBean.getFileExt().equalsIgnoreCase("xlsx")){
    wb_xssf = new XSSFWorkbook(fileBean.getFileInput());      
    sheet = wb_xssf.getSheetAt(0);                                                          
}

回答by noego

For one of my projects I have created a basic utility that uses Apache POI and OpenCSV and can read both xlsx, xls and csv files.

对于我的一个项目，我创建了一个使用 Apache POI 和 OpenCSV 的基本实用程序，并且可以读取 xlsx、xls 和 csv 文件。

Given a converter it can convert rows to objects, like this:

给定一个转换器，它可以将行转换为对象，如下所示：

RowConverter<Country> converter = (row) -> new Country(row[0], row[1]);

ExcelReader<Country> reader = ExcelReader.builder(Country.class)
     .converter(converter)
     .withHeader()
     .csvDelimiter(';')
     .sheets(1)
     .build();

List<Country> list;
list = reader.read("CountryCodes.xlsx");
list = reader.read("CountryCodes.xls");
list = reader.read("CountryCodes.csv");

You may find the project on github.

您可以在github上找到该项目。

回答by Geovani Diaz

You can try this for xlsx files:

您可以为 xlsx 文件尝试此操作：

Firstly, you need the following jar downloads:

首先，您需要以下 jar 下载：

• dom4j-2.1.0.jar
• poi-3.17.jar
• poi-ooxml-3.17.jar
• commons-collections4-4.1.jar
• xmlbeans-2.3.0.jar

• dom4j-2.1.0.jar
• poi-3.17.jar
• poi-ooxml-3.17.jar
• commons-collections4-4.1.jar
• xmlbeans-2.3.0.jar

Secondly, add the following imports in your workspace:

其次，在您的工作区中添加以下导入：

import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.IOException;
import java.io.FileInputStream;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

Thirdly, begin building your method, for example for read use this:

第三，开始构建你的方法，例如阅读使用这个：

public void ReadExcelFiles(String pathxlsx,javax.swing.JTable jtable) throws IOException{
    //String nameSheet;
    File file = new File(pathxlsx);
    FileInputStream fis = new FileInputStream(file);
    XSSFWorkbook wb = new XSSFWorkbook(fis);
    // nameSheet=wb.getSheetName(0);
    //XSSFSheet sh = wb.getSheet(nameSheet);
    XSSFSheet sh = wb.getSheetAt(0);
    System.out.println(sh.getLastRowNum());
    System.out.println("Name: "+sh.getSheetName()); 
    Row row = sh.getRow(6);

    System.out.println(row.getRowNum());
    System.out.println("columna "+row.getCell(1).getStringCellValue());
    System.out.println("columna "+row.getCell(2).getStringCellValue());
    System.out.println("columna "+row.getCell(3).getStringCellValue());
    System.out.println("columna "+row.getCell(4).getStringCellValue());

    System.out.println("Val: "+sh.getRow(4).getCell(6).getStringCellValue()); 
}

回答by Annivey

You can use the following code and change it (depends on your needs):

您可以使用以下代码并对其进行更改（取决于您的需要）：

public void parseXLSX() {

    String pathToXLSX = "file.xlsx";
    File file = new File(pathToXLSX);

    FileInputStream in = null;

    try {
        in = new FileInputStream(file);
        XSSFWorkbook workbook = new XSSFWorkbook(in);
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            XSSFSheet sheet = workbook.getSheetAt(i);
            int rowNumber = sheet.getLastRowNum() + 1;
            for (int j = 1; j < rowNumber; j++) {
                Iterator it = sheet.getRow(j).cellIterator();
                while (it.hasNext()) {
                    System.out.println(it.next().toString());
                }
            }
        }
    } catch (IOException ex) {
        ex.getMessage();
        ex.printStackTrace();
    } finally {
        if (in != null) {
            try {
                in.close();
            } catch (IOException ex) {
                ex.getMessage();
                ex.printStackTrace();
            }
        }
    }

}

}