Here we will discuss how to automatically generate excel sheet based on any data you have. Following code will automatically fetch that data, generate an excel sheet and put your data in different cells of spreadsheet created.
Excel sheet contains various rows and columns and each row and column have multiple cells in x-axis and y-axis direction.
To implement this functionality, there is need to use PHP library named phpspreadsheet. PHP library is a pre-written set of code which we can call to implement the the pre-defined functionality. phpspreadsheet library is used to create spreadsheet. This library is written in pure PHP and allows you to read and write in various spreadsheet file formats such as in Excel and LibreOffice Calc. This library requires latest PHP version to run.
Let us see steps how to install phpspreadsheet library…
Library can be installed through CLI. To use CLI you should a code editor, such as VS Code. You can download Visual Studio Code editor here: https://ziscom.in/free-softwares/ . Create a folder and Add project folder in Visual Studio Code. Once project folder added you can open new Terminal from main menu in VS Code.
Once Terminal opened in Visual Studio code, install phpspreadsheet using CLI (command line interface) in code editor using following command:
composer require phpoffice/phpspreadsheet
It will install a set of code in the project folder. Above installation adds a folder named ‘vendor’ which have multiple other folders and files. After that we need to create a php file in the main project folder eg. index.php and give path to autoload.php file present in vendor folder, as mentioned below:
require 'vendor/autoload.php';
After that in next lines we can call functionalities using ‘use’ command as mentioned in code below. Next add two more lines of code required to make it work properly as shown below:
use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet();
Now, let us add our data in different cells in spreadsheet. All columns in spreadsheet are named as ‘A’, ‘B’, ‘C’, … and all rows in spreadsheet and named as ‘1’, ‘2’, ‘3’, … . We can point to a cell as ‘A1’, ‘A2’, ‘A3’, … . To add data in each cell, we need to refer to cell’s location and them mention the text data to be added in it, a mentioned below:
$sheet->setCellValue('A1', 'My Data 1');
$sheet->setCellValue('A2', 'My Data 2');
To work on a bigger data set, we can use ‘for’, ‘foreach’, ‘while’, etc. loops to automatically populate data in different cells of the spreadsheet.
Once data is populated as mentioned above, in the end part of file, we need to add some code and name the spreadsheet file as mentioned below:
$writer = new Xlsx($spreadsheet);
$writer->save('ziscom.xlsx');
In above code, we have named file name as ‘ziscom.xlsx’. It will save the spreadsheet in your project folder named ‘ziscom.xlsx’. In case you want to download file in your computer’s download folder, as soon as the file is created by above code, you can download it in download folder by adding proper tags to following code:
meta http-equiv=’refresh’ content=’0;url=ziscom.xlsx’
Add this code on top of the file.