1. Go to this page and download the library: Download avadim/fast-excel-reader 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/ */
avadim / fast-excel-reader example snippets
use \avadim\FastExcelReader\Excel;
$file = __DIR__ . '/files/demo-00-simple.xlsx';
// Open XLSX-file
$excel = Excel::open($file);
// Read all values as a flat array from current sheet
$result = $excel->readCells();
// Read all rows in two-dimensional array (ROW x COL)
$result = $excel->readRows();
// Read all columns in two-dimensional array (COL x ROW)
$result = $excel->readColumns();
$sheet = $excel->sheet();
foreach ($sheet->nextRow() as $rowNum => $rowData) {
// $rowData is array ['A' => ..., 'B' => ...]
$addr = 'C' . $rowNum;
if ($sheet->hasImage($addr)) {
$sheet->saveImageTo($addr, $fullDirectoryPath);
}
// handling of $rowData here
// ...
}
// OR
foreach ($sheet->nextRow() as $rowNum => $rowData) {
// handling of $rowData here
// ...
// get image list from current row
$imageList = $sheet->getImageListByRow();
foreach ($imageList as $imageInfo) {
$imageBlob = $sheet->getImageBlob($imageInfo['address']);
}
}
// OR
foreach ($sheet->nextRow(['A' => 'One', 'B' => 'Two'], Excel::KEYS_FIRST_ROW) as $rowNum => $rowData) {
// $rowData is array ['One' => ..., 'Two' => ...]
// ...
}
// Init internal read generator
$sheet->reset(['A' => 'One', 'B' => 'Two'], Excel::KEYS_FIRST_ROW);
// read the first row
$rowData = $sheet->readNextRow();
var_dump($rowData);
// read the next 3 rows
for ($i = 0; $i < 3; $i++) {
$rowData = $sheet->readNextRow();
var_dump($rowData);
}
// Reset internal generator and read all rows
$sheet->reset(['A' => 'One', 'B' => 'Two'], Excel::KEYS_FIRST_ROW);
$result = [];
while ($rowData = $sheet->readNextRow()) {
$result[] = $rowData;
}
var_dump($result);
// Read rows and use the first row as column keys
$result = $excel->readRows(true);
// Rows and cols start from zero
$result = $excel->readRows(false, Excel::KEYS_ZERO_BASED);
$sheet = $excel->sheet();
// Skip empty cells and empty rows
foreach ($sheet->nextRow() as $rowNum => $rowData) {
// handle $rowData
}
// Skip empty cells and cells with empty strings
foreach ($sheet->nextRow([], Excel::TREAT_EMPTY_STRING_AS_EMPTY_CELL) as $rowNum => $rowData) {
// handle $rowData
}
// Skip empty cells and empty rows (rows containing only whitespace characters are also considered empty)
foreach ($sheet->nextRow([], Excel::TRIM_STRINGS | Excel::TREAT_EMPTY_STRING_AS_EMPTY_CELL) as $rowNum => $rowData) {
// handle $rowData
}
$sheet->reset([], Excel::TRIM_STRINGS | Excel::TREAT_EMPTY_STRING_AS_EMPTY_CELL);
$rowData = $sheet->readNextRow();
// do something
$rowData = $sheet->readNextRow();
// handle next row
// ...
use \avadim\FastExcelReader\Excel;
$file = __DIR__ . '/files/demo-02-advanced.xlsx';
$excel = Excel::open($file);
$result = [
'sheets' => $excel->getSheetNames() // get all sheet names
];
$result['#1'] = $excel
// select sheet by name
->selectSheet('Demo1')
// select area with data where the first row contains column keys
->setReadArea('B4:D11', true)
// set date format
->setDateFormat('Y-m-d')
// set key for column 'C' to 'Birthday'
->readRows(['C' => 'Birthday']);
// read other arrays with custom column keys
// and in this case we define range by columns only
$columnKeys = ['B' => 'year', 'C' => 'value1', 'D' => 'value2'];
$result['#2'] = $excel
->selectSheet('Demo2', 'B:D')
->readRows($columnKeys);
$result['#3'] = $excel
->setReadArea('F5:H13')
->readRows($columnKeys);
$sheet = $excel->getSheet('Demo2')->setReadArea('Headers');
// Exception: Wrong address or range "Values"
use \avadim\FastExcelReader\Excel;
$excel = Excel::open($file);
$result = [];
$excel->readCallback(function ($row, $col, $val) use(&$result) {
// Any manipulation here
$result[$row][$col] = (string)$val;
// if the function returns true then data reading is interrupted
return false;
});
var_dump($result);
$excel = Excel::open($file);
$sheet = $excel->sheet()->setReadArea('B5:D7');
$cells = $sheet->readCells();
echo $cells['C5']; // -2205187200
// If argument TRUE is passed, then all dates will be formatted as specified in cell styles
// IMPORTANT! The datetime format depends on the locale
$excel->dateFormatter(true);
$cells = $sheet->readCells();
echo $cells['C5']; // '14.02.1900'
// You can specify date format pattern
$excel->dateFormatter('Y-m-d');
$cells = $sheet->readCells();
echo $cells['C5']; // '1900-02-14'
// set date formatter function
$excel->dateFormatter(fn($value) => gmdate('m/d/Y', $value));
$cells = $sheet->readCells();
echo $cells['C5']; // '02/14/1900'
// returns DateTime instance
$excel->dateFormatter(fn($value) => (new \DateTime())->setTimestamp($value));
$cells = $sheet->readCells();
echo get_class($cells['C5']); // 'DateTime'
// custom manipulations with datetime values
$excel->dateFormatter(function($value, $format, $styleIdx) use($excel) {
// get Excel format of the cell, e.g. '[$-F400]h:mm:ss\ AM/PM'
$excelFormat = $excel->getFormatPattern($styleIdx);
// get format converted for use in php functions date(), gmdate(), etc
// for example the Excel pattern above would be converted to 'g:i:s A'
$phpFormat = $excel->getDateFormatPattern($styleIdx);
// and if you need you can get value of numFmtId for this cell
$style = $excel->getCompleteStyleByIdx($styleIdx, true);
$numFmtId = $style['format-num-id'];
// do something and write to $result
$result = gmdate($phpFormat, $value);
return $result;
});
$excel = Excel::open($file);
// default mode
$cells = $sheet->readCells();
echo $cell['B1']; // -2208798720 - the library tries to interpret the number 3.2 as a timestamp
echo $cell['B2']; // 1706918400 - timestamp of 2024-02-03
// date formatter is on
$excel->dateFormatter(true);
$cells = $sheet->readCells();
echo $cell['B1']; // '03.01.1900'
echo $cell['B2']; // '3.2'
// date formatter is off
$excel->dateFormatter(false);
$cells = $sheet->readCells();
echo $cell['B1']; // '3.2'
echo $cell['B2']; // 1706918400 - timestamp of 2024-02-03
// Returns count images on all sheets
$excel->countImages();
// Returns count images on sheet
$sheet->countImages();
// Returns image list of sheet
$sheet->getImageList();
// Returns image list of specified row
$sheet->getImageListByRow($rowNumber);
// Returns TRUE if the specified cell has an image
$sheet->hasImage($cellAddress);
// Returns mime type of image in the specified cell (or NULL)
$sheet->getImageMimeType($cellAddress);
// Returns inner name of image in the specified cell (or NULL)
$sheet->getImageName($cellAddress);
// Returns an image from the cell as a blob (if exists) or NULL
$sheet->getImageBlob($cellAddress);
// Writes an image from the cell to the specified filename
$sheet->saveImage($cellAddress, $fullFilenamePath);
// Writes an image from the cell to the specified directory
$sheet->saveImageTo($cellAddress, $fullDirectoryPath);