PHP and Excel: Spreadsheet Data Handling

In this tutorial, you’ll learn how to efficiently handle spreadsheet data with PHP. We’ll utilize the widely-used PHP library, PhpSpreadsheet, to guide you through the process of reading and writing data in Excel files. By the end of this tutorial, you’ll have the skills to extract information from Excel files, perform data manipulations, and save the modified data into a new Excel file.

Exploring PHP’s DateTime functions can significantly enhance your proficiency in PHP Spreadsheet. Check out the accompanying article about DateTime in PHP—you may also find it interesting.

Setting Up PhpSpreadsheet

To begin your journey with PhpSpreadsheet, you’ll need to install it using Composer. Assuming you have Composer installed, follow these steps within your project directory:

• Open your terminal or command prompt;
• Run the following command:

composer require phpoffice/phpspreadsheet

Executing this command will initiate the installation process, ensuring PhpSpreadsheet and its necessary dependencies are added to your project environment.

Loading Excel Files with PhpSpreadsheet

The first step involves loading an Excel file into our PHP environment. Below, you’ll find a code snippet illustrating how to accomplish this task:

require_once 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

$filename = 'path/to/your/excel/file.xlsx';
$spreadsheet = IOFactory::load($filename);

• In the provided code, make sure to replace ‘path/to/your/excel/file.xlsx’ with the actual file path to your Excel document;
• The IOFactory::load() method efficiently reads the designated Excel file and returns a Spreadsheet object for further manipulation.

Extracting Data from an Excel File

With our Excel file loaded, the next step involves extracting valuable data from it. Utilizing the Spreadsheet object, we can efficiently retrieve data. The following example outlines the process of reading data from an Excel file:

require_once 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

$filename = 'path/to/your/excel/file.xlsx';
$spreadsheet = IOFactory::load($filename);

$worksheet = $spreadsheet->getActiveSheet();
$rows = $worksheet->toArray();

foreach ($rows as $row) {
    echo implode(', ', $row) . PHP_EOL;
}

In the code above, the getActiveSheet() method fetches the active sheet within the Spreadsheet object, and toArray() converts the sheet’s contents into a multidimensional array. Finally, the foreach loop iterates through the rows, allowing you to process and display the data as needed.

Data Export to Excel with PhpSpreadsheet

Now, let’s explore the process of writing data to an Excel file using PhpSpreadsheet. The following example will guide you through the steps to create and populate a new Excel file:

$data = [
    ['Name', 'Age', 'Country'],
    ['John', 25, 'USA'],
    ['Jane', 28, 'UK'],
];

$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();

foreach ($data as $rowNum => $rowData) {
    $worksheet->fromArray($rowData, null, 'A' . ($rowNum + 1));
}

$writer = new Xlsx($spreadsheet);
$writer->save('output.xlsx');

In the provided code, we start by creating a new Spreadsheet object. Next, we define a multidimensional array containing the data you wish to write to the Excel file. The code then iterates through this array and adds the data to the Worksheet object using the fromArray() method. Finally, we create a new Writer object and save the data to a freshly generated Excel file, named ‘output.xlsx’.

Practical Examples of Data Manipulation with PhpSpreadsheet

In this section, we’ll explore practical examples that demonstrate both data reading and writing capabilities using PhpSpreadsheet:

Reading and Summing Values in a Column:

require_once 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

$filename = 'path/to/your/excel/file.xlsx';
$spreadsheet = IOFactory::load($filename);

$worksheet = $spreadsheet->getActiveSheet();
$rows = $worksheet->toArray();

$sum = 0;
foreach ($rows as $row) {
    $sum += $row[1];
}

echo 'Total sum of column B: ' . $sum . PHP_EOL;

Modifying Cell Data and Saving to a New File:

require_once 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$filename = 'path/to/your/excel/file.xlsx';
$spreadsheet = IOFactory::load($filename);

$worksheet = $spreadsheet->getActiveSheet();
$rows = $worksheet->toArray();

foreach ($rows as $rowNum => $rowData) {
    $worksheet->setCellValueByColumnAndRow(2, $rowNum + 1, $rowData[1] * 2);
}

$writer = new Xlsx($spreadsheet);
$writer->save('output.xlsx');

In the previously mentioned illustration, we’ve demonstrated a data manipulation process. Initially, we extracted information from the Excel file and subsequently doubled the values found in column B. Ultimately, the adjusted data was saved into a fresh Excel file named ‘output.xlsx’.

Conclusion

In this comprehensive tutorial, we delved into the techniques for interacting with Excel files using PHP and the PhpSpreadsheet library. We discussed the steps for loading, reading, and writing data within Excel files, providing you with a robust skill set for managing Excel files within your PHP projects.