Syntax ci untuk export database ke excel

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

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

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

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

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

* Use php spreadsheet writer to write data to the sheet

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

* 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