Download the PHP package kczer/excel-importer without Composer

Table of contents:

• Excel-importer
  • Installation
  • Documentation
    • Creating simple EXCEL data import
    • Mapping EXCEL data to objects
    • DictionaryExcelCell
    • MultipleDictionaryExcelCell
    • Custom ExcelCellClasses
    • More complex imports
    • Data encoding

Excel-importer

Excel-importer is a PHP library that enables to easy import EXCEL formats data and event parse it to objects.

Installation

You can install it with composer like so:

Documentation

Creating simple EXCEL data import

First you need to create Import class that extends Kczer\ExcelImporter\AbstractExcelImporter.

For version >= 3.0:

`

For version < 3.0

:warning: Since version 3.0 int column keys are no longer supported.

Then using the service:

:warning: Since version 3.0 importer always omits first row.

All methods shown in processParsedData() are public and therefore can be accessed from outside the service. As You could see in Configuration above there StringExcelCell and IntegerExcelCell classes used. These classes are used to perform proper parsing and validations on EXCEL cell values. You can crate Your own ExcelCell classes- more info below.

Predefined ExcelCell classes:

• StringExcelCell - Simple string values with no validation of data. getValue() returns string.
• IntegerExcelCell - Accepts only valid ints. getValue() returns int.
• FloatExcelCell - Accepts only valid numbers. getValue() return float.
• BoolExcelCell - Accepts 'y', 'yes', 't', 'tak', 't', 'true' (case insensitive) as true. Other values are considered false. getValue() returns bool.
• DateTimeExcelCell - Accepts all strings acceptable by DateTime class constructor. getValue() returns DataTime object
• AbstractDictionaryExcelCell - Abstract class useful for key-value types example below
• AbstractMultipleDictionaryExcelCell - Abstract class that can merge a couple of AbstractDictionaryCell dictionaries into one

Congratulations! You created your first simple EXCEL import class. Although, there is a better and faster way of doing imports with Excel-importer.

Mapping EXCEL data to objects

In previous example import resulted in array of ExcelRow objects, but if we wanted map out EXCEL data to some model object? Let's assume that we have some PHP object named SomeModel. We can import it by extending Kczer\ExcelImporter\AbstractModelExcelImporter:

It's almost ready. To make it work we need to do one more step of setup. In our SomeModel class:

The most important thing in code below is ExcelColumn annotation class, that tells the importer everything about our model data type, name, column key (again, key column can be omitted, and then properties order is taken, but it's not considered) and cell obligatory. These two classes is everything you need to do. Excel importer will do the rest and create Your model instances for You.

DictionaryExcelCell

Dictionary EXCEL cells are used to define "range" of values, that cell can have. It's perfect when cell value can be for example id of some resource from database. Sample DictionaryExcelCell class:

Now, we could just add this class to Import configuration of to ExcelColumn annotation, and excel-importer will accept only values from range 1-4 and getValue will return User objects.

MultipleDictionaryExcelCell

Let's say that one column can contain either value from one dictionary or from another. MultipleDictionaryExcelCell is a perfect tool for such situation:

Now, excel-import accepts values from both SomeDictionaryClass and SomeOtherDictionaryClasss dictionaries (if keys intersect, class with lower array key has priority).

Custom ExcelCellClasses

If You want more flexible or more validation in ExcelCell class, You can simply extend AbstractExcelCellClass and create custom validations and return data types. Int the example we will create cell that needs to be a valid email:

More complex imports

Sometimes, we need to validate dependencies between cells inside a row, or even dependencies between rows. We can do that as well. AbstractExcelImporter implements checkRow checkRowRequirements() method that can be overriden to check required dependencies and add some errors if needed. It is called right before model creation in AbstractModelExcelImporter, so we can still be able to create object from EXCEL data.

Example of dependency validation:

Lets say we have some Model:

Let's assume, that num1 should be bigger than num2. We can validate this dependency like so:

If validation adds any error, then excel will be considered invalid, therefore models WILL NOT be created.

Data encoding

If you want to encode data from importer (for example to send it with request), You can do it like so:

... and then re-create importer instance:

Since version 3.0, it is also possible to get rows as JSON:

... and then re-create rows from this JSON: