Download the PHP package kczer/excel-importer-bundle without Composer

Table of contents

• ExcelImporterBundle
  • Installation
  • Documentation
  • Sample import model- @ExcelColumn annotation:
  • Sample import - model (Technical keys):
  • Sample import - model (Named column keys):
  • Sample import - display model
  • Sample import
  • Sample import - complex validation
  • Sample import- useful methods
  • Sample import- annotation validation
  • Sample export
  • AbstractDictionaryExcelCell
  • Yaml configuration

ExcelImporterBundle

ExcelImportedBundle enables PHP objects import from EXCEL files as well as export of the same objects.

Installation

You can install it with composer like so:

Documentation

Sample import model- @ExcelColumn annotation:

First, we need to create model that will represent EXCEL file content. Here we use @ExcelColumn annotation with following options:

• columnKey (Supports translate keys): can be Technical EXCEL column name (in A-ZZZ... notation) or Named column name (recommended) that will be value in EXCEL file. For named column names to work we need to have one row that matches all column names. Named and technical column keys must not be mixed.

• cellName (Supports translate keys): Name that is used for displaying error messages and also can be used for displaying straight from ExcelRow instance.

• targetExcelCellClass: Excel cell klas that will be used to parse excel value to property. Several of them are available out of the box:

  Syntax	Description	Returned	Default validation	Configuration
  StringExcelCell	Basic type for handling simple string	string	None	none
  IntegerExcelCell	Type for handling integer values	int	EXCEL value needs to be valid int	none
  FloatExcelCell	Type for handling float values	float	EXCEL value needs to be number	none
  BoolExcelCell	Type for handling bool values	bool	Value needs to be one of ['tak', 'y', 'yes', 't', 't', 'true', '1'] for true, or ['nie', 'n', 'no', 'false', 'f', '0'] for false (case insensitive)	true and false values are configurable, as well as option to treat empty values as false (more info in yaml configuration section)
  DateTimeExcelCell	Type for handling dates or dates with time	DateTime	Value needs to be acceptable by DateTime class constructor	none
  AbstractDictionaryExcelCell	Type for handling EXCEL values that can be only in specified range (each dictionary excel cell needs to be created by extending AbstractExcelCell class)	mixed	Value needs to be a key from dictionary (more info in AbstractDictionaryExcelCell section)	none

• required: Whether the value is required in the EXCEL file (default to true)

• options: additional options specific for EXCEL cell. Supported options:

  EXCEL cell class	option	description
  DateTimeExcelCell	reverseDateTimeFormat	Format used for EXCEL export of related property

Sample import - model (Technical keys):

Sample model for EXCEL file would look like this:

Following EXCEL file would match this model:

Sample import - model (Named column keys):

The same model as above using named column keys would look like so:

Sample import - display model

Sometimes we would like to display some of imported content or see validation messages generated by importer. It can be achieved by creating display model class that extends AbstractDisplayModel class. Display model classes must have the same property names as model one (for all mapped fields) and have them declared as strings. Display model class for above model would look like so:

Thanks to extending AbstractDisplayModel class we have access to two methods:

• isValid: Returns true or false depending on model validity
• getMergedAllErrorMessages: Returns string containing concatenated error messages from all properties of model

Sample import

Model defined above can be imported via importing ModelExcelImporterFactory and creating ModelExcelImporter instance

Method parseExcelFile takes up two arguments:

• excelFilePath: full path to EXCEL file
• firstRowMode (optional, works only with technical column keys): Tells what importer should do with first data row. Available options:
  • AbstractExcelImporter::FIRST_ROW_MODE_SKIP(default): skip first data row
  • AbstractExcelImporter::FIRST_ROW_MODE_DONT_SKIP: do not skip first data row
  • AbstractExcelImporter::FIRST_ROW_MODE_SKIP_IF_INVALID: skip first data row only if it is not valid against model

Sample import - complex validation

Sometimes it is required to perform some more complex validation, for example validation between columns, when one cell should be dependent on others. Let's say that we want every name cell to end with corresponding id. It is achievable by calling setRowRequirementsValidator:

Sample import- useful methods

Here are some useful methods from importer:

Sample import- annotation validation

Bundle comes with two built-in validation available from model level:

• @Validator\Regex: Validates excel cell value against regex expression (regex is case insensitive)
• @Validator\Length: Validates excel cell length

Each validator has message property, that specifies error message when validation fails. These messages support translations and each validator passes specific translation parameters. Supported translation parameters

• @Validator\Regex:
  • %pattern%: pattern passed in annotation
• @Validator\Length:
  • %minLength%: minimum length passed in annotation
  • %maxLength%: maximum length passed in annotation

If we for example wanted name field to always end with id, and to be at most 10 characters long, we can type:

Sample export

Having our model we can not only import it from EXCEL file, but also export it and merge to existing EXCEL FILES.

There are two export methods:

• exportModelsToNewFile: Create new EXCEL file and output models to it. Method takes up to three arguments
  • models: Models to export
  • newFileNameWithoutExtension: Generated EXCEL file name without extension. random name if null provided
  • outputHeaders: Whether to add header columns
• exportAndMergeModelsToExistingFile: Create new EXCEL file and output models to it. Method takes up to three arguments
  • models: Same as above
  • newFileNameWithoutExtension: Same as above
  • comparer: Can be either model property name to compare or anonymous function taking two models as arguments that returns bool if two models are equal. If two models are equal, the EXCEL one will be replaced with imported one
  • firstRowMode: first row mode passed to importer when getting data from EXCEL (works only with technical column keys)

AbstractDictionaryExcelCell

Dictionary EXCEL cells are used to define "range" of values, that cell can take. It's use when cell value must match some column value from database Sample DictionaryClass class:

Yaml configuration

Example below shows all available configuration options, that can be paced in _config/packages/kczer_excelimporter.yaml file: