I'm about to migrate the PhpExcel to PhpSpreadsheet using Codeigniter 3.1.11 Previously I use a PHP class file named Excel.php which helps to generate Excel using PhpExcel but now I want to connect it using PhpSpreadsheet.
The code is as follow
class Excel extends PHPExcel{
public $alphabet;
/**
* Constructor
*
* Responsible for initializing library class
*
* @access public
* @return void
*/
public function __construct() {
parent::__construct();
$this->alphabet = range('A', 'Z');
$this->alphabet[26] = 'AA';
$this->alphabet[27] = 'AB';
$this->alphabet[28] = 'AC';
$this->alphabet[29] = 'AD';
$this->alphabet[30] = 'AE';
$this->alphabet[31] = 'AF';
$this->alphabet[32] = 'AG';
$this->alphabet[33] = 'AH';
$this->alphabet[34] = 'AI';
$this->alphabet[35] = 'AJ';
$this->alphabet[36] = 'AK';
$this->alphabet[37] = 'AL';
$this->alphabet[38] = 'AM';
}
/**
* Responsible for generating excel
*
* Column_format array is use as a column name having
* number => '#,##0.00'
* number => '0.00'
* number => '0'
*
* @access public
* @param array, array, array, array, Boolean
* @return void
*/
private function generate_excel_sheet($header_data, $content_data, $column_width = NULL, $column_format = NULL, $border = FALSE){
//make 2st row become bold
$this->getActiveSheet()->getStyle('A2:'.$this->alphabet[count($header_data)-1].'2')->getFont()->setBold(true);
//Wraping text
$this->getActiveSheet()->getStyle('A2:'.$this->alphabet[count($header_data)-1].'2')->getAlignment()->setWrapText(true);
//Setting column width
if(!empty($column_width)){
foreach($column_width as $column => $size){
$this->getActiveSheet()->getColumnDimension($column)->setWidth($size);
}
}
//Merger table header and content
$excel_data[0] = $header_data;
if(!empty($content_data))
$excel_data = array_merge($excel_data,$content_data);
$this->getActiveSheet()->fromArray($excel_data, null, 'A2'); //Adding table data from A2 cell
//Setting column formate as Number
if(!empty($column_format)){
$last_row = $this->getActiveSheet()->getHighestRow();
foreach($column_format as $column => $format){
if(isset($format[0]) && $format[0] == 'Number')
$this->getActiveSheet()->getStyle($column)->getNumberFormat()->setFormatCode($format[1]);
if(isset($format[0]) && $format[0] == 'Date')
$this->getActiveSheet()->getStyle($column.'3:'.$column.$last_row)->getNumberFormat()->setFormatCode($format[1]);
}
}
//Creating Border if $border = TRUE
if($border){
$styleArray = array( 'borders' => array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
)
)
);
$this->getActiveSheet()->getStyle('A2:'.$this->alphabet[count($header_data)-1].(count($excel_data)+1))->applyFromArray($styleArray);
}
}//end function generate_excel(...)
/**
* This is the main class to create excel we pass the Excel name, Table data, Sheet Name and Sheet Heading
* Where Table data is a multidimensional associative array detials are as follow
* Sheet
* $table_data[0][table_heading]= array();
* $table_data[0][contant_data] = array();
* $table_data[0][column_width] = array() (by default NULL);
* $table_data[0][column_format]= array() (by default NULL);
* $table_data[0][table_border] = Boolean (by default FALSE);
*
* $sheet_name[0] = 'sheet1';
* $sheet_heading[0] = 'This is a testing heading'
*
* @access public
* @param string, array, array, array
* @return void
*/
public function create_excel($excel_name = 'new_excel', $table_data = NULL, $sheet_name = NULL, $sheet_heading = NULL){
foreach($table_data as $key => $row){
//Create a new worksheet, after the default sheet
if($key > 0) $this->createSheet();
//Set Active Sheet
$this->setActiveSheetIndex($key);
//name the worksheet
$sheet = $this->getActiveSheet();
$sheet->setTitle($sheet_name[$key]);
//Getting last column name
$last_column = $this->alphabet[count($table_data[$key]['table_heading'])-1];
//heading worksheet
$sheet->setCellValue('A1', $sheet_heading[$key]);
$sheet->mergeCells('A1:'.$last_column.'1'); //Need to make it dynamic
$sheet->getStyle('A1:'.$last_column.'1')->getFont()->setSize(20);
$sheet->getStyle('A1:'.$last_column.'1')->getFont()->setBold(true);
$sheet->getStyle('A1:'.$last_column.'1')->getAlignment()->applyFromArray(array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
$header_data = NULL;
$content_data = NULL;
$column_width = NULL;
$column_format = NULL;
$border = FALSE;
if(!empty($table_data[$key]['table_heading']))
$header_data = $table_data[$key]['table_heading'];
if(!empty($table_data[$key]['content_data']))
$content_data = $table_data[$key]['content_data'];
if(!empty($table_data[$key]['column_width']))
$column_width = $table_data[$key]['column_width'];
if(!empty($table_data[$key]['column_format']))
$column_format = $table_data[$key]['column_format'];
$table_border = $table_data[$key]['table_border'];
$this->generate_excel_sheet($header_data, $content_data, $column_width, $column_format, $table_border);
}//end foreach($table_data as $key => $value)
//Print excel here;
$this->print_excel($excel_name);
}//end function create_excel(...)
/**
*
*/
public function print_excel($excel_name){
//Creating header
header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename='.$excel_name.'.xlsx');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($this, 'Excel2007');
$objWriter->save('php://output');
}
}// END Excel CLASS
/* End of file Excel.php */
/* Location: ./application/libraries/Excel.php */
I called the above code using following function
/**
* Download Excel file
*
* @access private
* @param array
* @return void
*/
private function download_excel($excel_data){
$excel_name = $this->controller_name.'_'.date('Y-m-d');
$sheet_name = array('Report Salary');
$sheet_heading = array('Report Salary');
$header_data = array( 'S. No.', 'Title', 'First Name', 'Last Name', 'salary');
$content_data = array();
//Setting content data
$i =0;
foreach($excel_data as $row){
$i++;
$row_content = array();
$row_content[] = $i;
$row_content[] = $row->title;
$row_content[] = html_entity_decode($row->first_name, ENT_QUOTES, "UTF-8");
$row_content[] = html_entity_decode($row->last_name, ENT_QUOTES, "UTF-8");
$row_content[] = html_entity_decode($row->salary, ENT_QUOTES, "UTF-8");
$content_data[] = $row_content;
}
//Set number format
//$column_format = NULL;
$column_format = array('E' => array('Number','#,##0'));
//Set excel column width
$column_width = array( 'A' => 10, 'B' => 6, 'C' => 30, 'D' => 30, 'E' => 8);
$table_data[0]['table_heading']= $header_data;
$table_data[0]['content_data'] = $content_data;
$table_data[0]['column_width'] = $column_width;
$table_data[0]['column_format']= $column_format;
$table_data[0]['table_border'] = TRUE;
//Generate excel
$this->excel->create_excel($excel_name, $table_data, $sheet_name, $sheet_heading);
}//end function download()
Could any one help to convert it?
Thanks
Finally, I solve it myself. I hope it helps others too. Just to update here Excel.php file is written by me which helps to make it easier to use the PhpSpreadsheet library. And I used to put the Excel.php file in Library Directory in the Codeigniter table structured.
<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
/**
* An open source application development framework for PHP 7.3 or newer
*
* @author Sameer Naqvi
* @since Version 2.0
* @filesource
*/
// ------------------------------------------------------------------------
/**
* Excel Class
*
* This library holds the functions which helps to generate the Excel in (xlsx formate)
*
* @package Libraries
* @subpackage -
* @category Library
* @author Sameer Naqvi
*/
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
//use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
class Excel extends Spreadsheet{
public $alphabet;
/**
* Constructor
*
* Responsible for initializing library class
*
* @access public
* @return void
*/
public function __construct() {
parent::__construct();
$this->alphabet = range('A', 'Z');
$this->alphabet[26] = 'AA';
$this->alphabet[27] = 'AB';
$this->alphabet[28] = 'AC';
$this->alphabet[29] = 'AD';
$this->alphabet[30] = 'AE';
$this->alphabet[31] = 'AF';
$this->alphabet[32] = 'AG';
$this->alphabet[33] = 'AH';
$this->alphabet[34] = 'AI';
$this->alphabet[35] = 'AJ';
$this->alphabet[36] = 'AK';
$this->alphabet[37] = 'AL';
$this->alphabet[38] = 'AM';
}
/**
* Responsible for generating excel sheet
*
* Column_format array is use as a column name having
* Number => '#,##0.00'
* Number => '0.00'
* Number => '0'
* Date => 'yyyy-mm-dd'
*
* @access public
* @param array, array, array, array, Boolean
* @return void
*/
private function generate_excel_sheet($header_data, $content_data, $column_width = NULL, $column_format = NULL, $border = FALSE){
//make 2st row become bold
$this->getActiveSheet()->getStyle('A2:'.$this->alphabet[count($header_data)-1].'2')->getFont()->setBold(true);
//Wraping text
$this->getActiveSheet()->getStyle('A2:'.$this->alphabet[count($header_data)-1].'2')->getAlignment()->setWrapText(true);
//Alignment = Vertical_center
$this->getActiveSheet()->getStyle('A2:'.$this->alphabet[count($header_data)-1].'2')->getAlignment()->applyFromArray(array('vertical' => Alignment::VERTICAL_CENTER));
//Setting column width
if(!empty($column_width)){
foreach($column_width as $column => $size){
$this->getActiveSheet()->getColumnDimension($column)->setWidth($size);
}
}
//Merger table header and content
$excel_data[0] = $header_data;
if(!empty($content_data))
$excel_data = array_merge($excel_data,$content_data);
$this->getActiveSheet()->fromArray($excel_data, null, 'A2'); //Adding table data from A2 cell
//Setting column formate as Number or Date
if(!empty($column_format)){
$last_row = $this->getActiveSheet()->getHighestRow();
foreach($column_format as $column => $format){
if(isset($format[0]) && $format[0] == 'Number')
$this->getActiveSheet()->getStyle($column)->getNumberFormat()->setFormatCode($format[1]);
if(isset($format[0]) && $format[0] == 'Date')
$this->getActiveSheet()->getStyle($column.'3:'.$column.$last_row)->getNumberFormat()->setFormatCode($format[1]);
//$this->getActiveSheet()->getStyle($column.'3:'.$column.$last_row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_YYYYMMDD);
}
}
//Creating Border if $border = TRUE
if($border){
$styleArray = array(
'borders' => array(
'allBorders' => array(
'borderStyle' => Border::BORDER_THIN,
'color' => array('argb' => '000000'),
),
),
);
$this->getActiveSheet()->getStyle('A2:'.$this->alphabet[count($header_data)-1].(count($excel_data)+1))->applyFromArray($styleArray);
}
}//end function generate_excel(...)
/**
* This is the main class to create excel we pass the Excel name, Table data, Sheet Name and Sheet Heading
* Where Table data is a multidimensional associative array detials are as follow
* Sheet
* $table_data[0][table_heading]= array();
* $table_data[0][contant_data] = array();
* $table_data[0][column_width] = array() (by default NULL);
* $table_data[0][column_format]= array() (by default NULL);
* $table_data[0][table_border] = Boolean (by default FALSE);
*
* $sheet_name[0] = 'sheet1';
* $sheet_heading[0] = 'This is a testing heading'
*
* @access public
* @param string, array, array, array
* @return void
*/
public function create_excel($excel_name = 'new_excel', $table_data = NULL, $sheet_name = NULL, $sheet_heading = NULL){
foreach($table_data as $key => $row){
//Create a new worksheet, after the default sheet
if($key > 0) $this->getActiveSheet();
//Set Active Sheet
$this->setActiveSheetIndex($key);
//name the worksheet
$sheet = $this->getActiveSheet();
$sheet->setTitle($sheet_name[$key]);
//Getting last column name
$last_column = $this->alphabet[count($table_data[$key]['table_heading'])-1];
//heading worksheet
$sheet->setCellValue('A1', $sheet_heading[$key]);
$sheet->mergeCells('A1:'.$last_column.'1'); //Need to make it dynamic
$sheet->getStyle('A1:'.$last_column.'1')->getFont()->setSize(20);
$sheet->getStyle('A1:'.$last_column.'1')->getFont()->setBold(true);
$sheet->getStyle('A1:'.$last_column.'1')->getAlignment()->applyFromArray(array('horizontal' => Alignment::HORIZONTAL_CENTER));
$header_data = NULL;
$content_data = NULL;
$column_width = NULL;
$column_format = NULL;
$border = FALSE;
if(!empty($table_data[$key]['table_heading']))
$header_data = $table_data[$key]['table_heading'];
if(!empty($table_data[$key]['content_data']))
$content_data = $table_data[$key]['content_data'];
if(!empty($table_data[$key]['column_width']))
$column_width = $table_data[$key]['column_width'];
if(!empty($table_data[$key]['column_format']))
$column_format = $table_data[$key]['column_format'];
$table_border = $table_data[$key]['table_border'];
$this->generate_excel_sheet($header_data, $content_data, $column_width, $column_format, $table_border);
}//end foreach($table_data as $key => $value)
//Print excel here;
$this->print_excel($excel_name);
}//end function create_excel(...)
/**
* This function helps to print or downlaod excel
*/
public function print_excel($excel_name){
//Creating header
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename='.$excel_name.'.xlsx');
header('Cache-Control: max-age=0');
$writer = new Xlsx($this);
ob_end_clean();
$writer->save('php://output');
exit;
}
/**
* This function is responsible to convert php date into excel date
* Then we can apply excel formating on date field
*
* @access public
* @param data (yyyy-mm-dd)
* @return int (excel number e.g. 2008-12-31 to 39813)
*/
public function convert_date_php_2_excel($php_date){
if(isset($php_date) && trim($php_date)!= "")
return intval(25569 + strtotime($php_date) / 86400);
return NULL;
}
}// END Excel CLASS
/* End of file Excel.php */
/* Location: ./application/libraries/Excel.php */