How to customize the excel data's with two sheets in one excel with php?

Question

I am using codeigniter framework, in this I am creating excel file with MySQL data's. I need to create header table (ie, first loop) data's into first sheet, detail table (ie, second loop) data's into second sheet. Below I have given my code, this generating in same sheet with next next data's. Can any one give some ideas to solve this.

$out = '"S.no","HeaderID","InvoiceID","InvoiceNo","doc_no","InvoiceDate","PartyCode","doc_type","CurrencyID","Remarks","loc_amt","doc_amt"'."\r\n";
        $i=1;       
        foreach($export_list as $d)
        {
            $out .= $i.',"'.$d->slsid.'","'.'0'.'","'.$d->reference_no.'","'.' '.'","'.$d->date.'","'.$d->customer_code.'","'.' '.'","'.' '.'","'.$d->internal_note.'","'.'0'.'","'.$d->total.'"'."\r\n";
            $i++;                   
        }       

        $out .= '"S.no","HeaderID","DetailID","ProductID","Description","Qty","loc_amt","doc_amt"'."\r\n";
        $i=1;       
        foreach($export_detail as $d)
        {
            $out .= $i.',"'.$d->sale_id.'","'.$d->id.'","'.$d->product_code.'","'.' '.'","'.$d->quantity.'","'.'0'.'","'.$d->gross_total.'"'."\r\n";
            $i++;                   
        }

        header('Content-Description: File Transfer');
        header('Content-Type: application/octet-stream'); 
        header('Content-Disposition: attachment; filename=Users.xls');
        header('Content-Transfer-Encoding: binary');
        header('Expires: 0');
        header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
        header('Pragma: public');
        echo "\xEF\xBB\xBF"; // UTF-8 BOM
        echo $out; 
        exit;

Thanks in advance.

For that i need to download the PHPExcel core files and add to my modules right? Then how to form excel file. If you have any link give that, i'll follow that. — Jagan Akash, Sep 08 '15 at 11:07

score 1 · Accepted Answer · answered Sep 08 '15 at 11:24

Yes have to download PHPExcel library for codeigniter.

I have some sample code which help you to work on PHPExcel.

    function test_excel()
        {
            $this->load->library('excel');
            $this->excel->setActiveSheetIndex(0);
            $this->excel->getActiveSheet()->setTitle('test worksheet');
            $this->excel->getActiveSheet()->setCellValue('A1', 'User id');
            $this->excel->getActiveSheet()->setCellValue('B1', 'User name');
            $this->excel->getActiveSheet()->setCellValue('C1', 'Email');
            $this->excel->getActiveSheet()->setCellValue('D1', 'Status');
            $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
            $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
            $this->excel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);
            $this->excel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);
            $query = $this->db->query("SELECT * FROM users");
            $k=2;
            foreach($query->result_array() as $row)
            {
                $this->excel->getActiveSheet()->setCellValue("A".$k, $row['USER_ID']);
                $this->excel->getActiveSheet()->setCellValue("B".$k, $row['FIRST_NAME']);
                $this->excel->getActiveSheet()->setCellValue("C".$k, $row['USER_EMAIL']);
                $this->excel->getActiveSheet()->setCellValue("D".$k, $row['USER_TYPE_ID']);
                $k++;
            }
            $filename='just_some_random_name.xls'; 
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="'.$filename.'"'); 
            header('Cache-Control: max-age=0'); //no cache

            $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  

            $objWriter->save('php://output');
        }

Hope it will help you.

