How to import excel files in java-JavaBase-php.cn

How to import excel files in java

王林

Release： 2019-11-16 13:13:15

Original

6805 people have browsed it

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.

How to import excel files in java

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;

Copy after login

Read Excel file

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

Copy after login

Traverse Excel Sheet table

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

Copy after login

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

Copy after login

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));

Copy after login