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?
$num_rows = $objPHPExcel->getActiveSheet()->getHighestRow(); Following this, you can look into inserting a row by using the following statement: $objWorksheet->insertNewRowBefore($num_rows + 1, 1);
I know that this line of code will make the cell text-wrap: $objPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setWrapText(true);
Use composer to install PhpSpreadsheet into your project. Or also download the documentation and samples if you plan to use them. A good way to get started is to run some of the samples. Don't forget to download them via --prefer-source composer flag.
$objPHPExcel->getActiveSheet()->getStyle("F1:G1")->getFont()->setFontSize(16);
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');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With