使用spring将数据从数据库导出到xlsx文件(Excel)

// 数据访问层（DAO）
public interface UserDao {
    List<User> getAllUsers();
}

@Repository
public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public List<User> getAllUsers() {
        String sql = "SELECT * FROM users";
        return jdbcTemplate.query(sql, new UserRowMapper());
    }
}

// 业务逻辑层（Service）
public interface UserService {
    List<User> getAllUsers();
}

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserDao userDao;
    
    public List<User> getAllUsers() {
        return userDao.getAllUsers();
    }
}

// 控制层（Controller）
@RestController
public class UserController {
    @Autowired
    private UserService userService;
    
    @GetMapping("/export")
    public void exportToExcel(HttpServletResponse response) throws IOException {
        List<User> users = userService.getAllUsers();
        
        // 创建Workbook对象
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Users");
        
        // 创建表头
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("ID");
        headerRow.createCell(1).setCellValue("姓名");
        headerRow.createCell(2).setCellValue("年龄");
        
        // 填充数据
        int rowNum = 1;
        for (User user : users) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(user.getId());
            row.createCell(1).setCellValue(user.getName());
            row.createCell(2).setCellValue(user.getAge());
        }
        
        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=\"users.xlsx\"");
        
        // 写入输出流
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    }
}