How to import excel files in java

Java uses POI to import Excel files, which is relatively simple to operate and supports xlsx format.

Download POI resource package

Download POI from the official website. The version selected in this article is 3.17. After downloading, the file name is poi-bin-3.17.zip. At first I was worried that the version was too new and would be incompatible with existing projects, but later the program ran and no problems were found.

Copy the jar package under poi-3.17 and poi-3.17\lib\*.jar package to WEB-INF\lib of the project, and configure the compilation path in the project.

Add the following references in the Java file, where hssf is for xls format and xssf is for xlsx format

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

Read Excel file

HSSFWorkbook workbook = null;
try {
    // 读取Excel文件
    InputStream inputStream = new FileInputStream('c:\test.xlsx');
    workbook = new HSSFWorkbook(inputStream);
    inputStream.close();
} catch (Exception e) {
    e.printStackTrace();
}

Traverse Excel Sheet table

// 遍历Excel Sheetfor (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
    workbook.getSheetAt(numSheet);
}

Traverse rows

HSSFSheet sheet = workbook.getSheetAt(0);
if (sheet != null) {
    // 循环行
    for     (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
        HSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            continue;// 忽略并继续读取
        }
        HSSFCell cell = row.getCell(0);
      if (cell == null) {
            continue;
        }
      //System.out.println(&#39;cell.getStringCellValue0():&#39; + cell.getStringCellValue());
        cell = row.getCell(1);
       if (cell == null) {
            continue;
       }
        //System.out.println(&#39;cell.getStringCellValue1():&#39; + cell.getStringCellValue());
   }
}

POI encountered a problemCannot get a text value from a numeric cell “Poi”

When importing an Excel file, if a column is of numeric type, directly Through cell.getStringCellValue(), a Cannot get a text value from a numeric cell “Poi” error will be reported.

Solution:

DataFormatter formatter = new DataFormatter();
String val = formatter.formatCellValue(sheet.getRow(col).getCell(row));

Recommended tutorial: Java tutorial

The above is the detailed content of How to import excel files in java. For more information, please follow other related articles on the PHP Chinese website!