Generate excel sheet with multiple sheet in Codeigniter PHPExcel

Codeingiter is a powerfull MVC framework with small footprint for high end web applications. in this topic we are going to discuss about how to generate excel sheet in codeigniter and here also we will discuss how to work with multiple excel sheet also.

How to install:
1. Download PHPExcel library from this URL :https://github.com/PHPOffice/PHPExcel
2. Extract the downloaded zip to the folder application/library

Now you are ready to write the following code in your controller. The following code is the example where we will fetch all data from the respective tables as group, subgroup, brand, uqc and also one blank sheet will be there for product.

First of all fetch all data from the respective tables. Please use the following code where I have created product_model in which we have the respective functions.

public function getgroup(){
    $this->db->select('group_id,group_name');
    $data =$this->db->get_where('masters__group',array('deleted'=>0));
return $data->result();
}
public function getSubgroup($id){
$data=$this->db->select('*')
    ->from("masters__sub_group")
    ->where("group_id",$id)
    ->get();
return $data->result();

}
public function getBrand(){
 $data =$this->db->select('*')
     ->from('masters__brand')
     ->where('delete_status',"0")
     ->get();
 return $data->result();
}
public function getUqc($companyid,$branchid){
 $data=$this->db->select("*")
    ->from("masters__uqc")
    ->where("delete_status",0)
    ->where("company_id",$companyid)
    ->get();
 return $data->result();
}

Now we have to call the function in the controller to get all those data. Use the following code to get all data to the respective variables.

$group = $this->product_model->getgroup();
$subgroup = $this->product_model->getSubgroup();
$brand = $this->product_model->getBrand();
$uqc = $this->product_model->getUqc();  

Now set the sheet titles in an array.

$sheetsstitle=array("Group","Subgroup","Brand","UQC","Products");

Load the PHPExcellibrary in the controller.

$this->load->library('PHPExcel');
//$this->load->library('PHPExcel/iofactory');
$objPHPExcel = new PHPExcel();

Now we are ready to write the data to the excel sheet. The following code will create the sheets and add data to the respective sheets.

$i=0;
$columnarray = array(
'A',
'B',
'C',
'D',
'E',
'F',
'G',
'H',
'I',
'J',
'K');
while ($i < 5) {
    // Add new sheet
    $objWorkSheet = $objPHPExcel->createSheet($i); //Setting index when creating
    ////Write cells
    if($i==0){
        //group data
        $objWorkSheet->setCellValue('A1', 'Group ID');
        $objWorkSheet->setCellValue('B1', 'Group Name');
        $row=2;
        foreach($group as $idata){
            $col=0;
            $objWorkSheet->setCellValue($columnarray[$col].$row, $idata->group_id);
            $col++;
            $objWorkSheet->setCellValue($columnarray[$col].$row, $idata->group_name);
            $row++;
        }
    }
    elseif($i==1){
        //subgroup data
        $objWorkSheet->setCellValue('A1', 'Subgroup ID');
        $objWorkSheet->setCellValue('B1', 'Group Name');
        $objWorkSheet->setCellValue('C1', 'Subgroup Name');
        $row=2;
        foreach($subgroup as $idata){
            $col=0;
            $objWorkSheet->setCellValue($columnarray[$col].$row, $idata->sub_group_id);
            $col++;
            $objWorkSheet->setCellValue($columnarray[$col].$row, $idata->group_name);
            $col++;
            $objWorkSheet->setCellValue($columnarray[$col].$row, $idata->sub_group_name);
            $row++;
        }
    }
    elseif($i==2){
        //brand data
        $objWorkSheet->setCellValue('A1', 'Brand ID');
        $objWorkSheet->setCellValue('B1', 'Brand Name');
        $row=2;
        foreach($brand as $idata){
            $col=0;
            $objWorkSheet->setCellValue($columnarray[$col].$row, $idata->id);
            $col++;
            $objWorkSheet->setCellValue($columnarray[$col].$row, $idata->brand_name);
            $row++;
        }
   }
   elseif($i==3){
        //UQC data
        $objWorkSheet->setCellValue('A1', 'UQC ID');
        $objWorkSheet->setCellValue('B1', 'UQC Name');
        $objWorkSheet->setCellValue('C1', 'Description');
        $row=2;
        foreach($uqc as $idata){
            $col=0;
            $objWorkSheet->setCellValue($columnarray[$col].$row, $idata->id);
            $col++;
            $objWorkSheet->setCellValue($columnarray[$col].$row, $idata->uom);
            $col++;
            $objWorkSheet->setCellValue($columnarray[$col].$row, $idata->description);
            $row++;
        }
    }
    else{
        $objWorkSheet->setCellValue('A1', 'Product Code');
        $objWorkSheet->setCellValue('B1', 'Product Name');
        $objWorkSheet->setCellValue('C1', 'Group ID/Name');
        $objWorkSheet->setCellValue('D1', 'Subgroup ID/Name');
        $objWorkSheet->setCellValue('E1', 'Brand ID/Name');
        $objWorkSheet->setCellValue('F1', 'UQC ID/Name');
        $objWorkSheet->setCellValue('G1', 'Size');
        $objWorkSheet->setCellValue('H1', 'Alert Quantity');
        $objWorkSheet->setCellValue('I1', 'Product Detail');
    }
    // Rename sheet
    $objWorkSheet->setTitle($sheetsstitle[$i]);
    $i++;
}
$objPHPExcel->setActiveSheetIndex($i-1);
//Freeze pane
//$objPHPExcel->getActiveSheet()->freezePane('A2');
//Save as an Excel BIFF (xls) file
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="filename.xls"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache
//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
//if you want to save it as .XLSX Excel 2007 format
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
//force user to download the Excel file without writing it to server's HD
$objWriter->save('php://output');
//
exit();



That’s it. Now you are ready to download the genaerated excel sheet. If you are following the above steps correctly you can able to download the generated excel sheet in php codeigniter using PHPExcel.

Leave a Reply

Your email address will not be published. Required fields are marked *