HTML to Excel with multiple worksheets

Question

I'm serving HTML to an Excel file by doing:

header('Content-type: application/excel');
header('Content-Disposition: attachment; filename=testfile.xls');

$html = "<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">
  <head>
    <!--[if gte mso 9]>
    <xml>
      <x:ExcelWorkbook>
        <x:ExcelWorksheets>
          <x:ExcelWorksheet>
            <x:Name>Sheet1</x:Name>
            <x:WorksheetOptions>
              <x:Print>
                <x:ValidPrinterInfo/>
              </x:Print>
            </x:WorksheetOptions>
          </x:ExcelWorksheet>
        </x:ExcelWorksheets>
      </x:ExcelWorkbook>
    </xml>
    <![endif]-->
  </head>
  <body>
    <table>
      <thead><tr><th>Header</th></tr></thead>
      <tbody><tr><td>Values</td></tr></tbody>
    </table>
  </body>
</html>";

echo $html;

This creates an Excel file with 1 worksheet but I need a second worksheet. So what I did was to duplicate

<x:ExcelWorksheet>
  <x:Name>Sheet2</x:Name>
  <x:WorksheetOptions>
    <x:Print>
     <x:ValidPrinterInfo/>
    </x:Print>
  </x:WorksheetOptions>
</x:ExcelWorksheet>

and just used a different name. This worked as expected but now I don't know how to put data into this second worksheet. Is there a way for me to specify which worksheet the <table> is supposed to go into?

Notes

I searched around for a bit and found this and this.

The solutions on the first link uses XML instead of HTML and I would have been okay with it but I couldn't get multi-line to display properly because I can't set the row to auto fit the height based on the content. It only shows the first line.

The second link talks about MHTML (Multipart HTML) but I could not understand it.

Is it as simple as changing the name attribute from ``Sheet1`` to `Sheet2` — RiggsFolly, Jun 28 '18 at 14:29
@RiggsFolly yes I'm already able to do that but whenever I create a `` it only goes to the first worksheet. The second worksheet is always blank. — dokgu, Jun 28 '18 at 14:30
Posted the solution here [https://stackoverflow.com/questions/29698796/how-to-convert-html-table-to-excel-with-multiple-sheet/56037196#answer-56037196](https://stackoverflow.com/questions/29698796/how-to-convert-html-table-to-excel-with-multiple-sheet/56037196#answer-56037196) — thundorstorm, May 08 '19 at 09:11
@PatrickGregorio I am having the same problem. Did you found the solution for this? — Ajay Lingayat, May 14 '21 at 17:35
@AjayLingayat unfortunately no - we've migrated to using [PhpSpreadsheet](https://phpspreadsheet.readthedocs.io/en/latest/) instead. Take a look at that - it's so much better. — dokgu, May 18 '21 at 16:19

thundorstorm · Answer 1 · 2019-05-08T13:12:21.053

0

I have posted an answer for same request here. Creates 2 sheets (1 sheet per table) and WORKS PROPERLY: How to convert html table to excel with multiple sheet?

edited May 08 '19 at 13:12

answered May 08 '19 at 09:12

thundorstorm

99
1
2

Provided some explanations – thundorstorm May 08 '19 at 13:21

HTML to Excel with multiple worksheets

Notes

1 Answers1