Row number must be greater than written rows

[SWAT10101]

Hello Everyone,

I facing the issue after using writeRow to fill A B C and D columns I want to write the total in the empty area F3:H6 so I use makeArea to select the area I want work with but I got error row number must be greater than written rows Is there any way to overwrite these rows without changing the row data?

[aVadim483]

There are two ways to write to a file - writing row by row and direct writing. In the first case, the data is buffered by only one row, as soon as the row is written, the cursor moves to the next row and writing to a row with a lower number is not possible. But this method gives high speed and saves memory.

But you can define wtiting area and write directly to specified cellss. This method is not recommended for large amounts of data because it is slower and requires much more memory.

Your problem can be solved in two ways:

1. If the number of rows is large or exactly unknown, then use formulas

   
   $excel = Excel::create();
   $sheet = $excel->getSheet();

$data = [ ['log' => 1, 'amount' => 100], ['log' => 2, 'amount' => 200], ['log' => 3, 'amount' => 300], ['log' => 4, 'amount' => 400], ['log' => 5, 'amount' => 500], ['log' => 6, 'amount' => 600], ['log' => 7, 'amount' => 700], ['log' => 8, 'amount' => 800], ['log' => 9, 'amount' => 900], ];

foreach ($data as $cnt => $row) { if ($cnt === 2) { $row[] = null; $row[] = null; $row[] = 'SUM'; $row[] = '=SUM(B:B)'; } elseif ($cnt === 3) { $row[] = null; $row[] = null; $row[] = 'AVG'; $row[] = '=AVERAGE(B:B)'; } $sheet->writeRow($row); }

$excel->save($outFileName);

2. If the amount of recorded data is not very large, then use two areas for direct writing
```php
$excel = Excel::create();
$sheet = $excel->getSheet();

$data = [
    ['log' => 1, 'amount' => 100],
    ['log' => 2, 'amount' => 200],
    ['log' => 3, 'amount' => 300],
    ['log' => 4, 'amount' => 400],
    ['log' => 5, 'amount' => 500],
    ['log' => 6, 'amount' => 600],
    ['log' => 7, 'amount' => 700],
    ['log' => 8, 'amount' => 800],
    ['log' => 9, 'amount' => 900],
];

// make area within the given boundaries
$areaTotal = $sheet->makeArea('E3:F4');
// open area without bottom boundary
$areaData = $sheet->beginArea('A1');

foreach ($data as $cnt => $row) {
    $numRow = $cnt + 1;
    $areaData->setValue('A' . $numRow, $row['log']);
    $areaData->setValue('B' . $numRow, $row['amount']);
}

$areaTotal->setValue('E3', 'SUM');
$areaTotal->setValue('F3', 4500);
$areaTotal->setValue('E4', 'AVG');
$areaTotal->setValue('F4', 500);

// Close and write all areas
$sheet->writeAreas();

$excel->save($outFileName);

[SWAT10101]

Thank you, yes the number of rows is large and unknown but the second method is working for me