read all sheets in xls and xlsx file using PHPExcel

Question

hi im working on a project that displays the content of an excel file using php so far here is the code im working on

$inputFileType = 'Excel5'; // Excel2007 for xlsx
$inputFileName = $opendoc;

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);

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

my problem is how to display the other sheets because only one sheet is being displayed any ideas? thanks so much in advance

score 2 · Answer 1 · answered May 23 '16 at 06:23

2

Try adding the following line after creating the reader and before loading the file:

$objReader->setLoadAllSheets();

so it becomes:

$inputFileType = 'Excel5'; // Excel2007 for xlsx
$inputFileName = $opendoc;

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setLoadAllSheets();
$objPHPExcel = $objReader->load($inputFileName);

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

answered May 23 '16 at 06:23

Itai Bar-Haim

1,686
16
40

hi! for example i have 2 sheets what it did was loaded the 1st sheet twice. any ideas? – BourneShady May 23 '16 at 06:29
Try saving to a different format, see if it exports the sheets correctly. If so, then there's a bug in the save HTML implementation (they do say in the documentation it has its limitations). If it does work, and there is a bug in saving HTML, perhaps you can roll your own, by saving each and every sheet to HTML separately in a loop. – Itai Bar-Haim May 23 '16 at 06:31

score 2 · Answer 2 · answered May 23 '16 at 06:57

By default, the HTML Writer only displays a single sheet (the current active worksheet).

You can change that by calling the Writer's writeAllSheets() method before saving.

$objw = PHPExcel_IOFactory::createWriter($objPHPExcel, 'HTML');
$objw->writeAllSheets();
$objw->save('php://output');

read all sheets in xls and xlsx file using PHPExcel

2 Answers2