Discover in our latest blog post how to effectively process Excel files in Java using Apache POI. From simple data extraction to writing complex formulas – we guide you through all the steps. Learn why Apache POI is the preferred choice for integrating Excel into your Java applications.
Why Apache POI?
Apache POI is a powerful library designed specifically for working with Microsoft Excel documents. Here are some reasons why Apache POI is an excellent choice:
- Support for both formats: Apache POI can handle both older XLS and modern XLSX file formats.
- Apache support: As part of the Apache Foundation, POI offers reliable and active support.
- Stream-based processing: This allows efficient processing of large files with less memory usage.
Matching infrastructure at centron
Node applications need somewhere to run: ccloud³ VMs with full root access from €3.12 per month – or as a managed server if you would rather not run it yourself. Rent a cloud server →
Maven Dependencies
If you use Maven, you can easily integrate Apache POI into your project:
org.apache.poi
poi
3.10-FINAL
org.apache.poi
poi-ooxml
3.10-FINALReading Excel Files
To read data from an Excel file, we can use the `Workbook` instance and extract the desired data from the sheets. Here’s a simple example:
public class ReadExcelFileToList {
public static List readExcelData(String fileName) {
List countriesList = new ArrayList<>();
try {
FileInputStream fis = new FileInputStream(fileName);
Workbook workbook = fileName.toLowerCase().endsWith("xlsx") ? new XSSFWorkbook(fis) : new HSSFWorkbook(fis);
int numberOfSheets = workbook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
Sheet sheet = workbook.getSheetAt(i);
Iterator rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
// Reading the cells and extracting the data
}
}
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
return countriesList;
}
}Writing Excel Files
Writing data to Excel using Apache POI is similarly simple. Here’s an example that writes a list of countries to an Excel file:
public class WriteListToExcelFile {
public static void writeCountryListToFile(String fileName, List countryList) throws Exception {
Workbook workbook = fileName.endsWith("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook();
Sheet sheet = workbook.createSheet("Countries");
Iterator iterator = countryList.iterator();
int rowIndex = 0;
while (iterator.hasNext()) {
// Writing the data into the cells
}
FileOutputStream fos = new FileOutputStream(fileName);
workbook.write(fos);
fos.close();
System.out.println(fileName + " successfully written");
}
}Conclusion
Apache POI is a powerful library for working with Excel files in Java. With support for both formats, easy integration with Maven, and extensive functionality for editing Excel documents, it is the preferred choice for many developers. Try it out and see for yourself how easy it is to work with Excel files in Java!
You might also be interested in:
Apache Spark in Java : A Simple Word Counter Program
Apache Server Name Error: A Guide to Troubleshooting
The Art of Decoupling: The Bridge Design Pattern in Java
Testen Sie Ihr Setup auf ccloud³
Registrieren Sie sich in der ccloud³ und erhalten Sie 200 € Startguthaben für Ihr Projekt – z. B. für eine PostgreSQL-VM mit automatischen Backups.