Generate Excel sheet for your data

In the world of web development and data manipulation, a reliable and feature-rich spreadsheet library is an invaluable tool. For PHP developers, PhpSpreadsheet has emerged as a game-changer, replacing the now-deprecated PHPExcel library. With its robust features, versatility, and active community support, PhpSpreadsheet has become the go-to choice for handling spreadsheet-related tasks in PHP applications. In this essay, we will delve into the world of PhpSpreadsheet, exploring its history, features, use cases, and the reasons behind its growing popularity.

A Brief History

Before we dive into the depths of PhpSpreadsheet, it’s essential to understand its roots. PhpSpreadsheet is the successor to PHPExcel, an older library that served as a reliable tool for reading and writing spreadsheet files in various formats. PHPExcel was widely used and appreciated by developers for its capabilities. However, as technology evolved, it became clear that PHPExcel had its limitations and needed to be replaced.

PhpSpreadsheet was born as an open-source project to address these limitations and provide a more modern and robust solution for spreadsheet manipulation in PHP applications. Developed by a team of dedicated volunteers and contributors, it quickly gained traction within the PHP community and became the de facto choice for working with spreadsheets.

Key Features

1. Support for Multiple File Formats: PhpSpreadsheet supports various file formats, including Excel (XLSX, XLS), CSV, PDF, and HTML, making it versatile for both reading and writing spreadsheet data in different scenarios.
2. Rich Data Manipulation: It offers a wide range of features for data manipulation, such as cell merging, formatting, and validation. You can easily set cell values, apply conditional formatting, and create formulas to perform complex calculations.
3. Charts and Graphs: PhpSpreadsheet allows you to create charts and graphs directly within your spreadsheet, enhancing the visual representation of your data. This feature is especially valuable for data analysis and reporting.
4. Rich Text and Styling: You can apply various styles to cells, including fonts, colors, borders, and alignment. Additionally, it supports rich text formatting, enabling you to create visually appealing spreadsheets.
5. Data Import and Export: Importing data from existing spreadsheet files and exporting data to different formats is a breeze with PhpSpreadsheet. This functionality is vital for seamless integration with external data sources.
6. Formulas and Functions: PhpSpreadsheet supports a wide range of Excel-compatible functions and formulas, allowing you to perform advanced calculations and data analysis directly within your PHP application.
7. Performance Optimization: The library has been optimized for performance, making it suitable for processing large datasets efficiently. Caching and lazy-loading mechanisms help reduce memory consumption and enhance overall performance.
8. Integration with Third-Party Libraries: PhpSpreadsheet integrates seamlessly with other PHP libraries and frameworks, making it a valuable addition to any PHP project.

Use Cases

1. Data Import and Export: One of the most common use cases for PhpSpreadsheet is importing data from external sources into a PHP application and exporting data from the application to spreadsheet formats. This functionality is particularly useful in scenarios where data needs to be exchanged with clients, partners, or other systems.
2. Report Generation: Businesses often require automated report generation for various purposes, such as financial reports, sales summaries, or inventory tracking. PhpSpreadsheet’s ability to create richly formatted and data-driven reports makes it an ideal choice for such applications.
3. Data Analysis and Visualization: Researchers and analysts can benefit from PhpSpreadsheet’s charting and graphing capabilities. It enables them to process and visualize data directly within their PHP applications, eliminating the need for external tools or software.
4. Invoice and Order Processing: E-commerce platforms and accounting software frequently use PhpSpreadsheet to generate invoices, purchase orders, and other financial documents. The library’s support for rich text and styling allows for the creation of professional-looking documents.
5. Data Validation and Quality Control: Data quality is essential in many applications. PhpSpreadsheet’s features for data validation and manipulation help ensure that data entered into spreadsheets is accurate and meets predefined criteria.
6. Educational Resources: Educational platforms often use PhpSpreadsheet to provide interactive lessons and exercises involving spreadsheets. Students can practice formulas, charts, and various spreadsheet functions in a controlled environment.
7. Inventory Management: Businesses and warehouses use PhpSpreadsheet to manage their inventory, track stock levels, and generate inventory reports. The library’s ability to handle large datasets efficiently is crucial in this context.
8. Data Migration: During data migration projects, PhpSpreadsheet can be used to convert data from one format to another, ensuring a smooth transition between systems.

How to generate Spreadsheet by PHP code:

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. To run following code correctly PHP version 8.1 is required.

Once Terminal opened in Visual Studio code, install phpspreadsheet using CLI (command line interface) in code editor using following command:

composer require phpoffice/phpspreadsheet

If above code do not work, follow these commands:

composer require "ext-gd:*" --ignore-platform-reqs
composer require "ext-fileinfo:*" --ignore-platform-reqs
composer require phpoffice/phpspreadsheet --ignore-platform-reqs

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.

In case you want to append excel file, replace $spreadsheet = new Spreadsheet(); with

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);

and add dynamic row number to cell through variable $num:

$sheet->setCellValue('A'.$num, $data);

and replace last two lines with:

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save($inputFileName);

It can be further made dynamic with dynamic page number saved in files, dynamic rows saved in file, dynamic data coming repeatedly from different pages as array and added at next row saved in file.
Contact here.