Wednesday 11 March 2020

PHPSpreadSheet Beginner Tutorial



This is PHPSpreadSheet tutorial for beginner who are looking for basic tutorial of PHPSpreadSheet. If you want to learn how can we perform basic operation like excel to html, html to excel, import excel data, export excel data etc. For perform this basic operation in PHP, here we have use PHPSpreadSheet library. This is basic tutorial on PHPSpreadSheet for beginner level like, how to download PHPSpreadSheet library and how can we use in our PHP Application.

What is PHPSpreadSheet Library


PHPSpreadSheet library is purely writter in PHP and it main purpose of communicate your PHP application with Excel spreadsheet. Here communicate means data send and received or import or export from excel spreadsheet to PHP application and from PHP application to Excel file. It not only support excel file for reading and writing of data, but also read and write html file data and CSV file data. PHPSpreadSheet library is a import version of PHPExcel library. PHPExcel library is not maintained for many years and very less has used this library. Most of the user has been migrated to this PHPSpreadSheet library or any other alternate library.

If you want to use this PHPSpreadSheet Library, you have to PHP 7.1 version or latest PHP version. For this library you have to install composer in your computer.

How to install PHPSpreadSheet Library


If you are looking for install PHPSpreadsheet library, then you have to go command prompt and go to directory in which you want to download this library and write following command.


composer require phpoffice/phpspreadsheet


This command will download PHPSpreadSheet library by using composer. We will discuss following topic of PHPSpreadsheet library in this post.

  1. Load Excel File Data in Browser
  2. Convert HTML Table Data to Excel Sheet Data
  3. Import Data From Xls, Xlsx or CSV file to Mysql
  4. Export Mysql Table Data to .xlsx, .xls or .csv file

1. Load Excel File Data in Browser




This is first topic, which we have discuss in this post is that How to view Excel Spreadsheet data in Browser by using PHPSpreadSheet library. Here we will make PHP script with PHPSpreadsheet library for load Excel sheet content directly in browser without store in our computer. Because this library has capability to view excel data in browser. Below you can find source code of directly view Excel data in browser.



index.php

<!DOCTYPE html>
<html>
   <head>
     <title>Load Excel Sheet in Browser using PHPSpreadsheet</title>
     <meta name="viewport" content="width=device-width, initial-scale=1.0">
     <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
   </head>
   <body>
     <div class="container">
      <br />
      <h3 align="center">Load Excel Sheet in Browser using PHPSpreadsheet</h3>
      <br />
      <div class="table-responsive">
       <span id="message"></span>
          <form method="post" id="load_excel_form" enctype="multipart/form-data">
            <table class="table">
              <tr>
                <td width="25%" align="right">Select Excel File</td>
                <td width="50%"><input type="file" name="select_excel" /></td>
                <td width="25%"><input type="submit" name="load" class="btn btn-primary" /></td>
              </tr>
            </table>
          </form>
       <br />
          <div id="excel_area"></div>
      </div>
     </div>
     <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
  </body>
</html>
<script>
$(document).ready(function(){
  $('#load_excel_form').on('submit', function(event){
    event.preventDefault();
    $.ajax({
      url:"upload.php",
      method:"POST",
      data:new FormData(this),
      contentType:false,
      cache:false,
      processData:false,
      success:function(data)
      {
        $('#excel_area').html(data);
        $('table').css('width','100%');
      }
    })
  });
});
</script>


upload.php

<?php

//upload.php

include 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

if($_FILES["select_excel"]["name"] != '')
{
 $allowed_extension = array('xls', 'xlsx');
 $file_array = explode(".", $_FILES['select_excel']['name']);
 $file_extension = end($file_array);
 if(in_array($file_extension, $allowed_extension))
 {
  $reader = IOFactory::createReader('Xlsx');
  $spreadsheet = $reader->load($_FILES['select_excel']['tmp_name']);
  $writer = IOFactory::createWriter($spreadsheet, 'Html');
  $message = $writer->save('php://output');
 }
 else
 {
  $message = '<div class="alert alert-danger">Only .xls or .xlsx file allowed</div>';
 }
}
else
{
 $message = '<div class="alert alert-danger">Please Select File</div>';
}

