PhpSpreadsheet: Unterschied zwischen den Versionen

Aus Wikizone
Wechseln zu: Navigation, Suche
(Die Seite wurde neu angelegt: „== Excel, CSV, OpenOffice und mehr mit PHP verarbeiten == PhpSpreadsheet, ist eine PHP Library, mit der es möglich ist Verschiedenste Tabellenformate zu verar…“)
 
 
(Eine dazwischenliegende Version desselben Benutzers wird nicht angezeigt)
Zeile 40: Zeile 40:
  
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
=== XLS 2 HTML ===
 +
<syntaxhighlight lang="php">
 +
<?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;
 +
</syntaxhighlight>
 +
=== Create data and write CSV file ===
 
<syntaxhighlight lang="php">
 
<syntaxhighlight lang="php">
−
</syntaxhighlight>
+
<?php
 +
 
 +
require 'vendor/autoload.php';
 +
 
 +
use PhpOffice\PhpSpreadsheet\Spreadsheet;
 +
use PhpOffice\PhpSpreadsheet\Writer\Csv;
 +
$targetFile='data/writeCsvSample.csv';
 +
// Create Spreadsheet...
 +
$spreadsheet = new Spreadsheet();
 +
$sheet = $spreadsheet->getActiveSheet();
 +
 
 +
// Set some date in sheet...
 +
$arrayData = [
 +
    [NULL, 2010, 2011, 2012],
 +
    ['Q1',  12,  15,  21],
 +
    ['Q2',  56,  73,  86],
 +
    ['Q3',  52,  61,  69],
 +
    ['Q4',  30,  32,    0],
 +
];
 +
$sheet->fromArray(
 +
        $arrayData,  // The data to set
 +
        NULL,        // Array values with this value will not be set
 +
        'C3'        // Top left coordinate of the worksheet range where
 +
                    //    we want to set these values (default is A1)
 +
    );
  
−
<syntaxhighlight lang="php">
+
// Write as csv...
 +
$writer = new Csv($spreadsheet);
 +
$writer->save($targetFile);
 +
$writer->setDelimiter(';');
 +
$writer->setEnclosure('');
 +
$writer->setLineEnding("\r\n");
 +
$writer->setSheetIndex(0);
 +
echo('<div><a href="'.$targetFile.'" target="_blank">'.$targetFile.'</a> created</div>');
 +
?>
 
</syntaxhighlight>
 
</syntaxhighlight>
  
 
<syntaxhighlight lang="php">
 
<syntaxhighlight lang="php">
 
</syntaxhighlight>
 
</syntaxhighlight>

Aktuelle Version vom 5. März 2019, 16:09 Uhr

Excel, CSV, OpenOffice und mehr mit PHP verarbeiten[Bearbeiten]

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

Links[Bearbeiten]

https://phpspreadsheet.readthedocs.io

Snippets[Bearbeiten]

XLS Datei erstellen (Hello World)[Bearbeiten]

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[Bearbeiten]

<?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[Bearbeiten]

<?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;

Create data and write CSV file[Bearbeiten]

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
$targetFile='data/writeCsvSample.csv';
// Create Spreadsheet...
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Set some date in sheet...
$arrayData = [
    [NULL, 2010, 2011, 2012],
    ['Q1',   12,   15,   21],
    ['Q2',   56,   73,   86],
    ['Q3',   52,   61,   69],
    ['Q4',   30,   32,    0],
];
$sheet->fromArray(
        $arrayData,  // The data to set
        NULL,        // Array values with this value will not be set
        'C3'         // Top left coordinate of the worksheet range where
                     //    we want to set these values (default is A1)
    );

// Write as csv...
$writer = new Csv($spreadsheet);
$writer->save($targetFile);
$writer->setDelimiter(';');
$writer->setEnclosure('');
$writer->setLineEnding("\r\n");
$writer->setSheetIndex(0);
echo('<div><a href="'.$targetFile.'" target="_blank">'.$targetFile.'</a> created</div>');
?>
Abgerufen von „https://wiki.stephanschlegel.de/index.php?title=PhpSpreadsheet&oldid=23736“