SpringBoot中Excel處理完全指南分享
SpringBoot中Excel處理指南
1. Excel處理基礎知識
1.1 為什么需要在應用中處理Excel文件?
在企業(yè)應用開發(fā)中,Excel文件處理是一個非常常見的需求,主要用于以下場景:
- 數(shù)據(jù)導入:允許用戶通過Excel上傳批量數(shù)據(jù)到系統(tǒng)
- 數(shù)據(jù)導出:將系統(tǒng)數(shù)據(jù)導出為Excel供用戶下載分析
- 報表生成:生成復雜的報表并格式化為Excel
- 數(shù)據(jù)交換:作為不同系統(tǒng)間交換數(shù)據(jù)的媒介
- 批量數(shù)據(jù)處理:處理大量結構化數(shù)據(jù)
1.2 Java中的Excel處理庫介紹
Java中處理Excel文件的主要庫有以下幾種:
1.2.1 Apache POI
Apache POI是Java中使用最廣泛的Excel處理庫,提供了全面的API來創(chuàng)建、讀取和修改Office文檔。
優(yōu)點:
- 功能全面,支持Excel所有功能
- 支持.xls (HSSF - Excel 97-2003)和.xlsx (XSSF - Excel 2007+)格式
- 社區(qū)活躍,文檔豐富
- 支持公式計算、圖表、合并單元格等高級功能
缺點:
- API相對復雜
- 處理大文件時內存消耗大(尤其是XSSF)
1.2.2 EasyExcel
EasyExcel是阿里巴巴開源的Excel處理庫,基于POI,但做了大量優(yōu)化。
優(yōu)點:
- 內存占用低,使用SAX模式讀取,避免OOM
- API簡單易用,注解驅動
- 讀寫速度快
- 適合處理大型Excel文件
缺點:
- 功能不如POI全面
- 靈活性相對較低
1.2.3 JExcel
JExcel是另一個處理Excel的Java庫。
優(yōu)點:
- API較簡單
- 速度較快
缺點:
- 僅支持舊版Excel (.xls)格式
- 不再積極維護
- 功能有限
1.2.4 Apache POI SXSSF
SXSSF是POI提供的一種流式處理模式,專為處理大型Excel文件設計。
優(yōu)點:
- 大大降低內存占用
- 適合生成大型Excel文件
缺點:
- 僅支持寫入操作,不支持讀取
- 功能比XSSF受限
1.3 Spring Boot中集成Excel處理
Spring Boot本身不提供Excel處理功能,但可以輕松集成上述各種Excel處理庫。本指南將主要介紹:
- 如何在Spring Boot項目中集成Apache POI和EasyExcel
- 如何實現(xiàn)Excel導入導出的常見功能
- 如何處理常見問題和優(yōu)化性能
2. 在Spring Boot中集成Excel處理庫
2.1 集成Apache POI
2.1.1 添加依賴
在pom.xml
文件中添加以下依賴:
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>5.2.3</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>5.2.3</version> </dependency>
如果使用Gradle,在build.gradle
中添加:
implementation 'org.apache.poi:poi:5.2.3' implementation 'org.apache.poi:poi-ooxml:5.2.3'
2.1.2 創(chuàng)建基本配置類
創(chuàng)建一個配置類來處理Excel相關的配置:
package com.example.excel.config; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.web.multipart.MultipartResolver; import org.springframework.web.multipart.commons.CommonsMultipartResolver; @Configuration public class ExcelConfig { @Bean public MultipartResolver multipartResolver() { CommonsMultipartResolver resolver = new CommonsMultipartResolver(); resolver.setMaxUploadSize(10485760); // 設置上傳文件最大為10MB return resolver; } }
2.2 集成EasyExcel
2.2.1 添加依賴
在pom.xml
文件中添加以下依賴:
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.2.1</version> </dependency>
如果使用Gradle,在build.gradle
中添加:
implementation 'com.alibaba:easyexcel:3.2.1'
2.2.2 創(chuàng)建配置類
package com.example.excel.config; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.web.multipart.MultipartResolver; import org.springframework.web.multipart.commons.CommonsMultipartResolver; @Configuration public class EasyExcelConfig { @Bean public MultipartResolver multipartResolver() { CommonsMultipartResolver resolver = new CommonsMultipartResolver(); resolver.setMaxUploadSize(10485760); // 設置上傳文件最大為10MB return resolver; } }
3. 使用Apache POI讀取Excel文件
3.1 創(chuàng)建數(shù)據(jù)模型
首先,創(chuàng)建一個模型類來映射Excel中的數(shù)據(jù):
package com.example.excel.model; import lombok.Data; @Data public class User { private Long id; private String name; private Integer age; private String email; private String department; }
3.2 創(chuàng)建Excel讀取服務
創(chuàng)建一個服務類來處理Excel文件讀?。?/p>
package com.example.excel.service; import com.example.excel.model.User; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.Iterator; import java.util.List; @Service public class ExcelService { public List<User> readUsersFromExcel(MultipartFile file) throws IOException { List<User> userList = new ArrayList<>(); // 獲取工作簿 try (InputStream inputStream = file.getInputStream()) { Workbook workbook = WorkbookFactory.create(inputStream); // 獲取第一個工作表 Sheet sheet = workbook.getSheetAt(0); // 跳過標題行 Iterator<Row> rowIterator = sheet.rowIterator(); if (rowIterator.hasNext()) { rowIterator.next(); // 跳過標題行 } // 遍歷數(shù)據(jù)行 while (rowIterator.hasNext()) { Row row = rowIterator.next(); User user = new User(); // 讀取單元格數(shù)據(jù) user.setId((long) row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getNumericCellValue()); user.setName(getCellValueAsString(row.getCell(1))); user.setAge((int) row.getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getNumericCellValue()); user.setEmail(getCellValueAsString(row.getCell(3))); user.setDepartment(getCellValueAsString(row.getCell(4))); userList.add(user); } workbook.close(); } return userList; } // 獲取單元格的字符串值 private String getCellValueAsString(Cell cell) { if (cell == null) { return ""; } switch (cell.getCellType()) { case STRING: return cell.getStringCellValue(); case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue().toString(); } else { return String.valueOf((int) cell.getNumericCellValue()); } case BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case FORMULA: return cell.getCellFormula(); default: return ""; } } }
3.3 創(chuàng)建Controller處理Excel上傳
創(chuàng)建一個Controller來處理Excel文件上傳:
package com.example.excel.controller; import com.example.excel.model.User; import com.example.excel.service.ExcelService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.ResponseEntity; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import org.springframework.web.multipart.MultipartFile; import java.io.IOException; import java.util.List; @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 = excelService.readUsersFromExcel(file); return ResponseEntity.ok(users); } catch (IOException e) { e.printStackTrace(); return ResponseEntity.badRequest().build(); } } }
3.4 創(chuàng)建HTML上傳頁面
在src/main/resources/templates
目錄下創(chuàng)建upload.html
:
<!DOCTYPE html> <html xmlns:th="http://www.thymeleaf.org"> <head> <meta charset="UTF-8"> <title>Excel上傳</title> <link rel="stylesheet" rel="external nofollow" rel="external nofollow" > </head> <body> <div class="container mt-5"> <div class="card"> <div class="card-header"> <h3>上傳Excel文件</h3> </div> <div class="card-body"> <form id="uploadForm" enctype="multipart/form-data"> <div class="form-group"> <label for="file">選擇Excel文件:</label> <input type="file" class="form-control-file" id="file" name="file" accept=".xls,.xlsx"> </div> <button type="button" class="btn btn-primary" onclick="uploadExcel()">上傳</button> </form> <div class="mt-4"> <h4>上傳結果:</h4> <div id="resultContainer"></div> </div> </div> </div> </div> <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script> <script> function uploadExcel() { var formData = new FormData(document.getElementById('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>姓名</th><th>年齡</th><th>郵箱</th><th>部門</th></tr></thead>' + '<tbody>'; for (var i = 0; i < response.length; i++) { var user = response[i]; resultHtml += '<tr>' + '<td>' + user.id + '</td>' + '<td>' + user.name + '</td>' + '<td>' + user.age + '</td>' + '<td>' + user.email + '</td>' + '<td>' + user.department + '</td>' + '</tr>'; } resultHtml += '</tbody></table>'; $('#resultContainer').html(resultHtml); }, error: function(error) { $('#resultContainer').html('<div class="alert alert-danger">上傳失敗: ' + error.responseText + '</div>'); } }); } </script> </body> </html>
3.5 處理更復雜的Excel結構
在實際應用中,Excel結構可能更復雜,如多個工作表、合并單元格、公式等。以下是處理這些情況的示例:
public List<Department> readComplexExcel(MultipartFile file) throws IOException { List<Department> departments = new ArrayList<>(); try (InputStream inputStream = file.getInputStream()) { Workbook workbook = WorkbookFactory.create(inputStream); // 讀取部門信息(第一個工作表) Sheet departmentSheet = workbook.getSheetAt(0); for (int i = 1; i <= departmentSheet.getLastRowNum(); i++) { Row row = departmentSheet.getRow(i); if (row == null) continue; Department department = new Department(); department.setId((long) row.getCell(0).getNumericCellValue()); department.setName(row.getCell(1).getStringCellValue()); department.setManager(row.getCell(2).getStringCellValue()); department.setEmployees(new ArrayList<>()); departments.add(department); } // 讀取員工信息(第二個工作表) Sheet employeeSheet = workbook.getSheetAt(1); for (int i = 1; i <= employeeSheet.getLastRowNum(); i++) { Row row = employeeSheet.getRow(i); if (row == null) continue; User employee = new User(); employee.setId((long) row.getCell(0).getNumericCellValue()); employee.setName(row.getCell(1).getStringCellValue()); employee.setAge((int) row.getCell(2).getNumericCellValue()); employee.setEmail(row.getCell(3).getStringCellValue()); // 獲取部門ID并關聯(lián)到相應部門 long departmentId = (long) row.getCell(4).getNumericCellValue(); for (Department dept : departments) { if (dept.getId() == departmentId) { dept.getEmployees().add(employee); break; } } } workbook.close(); } return departments; }
4. 使用Apache POI創(chuàng)建和導出Excel文件
4.1 創(chuàng)建基本Excel文件
以下是一個創(chuàng)建簡單Excel文件的示例:
package com.example.excel.service; import com.example.excel.model.User; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.stereotype.Service; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.util.List; @Service public class ExcelExportService { public ByteArrayInputStream exportUsersToExcel(List<User> users) throws IOException { try (Workbook workbook = new XSSFWorkbook()) { // 創(chuàng)建工作表 Sheet sheet = workbook.createSheet("用戶數(shù)據(jù)"); // 創(chuàng)建表頭樣式 Font headerFont = workbook.createFont(); headerFont.setBold(true); headerFont.setColor(IndexedColors.BLUE.getIndex()); CellStyle headerCellStyle = workbook.createCellStyle(); headerCellStyle.setFont(headerFont); headerCellStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex()); headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); headerCellStyle.setBorderBottom(BorderStyle.THIN); headerCellStyle.setBorderTop(BorderStyle.THIN); headerCellStyle.setBorderRight(BorderStyle.THIN); headerCellStyle.setBorderLeft(BorderStyle.THIN); // 創(chuàng)建表頭行 Row headerRow = sheet.createRow(0); // 創(chuàng)建表頭單元格 Cell cell0 = headerRow.createCell(0); cell0.setCellValue("ID"); cell0.setCellStyle(headerCellStyle); Cell cell1 = headerRow.createCell(1); cell1.setCellValue("姓名"); cell1.setCellStyle(headerCellStyle); Cell cell2 = headerRow.createCell(2); cell2.setCellValue("年齡"); cell2.setCellStyle(headerCellStyle); Cell cell3 = headerRow.createCell(3); cell3.setCellValue("郵箱"); cell3.setCellStyle(headerCellStyle); Cell cell4 = headerRow.createCell(4); cell4.setCellValue("部門"); cell4.setCellStyle(headerCellStyle); // 設置數(shù)據(jù)單元格樣式 CellStyle dataCellStyle = workbook.createCellStyle(); dataCellStyle.setBorderBottom(BorderStyle.THIN); dataCellStyle.setBorderTop(BorderStyle.THIN); dataCellStyle.setBorderRight(BorderStyle.THIN); dataCellStyle.setBorderLeft(BorderStyle.THIN); // 創(chuàng)建數(shù)據(jù)行 int rowIdx = 1; for (User user : users) { Row row = sheet.createRow(rowIdx++); Cell idCell = row.createCell(0); idCell.setCellValue(user.getId()); idCell.setCellStyle(dataCellStyle); Cell nameCell = row.createCell(1); nameCell.setCellValue(user.getName()); nameCell.setCellStyle(dataCellStyle); Cell ageCell = row.createCell(2); ageCell.setCellValue(user.getAge()); ageCell.setCellStyle(dataCellStyle); Cell emailCell = row.createCell(3); emailCell.setCellValue(user.getEmail()); emailCell.setCellStyle(dataCellStyle); Cell deptCell = row.createCell(4); deptCell.setCellValue(user.getDepartment()); deptCell.setCellStyle(dataCellStyle); } // 自動調整列寬 for (int i = 0; i < 5; i++) { sheet.autoSizeColumn(i); } // 寫入ByteArrayOutputStream ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); workbook.write(outputStream); return new ByteArrayInputStream(outputStream.toByteArray()); } } }
4.2 創(chuàng)建導出控制器
package com.example.excel.controller; import com.example.excel.model.User; import com.example.excel.service.ExcelExportService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.core.io.InputStreamResource; import org.springframework.http.HttpHeaders; import org.springframework.http.MediaType; import org.springframework.http.ResponseEntity; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.io.ByteArrayInputStream; import java.io.IOException; import java.util.ArrayList; import java.util.List; @RestController @RequestMapping("/api/excel") public class ExcelExportController { @Autowired private ExcelExportService excelExportService; @GetMapping("/export") public ResponseEntity<InputStreamResource> exportUsers() throws IOException { // 生成示例數(shù)據(jù) List<User> users = getTestUsers(); // 生成Excel文件 ByteArrayInputStream in = excelExportService.exportUsersToExcel(users); // 設置HTTP頭 HttpHeaders headers = new HttpHeaders(); headers.add("Content-Disposition", "attachment; filename=users.xlsx"); // 返回Excel文件 return ResponseEntity .ok() .headers(headers) .contentType(MediaType.parseMediaType("application/vnd.ms-excel")) .body(new InputStreamResource(in)); } // 生成測試用戶數(shù)據(jù) private List<User> getTestUsers() { List<User> users = new ArrayList<>(); User user1 = new User(); user1.setId(1L); user1.setName("張三"); user1.setAge(28); user1.setEmail("zhangsan@example.com"); user1.setDepartment("研發(fā)部"); users.add(user1); User user2 = new User(); user2.setId(2L); user2.setName("李四"); user2.setAge(32); user2.setEmail("lisi@example.com"); user2.setDepartment("市場部"); users.add(user2); User user3 = new User(); user3.setId(3L); user3.setName("王五"); user3.setAge(45); user3.setEmail("wangwu@example.com"); user3.setDepartment("行政部"); users.add(user3); User user4 = new User(); user4.setId(4L); user4.setName("趙六"); user4.setAge(36); user4.setEmail("zhaoliu@example.com"); user4.setDepartment("財務部"); users.add(user4); User user5 = new User(); user5.setId(5L); user5.setName("錢七"); user5.setAge(29); user5.setEmail("qianqi@example.com"); user5.setDepartment("人力資源部"); users.add(user5); return users; } }
4.3 創(chuàng)建導出頁面
在src/main/resources/templates
目錄下創(chuàng)建export.html
:
<!DOCTYPE html> <html xmlns:th="http://www.thymeleaf.org"> <head> <meta charset="UTF-8"> <title>Excel導出</title> <link rel="stylesheet" rel="external nofollow" rel="external nofollow" > </head> <body> <div class="container mt-5"> <div class="card"> <div class="card-header"> <h3>導出Excel文件</h3> </div> <div class="card-body"> <p>點擊下面的按鈕導出用戶數(shù)據(jù)到Excel文件:</p> <a href="/api/excel/export" rel="external nofollow" class="btn btn-primary">導出用戶數(shù)據(jù)</a> </div> </div> </div> </body> </html>
4.4 創(chuàng)建復雜的Excel文件
以下是一個創(chuàng)建更復雜Excel文件的示例,包含多個工作表、合并單元格、公式等:
public ByteArrayInputStream exportComplexExcel(List<Department> departments) throws IOException { try (Workbook workbook = new XSSFWorkbook()) { // 創(chuàng)建字體和樣式 Font headerFont = workbook.createFont(); headerFont.setBold(true); headerFont.setFontHeightInPoints((short) 14); CellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFont(headerFont); headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); headerStyle.setAlignment(HorizontalAlignment.CENTER); CellStyle titleStyle = workbook.createCellStyle(); Font titleFont = workbook.createFont(); titleFont.setBold(true); titleFont.setFontHeightInPoints((short) 16); titleStyle.setFont(titleFont); titleStyle.setAlignment(HorizontalAlignment.CENTER); // 創(chuàng)建匯總表 Sheet summarySheet = workbook.createSheet("部門匯總"); // 創(chuàng)建標題行 Row titleRow = summarySheet.createRow(0); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("公司部門人員統(tǒng)計"); titleCell.setCellStyle(titleStyle); // 合并標題單元格 summarySheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); // 創(chuàng)建表頭 Row headerRow = summarySheet.createRow(1); String[] headers = {"部門ID", "部門名稱", "部門經(jīng)理", "員工數(shù)量"}; for (int i = 0; i < headers.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(headers[i]); cell.setCellStyle(headerStyle); } // 填充部門數(shù)據(jù) int rowIdx = 2; int totalEmployees = 0; for (Department dept : departments) { Row row = summarySheet.createRow(rowIdx++); row.createCell(0).setCellValue(dept.getId()); row.createCell(1).setCellValue(dept.getName()); row.createCell(2).setCellValue(dept.getManager()); row.createCell(3).setCellValue(dept.getEmployees().size()); totalEmployees += dept.getEmployees().size(); // 為每個部門創(chuàng)建單獨的工作表 Sheet deptSheet = workbook.createSheet(dept.getName()); // 創(chuàng)建部門表頭 Row deptHeaderRow = deptSheet.createRow(0); Cell deptTitleCell = deptHeaderRow.createCell(0); deptTitleCell.setCellValue(dept.getName() + " - 員工列表"); deptTitleCell.setCellStyle(titleStyle); deptSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4)); // 員工表頭 Row empHeaderRow = deptSheet.createRow(1); String[] empHeaders = {"員工ID", "姓名", "年齡", "郵箱", "入職年限"}; for (int i = 0; i < empHeaders.length; i++) { Cell cell = empHeaderRow.createCell(i); cell.setCellValue(empHeaders[i]); cell.setCellStyle(headerStyle); } // 填充員工數(shù)據(jù) int empRowIdx = 2; for (User emp : dept.getEmployees()) { Row empRow = deptSheet.createRow(empRowIdx++); empRow.createCell(0).setCellValue(emp.getId()); empRow.createCell(1).setCellValue(emp.getName()); empRow.createCell(2).setCellValue(emp.getAge()); empRow.createCell(3).setCellValue(emp.getEmail()); // 使用公式計算入職年限(假設年齡減去25) Cell tenureCell = empRow.createCell(4); tenureCell.setCellFormula("C" + empRowIdx + "-25"); } // 自動調整列寬 for (int i = 0; i < 5; i++) { deptSheet.autoSizeColumn(i); } } // 創(chuàng)建總計行 Row totalRow = summarySheet.createRow(rowIdx); Cell totalLabelCell = totalRow.createCell(0); totalLabelCell.setCellValue("總計"); totalLabelCell.setCellStyle(headerStyle); // 合并總計標簽單元格 summarySheet.addMergedRegion(new CellRangeAddress(rowIdx, rowIdx, 0, 2)); Cell totalValueCell = totalRow.createCell(3); totalValueCell.setCellValue(totalEmployees); totalValueCell.setCellStyle(headerStyle); // 自動調整列寬 for (int i = 0; i < 4; i++) { summarySheet.autoSizeColumn(i); } // 添加圖表 XSSFSheet chartSheet = (XSSFSheet) workbook.createSheet("部門統(tǒng)計圖"); // 復制部門數(shù)據(jù)到圖表數(shù)據(jù)表 Row chartHeaderRow = chartSheet.createRow(0); chartHeaderRow.createCell(0).setCellValue("部門"); chartHeaderRow.createCell(1).setCellValue("員工數(shù)"); int chartRowIdx = 1; for (Department dept : departments) { Row row = chartSheet.createRow(chartRowIdx++); row.createCell(0).setCellValue(dept.getName()); row.createCell(1).setCellValue(dept.getEmployees().size()); } // 創(chuàng)建圖表和數(shù)據(jù)序列 XSSFDrawing drawing = chartSheet.createDrawingPatriarch(); XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 4, 0, 15, 15); XSSFChart chart = drawing.createChart(anchor); chart.setTitleText("部門人員分布"); chart.setTitleOverlay(false); XDDFChartLegend legend = chart.getOrAddLegend(); legend.setPosition(LegendPosition.RIGHT); // X軸和Y軸 XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM); bottomAxis.setTitle("部門"); XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT); leftAxis.setTitle("員工數(shù)"); // 創(chuàng)建數(shù)據(jù)源 XDDFDataSource<String> departments = XDDFDataSourcesFactory.fromStringCellRange( chartSheet, new CellRangeAddress(1, chartRowIdx - 1, 0, 0)); XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange( chartSheet, new CellRangeAddress(1, chartRowIdx - 1, 1, 1)); // 創(chuàng)建柱狀圖 XDDFBarChartData barChart = (XDDFBarChartData) chart.createData( ChartTypes.BAR, bottomAxis, leftAxis); barChart.setVaryColors(true); XDDFBarChartData.Series series = (XDDFBarChartData.Series) barChart.addSeries(departments, values); series.setTitle("員工數(shù)", null); chart.plot(barChart); // 寫入ByteArrayOutputStream ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); workbook.write(outputStream); return new ByteArrayInputStream(outputStream.toByteArray()); } }
注意:上面的圖表代碼需要添加以下依賴:
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-full</artifactId> <version>5.2.3</version> </dependency>
4.5 使用模板導出Excel
在某些場景下,我們需要基于預定義的Excel模板生成文件,以下是一個示例:
public ByteArrayInputStream exportFromTemplate(List<User> users) throws IOException { // 加載模板文件 try (InputStream templateStream = getClass().getResourceAsStream("/templates/user_template.xlsx"); Workbook workbook = WorkbookFactory.create(templateStream)) { Sheet sheet = workbook.getSheetAt(0); // 從第二行開始填充數(shù)據(jù)(第一行是表頭) int rowIdx = 1; for (User user : users) { Row row = sheet.createRow(rowIdx++); row.createCell(0).setCellValue(user.getId()); row.createCell(1).setCellValue(user.getName()); row.createCell(2).setCellValue(user.getAge()); row.createCell(3).setCellValue(user.getEmail()); row.createCell(4).setCellValue(user.getDepartment()); } // 更新模板中的日期單元格(假設在A1位置) Row headerRow = sheet.getRow(0); if (headerRow.getCell(6) != null) { Cell dateCell = headerRow.getCell(6); dateCell.setCellValue(new Date()); } // 自動調整列寬 for (int i = 0; i < 5; i++) { sheet.autoSizeColumn(i); } // 寫入ByteArrayOutputStream ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); workbook.write(outputStream); return new ByteArrayInputStream(outputStream.toByteArray()); } }
5. 使用EasyExcel處理Excel文件
EasyExcel是阿里巴巴開源的基于POI的Excel處理工具,相比原生POI,它提供了更簡潔的API,并且在處理大文件時有明顯的性能優(yōu)勢。
5.1 使用EasyExcel讀取Excel
5.1.1 創(chuàng)建數(shù)據(jù)模型
使用EasyExcel時,通常使用注解來映射Excel列:
package com.example.excel.model; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.format.DateTimeFormat; import lombok.Data; import java.util.Date; @Data public class Employee { @ExcelProperty("員工ID") private Long id; @ExcelProperty("姓名") private String name; @ExcelProperty("年齡") private Integer age; @ExcelProperty("郵箱") private String email; @ExcelProperty("部門") private String department; @ExcelProperty("入職日期") @DateTimeFormat("yyyy-MM-dd") private Date hireDate; @ExcelProperty("薪資") private Double salary; }
5.1.2 創(chuàng)建讀取監(jiān)聽器
EasyExcel采用事件模式讀取Excel,需要創(chuàng)建一個監(jiān)聽器來處理讀取的數(shù)據(jù):
package com.example.excel.listener; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.example.excel.model.Employee; import lombok.extern.slf4j.Slf4j; import java.util.ArrayList; import java.util.List; @Slf4j public class EmployeeReadListener extends AnalysisEventListener<Employee> { /** * 用于暫存讀取的數(shù)據(jù) */ private List<Employee> employeeList = new ArrayList<>(); /** * 每讀取一行數(shù)據(jù)就會調用一次invoke方法 */ @Override public void invoke(Employee employee, AnalysisContext context) { log.info("讀取到一條數(shù)據(jù): {}", employee); employeeList.add(employee); // 達到BATCH_COUNT時,需要存儲一次數(shù)據(jù)庫,防止數(shù)據(jù)幾萬條數(shù)據(jù)在內存,容易OOM if (employeeList.size() >= 5000) { saveData(); // 清理內存 employeeList.clear(); } } /** * 所有數(shù)據(jù)解析完成后調用此方法 */ @Override public void doAfterAllAnalysed(AnalysisContext context) { // 確保最后一批數(shù)據(jù)被保存 saveData(); log.info("所有數(shù)據(jù)解析完成!"); } /** * 保存數(shù)據(jù),這里只是打印,實際應用中可以將數(shù)據(jù)存入數(shù)據(jù)庫 */ private void saveData() { log.info("{}條數(shù)據(jù),開始保存數(shù)據(jù)庫!", employeeList.size()); // 這里可以調用持久層完成數(shù)據(jù)入庫 log.info("存儲數(shù)據(jù)庫成功!"); } /** * 獲取讀取到的數(shù)據(jù) */ public List<Employee> getEmployeeList() { return employeeList; } }
5.1.3 創(chuàng)建Excel讀取服務
package com.example.excel.service; import com.alibaba.excel.EasyExcel; import com.example.excel.listener.EmployeeReadListener; import com.example.excel.model.Employee; import lombok.extern.slf4j.Slf4j; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import java.io.IOException; import java.util.List; @Slf4j @Service public class EasyExcelService { public List<Employee> readEmployeeData(MultipartFile file) throws IOException { EmployeeReadListener listener = new EmployeeReadListener(); EasyExcel.read(file.getInputStream(), Employee.class, listener).sheet().doRead(); return listener.getEmployeeList(); } }
5.1.4 創(chuàng)建Controller
package com.example.excel.controller; import com.example.excel.model.Employee; import com.example.excel.service.EasyExcelService; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.ResponseEntity; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import org.springframework.web.multipart.MultipartFile; import java.io.IOException; import java.util.List; @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 = easyExcelService.readEmployeeData(file); return ResponseEntity.ok(employees); } catch (IOException e) { log.error("Excel讀取失敗", e); return ResponseEntity.badRequest().build(); } } }
5.2 使用EasyExcel導出Excel
5.2.1 簡單導出示例
package com.example.excel.service; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy; import com.example.excel.model.Employee; import org.springframework.stereotype.Service; import java.io.File; import java.io.IOException; import java.io.OutputStream; import java.util.List; @Service public class EasyExcelExportService { /** * 導出員工數(shù)據(jù)到Excel文件 */ public void exportEmployees(List<Employee> employees, OutputStream outputStream) { EasyExcel.write(outputStream, Employee.class) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 自動調整列寬 .sheet("員工數(shù)據(jù)") .doWrite(employees); } /** * 導出員工數(shù)據(jù)到指定文件 */ public void exportEmployeesToFile(List<Employee> employees, String fileName) throws IOException { // 確保目錄存在 File file = new File(fileName); if (!file.getParentFile().exists()) { file.getParentFile().mkdirs(); } EasyExcel.write(fileName, Employee.class) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .sheet("員工數(shù)據(jù)") .doWrite(employees); } /** * 導出多個Sheet的Excel */ public void exportMultipleSheets(List<List<Employee>> departmentEmployees, List<String> sheetNames, OutputStream outputStream) { // 創(chuàng)建ExcelWriter try (var excelWriter = EasyExcel.write(outputStream, Employee.class) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .build()) { // 同一個對象多個Sheet寫入 for (int i = 0; i < departmentEmployees.size(); i++) { // 獲取Sheet名稱 String sheetName = i < sheetNames.size() ? sheetNames.get(i) : "Sheet" + (i + 1); // 創(chuàng)建新的Sheet var writeSheet = EasyExcel.writerSheet(i, sheetName).build(); // 寫入數(shù)據(jù) excelWriter.write(departmentEmployees.get(i), writeSheet); } } } }
5.2.2 創(chuàng)建Controller
package com.example.excel.controller; import com.example.excel.model.Employee; import com.example.excel.service.EasyExcelExportService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.net.URLEncoder; import java.nio.charset.StandardCharsets; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.List; @RestController @RequestMapping("/api/easyexcel") public class EasyExcelExportController { @Autowired private EasyExcelExportService exportService; @GetMapping("/export") public void exportEmployees(HttpServletResponse response) throws IOException { // 設置響應內容 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); // 設置文件名 String fileName = URLEncoder.encode("員工數(shù)據(jù)", StandardCharsets.UTF_8).replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); // 獲取測試數(shù)據(jù) List<Employee> employees = getTestEmployees(); // 導出Excel exportService.exportEmployees(employees, response.getOutputStream()); } @GetMapping("/export-multiple-sheets") public void exportMultipleSheets(HttpServletResponse response) throws IOException { // 設置響應內容 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); // 設置文件名 String fileName = URLEncoder.encode("部門員工數(shù)據(jù)", StandardCharsets.UTF_8).replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); // 獲取測試數(shù)據(jù) - 三個部門的員工 List<List<Employee>> departmentEmployees = new ArrayList<>(); departmentEmployees.add(getEmployeesByDepartment("研發(fā)部")); departmentEmployees.add(getEmployeesByDepartment("市場部")); departmentEmployees.add(getEmployeesByDepartment("行政部")); // Sheet名稱 List<String> sheetNames = Arrays.asList("研發(fā)部員工", "市場部員工", "行政部員工"); // 導出Excel exportService.exportMultipleSheets(departmentEmployees, sheetNames, response.getOutputStream()); } /** * 生成測試員工數(shù)據(jù) */ private List<Employee> getTestEmployees() { List<Employee> employees = new ArrayList<>(); // 添加測試數(shù)據(jù) for (int i = 1; i <= 10; i++) { Employee employee = new Employee(); employee.setId((long) i); employee.setName("員工" + i); employee.setAge(20 + i); employee.setEmail("employee" + i + "@example.com"); employee.setDepartment(i % 3 == 0 ? "研發(fā)部" : (i % 3 == 1 ? "市場部" : "行政部")); employee.setHireDate(new Date()); employee.setSalary(5000.0 + i * 1000); employees.add(employee); } return employees; } /** * 根據(jù)部門獲取員工 */ private List<Employee> getEmployeesByDepartment(String department) { List<Employee> allEmployees = getTestEmployees(); List<Employee> departmentEmployees = new ArrayList<>(); for (Employee employee : allEmployees) { if (department.equals(employee.getDepartment())) { departmentEmployees.add(employee); } } return departmentEmployees; } // ... 5.2.3 使用自定義樣式和復雜表頭 /** * 導出自定義樣式的Excel */ public void exportWithCustomStyle(List<Employee> employees, OutputStream outputStream) { // 設置自定義攔截器來處理樣式 EasyExcel.write(outputStream, Employee.class) // 自動調整列寬 .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 設置表頭樣式 .registerWriteHandler(new AbstractRowHeightStyleStrategy() { @Override protected void setHeadColumnHeight(Row row, int relativeRowIndex) { // 設置表頭行高 row.setHeight((short) 500); } @Override protected void setContentColumnHeight(Row row, int relativeRowIndex) { // 設置內容行高 row.setHeight((short) 400); } }) // 設置單元格樣式 .registerWriteHandler(new CellWriteHandler() { @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { // 設置表頭樣式 if (isHead) { Workbook workbook = writeSheetHolder.getSheet().getWorkbook(); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBold(true); font.setFontHeightInPoints((short) 12); font.setColor(IndexedColors.WHITE.getIndex()); style.setFont(font); style.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); cell.setCellStyle(style); } } @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { // 在這里可以根據(jù)數(shù)據(jù)內容設置樣式 } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { // 內容行的樣式 if (!isHead) { // 偶數(shù)行設置背景色 if (relativeRowIndex % 2 == 0) { Workbook workbook = writeSheetHolder.getSheet().getWorkbook(); CellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); cell.setCellStyle(style); } } } }) .sheet("員工數(shù)據(jù)") .doWrite(employees); } /** * 導出復雜表頭的Excel */ public void exportWithComplexHead(List<Employee> employees, OutputStream outputStream) { // 構建復雜表頭 List<List<String>> head = new ArrayList<>(); // 第一列 ID List<String> head1 = new ArrayList<>(); head1.add("基本信息"); head1.add("員工ID"); head.add(head1); // 第二列 姓名 List<String> head2 = new ArrayList<>(); head2.add("基本信息"); head2.add("姓名"); head.add(head2); // 第三列 年齡 List<String> head3 = new ArrayList<>(); head3.add("基本信息"); head3.add("年齡"); head.add(head3); // 第四列 郵箱 List<String> head4 = new ArrayList<>(); head4.add("聯(lián)系方式"); head4.add("郵箱"); head.add(head4); // 第五列 部門 List<String> head5 = new ArrayList<>(); head5.add("工作信息"); head5.add("部門"); head.add(head5); // 第六列 入職日期 List<String> head6 = new ArrayList<>(); head6.add("工作信息"); head6.add("入職日期"); head.add(head6); // 第七列 薪資 List<String> head7 = new ArrayList<>(); head7.add("薪資信息"); head7.add("月薪(元)"); head.add(head7); // 將數(shù)據(jù)轉為List<List<Object>>格式 List<List<Object>> dataList = new ArrayList<>(); for (Employee employee : employees) { List<Object> data = new ArrayList<>(); data.add(employee.getId()); data.add(employee.getName()); data.add(employee.getAge()); data.add(employee.getEmail()); data.add(employee.getDepartment()); data.add(employee.getHireDate()); data.add(employee.getSalary()); dataList.add(data); } // 寫入Excel EasyExcel.write(outputStream) .head(head) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .sheet("員工數(shù)據(jù)") .doWrite(dataList); } }
6. 處理大型Excel文件的策略
6.1 使用Apache POI SXSSF模式
SXSSF(Streaming Xlsx Writer)是POI提供的流式寫入方式,可以大大減少內存使用:
public void exportLargeExcel(String fileName, int rowCount) throws IOException { try (SXSSFWorkbook workbook = new SXSSFWorkbook(100)) { // 100表示內存中保留的行數(shù) Sheet sheet = workbook.createSheet("大數(shù)據(jù)"); // 創(chuàng)建表頭 Row headerRow = sheet.createRow(0); for (int i = 0; i < 10; i++) { headerRow.createCell(i).setCellValue("列 " + (i + 1)); } // 創(chuàng)建數(shù)據(jù)行 for (int i = 0; i < rowCount; i++) { Row row = sheet.createRow(i + 1); for (int j = 0; j < 10; j++) { row.createCell(j).setCellValue("數(shù)據(jù) " + (i + 1) + "-" + (j + 1)); } // 每生成10000行清理一次臨時文件 if (i % 10000 == 0) { ((SXSSFSheet)sheet).flushRows(); } } // 寫入文件 try (FileOutputStream outputStream = new FileOutputStream(fileName)) { workbook.write(outputStream); } // 清理臨時文件 workbook.dispose(); } }
注意事項:
- 使用完畢后一定要調用
dispose()
方法清理臨時文件 - SXSSF僅支持寫入操作,不支持讀取
- 不支持某些高級特性(如合并單元格等)
6.2 使用EasyExcel處理大文件
EasyExcel在設計上就考慮了大文件處理,采用SAX方式逐行讀取,內存占用?。?/p>
// 讀取大文件 public void readLargeExcel(String fileName) { // 使用SAX方式讀取 EasyExcel.read(fileName, Employee.class, new EmployeeReadListener()) .sheet() .doRead(); } // 寫入大文件 public void writeLargeExcel(String fileName, int batchSize) { // 分批獲取數(shù)據(jù) try (ExcelWriter excelWriter = EasyExcel.write(fileName, Employee.class) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .build()) { // 獲取WriteSheet對象 WriteSheet writeSheet = EasyExcel.writerSheet("員工數(shù)據(jù)").build(); // 模擬分批獲取數(shù)據(jù) int totalCount = 100000; // 總數(shù)據(jù)量 for (int i = 0; i < totalCount; i += batchSize) { // 獲取當前批次數(shù)據(jù) List<Employee> data = getBatchData(i, Math.min(i + batchSize, totalCount)); // 寫入Excel excelWriter.write(data, writeSheet); } } } // 模擬分批獲取數(shù)據(jù) private List<Employee> getBatchData(int start, int end) { List<Employee> list = new ArrayList<>(); for (int i = start; i < end; i++) { Employee employee = new Employee(); employee.setId((long) i); employee.setName("員工" + i); employee.setAge(20 + (i % 20)); employee.setEmail("employee" + i + "@example.com"); employee.setDepartment(i % 3 == 0 ? "研發(fā)部" : (i % 3 == 1 ? "市場部" : "行政部")); employee.setHireDate(new Date()); employee.setSalary(5000.0 + (i % 10) * 1000); list.add(employee); } return list; }
6.3 使用CSV代替Excel
對于極大的數(shù)據(jù)集,考慮使用CSV格式替代Excel:
public void exportToCsv(List<Employee> employees, String fileName) throws IOException { try (FileWriter writer = new FileWriter(fileName); CSVPrinter csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT .withHeader("ID", "姓名", "年齡", "郵箱", "部門", "入職日期", "薪資"))) { for (Employee employee : employees) { csvPrinter.printRecord( employee.getId(), employee.getName(), employee.getAge(), employee.getEmail(), employee.getDepartment(), employee.getHireDate(), employee.getSalary() ); } csvPrinter.flush(); } }
注意:使用CSV需要添加依賴:
<dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-csv</artifactId> <version>1.9.0</version> </dependency>
6.4 分頁導出大型數(shù)據(jù)集
對于Web應用中需要導出的大型數(shù)據(jù)集,可以考慮分頁導出:
@GetMapping("/export/paged") public ResponseEntity<String> exportPaged() { // 生成唯一任務ID String taskId = UUID.randomUUID().toString(); // 啟動異步任務 CompletableFuture.runAsync(() -> { try { // 導出文件路徑 String filePath = "/temp/" + taskId + ".xlsx"; // 分頁查詢數(shù)據(jù)并寫入Excel int pageSize = 1000; int totalPages = getTotalPages(pageSize); try (ExcelWriter excelWriter = EasyExcel.write(filePath, Employee.class) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .build()) { WriteSheet writeSheet = EasyExcel.writerSheet("員工數(shù)據(jù)").build(); // 分頁導出 for (int pageNum = 0; pageNum < totalPages; pageNum++) { // 從數(shù)據(jù)庫分頁查詢數(shù)據(jù) List<Employee> pageData = getPageData(pageNum, pageSize); // 寫入Excel excelWriter.write(pageData, writeSheet); // 更新進度 updateExportProgress(taskId, (pageNum + 1) * 100 / totalPages); } } // 更新導出狀態(tài)為完成 updateExportStatus(taskId, "COMPLETED", filePath); } catch (Exception e) { // 更新導出狀態(tài)為失敗 updateExportStatus(taskId, "FAILED", null); } }); // 返回任務ID return ResponseEntity.ok(taskId); } @GetMapping("/export/status/{taskId}") public ResponseEntity<Map<String, Object>> getExportStatus(@PathVariable String taskId) { // 獲取任務狀態(tài) Map<String, Object> status = getTaskStatus(taskId); return ResponseEntity.ok(status); } @GetMapping("/export/download/{taskId}") public ResponseEntity<Resource> downloadExportedFile(@PathVariable String taskId) { // 獲取導出文件路徑 String filePath = getExportedFilePath(taskId); if (filePath == null) { return ResponseEntity.notFound().build(); } // 創(chuàng)建文件資源 Resource resource = new FileSystemResource(filePath); return ResponseEntity.ok() .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=employee_data.xlsx") .contentType(MediaType.parseMediaType("application/vnd.ms-excel")) .body(resource); }
7. 實際應用場景和最佳實踐
7.1 動態(tài)列導出
在某些業(yè)務場景中,需要根據(jù)用戶選擇動態(tài)確定導出的列:
public ByteArrayInputStream exportDynamicColumns(List<Employee> employees, List<String> selectedColumns) throws IOException { // 定義所有可能的列 Map<String, String> allColumns = new HashMap<>(); allColumns.put("id", "員工ID"); allColumns.put("name", "姓名"); allColumns.put("age", "年齡"); allColumns.put("email", "郵箱"); allColumns.put("department", "部門"); allColumns.put("hireDate", "入職日期"); allColumns.put("salary", "薪資"); try (Workbook workbook = new XSSFWorkbook()) { Sheet sheet = workbook.createSheet("員工數(shù)據(jù)"); // 創(chuàng)建表頭行 Row headerRow = sheet.createRow(0); // 設置表頭樣式 CellStyle headerStyle = workbook.createCellStyle(); Font headerFont = workbook.createFont(); headerFont.setBold(true); headerStyle.setFont(headerFont); // 填充表頭 int colIdx = 0; for (String column : selectedColumns) { if (allColumns.containsKey(column)) { Cell cell = headerRow.createCell(colIdx++); cell.setCellValue(allColumns.get(column)); cell.setCellStyle(headerStyle); } } // 填充數(shù)據(jù) int rowIdx = 1; for (Employee employee : employees) { Row row = sheet.createRow(rowIdx++); colIdx = 0; for (String column : selectedColumns) { Cell cell = row.createCell(colIdx++); // 根據(jù)列名設置單元格值 switch (column) { case "id": cell.setCellValue(employee.getId()); break; case "name": cell.setCellValue(employee.getName()); break; case "age": cell.setCellValue(employee.getAge()); break; case "email": cell.setCellValue(employee.getEmail()); break; case "department": cell.setCellValue(employee.getDepartment()); break; case "hireDate": if (employee.getHireDate() != null) { cell.setCellValue(employee.getHireDate()); // 設置日期格式 CellStyle dateStyle = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd")); cell.setCellStyle(dateStyle); } break; case "salary": cell.setCellValue(employee.getSalary()); break; } } } // 自動調整列寬 for (int i = 0; i < selectedColumns.size(); i++) { sheet.autoSizeColumn(i); } // 輸出 ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); workbook.write(outputStream); return new ByteArrayInputStream(outputStream.toByteArray()); } }
7.2 Excel模板填充
使用Freemarker或其他模板引擎生成Excel:
public ByteArrayInputStream fillTemplate(Map<String, Object> data) throws Exception { // 加載模板 Configuration cfg = new Configuration(Configuration.VERSION_2_3_30); cfg.setClassLoaderForTemplateLoading(getClass().getClassLoader(), "templates"); cfg.setDefaultEncoding("UTF-8"); // 獲取模板 Template template = cfg.getTemplate("excel_template.ftl"); // 輸出目錄 File tempDir = new File(System.getProperty("java.io.tmpdir")); File tempFile = new File(tempDir, "temp_" + System.currentTimeMillis() + ".xlsx"); // 填充模板 try (Writer out = new FileWriter(tempFile)) { template.process(data, out); } // 讀取填充后的文件 try (FileInputStream fis = new FileInputStream(tempFile)) { ByteArrayOutputStream baos = new ByteArrayOutputStream(); byte[] buffer = new byte[1024]; int len; while ((len = fis.read(buffer)) > -1) { baos.write(buffer, 0, len); } baos.flush(); // 刪除臨時文件 tempFile.delete(); return new ByteArrayInputStream(baos.toByteArray()); } }
7.3 Excel文件校驗
在導入Excel文件前進行數(shù)據(jù)校驗:
public class ExcelValidationListener extends AnalysisEventListener<Employee> { private List<Employee> validEmployees = new ArrayList<>(); private List<Map<String, Object>> errorRecords = new ArrayList<>(); private int rowIndex = 1; // 從1開始,0是表頭 @Override public void invoke(Employee employee, AnalysisContext context) { rowIndex++; // 驗證數(shù)據(jù) List<String> errors = validateEmployee(employee); if (errors.isEmpty()) { // 數(shù)據(jù)有效 validEmployees.add(employee); } else { // 記錄錯誤 Map<String, Object> errorRecord = new HashMap<>(); errorRecord.put("rowIndex", rowIndex); errorRecord.put("data", employee); errorRecord.put("errors", errors); errorRecords.add(errorRecord); } } @Override public void doAfterAllAnalysed(AnalysisContext context) { // 處理完成 } // 驗證員工數(shù)據(jù) private List<String> validateEmployee(Employee employee) { List<String> errors = new ArrayList<>(); // 驗證姓名 if (employee.getName() == null || employee.getName().trim().isEmpty()) { errors.add("姓名不能為空"); } // 驗證年齡 if (employee.getAge() == null) { errors.add("年齡不能為空"); } else if (employee.getAge() < 18 || employee.getAge() > 65) { errors.add("年齡必須在18-65歲之間"); } // 驗證郵箱 if (employee.getEmail() != null && !employee.getEmail().isEmpty()) { String emailRegex = "^[a-zA-Z0-9_+&*-]+(?:\\.[a-zA-Z0-9_+&*-]+)*@" + "(?:[a-zA-Z0-9-]+\\.)+[a-zA-Z]{2,7}$"; if (!employee.getEmail().matches(emailRegex)) { errors.add("郵箱格式不正確"); } } // 驗證部門 if (employee.getDepartment() == null || employee.getDepartment().trim().isEmpty()) { errors.add("部門不能為空"); } // 驗證薪資 if (employee.getSalary() != null && employee.getSalary() < 0) { errors.add("薪資不能為負數(shù)"); } return errors; } public List<Employee> getValidEmployees() { return validEmployees; } public List<Map<String, Object>> getErrorRecords() { return errorRecords; } public boolean hasErrors() { return !errorRecords.isEmpty(); } }
7.4 統(tǒng)一異常處理
為Excel處理添加統(tǒng)一的異常處理:
@ControllerAdvice public class ExcelExceptionHandler { private static final Logger logger = LoggerFactory.getLogger(ExcelExceptionHandler.class); @ExceptionHandler(IOException.class) public ResponseEntity<Map<String, String>> handleIOException(IOException e) { logger.error("文件讀寫異常", e); Map<String, String> response = new HashMap<>(); response.put("error", "文件讀寫異常"); response.put("message", e.getMessage()); return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(response); } @ExceptionHandler(IllegalArgumentException.class) public ResponseEntity<Map<String, String>> handleIllegalArgumentException(IllegalArgumentException e) { logger.error("參數(shù)異常", e); Map<String, String> response = new HashMap<>(); response.put("error", "參數(shù)異常"); response.put("message", e.getMessage()); return ResponseEntity.status(HttpStatus.BAD_REQUEST).body(response); } @ExceptionHandler(Exception.class) public ResponseEntity<Map<String, String>> handleGenericException(Exception e) { logger.error("Excel處理異常", e); Map<String, String> response = new HashMap<>(); response.put("error", "Excel處理異常"); response.put("message", e.getMessage()); return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(response); } }
8. 性能優(yōu)化和注意事項
8.1 性能優(yōu)化建議
使用適當?shù)腅xcel庫:
- 小文件可使用Apache POI
- 大文件請使用EasyExcel或POI的SXSSF模式
- 極大文件考慮使用CSV格式
避免一次性加載整個文件:
- 讀取時使用流式解析
- 寫入時使用分批寫入
合理設置緩沖區(qū)大小:
- 在SXSSFWorkbook中設置合理的內存行數(shù)
- 在批處理中選擇合適的批次大小
減少樣式對象:
- 樣式對象重用,而不是為每個單元格創(chuàng)建新樣式
- 限制使用的顏色、字體和邊框樣式數(shù)量
使用異步處理:
- 將大文件處理放在后臺線程中執(zhí)行
- 提供進度反饋機制
8.2 注意事項
內存管理:
- 注意監(jiān)控JVM內存使用情況
- 對于大文件處理,考慮增加JVM堆內存(-Xmx參數(shù))
- 使用完畢后及時關閉資源和清理臨時文件
安全考慮:
- 限制上傳文件大小
- 驗證文件類型和內容
- 防止惡意Excel文件(包含宏或公式)
編碼問題:
- 處理國際字符時,確保使用正確的字符編碼
- 文件名包含中文時,確保正確編碼
并發(fā)控制:
- 大文件處理時注意服務器負載
- 限制并發(fā)處理任務數(shù)量
臨時文件清理:
- 使用SXSSF時,必須調用dispose()方法清理臨時文件
- 定期清理服務器上的臨時文件
總結
Spring Boot提供了強大而靈活的Excel處理能力,通過結合Apache POI和EasyExcel等工具,可以輕松實現(xiàn)Excel文件的讀取、創(chuàng)建和導出功能。在實際應用中,應根據(jù)具體需求和數(shù)據(jù)量選擇合適的處理策略,既要保證功能完整,又要注重性能和資源使用。
無論是簡單的數(shù)據(jù)導出,還是復雜的報表生成,或是大數(shù)據(jù)量的文件處理,都可以通過本文介紹的方法靈活實現(xiàn)。重點是要根據(jù)實際業(yè)務場景,選擇合適的技術方案,并注意性能優(yōu)化和異常處理。
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關文章
java 輸入3個數(shù)a,b,c,按大小順序輸出的實例講解
今天小編就為大家分享一篇java 輸入3個數(shù)a,b,c,按大小順序輸出的實例講解,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2018-07-07Spring Boot配置線程池拒絕策略的場景分析(妥善處理好溢出的任務)
本文通過實例代碼給大家介紹下如何為線程池配置拒絕策略、如何自定義拒絕策略。對Spring Boot配置線程池拒絕策略的相關知識感興趣的朋友一起看看吧2021-09-09使用Java橋接模式打破繼承束縛優(yōu)雅實現(xiàn)多維度變化
這篇文章主要為大家介紹了使用Java橋接模式打破繼承束縛,優(yōu)雅實現(xiàn)多維度變化,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪2023-05-05