How to merge row with same value in PHPExcel, array looping with if logic

Question

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

ariefbayu · Accepted Answer · 2018-07-12 06:50:03Z

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:

Collectives™ on Stack Overflow

How to merge row with same value in PHPExcel, array looping with if logic

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related