Tutorials

Article Content:export data to excel in php codeigniter

export data to excel in php codeigniter

export data to excel in php codeigniter.

If you remember the last article about integrate PhpWord library with codeigniter,i told you that Phpword is a part of Phpoffice library’s collection to dealing with Microsoft office product with a professional  way,so you can consider this tutorial as a part two of this series.

In this tutorial we will learn how to integrate Phpecxel library with codeigniter and how to export to excel in php,Phpexcel is  a set of classes for the PHP programming language, which allow you to write to and read from different spreadsheet file formats, like Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML, ... This project is built around Microsoft's OpenXML standard and PHP.

The earlier versions of this library was named as “Phpexcel” with codeplex under (LGPL) licence ,until 2015-08-10 it will moved to github and renamed as “PhpSpreadsheet”.So PhpSpreadsheet is the next version of PhpExcel. It breaks compatibility to dramatically improve the code base quality (namespaces, PSR compliance, use of latest PHP language features, etc.).

Because all efforts have shifted to PhpSpreadsheet, PHPExcel will no longer be maintained, However PhpSpreadsheet is still unstable and not yet released. So if you need stability stick to PhpExcel until this project is released.

 

PhpSpreadsheet  Requirements

-PHP version 5.2.0 or higher

-PHP extension php_zip enabled (required if you need PHPExcel to handle .xlsx .ods or .gnumeric files)

-PHP extension php_xml enabled

-PHP extension php_gd2 enabled (optional, but required for exact column width autocalculation)

File Formats supported

Reading

-BIFF 5-8 (.xls) Excel 95 and above

-Office Open XML (.xlsx) Excel 2007 and above

-SpreadsheetML (.xml) Excel 2003

-Open Document Format/OASIS (.ods)

-Gnumeric

-HTML

-SYLK

-CSV

Writing

-BIFF 8 (.xls) Excel 95 and above

-Office Open XML (.xlsx) Excel 2007 and above

-HTML

-CSV

-PDF (using either the tcPDF, DomPDF or mPDF libraries, which need to be installed separately).

for more details about spreadsheet library i recommended to read Full documentaion

Important Note 
I recommended to download the original source from here and go to http://localhost/PhpSpreadsheet/samples/ , if you see all requirements is green like this picture go ahead , if no, fix them and go on to the next step.

Installation:

1- Download the project from the bottom link page and extract it on localhost

2- Create new database, name it as (demo)

3- Import tables from application/tables

4- Go to the URL  http://localhost/codeigniter_demo/

Project structure:

 In this app we have:

1- Phpexcel controller

In this controller, we have two methods Index to show users table and download to export excel files from users table

2- Phpexcel model

To select our users from users table

3- Phpexcel  third_party

The original files for Phpexcel library, you can also download this package from their GitHub repo in this link, but I recommended to use it from my full example directly

4- Global folder

Contains assets file like bootstrap 3 main framework and starter theme with JavaScript ,HTML ,  CSS  files

5- Views folder

This contains views file for our layout templates with header and footer and content

 

How it works?

When you see the demo page you will find the download link on the bottom of the page, when you click on it you will invoke download function.

 In this function :

*  Fetch users and pass it to the variable $users

$subscribers = $this->phpexcel_model->get_users();

*  We load Phpexcel library

require_once APPPATH . '/third_party/Phpexcel/Bootstrap.php';

*  Create new Spreadsheet object

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

* Set document properties

$spreadsheet->getProperties()->setCreator('Webeasystep.com ')
			->setLastModifiedBy('Ahmed Fakhr')
			->setTitle('Phpecxel codeigniter tutorial')
			->setSubject('integrate codeigniter with PhpExcel')
			->setDescription('this is the file test');

Add style to the header

