adnzaki/excel-creator

A PHPOffice Spreadsheet simplifier

1.0.2 2023-05-26 07:41 UTC

Requires

• php: ^7.4 || ^8.0
• phpoffice/phpspreadsheet: ^1.28

MIT

• Adnan Zaki

README

A simple way to work with PHPSpreadsheet

Introduction

ExcelCreator is an additional tool that enables you to use PHPSpreadsheet more easily. ExcelCreator simplifies method calls like $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(12) into $excel->setColumnWidth('A', 12). You may think it does not really help you, but imagine that you have to write code like you usually use in PHPSpreadsheet many times. If you are a lazy programmer, ExcelCreator is a perfect tool to access commonly-used features in PHPSpreadsheet in a convenient way.

Installation

The only thing you need to install ExcelCreator is via Composer with the following options.

With existing composer.json file

If you have existing composer.json file, add a requirement pointed to "adnzaki/excel-creator": "^1.0"

{
    "require": {
        "adnzaki/excel-creator": "^1.0"
    }
}

And then run composer update to install it.

No composer.json file

Run composer require adnzaki/excel-creator to install it and automatically creates composer.json file.

Install the latest source code

If you prefer get the latest source code of ExcelCreator, simply change the version from ^1.0 to dev-main, and then run composer update to switch the source code.

Usage

This section will guide you how to transform PHPSpreadsheet original use into ExcelCreator

• Initializing ExcelCreator
  

require 'vendor/autoload.php'
$excel = new ExcelCreator();

• Call the Xlsx writer

$excel->writer($excel->spreadsheet);

• Call the Xlsx reader

$excel->reader($excel->spreadsheet);

• Saving excel file to client's browser not get more simple
  

$excel->save('hello world.xlsx'); // save in Excel 2007 format
// or
$excel->save('hello world.xlsx', 'Xls'); // save in Xls format

Note that you have to set Content-Type header before use this method. And also the second parameter should follow the PHPSpreadsheet file type.

• Apply styles into cells
  The best way to apply style is using array and pass it as parameter

$dataStyle = [            
    'font' => [
        'name' => 'Arial',
        'size' => 10,
    ],
    'border' => [
        'borderStyle' => $excel->border::BORDER_THIN,
        'color' => $excel->color::COLOR_BLACK,
    ],
];

$excel->applyStyle($dataStyle, 'A2:D10');

Styles that have been supported in ExcelCreator are Alignment, Border, Color, Fill and Font

• Fill cells with data
  ExcelCreator provides simple way to fill cells with your data/value. ExcelCreator uses method chaining from PHPSpreadsheet Spreadsheet::getActiveSheet()->fromArray($value);

$data = [
    ['Name',        'Place of birth'],
    ['Adnan Zaki',  'Jakarta'],
    ['Dien Azizah', 'Bojonegoro']
];

$excel->fillCell($data); // fill cell from A1
// or 
$excel->fillCell($data, 'A2'); // fill cell from A2

• Wrapping text
  Wrapping a cell is get easier

$excel->wrapText('B5');

Note: Wrapped text can be overridden if you set style array after wrapText() without defining wrapText in alignment.

• Merge and unmerge cells
  Although merging and unmerging cells in PHPSpreadsheet is easy, but we make it more simple.

$excel->mergeCells('A1:B1');
$excel->unmergeCells('A1:B1');

• Setting column's width
  Have you ever get tired of writing $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(12); just to set column's width? Here is how ExcelCreator makes it easy for you:

$excel->setColumnWidth('D', 12);

// or

$excel->setColumnWidth('D'); // will create auto size

Or if you would like to set some columns with the same size:

$columns = ['A', 'B', 'C'];
$excel->setMultipleColumnsWidth($columns, 12);

or

$excel->setMultipleColumnsWidth($columns); // will create auto size for those columns

And if you would like to set default column's width

$excel->setDefaultColumnWidth(12);

• Setting row's height
  Setting a row's height is much similar like setting column's width

$excel->setRowHeight('5', 20);

Or if you would like to set some rows with the same height:

$excel->setMultipleRowsHeight('1-5', 20);

But now setMultipleRowsHeight() supports more multiple rows:

$excel->setMultipleRowsHeight(['1' => 40, '2' => 30 '3-6' => 20]);

And if you would like to set default row's height

$excel->setDefaultRowHeight(20);

• Set default font
  Setting default font is as easy as follow:

$excel->setDefaultFont('Arial', 12)