PhpSpreadsheet is the next version of PHPExcel. It can read and write: Open Document Format/OASIS (.ods), Office Open XML (.xlsx) Excel, BIFF 8 (.xls) Excel, HTML, CSV. Also, it can write PDF (using either the tcPDF, DomPDF or mPDF libraries), and charts.
• To download the script, click on this link: Download PhpSpreadsheet (48 MB).
Software requirements
- PHP version 5.6 or newer.- PHP extension php_zip enabled.
- PHP extension php_xml enabled.
- PHP extension php_gd2 enabled (if not compiled in, optional, but required for exact column width autocalculation).
PhpSpreadsheet Installation and Usage
- Download the archive with PhpSpreadsheet from the link above. It contains also all the 3rd party required php libraries, in the "vendor/" folder. So, you can create PDF documents and Charts.
- Copy the spreadsheet folder with all its content on your server.
- In the php file where you want to use the PhpSpreadsheet library, include the "spreadsheet/vendor/autoload.php" file. Then, with the "use" instruction add the classes you want to use.
- Like in this code:
//include the file that loads the PhpSpreadsheet classes require 'spreadsheet/vendor/autoload.php'; //include the classes needed to create and write .xlsx file use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; //now you can use the Spreadsheet and Xlsx classes
Click on the code to select it.
Create a simple .xlsx file
//include the file that loads the PhpSpreadsheet classes
require 'spreadsheet/vendor/autoload.php';
//include the classes needed to create and write .xlsx file
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
//object of the Spreadsheet class to create the excel data
$spreadsheet = new Spreadsheet();
//add some data in excel cells
$spreadsheet->setActiveSheetIndex(0)
->setCellValue('A1', 'Domain')
->setCellValue('B1', 'Category')
->setCellValue('C1', 'Nr. Pages');
$spreadsheet->setActiveSheetIndex(0)
->setCellValue('A2', 'CoursesWeb.net')
->setCellValue('B2', 'Web Development')
->setCellValue('C2', '4000');
$spreadsheet->setActiveSheetIndex(0)
->setCellValue('A3', 'MarPlo.net')
->setCellValue('B3', 'Courses & Games')
->setCellValue('C3', '15000');
//set style for A1,B1,C1 cells
$cell_st =[
'font' =>['bold' => true],
'alignment' =>['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
'borders'=>['bottom' =>['style'=> \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM]]
];
$spreadsheet->getActiveSheet()->getStyle('A1:C1')->applyFromArray($cell_st);
//set columns width
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(16);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(18);
$spreadsheet->getActiveSheet()->setTitle('Simple'); //set a title for Worksheet
//make object of the Xlsx class to save the excel file
$writer = new Xlsx($spreadsheet);
$fxls ='excel-file_1.xlsx';
$writer->save($fxls);
- Results an excel document like in this image:
Reading Excel file data and display it in html table
- In this example it is used the excel document created in the example above. We add and display its data into a html table.
//include the file that loads the PhpSpreadsheet classes
require 'spreadsheet/vendor/autoload.php';
//create directly an object instance of the IOFactory class, and load the xlsx file
$fxls ='excel-file_1.xlsx';
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($fxls);
//read excel data and store it into an array
$xls_data = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
/* $xls_data contains this array:
[1=>['A'=>'Domain', 'B'=>'Category', 'C'=>'Nr. Pages'], 2=>['A'=>'CoursesWeb.net', 'B'=>'Web Development', 'C'=>4000], 3=>['A'=>'MarPlo.net', 'B'=>'Courses & Games', 'C'=>15000]]
*/
//now it is created a html table with the excel file data
$html_tb ='<table border="1"><tr><th>'. implode('</th><th>', $xls_data[1]) .'</th></tr>';
$nr = count($xls_data); //number of rows
for($i=2; $i<=$nr; $i++){
$html_tb .='<tr><td>'. implode('</td><td>', $xls_data[$i]) .'</td></tr>';
}
$html_tb .='</table>';
echo $html_tb;
Results this html table:
| Domain | Category | Nr. Pages |
|---|---|---|
| CoursesWeb.net | Web Development | 4000 |
| MarPlo.net | Courses & Games | 15000 |
Make Excel document with a Pie Chart
The PhpSpreadsheet can also be used to create Excel documents with various type of Charts inside.
//include the file that loads the PhpSpreadsheet classes
require 'spreadsheet/vendor/autoload.php';
//include the class needed to create excel data
use PhpOffice\PhpSpreadsheet\Spreadsheet;
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
//create an excel worksheet and add some data for chart
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->fromArray([
['', 2010, 2011, 2012],
['Q1', 12, 15, 21],
['Q2', 56, 73, 86],
['Q3', 52, 61, 69],
['Q4', 30, 32, 0],
]);
//Set the Labels for each data series we want to plot
// Datatype
// Cell reference for data
// Format Code
// Number of datapoints in series
// Data values
// Data Marker
$dataSeriesLabels = [
new \PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues('String', 'Worksheet!$C$1', null, 1), // 2011
];
//Set the X-Axis Labels
// Datatype
// Cell reference for data
// Format Code
// Number of datapoints in series
// Data values
// Data Marker
$xAxisTickValues = [
new \PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues('String', 'Worksheet!$A$2:$A$5', null, 4), // Q1 to Q4
];
//Set the Data values for each data series we want to plot
// Datatype
// Cell reference for data
// Format Code
// Number of datapoints in series
// Data values
// Data Marker
$dataSeriesValues = [
new \PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues('Number', 'Worksheet!$C$2:$C$5', null, 4),
];
// Build the dataseries
$series = new \PhpOffice\PhpSpreadsheet\Chart\DataSeries(
\PhpOffice\PhpSpreadsheet\Chart\DataSeries::TYPE_PIECHART, // plotType
null, // plotGrouping (Pie charts don't have any grouping)
range(0, count($dataSeriesValues) - 1), // plotOrder
$dataSeriesLabels, // plotLabel
$xAxisTickValues, // plotCategory
$dataSeriesValues // plotValues
);
// Set up a layout object for the Pie chart
$layout = new \PhpOffice\PhpSpreadsheet\Chart\Layout();
$layout->setShowVal(true);
$layout->setShowPercent(true);
// Set the series in the plot area
$plotArea = new \PhpOffice\PhpSpreadsheet\Chart\PlotArea($layout, [$series]);
// Set the chart legend
$legend = new \PhpOffice\PhpSpreadsheet\Chart\Legend(\PhpOffice\PhpSpreadsheet\Chart\Legend::POSITION_RIGHT, null, false);
$title = new \PhpOffice\PhpSpreadsheet\Chart\Title('Test Pie Chart');
// Create the chart
$chart = new \PhpOffice\PhpSpreadsheet\Chart(
'chart', // name
$title, // title
$legend, // legend
$plotArea, // plotArea
true, // plotVisibleOnly
0, // displayBlanksAs
null, // xAxisLabel
null // yAxisLabel - Pie charts don't have a Y-Axis
);
//Set the position where the chart should appear in the worksheet
$chart->setTopLeftPosition('A7');
$chart->setBottomRightPosition('H20');
//Add the chart to the worksheet
$worksheet->addChart($chart);
//Save Excel 2007 file
$filename ='excel-pie-chart.xlsx';
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setIncludeCharts(true);
$writer->save($filename);
- Results an excel document like in this image:
- The PhpSpreadsheet can also be used to create PDF documents with DomPdf or mPdf class, and calculations; run and see the examples in the "samples/" folder.
• Main source: PhpSpreadsheet - Git repository
• Documentation: PhpSpreadsheet's documentation
- A good way to get started and to learn is to run and study some of the samples.