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

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>

Download Source Code

Remaining source code will be added very soon.

29 comments:

Unknown14 May 2020 at 08:56
very useful thank you so much !
Unknown30 May 2020 at 23:55
It's very helpful, thanks very much from Viet Nam <3
venu29 July 2020 at 01:29
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
venu29 July 2020 at 01:29
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
Unknown18 August 2020 at 11:40
thanks a lot its working ..
مجتبی22 September 2020 at 01:15
it doesn`t work for me.? why? Could you please help me?
مجتبی22 September 2020 at 03:22
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".
Unknown25 October 2020 at 03:30
Only two rows are updating, Could you please help me on this
Jonathan D. Diaz Aranda28 October 2020 at 15:02
Muchas gracias
zidou12 November 2020 at 08:24
very useful easy to use and implement thanks so much
Unknown21 December 2020 at 04:12
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.
Faisal17 February 2021 at 11:43
Thank you for saving me..
aten10 March 2021 at 23:49
Thank you.
Network Handling Council (NHC)20 March 2021 at 21:30
include 'vendor/autoload.php'; <- would you please share this file.
Mohamed Shehata8 April 2021 at 11:38
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;
}
maya13 April 2021 at 02:57
include 'vendor/autoload.php';
where is this file codeignator 3
Mawan A. Nugroho23 April 2021 at 18:44
How do I create an editable sheet in a web browser? Like Google Sheet. We can copy and paste range.
Unknown28 May 2021 at 07:10
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
FindMyJob30 May 2021 at 07:14
Can please provide the Source code for the Search Filter in whole excel sheet after converting from Excel to a web page ??
hghg19 August 2021 at 09:46
well done its so helpful
Unknown16 November 2021 at 10:52
This is awesome! Thanks aplenty
Unknown16 November 2021 at 10:54
Thanks, but what if I only need to load an existing file, edit it and output the file again?
Jason19 February 2022 at 12:46
How to append a row to an existing Excel file from a MySQL Table?
Anonymous21 March 2022 at 23:27
But who would you skip the first row like header?