CODEWITHSUNDEEP
phphtmlgoogle-sheetsphpexcel
wdwtest
wdwtest

Reputation: 11

PHPExcel : Create multiple sheets from different html files

We are using PHP Excel library to create excel file with multiple sheets. We need to load multiple html page contents in multiple sheets. We strong text have successfully implemented 1 sheet with dynamic data from HTML. When I add second sheets it returns excel with latest sheet added only.

Below is code

        <?php 
                require_once './PHPExcel/Classes/PHPExcel.php';
                require_once './PHPExcel/Classes/PHPExcel/IOFactory.php';
                   // Create new PHPExcel object
                    $objPHPExcel = new PHPExcel();
                    //Create a first sheet, representing sales data
                    $objReader = PHPExcel_IOFactory::createReader('HTML');


                    $objPHPExcel->setActiveSheetIndex(0);
                    //$objPHPExcel->getActiveSheet()->setCellValue('A1', 'aaaaaaa');
                    $objPHPExcel= $objReader->load('./report_html/report1.html');
                    // Rename sheet
                    $objPHPExcel->getActiveSheet()->setTitle('Sheet1 ');

                    //----------------------------------------

                    $objWorkSheet=$objPHPExcel->createSheet(1);

                    // Add some data to the second sheet, resembling some different data types
                    $objPHPExcel->setActiveSheetIndex(1);
                    // Rename 2nd sheet
                    //$objPHPExcel->getActiveSheet()->setCellValue('A1','bbbbbbb ');
                    $objPHPExcel= $objReader->load('./report_html/report2.html');
                    $objPHPExcel->getActiveSheet()->setTitle('sheet2');

                    //$objWorkSheet->setTitle('Second sheet');

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

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

                ?>

Upvotes: 0

Views: 2277

Answers (1)

Mark Baker
Mark Baker

Reputation: 212412

The load() method is designed to load an entire file into a new PHPExcel object, not to load new sheets into an existing PHPExcel object, so when you call it a second time, you're replacing what you already had from the first load, not adding to it.

My recommended option would be to load the second file into a second PHPExcel object, and then use the addExternalSheet() method to transfer the worksheet to your first PHPExcel object, which will add it as a second worksheet..... this mechanism is specifically written to ensure that there are no clashes in the styles, and that the new sheet retains all its existing styling.

An alternative is to use the Reader's loadIntoExisting() method, and as you're loading HTML documents, then this shouldn't result in any style clashes.

Upvotes: 1

Related Questions