How can I export HTML tables to multiple excel worksheets in php

Question

I am using php and I want to export two HTML tables to excel file with two sheets each has one table.

I followed documentation but it only create one sheet with one table.

 <?php
  use PhpOffice\PhpSpreadsheet\Spreadsheet;
  use PhpOffice\PhpSpreadsheet\IOFactory;
  use PhpOffice\PhpSpreadsheet\Reader\Html;
  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

  $firstHtmlString = '<table>
              <tr>
                  <td>Hello World</td>
              </tr>
          </table>';
  $secondHtmlString = '<table>
              <tr>
                  <td>Hello World</td>
              </tr>
          </table>';

  $reader = new \PhpOffice\PhpSpreadsheet\Reader\Html();
  $spreadsheet = $reader->loadFromString($firstHtmlString);
  $reader->setSheetIndex(1);
  $spreadhseet = $reader->loadFromString($secondHtmlString, $spreadsheet);

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

    $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
    return $writer->save('php://output'); 
  ?>

any help what went wrong?

Answer 1

That's worked version:

require "vendor/autoload.php";

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as xlsx; 
use PhpOffice\PhpSpreadsheet\IOFactory as io_factory; 



$firstHtmlString = '<table>
                  <tr>
                      <td>Hello World</td>
                  </tr>
              </table>';
$secondHtmlString = '<table>
                  <tr>
                      <td>Hello World</td>
                  </tr>
              </table>';

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Html;
$spreadsheet = $reader->loadFromString($firstHtmlString);
$reader->setSheetIndex(1);
$spreadhseet = $reader->loadFromString($secondHtmlString, $spreadsheet);
$objWriter = io_factory::createWriter($spreadsheet, 'Xlsx');
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment;filename=test.xlsx");
header("Content-Transfer-Encoding: binary ");

ob_end_clean();
ob_start();
$objWriter->save('php://output');

Answer 2

The problem is that you the reader say make only one sheet.

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Html;
$spreadsheet = $reader->loadFromString($firstHtmlString);
$reader->setSheetIndex(1);

You must add different sheets with different inputs

$myWorkSheet1 = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'My Data');
$spreadsheet->addSheet($myWorkSheet1, 0);

$myWorkSheet2 = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'My Data 2');
$spreadsheet->addSheet($myWorkSheet2, 1);

See documentation here

Answer 3

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
//--- you can erase this first worksheet.. 
//Assume you have native like below
$page =$spreadsheet->getActiveSheet();
$spreadsheet->getActiveSheet()->setTitle("welcome");

$str="HELLO WORLD";
$page->setCellValue( "A2", $str);
//Next worksheet from html
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Html;

$html = "<h1>ini adalah html 1</h1>";
$work1 = $reader->loadFromString($html);

$html = "<h1>this is html 2</h1>";
$work2 = $reader->loadFromString($html);

//try load by name
$main1 = clone $work1->getSheetByName('Worksheet');
//Use external.. because is different object
$spreadsheet->addExternalSheet($main1 );
//Must rename 
$spreadsheet->setActiveSheetIndex(1);
$spreadsheet->getActiveSheet()->setTitle("Work001");

//Last worksheet.. not tested.. please fix it
$main2 = clone $work2->getSheetByName('Worksheet');

//not tested the 2nd worksheet
$spreadsheet->addExternalSheet($main2);
$spreadsheet->setActiveSheetIndex(2)
$spreadsheet->getActiveSheet()->setTitle("Work002");

$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');

            $dir = __DIR__."/";
// change it as you desire
            $objWriter->save($dir.$nameFile.".".$ext );

            echo "save on:{$nameFile}.{$ext}";

The answer already given above.. the problem if you have different style on the excel/spreadsheet object. Using this on laravel.. where the html is from view