How to read and write Excel file in CakePHP 3.X

ByShubham Jain

How to read and write Excel file in CakePHP 3.X

How to read and write Excel file in CakePHP 3.X

PHPExcel library was the best library available in PHP to read and write Excel files. Now a new library PHPSpreadsheet is here with latest updates and support. PHP Spreadsheet is written in pure PHP and allow you to read from and to write to different spreadsheet file formats, like Excel and LibreOffice Calc.

PHPSpreadsheet is very simple to use in any PHP application. We are showing steps how to use PHP Spreadsheet in your CakePHP 3.X application.

  1. Install composer if not installed on your system. Follow this link to install composer
  2. Install PHPSpreadsheet in your project. Ex. for CakePHP 3.X , go to vendor folder and run composer require phpoffice/phpspreadsheet
  3. After installation import the library in your PHP file where you want to perform the action. See below to import in CakePHP 3.X
  4. Now you can use the various features of PHPSpreadsheet library. To use this library in CakePHP 3.X, examples are given below with source code.

 

Step 1:- Download the spreadsheet library.

The easy way to download library, first go to vendor folder and open. press shift+right click and click on(Open Powershell window here).go to the link and see the code which we marked .just copy and paste into the spreadsheet.And the link is

https://phpspreadsheet.readthedocs.io/en/develop/

go to the link and install PhpSpreadsheet.

Composer install php spreadsheet

Composer install php spreadsheet

Step 2:- Check library is installed on your project.

After running the above command, the PHPSpreadsheet library installed with name PHPOffice as shown in image below –

 

Step 3:- Create an XLSX file by below code in CakePHP-

controller :-EmployeesController.php

<?php 
namespace App\Controller; 
use Cake\ORM\TableRegistry;
use App\Model\Table\User; // <—My model
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Helper;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
require ROOT.DS. ‘vendor’ .DS. ‘phpoffice/phpspreadsheet/src/Bootstrap.php’ ;
class EmployeesController extends AppController {
public function exportExcelfile(){
$spreadsheet = new Spreadsheet();
$helper = new Helper\Sample();
$helper ->log( ‘Create new Spreadsheet object’ );
$spreadsheet  = new Spreadsheet();
//Set document properties 
$helper ->log(‘Set document properties’);
$spreadsheet ->getProperties()
->setCreator(‘shubh ‘)
->setLastModifiedBy(‘Arjun’)
->setTitle(‘Example’)
->setSubject(‘Example’)
->setDescription(‘Example’)
->setKeywords(‘office PhpSpreadsheet php’)
->setCategory(‘Example’);
// Add some data 
$helper ->log(‘Add some data’);
$spreadsheet ->setActiveSheetIndex(0)
->setCellValue(‘A1’, ‘Hello’)
->setCellValue(‘B2’, ‘world!’)
->setCellValue(‘C1’, ‘Hello’)
->setCellValue(‘D2’, ‘world!’);
$helper ->log(‘Rename worksheet’);
$spreadsheet ->getActiveSheet()
->setTitle(‘Simple’);
$writer = new Xlsx($spreadsheet);
$writer ->save(WWW_ROOT . ‘/files/ example.xlsx’);
die;
}
}

Step 4:- Read an XLS file by below code in CakePHP-

controller :-EmployeesController.php

<?php 
namespace App\Controller;
use PhpOffice\PhpSpreadsheet\IOFactory;
use Cake\ORM\TableRegistry;
use App\Model\Table\User; // <—My model
use Cake\Datasource\ConnectionManager;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Helper;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
require ROOT.DS.’vendor‘ .DS. ‘phpoffice/phpspreadsheet/src/Bootstrap.php’;

class EmployeesController extends AppController {

public function importExcelfile (){
$helper = new Helper\Sample();
debug($helper);
$inputFileName = WWW_ROOT . ‘example1.xls‘;
$spreadsheet = IOFactory::load($inputFileName);
$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
var_dump($sheetData);
die(“here”);
}
}

 

Leave comments below if you face any difficulty in reading and writing Excel files in your PHP application.

 

About the author

Shubham Jain administrator

2 Comments so far

JariPosted on3:29 pm - Nov 7, 2018

How to get the file without using Writer?

ChanduPosted on5:55 pm - Apr 5, 2019

Thanks a lot. It saved my day.

Leave a Reply