Export php array data into excel

Question

I am trying to export my array result into excel using php excel.I want to put this data into multiple sheets based array. For eg: In array, person_mast,person_ex should be sheet1 and sheet2 and their respective array should be put with array key as heading of columns and array values as data in multiple rows in excel file.

Problem :

Not able to loop through array and put values in cells.

My array :

Array
(
[person_mast] => Array
    (
        [0] => Array
            (
                [details_id] => 10470
                [head_id] => 1
                [recdate] => 2011-02-23 00:00:00
            )
        [1] => Array
            (
                [details_id] => 38708
                [head_id] => 1
                [recdate] => 
            )
    )

[person_ex] => Array
    (
        [0] => Array
            (
                [details_id] => 9842
                [head_id] => 19
                [visit_dt] => 2014-08-07 00:00:00
                [addinfo] => abc
            )

        [1] => Array
            (
                [details_id] => 10234
                [head_id] => 20
                [visit_dt] => 2007-11-20 00:00:00
                [addinfo] => 3 3
            )

    )
)

My Code :

    $filename = "report_".date('Ymd_His').".xls";
    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();

    $sheet_count = '0';
    foreach($patient_result as $tb_name => $tb_data){
        $sheetname = $tb_name ;
        if($sheet_count != '0'){
            // Create a new worksheet, after the default sheet
            $objPHPExcel->createSheet();
        }
        // Create a first sheet, representing sales data
        $objPHPExcel->setActiveSheetIndex($sheet_count);

        $column_count = 0;
        foreach($tb_data as $key => $curr_data){
                foreach($curr_data as $colname => $data){
                    //echo "<br>colname : ".$colname;
                    $objPHPExcel->getActiveSheet()->setCellValue($colname.$key, $data);
                    $column_count ++;
                }
        } 
        // Rename sheet
        $objPHPExcel->getActiveSheet()->setTitle($sheetname);

        $sheet_count++;

Expected Excel file format :

Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. — Alex
– Alex, Commented Aug 3, 2018 at 2:31
@Alex i have updated my code and have specified the problem and error. Please help.Thanks — piya
– piya, Commented Aug 3, 2018 at 3:46
Did you debug? Your error is not a specific error so I can't help you fix this. — crew
– crew, Commented Aug 3, 2018 at 4:48
Notice that sheet_count define as string - it should be int as column_count — dWinder
– dWinder, Commented Aug 3, 2018 at 7:07

Rahul kalal · Accepted Answer · 2018-08-04 05:21:12Z

2

Sheet number must be integer, increment operator work only with int val. must define header part (column name of each sheet) and need to pass sheet number to fill data which one currently in loop.

Just put this code and it will work 100%

$objPHPExcel = new PHPExcel();


        $filename = "report_".date('Ymd_His').".xls";

        $sheet_title = array('person_mast','person_ex');            

       $sheet_count = 0;
       foreach($patient_result as $tb_name => $tb_data){


            $sheet = $objPHPExcel->createSheet($sheet_count);

           $header_key = array_keys($tb_data[0]);

            $header = array($header_key[0], $header_key[1], $header_key[2]);
            $list = array ($header);
            //$this->excel->setActiveSheetIndex($sheet_count);
            $sheet->setTitle($sheet_title[$sheet_count]);

            $tmp_row = array();
            foreach($tb_data as $key => $curr_data){
                //echo '<pre>'; print_r( $curr_data); exit;
                $list[] = $curr_data;
            }

            $sheet->fromArray($list);
        $sheet_count++; //break;

    }


    header('Content-Type: application/vnd.ms-excel');
    //tell browser what's the file name
    header('Content-Disposition: attachment;filename="' . $filename . '"');

    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');

       $objWriter->save($filename);

edited Aug 4, 2018 at 5:21

answered Aug 3, 2018 at 7:02

Rahul kalal

516 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

dWinder Over a year ago

It is preferred to explain what the code in question was missing so one could learn...

Rahul kalal Over a year ago

Sheet number must be integer, increment operator work only with int val. must define header part (column name of each sheet) and need to pass sheet number to fill data which one currently in loop. please do upvote if it works thanks.

piya Over a year ago

@rahul i can't take header part as static. I want header to be made of inner array keys.

Rahul kalal Over a year ago

Somewhere you have to define your column name, my code is working fine now you have to use according to your requirement.

Rahul kalal Over a year ago

Just edited the code, try now hope it fulfill your requirement .

|

Collectives™ on Stack Overflow

Export php array data into excel

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related