How to use PhpSpreadsheet in custom files?

[ross_t]

I have a need to read Excel File data. When I select the Export to Excel and related Extension, I see the phpoffice folder is created with all files in it. However, the example code of phpOffice Git is not working the way it is intended. Can someone please show me how to use the phpoffice/PhpSpreadsheet in custom files? What files I need to import in order to use phpoffice for reading Excel file? Note that the Custom File I'm talking about here does not include headers.

Waiting for your reply...

Thanks!

[mobhar]

I suggest you to learn from the generated code of the certain List Page that uses PhpSpreadsheet for Export to Excel.

[ross_t]

I went through the List; but somehow I couldn't make it which file I should include to make it to work. The Git code examples tell us to include_once "autoload.php" which is not there in the phpoffice folder created by PhpMaker. The List page of PhpMaker did not help me much, but after going through the code of phpoffice folder, I think I found the solution which has worked so far.

Here is my sample code:

Following two lines of code you need to add to Custom File (without headers):
require_once 'vendor/phpoffice/phpspreadsheet/src/Bootstrap.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;

if (file_exists('./uploads/job-files/test.xlsx') == false) {
die('specified file path is wrong');
}

try {

echo 'Create Reader' . PHP_EOL;
$reader = PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);

echo 'Read File' . PHP_EOL;
$spreadsheet = $reader->load("./uploads/job-files/test.xlsx");

echo 'Get active sheet' . PHP_EOL;
$worksheet = $spreadsheet->getActiveSheet();

echo 'Iterate through cells' . PHP_EOL;
echo '<table>' . PHP_EOL;
foreach ($worksheet->getRowIterator() as $row) {
    echo '<tr>' . PHP_EOL;
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,
                                                       //    even if a cell value is not set.
                                                       // By default, only cells that have a value
                                                       //    set will be iterated.
    foreach ($cellIterator as $cell) {
        echo '<td>' .
             $cell->getValue() .
             '</td>' . PHP_EOL;
    }
    echo '</tr>' . PHP_EOL;
}
echo '</table>' . PHP_EOL;

}
catch(\Exception $e) {
echo $e->getMessage();
}

Refer my above sample code or any other example included in phpspreadsheet folder to accomplish rest of the Excel file manipulations and operations.