PHPSpreadsheet Select Specific Sheet To Display in HTML

Question

The excel files has multiple tabs (sheets)

I would like to display a specific sheet or a way to navigate between sheets using a form submit button.

include 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

$file = 'Excel Files/Book1.xlsx';

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$excel = $reader->load($file);
$writer = IOFactory::createWriter($excel, 'Html');
$message = $writer->save('php://output');
echo $message;

score 0 · Answer 1 · answered Jul 01 '21 at 14:47

I found a workaround to this problem. I used removeSheetByIndex() so it will forced PHPSpreadsheet to load the last remaining sheet.

Here is my complete code

include 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

$file = 'Excel Files/Book1.xlsx';

##  GET ALL SHEETS OF EXCEL FILE
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$spreadsheet = $reader->load($file);
$array_sheet = $spreadsheet->getSheetNames();
##  DISPLAY ALL SHEETS
echo "<pre>\n";
print_r($array_sheet);
echo "</pre>\n";

##  DECLARE OR SET POST sheet and the index  depending what is submitted
if(!isset($_POST['sheet'])){
    ##  LOAD FIRST SHEET AS DEFAULT
    $_POST['sheet'] = $array_sheet[0];
    $index = 0;
}else{
    ##  SET SELECTED SHEET
    for($i = 0;$i < sizeof($array_sheet);$i++){
        if($_POST['sheet'] == $array_sheet[$i]){
            $_POST['sheet'] = $array_sheet[$i];
            $index = $i;
        }
    }
}
##  BLOCK FOR DISPLAY FROM EXCEL TO HTML
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$spreadsheet = $reader->load($file);
$spreadsheet->setActiveSheetIndexByName($_POST['sheet']);
##  LOOP TO DETERMINE WHICH SHEETS TO REMOVE
for($i = 0;$i < sizeof($array_sheet);$i++){
    ##  REMOVE SHEET
    if($i != $index){
        $spreadsheet->removeSheetByIndex($i);
    }
}
##  ADD TO WRITER
$writer = IOFactory::createWriter($spreadsheet, 'Html');
$message = $writer->save('php://output');
##  THIS WILL DISPLAY THE TABLE
echo $message;

##  FORM AND BUTTONS WHICH WILL BE USED TO NAVIGATE BETWEEN SHEETS
echo "<form method = \"POST\" action = \"\">\n";
for($i = 0;$i < sizeof($array_sheet);$i++){
    $style = "";
    if($array_sheet[$i] == $_POST['sheet']){
        $style = "background:red;";
    }
    echo "<button type = \"submit\" name = \"sheet\" value = \"".$array_sheet[$i]."\" style = \"$style\">".$array_sheet[$i]."</button>\n";
}
echo "</form>\n";

PHPSpreadsheet Select Specific Sheet To Display in HTML

1 Answers1