How to export html table to excel or pdf in php

Question

In my php page i have a table and if the user requires he has to export that table to excel sheet..

The code for displaying the table is:

$sql=mysql_query("SELECT * FROM attendance WHERE (year = '" . 
mysql_real_escape_string($_SESSION['year']) . "') and ( branch= '" . 
mysql_real_escape_string(($_SESSION['branch'])). "') and ( sem= '" . 
mysql_real_escape_string(($_SESSION['sem'])). "') and (sec= '" . 
mysql_real_escape_string(($_SESSION['sec'])). "')"); print "<body 
background='bg.jpg'>";                                                  
Print "<br><br><BR><center><table border 
cellpadding=3><tr><th>idno</th><th>name</th><th>subject</th><th>Held 
Classes</th><th>Attended Classes</th></tr>";   
while($data=mysql_fetch_array( 
$sql ))   { 

echo "<tr><td>".$data['idno']." </td><td>".$data['name'] . " 
<td>".$data['subject']." </td><td>".$data['heldcls'] . " 
<td>".$data['attendcls']." </td>"; } 
Print "</table><br><br><form action = excel.php method = POST><input type = 
'submit' name = 'submit' Value = 'Export to excel'></form></center>";

how do i export this table to excel sheet. And what should b the code in excel.php. Please help me.. thank you in advance..

you need to work with libraries for manipulating Excel files .. — Gntem
– Gntem, Commented Sep 22, 2012 at 5:30

Isaac · Accepted Answer · 2013-12-30 21:56:27Z

32

Either you can use CSV functions or PHPExcel

or you can try like below

<?php
$file="demo.xls";
$test="<table  ><tr><td>Cell 1</td><td>Cell 2</td></tr></table>";
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$file");
echo $test;
?>

The header for .xlsx files is Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

edited Dec 30, 2013 at 21:56

Isaac

6251 gold badge13 silver badges30 bronze badges

answered Sep 22, 2012 at 5:31

Mayank K. Swami

57.3k23 gold badges130 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FluffyKitten Over a year ago

Just an FYI: saving html as an xls file works fine for Excel 2003, but just be aware that the current version of Excel which most people have these days gives a warning when opening the file, and this is not ideal if the download is for a client. Changing the filename and mimetype for xlsx doesn't work at all (I tried!). Like the others, I recommend PHPExcel.

Azad Hussain Over a year ago

@NullPoiиteя its work great with only table that is <tr><td>.. but its not loading images avalable in the html page

darkrat · Accepted Answer · 2015-07-06 19:41:29Z

20

If all you want is a simple excel worksheet try this:

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

$data = '<html xmlns:x="urn:schemas-microsoft-com:office:excel">
<head>
    <!--[if gte mso 9]>
    <xml>
        <x:ExcelWorkbook>
            <x:ExcelWorksheets>
                <x:ExcelWorksheet>
                    <x:Name>Sheet 1</x:Name>
                    <x:WorksheetOptions>
                        <x:Print>
                            <x:ValidPrinterInfo/>
                        </x:Print>
                    </x:WorksheetOptions>
                </x:ExcelWorksheet>
            </x:ExcelWorksheets>
        </x:ExcelWorkbook>
    </xml>
    <![endif]-->
</head>

<body>
   <table><tr><td>Cell 1</td><td>Cell 2</td></tr></table>
</body></html>';

echo $data;

The key here is the xml data. This will keep excel from complaining about the file.

edited Jul 6, 2015 at 19:41

answered Mar 28, 2013 at 18:26

darkrat

6916 silver badges14 bronze badges

4 Comments

chiliNUT Over a year ago

excel 07 still complains about this

Lonare Over a year ago

This is not working with windows 10 Microsoft excel 2016

hansmei Over a year ago

I'll add that if you have trouble with getting the proper encoding (eg. UTF-8) in your export file, adding this inside the <head></head>-tags will make the Excel-file use the proper heading: <meta http-equiv="Content-Type" content="text/html; charset=utf-8">

Tiago Over a year ago

How do I put data on each Sheet created? I'm creating a foreach, which will be the name of each company, and the equipment will be inside each company.

Aaron Mason · Accepted Answer · 2017-07-05 05:51:51Z

9

Use a PHP Excel for generatingExcel file. You can find a good one called PHPExcel here: https://github.com/PHPOffice/PHPExcel

And for PDF generation use http://princexml.com/

edited Jul 5, 2017 at 5:51

Aaron Mason

3723 silver badges12 bronze badges

answered Sep 22, 2012 at 5:35

Yogesh Suthar

30.5k18 gold badges75 silver badges100 bronze badges

2 Comments

Marco Klein Over a year ago

I had good experience with PHPExcel. It's a really cool library!

Cedric-J. Oct 3 at 7:01

PHPExcel is abandoned, replaced by PHPSPreadsheet

Praveen Srinivasan · Accepted Answer · 2014-07-28 15:28:18Z

<script src="jquery.min.js"></script>
<table border="1" id="ReportTable" class="myClass">
    <tr bgcolor="#CCC">
      <td width="100">xxxxx</td>
      <td width="700">xxxxxx</td>
      <td width="170">xxxxxx</td>
      <td width="30">xxxxxx</td>
    </tr>
    <tr bgcolor="#FFFFFF">
      <td><?php                 
            $date = date_create($row_Recordset3['fecha']);
            echo date_format($date, 'd-m-Y');
            ?></td>
      <td><?php echo $row_Recordset3['descripcion']; ?></td>
      <td><?php echo $row_Recordset3['producto']; ?></td>
      <td><img src="borrar.png" width="14" height="14" class="clickable" onClick="eliminarSeguimiento(<?php echo $row_Recordset3['idSeguimiento']; ?>)" title="borrar"></td>
    </tr>
  </table>

  <input type="hidden" id="datatodisplay" name="datatodisplay">  
    <input type="submit" value="Export to Excel">

exporttable.php

<?php
header('Content-Type: application/force-download');
header('Content-disposition: attachment; filename=export.xls');
// Fix for crappy IE bug in download.
header("Pragma: ");
header("Cache-Control: ");
echo $_REQUEST['datatodisplay'];
?>

Love Kumar · Accepted Answer · 2019-10-22 09:23:37Z

-1

Easiest way to export Excel to Html table

$file_name ="file_name.xls";
$excel_file="Your Html Table Code";
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$file_name");
echo $excel_file;

answered Oct 22, 2019 at 9:23

Love Kumar

1,13410 silver badges10 bronze badges

2 Comments

Mulli Over a year ago

Too simplistic. There are more issues than just simple hello world string.

Rishabh Over a year ago

You solution is exporting file containing whole page html instead of just table html which we store in here $excel_file="Your Html Table Code";

Collectives™ on Stack Overflow

How to export html table to excel or pdf in php

5 Answers 5

2 Comments

4 Comments

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

4 Comments

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related