PHP code example of dprmc / excel
1. Go to this page and download the library: Download dprmc/excel library . Choose the download type require .
2. Extract the ZIP file and open the index.php.
3. Add this code to the index.php.
<?php
require_once('vendor/autoload.php');
/* Start to develop here. Best regards https://php-download.com/ */
dprmc / excel example snippets
$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 );
$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 );
$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 );
/** 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 <= 7) {
if (in_array($column,range('A','E'))) {
return true;
}
}
return false;
}
}
/** Create an Instance of our Read Filter **/
$filterSubset = new MyReadFilter();
$pathToWorkbook = '/outputFile.xlsx';
$sheetName = 'Security_Pricing_Update';
$array = Excel::sheetToArray($pathToWorkbook, $sheetName, $filterSubset);
print_r($array);