For this code i need to add any core modules, or i just i can use this. — Jagan Akash, Sep 08 '15 at 12:24
You have to download PHPExcel library, and place in third_party folder and add this code in your module. — Keval Rathi, Sep 08 '15 at 12:27
i'll accept, dont worry. I have one doubt, in this you have show get from single query to form a loop and make excel. But i've two queries from different tables. For that how can i split the cells and add it. — Jagan Akash, Sep 08 '15 at 12:36
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/89066/discussion-between-keval-rathi-and-jagan-akash). — Keval Rathi, Sep 08 '15 at 12:38
I have checked this code, it shows error for me. Unable to load the requested class: excel — Jagan Akash, Sep 09 '15 at 04:38
I have referred this link (http://stackoverflow.com/questions/3269345/how-to-generate-an-excel-document-with-multiple-worksheets-from-php) and done the excel. Below code i did not check. Because I cant wait in long time. Thank you so much for your update. — Jagan Akash, Sep 11 '15 at 12:19
@JaganAkash before asking any question just check any similar type of question will help you or not, if not then post question — Keval Rathi, Sep 12 '15 at 09:22

score 0 · Answer 2 · answered Sep 10 '15 at 09:50

Hi @Jagan Akash check this,

public function test_excel() 
    { 
        $this->load->library('excel');
        $this->excel->setActiveSheetIndex(0);
        $this->excel->getActiveSheet()->setTitle('test worksheet');

        $this->excel->getActiveSheet()->setCellValue('A1', 'User id');
        $this->excel->getActiveSheet()->setCellValue('B1', 'User name');
        $this->excel->getActiveSheet()->setCellValue('C1', 'Email');
        $this->excel->getActiveSheet()->setCellValue('D1', 'Status');
        $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);

        $query = $this->db->query("SELECT * FROM user limit 2");
        $k=2;
        foreach($query->result_array() as $row)
        {
            $this->excel->getActiveSheet()->setCellValue("A".$k, $row['user_id']);
            $this->excel->getActiveSheet()->setCellValue("B".$k, $row['first_name']);
            $this->excel->getActiveSheet()->setCellValue("C".$k, $row['last_name']);
            $this->excel->getActiveSheet()->setCellValue("D".$k, $row['user_email']);
            $k++;
        }

        $init_cnt = $this->excel->getSheetCount();

        $this->excel->createSheet($init_cnt);
        $this->excel->setActiveSheetIndex($init_cnt);
        $this->excel->getActiveSheet()->setTitle('test 1');

        $this->excel->getActiveSheet()->setCellValue('A1', '1');
        $this->excel->getActiveSheet()->setCellValue('B1', '2');
        $this->excel->getActiveSheet()->setCellValue('C1', '3');
        $this->excel->getActiveSheet()->setCellValue('D1', '4');
        $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);

        $query = $this->db->query("SELECT * FROM user limit 2,2");
        $k=2;
        foreach($query->result_array() as $row)
        {
            $this->excel->getActiveSheet()->setCellValue("A".$k, $row['user_id']);
            $this->excel->getActiveSheet()->setCellValue("B".$k, $row['first_name']);
            $this->excel->getActiveSheet()->setCellValue("C".$k, $row['last_name']);
            $this->excel->getActiveSheet()->setCellValue("D".$k, $row['user_email']);
            $k++;
        }

        $init_cnt = $this->excel->getSheetCount();

        $this->excel->createSheet($init_cnt);
        $this->excel->setActiveSheetIndex($init_cnt);
        $this->excel->getActiveSheet()->setTitle('test 2');

        $this->excel->getActiveSheet()->setCellValue('A1', '12');
        $this->excel->getActiveSheet()->setCellValue('B1', '22');
        $this->excel->getActiveSheet()->setCellValue('C1', '32');
        $this->excel->getActiveSheet()->setCellValue('D1', '42');
        $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);

        $query = $this->db->query("SELECT * FROM user limit 4,2");
        $k=2;
        foreach($query->result_array() as $row)
        {
            $this->excel->getActiveSheet()->setCellValue("A".$k, $row['user_id']);
            $this->excel->getActiveSheet()->setCellValue("B".$k, $row['first_name']);
            $this->excel->getActiveSheet()->setCellValue("C".$k, $row['last_name']);
            $this->excel->getActiveSheet()->setCellValue("D".$k, $row['user_email']);
            $k++;
        }

        $this->excel->setActiveSheetIndex(0);

        $filename='just_some_random_name.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
        $objWriter->save('php://output');
    }

This will give answer to all question.

How to customize the excel data's with two sheets in one excel with php?

2 Answers2