Extract images from URL in excel with PHP using PhpSpreadsheet

There are various ways to extract images from a given URL. PHP contains built-in functions for extracting data including the images with a URL.

This article is for PHP code to extract images from URLs existing in an excel file.

I have used PhpSpreadsheet to read the URLs from an Excel file. Then, I created a cURL script to extract images from the URL.

PhpSpreadsheet library supports Excel read-write operations. It provides enormous features like formatting content, manipulating data and more. It has a rich set of built-in classes and thereby makes the development process easy.

Working with spreadsheets is a common need while handling excel data via programming. PhpSpreadsheet library reduces the developer’s effort in building applications with excel data handing.

We have already seen several examples of URL extract using PHP. Also, we have created code for getting video thumbnails from Youtube URL.

What is inside?

1. Uses of extracting images from URL from Excel
2. Advantages of PhpSpreadsheet Library
3. Existing PHP libraries used to import-export
4. File Structure
5. About this example
6. PHP code to load and extract image data
7. Render extracted images in a gallery
8. Database script
9. Extract images from URL in excel using PhpSpreadsheet Output

Uses of extracting images from URL read from an Excel

Extracting images from URLs from an excel file will be helpful in many scenarios. The below list shows some scenarios.

1. To import a large volume of images into your application’s media library.
2. To migrate media files from one domain to another.
3. To restore the Excel backup images into a database.
4. To create a dynamic photo gallery without a database.

Advantages of PhpSpreadsheet Library

PhpSpreadsheet has many features and thereby has more advantages of using it.

• It provides methods to prepare reports, charts, plans and more.
• It has an option the read, and write from a specified row, column and sheet of a spreadsheet document.
• It is suitable for handling a large amount of data.
• It helps to manage checklists, calendars, timesheets, schedules, and proposal plans.
• It provides security to protect spreadsheet data from editing.
• It supports encryption to prevent the spreadsheet data from viewing.

Existing PHP libraries used to import-export

There are many PHP libraries available in the market that support spreadsheet data handling.

• PortPHP supports import-export data between Excel, CSV and database storage. It has readers, writers and converters to process data exchange and manipulation.
• The Spout is a PHP library used to read and write spreadsheets in an efficient way. It supports three types of spreadsheets XLS, CSV, and ODS.

File structure

The below screenshot shows the file structure of this example. The ExcelImportService class file is an integral part of this example. It loads the PhpSpreadsheet library and covers all the operations related to the excel image extract.

The excel_template folder contains an input Excel file with image URLs. This example code loads this file to extract images from the URL.

Instead of using this fixed excel template, you can also allow users to choose an excel file. By adding a HTML form with a file input option users can choose their excel to explore extract.

About this example

This example loads an input Excel file in an Import service class. This sample excel file will contain image URLs.

In this example, I have used the PhpSpreadsheet library to read the excel data. This library method helps to get the URLs and store them in an array.

Then I iterate this URL array in a loop to extract the image data. I used the PHP cURL script to extract images. In a previous tutorial, we have seen how to run a PHP cURL script to extract content from a remote URL.

Finally, this code will store the extracted images in a directory and save the path to the database. In a previous article, we import excel data into a database without images. Also, we have seen examples to import data from CSV to a database.

PHP code to load and extract image data

This PHP code loads the ExcelImportService class to load and import image data from an excel.

This is the main PHP class created for this example. It handles all operations during the excel image extract.

<?php
use \Phppot\ExcelImportService;

require_once 'Class/ExcelImportService.php';

$excelImportService = new ExcelImportService();
$excelDataArray = $excelImportService->loadExcel();
if (! empty($excelDataArray)) {
    $isNewData = $excelImportService->importImages($excelDataArray);
    if ($isNewData) {
        $message = "Images extracted from excel successfully!";
    } else {
        $message = "No new images found during the excel extract!";
    }
}
$imageResult = $excelImportService->getAllImages();
?>

ExcelImportService.php

This class loads the PhpSpreadsheet library. It also has the DataSource instance in the class level.

The database access request from this class uses this instance. It is for saving the extracted image path to the database.

Note: Download the PhpSpreadsheet library from Github without dependencies. Then run the get the dependencies via composer by using the following command.

