How to Combine Excel Files Using PHP

How to Combine Excel Files Using PHP

Last Updated on May 06, 2024


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.


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:


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: ';

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);
    $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
    echo 'All files have been combined into combined.xlsx.' . PHP_EOL;
} else {
    echo 'No XLSX files were combined.' . PHP_EOL;


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

Category: programming

Tags: #php #tips and tricks #edited by chatgpt

Join the Newsletter

Subscribe to get my latest content by email.

I won't send you spam. Unsubscribe at any time.

Related Posts