How to merge row with same value in PHPExcel, arra

问题:

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

here's the example code

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

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.

I want the result like this

回答1:

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))

• And finally, render merge using mergeCells() method:

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

Full example code:

<?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: