PhpSpreadsheet - Read, Write Excel and LibreOffice Calc files

Php-mysql Course

PhpSpreadsheet is a PHP library with a set of classes for reading and writing spreadsheet files, like Excel and LibreOffice Calc.
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

1. 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.
2. Copy the spreadsheet folder with all its content on your server.
3. 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
   

Here are some examples with PhpSpreadsheet.
_{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:

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.

<img src="http://coursesweb.net/imgs/webcourses.gif" width="191" height="63" alt="Courses-Web" />

#id {
  font-style: italic;
}

$(document).ready(function() {
  $(".a_class").click(function(){ $(this).hide("slow"); });
});

function fname($a, $b) {
  echo $a * $b;
}