$styleArray = array(
'font' => array('bold' => true,),
'alignment' => array(
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,),
'borders' => array('top' => array(
'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,),,
'fill' => array(
'type' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
'rotation' => 90,
'startcolor' => array('argb' => 'FFA0A0A0',),'endcolor' => 
array('argb' => 'FFFFFFFF',),),);
$spreadsheet->getActiveSheet()->getStyle('A1:F1')->applyFromArray($styleArray);

* Auto fit column to content

foreach(range('A','F') as $columnID) {
			$spreadsheet->getActiveSheet()->getColumnDimension($columnID)
					->setAutoSize(true);
		}

* Set the names of header cells

$spreadsheet->setActiveSheetIndex(0)
				->setCellValue("A1",'Username')
				->setCellValue("B1",'Name')
				->setCellValue("C1",'UserEmail')
				->setCellValue("D1",'UserAddress')
				->setCellValue("E1",'UserJob')
				->setCellValue("F1",'Gender');

* Add some data

	// to start from the next line after header we set increment variable to 2	
$x= 2;
		foreach($subscribers as $sub){
			$spreadsheet->setActiveSheetIndex(0)
					->setCellValue("A$x",$sub['user_username'])
					->setCellValue("B$x",$sub['user_name'])
					->setCellValue("C$x",$sub['gender'])
					->setCellValue("D$x",$sub['user_email'])
					->setCellValue("E$x",$sub['user_address'])
					->setCellValue("F$x",$sub['user_job']);
			$x++;
		}

* Rename worksheet

$spreadsheet->getActiveSheet()->setTitle('Users Information');

* Set right to left direction

//		$spreadsheet->getActiveSheet()->setRightToLeft(true);

*  Set active sheet index to the first sheet, so Excel opens this as the first sheet

$spreadsheet->setActiveSheetIndex(0);

* Redirect output to a client’s web browser (Excel2007)

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="subscribers_sheet.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

* Use php spreadsheet writer to write data to the sheet

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Excel2007');

* Then export data to excel in php Using save function to save the xlsx file as temp file on the fly and then use header to download xlsx file and exit the function

$writer->save('php://output');
exit;

Try Demo  Download Code



  • shalaka
    Error Number: 1146 Table 'demo.ci_sessions' doesn't exist SELECT `data` FROM `ci_sessions` WHERE `id` = 'a77edb19011d5667126b22fec856e28100000327' Filename: libraries/Session/drivers/Session_database_driver.php Line Number: 166
    March 8, 2017
    Reply
    • admin
      [email protected]
      Hi , shalaka , you should import ci_sessions table to your database , you can copy and paste from here https://bitbucket.org/webeasystep/threaded-comments-codeigniter/src/2b97d8b39273d92446caf75a8d4773675b7407b5/application/tables/ci_sessions.sql?at=master&fileviewer=file-view-default
      March 8, 2017
      Reply
      • Ravindra
        [email protected]
        Hi, I did not understand Why this [ci_sessions] table required. Can we remove it? ,if yes then how?
        March 23, 2017
        Reply
        • admin
          [email protected]
          The ‘database’ driver uses a relational database such as MySQL or PostgreSQL to store sessions. This is a popular choice among many users, because it allows the developer easy access to the session data within an application - it is just another table in your database. In order to use the ‘database’ session driver, you must also create this table that we already mentioned and then set it as your $config['sess_save_path'] value. For example, if you would like to use ‘ci_sessions’ as your table name, you would do this: for more info i recommended read this page of codeigniter documentation https://www.codeigniter.com/user_guide/libraries/sessions.html#database-driver
          March 24, 2017
          Reply
  • sydney
    Please see error nelow after i click DOwnload.Thanks http://localhost/codeigniter-phpexcel/phpexcel/download Not Found The requested URL /codeigniter-phpexcel/phpexcel/download was not found on this server. Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.
    March 15, 2017
    Reply
  • admin
    Hi Sydny, link fixed but you can also download from here https://bitbucket.org/webeasystep/codeigniter-phpexcel/downloads/
    March 15, 2017
    Reply
  • SYDNEY
    Yes i already downloded this file , what i mean is that when i execute the code then i try to download excel file when i click the Downlaod button it that error will prompt.
    March 15, 2017
    Reply
    • admin
      [email protected]
      check your installation steps and check route path , i think this will fix the 404 error
      March 24, 2017
      Reply
  • Arslan Bilal
    Hi After following the steps when i open the downloaded file it shows me this error, "It may be damaged or use a file format that Preview doesn’t recognize."
    April 27, 2017
    Reply
    • admin
      [email protected]
      download the full code and compare steps, also check that your php version is under php 7
      April 28, 2017
      Reply

Leave a Reply

Your email address will not be published.


Notify me of followup comments via e-mail.
You can also Subscribe without commenting.