Java SpringBoot使用EasyExcel导入导出Excel文件(源代码)

package com.yyqq.exceldemo.controller; import com.alibaba.excel.EasyExcel; import com.yyqq.exceldemo.model.UserData; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.springframework.http.HttpHeaders; import org.springframework.http.MediaType; import org.springframework.http.ResponseEntity; import org.springframework.web.bind.annotation.*; import org.springframework.web.multipart.MultipartFile; import java.io.ByteArrayOutputStream; import java.io.InputStream; import java.util.ArrayList; import java.util.Iterator; import java.util.List; @RestController public class ExcelController { /** * 导入execl文件 */ @GetMapping("/export") public ResponseEntity<byte[]> exportExcel() { String fileName = "用户数据.xlsx"; List<UserData> userList = generateUserData(); // EasyExcel 写入到字节数组 byte[] bytes = exportToExcel(userList); // 设置响应头 HttpHeaders headers = new HttpHeaders(); headers.setContentType(MediaType.APPLICATION_OCTET_STREAM); headers.setContentDispositionFormData("attachment", fileName); return ResponseEntity.ok() .headers(headers) .body(bytes); } @PostMapping("/import") public String importExcel(@RequestParam("file") MultipartFile file) { try { InputStream inputStream = file.getInputStream(); // 使用 EasyExcel 读取 Excel 数据 List<UserData> userList = EasyExcel.read(inputStream) .head(UserData.class) .sheet() .doReadSync(); // 处理导入的数据，例如保存到数据库 processUserData(userList); return "导入成功，共导入 " + userList.size() + " 条数据"; } catch (Exception e) { e.printStackTrace(); return "导入失败: " + e.getMessage(); } } /** * 模拟生产用户数据 * @return */ private List<UserData> generateUserData() { List<UserData> list = new ArrayList<>(); for (int i = 1; i <= 100; i++) { UserData user = new UserData(); user.setId(i); user.setName("用户" + i); user.setAge(20 + i % 30); user.setEmail("user" + i + "@example.com"); list.add(user); } return list; } /** * 将数据导出至byte数组中 */ private byte[] exportToExcel(List<UserData> userList) { // 使用 EasyExcel 写入 Excel 到字节数组 ByteArrayOutputStream out = new ByteArrayOutputStream(); EasyExcel.write(out, UserData.class) .sheet("用户数据") .doWrite(userList); return out.toByteArray(); } /** * 导入数据方法 */ private List<UserData> importFromExcelWithPOI(MultipartFile file) { List<UserData> userList = new ArrayList<>(); try (InputStream inputStream = file.getInputStream(); Workbook workbook = WorkbookFactory.create(inputStream)) { Sheet sheet = workbook.getSheetAt(0); Iterator<Row> iterator = sheet.iterator(); // 跳过表头 if (iterator.hasNext()) { iterator.next(); } while (iterator.hasNext()) { Row row = iterator.next(); UserData user = new UserData(); user.setId((int) row.getCell(0).getNumericCellValue()); user.setName(row.getCell(1).getStringCellValue()); user.setAge((int) row.getCell(2).getNumericCellValue()); user.setEmail(row.getCell(3).getStringCellValue()); userList.add(user); } } catch (Exception e) { e.printStackTrace(); } return userList; } private void processUserData(List<UserData> userList) { // 这里可以添加将数据保存到数据库的逻辑 // 例如使用 JPA 或 MyBatis 等持久层框架 userList.forEach(user -> { // 模拟保存操作 System.out.println("保存用户: " + user); }); } }