How do i download phpspreadsheet?

Question

View Discussion

Table of Contents Show

What is PHPSpreadSheet Library
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
How do I download PhpSpreadsheet library?
What is PhpSpreadsheet?
How install PHP Excel?
How do I migrate from PhpSpreadsheet to PHPExcel?

Improve Article

Save Article

Read

Discuss

View Discussion

Improve Article

Save Article

Introduction: PHPSpreadsheet is a library written in PHP which helps to read from and write to different types of spreadsheet file formats with the help of a given set of classes. The various format which support spreadsheet are Excel(.xlsx), Open Document Format(.ods),SpreadsheetML(.xml), CSV and many more.

Advantages:

Easy and effective comparisons.
Powerful analysis of large amounts of data.

Usability:

Agendas
Budgets
Calendars
Cards
Charts and Diagrams
Financial Tools (Loan calculators etc.)
Flyers
Forms
Inventories
Invoices
Lists and to-do checklists
Planners
Plans and proposals
Reports
Schedules
Timesheets

Requirements: The following software is developed using PHPSpreadsheet:

PHP version 5.6 or newer
PHP extension php_zip enabled
PHP extension php_xml enabled
PHP extension php_gd2 enabled

Installation: The PHPSpreadsheet can be installed with the help of Composer

On Terminal: The following command runs on the terminal to install PHPSpreadsheet:

composer require phpoffice/phpspreadsheet

Example 1:

PHP

<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();

$sheet->setCellValue('A1', 'GeeksForGeeks!');

$sheet->setCellValue('B1', 'A Computer Science Portal For Geeks');

$writer = new Xlsx($spreadsheet);

$writer->save('gfg.xlsx');

?>

Output:

Example 2:

PHP

<?php

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('gfg.xlsx');

$data = array(1,$spreadsheet->getActiveSheet()

->toArray(null,true,true,true));

var_dump($data);

?>

Output:

array(2) { 
    [0]=> int(1) 
    [1]=> array(1) { 
        [1]=> array(2) {
            ["A"]=> string(14) "GeeksForGeeks!" 
            ["B"]=> string(35) "A Computer Science Portal For Geeks"
        } 
    }
}

Reference: https://phpspreadsheet.readthedocs.io/en/develop/

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.

Load Excel File Data in Browser
Convert HTML Table Data to Excel Sheet Data
Import Data From Xls, Xlsx or CSV file to Mysql
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.

How do I download PhpSpreadsheet library?

Use composer to install PhpSpreadsheet into your project. Or also download the documentation and samples if you plan to use them. A good way to get started is to run some of the samples. Don't forget to download them via --prefer-source composer flag.

What is PhpSpreadsheet?

Introduction: PHPSpreadsheet is a library written in PHP which helps to read from and write to different types of spreadsheet file formats with the help of a given set of classes. The various format which support spreadsheet are Excel(. xlsx), Open Document Format(. ods),SpreadsheetML(. xml), CSV and many more.

How install PHP Excel?

1:- Add "phpoffice/phpexcel": "dev-master" to your composer. json. 2:- execute "composer update" on terminal. 3:- Open the file "/vendor/composer/autoload_namespaces.