PhpSpreadsheet

Excel, CSV, OpenOffice und mehr mit PHP verarbeiten

PhpSpreadsheet, ist eine PHP Library, mit der es möglich ist Verschiedenste Tabellenformate zu verarbeiten und zu erzeugen.

Links

https://phpspreadsheet.readthedocs.io

Snippets

XLS Datei erstellen (Hello World)

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');

$writer = new Xlsx($spreadsheet);
$writer->save('data/hello_world.xlsx');

echo('<div>hello_world.xlsx created</div>');
?>

XLS Datei lesen

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileType = 'Xls';
$inputFileName = __DIR__ . '/data/sampleData/example1.xls';

$reader = IOFactory::createReader($inputFileType);
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($inputFileName);

$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
var_dump($sheetData);

XLS 2 HTML

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileType = 'Xls';
$inputFileName = __DIR__ . '/data/sampleData/example1.xls';

$reader = IOFactory::createReader($inputFileType);
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($inputFileName);
$worksheet = $spreadsheet->getActiveSheet();
// Get the highest row number and column letter referenced in the worksheet
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
// Increment the highest column letter (possible for chars in php)
$highestColumn++;
echo '<p>Highest Row: '.$highestRow.' | Highest Column: '.$highestColumn.'</p>';

echo '<table>' . "\n";
for ($row = 1; $row <= $highestRow; ++$row) {
	echo '<tr>' . PHP_EOL;
	for ($col = 'A'; $col != $highestColumn; ++$col) {
		echo '<td>' .
		$worksheet->getCell($col . $row)->getValue() .
		'</td>' . PHP_EOL;
	}
	echo '</tr>' . PHP_EOL;
}
echo '</table>' . PHP_EOL;