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?

there is no error. it just make one sheet with one table .. not two sheets in the file. — nashwa ghazy, Sep 29 '20 at 13:09

score 1 · Answer 1 · answered Sep 29 '20 at 13:32

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');

sorry .. it didn't work .. it makes only one sheet not two sheet.. same problem — nashwa ghazy, Sep 29 '20 at 13:50
i tested it and work, so it's your reposity. try to re-install it please. — Simone Rossaini, Sep 30 '20 at 06:08

Collie-IT Anne K. Frey · Answer 2 · 2020-09-29T15:59:48.723

0

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');
$spreadsheet->addSheet($myWorkSheet2, 1);

See documentation here

edited Sep 29 '20 at 15:59

answered Sep 29 '20 at 15:54

Collie-IT Anne K. Frey

825
7
19

I didn't understand your code!!. where is `$secondHtmlString` variable? Yes it create two `sheets` but with table in one of them, and the other is empty. – nashwa ghazy Sep 29 '20 at 16:17
you need to create 2 different worksheets. And fill them In you code you stands literal create one sheet and then override this sheet. – Collie-IT Anne K. Frey Sep 29 '20 at 17:55
I still don't get it !!. Yes two work sheets have been added.. but empty. How to specific data for the first sheet and different data for the second sheet? – nashwa ghazy Sep 29 '20 at 18:10

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

2 Answers2