echo $message;

?>


2. Convert HTML Table Data to Excel Sheet Data




There are lots of tutorial available on Internet regarding make excel sheet from HTML table data, but that tutorial use javascript for convert html table data to excel data. But it has not make original excel spreadsheet but it has only open html data in excel file format. So, when we have open that type of excel then it has display corrupted or invalid file format message has been appear in window. This is because that file is not correctly excel file but only open html file in excel file. For make original excel file you have to use PHPSpreadsheet library or PHPExcel library. By using both library you can make excel sheet.

Here we have use PHPSpreadsheet library for create excel file from HTML table data. Here we have use simple jquery code with PHP script and PHPSpreadsheet library for export html table data to excel. First it will store html table data in temporary html file, then after by using PHPSpreadsheet IOFactory class method, it will read HTML file by using createReader() method of IOFactory class. Next it will make excel file from that temporary html file by using createWriter() method of PHPSpreadSheet IOFactory class. Once HTML table data has been exported to Excel file then excel sheet will be downloaded in computer and temporary excel file and temporary html file will be removed from working folder.



html-to-excel.php

<?php

//html-to-excel.php

$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");

$query = "SELECT * FROM sample_datas";

$statement = $connect->prepare($query);

$statement->execute();

$result = $statement->fetchAll();

?>

<!DOCTYPE html>
<html>
   <head>
     <title>Convert HTML Table to Excel using PHPSpreadsheet</title>
     <meta name="viewport" content="width=device-width, initial-scale=1.0">
     <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
   </head>
   <body>
     <div class="container">
      <br />
      <h3 align="center">Convert HTML Table to Excel using PHPSpreadsheet</h3>
      <br />
      <div class="table-responsive">
       <form method="POST" id="convert_form" action="export.php">
            <table class="table table-striped table-bordered" id="table_content">
              <tr>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Created At</th>
                <th>Updated At</th>
              </tr>
              <?php
              foreach($result as $row)
              {
                echo '
                <tr>
                  <td>'.$row["first_name"].'</td>
                  <td>'.$row["last_name"].'</td>
                  <td>'.$row["created_at"].'</td>
                  <td>'.$row["updated_at"].'</td>
                </tr>
                ';
              }
              ?>
            </table>
            <input type="hidden" name="file_content" id="file_content" />
            <button type="button" name="convert" id="convert" class="btn btn-primary">Convert</button>
          </form>
          <br />
          <br />
      </div>
     </div>
     <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
  </body>
</html>

<script>
$(document).ready(function(){
 $('#convert').click(function(){
    var table_content = '<table>';
    table_content += $('#table_content').html();
    table_content += '</table>';
    $('#file_content').val(table_content);
    $('#convert_form').submit();
  });
});
</script>


export.php

<?php

//export.php

include 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

if(isset($_POST["file_content"]))
{
 $temporary_html_file = './tmp_html/' . time() . '.html';

 file_put_contents($temporary_html_file, $_POST["file_content"]);

 $reader = IOFactory::createReader('Html');

 $spreadsheet = $reader->load($temporary_html_file);

 $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');

 $filename = time() . '.xlsx';

 $writer->save($filename);

 header('Content-Type: application/x-www-form-urlencoded');

 header('Content-Transfer-Encoding: Binary');

 header("Content-disposition: attachment; filename=\"".$filename."\"");

 readfile($filename);

 unlink($temporary_html_file);

 unlink($filename);

 exit;
}

?>



3. Import Data From Xls, Xlsx or CSV file to Mysql




