如何在PHPExcel中合并具有相同值的行，使用if逻辑进行数组循环

问题描述

我需要phpexcel中的循环逻辑帮助，该逻辑可以合并具有相同事务ID的行

I need help for the looping logic in phpexcel, that can merge row with same transaction id

这是示例代码

foreach ($transaction as $key => $value) {
     // I need if condition for $merge (ex. N4:N5)
     $excel->getActiveSheet()->mergeCells($merge);
}

我尝试了一些逻辑，将上次循环的交易ID与当前交易ID进行比较.但是我的逻辑仍然很糟糕，无法正常工作.

i have tried some logic that compare last looping transaction id, with current transaction id. but my logic is still bad and doesnt work well.

我想要这样的结果

推荐答案

以下解决方案是使用 PHPSpreadSheet ，PHPExcel的升级.但是，您应该能够将方法调整为PHPExcel，因为逻辑应该相同.

The following solution was developed using PHPSpreadSheet, an upgrade of PHPExcel. However, you should be able to adjust the method to PHPExcel as the logic should be the same.

主要逻辑是(对于每个循环):

The main logic is (for every loop):

• 如果代码为空，请检查或标记当前发票代码
• 设置下一个发票代码，从下一行的值中获取
• 根据以下条件渲染合并:
  • 当前行是否等于或大于发票代码行的开头
  • 如果上一个发票代码与下一个发票代码不同(或者下一个发票代码为空(此标记记录的结尾))
  • Check or mark current invoice code if code is empty
  • Set next invoice code, get it from next row's value
  • render merge based of the following condition:
    • is current row is equal or greater than start of invoice code's row
    • if prev invoice code is different than next invoice code (or if next invoice code is null (this mark the end of record))

    最后，使用 mergeCells()进行渲染合并方法:

    And finally, render merge using mergeCells() method:

    $spreadsheet->getActiveSheet()->mergeCells($cellToMerge);
    

  完整的示例代码:

  <?php
  require_once('vendor/autoload.php');
  
  use PhpOffice\PhpSpreadsheet\Spreadsheet;
  use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
  
  $path = dirname(__FILE__);
  
  $data = array(
      array(1, 'INV201806001', '1000'),
      array(2, 'INV201806001', '1000'),
      array(3, 'INV201806002', '0'),
      array(4, 'INV201807001', '1000'),
      array(5, 'INV201807001', '1000'),
      array(6, 'INV201807001', '1000'),
      array(7, 'INV201807002', '0'),
      array(8, 'INV201807002', '0'),
  );
  
  $spreadsheet = new Spreadsheet();
  $sheet = $spreadsheet->getActiveSheet();
  
  $row = 3;
  $startRow = -1;
  $previousKey = '';
  foreach($data as $index => $value){
      if($startRow == -1){
          $startRow = $row;
          $previousKey = $value[1];
      }
      $sheet->setCellValue('C' . $row, $value[0]);
      $sheet->setCellValue('D' . $row, $value[1]);
      $sheet->setCellValue('E' . $row, $value[2]);
      $nextKey = isset($data[$index+1]) ? $data[$index+1][1] : null;
  
      if($row >= $startRow && (($previousKey <> $nextKey) || ($nextKey == null))){
          $cellToMerge = 'E'.$startRow.':E'.$row;
          $spreadsheet->getActiveSheet()->mergeCells($cellToMerge);
          $startRow = -1;
      }
  
      $row++;
  }
  
  $writer = new Xlsx($spreadsheet);
  $writer->save('test-xls-merge.xlsx');
  

  这是上面代码的屏幕截图:

  Here's the screenshot of codes above:

  这篇关于如何在PHPExcel中合并具有相同值的行，使用if逻辑进行数组循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！