How to insert data with looping in PhpSpreadsheet

Question

I have a problem with my code. When I want to insert values using foreach, but it only return the first data only.

I have tried using fromArray(), but I need my cell to be merge at certain field. When I used the foreach, it just display the first data.

My code is like this:

public function printReport()
    {
        $data = $this->allDokumenDipinjam();

        if ($data) {
            $data = $data->values();

            $spreadsheet = IOFactory::load('format/format-laporan.xlsx');
            $worksheet = $spreadsheet->getActiveSheet();

            foreach ($data as $index => $debitur) {
                $dokumen = $debitur->dokumen->values();
                $dokumenCount = count($dokumen);
                $worksheet->mergeCells("A" . 5 + $index . ":A" . 5 + $dokumenCount - 1);
                $worksheet->getCell('A' . 5 + $index)->setValueExplicit($index + 1);
                $worksheet->mergeCells("B" . 5 + $index . ":B" . 5 + $dokumenCount - 1);
                $worksheet->getCell('B' . 5 + $index)->setValueExplicit($debitur->no_debitur);
                $worksheet->mergeCells("C" . 5 + $index . ":C" . 5 + $dokumenCount - 1);
                $worksheet->getCell('C' . 5 + $index)->setValueExplicit($debitur->nama_debitur);
            }
        }

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('tes.xlsx');

        return response()->download('tes.xlsx')->deleteFileAfterSend(true);
    }

Answer 1

ok @PinKevin, i think it`s better to calculate the new 'row' number before use it, like this:

public function printReport()
    {
        $data = $this->allDokumenDipinjam();

        if ($data) {
            $data = $data->values();

            $spreadsheet = IOFactory::load('format/format-laporan.xlsx');
            $worksheet = $spreadsheet->getActiveSheet();

            foreach ($data as $index => $debitur) {
                $dokumen = $debitur->dokumen->values();
                $dokumenCount = count($dokumen);

                // this is my change
                $row_index = 5 + $index;
                $row_dokumen = $dokumenCount > 0 ? (5 + $dokumenCount - 1) : 5 + $index;

                $worksheet->mergeCells("A" . $row_index . ":A" . $row_dokumen);
                $worksheet->getCell('A' . $row_index)->setValueExplicit($index + 1);
                $worksheet->mergeCells("B" . $row_index . ":B" . $row_dokumen);
                $worksheet->getCell('B' . $row_index)->setValueExplicit($debitur->no_debitur);
                $worksheet->mergeCells("C" . $row_index . ":C" . $row_dokumen);
                $worksheet->getCell('C' . $row_index)->setValueExplicit($debitur->nama_debitur);
            }
        }

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('tes.xlsx');

        return response()->download('tes.xlsx')->deleteFileAfterSend(true);
    }

however your '$dokumenCount' should be '>0'. if it dosn`t work for you pleas explain more about your error and your goal.