Table of Contents
Install with Maven
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls.free</artifactId>
<version>5.1.0</version>
</dependency>
Related Links
The ability to read data from Excel files can be useful in various industries and scenarios where structured data needs to be accessed and utilized. By reading data from Excel, you can extract information, perform calculations, and analyze the data using programming languages or specialized tools. This process enables efficient data manipulation and facilitates tasks such as generating reports, performing statistical analysis, and automating workflows.
In this article, we're going to introduce how to read Excel files in Java by using E-iceblue's Free Java Excel library. The following sub-topics are included.
- Read Value of a Cell in Java
- Read Data of a Cell Range in Java
- Read Data of an Entire Worksheet in Java
- Read Formula in a Cell in Java
- Read Formula Result in a Cell in Java
Free Java Library for Reading Excel
Free Spire.XLS for Java, provided by E-iceblue Inc., is a small but powerful Java library that empowers developers to effortlessly create, read and modify Excel XLS and XLSX files. Although it is a community edition of the commercial library Spire.XLS for Java, it offers nearly all the Excel handling capabilities, excluding file format conversion. Users can use it to easily create an Excel document from scratch, or perform operations on existing documents, such as retrieving data from a worksheet.
To install the library from the Maven repository, simply add the following configuration to your project's pom.xml file.
<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls.free</artifactId>
<version>5.1.0</version>
</dependency>
</dependencies>
Alternatively, you can download Free Spire.XLS for Java and manually import the jar file as dependency in your Java application.
Read Value of a Cell in Java
In Free Spire.XLS, the CellRange class represents a cell or a range, and the getValue() method under the class retrieves the value of a specific cell, which can be text, a number, or a formula. Follow the following steps to read the value of a cell in Java.
- Create a Workbook object.
- Load an Excel file into the Workbook object.
- Get a specific worksheet from the Workbook object.
- Get a specific cell range (in this case, cell C6) from the worksheet.
- Retrieve the value of the cell using the getValue() method.
- Java
import com.spire.xls.CellRange;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class ReadCellValue {
public static void main(String[] args) {
// Create a Workbook object
Workbook wb = new Workbook();
// Load an Excel file
wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\Score Sheet.xlsx");
//Get a specific worksheet
Worksheet sheet = wb.getWorksheets().get(0);
// Get a specific cell
CellRange certainCell = sheet.getCellRange("C6");
// Get the value of the cell
String cellValue = certainCell.getValue();
// Print out result
System.out.println("C6 has the value: " + cellValue);
}
}
Read Data of a Cell Range in Java
Now that we've learned how to get the value of a particular cell, it's easy to extract data from a cell region by looping through the cells one by one. The steps to read data of a specific cell range are as follows.
- Create a Workbook object.
- Load an Excel file into the Workbook object.
- Get a specific worksheet from the Workbook object.
- Get a cell range (in this case, A3:F6) from the worksheet.
- Iterate through the cells in the range using nested loops.
- Within each iteration, get a specific cell within the range.
- Retrieve the value of the cell using the getValue() method.
- Repeat the process for all cells in the range.
- Java
import com.spire.xls.CellRange;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class ReadCellRange {
public static void main(String[] args) {
// Create a Workbook object
Workbook wb = new Workbook();
// Load an Excel file
wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\Score Sheet.xlsx");
//Get a specific worksheet
Worksheet sheet = wb.getWorksheets().get(0);
// Get a cell range
CellRange cellRange = sheet.getCellRange("A3:F6");
// Iterate through the cells in the range
for (int i = 0; i < cellRange.getRowCount(); i++) {
for (int j = 0; j < cellRange.getColumnCount(); j++) {
// Get a specific cell
CellRange certainCell = cellRange.get(3 + i, 1 + j);
// Get the value of the cell
String cellValue = certainCell.getValue();
// Print out result
System.out.print(cellValue + " ");
}
System.out.println();
}
}
}
Read Data of an Entire Worksheet in Java
To identify the range of cells containing data, employ the Worksheet.getAllocatedRange() method, which generates a CellRange object. By iterating through the cells within the range, you can access each cell and its corresponding value. Here are the steps to read the data of an entire worksheet in Java.
- Create a Workbook object.
- Load an existing Excel file into the Workbook object.
- Get the first worksheet from the Workbook object.
- Use the getAllocatedRange() method on the worksheet to retrieve the cell range that contains data.
- Iterate through each row in the located range using a for loop.
- Within each row iteration, iterate through each column in the located range using another for loop.
- Get a specific cell within the located range using the get() method, passing the row and column indices.
- Retrieve the value of the cell using the getValue() method.
- Repeat the process for all cells in the range.
- Java
import com.spire.xls.CellRange;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class ReadWorksheet {
public static void main(String[] args) {
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\Score Sheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.getWorksheets().get(0);
//Get the cell range containing data
CellRange locatedRange = sheet.getAllocatedRange();
//Iterate through the rows
for (int i = 0; i < locatedRange.getRows().length; i++) {
//Iterate through the columns
for (int j = 0; j < locatedRange.getColumnCount(); j++) {
// Get a specific cell
CellRange certainCell = locatedRange.get(i + 1, j + 1);
// Get the value of the cell
String cellValue = certainCell.getValue();
// Print out result
System.out.print(cellValue + " ");
}
System.out.println();
}
}
}
Read Formula in a Cell in Java
If a cell contains a formula, you can get the formula using either the getValue() method or the getFormula() method. Here are the steps to read formula of a cell in Java.
- Create a Workbook object.
- Load an Excel file into the Workbook object.
- Get a specific worksheet from the Workbook object.
- Get a specific cell (D4 in this case) from the worksheet.
- Check if the cell has a formula using the hasFormula() method.
- If the cell has a formula, retrieve the formula using the getFormula() method.
- Java
import com.spire.xls.CellRange;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class ReadFormula {
public static void main(String[] args) {
// Create a Workbook object
Workbook wb = new Workbook();
// Load an Excel file
wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\Formula.xlsx");
//Get a specific worksheet
Worksheet sheet = wb.getWorksheets().get(0);
// Get a specific cell
CellRange certainCell = sheet.getCellRange("D4");
// Determine if the cell has formula or not
if (certainCell.hasFormula()){
// Get the formula of the cell
String formula = certainCell.getFormula();
// Print out result
System.out.println("D4 has a formula: " + formula);
}
}
}
Read Formula Result in a Cell in Java
In many cases, users are primarily interested in the results produced by formulas rather than the actual formulas themselves. To obtain the result of a formula, use the getFormulaValue() method. The following are the steps to read formula result in a cell in Java.
- Create a Workbook object.
- Load an Excel file into the Workbook object.
- Get a specific worksheet from the Workbook object.
- Get a specific cell (D4 in this case) from the worksheet.
- Check if the cell has a formula using the hasFormula() method.
- If the cell has a formula, retrieve the result of the formula using the getFormulaValue() method and convert the result to a string representation using the toString() method.
- Java
import com.spire.xls.CellRange;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class ReadFormulaResult {
public static void main(String[] args) {
// Create a Workbook object
Workbook wb = new Workbook();
// Load an Excel file
wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\Formula.xlsx");
//Get a specific worksheet
Worksheet sheet = wb.getWorksheets().get(0);
// Get a specific cell
CellRange certainCell = sheet.getCellRange("D4");
// Determine if the cell has formula or not
if (certainCell.hasFormula()){
// Get the result of formula
String formulaResult = certainCell.getFormulaValue().toString();
// Print out result
System.out.println("D4 has a formula and its result is: " + formulaResult);
}
}
}
Conclusion
In this blog post, we have explained the process of retrieving data (text and numbers) from a specific cell, a designated cell range, or an entire Excel worksheet using Free Spire.XLS for Java. Also, we’ve discussed how to determine if a cell contains a formula, and explored methods to extract both the formula itself and its resulting value. As a professional Excel library, Free Spire.XLS for Java supports the extraction of images and OLE objects from Excel documents as well.
For more tutorials, please check out our Excel programming guide. If you have any questions, feel free to contact us via the Spire.XLS forum.
