PHPackages                             dprmc/excel - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Utility & Helpers](/categories/utility)
4. /
5. dprmc/excel

ActiveLibrary[Utility & Helpers](/categories/utility)

dprmc/excel
===========

A php wrapper around the PhpSpreadsheet library.

v2.0.49(1y ago)75.9k↓74.2%1[1 issues](https://github.com/DPRMC/Excel/issues)2MITPHPPHP ^8.1

Since Feb 28Pushed 1y ago1 watchersCompare

[ Source](https://github.com/DPRMC/Excel)[ Packagist](https://packagist.org/packages/dprmc/excel)[ RSS](/packages/dprmc-excel/feed)WikiDiscussions master Synced 3w ago

READMEChangelog (10)Dependencies (5)Versions (65)Used By (2)

Excel v2
========

[](#excel-v2)

[![Latest Stable Version](https://camo.githubusercontent.com/91c092e1692e2855c62b171573f3bbfd89e0521945d4b2ea06808afb87707be4/68747470733a2f2f706f7365722e707567782e6f72672f6470726d632f657863656c2f76657273696f6e)](https://packagist.org/packages/dprmc/excel)[![codecov](https://camo.githubusercontent.com/f55fb4e510f16e8f40f9bb3e0b302e199644a6038096b794dc933d6b3838bd34/68747470733a2f2f636f6465636f762e696f2f67682f4450524d432f457863656c2f6272616e63682f6d61737465722f67726170682f62616467652e737667)](https://codecov.io/gh/DPRMC/Excel)[![Build Status](https://camo.githubusercontent.com/96c362d01999140992d5eeecbb15019b243113f339ef7560c75b606fb59801bc/68747470733a2f2f7472617669732d63692e6f72672f4450524d432f457863656c2e7376673f6272616e63683d6d6173746572)](https://travis-ci.org/DPRMC/Excel)[![Total Downloads](https://camo.githubusercontent.com/b8e8702e9b0f9b145b4f8b678571a7e26758fae66e2586d13707b2b2f3174c1a/68747470733a2f2f706f7365722e707567782e6f72672f6470726d632f657863656c2f646f776e6c6f616473)](https://packagist.org/packages/dprmc/excel)[![License](https://camo.githubusercontent.com/b468766478d61c2462927d4fa215c7a3fe05c65d42d4366167457c40163da64f/68747470733a2f2f706f7365722e707567782e6f72672f6470726d632f657863656c2f6c6963656e7365)](https://packagist.org/packages/dprmc/excel)

A php library that is a wrapper around the PhpSpreadsheet library.

`composer require dprmc/excel`

Usage: Create a Simple Spreadsheet
----------------------------------

[](#usage-create-a-simple-spreadsheet)

Below is an example showing usage of this class.

You can see we create a couple of associative arrays:

- $rows
- $totals

This library will take the keys from the $rows array, and make those the column headers.

If the output file already exists, this method will append a timestamp at the end to try to make a unique filename.

```
$rows[]     = [
    'CUSIP'  => '123456789',
    'DATE'   => '2018-01-01',
    'ACTION' => 'BUY',
];
$totals     = [
    'CUSIP'  => '1',
    'DATE'   => '2',
    'ACTION' => '3',
];

$options = [
    'title'    => "Sample Title",
    'subject'  => "CUSIP List",
    'category' => "Back Office",
];

$pathToFile = Excel::simple( $rows, $totals, "Tab Label", '/outputFile.xlsx', $options );
```

Usage: Create an Advanced Spreadsheet
-------------------------------------

[](#usage-create-an-advanced-spreadsheet)

Below is an example showing usage of this class.

You can see we create multiple associative arrays:

- $rows
- $totals
- $options
- $columnDataTypes
- $columnsWithCustomNumberFormats
- $columnsWithCustomWidths
- $styles

The '$columnDataTypes' optional associative array parameter will apply the value of the array as the Data Type to the column cells corresponding to the array key.

The '$columnsWithCustomNumberFormats' optional associative array parameter will apply the number format value of the array to the column cells corresponding to the array key.

The '$columnsWithCustomWidths' optional associative array parameter will apply the width value of the array to the column cells corresponding to the array key.

The '$styles' optional associative array parameter will apply the style values of the $styles array to the corresponding column headers, non-header cells, or to a specific cell.

Additionally, an optional boolean parameter '$freezeHeader' will determine if the header row will be frozen. Defaults to 'TRUE'.

If the output file already exists, this method will append a timestamp at the end to try to make a unique filename.

```
$rows[]     = [
    'CUSIP'  => '123456789',
    'DATE'   => '2018-01-01',
    'PRICE'  => '123.45',
    'ACTION' => 'BUY',
    'FORM'   => '=IFERROR(((E2-D2)/D2),"")'
];
$totals     = [
    'CUSIP'  => '1',
    'DATE'   => '2',
    'PRICE'  => '3',
    'ACTION' => '4',
    'FORM'   => '5'
];

$sheetName = 'Sheet Name';
$pathToFile = '/outputFile.xlsx';
$options = [];

$columnDataTypes = [
    'CUSIP' => DataType::TYPE_STRING,
    'DATE'  => DataType::TYPE_STRING,
    'PRICE' => DataType::TYPE_NUMERIC,
    'FORM'  => DataType::TYPE_FORMULA
];
$columnsWithCustomNumberFormats = [
    'PRICE' => Excel::FORMAT_NUMERIC,
    'FORM'  => NumberFormat::FORMAT_NUMBER
];
$columnsWithCustomWidths = [
    'CUSIO' => 50,
    'PRICE' => 75,
    'FORM' => 100
];
$styles = [
    'CUSIP'   => [ 'font' => [ 'bold' => TRUE ] ], // Apply style to column header
    'CUSIP:*' => [ 'borders' => [ 'top' => [ 'borderStyle' => 'thin'] ] ], // Apply style to all column rows except header row
    'DATE:4'  => [ 'fill' => [ 'fillType' => 'linear', 'rotation' => 90 ] ] // Apply style to cell in column and specified row
];

$freezeHeader = TRUE;
$pathToFile = Excel::advanced( $rows, $totals, $sheetName, $pathToFile, $options, $columnDataTypes, $columnsWithCustomNumberFormats, $columnsWithCustomWidths, $styles, $freezeHeader );
```

Usage: Create a Workbook with multiple sheets
---------------------------------------------

[](#usage-create-a-workbook-with-multiple-sheets)

A multidimensional associative array is used to create a workbook with multiple sheets. Each key of the multidimensional array will represent a new sheet within the Workbook. Each value of the multidimensional array follows the formatting of the advanced sheet shown in the example above.

```
$pathToFile = '/outputFile.xlsx';
$options = [];

$workbook['first sheet'] = [
    'rows'                           => [], // A multidimensional array with each item representing a row on the sheet
    'totals'                         => [],
    'columnDataTypes'                => [],
    'columnsWithCustomNumberFormats' => [],
    'columnsWithCustomWidths'        => [],
    'styles'                         => [],
    'freezeHeader'                   => TRUE // A boolean value, defaults to true
];

$workbook['first sheet']['rows'][0] = [
    'CUSIP'     => '123456789',
    'DATE'      => '2024-01-01',
    'ACTION'    => 'BUY',
    'PRICE'     => '123.456',
    'QUANTITY'  => '1'
];

$workbook['first sheet']['rows'][1] = [
    'CUSIP'     => '123456789',
    'DATE'      => '2024-09-01',
    'ACTION'    => 'SELL',
    'PRICE'     => '123.456',
    'QUANTITY'  => '1'
];

$workbook['first sheet']['totals'] = [
    'CUSIP'     => '123456789',
    'DATE'      => '2024-09-17',
    'ACTION'    => '',
    'PRICE'     => '123.456',
    'QUANTITY'  => '0'
];

$workbook['first sheet']['columnDataTypes'] = [
    'CUSIP'     => DataType::TYPE_STRING,
    'ACTION'    => DataType::TYPE_STRING,
    'PRICE'     => DataType::TYPE_NUMERIC,
    'QUANTITY'  => DataType::TYPE_NUMERIC
];

$workbook['first sheet']['columnsWithCustomNumberFormats'] = [
    'PRICE'     => Excel::FORMAT_NUMERIC,
    'QUANTITY'  => Excel::FORMAT_NUMERIC
];

$workbook['first sheet']['columnsWithCustomWidths'] = [
    'CUSIP'    => 50,
    'PRICE'    => 50,
    'ACTION'   => 25,
    'QUANTITY' => 25
];

$workbook['first sheet']['styles'] = [
    'CUSIP' => [
        'font' => ['bold' => TRUE]
    ]
];

$workbook['second sheet'] = [];
$workbook['second sheet']['rows'][0] = [
    'CUSIP' => '987654321',
    'NAV'   => '1234.56'
];
$workbook['second sheet']['rows'][1] = [
    'CUSIP' => 'ABCDEFGHI',
    'NAV'   => '6543.21'
];

$workbook['second sheet']['totals'] = [];
$workbook['second sheet']['columnDataTypes'] = [
    'CUSIP' => DataType::TYPE_STRING,
    'NAV'   => DataType::TYPE_NUMERIC
];
$workbook['second sheet']['columnsWithCustomNumberFormats'] = ['NAV' => Excel::FORMAT_NUMERIC];
$workbook['second sheet']['columnsWithCustomWidths'] = [];
$workbook['second sheet']['styles'] = [
    'CUSIP' => [
        'font' => ['bold' => TRUE]
    ],
    'NAV' => [
        'font' => ['italic' => TRUE]
    ]
];
$workbook['second sheet']['freezeHeader'] = FALSE;
$workbook['third sheet'] = [];
$workbook['third sheet']['rows'][0] = [
    'CUSIP' => '000111222',
    'NAV'   => '56.78'
];
$workbook['third sheet']['rows'][1] = [
    'CUSIP' => 'AAABBBCCC',
    'NAV'   => '111'
];

$pathToFile = Excel::multiSheet( $pathToFile, $options, $workbook );
```

Usage: Reading a Spreadsheet into a PHP Array
---------------------------------------------

[](#usage-reading-a-spreadsheet-into-a-php-array)

Pass in the path to an XLSX spreadsheet and a sheet name, and this method will return an associative array.

```
/**  Define a Read Filter class implementing \PhpOffice\PhpSpreadsheet\Reader\IReadFilter  */
class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter
{
    public function readCell($column, $row, $worksheetName = '') {
        //  Read rows 1 to 7 and columns A to E only
        if ($row >= 1 && $row