PHPSpreadSheet library is able to read data from Excel file or CSV file. So, here we have use PHPSpreadSheet library has been use for Import data from Excel sheet or CSV file and Insert into Mysql table using PHP script. If we have developed any web based application, then data import is the basic feature for any PHP application. So, for this feature here we have use PHPSpreadsheet library for read data from Excel or CSV file, this is because by using this library, we can easily import data from Excel or CSV file and Insert into Mysql table using PHP script.



php_spreadsheet_import.php

<!DOCTYPE html>
<html>
   <head>
     <title>Import Data From Excel or CSV File to Mysql using PHPSpreadsheet</title>
     <meta name="viewport" content="width=device-width, initial-scale=1.0">
     <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
   </head>
   <body>
     <div class="container">
      <br />
      <h3 align="center">Import Data From Excel or CSV File to Mysql using PHPSpreadsheet</h3>
      <br />
        <div class="panel panel-default">
          <div class="panel-heading">Import Data From Excel or CSV File to Mysql using PHPSpreadsheet</div>
          <div class="panel-body">
          <div class="table-responsive">
           <span id="message"></span>
              <form method="post" id="import_excel_form" enctype="multipart/form-data">
                <table class="table">
                  <tr>
                    <td width="25%" align="right">Select Excel File</td>
                    <td width="50%"><input type="file" name="import_excel" /></td>
                    <td width="25%"><input type="submit" name="import" id="import" class="btn btn-primary" value="Import" /></td>
                  </tr>
                </table>
              </form>
           <br />
              
          </div>
          </div>
        </div>
     </div>
     <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
  </body>
</html>
<script>
$(document).ready(function(){
  $('#import_excel_form').on('submit', function(event){
    event.preventDefault();
    $.ajax({
      url:"import.php",
      method:"POST",
      data:new FormData(this),
      contentType:false,
      cache:false,
      processData:false,
      beforeSend:function(){
        $('#import').attr('disabled', 'disabled');
        $('#import').val('Importing...');
      },
      success:function(data)
      {
        $('#message').html(data);
        $('#import_excel_form')[0].reset();
        $('#import').attr('disabled', false);
        $('#import').val('Import');
      }
    })
  });
});
</script>


import.php

<?php

//import.php

include 'vendor/autoload.php';

$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");

if($_FILES["import_excel"]["name"] != '')
{
 $allowed_extension = array('xls', 'csv', 'xlsx');
 $file_array = explode(".", $_FILES["import_excel"]["name"]);
 $file_extension = end($file_array);

 if(in_array($file_extension, $allowed_extension))
 {
  $file_name = time() . '.' . $file_extension;
  move_uploaded_file($_FILES['import_excel']['tmp_name'], $file_name);
  $file_type = \PhpOffice\PhpSpreadsheet\IOFactory::identify($file_name);
  $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($file_type);

  $spreadsheet = $reader->load($file_name);

  unlink($file_name);

  $data = $spreadsheet->getActiveSheet()->toArray();

  foreach($data as $row)
  {
   $insert_data = array(
    ':first_name'  => $row[0],
    ':last_name'  => $row[1],
    ':created_at'  => $row[2],
    ':updated_at'  => $row[3]
   );

   $query = "
   INSERT INTO sample_datas 
   (first_name, last_name, created_at, updated_at) 
   VALUES (:first_name, :last_name, :created_at, :updated_at)
   ";

   $statement = $connect->prepare($query);
   $statement->execute($insert_data);
  }
  $message = '<div class="alert alert-success">Data Imported Successfully</div>';

 }
 else
 {
  $message = '<div class="alert alert-danger">Only .xls .csv or .xlsx file allowed</div>';
 }
}
else
{
 $message = '<div class="alert alert-danger">Please Select File</div>';
}

echo $message;

?>


4. Export Mysql Table Data to .xlsx, .xls or .csv File