composer require phpoffice/phpspreadsheet

In this class, the loadExcel() function loads the input excel to read the URLs as an array. It returns this array to extract the image blob via a cURL request.

The extractImage() function executes the cURL script. It gets the image resource data from the remote URL read from Excel. Then it writes the file into a target as specified in this example.

After putting the extracted images into a folder, then the code saves the to the image database table. The saveImagePath() method contains the insert query and parameters to invoke the DataSource insert.

<?php 
namespace Phppot;

use \Phppot\DataSource;

require 'Vendor/PhpSpreadsheet/autoload.php';

class ExcelImportService {
    
    private $ds;
    
    function __construct()
    {
        require_once __DIR__ . './DataSource.php';
        $this->ds = new DataSource();
    }
    
    private function isUrlExist($url)
    {
        $query = 'SELECT * FROM tbl_images where remote_url = ?';
        $paramType = 's';
        $paramValue = array($url);
        $count = $this->ds->numRows($query, $paramType, $paramValue);
        return $count;
    }
    
    private function extractImage($url) 
    {
        $path = pathinfo($url);
        
        $imageTargetPath = 'uploads/' . time() . $path['basename'];
        
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_VERBOSE, 1);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($ch, CURLOPT_AUTOREFERER, false);
        curl_setopt($ch, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_1);
        curl_setopt($ch, CURLOPT_HEADER, 0);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0); // <-- important to specify
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0); // <-- important to specify
        $resultImage = curl_exec($ch);
        curl_close($ch);
        
        $fp = fopen($imageTargetPath, 'wb');
        fwrite($fp, $resultImage);
        fclose($fp);
        
        $imageInfo["image_name"] = $path['basename'];
        $imageInfo["image_path"] = $imageTargetPath;
        
        return $imageInfo;
    }
    
    private function saveImagePath($imageInfo, $remoteUrl) {
        $query = "INSERT INTO tbl_images (image_name,image_path, remote_url) VALUES (?, ?, ?)";
        $paramType = 'sss';
        $paramValue = array($imageInfo["image_name"], $imageInfo["image_path"], $remoteUrl);
        $this->ds->insert($query, $paramType, $paramValue);
    }
    
    public function loadExcel() 
    {
        //create directly an object instance of the IOFactory class, and load the xlsx file
        $xlsFile ='Excel_Template/imageURLs.xlsx';
        
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($xlsFile);
        
        //read excel data and store it into an array
        $excelData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        $rowCount = count($excelData);
        $urlArray = array();
        for($i=2;$i<$rowCount;$i++) {
            $url = $excelData[$i]['A'];
            if(!empty($url)) {
                $urlArray[] = $url;
            }
        }
        return $urlArray;
    }
    
    public function importImages($excelDataArray) 
    {
        $isNewData = false;
        foreach($excelDataArray as $url) {
            
            $isUrlExist = $this->isUrlExist($url);
            
            if (empty($isUrlExist)) {
                
                $imageInfo = $this->extractImage($url);
                
                if(!empty($imageInfo)) {
                    $this->saveImagePath($imageInfo, $url);
                }
                $isNewData = true;
            }
        }
        return $isNewData;
    }
    
    public function getAllImages() 
    {
        $query = 'SELECT * FROM tbl_images';
        $result = $this->ds->select($query);
        return $result;
    }
}

DataSource.php

This is a common PHP class that we have used in many examples. It contains functions to execute the database operations planned for the example code. It establishes the database connection at its constructor.

It is available in the project download zip available for free.
Model classes used in our PHP examples load this class and instantiate it to access the database.

Render extracted images in a gallery

This is the HTML code to display the extracted images in the UI. I embed PHP code with this HTML to display the image path from the database dynamically.

The getAllImages() method fetches image results from the database. It returns an array of images extracted from Excel. This array data iteration helps to render images in a gallery view.

<!doctype html>
<html>
<head>

<link rel="stylesheet" type="text/css" href="CSS/style.css">
<title>Extract Images from URL in Excel using PHPSpreadSheet with PHP</title>

