How to Combine Excel Files Using PHP

Introduction

Working with data often involves dealing with multiple Excel files. Whether it's consolidating financial reports, combining customer data, or aggregating research findings, merging these files into one can save a considerable amount of time and effort. With PHP and the PhpSpreadsheet library, this process can be automated efficiently. This tutorial will guide you through the steps to combine multiple Excel files into a single file using PHP.

Prerequisites

Before starting, ensure you have:

1. PHP: A basic setup of PHP on your machine.
2. Composer: The PHP dependency manager, for installing PhpSpreadsheet.

Step 1: Install PhpSpreadsheet

PhpSpreadsheet is a powerful library for reading and writing spreadsheet files in PHP. To install it, navigate to your project directory in the terminal and run:

composer require phpoffice/phpspreadsheet

Step 2: Prepare Your PHP Script

Create a new PHP file in your project directory, for example, combine_excel.php. Open this file in your editor and include the PhpSpreadsheet classes you'll be using:

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

Step 3: Locate and Verify Excel Files

Start by specifying the directory containing the Excel files you want to combine. Then, ensure your script can access and list these files:

$directory = './combine';

echo 'Absolute directory path: ' . realpath($directory) . PHP_EOL;
echo 'Files in directory: ';
print_r(scandir($directory));

Step 4: Combine the Excel Files

Now, let's read the data from each Excel file in the specified directory and merge it into a single dataset. You'll loop through the files, ignoring non-Excel files, and compile the data into an array:

$allData = [];

echo 'Combining process has started.' . PHP_EOL;

foreach (scandir($directory) as $filename) {
    if (strpos($filename, '.xlsx') !== false) {
        echo 'Reading file ' . $filename . PHP_EOL;
        $filePath = $directory . '/' . $filename;
        try {
            $spreadsheet = IOFactory::load($filePath);
            $data = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
            $allData = array_merge($allData, $data);
        } catch (Exception $e) {
            echo 'Error reading file ' . $filename . ': ' . $e->getMessage() . PHP_EOL;
        }
    }
}

Step 5: Create and Save the Combined Excel File

After compiling the data from each file, create a new Excel file and populate it with the combined data. Finally, save this new file:

if (!empty($allData)) {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $rowIndex = 1;
    foreach ($allData as $row) {
        $colIndex = 'A';
        foreach ($row as $cellValue) {
            $sheet->setCellValue($colIndex++ . $rowIndex, $cellValue);
        }
        $rowIndex++;
    }
    $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('combined.xlsx');
    echo 'All files have been combined into combined.xlsx.' . PHP_EOL;
} else {
    echo 'No XLSX files were combined.' . PHP_EOL;
}

Conclusion

You've now automated the process of combining multiple Excel files into one using PHP. This script can be a significant time-saver, especially when working with large datasets distributed across multiple files. Remember, the PhpSpreadsheet library offers a wealth of functionality for manipulating spreadsheet data, so feel free to explore and extend this script to fit your specific needs.

Key Takeaways

• installing PhpSpreadsheet package
• reading and writing excel files using PhpSpreadsheet
• combining many excel files into one file