PHPSpreadSheet library can write data in Excel or CSV file. So suppose you have build any web based application by using PHP script. Then you have to use PHPSpreadSheet library for export data from Mysql database to any spreadsheet file format like .xlsx, .xls or .csv file. Export data from web application is one of the feature of you system. If your system is able to give data to user in Excel or csv file, so it will increase your application usabilty and user can use that data for other purpose. So, below you can find source code for How to Export Mysql data to Excel or Csv file format by using PhpSpreadsheet library.



php_spreadsheet_export.php

<?php

//php_spreadsheet_export.php

include 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;


$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");


$query = "SELECT * FROM sample_datas ORDER BY id DESC";

$statement = $connect->prepare($query);

$statement->execute();

$result = $statement->fetchAll();

if(isset($_POST["export"]))
{
  $file = new Spreadsheet();

  $active_sheet = $file->getActiveSheet();

  $active_sheet->setCellValue('A1', 'First Name');
  $active_sheet->setCellValue('B1', 'Last Name');
  $active_sheet->setCellValue('C1', 'Created At');
  $active_sheet->setCellValue('D1', 'Updated At');

  $count = 2;

  foreach($result as $row)
  {
    $active_sheet->setCellValue('A' . $count, $row["first_name"]);
    $active_sheet->setCellValue('B' . $count, $row["last_name"]);
    $active_sheet->setCellValue('C' . $count, $row["created_at"]);
    $active_sheet->setCellValue('D' . $count, $row["updated_at"]);

    $count = $count + 1;
  }

  $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($file, $_POST["file_type"]);

  $file_name = time() . '.' . strtolower($_POST["file_type"]);

  $writer->save($file_name);

  header('Content-Type: application/x-www-form-urlencoded');

  header('Content-Transfer-Encoding: Binary');

  header("Content-disposition: attachment; filename=\"".$file_name."\"");

  readfile($file_name);

  unlink($file_name);

  exit;

}

?>
<!DOCTYPE html>
<html>
   <head>
     <title>Export Data From Mysql to Excel using PHPSpreadsheet</title>
     <meta name="viewport" content="width=device-width, initial-scale=1.0">
     <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
   </head>
   <body>
     <div class="container">
      <br />
      <h3 align="center">Export Data From Mysql to Excel using PHPSpreadsheet</h3>
      <br />
        <div class="panel panel-default">
          <div class="panel-heading">
            <form method="post">
              <div class="row">
                <div class="col-md-6">User Data</div>
                <div class="col-md-4">
                  <select name="file_type" class="form-control input-sm">
                    <option value="Xlsx">Xlsx</option>
                    <option value="Xls">Xls</option>
                    <option value="Csv">Csv</option>
                  </select>
                </div>
                <div class="col-md-2">
                  <input type="submit" name="export" class="btn btn-primary btn-sm" value="Export" />
                </div>
              </div>
            </form>
          </div>
          <div class="panel-body">
          <div class="table-responsive">
           <table class="table table-striped table-bordered">
                <tr>
                  <th>First Name</th>
                  <th>Last Name</th>
                  <th>Created At</th>
                  <th>Updated At</th>
                </tr>
                <?php

                foreach($result as $row)
                {
                  echo '
                  <tr>
                    <td>'.$row["first_name"].'</td>
                    <td>'.$row["last_name"].'</td>
                    <td>'.$row["created_at"].'</td>
                    <td>'.$row["updated_at"].'</td>
                  </tr>
                  ';
                }
                ?>

              </table>
          </div>
          </div>
        </div>
     </div>
      <br />
      <br />
     <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
  </body>
</html>





Remaining source code will be added very soon.