</head>
<body>
    <div id="gallery">

        <div id="image-container">
            <h2>Extract Images from URL in Excel using PHPSpreadSheet
                with PHP</h2>
                <?php
                if (! empty($message)) {
                    ?>
            <div id="txtresponse"><?php echo $message; ?></div>
            <?php
                }
                ?>
            <ul id="image-list">
                <?php
                if (! empty($imageResult)) {
                    foreach ($imageResult as $k => $v) {
                        ?>
                        <li><img
                    src="<?php echo $imageResult[$k]['image_path']; ?>"
                    class="image-thumb"
                    alt="<?php echo $imageResult[$k]['image_name'];?>"></li>
                        <?php
                    }
                }
                ?>
             </ul>

        </div>

    </div>
</body>
</html>

After a successful image extract, this UI will acknowledge the user. It shows an appropriate message based on the image extract result.

If you extract an older excel that was already done, then the notification will say “No new images found”.

The following styles are used to present the extracted images in a gallery.

body {
    font-family: Arial;
    color: #212121;
    text-align: center;
}

#gallery {
    width: 1057px;
    margin: 0 auto;
}

#image-list {
    list-style-type: none;
    margin: 0;
    padding: 0;
}

#image-list li {
    margin: 10px 20px 10px 0px;
    display: inline-block;
}

#image-list li img {
    width: 250px;
    height: 155px;
}

#image-container {
    margin-bottom: 14px;
}

#txtresponse {
    padding: 10px 40px;
    border-radius: 3px;
    margin: 10px 0px 30px 0px;
    border: #ecdeaa 1px solid;
    color: #848483;
    background: #ffefb6;
    display: inline-block;
}

.btn-submit {
    padding: 10px 30px;
    background: #333;
    border: #E0E0E0 1px solid;
    color: #FFF;
    font-size: 0.9em;
    width: 100px;
    border-radius: 0px;
    cursor: pointer;
    position: absolute;
}

.image-thumb {
    background-color: grey;
    padding: 10px;
}

Database script

This SQL script is for creating the required database table in your environment. It has the create-statement of the tbl_images database table. This table is the storage point to store the image’s local path.

Run this script before executing this example. You can also get the SQL script from the downloadable source code added with this article.

CREATE TABLE IF NOT EXISTS `tbl_images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `image_name` varchar(50) NOT NULL,
  `image_path` varchar(50) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=263 ;

Extract images from URL in excel using PhpSpreadsheet Output

The screenshot below shows the image gallery output. These images are from the uploads folder of this example. This is the local location to store the extracted images from the database.

This screen shows the user acknowledgment message above the gallery view. This acknowledgment varies based on the input excel file data.

If the input excel is too older and extracted already, then the below message will notify the user.

Hope this article helps you to image extract from URLs present in excel. The example presented is the simplest way of demonstrating image extract from Excel.

Download

Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

↑ Back to Top

• PHP
  • Learn PHP
  • PHP Introduction
  • PHP Basics
  • PHP Shopping Cart
  • Payment Gateway
  • Contact Form
  • PHP Login Registration
  • Comments System
  • Types, Variables & Operators
  • PHP Strings
  • PHP Arrays
  • PHP Functions
  • PHP OOPS
  • Event Management System
  • PHP Mail
  • PHP Forms
  • Advanced
  • PHP AJAX
  • RESTful API
  • PHP Databases
  • PHP Sessions and Cookies
  • Error and Exception Handling
  • File Upload
  • File Import Export
    • How to Export Contact as vCard using PHP
    • Export DataTables Data using PHP and MySQL
    • Extract images from URL in excel with PHP using PhpSpreadsheet
    • Database Data Export to Excel File using PHP
    • PHP Excel Export Code (Data to File)
  • Files and Directories
  • PHP Date Time
  • PHP XML
  • PHP CSV
  • PHP JSON
  • PHP Code Samples
  • Library
  • PHP Freelancer
• jQuery
• JavaScript
• WordPress
• MySQL
• CSS

• Share
• Tweet
• Share

1. Simple PHP Shopping Cart
2. Stripe Payment Gateway Integration using PHP
3. User Registration in PHP with Login: Form with MySQL and Code Download
4. PHP Contact Form
5. How to Create Dynamic Stacked Bar, Doughnut and Pie charts in PHP with Chart.js