• notice
  • Congratulations on the launch of the Sought Tech site

Use Java to read values ​​from Excel

**1 Overview

For Microsoft Excel files, reading values from different cells can be a bit tricky. ExcelA file is a spreadsheet organized by rows and cells, which can contain String, Numeric, Date, Boolean, and even Formulavalues or even formula values. Apache POI is a library that provides a complete set of tools to handle different excel files and value types .

In this tutorial, we will focus on learning how to handle excel files, traverse rows and cells, and use the correct method to read each cell value type.

2. Maven dependency

Let's first add the Apache POI dependency to pom.xml:

<dependency>
 <groupId>org.apache.poi</groupId>
 <artifactId>poi-ooxml</artifactId>
 <version>5.0.0</version>
 </dependency>

You can find the latest version in Maven Central[poi-ooxml](https://search.maven.org/classic/#search%7Cga%7C1%7Cg%3A%22org.apache.poi%22%20AND%20a%3A%22poi-ooxml%22)

3. Overview of Apache POI

The hierarchy starts with a workbook that represents the entire Excel file. Each file can contain one or more worksheets, which are collections of rows and cells. According to the version of the Excel file, HSSF is the prefix ( .xls) that represents the class of the old Excel file , and XSSF is used for the latest version ( .xlsx). So we have:

  • XSSFWorkbookAnd the HSSFWorkbookclass represents the Excel workbook

  • SheetThe interface represents an Excel worksheet

  • RowInterface representative line

  • CellInterface representative cell

3.1. Processing Excel files

First, we open the file to be read and convert it FileInputStreamfor further processing. FileInputStreamThe constructor throws one java.io.FileNotFoundExceptionso we need to wrap it in a try-catch block and close the stream at the end:

public static void readExcel(String filePath) {
 File file = new File(filePath); try {
 FileInputStream inputStream = new FileInputStream(file);
 ...
 inputStream.close();
 } catch (IOException e) {
 e.printStackTrace();
 }
 }

3.2. Traverse the Excel file

After we successfully opened InputStream, it's time to create XSSFWorkbookand traverse the rows and cells of each worksheet. In cases where we know the exact number of sheets or the name of a specific sheet, we can use the getSheetAt(int index)and getSheet(String sheetName)methods XSSFWorkbook,respectively.

Since we want to read any type of Excel file, we will use three nested for loops to traverse all worksheets, one for the worksheet, one for the rows of each worksheet, and the last one for each work Table cell .

In this tutorial, we will only print the data to the console:

FileInputStream inputStream = new FileInputStream(file);
 Workbook baeuldungWorkBook = new XSSFWorkbook(inputStream); for (Sheet sheet : baeuldungWorkBook) {
 ...
 }

Then, in order to traverse the rows of the worksheet, we need to find the index of the first and last row obtained from the worksheet object:

int firstRow = sheet.getFirstRowNum(); int lastRow = sheet.getLastRowNum(); for (int index = firstRow + 1; index <= lastRow; index++) {
 Row row = sheet.getRow(index);
 }

Finally, we do the same thing with the cell. In addition, when accessing each cell, we can choose to pass one MissingCellPolicy, which basically tells the POI what to return when the cell value is empty or null. MissingCellPolicyThe enumeration contains three enumeration values:

  • RETURN_NULL_AND_BLANK

  • RETURN_BLANK_AS_NULL

  • CREATE_NULL_AS_BLANK ;

The code for cell iteration is as follows:

for (int cellIndex = row.getFirstCellNum(); cellIndex < row.getLastCellNum(); cellIndex++) {
 Cell cell = row.getCell(cellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
 ...
 }

3.3. Read cell value in Excel

As we mentioned before, cells in Microsoft Excel can contain different value types, so it is very important to be able to distinguish one cell value type from another and use appropriate methods to extract the value. The following is a list of all value types:

  • NONE

  • NUMERIC

  • STRING

  • FORMULA

  • BLANK

  • BOOLEAN

  • ERROR

We will focus on four main cell value types:, the Numeric, String, Boolean, and Formulalast of which contains calculated values of the first three types.

Let's create a helper method that will basically check each value type and use the appropriate method to access the value on this basis. You can also treat the cell value as a string and use the corresponding method to retrieve it.

Two important things are worth noting. First, the Datevalue is stored as a Numericvalue, and if the value type of the cell is that FORMULAwe need to use getCachedFormulaResultType()instead of a getCellType()method to check the calculation result of the formula:

public static void printCellValue(Cell cell) {
 CellType cellType = cell.getCellType().equals(CellType.FORMULA)
 ? cell.getCachedFormulaResultType() : cell.getCellType(); if (cellType.equals(CellType.STRING)) {
 System.out.print(cell.getStringCellValue() + " | ");
 } if (cellType.equals(CellType.NUMERIC)) { if (DateUtil.isCellDateFormatted(cell)) {
 System.out.print(cell.getDateCellValue() + " | ");
 } else {
 System.out.print(cell.getNumericCellValue() + " | ");
 }
 } if (cellType.equals(CellType.BOOLEAN)) {
 System.out.print(cell.getBooleanCellValue() + " | ");
 }
 }

Now, all we need to do is the printCellValuemethod, and we are done. This is a snippet of the complete code:

... for (int cellIndex = row.getFirstCellNum(); cellIndex < row.getLastCellNum(); cellIndex++) {
 Cell cell = row.getCell(cellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
 printCellValue(cell);
 }
 ...

4. in conclusion

In this article, we showed a sample project that uses Apache POI to read Excel files and access different cell values.


Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

buy generic lipitor & lt;a href="https://lipiws.top/"& gt;buy lipitor 10mg sale& lt;/a& gt; order lipitor generic

Hltrhl

2024-03-07

Leave a Reply

+