PHP read and write data in Excel format

I have seen a lot of people search and ask how to read and write data to Excel file format. There was a library available PHPExcel which is deprecated so you should download PHPSpreadSheet library from GitHub.

Composer

You can download this library directly in your project by using composer. Open the terminal in the project root and hit the following command:

composer require phpoffice/phpspreadsheet

This will download this library directly in your project. It will create a vendor name folder.

There are people who don’t know how to use this library to read and write data I will tell how can you use this library in your project. Here are the following methods to read and writeXLS, XLSX, CSV, XML, HTML, ODS files.

Read Excel File

Method 1: using IOFactory class recommended, you can read XLS, XLSX, CSV, XML, HTML, ODS format files.

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileName = '/path/to/file/example1.xls';
$inputFileType = 'Xls';   // input file type Csv, Xls, Xlsx, Xml, Ods, Html
$reader = IOFactory::createReader($inputFileType);
$spreadsheet = $reader->load($inputFileName);
$data = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);

var_dump( $data );

Method 2: using Xls class.

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Reader\Xls;

$inputFileName = '/path/to/file/example1.xls';

$reader = new Xls();
$spreadsheet = $reader->load($inputFileName);
$data = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);

var_dump( $data );

Write File

Method 1: Write XLSX file.

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Name');
$sheet->setCellValue('B1', 'Email');

$array = array(
 array(
 'name' => 'Arun',
 'email' => '[email protected]',
 'phone' => '9288483494'
 ),
 array(
 'name' => 'Amit',
 'email' => '[email protected]',
 'phone' => '9288483494'
 )
 );

$i = 2;
foreach( $array as $row ) {
 $sheet->setCellValue( 'A' . $i, $row['name'] );
 $sheet->setCellValue( 'B' . $i, $row['email'] );
 $i++;
}

$writer = new Xlsx($spreadsheet);
$writer->save('contact.xlsx');

Method 2: Write XLS/XLSX file using IOFactory class.

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Name');
$sheet->setCellValue('B1', 'Email');

$array = array(
 array(
 'name' => 'Arun',
 'email' => '[email protected]',
 'phone' => '9288483494'
 ),
 array(
 'name' => 'Amit',
 'email' => '[email protected]',
 'phone' => '9288483494'
 )
 );

$i = 2;
foreach( $array as $row ) {
 $sheet->setCellValue( 'A' . $i, $row['name'] );
 $sheet->setCellValue( 'B' . $i, $row['email'] );
 $i++;
}

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, "Xls"); // you can create xlsx as well
$writer->save('contact.xls');

Method 3: Write Html file using HTML class.

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Html;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Name');
$sheet->setCellValue('B1', 'Email');

$array = array(
 array(
 'name' => 'Arun',
 'email' => '[email protected]',
 'phone' => '9288483494'
 ),
 array(
 'name' => 'Amit',
 'email' => '[email protected]',
 'phone' => '9288483494'
 )
 );

$i = 2;
foreach( $array as $row ) {
 $sheet->setCellValue( 'A' . $i, $row['name'] );
 $sheet->setCellValue( 'B' . $i, $row['email'] );
 $i++;
}

$writer = new Html($spreadsheet);
$writer->save("05featuredemo.htm");

Method 4: Write Ods file using Ods class.

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Ods;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Name');
$sheet->setCellValue('B1', 'Email');

$array = array(
 array(
 'name' => 'Arun',
 'email' => '[email protected]',
 'phone' => '9288483494'
 ),
 array(
 'name' => 'Amit',
 'email' => '[email protected]',
 'phone' => '9288483494'
 )
 );

$i = 2;
foreach( $array as $row ) {
 $sheet->setCellValue( 'A' . $i, $row['name'] );
 $sheet->setCellValue( 'B' . $i, $row['email'] );
 $i++;
}

$writer = new Ods($spreadsheet);
$writer->save("05featuredemo.ods");

Comments

Be the first to comment