This article introduces through examples how Java and the Apache POI library process XLSX files, helping you automate Excel-related tasks and effectively process data in Java applications.
Search on WeChat and follow “Java Learning and Research Base Camp”
1 Introduction
In today’s data-driven world, programmatic processing of Excel files is crucial. Java provides powerful libraries to easily read, write and validate XLSX files. In this article, we’ll explore this simplified approach to performing these operations using Java code examples.
2 Read XLSX files
To read data from XLSX files in Java, we can use the Apache POI library. Here’s a simplified example:
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.io.IOException;
public class XLSXReaderExample {
public static void main(String[] args) {
try {
FileInputStream file = new FileInputStream("path/to/your/sample.xlsx");
Workbook workbook = WorkbookFactory.create(file);
Sheet sheet = workbook.getSheetAt(0);
for (Row row : sheet) {
for (Cell cell : row) {
String value = cell.toString();
System.out.print(value + "\t");
}
System.out.println();
}
workbook.close();
file.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
This example uses Apache POI to read data from an XLSX file. We open the file, create a Workbook object, and access the first worksheet. Next, we iterate through each row and each cell, get the value of the cell and print it out.
3 Write XLSX file
To write data to an XLSX file, we can continue to use the Apache POI library. Here’s a simplified example:
import org.apache.poi.ss.usermodel.*;
import java.io.FileOutputStream;
import java.io.IOException;
public class XLSXWriterExample {
public static void main(String[] args) {
try {
Workbook workbook = WorkbookFactory.create(true);
Sheet sheet = workbook.createSheet("Sheet1");
Row headerRow = sheet.createRow(0);
Cell headerCell = headerRow.createCell(0);
headerCell.setCellValue("Name");
Row dataRow = sheet.createRow(1);
Cell dataCell = dataRow.createCell(0);
dataCell.setCellValue("John Doe");
FileOutputStream file = new FileOutputStream("path/to/your/output.xlsx");
workbook.write(file);
workbook.close();
file.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
In this simplified example, we create a new Workbook, add a worksheet, and create a header row and data rows. We set the value of the cell for each row and use a FileOutputStream to save the workbook to a file.
4 Verify XLSX files
Validating XLSX files ensures that the data meets specific standards. Here is a simplified example of performing validation using Apache POI:
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
public class XLSXValidationExample {
public static void main(String[] args) {
try {
FileInputStream file = new FileInputStream("path/to/your/sample");
Workbook workbook = WorkbookFactory.create(file);
Sheet sheet = workbook.getSheetAt(0);
DataValidationHelper validationHelper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = validationHelper.createNumericConstraint(
OperatorType.BETWEEN, "1", "100");
CellRangeAddressList addressList = new CellRangeAddressList(1, sheet.getLastRowNum(), 0, 0);
DataValidation validation = validationHelper.createValidation(constraint, addressList);
validation.setShowErrorBox(true);
sheet.addValidationData(validation);
FileOutputStream output = new FileOutputStream("path/to/your/validated_data.xlsx");
workbook.write(output);
workbook.close();
file.close();
output.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
In this simplified example, we read an XLSX file, create a numeric constraint to limit values between 1 and 100, and apply it to a set of cells. We then save the validated data to a new XLSX file.
5 Summary
XLSX files in Java can be easily processed using libraries like Apache POI. In this article, we explore a simplified approach to reading, writing, and validating XLSX files using Java code examples. By leveraging these features, you can automate Excel-related tasks and efficiently process data in Java applications.
Recommended book list
[Autumn Reading Plan] Click the link below to enjoy the discount immediately, and use it with a 50% discount
Autumn Reading Project
https://pro.m.jd.com/mall/active/2Z3HoZGKy5i9aEpmoTUZnmcoAhHg/index.html
“Project-driven learning Java from scratch”
“Project Driven Learning Java from Zero” runs through 6 complete projects, which are refined through the author’s many years of teaching experience. The projects range from small to large, from short to long, allowing readers to quickly master a series of knowledge points in the process of practicing projects.
The author is a well-known Java teacher and communicator in China. He has overcome obstacles and worked hard for more than 20 years. It has accumulated rich experience in “training” and “training” and has also produced many high-quality teaching theories.
After decades of development, the Java language system has gradually become large and complex. This book simplifies the complex and extracts the most important knowledge points, allowing readers to get started easily. This book is accompanied by a column course, which provides extended content.
Project-driven learning Java from scratchhttps://item.jd.com/13607758.html
Highlights
Understand Java regular expressions in one article
Understand the lambda anonymous function in Java in one article
Java 21 performance improvements, what new features will be introduced?
Super simple, implement Java thread pool
Super simple and proficient in Java exception handling
Search on WeChat and follow “Java Learning and Research Base Camp”
Visit [IT Today’s Hot List] to discover daily technology hot spots