If you're working in Java and need to process Excel files, reading data from Excel sheets can be a common task. Whether it's for data migration, analysis, or reporting, having efficient ways to handle Excel files can save both time and effort. This blog post will explore five effective methods to read Excel values in Java, offering you a range of options suited to different needs and environments.
1. Apache POI
Apache POI is one of the most popular libraries for working with Microsoft Office documents. Here’s how you can use it to read Excel files:
- Download and Setup: Add Apache POI to your project via Maven, Gradle, or by downloading the JAR files.
- Usage:
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
public class ReadExcelApachePOI {
public static void main(String[] args) {
try {
FileInputStream file = new FileInputStream(new File("example.xlsx"));
Workbook workbook = new XSSFWorkbook(file);
Sheet sheet = workbook.getSheetAt(0);
// Iterate through each rows one by one
Iterator rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
// Now let's iterate over the columns of the current row
Iterator| cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case STRING:
System.out.print(cell.getStringCellValue() + "\t");
break;
case NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t");
break;
case BOOLEAN:
System.out.print(cell.getBooleanCellValue() + "\t");
break;
default:
System.out.print("nothing\t");
}
}
System.out.println();
}
file.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
|
💡 Note: Apache POI supports various versions of Excel files, including both the older .xls format and the newer .xlsx format. Make sure to use the correct Workbook type according to the file format.
2. JExcelAPI
JExcelAPI is another lightweight library focused specifically on Excel files. Here’s how to use it:
- Download and Setup: Similar to Apache POI, you need to download the JAR or use a build tool like Maven.
- Usage:
import jxl.*;
public class ReadExcelJExcelAPI {
public static void main(String[] args) {
try {
Workbook workbook = Workbook.getWorkbook(new File("example.xls"));
Sheet sheet = workbook.getSheet(0);
for (int row = 0; row < sheet.getRows(); row++) {
for (int col = 0; col < sheet.getColumns(); col++) {
Cell cell = sheet.getCell(col, row);
System.out.print(cell.getContents() + "\t");
}
System.out.println();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
3. Using JDBC-ODBC Bridge
This method involves setting up an ODBC data source for your Excel file and then using JDBC to connect:
- Setup ODBC Data Source: Configure an ODBC source for your Excel file in your system settings.
- Usage:
import java.sql.*;
public class ReadExcelJDBCODBC {
public static void main(String[] args) {
try {
// Load the driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Establish the connection
Connection conn = DriverManager.getConnection("jdbc:odbc:ExcelDSN");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM [Sheet1$]");
while (rs.next()) {
// Print out the values for each column
System.out.print(rs.getString("Column1") + "\t");
System.out.print(rs.getString("Column2") + "\t");
System.out.println();
}
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
4. Java XLSXAccess
Java XLSXAccess is a library specifically for handling .xlsx files:
- Download and Setup: Add the library to your project’s dependencies.
- Usage:
import net.xlsxaccess.*;
public class ReadExcelXLSXAccess {
public static void main(String[] args) {
try {
XlsxAccess xlsxAccess = new XlsxAccess("example.xlsx");
Row row;
while ((row = xlsxAccess.readNextRow()) != null) {
for (int i = 0; i < row.getCellCount(); i++) {
System.out.print(row.getCell(i) + "\t");
}
System.out.println();
}
xlsxAccess.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
5. Reading Excel Using XML Streaming
If your files are very large, you might consider using XML streaming to parse .xlsx files without loading the entire file into memory:
- Approach: Treat .xlsx files as a set of XML files and use an XML parser for streaming.
- Usage:
import javax.xml.stream.XMLInputFactory;
import javax.xml.stream.XMLStreamConstants;
import javax.xml.stream.XMLStreamReader;
import java.io.FileInputStream;
public class ReadExcelXMLStreaming {
public static void main(String[] args) {
try {
XMLInputFactory inputFactory = XMLInputFactory.newInstance();
FileInputStream file = new FileInputStream("example.xlsx");
Unzipper.unzip(file, "xl", "sharedStrings.xml");
XMLStreamReader reader = inputFactory.createXMLStreamReader(new FileInputStream("xl/sharedStrings.xml"));
boolean readValue = false;
StringBuilder currentValue = new StringBuilder();
while (reader.hasNext()) {
int event = reader.next();
if (event == XMLStreamConstants.START_ELEMENT) {
if (reader.getLocalName().equals("t")) {
readValue = true;
}
} else if (event == XMLStreamConstants.CHARACTERS) {
if (readValue) {
currentValue.append(reader.getText());
}
} else if (event == XMLStreamConstants.END_ELEMENT) {
if (reader.getLocalName().equals("t")) {
System.out.println(currentValue.toString());
currentValue = new StringBuilder();
readValue = false;
}
}
}
reader.close();
file.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
Each method has its own advantages:
- Apache POI: Comprehensive with good community support; perfect for projects needing extensive Excel manipulation.
- JExcelAPI: Lightweight and fast for basic operations on .xls files.
- JDBC-ODBC: Useful when integrating with databases or when you need to query Excel data.
- XLSXAccess: Efficient for handling large .xlsx files.
- XML Streaming: Best for memory efficiency when working with very large Excel files.
Choosing the right method depends on your specific requirements, such as file size, the type of data you're working with, and the complexity of operations you need to perform. Remember to consider performance, ease of integration, and the level of control you need over the reading process. In summary, these five methods provide flexible options for reading Excel data in Java, ensuring that regardless of your project's scope or constraints, there's an approach that fits your needs.
Which method is best for reading large Excel files?
+
For very large Excel files, XML Streaming or Apache POI’s streaming features are the most memory-efficient since they read the file row by row, instead of loading everything into memory.
Can I use these libraries to write Excel files too?
+
Yes, Apache POI, JExcelAPI, and XLSXAccess can also be used to write data into Excel files. Each library provides methods for creating new files or modifying existing ones.
Do these libraries support Excel formula calculations?
+
Apache POI supports reading and evaluating Excel formulas. JExcelAPI can read formulas but might not evaluate them. XLSXAccess primarily focuses on reading data, not formula manipulation.
