01-29-2020, 10:42 AM
Extract images from URL in excel with PHP using PhpSpreadsheet
<div style="margin: 5px 5% 10px 5%;"><img src="https://www.sickgaming.net/blog/wp-content/uploads/2020/01/extract-images-from-url-in-excel-with-php-using-phpspreadsheet.jpg" width="550" height="288" title="" alt="" /></div><div><p>Last modified on August 7th, 2019 by Vincy.</p>
<p>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.</p>
<p>This article is for PHP code to extract images from URLs existing in an excel file.</p>
<p>I have used PhpSpreadsheet to read the URLs from an Excel file. Then, I created cURL script to extract images from the URL.</p>
<p><img class="alignnone wp-image-10416 size-large" src="https://phppot.com/wp-content/uploads/2019/08/extract-images-from-url-read-from-excel-550x288.jpg" alt="Extract Images from URL Read from Excel" width="550" height="288" srcset="https://phppot.com/wp-content/uploads/2019/08/extract-images-from-url-read-from-excel-550x288.jpg 550w, https://phppot.com/wp-content/uploads/20...00x157.jpg 300w, https://phppot.com/wp-content/uploads/20...68x402.jpg 768w, https://phppot.com/wp-content/uploads/20...-excel.jpg 1200w" sizes="(max-width: 550px) 100vw, 550px"></p>
<p>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.</p>
<p>Working with spreadsheets is a common need while handling excel data via programming. PhpSpreadsheet library reduces the developer’s effort on building applications with excel data handing.</p>
<p>We have already seen several <a href="https://phppot.com/jquery/facebook-style-url-extract-with-php-and-jquery-ajax/">examples of URL extract using PHP</a>. Also, we have created code for getting <a href="https://phppot.com/php/extracting-title-description-thumbnail-using-youtube-data-api/">video thumbnail from Youtube URL</a>.</p>
<h2>What is inside?</h2>
<ol>
<li><a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#uses-of-extracting-images-from-url-during-excel-import">Uses of extracting images from URL from Excel</a></li>
<li><a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#advantages-of-using-phpspreadsheet">Advantages of PhpSpreadsheet Library</a></li>
<li><a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#existing-php-libraries-used-to-import-export">Existing PHP libraries used to import-export</a></li>
<li><a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#file-structure">File Structure</a></li>
<li><a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#about-this-example">About this example</a></li>
<li><a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#php-code-to-load-and-extract-image-data">PHP code to load and extract image data</a></li>
<li><a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#displaying-the-images-in-a-gallery">Render extracted images in a gallery </a></li>
<li><a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#database-script">Database script </a></li>
<li><a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#extract-images-from-url-uisng-phpspreadsheet-output">Extract images from URL in excel using PhpSpreadsheet Output </a></li>
</ol>
<p>Extracting of images from URL from an excel file will be helpful in many scenarios. Below list shows some scenarios.</p>
<ol>
<li>To import a large volume of images into your application’s media library.</li>
<li>To migrate media files from one domain to another.</li>
<li>To restore the Excel backup images into a database.</li>
<li>To create a dynamic photo gallery without a database.</li>
</ol>
<h2 id="advantages-of-using-phpspreadsheet">Advantages of PhpSpreadsheet Library</h2>
<p>PhpSpreadsheet has many features and thereby has more advantages of using it.</p>
<ul>
<li>It provides methods to prepare reports, charts, plans and more.</li>
<li>It has an option the read, write from a specified row, column and sheet of a spreadsheet document.</li>
<li>It is suitable for handling a large amount of data.</li>
<li>It helps to manage checklists, calendars, timesheets, schedules, proposal plans.</li>
<li>It provides security to protect spreadsheet data from editing.</li>
<li>It supports encryption to prevent the spreadsheet data from viewing. </li>
</ul>
<h2 id="existing-php-libraries-used-to-import-export">Existing PHP libraries used to import-export</h2>
<p>There are many PHP libraries available in the market support spreadsheet data handling.</p>
<ul>
<li><a href="https://github.com/portphp/portphp" target="_blank" rel="noopener noreferrer">PortPHP</a> supports import-export data between Excel, CSV and database storages. It has readers, writers and converters to process data exchange and manipulation.</li>
<li>The <a href="https://github.com/box/spout" target="_blank" rel="noopener noreferrer">Spout</a> is a PHP library used to read write spreadsheets in an efficient way. It supports three types of spreadsheets XLS, CSV, ODS.</li>
</ul>
<h2 id="file-structure">File structure</h2>
<p>Below screenshot shows the file structure of this example. The ExcelImportService class file is an integral part of this example. It loads PhpSpreadsheet library and covers all the operations related to the excel image extract.</p>
<p>The excel_template folder contains an input Excel file with image URLs. This example code loads this file to extract images from the URL.</p>
<p>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 user can choose their excel to explore extract.</p>
<p><img class="alignnone size-full wp-image-10391" src="https://phppot.com/wp-content/uploads/2019/08/extract-images-from-url-from-the-excel-file-structure.jpg" alt="Extract Images from URL from the Excel File Structure" width="371" height="256" srcset="https://phppot.com/wp-content/uploads/2019/08/extract-images-from-url-from-the-excel-file-structure.jpg 371w, https://phppot.com/wp-content/uploads/20...00x207.jpg 300w" sizes="(max-width: 371px) 100vw, 371px"></p>
<h2 id="loading-data-from-excel">About this example</h2>
<p>This example loads an input Excel file in an Import service class. This sample excel file will contain image URLs.</p>
<p>In this example, I have used PhpSpreadsheet library to read the excel data. This library method helps to get the URLs and store into an array.</p>
<p>Then I iterate this URL array in a loop to extract the image data. I used PHP cURL script to extract images. In a previous tutorial, we have seen how to <a href="https://phppot.com/php/php-curl/">run PHP cURL script to extract content</a> from a remote URL.</p>
<p>Finally, this code will store the extracted images into a directory and save the path to the database. In a previous article, we <a href="https://phppot.com/php/import-excel-file-into-mysql-database-using-php/">import excel data into a database</a> without images. Also, we have seen examples to <a href="https://phppot.com/php/import-csv-file-into-mysql-using-php/">import data from CSV to a database</a>.</p>
<h2 id="php-code-to-load-and-extract-image-data">PHP code to load and extract image data</h2>
<p>This PHP code loads the ExcelImportService class to load and import image data from an excel.</p>
<p>This is the main PHP class created for this example. It handles all operations during the excel image extract.</p>
<pre class="prettyprint lang-php"><?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(); ?> </pre>
<h3>ExcelImportService.php</h3>
<p>This class loads the PhpSpreadsheet library. It also has the DataSource instance in the class level.</p>
<p>The database access request from this class uses this instance. It is for saving the extracted image path to the database.</p>
<p><strong>Note:</strong> <a href="https://github.com/PHPOffice/PhpSpreadsheet" target="_blank" rel="noopener noreferrer">Download PhpSpreadsheet library</a> from Github without dependencies. Then run the get the dependencies via composer by using the following command.</p>
<pre class="prettyprint lang-php">composer require phpoffice/phpspreadsheet </pre>
<p>In this class, the <em>loadExcel() </em>function loads the input excel to read the URLs as an array. It returns this array to extract image blob via cURL request. </p>
<p>The <em>extractImage() </em>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.</p>
<p>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 DataSource insert.</p>
<pre class="prettyprint lang-php"><?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; } }</pre>
<h3>DataSource.php</h3>
<p>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.</p>
<p>Model classes used in our PHP examples load this class and instantiate it to access the database.</p>
<pre class="prettyprint lang-php"><?php namespace Phppot; /** * Generic datasource class for handling DB operations. * Uses MySqli and PreparedStatements. * * @version 2.3 */ class DataSource { // PHP 7.1.0 visibility modifiers are allowed for class constants. // when using above 7.1.0, declare the below constants as private const HOST = 'localhost'; const USERNAME = 'root'; const PASSWORD = ''; const DATABASENAME = 'phpsamples'; private $conn; /** * PHP implicitly takes care of cleanup for default connection types. * So no need to worry about closing the connection. * * Singletons not required in PHP as there is no * concept of shared memory. * Every object lives only for a request. * * Keeping things simple and that works! */ function __construct() { $this->conn = $this->getConnection(); } /** * If connection object is needed use this method and get access to it. * Otherwise, use the below methods for insert / update / etc. * * @return \mysqli */ public function getConnection() { $conn = new \mysqli(self::HOST, self::USERNAME, self::PASSWORD, self::DATABASENAME); if (mysqli_connect_errno()) { trigger_error("Problem with connecting to database."); } $conn->set_charset("utf8"); return $conn; } /** * To get database results * @param string $query * @param string $paramType * @param array $paramArray * @return array */ public function select($query, $paramType="", $paramArray=array()) { $stmt = $this->conn->prepare($query); if(!empty($paramType) && !empty($paramArray)) { $this->bindQueryParams($sql, $paramType, $paramArray); } $stmt->execute(); $result = $stmt->get_result(); if ($result->num_rows > 0) { while ($row = $result->fetch_assoc()) { $resultset[] = $row; } } if (! empty($resultset)) { return $resultset; } } /** * To insert * @param string $query * @param string $paramType * @param array $paramArray * @return int */ public function insert($query, $paramType, $paramArray) { $stmt = $this->conn->prepare($query); $this->bindQueryParams($stmt, $paramType, $paramArray); $stmt->execute(); $insertId = $stmt->insert_id; return $insertId; } /** * To execute query * @param string $query * @param string $paramType * @param array $paramArray */ public function execute($query, $paramType="", $paramArray=array()) { $stmt = $this->conn->prepare($query); if(!empty($paramType) && !empty($paramArray)) { $this->bindQueryParams($stmt, $paramType="", $paramArray=array()); } $stmt->execute(); } /** * 1. Prepares parameter binding * 2. Bind prameters to the sql statement * @param string $stmt * @param string $paramType * @param array $paramArray */ public function bindQueryParams($stmt, $paramType, $paramArray=array()) { $paramValueReference[] = & $paramType; for ($i = 0; $i < count($paramArray); $i ++) { $paramValueReference[] = & $paramArray[$i]; } call_user_func_array(array( $stmt, 'bind_param' ), $paramValueReference); } /** * To get database results * @param string $query * @param string $paramType * @param array $paramArray * @return array */ public function numRows($query, $paramType="", $paramArray=array()) { $stmt = $this->conn->prepare($query); if(!empty($paramType) && !empty($paramArray)) { $this->bindQueryParams($stmt, $paramType, $paramArray); } $stmt->execute(); $stmt->store_result(); $recordCount = $stmt->num_rows; return $recordCount; } } </pre>
<h2 id="displaying-the-images-in-a-gallery">Render extracted images in a gallery</h2>
<p>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.</p>
<p>The <em>getAllImages()</em> method fetches image results from the database. It returns an array of images extracted from the Excel. This array data iteration helps to render images in a gallery view.</p>
<pre class="prettyprint lang-php"><!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> </pre>
<p>After a successful image extract, this UI will acknowledge the user. It shows an appropriate message based on the image extract result.</p>
<p>If you extract an older excel that was already done, then the notification will say “No new images found”.</p>
<p>The following styles are used to present the extracted images in a gallery.</p>
<pre class="prettyprint lang-php">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; } </pre>
<h2 id="database-script">Database script</h2>
<p>This SQL script is for creating the required database table in your environment. It has the create a statement of the <em>tbl_images </em>database table. This table is the storage the point to store the image local path.</p>
<p>Run this script before executing this example. You can also get the SQL script from the downloadable source code added with this article.</p>
<pre class="prettyprint lang-php">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 ; </pre>
<p>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.</p>
<p>This screen shows the user acknowledgment message above the gallery view. This acknowledgment varies based on the input excel file data.</p>
<p><a href="https://phppot.com/wp-content/uploads/2019/08/extract-images-from-url-from-the-excel-output.jpg"><img class="alignnone size-large wp-image-10398" src="https://phppot.com/wp-content/uploads/2019/08/extract-images-from-url-from-the-excel-output-550x197.jpg" alt="Extract Images from URL from the Excel Output" width="550" height="197" srcset="https://phppot.com/wp-content/uploads/2019/08/extract-images-from-url-from-the-excel-output-550x197.jpg 550w, https://phppot.com/wp-content/uploads/20...00x107.jpg 300w, https://phppot.com/wp-content/uploads/20...68x275.jpg 768w, https://phppot.com/wp-content/uploads/20...output.jpg 892w" sizes="(max-width: 550px) 100vw, 550px"></a></p>
<p>If the input excel is too older and extracted already, then the below message will notify the user.</p>
<p><a href="https://phppot.com/wp-content/uploads/2019/08/no-new-images.jpg"><img class="alignnone size-full wp-image-10399" src="https://phppot.com/wp-content/uploads/2019/08/no-new-images.jpg" alt="No New Images" width="437" height="68" srcset="https://phppot.com/wp-content/uploads/2019/08/no-new-images.jpg 437w, https://phppot.com/wp-content/uploads/20...300x47.jpg 300w" sizes="(max-width: 437px) 100vw, 437px"></a></p>
<p>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.</p>
<p><a class="download" href="https://phppot.com/downloads/extract-images-from-url-from-excel-using-phpspreadsheet.zip">Download</a></p>
<p> <!-- #comments --> </p>
<div class="related-articles">
<h2>Popular Articles</h2>
</p></div>
<p> <a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#top" class="top">↑ Back to Top</a> </p>
</div>
https://www.sickgaming.net/blog/2019/08/...readsheet/
<div style="margin: 5px 5% 10px 5%;"><img src="https://www.sickgaming.net/blog/wp-content/uploads/2020/01/extract-images-from-url-in-excel-with-php-using-phpspreadsheet.jpg" width="550" height="288" title="" alt="" /></div><div><p>Last modified on August 7th, 2019 by Vincy.</p>
<p>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.</p>
<p>This article is for PHP code to extract images from URLs existing in an excel file.</p>
<p>I have used PhpSpreadsheet to read the URLs from an Excel file. Then, I created cURL script to extract images from the URL.</p>
<p><img class="alignnone wp-image-10416 size-large" src="https://phppot.com/wp-content/uploads/2019/08/extract-images-from-url-read-from-excel-550x288.jpg" alt="Extract Images from URL Read from Excel" width="550" height="288" srcset="https://phppot.com/wp-content/uploads/2019/08/extract-images-from-url-read-from-excel-550x288.jpg 550w, https://phppot.com/wp-content/uploads/20...00x157.jpg 300w, https://phppot.com/wp-content/uploads/20...68x402.jpg 768w, https://phppot.com/wp-content/uploads/20...-excel.jpg 1200w" sizes="(max-width: 550px) 100vw, 550px"></p>
<p>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.</p>
<p>Working with spreadsheets is a common need while handling excel data via programming. PhpSpreadsheet library reduces the developer’s effort on building applications with excel data handing.</p>
<p>We have already seen several <a href="https://phppot.com/jquery/facebook-style-url-extract-with-php-and-jquery-ajax/">examples of URL extract using PHP</a>. Also, we have created code for getting <a href="https://phppot.com/php/extracting-title-description-thumbnail-using-youtube-data-api/">video thumbnail from Youtube URL</a>.</p>
<h2>What is inside?</h2>
<ol>
<li><a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#uses-of-extracting-images-from-url-during-excel-import">Uses of extracting images from URL from Excel</a></li>
<li><a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#advantages-of-using-phpspreadsheet">Advantages of PhpSpreadsheet Library</a></li>
<li><a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#existing-php-libraries-used-to-import-export">Existing PHP libraries used to import-export</a></li>
<li><a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#file-structure">File Structure</a></li>
<li><a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#about-this-example">About this example</a></li>
<li><a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#php-code-to-load-and-extract-image-data">PHP code to load and extract image data</a></li>
<li><a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#displaying-the-images-in-a-gallery">Render extracted images in a gallery </a></li>
<li><a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#database-script">Database script </a></li>
<li><a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#extract-images-from-url-uisng-phpspreadsheet-output">Extract images from URL in excel using PhpSpreadsheet Output </a></li>
</ol>
<p>Extracting of images from URL from an excel file will be helpful in many scenarios. Below list shows some scenarios.</p>
<ol>
<li>To import a large volume of images into your application’s media library.</li>
<li>To migrate media files from one domain to another.</li>
<li>To restore the Excel backup images into a database.</li>
<li>To create a dynamic photo gallery without a database.</li>
</ol>
<h2 id="advantages-of-using-phpspreadsheet">Advantages of PhpSpreadsheet Library</h2>
<p>PhpSpreadsheet has many features and thereby has more advantages of using it.</p>
<ul>
<li>It provides methods to prepare reports, charts, plans and more.</li>
<li>It has an option the read, write from a specified row, column and sheet of a spreadsheet document.</li>
<li>It is suitable for handling a large amount of data.</li>
<li>It helps to manage checklists, calendars, timesheets, schedules, proposal plans.</li>
<li>It provides security to protect spreadsheet data from editing.</li>
<li>It supports encryption to prevent the spreadsheet data from viewing. </li>
</ul>
<h2 id="existing-php-libraries-used-to-import-export">Existing PHP libraries used to import-export</h2>
<p>There are many PHP libraries available in the market support spreadsheet data handling.</p>
<ul>
<li><a href="https://github.com/portphp/portphp" target="_blank" rel="noopener noreferrer">PortPHP</a> supports import-export data between Excel, CSV and database storages. It has readers, writers and converters to process data exchange and manipulation.</li>
<li>The <a href="https://github.com/box/spout" target="_blank" rel="noopener noreferrer">Spout</a> is a PHP library used to read write spreadsheets in an efficient way. It supports three types of spreadsheets XLS, CSV, ODS.</li>
</ul>
<h2 id="file-structure">File structure</h2>
<p>Below screenshot shows the file structure of this example. The ExcelImportService class file is an integral part of this example. It loads PhpSpreadsheet library and covers all the operations related to the excel image extract.</p>
<p>The excel_template folder contains an input Excel file with image URLs. This example code loads this file to extract images from the URL.</p>
<p>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 user can choose their excel to explore extract.</p>
<p><img class="alignnone size-full wp-image-10391" src="https://phppot.com/wp-content/uploads/2019/08/extract-images-from-url-from-the-excel-file-structure.jpg" alt="Extract Images from URL from the Excel File Structure" width="371" height="256" srcset="https://phppot.com/wp-content/uploads/2019/08/extract-images-from-url-from-the-excel-file-structure.jpg 371w, https://phppot.com/wp-content/uploads/20...00x207.jpg 300w" sizes="(max-width: 371px) 100vw, 371px"></p>
<h2 id="loading-data-from-excel">About this example</h2>
<p>This example loads an input Excel file in an Import service class. This sample excel file will contain image URLs.</p>
<p>In this example, I have used PhpSpreadsheet library to read the excel data. This library method helps to get the URLs and store into an array.</p>
<p>Then I iterate this URL array in a loop to extract the image data. I used PHP cURL script to extract images. In a previous tutorial, we have seen how to <a href="https://phppot.com/php/php-curl/">run PHP cURL script to extract content</a> from a remote URL.</p>
<p>Finally, this code will store the extracted images into a directory and save the path to the database. In a previous article, we <a href="https://phppot.com/php/import-excel-file-into-mysql-database-using-php/">import excel data into a database</a> without images. Also, we have seen examples to <a href="https://phppot.com/php/import-csv-file-into-mysql-using-php/">import data from CSV to a database</a>.</p>
<h2 id="php-code-to-load-and-extract-image-data">PHP code to load and extract image data</h2>
<p>This PHP code loads the ExcelImportService class to load and import image data from an excel.</p>
<p>This is the main PHP class created for this example. It handles all operations during the excel image extract.</p>
<pre class="prettyprint lang-php"><?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(); ?> </pre>
<h3>ExcelImportService.php</h3>
<p>This class loads the PhpSpreadsheet library. It also has the DataSource instance in the class level.</p>
<p>The database access request from this class uses this instance. It is for saving the extracted image path to the database.</p>
<p><strong>Note:</strong> <a href="https://github.com/PHPOffice/PhpSpreadsheet" target="_blank" rel="noopener noreferrer">Download PhpSpreadsheet library</a> from Github without dependencies. Then run the get the dependencies via composer by using the following command.</p>
<pre class="prettyprint lang-php">composer require phpoffice/phpspreadsheet </pre>
<p>In this class, the <em>loadExcel() </em>function loads the input excel to read the URLs as an array. It returns this array to extract image blob via cURL request. </p>
<p>The <em>extractImage() </em>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.</p>
<p>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 DataSource insert.</p>
<pre class="prettyprint lang-php"><?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; } }</pre>
<h3>DataSource.php</h3>
<p>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.</p>
<p>Model classes used in our PHP examples load this class and instantiate it to access the database.</p>
<pre class="prettyprint lang-php"><?php namespace Phppot; /** * Generic datasource class for handling DB operations. * Uses MySqli and PreparedStatements. * * @version 2.3 */ class DataSource { // PHP 7.1.0 visibility modifiers are allowed for class constants. // when using above 7.1.0, declare the below constants as private const HOST = 'localhost'; const USERNAME = 'root'; const PASSWORD = ''; const DATABASENAME = 'phpsamples'; private $conn; /** * PHP implicitly takes care of cleanup for default connection types. * So no need to worry about closing the connection. * * Singletons not required in PHP as there is no * concept of shared memory. * Every object lives only for a request. * * Keeping things simple and that works! */ function __construct() { $this->conn = $this->getConnection(); } /** * If connection object is needed use this method and get access to it. * Otherwise, use the below methods for insert / update / etc. * * @return \mysqli */ public function getConnection() { $conn = new \mysqli(self::HOST, self::USERNAME, self::PASSWORD, self::DATABASENAME); if (mysqli_connect_errno()) { trigger_error("Problem with connecting to database."); } $conn->set_charset("utf8"); return $conn; } /** * To get database results * @param string $query * @param string $paramType * @param array $paramArray * @return array */ public function select($query, $paramType="", $paramArray=array()) { $stmt = $this->conn->prepare($query); if(!empty($paramType) && !empty($paramArray)) { $this->bindQueryParams($sql, $paramType, $paramArray); } $stmt->execute(); $result = $stmt->get_result(); if ($result->num_rows > 0) { while ($row = $result->fetch_assoc()) { $resultset[] = $row; } } if (! empty($resultset)) { return $resultset; } } /** * To insert * @param string $query * @param string $paramType * @param array $paramArray * @return int */ public function insert($query, $paramType, $paramArray) { $stmt = $this->conn->prepare($query); $this->bindQueryParams($stmt, $paramType, $paramArray); $stmt->execute(); $insertId = $stmt->insert_id; return $insertId; } /** * To execute query * @param string $query * @param string $paramType * @param array $paramArray */ public function execute($query, $paramType="", $paramArray=array()) { $stmt = $this->conn->prepare($query); if(!empty($paramType) && !empty($paramArray)) { $this->bindQueryParams($stmt, $paramType="", $paramArray=array()); } $stmt->execute(); } /** * 1. Prepares parameter binding * 2. Bind prameters to the sql statement * @param string $stmt * @param string $paramType * @param array $paramArray */ public function bindQueryParams($stmt, $paramType, $paramArray=array()) { $paramValueReference[] = & $paramType; for ($i = 0; $i < count($paramArray); $i ++) { $paramValueReference[] = & $paramArray[$i]; } call_user_func_array(array( $stmt, 'bind_param' ), $paramValueReference); } /** * To get database results * @param string $query * @param string $paramType * @param array $paramArray * @return array */ public function numRows($query, $paramType="", $paramArray=array()) { $stmt = $this->conn->prepare($query); if(!empty($paramType) && !empty($paramArray)) { $this->bindQueryParams($stmt, $paramType, $paramArray); } $stmt->execute(); $stmt->store_result(); $recordCount = $stmt->num_rows; return $recordCount; } } </pre>
<h2 id="displaying-the-images-in-a-gallery">Render extracted images in a gallery</h2>
<p>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.</p>
<p>The <em>getAllImages()</em> method fetches image results from the database. It returns an array of images extracted from the Excel. This array data iteration helps to render images in a gallery view.</p>
<pre class="prettyprint lang-php"><!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> </pre>
<p>After a successful image extract, this UI will acknowledge the user. It shows an appropriate message based on the image extract result.</p>
<p>If you extract an older excel that was already done, then the notification will say “No new images found”.</p>
<p>The following styles are used to present the extracted images in a gallery.</p>
<pre class="prettyprint lang-php">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; } </pre>
<h2 id="database-script">Database script</h2>
<p>This SQL script is for creating the required database table in your environment. It has the create a statement of the <em>tbl_images </em>database table. This table is the storage the point to store the image local path.</p>
<p>Run this script before executing this example. You can also get the SQL script from the downloadable source code added with this article.</p>
<pre class="prettyprint lang-php">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 ; </pre>
<p>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.</p>
<p>This screen shows the user acknowledgment message above the gallery view. This acknowledgment varies based on the input excel file data.</p>
<p><a href="https://phppot.com/wp-content/uploads/2019/08/extract-images-from-url-from-the-excel-output.jpg"><img class="alignnone size-large wp-image-10398" src="https://phppot.com/wp-content/uploads/2019/08/extract-images-from-url-from-the-excel-output-550x197.jpg" alt="Extract Images from URL from the Excel Output" width="550" height="197" srcset="https://phppot.com/wp-content/uploads/2019/08/extract-images-from-url-from-the-excel-output-550x197.jpg 550w, https://phppot.com/wp-content/uploads/20...00x107.jpg 300w, https://phppot.com/wp-content/uploads/20...68x275.jpg 768w, https://phppot.com/wp-content/uploads/20...output.jpg 892w" sizes="(max-width: 550px) 100vw, 550px"></a></p>
<p>If the input excel is too older and extracted already, then the below message will notify the user.</p>
<p><a href="https://phppot.com/wp-content/uploads/2019/08/no-new-images.jpg"><img class="alignnone size-full wp-image-10399" src="https://phppot.com/wp-content/uploads/2019/08/no-new-images.jpg" alt="No New Images" width="437" height="68" srcset="https://phppot.com/wp-content/uploads/2019/08/no-new-images.jpg 437w, https://phppot.com/wp-content/uploads/20...300x47.jpg 300w" sizes="(max-width: 437px) 100vw, 437px"></a></p>
<p>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.</p>
<p><a class="download" href="https://phppot.com/downloads/extract-images-from-url-from-excel-using-phpspreadsheet.zip">Download</a></p>
<p> <!-- #comments --> </p>
<div class="related-articles">
<h2>Popular Articles</h2>
</p></div>
<p> <a href="https://phppot.com/php/extract-images-from-url-in-excel-with-php-using-phpspreadsheet/#top" class="top">↑ Back to Top</a> </p>
</div>
https://www.sickgaming.net/blog/2019/08/...readsheet/