How to Split an Excel File into Multiple Files Using PHP

Introduction

If you've ever been overwhelmed by an excessively large Excel file, you're not alone. Large files can be cumbersome to work with, challenging to share, and slow to open. Fortunately, PHP and the PhpSpreadsheet library offer a powerful solution for splitting a single Excel file into manageable, smaller files. This guide will walk you through automating this process, making your data more accessible and easier to handle.

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 split_excel.php file in your project directory. Open it in your code editor and start by importing the necessary classes from PhpSpreadsheet:

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Reader\Xlsx as Reader;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as Writer;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

Step 3: Define the Split Function

Define a function called splitExcelFile that takes the path to an Excel file and the desired number of rows per split file as arguments:

function splitExcelFile($filePath, $rowsPerFile = 100) {
    $reader = new Reader();
    $spreadsheet = $reader->load($filePath);
    $worksheet = $spreadsheet->getActiveSheet();

    $highestRow = $worksheet->getHighestRow();
    $filesNeeded = ceil($highestRow / $rowsPerFile);

    $originalFileName = pathinfo($filePath, PATHINFO_FILENAME);
    $outputDir = $originalFileName . "_split";
    if (!is_dir($outputDir)) {
        mkdir($outputDir, 0777, true);
    }

    for ($fileIndex = 0; $fileIndex < $filesNeeded; $fileIndex++) {
        $newSpreadsheet = new Spreadsheet();
        $newSheet = $newSpreadsheet->getActiveSheet();

        $rowOffset = $fileIndex * $rowsPerFile;
        $rowLimit = min($rowsPerFile, $highestRow - $rowOffset);

        for ($row = 0; $row < $rowLimit; $row++) {
            $rowData = $worksheet->rangeToArray('A' . ($row + 1 + $rowOffset) . ':' . $worksheet->getHighestColumn() . ($row + 1 + $rowOffset), NULL, TRUE, FALSE)[0];
            $newSheet->fromArray($rowData, NULL, 'A' . ($row + 1));
        }

        $writer = new Writer($newSpreadsheet);
        $newFileName = $outputDir . '/' . $originalFileName . '_part_' . ($fileIndex + 1) . '.xlsx';
        $writer->save($newFileName);
        echo "Created file: $newFileName\n";
    }
}

Step 4: Use the Function

After defining the splitExcelFile function, use it by providing the path to your Excel file. Optionally, adjust the number of rows per file according to your needs:

$filePath = 'full.xlsx';
splitExcelFile($filePath);

Conclusion

By following this guide, you've learned how to automate the splitting of a large Excel file into smaller, more manageable pieces using PHP and the PhpSpreadsheet library. This script can significantly ease the handling of large datasets, allowing you to work with, share, and process your data more efficiently.

Key Takeaways

• installing PhpSpreadsheet package
• reading and writing excel files using PhpSpreadsheet
• splitting one excel file into multiple files