27 comments:

  1. very useful thank you so much !

    ReplyDelete
  2. It's very helpful, thanks very much from Viet Nam <3

    ReplyDelete
  3. Parse error: syntax error, unexpected '=' in C:\xampp\htdocs\open-excel-sheet-in-browser\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Reader\Xlsx.php on line 1332

    ReplyDelete
  4. Parse error: syntax error, unexpected '=' in C:\xampp\htdocs\open-excel-sheet-in-browser\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Reader\Xlsx.php on line 1332


    help me

    ReplyDelete
  5. it doesn`t work for me.? why? Could you please help me?

    ReplyDelete
  6. when I am going to export the table, i get these errors:
    "Warning: file_put_contents(./tmp_html/1600770085.html): failed to open stream: No such file or directory in C:\xampp\htdocs\PhpSpreadsheet\export.php on line 12

    Fatal error: Uncaught InvalidArgumentException: File "./tmp_html/1600770085.html" does not exist. in C:\xampp\htdocs\PhpSpreadsheet\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Shared\File.php:135 Stack trace: #0 C:\xampp\htdocs\PhpSpreadsheet\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Reader\BaseReader.php(148): PhpOffice\PhpSpreadsheet\Shared\File::assertFile('./tmp_html/1600...') #1 C:\xampp\htdocs\PhpSpreadsheet\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Reader\Html.php(146): PhpOffice\PhpSpreadsheet\Reader\BaseReader->openFile('./tmp_html/1600...') #2 C:\xampp\htdocs\PhpSpreadsheet\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Reader\Html.php(584): PhpOffice\PhpSpreadsheet\Reader\Html->canRead('./tmp_html/1600...') #3 C:\xampp\htdocs\PhpSpreadsheet\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Reader\Html.php(216): PhpOffice\PhpSpreadsheet\Reader\Html->loadIntoExisting('./tmp_html/1600...', Object(PhpOffice\PhpSpreadsheet\Spreadsheet)) #4 C:\xampp\htdocs\PhpSpreadsheet\export.php(16): in C:\xampp\htdocs\PhpSpreadsheet\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Shared\File.php on line 135".

    ReplyDelete
    Replies
    1. i have the same problem can you fix ? pls help me

      Delete
    2. create a "tmp_html" folder and change the directory of $temporary_html_file depending where you put that folder.

      Delete
  7. Only two rows are updating, Could you please help me on this

    ReplyDelete
  8. very useful easy to use and implement thanks so much

    ReplyDelete
  9. Hello,

    I couldn´t make the option 4 work with variables in the querry (date type in mysql). it shows in the page what i was looking for, but didn´t export to xlsx. Instead, i used the option 2, work like a charm, since it already showed the contents.
    Do you know how to format cells with the option 2, like centralize cells?

    Thanks.

    ReplyDelete
  10. include 'vendor/autoload.php'; <- would you please share this file.

    ReplyDelete
  11. I want the Save dialogue box to appear when I press Export and I added this code and it did not work and I want help

    $file_name = $_POST['drivar'] . '.' . $_POST["file_type"];
    $writer->save($file_name);
    add_action('template_redirect','yoursite_template_redirect');
    function yoursite_template_redirect() {
    header('Content-Type: application/x-www-form-urlencoded');
    header('Content-Transfer-Encoding: binary');
    header("Content-disposition: attachment; filename=\"".$file_name."\"");
    readfile($file_name);
    unlink($file_name);
    exit;
    }

    ReplyDelete
  12. include 'vendor/autoload.php';
    where is this file codeignator 3

    ReplyDelete
  13. How do I create an editable sheet in a web browser? Like Google Sheet. We can copy and paste range.

    ReplyDelete
  14. Parse error: syntax error, unexpected '.', expecting '&' or variable (T_VARIABLE) in C:\wamp\www\exchange\excel\vendor\markbaker\complex\classes\src\operations\add.php on line 18

    ReplyDelete
  15. Can please provide the Source code for the Search Filter in whole excel sheet after converting from Excel to a web page ??

    ReplyDelete
  16. This is awesome! Thanks aplenty

    ReplyDelete
  17. Thanks, but what if I only need to load an existing file, edit it and output the file again?

    ReplyDelete
  18. How to append a row to an existing Excel file from a MySQL Table?

    ReplyDelete
  19. But who would you skip the first row like header?

    ReplyDelete