Exporting Data to Excel

Frontend Export Trigger

// Export button component
<el-button type="primary" @click="exportData()">Export Data</el-button>

// Export method implementation
exportData() {
  window.location.href = 'http://localhost:8080/api/users/export';
}

Required Maven Dependencies

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>4.1.2</version>
</dependency>

Backend Export Endpoint

@GetMapping("/export")
public void exportUsers(HttpServletResponse response) throws IOException {
  // Retrieve all user records from database
  List<User> userList = userService.getAllUsers();
  
  // Prepare data structure for Excel generation
  List<Map<String, Object>> excelData = new ArrayList<>();
  
  // Transform user objects into key-value pairs
  for (User user : userList) {
    Map<String, Object> rowData = new HashMap<>();
    rowData.put("Full Name", user.getName());
    rowData.put("Contact Number", user.getPhone());
    rowData.put("User Category", user.getType());
    excelData.add(rowData);
  }
  
  // Generate Excel workbook
  ExcelWriter excelWriter = ExcelUtil.getWriter(true);
  excelWriter.write(excelData, true);
  
  // Configure response headers for file download
  response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
  response.setHeader("Content-Disposition", "attachment;filename=users.xlsx");
  
  // Stream Excel file to client
  ServletOutputStream output = response.getOutputStream();
  excelWriter.flush(output, true);
  excelWriter.close();
  IoUtil.close(System.out);
}

Importing Data from Excel

Frontedn Upload Component

<!-- File upload component for importing data -->
<el-upload 
  action="http://localhost:8080/api/users/import" 
  :show-file-list="false" 
  :on-success="handleImportSuccess"
  style="display:inline-block;">
  <el-button type="primary">Import Data</el-button>
</el-upload>

// Success callback handler
handleImportSuccess(result) {
  if(result.code === '0') {
    this.$message.success('Data imported successfully');
    this.loadUserData();
  } else {
    this.$message.error(result.message);
  }
}

Entity Class Configurasion

// Field annotations for mapping Excel columns
@Alias("Full Name")
private String name;

@Alias("Account Name")
private String username;

Back end Import Endpoint

@PostMapping("/import")
public Result importUsers(@RequestParam("file") MultipartFile uploadedFile) throws IOException {
  // Parse Excel file contents
  List<User> importedUsers = ExcelUtil.getReader(uploadedFile.getInputStream())
                                    .readAll(User.class);
  
  // Process each imported record
  if (!CollectionUtil.isEmpty(importedUsers)) {
    for (User userData : importedUsers) {
      try {
        userService.createUser(userData);
      } catch (Exception exception) {
        exception.printStackTrace();
      }
    }
  }
  
  return Result.success();
}