PhpSpreadsheet - Read, Write Excel and LibreOffice Calc files

Software requirements

PhpSpreadsheet Installation and Usage

1. Copy the spreadsheet folder with all its content on your server.
   - It contains also all the 3rd party required php libraries, in the "vendor/" folder. So, you can create PDF documents and Charts.
2. 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
   

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

Reading Excel file data and display it in 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;

Make Excel document with a Pie Chart

//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);