With the help of the xls_serialization module views_data_export can write files for Excel.

With the patch in #2789531: Support for batch operations we have batch support.

But using both together results in garbled files because xls_serialization doesn't support batch writing.

See #2789531-86: Support for batch operations

CommentFileSizeAuthor
#5 2945252-5.patch2.39 KBtavib47
#3 interdiff-batch_support_excel-2945252-3.patch1.38 KBmiiimooo
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

miiimooo created an issue. See original summary.

miiimooo’s picture

miiimooo
Location Europe
CreditAttribution: miiimooo as a volunteer commented
Issue summary: View changes
miiimooo’s picture

miiimooo
Location Europe
CreditAttribution: miiimooo as a volunteer commented
Status: Active » Needs work
FileSize
interdiff-batch_support_excel-2945252-3.patch1.38 KB

This is my first shot at a rather brutal hack of this.

This requires first patching with the patch from #2789531-106: Support for batch operations

How to use: set your encoder to CSV and your output file name to something ending with xls(x)

Note, that instead of encoding on each batch run, this does the encoding as XLS at the end of the batch run. If that takes very long it could still hit some timeout.

Ultimately, the integration with the encoders needs changing for this to work in a more modular way.

riskogab’s picture

riskogab CreditAttribution: riskogab commented

Hi, I made some modifications in /src/Plugin/views/display/DataExport.php in processBatch function.
The XLS Serializer return the XLS file content what I save to a tempfile, and after it I merge the 2 XLS files.

Put this code after XML workaround:

    
    // Process XLS batch
    if ($context['sandbox']['progress'] != 0 && reset($view->getStyle()->options['formats']) == 'xls') {
      $tmpfilename = tempnam($tmp_dir, 'phpxlstmp_');
      $tmpfile = fopen($tmpfilename, 'w');
      fwrite($tmpfile, $string);
      fclose($tmpfile);

      $objPHPExcel1 = \PHPExcel_IOFactory::load($context['sandbox']['vde_file']);
      $objPHPExcel2 = \PHPExcel_IOFactory::load($tmpfilename);
      
      // Remove the first row (header)
      $objPHPExcel2->getActiveSheet()->removeRow(1,1);
      
      $objPHPExcel1->getActiveSheet()->fromArray(
          $objPHPExcel2->getActiveSheet()->toArray(),
          null,
          'A' . ($objPHPExcel1->getActiveSheet()->getHighestRow() + 1)
      );
      
      $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel1, $view->getStyle()->options['xls_settings']['xls_format']);
      $objWriter->save($context['sandbox']['vde_file']);

      unlink($tmpfile);
    }

and modify this line:

// Write rendered rows to output file.
if (file_put_contents($context['sandbox']['vde_file'], $string, FILE_APPEND) === FALSE) {

to this (elseif instead of if):

// Write rendered rows to output file.
elseif (file_put_contents($context['sandbox']['vde_file'], $string, FILE_APPEND) === FALSE) {
tavib47’s picture

tavib47 CreditAttribution: tavib47 as a volunteer and at Eau de Web commented
FileSize
2945252-5.patch2.39 KB

Hi,

I think riskogab's idea is better: we should manage xls output within processBatch method, as for XML.

vensires’s picture

vensires CreditAttribution: vensires at E-Sepia Web Innovation commented
Status: Needs work » Closed (outdated)

None of the patches is getting applied nowadays and from checking the code, it seems they are already implemented.

Could you please check and reopen if I'm missing a point here?