Use Java to read values from Excel
**1 Overview
For Microsoft Excel files, reading values from different cells can be a bit tricky. Excel
A file is a spreadsheet organized by rows and cells, which can contain String, Numeric, Date, Boolean, and even Formula
values 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:
XSSFWorkbook
And theHSSFWorkbook
class represents the Excel workbookSheet
The interface represents an Excel worksheetRow
Interface representative lineCell
Interface representative cell
3.1. Processing Excel files
First, we open the file to be read and convert it FileInputStream
for further processing. FileInputStream
The constructor throws one java.io.FileNotFoundException
so 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 XSSFWorkbook
and 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. MissingCellPolicy
The 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 Formula
last 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 Date
value is stored as a Numeric
value, and if the value type of the cell is that FORMULA
we 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 printCellValue
method, 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.
buy generic lipitor & lt;a href="https://lipiws.top/"& gt;buy lipitor 10mg sale& lt;/a& gt; order lipitor generic
Hltrhl
2024-03-07