How to export this table of data to Excel file from php

Question

can anyone here help with the code to export to an Excel file i hope that someone can because I already have an export button.

I want to keep the most with this code because this code works well. and if you want to see the export.php ask for it.

here a picture enter image description here

<a class="btn btnx btn-orange" href="export.php" target="_new"><i class="fa fa-download"></i> Export to Excel</a>
         <div class="table-responsive">                  
      <table id="mytable" class="table table-bordred table-striped">                 
         <thead>
           <tr>
           <th>#</th>
           <th>Auditeur</th>
           <th>Afdeling</th>
           <th>Invoerdatum</th>
           <th>Week</th>
           <th>Zone</th>
           <th>Stand</th>
           <th>Zijn alle overbodeige gereedschappen / materialen van de werkpost verwijderd / geïdentificeerd? ( sleutel, bouten / moeren,.... )</th>
           <th>Opgeslagen foto nr1</th>
           <th>Is er rommel aanwezig op de werkpost ( bekertjes, kledij, flesjes,....)</th>
           <th>Opgeslagen foto nr2</th>
           <th>Zijn vervallen / niet geautoriseerde documenten verwijderd?</th>
           <th>Opgeslagen foto nr3</th>
           <th>Opmerking</th>
           <th>Edit</th>
           <th>Delete</th>
           </tr>
         </thead>
       <tbody>

<?php

      $fetchdata=new DB_con(); 
      $sql=$fetchdata->fetchdata();
      $cnt=1;
         while($row=mysqli_fetch_array($sql))

     {
?> 

       <tr>       
       <td><?php echo htmlentities($cnt);?></td>
       <td><?php echo htmlentities($row['Auditeur']);?></td>
       <td><?php echo htmlentities($row['Afdeling']);?></td>
       <td><?php echo htmlentities($row['PostingDate']);?></td>
       <td><?php echo htmlentities($row['Week']);?></td>
       <td><?php echo htmlentities($row['Zone']);?></td>
       <td><?php echo htmlentities($row['Stand']);?></td>
       <td><?php echo htmlentities($row['NOKOK01']);?></td>
       <td><?php echo htmlentities($row['Results']);?></td>
       <td><?php echo htmlentities($row['NOKOK02']);?></td>
       <td><?php echo htmlentities($row['Results2']);?></td>
       <td><?php echo htmlentities($row['NOKOK03']);?></td>
       <td><?php echo htmlentities($row['Results3']);?></td>
       <td><?php echo htmlentities($row['Bericht']);?></td>


       <td><a href="updat_form_sortings.php?id=<?php echo htmlentities($row['id']);?>"><button class="btn btn-info btn-xs"><span class="glyphicon glyphicon-pencil"></span></button></a></td>

       <td><a href="app_opslag_sorting.php?del=<?php echo htmlentities($row['id']);?>"><button class="btn btn-danger btn-xs" onclick="return confirm('Wil je bestand echt verwijderen?');"><span class="glyphicon glyphicon-trash"></span></button></a></td>
    </tr>


<?php 
// for serial number increment
     $cnt++;
   } 
?>

       </tbody>      
     </table>

here is my export.php code

<?php  
//export.php  
$connect = mysqli_connect("localhost", "root", "", "oopscrud02");
$output = '';
if(isset($_POST["export"]))
{
 $query = "SELECT * FROM tblusers";
 $result = mysqli_query($connect, $query);
 if(mysqli_num_rows($result) > 0)
 {
  $output .= '
   <table class="table" bordered="1">  
        <tr>  
          <th>#</th>  
          <th>Afdeling</th>
          <th>Invoerdatum</th>
          <th>Week</th>
          <th>Zone</th>
          <th>Stand</th>
          <th>Zijn alle overbodeige gereedschappen / materialen van de werkpost verwijderd / geïdentificeerd? ( sleutel, bouten / moeren,.... )</th>
          <th>Opgeslagen foto nr1</th>
          <th>Is er rommel aanwezig op de werkpost ( bekertjes, kledij, flesjes,....)</th>
          <th>Opgeslagen foto nr2</th>
          <th>Zijn vervallen / niet geautoriseerde documenten verwijderd?</th>
          <th>Opgeslagen foto nr3</th>          
          <th>Opmerking</th>
        </tr>
  ';
  while($row = mysqli_fetch_array($result))
  {
   $output .= '
        <tr>  
          <td>'.$row["Auditeur"].'</td>  
          <td>'.$row["Afdeling"].'</td>
          <td>'.$row["PostingDate"].'</td>
          <td>'.$row["Week"].'</td>
          <td>'.$row["Zone"].'</td>
          <td>'.$row["Stand"].'</td>  
          <td>'.$row["NOKOK01"].'</td>
          <td>'.$row["Results"].'</td>
          <td>'.$row["NOKOK02"].'</td>
          <td>'.$row["Results2"].'</td>
          <td>'.$row["NOKOK03"].'</td>
          <td>'.$row["Results3"].'</td>
          <td>'.$row["Bericht"].'</td>
        </tr>
   ';
  }
  $output .= '</table>';
  header('Content-Type: application/xls');
  header('Content-Disposition: attachment; filename=Sorterend form.xls');
  echo $output;
 }
}
?>

You need help and we have to ask for the export.php? Does not make sense to me — Claudio
– Claudio, Commented Feb 18, 2019 at 13:37
You can't do it like that, use the new PhpSpreadsheet library. Example: github.com/PHPOffice/PhpSpreadsheet/blob/master/samples/Basic/… — Claudio
– Claudio, Commented Feb 18, 2019 at 14:03
where do I have to integrate that code into my export.php file? — stefaanphp
– stefaanphp, Commented Feb 18, 2019 at 14:18

Pascal Tovohery · Accepted Answer · 2019-02-18 14:14:09Z

0

I suggest you to use javascript library, it work for me Install excell javascript and FileSaver for saving file Adding 2 library

<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/1.3.8/FileSaver.min.js"></script>

XLX Js

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.14.1/xlsx.full.min.js"></script>

The script

<script>
  $('#download-btn').on('click', function(){
    var wb = XLSX.utils.table_to_book(document.getElementById('my-table'),{sheet: "Sheet name"})

    var wbout = XLSX.write(wb, {bookType: 'xlsx', bookSST: true, type: 'binary'});

    function s2ab(s) {
      var buf = new ArrayBuffer(s.length);
      var view = new Uint8Array(buf);
      for (var i = 0; i < s.length; i++) {
        view[i] = s.charCodeAt(i) & 0xFF;
      }
      return buf;
    }

    saveAs(new Blob([s2ab(wbout)], {type:"application/octet-stream"}), 'test.xlsx');
  })
</script>

answered Feb 18, 2019 at 14:14

Pascal Tovohery

9969 silver badges21 bronze badges

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

1 Comment

stefaanphp Over a year ago

I'll try it out.

Kristijan Mikulec · Accepted Answer · 2019-02-18 14:21:05Z

0

Maybe the easiest way is to use DataTables. See example: https://datatables.net/extensions/buttons/examples/initialisation/export.html After that you can simply use css and make it look as you want.

answered Feb 18, 2019 at 14:21

Kristijan Mikulec

1641 silver badge13 bronze badges

1 Comment

stefaanphp Over a year ago

I will look at it once.

Collectives™ on Stack Overflow

How to export this table of data to Excel file from php

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related