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:
Example write excel

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:
DomainCategoryNr. Pages
CoursesWeb.netWeb Development4000
MarPlo.netCourses & Games15000

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:
Excel pie chart

- 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.

Daily Test with Code Example

HTML
CSS
JavaScript
PHP-MySQL
Which attribute specifies the URL address where to send the form-data?
method action name
<form action="script.php" method="post"> ... </form>
Which CSS property can be used to break lines in the middle of words?
word-wrap line-height font-size
#id {
  width: 100px;
  word-wrap: break-word;
}
Which function sorts the elements of an array into alphabetical order, based on the string values?
pop() sort() shift()
var tutorials = ["php", "html", "css", "flash"];
tutorials.sort();
alert(tutorials[0]);          // css
Indicate the function that returns the value of the last element into an array.
current() next() end()
$code =[10=>"Perl", 20=>"PHP", 21=>"Python", 30=>"JavaScript");
$last = end($code);
echo $last;      // JavaScript
PhpSpreadsheet - Read, Write Excel and LibreOffice Calc files

Last accessed pages

  1. JavaScript Course - Free lessons (23091)
  2. JavaScript Game - Find the Word (385)
  3. html2canvas - Save page screenshoot on server (2501)
  4. HTML Course - Free Lessons (18447)
  5. AJAX Course, free Lessons (13264)

Popular pages this month

  1. Courses Web: PHP-MySQL JavaScript Node.js Ajax HTML CSS (168)
  2. PHP-MySQL free course, online tutorials PHP MySQL code (118)
  3. Read Excel file data in PHP - PhpExcelReader (113)
  4. Create simple Website with PHP (82)
  5. SHA1 Encrypt data in JavaScript (76)