Exporting data to excel (multiple sheets) using php

Question

I am trying to export data to Excel using this PHP Class, so far things are working fine and the export is being generated. But now I have a new requirement of generating multiple sheets inside a single excel file.

For example if i have two arrays, i want both to be on separate sheets.

$myarray1 =  array (
       1 => array ("Oliver", "Peter", "Paul"),
            array ("Marlene", "Mica", "Lina")
    );

$myarray2 =  array (
       1 => array ("Oliver", "Peter", "Paul"),
            array ("Marlene", "Mica", "Lina")
    );

At present both arrays are being exported on a single sheet

$xls = new Excel_XML;
$xls->addArray ( $myarray );
$xls->addArray ( $myarray2 );
$xls->generateXML ( "testfile" );

I am wondering if someone tried this before and was able to achieve it and I will appreciate any help I can get on this.

Answer 1

i would suggest you to use PHPExcel library.supports variety of formats, can do visual formatting and is easy to use. You can find more about it at their webpage: http://phpexcel.codeplex.com/

You can do a lot more of course, reading excel files, setting visual styles, creating plots, expressions and lot more.

you can even use fgetcsv http://php.net/manual/en/function.fgetcsv.php

Answer 2

this example using PHPExcel

function exportToExcelsheets($data, $fileName){
    /* Create new PHPExcel object*/
    $objPHPExcel = new PHPExcel();
    $sheet_index = 0;
    foreach ($data as $s=>$sheet){
        /* Create a first sheet, representing sales data*/
        $alpha = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','V','W','X','Y','Z'];
        $objPHPExcel->setActiveSheetIndex($sheet_index);
        $head_keys = array_keys($sheet[0]);
        foreach ($head_keys as $a=>$headval){
            $objPHPExcel->getActiveSheet()->setCellValue($alpha[$a].'1', $headval);
        }
        $i=2;
        foreach($sheet as $row) {
            $index = 0;
            foreach ($row as $v=>$value){
                $value =  isset($value)?$value:'';
                $objPHPExcel->getActiveSheet()->setCellValue($alpha[$index].$i,$value);
                $index++;
            }
            $i++;
        }
        /*Rename sheet*/
        $objPHPExcel->getActiveSheet()->setTitle('sheet_'.$s);
        /* Create a new worksheet, after the default sheet*/
        $objPHPExcel->createSheet();
        $sheet_index++;
    }

    /* Redirect output to a client’s web browser (Excel5)*/
    header('Content-Type: application/vnd.ms-excel');
    header("Content-Disposition: attachment; filename=\"$fileName\"");
    header('Cache-Control: max-age=0');
    //$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    //$objWriter->save('php://output');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
}