How to write only active sheet of xlsx file PHPSpreadsheet

Question

i want to show the xlsx file as html, but it always return the first sheet of the file. What i want is to get the writer to show the one active sheet only if there are multiple sheets (name and order of sheet are random). here is my code :

public function view_excel($path){
        $file = realpath(FCPATH)."/uploads/PKB/".$path;
        $reader = IOFactory::createReader('Xlsx');
        $spreadsheet = $reader->load($file);
        // $spreadsheet = $spreadsheet->getActiveSheet();
        $writer = IOFactory::createWriter($spreadsheet, 'Html');
        $message = $writer->save('php://output');
    }

is there a way to do this ? Thanks

score 0 · Accepted Answer · answered Jan 23 '22 at 11:27

i managed to do this :

public function view_excel($path){
        $file = realpath(FCPATH)."/uploads/PKB/".$path;
        $reader = IOFactory::createReader('Xlsx');
        $spreadsheet = $reader->load($file);
        $active_sheet = $spreadsheet->getActiveSheet()->getTitle();
        $reader->setLoadSheetsOnly($active_sheet); //only load active sheet
        $worksheet = $reader->load($file);
        $writer = IOFactory::createWriter($worksheet, 'Html');
        $message = $writer->save('php://output');
    }

had to load the file twice

score 0 · Answer 2 · answered Oct 19 '22 at 20:13

Here you go!

public function view_excel($path){
    $file = realpath(FCPATH)."/uploads/PKB/".$path;
    $reader = IOFactory::createReader('Xlsx');
    $spreadsheet = $reader->load($file);
    $writer = IOFactory::createWriter($spreadsheet, 'Html');

 -> $writer->setSheetIndex($spreadsheet->getActiveSheetIndex());

    $message = $writer->save('php://output');
}

How to write only active sheet of xlsx file PHPSpreadsheet

2 Answers2