ExcelMapper: Simplify Excel Data Importation in Your PHP Projects

Managing Excel files in PHP projects can often be a tedious task, especially when it comes to importing data into your application’s database. Enter ExcelMapper, a PHP library designed to simplify this process. ExcelMapper allows you to map Excel columns directly to your database fields, providing a streamlined way to handle data importation.

In this article, I'll walk you through the key features of ExcelMapper, show you how to install and configure it, and provide some practical examples to help you get started.

Installation

You can install ExcelMapper using Composer:

composer require esmaeil/excelmapper

Basic Usage
Let’s dive into a basic example. Suppose you have an Excel file with the following structure:

| C   | E       | G        | N           | O          | P           |
|-----|---------|----------|-------------|------------|-------------|
| 123 | 456789  | John Doe | 09123456789 | 2023-12-31 | 123 Elm St  |

You want to map these columns to your database fields. With ExcelMapper, you can do this seamlessly.

The ExcelDataProcessor Class
The core of ExcelMapper is the ExcelDataProcessor class, which handles the mapping and processing of Excel data:

Here’s an example of a custom parser that converts Persian/Arabic digits to English digits:

use ExcelMapper\DataProcessor\ExcelDataProcessor;
use ExcelMapper\Parsers\DefaultParser;
use ExcelMapper\Parsers\DateColumn;

$processor = new ExcelDataProcessor();

$sheetData = [
    ['C', 'E', 'G', 'N', 'O', 'P'],  // Excel columns as a reference
    [123, 456789, 'John Doe', '۰۹۱۲۳۴۵۶۷۸۹', '2023-12-31', '123 Elm St'],  // Data row
];

$mapping = [
    'C' => 'insurance_code',
    'E' => 'national_id',
    'G' => ['customer_name', DefaultParser::class],
    'N' => ['mobile', DefaultParser::class],
    'O' => ['expiry_date', DateColumn::class],
    'P' => 'address',
];

$processor->process($sheetData, $mapping, function ($mappedData) {
    // Here, $mappedData will contain the processed data ready for database insertion
    print_r($mappedData);
});

Mapping Columns
In this example, we map specific Excel columns (like 'C', 'E', etc.) to database fields such as 'insurance_code', 'national_id', and more. The ExcelDataProcessor takes care of converting the Excel column letters to their corresponding zero-based indices and applying any necessary parsers, like converting Persian digits to English.

Custom Parsers
Sometimes, you need to process data in specific ways before importing it. For example, you might need to convert dates or clean up phone numbers. ExcelMapper allows you to define custom parsers, which are applied to the data during the processing stage.

Here’s how you can define a simple parser:

namespace ExcelMapper\Parsers;

use ExcelMapper\Interfaces\ColumnParserInterface;
use ExcelMapper\Utils\DataHelper;

class DigitsParser implements ColumnParserInterface
{
    public function parse(mixed $value): string|array
    {
        if ($value === null) {
            return $value;
        }

        return DataHelper::convertDigits($value);
    }
}

Conclusion
ExcelMapper simplifies the process of importing data from Excel files into your PHP projects. By leveraging custom parsers, column mapping, and a robust data processing engine, you can quickly integrate Excel data into your applications. Whether you're dealing with simple spreadsheets or complex data transformations, ExcelMapper offers a flexible solution.

If you're looking to streamline your data import processes, give ExcelMapper a try. You can find the full code and more examples on GitHub.