Excel processing guide in SpringBoot
1. Basics of Excel processing
1.1 Why do you need to process Excel files in your application?
In enterprise application development, Excel file processing is a very common requirement and is mainly used in the following scenarios:
- Data import: Allow users to upload batch data to the system through Excel
- Data Export: Export system data to Excel for users to download and analyze
- Report generation: Generate complex reports and format them into Excel
- Data exchange: As a medium for exchanging data between different systems
- Batch data processing: Processing large amounts of structured data
1.2 Introduction to Excel processing library in Java
There are the following main libraries for processing Excel files in Java:
1.2.1 Apache POI
Apache POI is the most widely used Excel processing library in Java, providing a comprehensive API to create, read and modify Office documents.
advantage：
- Full functionality, supporting all functions of Excel
- Supports .xls (HSSF - Excel 97-2003) and .xlsx (XSSF - Excel 2007+) formats
- Active community and rich documentation
- Supports advanced functions such as formula calculation, charts, and merging cells
shortcoming：
- The API is relatively complex
- Memory consumption is high when processing large files (especially XSSF)
1.2.2 EasyExcel
EasyExcel is an open source Excel processing library from Alibaba. It is based on POI, but has done a lot of optimizations.
advantage：
- Low memory footprint, use SAX mode to read, avoid OOM
- API is simple and easy to use, annotation driver
- Fast reading and writing speed
- Suitable for handling large Excel files
shortcoming：
- Not as comprehensive as POI
- Relatively low flexibility
1.2.3 JExcel
JExcel is another Java library that handles Excel.
advantage：
- The API is simpler
- Faster speed
shortcoming：
- Only support legacy Excel (.xls) format
- No longer active maintenance
- Limited functions
1.2.4 Apache POI SXSSF
SXSSF is a streaming processing mode provided by POI, designed for processing large Excel files.
advantage：
- Greatly reduce memory usage
- Suitable for generating large Excel files
shortcoming：
- Only write operations are supported, not reads
- Functionality is limited than XSSF
1.3 Integrated Excel processing in Spring Boot
Spring Boot itself does not provide Excel processing capabilities, but it can easily integrate various Excel processing libraries mentioned above. This guide will mainly introduce:
- How to integrate Apache POI and EasyExcel in Spring Boot Project
- How to implement common functions of Excel import and export
- How to deal with FAQs and optimize performance
2. Integrate Excel processing library in Spring Boot
2.1 Integration of Apache POI
2.1.1 Add dependencies
existAdd the following dependencies to the file:
<dependency>
<groupId></groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId></groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>If using Gradle,Added in:
implementation ':poi:5.2.3' implementation ':poi-ooxml:5.2.3'
2.1.2 Create a basic configuration class
Create a configuration class to handle Excel-related configurations:
package ;
import ;
import ;
import ;
import ;
import ;
@Configuration
public class ExcelConfig {
@Bean
public MultipartResolver multipartResolver() {
CommonsMultipartResolver resolver = new CommonsMultipartResolver();
(10485760); // Set the maximum upload file to 10MB return resolver;
}
}2.2 Integrated EasyExcel
2.2.1 Add dependencies
existAdd the following dependencies to the file:
<dependency>
<groupId></groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.1</version>
</dependency>If using Gradle,Added in:
implementation ':easyexcel:3.2.1'
2.2.2 Create a configuration class
package ;
import ;
import ;
import ;
import ;
@Configuration
public class EasyExcelConfig {
@Bean
public MultipartResolver multipartResolver() {
CommonsMultipartResolver resolver = new CommonsMultipartResolver();
(10485760); // Set the maximum upload file to 10MB return resolver;
}
}3. Read Excel files using Apache POI
3.1 Creating a Data Model
First, create a model class to map data in Excel:
package ;
import ;
@Data
public class User {
private Long id;
private String name;
private Integer age;
private String email;
private String department;
}3.2 Create an Excel read service
Create a service class to handle Excel file reading:
package ;
import ;
import .*;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
@Service
public class ExcelService {
public List<User> readUsersFromExcel(MultipartFile file) throws IOException {
List<User> userList = new ArrayList<>();
// Get the workbook try (InputStream inputStream = ()) {
Workbook workbook = (inputStream);
// Get the first worksheet Sheet sheet = (0);
// Skip the title line Iterator<Row> rowIterator = ();
if (()) {
(); // Skip the title line }
// traverse data rows while (()) {
Row row = ();
User user = new User();
// Read cell data ((long) (0, .CREATE_NULL_AS_BLANK).getNumericCellValue());
(getCellValueAsString((1)));
((int) (2, .CREATE_NULL_AS_BLANK).getNumericCellValue());
(getCellValueAsString((3)));
(getCellValueAsString((4)));
(user);
}
();
}
return userList;
}
// Get the string value of the cell private String getCellValueAsString(Cell cell) {
if (cell == null) {
return "";
}
switch (()) {
case STRING:
return ();
case NUMERIC:
if ((cell)) {
return ().toString();
} else {
return ((int) ());
}
case BOOLEAN:
return (());
case FORMULA:
return ();
default:
return "";
}
}
}3.3 Create Controller to handle Excel uploads
Create a Controller to handle Excel file uploads:
package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
@RestController
@RequestMapping("/api/excel")
public class ExcelController {
@Autowired
private ExcelService excelService;
@PostMapping("/upload")
public ResponseEntity<List<User>> uploadExcel(@RequestParam("file") MultipartFile file) {
try {
List<User> users = (file);
return (users);
} catch (IOException e) {
();
return ().build();
}
}
}3.4 Create HTML upload page
existsrc/main/resources/templatesCreated in the directory：
<!DOCTYPE html>
<html xmlns:th="">
<head>
<meta charset="UTF-8">
<title>ExcelUpload</title>
<link rel="stylesheet" href="/bootstrap/4.5.2/css/" rel="external nofollow" rel="external nofollow" >
</head>
<body>
<div class="container mt-5">
<div class="card">
<div class="card-header">
<h3>UploadExceldocument</h3>
</div>
<div class="card-body">
<form enctype="multipart/form-data">
<div class="form-group">
<label for="file">chooseExceldocument:</label>
<input type="file" class="form-control-file" name="file" accept=".xls,.xlsx">
</div>
<button type="button" class="btn btn-primary" onclick="uploadExcel()">Upload</button>
</form>
<div class="mt-4">
<h4>Upload结果:</h4>
<div ></div>
</div>
</div>
</div>
</div>
<script src="/jquery-3.5."></script>
<script>
function uploadExcel() {
var formData = new FormData(('uploadForm'));
$.ajax({
url: '/api/excel/upload',
type: 'POST',
data: formData,
processData: false,
contentType: false,
success: function(response) {
var resultHtml = '<table class="table table-striped">' +
'<thead><tr><th>ID</th><th>Name</th><th>age</th><th>Mail</th><th>department</th></tr></thead>' +
'<tbody>';
for (var i = 0; i < ; i++) {
var user = response[i];
resultHtml += '<tr>' +
'<td>' + + '</td>' +
'<td>' + + '</td>' +
'<td>' + + '</td>' +
'<td>' + + '</td>' +
'<td>' + + '</td>' +
'</tr>';
}
resultHtml += '</tbody></table>';
$('#resultContainer').html(resultHtml);
},
error: function(error) {
$('#resultContainer').html('<div class="alert alert-danger">Upload failed: ' + + '</div>'); }
});
}
</script>
</body>
</html>3.5 Handling more complex Excel structures
In practical applications, Excel structure may be more complex, such as multiple worksheets, merging cells, formulas, etc. Here are examples of handling these situations:
public List<Department> readComplexExcel(MultipartFile file) throws IOException {
List<Department> departments = new ArrayList<>();
try (InputStream inputStream = ()) {
Workbook workbook = (inputStream);
// Read department information (first worksheet) Sheet departmentSheet = (0);
for (int i = 1; i <= (); i++) {
Row row = (i);
if (row == null) continue;
Department department = new Department();
((long) (0).getNumericCellValue());
((1).getStringCellValue());
((2).getStringCellValue());
(new ArrayList<>());
(department);
}
// Read employee information (second worksheet) Sheet employeeSheet = (1);
for (int i = 1; i <= (); i++) {
Row row = (i);
if (row == null) continue;
User employee = new User();
((long) (0).getNumericCellValue());
((1).getStringCellValue());
((int) (2).getNumericCellValue());
((3).getStringCellValue());
// Obtain the department ID and associate it with the corresponding department long departmentId = (long) (4).getNumericCellValue();
for (Department dept : departments) {
if (() == departmentId) {
().add(employee);
break;
}
}
}
();
}
return departments;
}4. Create and export Excel files using Apache POI
4.1 Create a basic Excel file
Here is an example of creating a simple Excel file:
package ;
import ;
import .*;
import ;
import ;
import ;
import ;
import ;
import ;
@Service
public class ExcelExportService {
public ByteArrayInputStream exportUsersToExcel(List<User> users) throws IOException {
try (Workbook workbook = new XSSFWorkbook()) {
// Create worksheets Sheet sheet = ("User Data");
// Create a header style Font headerFont = ();
(true);
(());
CellStyle headerCellStyle = ();
(headerFont);
(IndexedColors.LIGHT_YELLOW.getIndex());
(FillPatternType.SOLID_FOREGROUND);
();
();
();
();
// Create a header row Row headerRow = (0);
// Create a header cell Cell cell0 = (0);
("ID");
(headerCellStyle);
Cell cell1 = (1);
("Name");
(headerCellStyle);
Cell cell2 = (2);
("age");
(headerCellStyle);
Cell cell3 = (3);
("Mail");
(headerCellStyle);
Cell cell4 = (4);
("department");
(headerCellStyle);
// Set the data cell style CellStyle dataCellStyle = ();
();
();
();
();
// Create data rows int rowIdx = 1;
for (User user : users) {
Row row = (rowIdx++);
Cell idCell = (0);
(());
(dataCellStyle);
Cell nameCell = (1);
(());
(dataCellStyle);
Cell ageCell = (2);
(());
(dataCellStyle);
Cell emailCell = (3);
(());
(dataCellStyle);
Cell deptCell = (4);
(());
(dataCellStyle);
}
// Automatically adjust column width for (int i = 0; i < 5; i++) {
(i);
}
// Write to ByteArrayOutputStream ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
(outputStream);
return new ByteArrayInputStream(());
}
}
}4.2 Create an export controller
package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
@RestController
@RequestMapping("/api/excel")
public class ExcelExportController {
@Autowired
private ExcelExportService excelExportService;
@GetMapping("/export")
public ResponseEntity<InputStreamResource> exportUsers() throws IOException {
// Generate sample data List<User> users = getTestUsers();
// Generate Excel file ByteArrayInputStream in = (users);
// Set HTTP header HttpHeaders headers = new HttpHeaders();
("Content-Disposition", "attachment; filename=");
// Return to Excel file return ResponseEntity
.ok()
.headers(headers)
.contentType(("application/-excel"))
.body(new InputStreamResource(in));
}
// Generate test user data private List<User> getTestUsers() {
List<User> users = new ArrayList<>();
User user1 = new User();
(1L);
("Zhang San");
(28);
("zhangsan@");
("R&D Department");
(user1);
User user2 = new User();
(2L);
("Li Si");
(32);
("lisi@");
("Market Department");
(user2);
User user3 = new User();
(3L);
("Wang Wu");
(45);
("wangwu@");
("Ministry of Administration");
(user3);
User user4 = new User();
(4L);
("Zhao Liu");
(36);
("zhaoliu@");
("Treasury Department");
(user4);
User user5 = new User();
(5L);
("Qian Qi");
(29);
("qianqi@");
("Human Resources Department");
(user5);
return users;
}
}4.3 Create an export page
existsrc/main/resources/templatesCreated in the directory：
<!DOCTYPE html>
<html xmlns:th="">
<head>
<meta charset="UTF-8">
<title>ExcelExport</title>
<link rel="stylesheet" href="/bootstrap/4.5.2/css/" rel="external nofollow" rel="external nofollow" >
</head>
<body>
<div class="container mt-5">
<div class="card">
<div class="card-header">
<h3>ExportExceldocument</h3>
</div>
<div class="card-body">
<p>点击下面的按钮Export用户数据到Exceldocument：</p>
<a href="/api/excel/export" rel="external nofollow" class="btn btn-primary">Export用户数据</a>
</div>
</div>
</div>
</body>
</html>4.4 Creating complex Excel files
Here is an example of creating a more complex Excel file, including multiple worksheets, merged cells, formulas, and more:
public ByteArrayInputStream exportComplexExcel(List<Department> departments) throws IOException {
try (Workbook workbook = new XSSFWorkbook()) {
// Create fonts and styles Font headerFont = ();
(true);
((short) 14);
CellStyle headerStyle = ();
(headerFont);
(IndexedColors.LIGHT_BLUE.getIndex());
(FillPatternType.SOLID_FOREGROUND);
();
CellStyle titleStyle = ();
Font titleFont = ();
(true);
((short) 16);
(titleFont);
();
// Create a summary table Sheet summarySheet = ("Department Summary");
// Create a title line Row titleRow = (0);
Cell titleCell = (0);
("Company Department Personnel Statistics");
(titleStyle);
// Merge the title cells (new CellRangeAddress(0, 0, 0, 3));
// Create a header Row headerRow = (1);
String[] headers = {"Department ID", "Department Name", "Department Manager", "Number of employees"};
for (int i = 0; i < ; i++) {
Cell cell = (i);
(headers[i]);
(headerStyle);
}
// Fill in department data int rowIdx = 2;
int totalEmployees = 0;
for (Department dept : departments) {
Row row = (rowIdx++);
(0).setCellValue(());
(1).setCellValue(());
(2).setCellValue(());
(3).setCellValue(().size());
totalEmployees += ().size();
// Create separate worksheets for each department Sheet deptSheet = (());
// Create department header Row deptHeaderRow = (0);
Cell deptTitleCell = (0);
(() + " - Employee List");
(titleStyle);
(new CellRangeAddress(0, 0, 0, 4));
// Employee table Row empHeaderRow = (1);
String[] empHeaders = {"Employee ID", "Name", "age", "Mail", "Enterprise Year"};
for (int i = 0; i < ; i++) {
Cell cell = (i);
(empHeaders[i]);
(headerStyle);
}
// Fill in employee data int empRowIdx = 2;
for (User emp : ()) {
Row empRow = (empRowIdx++);
(0).setCellValue(());
(1).setCellValue(());
(2).setCellValue(());
(3).setCellValue(());
// Use the formula to calculate the term of employment (assuming age minus 25) Cell tenureCell = (4);
("C" + empRowIdx + "-25");
}
// Automatically adjust column width for (int i = 0; i < 5; i++) {
(i);
}
}
// Create a total row Row totalRow = (rowIdx);
Cell totalLabelCell = (0);
("total");
(headerStyle);
// Merge total label cells (new CellRangeAddress(rowIdx, rowIdx, 0, 2));
Cell totalValueCell = (3);
(totalEmployees);
(headerStyle);
// Automatically adjust column width for (int i = 0; i < 4; i++) {
(i);
}
// Add a chart XSSFSheet chartSheet = (XSSFSheet) ("Department Statistical Chart");
// Copy department data to chart data table Row chartHeaderRow = (0);
(0).setCellValue("department");
(1).setCellValue("Number of Employees");
int chartRowIdx = 1;
for (Department dept : departments) {
Row row = (chartRowIdx++);
(0).setCellValue(());
(1).setCellValue(().size());
}
// Create charts and data sequences XSSFDrawing drawing = ();
XSSFClientAnchor anchor = (0, 0, 0, 0, 4, 0, 15, 15);
XSSFChart chart = (anchor);
("Department personnel distribution");
(false);
XDDFChartLegend legend = ();
();
// X-axis and Y-axis XDDFCategoryAxis bottomAxis = ();
("department");
XDDFValueAxis leftAxis = ();
("Number of Employees");
// Create a data source XDDFDataSource<String> departments = (
chartSheet, new CellRangeAddress(1, chartRowIdx - 1, 0, 0));
XDDFNumericalDataSource<Double> values = (
chartSheet, new CellRangeAddress(1, chartRowIdx - 1, 1, 1));
// Create a bar chart XDDFBarChartData barChart = (XDDFBarChartData) (
, bottomAxis, leftAxis);
(true);
series = () (departments, values);
("Number of Employees", null);
(barChart);
// Write to ByteArrayOutputStream ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
(outputStream);
return new ByteArrayInputStream(());
}
}Note: The above chart code needs to add the following dependencies:
<dependency>
<groupId></groupId>
<artifactId>poi-ooxml-full</artifactId>
<version>5.2.3</version>
</dependency>4.5 Export Excel using templates
In some scenarios, we need to generate files based on predefined Excel templates, and the following is an example:
public ByteArrayInputStream exportFromTemplate(List<User> users) throws IOException {
// Load the template file try (InputStream templateStream = getClass().getResourceAsStream("/templates/user_template.xlsx");
Workbook workbook = (templateStream)) {
Sheet sheet = (0);
//Fill data from the second row (the first row is the header) int rowIdx = 1;
for (User user : users) {
Row row = (rowIdx++);
(0).setCellValue(());
(1).setCellValue(());
(2).setCellValue(());
(3).setCellValue(());
(4).setCellValue(());
}
// Update the date cell in the template (assuming it is in the A1 position) Row headerRow = (0);
if ((6) != null) {
Cell dateCell = (6);
(new Date());
}
// Automatically adjust column width for (int i = 0; i < 5; i++) {
(i);
}
// Write to ByteArrayOutputStream ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
(outputStream);
return new ByteArrayInputStream(());
}
}5. Use EasyExcel to process Excel files
EasyExcel is Alibaba's open source POI-based Excel processing tool. Compared with native POI, it provides a cleaner API and has obvious performance advantages when processing large files.
5.1 Read Excel using EasyExcel
5.1.1 Creating a Data Model
When using EasyExcel, annotations are usually used to map Excel columns:
package ;
import ;
import ;
import ;
import ;
@Data
public class Employee {
@ExcelProperty("Employee ID")
private Long id;
@ExcelProperty("Name")
private String name;
@ExcelProperty("age")
private Integer age;
@ExcelProperty("Mail")
private String email;
@ExcelProperty("department")
private String department;
@ExcelProperty("Order date")
@DateTimeFormat("yyyy-MM-dd")
private Date hireDate;
@ExcelProperty("Salary")
private Double salary;
}5.1.2 Create a read listener
EasyExcel uses event mode to read Excel, and needs to create a listener to process the read data:
package ;
import ;
import ;
import ;
import .slf4j.Slf4j;
import ;
import ;
@Slf4j
public class EmployeeReadListener extends AnalysisEventListener<Employee> {
/**
* Used to temporarily store read data
*/
private List<Employee> employeeList = new ArrayList<>();
/**
* The invoke method will be called once every time a row of data is read
*/
@Override
public void invoke(Employee employee, AnalysisContext context) {
("Read a data: {}", employee);
(employee);
// When BATCH_COUNT is reached, the database needs to be stored once to prevent tens of thousands of pieces of data from being in memory, making it easy to OOM if (() >= 5000) {
saveData();
// Clean the memory ();
}
}
/**
* Call this method after all data parsing is completed
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// Make sure the last batch of data is saved saveData();
("All data parsing is completed!");
}
/**
* Save data, here is just printing, and in actual applications, you can save the data into the database
*/
private void saveData() {
("{}Data，Start saving the database！", ());
// Here you can call the persistence layer to complete the data entry into the database ("Storing the database successfully!");
}
/**
* Get the read data
*/
public List<Employee> getEmployeeList() {
return employeeList;
}
}5.1.3 Create an Excel read service
package ;
import ;
import ;
import ;
import .slf4j.Slf4j;
import ;
import ;
import ;
import ;
@Slf4j
@Service
public class EasyExcelService {
public List<Employee> readEmployeeData(MultipartFile file) throws IOException {
EmployeeReadListener listener = new EmployeeReadListener();
((), , listener).sheet().doRead();
return ();
}
}5.1.4 Creating a Controller
package ;
import ;
import ;
import .slf4j.Slf4j;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
@Slf4j
@RestController
@RequestMapping("/api/easyexcel")
public class EasyExcelController {
@Autowired
private EasyExcelService easyExcelService;
@PostMapping("/upload")
public ResponseEntity<List<Employee>> uploadExcel(@RequestParam("file") MultipartFile file) {
try {
List<Employee> employees = (file);
return (employees);
} catch (IOException e) {
("Excel read failed", e);
return ().build();
}
}
}5.2 Export Excel using EasyExcel
5.2.1 Simple export example
package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
@Service
public class EasyExcelExportService {
/**
* Export employee data to Excel file
*/
public void exportEmployees(List<Employee> employees, OutputStream outputStream) {
(outputStream, )
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // Automatically adjust column width .sheet("Employee Data")
.doWrite(employees);
}
/**
* Export employee data to specified file
*/
public void exportEmployeesToFile(List<Employee> employees, String fileName) throws IOException {
// Make sure the directory exists File file = new File(fileName);
if (!().exists()) {
().mkdirs();
}
(fileName, )
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.sheet("Employee Data")
.doWrite(employees);
}
/**
* Export multiple Sheets Excel
*/
public void exportMultipleSheets(List<List<Employee>> departmentEmployees,
List<String> sheetNames,
OutputStream outputStream) {
// Create ExcelWriter try (var excelWriter = (outputStream, )
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.build()) {
// Multiple Sheets are written to the same object for (int i = 0; i < (); i++) {
// Get the Sheet name String sheetName = i < () ? (i) : "Sheet" + (i + 1);
// Create a new Sheet var writeSheet = (i, sheetName).build();
// Write data ((i), writeSheet);
}
}
}
}5.2.2 Create Controller
package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
@RestController
@RequestMapping("/api/easyexcel")
public class EasyExcelExportController {
@Autowired
private EasyExcelExportService exportService;
@GetMapping("/export")
public void exportEmployees(HttpServletResponse response) throws IOException {
// Set the response content ("application/");
("utf-8");
// Set file name String fileName = ("Employee Data", StandardCharsets.UTF_8).replaceAll("\\+", "%20");
("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// Get test data List<Employee> employees = getTestEmployees();
// Export Excel (employees, ());
}
@GetMapping("/export-multiple-sheets")
public void exportMultipleSheets(HttpServletResponse response) throws IOException {
// Set the response content ("application/");
("utf-8");
// Set file name String fileName = ("Department Employee Data", StandardCharsets.UTF_8).replaceAll("\\+", "%20");
("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// Get test data - Employees in three departments List<List<Employee>> departmentEmployees = new ArrayList<>();
(getEmployeesByDepartment("R&D Department"));
(getEmployeesByDepartment("Market Department"));
(getEmployeesByDepartment("Ministry of Administration"));
// Sheet name List<String> sheetNames = ("R&D Department Employee", "State of Marketing", "Administrative Office Staff");
// Export Excel (departmentEmployees, sheetNames, ());
}
/**
* Generate test employee data
*/
private List<Employee> getTestEmployees() {
List<Employee> employees = new ArrayList<>();
// Add test data for (int i = 1; i <= 10; i++) {
Employee employee = new Employee();
((long) i);
("staff" + i);
(20 + i);
("employee" + i + "@");
(i % 3 == 0 ? "R&D Department" : (i % 3 == 1 ? "Market Department" : "Ministry of Administration"));
(new Date());
(5000.0 + i * 1000);
(employee);
}
return employees;
}
/**
* Obtain employees according to the department
*/
private List<Employee> getEmployeesByDepartment(String department) {
List<Employee> allEmployees = getTestEmployees();
List<Employee> departmentEmployees = new ArrayList<>();
for (Employee employee : allEmployees) {
if ((())) {
(employee);
}
}
return departmentEmployees;
}
// ... 5.2.3 Using custom styles and complex table headers
/**
* Export custom style Excel
*/
public void exportWithCustomStyle(List<Employee> employees, OutputStream outputStream) {
// Set custom interceptors to handle styles (outputStream, )
// Automatically adjust column width .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
// Set the header style .registerWriteHandler(new AbstractRowHeightStyleStrategy() {
@Override
protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
// Set the header row height ((short) 500);
}
@Override
protected void setContentColumnHeight(Row row, int relativeRowIndex) {
// Set content line height ((short) 400);
}
})
// Set cell style .registerWriteHandler(new CellWriteHandler() {
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// Set the header style if (isHead) {
Workbook workbook = ().getWorkbook();
CellStyle style = ();
Font font = ();
(true);
((short) 12);
(());
(font);
(IndexedColors.ROYAL_BLUE.getIndex());
(FillPatternType.SOLID_FOREGROUND);
();
();
(style);
}
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// Here you can set styles according to the data content }
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// Content line style if (!isHead) {
// Even lines set background color if (relativeRowIndex % 2 == 0) {
Workbook workbook = ().getWorkbook();
CellStyle style = ();
(IndexedColors.PALE_BLUE.getIndex());
(FillPatternType.SOLID_FOREGROUND);
();
();
(style);
}
}
}
})
.sheet("Employee Data")
.doWrite(employees);
}
/**
* Export complex table headers Excel
*/
public void exportWithComplexHead(List<Employee> employees, OutputStream outputStream) {
// Build complex table headers List<List<String>> head = new ArrayList<>();
// First column ID List<String> head1 = new ArrayList<>();
("Basic Information");
("Employee ID");
(head1);
// Second column Name List<String> head2 = new ArrayList<>();
("Basic Information");
("Name");
(head2);
// Column 3 Age List<String> head3 = new ArrayList<>();
("Basic Information");
("age");
(head3);
// Column 4 Email List<String> head4 = new ArrayList<>();
("Contact Information");
("Mail");
(head4);
// Column 5 Department List<String> head5 = new ArrayList<>();
("Work Information");
("department");
(head5);
// Column Sixth Entry Date List<String> head6 = new ArrayList<>();
("Work Information");
("Order date");
(head6);
// Column 7 Salary List<String> head7 = new ArrayList<>();
("Salary Information");
("Monthly salary(Yuan)");
(head7);
// Convert data to List<List<Object>> format List<List<Object>> dataList = new ArrayList<>();
for (Employee employee : employees) {
List<Object> data = new ArrayList<>();
(());
(());
(());
(());
(());
(());
(());
(data);
}
// Write to Excel (outputStream)
.head(head)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.sheet("Employee Data")
.doWrite(dataList);
}
}6. Strategy for handling large Excel files
6.1 Using Apache POI SXSSF Mode
SXSSF (Streaming Xlsx Writer) is a streaming writing method provided by POI, which can greatly reduce memory usage:
public void exportLargeExcel(String fileName, int rowCount) throws IOException {
try (SXSSFWorkbook workbook = new SXSSFWorkbook(100)) { // 100 indicates the number of rows reserved in memory Sheet sheet = ("Big Data");
// Create a header Row headerRow = (0);
for (int i = 0; i < 10; i++) {
(i).setCellValue("List " + (i + 1));
}
// Create data rows for (int i = 0; i < rowCount; i++) {
Row row = (i + 1);
for (int j = 0; j < 10; j++) {
(j).setCellValue("data " + (i + 1) + "-" + (j + 1));
}
// Clean up temporary files every 10,000 lines generated if (i % 10000 == 0) {
((SXSSFSheet)sheet).flushRows();
}
}
// Write to the file try (FileOutputStream outputStream = new FileOutputStream(fileName)) {
(outputStream);
}
// Clean up temporary files ();
}
}Notes:
- Be sure to call after use
dispose()Method to clean temporary files - SXSSF only supports write operations, not reads
- Some advanced features (such as merging cells, etc.) are not supported
6.2 Using EasyExcel to handle large files
EasyExcel has designed to consider large file processing, and uses SAX to read line by line, with a small memory footprint:
// Read large filespublic void readLargeExcel(String fileName) {
// Read using SAX (fileName, , new EmployeeReadListener())
.sheet()
.doRead();
}
// Write to large filespublic void writeLargeExcel(String fileName, int batchSize) {
// Get data in batches try (ExcelWriter excelWriter = (fileName, )
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.build()) {
// Get the WriteSheet object WriteSheet writeSheet = ("Employee Data").build();
// Simulate data acquisition in batches int totalCount = 100000; // Total data volume for (int i = 0; i < totalCount; i += batchSize) {
// Get the current batch data List<Employee> data = getBatchData(i, (i + batchSize, totalCount));
// Write to Excel (data, writeSheet);
}
}
}
// Simulate data acquisition in batchesprivate List<Employee> getBatchData(int start, int end) {
List<Employee> list = new ArrayList<>();
for (int i = start; i < end; i++) {
Employee employee = new Employee();
((long) i);
("staff" + i);
(20 + (i % 20));
("employee" + i + "@");
(i % 3 == 0 ? "R&D Department" : (i % 3 == 1 ? "Market Department" : "Ministry of Administration"));
(new Date());
(5000.0 + (i % 10) * 1000);
(employee);
}
return list;
}6.3 Use CSV instead of Excel
For extremely large data sets, consider using CSV format instead of Excel:
public void exportToCsv(List<Employee> employees, String fileName) throws IOException {
try (FileWriter writer = new FileWriter(fileName);
CSVPrinter csvPrinter = new CSVPrinter(writer,
.withHeader("ID", "Name", "age", "Mail", "department", "Order date", "Salary"))) {
for (Employee employee : employees) {
(
(),
(),
(),
(),
(),
(),
()
);
}
();
}
}Note: Using CSV requires adding dependencies:
<dependency>
<groupId></groupId>
<artifactId>commons-csv</artifactId>
<version>1.9.0</version>
</dependency>6.4 Pagination Export Large Data Sets
For large data sets that need to be exported in web applications, you can consider paging export:
@GetMapping("/export/paged")
public ResponseEntity<String> exportPaged() {
// Generate a unique task ID String taskId = ().toString();
// Start asynchronous task (() -> {
try {
// Export file path String filePath = "/temp/" + taskId + ".xlsx";
//Page query data and write to Excel int pageSize = 1000;
int totalPages = getTotalPages(pageSize);
try (ExcelWriter excelWriter = (filePath, )
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.build()) {
WriteSheet writeSheet = ("Employee Data").build();
// Pagination export for (int pageNum = 0; pageNum < totalPages; pageNum++) {
// Query data from database paging List<Employee> pageData = getPageData(pageNum, pageSize);
// Write to Excel (pageData, writeSheet);
// Update progress updateExportProgress(taskId, (pageNum + 1) * 100 / totalPages);
}
}
// Update the export status to complete updateExportStatus(taskId, "COMPLETED", filePath);
} catch (Exception e) {
// Update export status failed updateExportStatus(taskId, "FAILED", null);
}
});
// Return task ID return (taskId);
}
@GetMapping("/export/status/{taskId}")
public ResponseEntity<Map<String, Object>> getExportStatus(@PathVariable String taskId) {
// Get task status Map<String, Object> status = getTaskStatus(taskId);
return (status);
}
@GetMapping("/export/download/{taskId}")
public ResponseEntity<Resource> downloadExportedFile(@PathVariable String taskId) {
// Get the export file path String filePath = getExportedFilePath(taskId);
if (filePath == null) {
return ().build();
}
// Create file resources Resource resource = new FileSystemResource(filePath);
return ()
.header(HttpHeaders.CONTENT_DISPOSITION,
"attachment; filename=employee_data.xlsx")
.contentType(("application/-excel"))
.body(resource);
}7. Practical application scenarios and best practices
7.1 Dynamic column export
In some business scenarios, the exported column needs to be dynamically determined based on user selection:
public ByteArrayInputStream exportDynamicColumns(List<Employee> employees, List<String> selectedColumns) throws IOException {
// Define all possible columns Map<String, String> allColumns = new HashMap<>();
("id", "Employee ID");
("name", "Name");
("age", "age");
("email", "Mail");
("department", "department");
("hireDate", "Order date");
("salary", "Salary");
try (Workbook workbook = new XSSFWorkbook()) {
Sheet sheet = ("Employee Data");
// Create a header row Row headerRow = (0);
// Set the header style CellStyle headerStyle = ();
Font headerFont = ();
(true);
(headerFont);
// Fill the table header int colIdx = 0;
for (String column : selectedColumns) {
if ((column)) {
Cell cell = (colIdx++);
((column));
(headerStyle);
}
}
// Fill in data int rowIdx = 1;
for (Employee employee : employees) {
Row row = (rowIdx++);
colIdx = 0;
for (String column : selectedColumns) {
Cell cell = (colIdx++);
// Set cell value according to column name switch (column) {
case "id":
(());
break;
case "name":
(());
break;
case "age":
(());
break;
case "email":
(());
break;
case "department":
(());
break;
case "hireDate":
if (() != null) {
(());
// Set date format CellStyle dateStyle = ();
CreationHelper createHelper = ();
(().getFormat("yyyy-mm-dd"));
(dateStyle);
}
break;
case "salary":
(());
break;
}
}
}
// Automatically adjust column width for (int i = 0; i < (); i++) {
(i);
}
// Output ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
(outputStream);
return new ByteArrayInputStream(());
}
}7.2 Excel template filling
Generate Excel using Freemarker or other template engine:
public ByteArrayInputStream fillTemplate(Map<String, Object> data) throws Exception {
// Loading template Configuration cfg = new Configuration(Configuration.VERSION_2_3_30);
(getClass().getClassLoader(), "templates");
("UTF-8");
// Get the template Template template = ("excel_template.ftl");
// Output directory File tempDir = new File((""));
File tempFile = new File(tempDir, "temp_" + () + ".xlsx");
// Fill the template try (Writer out = new FileWriter(tempFile)) {
(data, out);
}
// Read the filled file try (FileInputStream fis = new FileInputStream(tempFile)) {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
byte[] buffer = new byte[1024];
int len;
while ((len = (buffer)) > -1) {
(buffer, 0, len);
}
();
// Delete temporary files ();
return new ByteArrayInputStream(());
}
}7.3 Excel file verification
Perform data verification before importing Excel files:
public class ExcelValidationListener extends AnalysisEventListener<Employee> {
private List<Employee> validEmployees = new ArrayList<>();
private List<Map<String, Object>> errorRecords = new ArrayList<>();
private int rowIndex = 1; // Starting from 1, 0 is the header
@Override
public void invoke(Employee employee, AnalysisContext context) {
rowIndex++;
// Verify data List<String> errors = validateEmployee(employee);
if (()) {
// The data is valid (employee);
} else {
// Record error Map<String, Object> errorRecord = new HashMap<>();
("rowIndex", rowIndex);
("data", employee);
("errors", errors);
(errorRecord);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// Processing is completed }
// Verify employee data private List<String> validateEmployee(Employee employee) {
List<String> errors = new ArrayList<>();
// Verify name if (() == null || ().trim().isEmpty()) {
("The name cannot be empty");
}
// Verify age if (() == null) {
("Age cannot be empty");
} else if (() < 18 || () > 65) {
("Age must be between 18 and 65 years old");
}
// Verify the email address if (() != null && !().isEmpty()) {
String emailRegex = "^[a-zA-Z0-9_+&*-]+(?:\\.[a-zA-Z0-9_+&*-]+)*@" +
"(?:[a-zA-Z0-9-]+\\.)+[a-zA-Z]{2,7}$";
if (!().matches(emailRegex)) {
("The email format is incorrect");
}
}
// Verification Department if (() == null || ().trim().isEmpty()) {
("The department cannot be empty");
}
// Verify salary if (() != null && () < 0) {
("Salary cannot be negative");
}
return errors;
}
public List<Employee> getValidEmployees() {
return validEmployees;
}
public List<Map<String, Object>> getErrorRecords() {
return errorRecords;
}
public boolean hasErrors() {
return !();
}
}7.4 Unified exception handling
Add unified exception handling for Excel processing:
@ControllerAdvice
public class ExcelExceptionHandler {
private static final Logger logger = ();
@ExceptionHandler()
public ResponseEntity<Map<String, String>> handleIOException(IOException e) {
("File read and write exception", e);
Map<String, String> response = new HashMap<>();
("error", "File read and write exception");
("message", ());
return (HttpStatus.INTERNAL_SERVER_ERROR).body(response);
}
@ExceptionHandler()
public ResponseEntity<Map<String, String>> handleIllegalArgumentException(IllegalArgumentException e) {
("Parameter exception", e);
Map<String, String> response = new HashMap<>();
("error", "Parameter exception");
("message", ());
return (HttpStatus.BAD_REQUEST).body(response);
}
@ExceptionHandler()
public ResponseEntity<Map<String, String>> handleGenericException(Exception e) {
("Excel handles exceptions", e);
Map<String, String> response = new HashMap<>();
("error", "Excel handles exceptions");
("message", ());
return (HttpStatus.INTERNAL_SERVER_ERROR).body(response);
}
}8. Performance optimization and precautions
8.1 Performance optimization suggestions
Use the appropriate Excel library：
- Apache POI can be used in small files
- Please use EasyExcel or POI's SXSSF mode for large files
- Consider using CSV format for extremely large files
Avoid loading entire files at once：
- Use streaming parsing when reading
- Use batch writes when writing
Set the buffer size reasonably：
- Setting a reasonable number of memory lines in SXSSFWorkbook
- Select the appropriate batch size in batch processing
Reduce style objects：
- Style object reuse instead of creating new styles for each cell
- Limit the number of colors, fonts, and border styles used
Using asynchronous processing：
- Put large file processing in background threads to execute
- Provide progress feedback mechanism
8.2 Notes
Memory management：
- Pay attention to monitoring JVM memory usage
- For large file processing, consider increasing JVM heap memory (-Xmx parameter)
- Close resources and clean temporary files in time after use
Safety considerations：
- Limit uploaded file size
- Verify file type and content
- Prevent malicious Excel files (including macros or formulas)
Coding issues：
- Make sure to use the correct character encoding when processing international characters
- Make sure to encode correctly when the file name contains Chinese
Concurrent control：
- Pay attention to server load when processing large files
- Limit the number of concurrent processing tasks
Temporary file cleaning：
- When using SXSSF, the dispose() method must be called to clean the temporary file
- Regularly clean temporary files on the server
Summarize
Spring Boot provides powerful and flexible Excel processing capabilities. By combining tools such as Apache POI and EasyExcel, you can easily realize the reading, creation and export of Excel files. In actual applications, appropriate processing strategies should be selected based on specific needs and data volumes, which should not only ensure complete functions, but also pay attention to performance and resource use.
Whether it is simple data export, complex report generation, or large data file processing, it can be flexibly implemented through the methods introduced in this article. The key is to choose appropriate technical solutions based on actual business scenarios, and pay attention to performance optimization and exception handling.
The above is personal experience. I hope you can give you a reference and I hope you can support me more.