How to add new row on Excel using PHPSpreadsheet

Question

Hi I'm new to this library called PHPSpreadsheet. I tried reading it's docs but I can't understand it.

I want to insert a new row on an existing Excel File and here is what I have so far:

<?php 

require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$inputFileName = 'Excel/hello.xlsx';
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);

$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Updated');

$writer = new Xlsx($spreadsheet);
$writer->save('../controller/excel/hello.xlsx');

?>

This inserts new data on the 'hello.xsls' file replacing the cell's previous data. How can I make it write data into a new row?

Nigel Ren · Accepted Answer · 2019-10-03T06:33:47.417

14

To create a new row, you need to call insertNewRowBefore() with the row number you want to insert before...

$sheet = $spreadsheet->getActiveSheet();
$sheet->insertNewRowBefore(1);
$sheet->setCellValue('A1', 'Updated');

You can also call it with a number of rows to insert, the default is 1.

If you want to append a row, you can call getHighestRow() to find the last row and add 1 to it for the new row. Also change the hard coding of the column in the setCellValue() call to use this row as well...

$sheet = $spreadsheet->getActiveSheet();
$row = $sheet->getHighestRow()+1;
$sheet->insertNewRowBefore($row);
$sheet->setCellValue('A'.$row, 'Updated');

edited Oct 03 '19 at 06:33

answered Oct 03 '19 at 06:25

Nigel Ren

56,122
11
43
55

1

Thanks! I'll Check it. Is there any way to insert new row after rather than before? and if yes, how can I get the last row number to use it as a parameter ? – jpneey Oct 03 '19 at 06:28
2

Updated answer. – Nigel Ren Oct 03 '19 at 06:33
1

Thanks! I've learned something again. This answers my question! Thank you! – jpneey Oct 03 '19 at 07:52

score 0 · Answer 2 · answered Oct 03 '19 at 06:21

0

Hello I think this issue came because of memory exhausted so increase your memory limit by php.ini file

like memory_limit = 256M

After increase memory limit , restart your apache

answered Oct 03 '19 at 06:21

ankit singh

565
3
8

score 0 · Answer 3 · answered Sep 11 '22 at 10:47

0

The accepted answer throws exception: "Column references should not be numeric."

For me works:

...
$row = 'A' . ($sheet->getHighestRow() +1);
$sheet->insertNewColumnBefore($row);
$sheet->setCellValue($row, 'Hello World again!');
...

answered Sep 11 '22 at 10:47

rammi22

271
1
11

How to add new row on Excel using PHPSpreadsheet

3 Answers3

Linked