Drupal 7：使用Views批量操作创建XLS文件及PHPExcel实现疑问

Here's a complete, working implementation of your VBO action to export user data to an XLS file using the PHPExcel module. I’ll fill in the missing parts of your code and add Drupal 7 best practices:

Step 1: Register the Action with hook_action_info() #

First, you need to define your action so it shows up in VBO’s available operations:

function mymodule_action_info() {
  return array(
    'mymodule_export_data_action' => array(
      'type' => 'user', // Swap to 'node' if exporting nodes instead
      'label' => t('Export selected users to XLS'),
      'configurable' => FALSE,
      'triggers' => array('any'),
    ),
  );
}

Step 2: Complete the Export Action Function #

Here’s the full logic for building and delivering the spreadsheet, including batch handling (since VBO runs operations in batches):

function mymodule_export_data_action(&$object, $context = array()) {
  // Track all exported users across batch runs
  static $exported_users = array();
  
  // Grab the user ID from either the passed object or context
  $uid = isset($object->uid) ? $object->uid : (isset($context['uid']) ? $context['uid'] : NULL);
  
  if ($uid) {
    $exported_users[$uid] = user_load($uid); // Load full user object for data
  }

  // Run export logic once when the entire batch finishes
  if (!empty($context['sandbox']['#finished']) && $context['sandbox']['#finished'] == 1) {
    // Load the PHPExcel library
    module_load_include('inc', 'phpexcel');
    
    // Initialize a new spreadsheet
    $objPHPExcel = new PHPExcel();
    
    // Set document metadata
    $objPHPExcel->getProperties()
      ->setCreator(t('Your Drupal Site'))
      ->setLastModifiedBy($GLOBALS['user']->name)
      ->setTitle(t('User Data Export'))
      ->setSubject(t('Exported Users from VBO'));
    
    // Add column headers to the first sheet
    $sheet = $objPHPExcel->setActiveSheetIndex(0);
    $sheet->setCellValue('A1', t('User ID'));
    $sheet->setCellValue('B1', t('Username'));
    $sheet->setCellValue('C1', t('Email'));
    $sheet->setCellValue('D1', t('Account Created'));
    
    // Style headers with bold text
    $header_style = array(
      'font' => array('bold' => TRUE),
    );
    $sheet->getStyle('A1:D1')->applyFromArray($header_style);
    
    // Populate rows with user data
    $row = 2;
    foreach ($exported_users as $user) {
      $sheet->setCellValue('A' . $row, $user->uid);
      $sheet->setCellValue('B' . $row, $user->name);
      $sheet->setCellValue('C' . $row, $user->mail);
      $sheet->setCellValue('D' . $row, format_date($user->created, 'medium'));
      $row++;
    }
    
    // Name the worksheet
    $objPHPExcel->getActiveSheet()->setTitle(t('Exported Users'));
    
    // Generate file path and name
    $filename = 'user-export-' . date('Y-m-d-His') . '.xls';
    $filepath = variable_get('file_public_path', conf_path() . '/files') . '/' . $filename;
    
    // Save the spreadsheet to the public files directory
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save($filepath);
    
    // Create a Drupal file entity (optional but helps with file management)
    $file = new stdClass();
    $file->uri = $filepath;
    $file->filename = $filename;
    $file->filemime = 'application/vnd.ms-excel';
    $file->uid = $GLOBALS['user']->uid;
    $file->status = FILE_STATUS_PERMANENT;
    file_save($file);
    
    // Show success message with download link
    drupal_set_message(t('Successfully exported @count users. Download your file: !link', array(
      '@count' => count($exported_users),
      '!link' => l($filename, file_create_url($filepath)),
    )));
    
    // Clear static variable after export
    unset($exported_users);
  }
}

Important Implementation Tips: #

• Batch Handling: VBO processes items in chunks, so we use a static variable to collect all users before generating the spreadsheet (avoids creating multiple files).
• PHPExcel Setup: Ensure the PHPExcel module is enabled and its required library is installed (the module should handle this if you installed it via Drupal’s module manager).
• Customization: Add more columns (like user roles, profile fields) by extending the header rows and data population loop.
• File Cleanup: Add a cron job to delete old export files after a set period to prevent cluttering your files directory.
• Permissions: Make sure the user running the action has access to view user data and create files in the public directory.

How to Use: #

1. Enable the Views Bulk Operations and PHPExcel modules.
2. Create/edit a View of users, add the VBO field to it.
3. In the VBO field settings, select your new "Export selected users to XLS" action.
4. Save the View, select users from the list, and run the action to generate your XLS file.

内容的提问来源于stack exchange，提问作者Mohsine