Warm tip: This article is reproduced from stackoverflow.com, please click
codeigniter phpspreadsheet

Convert Codeigniter's Excel class from PHPExcel to PhpSpreadsheet

发布于 2020-03-31 22:57:42

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

Questioner
Sameer
Viewed
58
Sameer 2020-01-31 19:39

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 */