How to Fetch API Data using cURL and Import Data into Excel

Your support helps keep this blog running! Secure payments via Paypal and Stripe.

A couple of weeks ago, one of my friends asked me to help with his Thesis. Basically, he needs to fetch the data from the API and extract some data into an Excel or CSV file. So in this tutorial, I will share how to fetch the data from the API using the cURL extension from PHP and then extract the data we want. Finally, we import it into the Excel file. Stay tuned!

Prepare Our Developing Environment

The easy way you can follow this tutorial is to install one of these local servers. They are the local server bundle for PHP (programming language), Apache (web server), and MySQL/MariaDB (database).

WampServer

I normally use WampServer since it is easy to install and I can simply install additional PHP versions, Apache versions, and MySQL/MariaDB versions I want to meet my client’s server environment.

XAMPP

Another option is XAMPP. It contains PHP, MariaDB, and Apache.

VSCode (Code editor tool)

I use VSCode nowadays since it is working fast and provides a ton of useful extensions for development. Here you can install from this link.

Enable cURL Extension

Once your local server environment is ready. We will enable the cURL extension for PHP.

What is cURL

In short, cURL is a command-line tool that is used to transfer data to and from a server. cURL lets us talk to a server by specifying the location, which is a URL form, and the data we want to send. cURL supports both HTTP and HTTPS protocols.

How to Enable the cURL Extension

Normally, when you install the local server bundle I mentioned above, cURL should come with it. Sometimes, cURL is enabled by default, but sometimes it may not be. Below is how to enable cURL in the local server.

Enable cURL in WampServer

• Start Wampserver
• Click on the WampServer icon (green color)
• Select the PHP menu, then select the PHP extension
• Click on curl
• Restart Wampserver

Enable cURL in XAMPP

• Open xampp\php\php.ini
• In php.ini, uncomment by removing the semi-colon(;) at the beginning of the “extension=php_curl.dll” line.
• Restart XAMPP

Basically, you want to enable the cURL extension in the php.ini from the PHP folder. To enable the PHP extensions in the php.ini, you just remove the semicolon (;) at the beginning of the specific extension line you want to enable. Finally, you must restart the local server to see the changes.

How Do I Know cURL is Enabled?

• At the local server root directory (www folder)
• Create a new PHP file named checkcurl.php.

phpinfo();

• Next, start the local server (WampServer or XAMPP)
• Then we will run the checkcurl.php at the localhost by entering “http://localhost/checkcurl.php” at the browser.
  • Note that, “http://localhost” is your local server address.
• Once you enter the checkcurl.php in the browser, you will see the server environments list, including cURL on the browser. Look for the cURL support section; it should say enabled. See the screenshot below.

Now cURL is enabled. We will start to fetch the API using cURL.

Fake Store API

In this tutorial, I will use the Fake API from Fake Store API. The API data will be a JSON format.

Fetching Data from API using cURL

Create a New index.php File

At the local server root directory (www folder), create a new index.php file and add the code below.

Fetch API Data using cURL

// ----------------------------------- //
// ---- 1. Fetch API data using cURL ---- //
// ----------------------------------- //

// Initiate curl session in a variable($curl_handle)
$curl_handle = curl_init();

$url = 'https://fakestoreapi.com/users'; // an API URL you want to fetch the data from. 
// Disable SSL verification:
// Since we call the API via our local server with HTTP protocal, so we will disable SSL verification.
// Otherwise, we can not call API.
curl_setopt($curl_handle, CURLOPT_SSL_VERIFYPEER, false);

// Set the curl URL option
curl_setopt($curl_handle, CURLOPT_URL, $url);

// This option will return data as a string instead of direct output
curl_setopt($curl_handle, CURLOPT_RETURNTRANSFER, true);

// Execute curl & store data in a variable
$curl_data = curl_exec($curl_handle);

curl_close($curl_handle);

In the code above, we have already fetched the API data and stored it in a $curl_data variable. In order to see the data on the screen. We need to decode the data and print out the decoded data on the screen. So add the code below under the curl_close($curl_handle) line.

// ----------------------------------- //
// ---- 2. Decode JSON into PHP array ---- //
// ----------------------------------- //
$response_data = json_decode($curl_data);

// Print all data for checking all data is fetched from API.
// Using <pre></pre> to see a preformatted object array from $response_data.
// Otherwise, the text is printed and it is hard to read.
// --
echo '<pre>';
print_r($response_data);
echo '</pre>';

In the code above, we decode the API data and store it in a $response_data variable. Then we print out the data on the screen.

Now you enter this “http://localhost/index.php” URL on your browser and hit enter. You should see the result as an object array.

Extract only the Data We Want

From the “https://fakestoreapi.com/users” fake API, we want to import only id, email, username, password, first name, last name, and phone into the Excel file. To do that, we want to see the array structure of the $response_data variable. Then we will loop through the array variable and access the data we want.

From the last code, you will see the data that is printed out on the screen, as shown below.

You will see that the data is an object array structure. And we need to access data inside using an arrow. So, continue to add the code below to the index.php.

// All data in 'data' object type. So you will access the data using an arrow.
// Traverse array and print the data on screen.
foreach ($response_data as $output) {
	echo 'id: '.$output->id;
	echo '<br />';
    echo 'email: '.$output->email;
	echo '<br />';
    echo 'username: '.$output->username;
	echo '<br />';
    echo 'password: '.$output->password;
	echo '<br />';
    echo 'name: '.$output->name->firstname . ' ' . $output->name->lastname;
	echo '<br />';
    echo 'phone: '.$output->phone;	
    echo '<br />';
    echo '----------';
    echo '<br />';
}

Now refresh your browser to see the change. You should see the data list below.

So far, we are able to fetch the API data and extract only the data we want. Next, we will import our data into the Excel file.

Import Data into an Excel file

SimpleXLSXGen

In this tutorial, we will use the simple PHP class called “SimpleXLSXGen“. You can use other libraries if you want. But I want to keep it simple here.

So go ahead, copy the code from SimpleXLSXGEn GitHub and paste the code into the new SimpleXLSXGen.php. We will store this file in our local server root directory (www folder).

Testing SimpleXLSXGen

You should have the new SimpleXLSXGen.php under your root directory (www folder). In order to use this SimpleXLSXGen.php from our index.php, we need to embed it using the required_once function.

To do that, add this code at the beginning of our code in index.php.

// We embed SimpleXLSXGen class from the SimpleXLSXGen file.
require_once 'SimpleXLSXGen.php';

Now we want to test the SimpleXLSXGen class that actually works from the index.php. To do that, we add the code below at the end of our code in index.php.

We will remove this code once we test the class working.

// Sample code on how to use SimpleXLSXGen class.
$books = [
    ['ISBN', 'title', 'author', 'publisher', 'ctry' ],
    [618260307, 'The Hobbit', 'J. R. R. Tolkien', 'Houghton Mifflin', 'USA'],
    [908606664, 'Slinky Malinki', 'Lynley Dodd', 'Mallinson Rendel', 'NZ']
];
$xlsx = SimpleXLSXGen::fromArray( $books );
$xlsx->saveAs('excel/books.xlsx'); // or downloadAs('books.xlsx') or $xlsx_content = (string) $xlsx 

Then we refresh the browser to see the change.

You will see that the new books.xlsx has been created and stored in your root directory (www folder). That means the SimpleXLXSGen class is working from the index.php.

Now remove the testing code for SimpleXLSXGen we just added. Because we will add the real data we want.

Prepare the Array for SimpleXLSXGen

From the testing code above, you will see that the SimpleXLSXGen needs the multidimensional array ($books) in order to supply it to the SimpleXLSXGen class. To do that, we will add the code below.

// ----------------------------------- //
// ---- 4. Import into Excel file ---- //
// ----------------------------------- //

// --- 4.1 create a array format in order to use in SimpleXLSXGen class.
$excel_arr = array();
$title_arr = array( 'ID','Email','UserName','PassWord','Name','Phone' );
array_push( $excel_arr, $title_arr);
// print_r($excel_arr);

foreach ($response_data as $output) {
    array_push( $excel_arr, array(
        $output->id,
        $output->email,
        $output->username,
        $output->password,
        $output->name->firstname . ' ' . $output->name->lastname,
        $output->phone
    ));
}
// print_r($excel_arr);

Now the data array is ready. You can also print out the array result by uncommenting the print_r($excel_arr) command. We use array_push() to add more arrays to our $excel_arr array.

Import Data into an Excel file

The last step is to import the data into the Excel file. So add the code below to the index.php.

//--- 4.2 import data into excel file
$xlsx = SimpleXLSXGen::fromArray( $excel_arr );
$xlsx->saveAs('books.xlsx'); // or downloadAs('books.xlsx') or $xlsx_content = (string) $xlsx

Finally, refresh the browser to see the change. Basically, we will see whether the new Excel file or the existing Excel file is recreated.

Don’t forget to close the books file (Excel file) before refreshing the browser. Then SimpleXLSXGen will fail if you open the books file (Excel file) and try to recreate it at the same time.

Complete Source Code

<?php
phpinfo();
?>

<?php
// We embed SimpleXLSXGen class from the SimpleXLSXGen file.
require_once 'SimpleXLSXGen.php';

// ----------------------------------- //
// ---- 1. Fetch API data using cURL ---- //
// ----------------------------------- //

// Initiate curl session in a variable ($curl_handle)
$curl_handle = curl_init();

$url = 'https://fakestoreapi.com/users';

// Disable SSL verification:
// Since we call the API via our local server with HTTP protocal, so we will disable SSL verification.
// Otherwise, we can not call API.
curl_setopt($curl_handle, CURLOPT_SSL_VERIFYPEER, false);

// Set the curl URL option
curl_setopt($curl_handle, CURLOPT_URL, $url);

// This option will return data as a string instead of direct output
curl_setopt($curl_handle, CURLOPT_RETURNTRANSFER, true);

// Execute curl & store data in a variable
$curl_data = curl_exec($curl_handle);

curl_close($curl_handle);



// ----------------------------------- //
// ---- 2. Decode JSON into PHP array ---- //
// ----------------------------------- //
$response_data = json_decode($curl_data);

// Print all data for checking all data is fetched from API.
// Using <pre></pre> to see a preformatted object array from $response_data.
// Otherwise, the text is printed and it is hard to read.
// --
// echo '<pre>';
// print_r($response_data);
// echo '</pre>';



// ----------------------------------- //
// ---- 3. Extract only data we want from the array ---- //
// ----------------------------------- //
// All data in 'data' object type. So you will access the data using an arrow.
// Traverse array and print the data on screen.
// foreach ($response_data as $output) {
// 	echo 'id: '.$output->id;
// 	echo '<br />';
//     echo 'email: '.$output->email;
// 	echo '<br />';
//     echo 'username: '.$output->username;
// 	echo '<br />';
//     echo 'password: '.$output->password;
// 	echo '<br />';
//     echo 'name: '.$output->name->firstname . ' ' . $output->name->lastname;
// 	echo '<br />';
//     echo 'phone: '.$output->phone;	
//     echo '<br />';
//     echo '----------';
//     echo '<br />';
// }



// ----------------------------------- //
// ---- 4. Import into Excel file ---- //
// ----------------------------------- //

// --- 4.1 create a array format in order to use in SimpleXLSXGen class.
$excel_arr = array();
$title_arr = array( 'ID','Email','UserName','PassWord','Name','Phone' );
array_push( $excel_arr, $title_arr);
// print_r($excel_arr);
// die();


foreach ($response_data as $output) {
    array_push( $excel_arr, array(
        $output->id,
        $output->email,
        $output->username,
        $output->password,
        $output->name->firstname . ' ' . $output->name->lastname,
        $output->phone
    ));
}

//--- 4.2 import data into excel file
$xlsx = SimpleXLSXGen::fromArray( $excel_arr );
$xlsx->saveAs('excel/excel_arr.xlsx'); // or downloadAs('books.xlsx') or $xlsx_content = (string) $xlsx



// ----------------------------------- //
// ----  Sample code on how to use SimpleXLSXGen class. ---- //
// ----------------------------------- //
// $books = [
//     ['ISBN', 'title', 'author', 'publisher', 'ctry' ],
//     [618260307, 'The Hobbit', 'J. R. R. Tolkien', 'Houghton Mifflin', 'USA'],
//     [908606664, 'Slinky Malinki', 'Lynley Dodd', 'Mallinson Rendel', 'NZ']
// ];
// $xlsx = SimpleXLSXGen::fromArray( $books );
// $xlsx->saveAs('excel/books.xlsx'); // or downloadAs('books.xlsx') or $xlsx_content = (string) $xlsx 

?>

<?php
/**
 * Class SimpleXLSXGen
 * Export data to MS Excel. PHP XLSX generator
 * Author: sergey.shuchkin@gmail.com
 * URL: https://github.com/shuchkin/simplexlsxgen/blob/master/src/SimpleXLSXGen.php
 */

class SimpleXLSXGen {

	public $curSheet;
	protected $defaultFont;
	protected $defaultFontSize;
	protected $sheets;
	protected $template;
	protected $F, $F_KEYS; // fonts
	protected $XF, $XF_KEYS; // cellXfs
	protected $SI, $SI_KEYS; // shared strings
	const N_NORMAL = 0; // General
	const N_INT = 1; // 0
	const N_DEC = 2; // 0.00
	const N_PERCENT_INT = 9; // 0%
	const N_PRECENT_DEC = 10; // 0.00%
	const N_DATE = 14; // mm-dd-yy
	const N_TIME = 20; // h:mm
	const N_DATETIME = 22; // m/d/yy h:mm
	const F_NORMAL = 0;
	const F_HYPERLINK = 1;
	const F_BOLD = 2;
	const F_ITALIC = 4;
	const F_UNDERLINE = 8;
	const F_STRIKE = 16;
	const A_DEFAULT = 0;
	const A_LEFT = 1;
	const A_RIGHT = 2;
	const A_CENTER = 3;


	public function __construct() {
		$this->curSheet = -1;
		$this->defaultFont = 'Calibri';
		$this->sheets = [ ['name' => 'Sheet1', 'rows' => [], 'hyperlinks' => [] ] ];
		$this->SI = [];		// sharedStrings index
		$this->SI_KEYS = []; //  & keys
		$this->F = [ self::F_NORMAL ]; // fonts
		$this->F_KEYS = [0]; // & keys
		$this->XF  = [ [self::N_NORMAL, self::F_NORMAL, self::A_DEFAULT] ]; // styles
		$this->XF_KEYS = ['N0F0A0' => 0 ]; // & keys

		$this->template = [
			'_rels/.rels' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>
</Relationships>',
			'docProps/app.xml' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties">
<TotalTime>0</TotalTime>
<Application>'.__CLASS__.'</Application></Properties>',
			'docProps/core.xml' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<dcterms:created xsi:type="dcterms:W3CDTF">{DATE}</dcterms:created>
<dc:language>en-US</dc:language>
<dcterms:modified xsi:type="dcterms:W3CDTF">{DATE}</dcterms:modified>
<cp:revision>1</cp:revision>
</cp:coreProperties>',
			'xl/_rels/workbook.xml.rels' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>
{SHEETS}',
			'xl/worksheets/sheet1.xml' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
	xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
><dimension ref="{REF}"/>{COLS}<sheetData>{ROWS}</sheetData>{HYPERLINKS}</worksheet>',
			'xl/worksheets/_rels/sheet1.xml.rels' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">{HYPERLINKS}</Relationships>',
			'xl/sharedStrings.xml' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="{CNT}" uniqueCount="{CNT}">{STRINGS}</sst>',
			'xl/styles.xml' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
{FONTS}
<fills count="1"><fill><patternFill patternType="none"/></fill></fills>
<borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>
<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" /></cellStyleXfs>
{XF}
<cellStyles count="1">
	<cellStyle name="Normal" xfId="0" builtinId="0"/>
</cellStyles>
</styleSheet>',
			'xl/workbook.xml' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<fileVersion appName="'.__CLASS__.'"/><sheets>
{SHEETS}
</sheets></workbook>',
			'[Content_Types].xml' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Override PartName="/rels/.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>
<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>
<Override PartName="/xl/_rels/workbook.xml.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>
<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
{TYPES}
</Types>',
		];

		// <col min="1" max="1" width="22.1796875" bestFit="1" customWidth="1"/>
		// <row r="1" spans="1:2" x14ac:dyDescent="0.35"><c r="A1" t="s"><v>0</v></c><c r="B1"><v>100</v></c></row><row r="2" spans="1:2" x14ac:dyDescent="0.35"><c r="A2" t="s"><v>1</v></c><c r="B2"><v>200</v></c></row>
		// <si><t>Простой шаблон</t></si><si><t>Будем делать генератор</t></si>
	}
	public static function fromArray( array $rows, $sheetName = null ) {
		$xlsx = new static();
		return $xlsx->addSheet( $rows, $sheetName );
	}

	public function addSheet( array $rows, $name = null ) {

		$this->curSheet++;
		if ( $name === null ) { // autogenerated sheet names
			$name = 'Sheet'.($this->curSheet+1);
		} else {
			$names = [];
			foreach( $this->sheets as $sh ) {
				$names[ mb_strtoupper( $sh['name']) ] = 1;
			}
			for( $i = 0; $i < 100; $i++ ) {
				$new_name = ($i === 0) ? $name : $name .' ('.$i.')';
				$NEW_NAME = mb_strtoupper( $new_name );
				if ( !isset( $names[ $NEW_NAME ]) ) {
					$name = $new_name;
					break;
				}
			}
		}

		$this->sheets[$this->curSheet] = ['name' => $name, 'hyperlinks' => []];

		if ( is_array( $rows ) && isset( $rows[0] ) && is_array($rows[0]) ) {
			$this->sheets[$this->curSheet]['rows'] = $rows;
		} else {
			$this->sheets[$this->curSheet]['rows'] = [];
		}
		return $this;
	}

	public function __toString() {
		$fh = fopen( 'php://memory', 'wb' );
		if ( ! $fh ) {
			return '';
		}

		if ( ! $this->_write( $fh ) ) {
			fclose( $fh );
			return '';
		}
		$size = ftell( $fh );
		fseek( $fh, 0);

		return (string) fread( $fh, $size );
	}

	public function saveAs( $filename ) {
		$fh = fopen( $filename, 'wb' );
		if (!$fh) {
			return false;
		}
		if ( !$this->_write($fh) ) {
			fclose($fh);
			return false;
		}
		fclose($fh);

		return true;
	}

	public function download() {
		return $this->downloadAs( gmdate('YmdHi') . '.xlsx' );
	}

	public function downloadAs( $filename ) {
		$fh = fopen('php://memory','wb');
		if (!$fh) {
			return false;
		}

		if ( !$this->_write( $fh )) {
			fclose( $fh );
			return false;
		}

		$size = ftell($fh);

		header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		header('Content-Disposition: attachment; filename="'.$filename.'"');
		header('Last-Modified: ' . gmdate('D, d M Y H:i:s \G\M\T' , time() ));
		header('Content-Length: '.$size);

		while( ob_get_level() ) {
			ob_end_clean();
		}
		fseek($fh,0);
		fpassthru( $fh );

		fclose($fh);
		return true;
	}

	protected function _write( $fh ) {


		$dirSignatureE= "\x50\x4b\x05\x06"; // end of central dir signature
		$zipComments = 'Generated by '.__CLASS__.' PHP class, thanks sergey.shuchkin@gmail.com';

		if (!$fh) {
			return false;
		}

		$cdrec = '';	// central directory content
		$entries= 0;	// number of zipped files
		$cnt_sheets = count( $this->sheets );

		foreach ($this->template as $cfilename => $template ) {
			if ( $cfilename === 'xl/_rels/workbook.xml.rels' ) {
				$s = '';
				for ( $i = 0; $i < $cnt_sheets; $i++) {
					$s .= '<Relationship Id="rId'.($i+2).'" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"'.
						 ' Target="worksheets/sheet'.($i+1).".xml\"/>\n";
				}
				$s .= '<Relationship Id="rId'.($i+2).'" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/></Relationships>';
				$template = str_replace('{SHEETS}', $s, $template);
				$this->_writeEntry($fh, $cdrec, $cfilename, $template);
				$entries++;
			} elseif ( $cfilename === 'xl/workbook.xml' ) {
				$s = '';
				foreach ( $this->sheets as $k => $v ) {
					$s .= '<sheet name="' . $this->esc( $v['name'] ) . '" sheetId="' . ( $k + 1) . '" state="visible" r:id="rId' . ( $k + 2) . '"/>';
				}
				$template = str_replace('{SHEETS}', $s, $template);
				$this->_writeEntry($fh, $cdrec, $cfilename, $template);
				$entries++;
			}
			elseif ( $cfilename === 'docProps/core.xml' ) {
				$template = str_replace('{DATE}', gmdate('Y-m-d\TH:i:s\Z'), $template);
				$this->_writeEntry($fh, $cdrec, $cfilename, $template);
				$entries++;
			} elseif ( $cfilename === 'xl/sharedStrings.xml' ) {
				if (!count($this->SI)) {
					$this->SI[] = 'No Data';
				}
				$si_cnt = count($this->SI);
				$si = '<si><t>'.implode("</t></si>\r\n<si><t>", $this->SI).'</t></si>';
				$template = str_replace(['{CNT}', '{STRINGS}'], [ $si_cnt, $si ], $template );
				$this->_writeEntry($fh, $cdrec, $cfilename, $template);
				$entries++;
			} elseif ( $cfilename === 'xl/worksheets/sheet1.xml' ) {
				foreach ( $this->sheets as $k => $v ) {
					$filename = 'xl/worksheets/sheet'.($k+1).'.xml';
					$xml = $this->_sheetToXML($k, $template);
					$this->_writeEntry($fh, $cdrec, $filename, $xml );
					$entries++;
				}
				$xml = null;
			} elseif ( $cfilename === 'xl/worksheets/_rels/sheet1.xml.rels' ) {
				foreach ( $this->sheets as $k => $v ) {
					if ( count($v['hyperlinks'])) {
						$RH = [];
						$filename = 'xl/worksheets/_rels/sheet' . ( $k + 1 ) . '.xml.rels';
						foreach ( $v['hyperlinks'] as $h ) {
							$RH[] = '<Relationship Id="' . $h['ID'] . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink" Target="' . $this->esc($h['H']) . '" TargetMode="External"/>';
						}
						$xml = str_replace( '{HYPERLINKS}', implode( "\r\n", $RH ), $template );
						$this->_writeEntry( $fh, $cdrec, $filename, $xml );
						$entries++;
					}
				}
				$xml = null;

			} elseif ( $cfilename === '[Content_Types].xml' ) {
				$TYPES = ['<Override PartName="/_rels/.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'];
				foreach ( $this->sheets as $k => $v) {
					$TYPES[] = '<Override PartName="/xl/worksheets/sheet'.($k+1).
							'.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>';
					if (count( $v['hyperlinks'])) {
						$TYPES[] = '<Override PartName="/xl/worksheets/_rels/sheet'.($k+1).'.xml.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>';
					}
				}
				$template = str_replace('{TYPES}', implode("\r\n", $TYPES), $template);
				$this->_writeEntry($fh, $cdrec, $cfilename, $template);
				$entries++;
			} elseif ( $cfilename === 'xl/styles.xml' ) {
				$FONTS = ['<fonts count="'.count($this->F).'">'];
				foreach ( $this->F as $f ) {
					$FONTS[] = '<font><name val="'.$this->defaultFont.'"/><family val="2"/>'
						. ( $this->defaultFontSize ? '<sz val="'.$this->defaultFontSize.'"/>' : '' )
						.( $f & self::F_BOLD ? '<b/>' : '')
						.( $f & self::F_ITALIC ? '<i/>' : '')
						.( $f & self::F_UNDERLINE ? '<u/>' : '')
						.( $f & self::F_STRIKE ? '<strike/>' : '')
						.( $f & self::F_HYPERLINK ? '<color rgb="FF0563C1"/><u/>' : '')
						.'</font>';
				}
				$FONTS[] = '</fonts>';
				$XF = ['<cellXfs count="'.count($this->XF).'">'];
				foreach( $this->XF as $xf ) {
					$align = ($xf[2] === self::A_LEFT ? ' applyAlignment="1"><alignment horizontal="left"/>' : '')
						.($xf[2] === self::A_RIGHT ? ' applyAlignment="1"><alignment horizontal="right"/>' : '')
						.($xf[2] === self::A_CENTER ? ' applyAlignment="1"><alignment horizontal="center"/>' : '');
					$XF[] = '<xf numFmtId="'.$xf[0].'" fontId="'.$xf[1].'" fillId="0" borderId="0" xfId="0"'
						.($xf[0] > 0 ? ' applyNumberFormat="1"' : '')
						.($align ? $align . '</xf>' : '/>');

				}
				$XF[] = '</cellXfs>';
				$template = str_replace(['{FONTS}','{XF}'], [implode("\r\n", $FONTS), implode("\r\n", $XF)], $template);
				$this->_writeEntry($fh, $cdrec, $cfilename, $template);
				$entries++;
			} else {
				$this->_writeEntry($fh, $cdrec, $cfilename, $template);
				$entries++;
			}
		}
		$before_cd = ftell($fh);
		fwrite($fh, $cdrec);

		// end of central dir
		fwrite($fh, $dirSignatureE);
		fwrite($fh, pack('v', 0)); // number of this disk
		fwrite($fh, pack('v', 0)); // number of the disk with the start of the central directory
		fwrite($fh, pack('v', $entries)); // total # of entries "on this disk"
		fwrite($fh, pack('v', $entries)); // total # of entries overall
		fwrite($fh, pack('V', mb_strlen($cdrec,'8bit')));     // size of central dir
		fwrite($fh, pack('V', $before_cd));         // offset to start of central dir
		fwrite($fh, pack('v', mb_strlen($zipComments,'8bit'))); // .zip file comment length
		fwrite($fh, $zipComments);

		return true;
	}

	protected function _writeEntry($fh, &$cdrec, $cfilename, $data) {
		$zipSignature = "\x50\x4b\x03\x04"; // local file header signature
		$dirSignature = "\x50\x4b\x01\x02"; // central dir header signature

		$e = [];
		$e['uncsize'] = mb_strlen($data, '8bit');

		// if data to compress is too small, just store it
		if($e['uncsize'] < 256){
			$e['comsize'] = $e['uncsize'];
			$e['vneeded'] = 10;
			$e['cmethod'] = 0;
			$zdata = $data;
		} else{ // otherwise, compress it
			$zdata = gzcompress($data);
			$zdata = substr(substr($zdata, 0, - 4 ), 2); // fix crc bug (thanks to Eric Mueller)
			$e['comsize'] = mb_strlen($zdata, '8bit');
			$e['vneeded'] = 10;
			$e['cmethod'] = 8;
		}

		$e['bitflag'] = 0;
		$e['crc_32']  = crc32($data);

		// Convert date and time to DOS Format, and set then
		$lastmod_timeS  = str_pad(decbin(date('s')>=32?date('s')-32:date('s')), 5, '0', STR_PAD_LEFT);
		$lastmod_timeM  = str_pad(decbin(date('i')), 6, '0', STR_PAD_LEFT);
		$lastmod_timeH  = str_pad(decbin(date('H')), 5, '0', STR_PAD_LEFT);
		$lastmod_dateD  = str_pad(decbin(date('d')), 5, '0', STR_PAD_LEFT);
		$lastmod_dateM  = str_pad(decbin(date('m')), 4, '0', STR_PAD_LEFT);
		$lastmod_dateY  = str_pad(decbin(date('Y')-1980), 7, '0', STR_PAD_LEFT);

		# echo "ModTime: $lastmod_timeS-$lastmod_timeM-$lastmod_timeH (".date("s H H").")\n";
		# echo "ModDate: $lastmod_dateD-$lastmod_dateM-$lastmod_dateY (".date("d m Y").")\n";
		$e['modtime'] = bindec("$lastmod_timeH$lastmod_timeM$lastmod_timeS");
		$e['moddate'] = bindec("$lastmod_dateY$lastmod_dateM$lastmod_dateD");

		$e['offset'] = ftell($fh);

		fwrite($fh, $zipSignature);
		fwrite($fh, pack('s', $e['vneeded'])); // version_needed
		fwrite($fh, pack('s', $e['bitflag'])); // general_bit_flag
		fwrite($fh, pack('s', $e['cmethod'])); // compression_method
		fwrite($fh, pack('s', $e['modtime'])); // lastmod_time
		fwrite($fh, pack('s', $e['moddate'])); // lastmod_date
		fwrite($fh, pack('V', $e['crc_32']));  // crc-32
		fwrite($fh, pack('I', $e['comsize'])); // compressed_size
		fwrite($fh, pack('I', $e['uncsize'])); // uncompressed_size
		fwrite($fh, pack('s', mb_strlen($cfilename, '8bit')));   // file_name_length
		fwrite($fh, pack('s', 0));  // extra_field_length
		fwrite($fh, $cfilename);    // file_name
		// ignoring extra_field
		fwrite($fh, $zdata);

		// Append it to central dir
		$e['external_attributes']  = (substr($cfilename, -1) === '/'&&!$zdata)?16:32; // Directory or file name
		$e['comments']             = '';

		$cdrec .= $dirSignature;
		$cdrec .= "\x0\x0";                  // version made by
		$cdrec .= pack('v', $e['vneeded']); // version needed to extract
		$cdrec .= "\x0\x0";                  // general bit flag
		$cdrec .= pack('v', $e['cmethod']); // compression method
		$cdrec .= pack('v', $e['modtime']); // lastmod time
		$cdrec .= pack('v', $e['moddate']); // lastmod date
		$cdrec .= pack('V', $e['crc_32']);  // crc32
		$cdrec .= pack('V', $e['comsize']); // compressed filesize
		$cdrec .= pack('V', $e['uncsize']); // uncompressed filesize
		$cdrec .= pack('v', mb_strlen($cfilename,'8bit')); // file name length
		$cdrec .= pack('v', 0);                // extra field length
		$cdrec .= pack('v', mb_strlen($e['comments'],'8bit')); // file comment length
		$cdrec .= pack('v', 0); // disk number start
		$cdrec .= pack('v', 0); // internal file attributes
		$cdrec .= pack('V', $e['external_attributes']); // internal file attributes
		$cdrec .= pack('V', $e['offset']); // relative offset of local header
		$cdrec .= $cfilename;
		$cdrec .= $e['comments'];
	}

	protected function _sheetToXML($idx, $template) {
		// locale floats fr_FR 1.234,56 -> 1234.56
		$_loc = setlocale(LC_NUMERIC, 0);
		setlocale(LC_NUMERIC,'C');
		$COLS = [];
		$ROWS = [];
		if ( count($this->sheets[$idx]['rows']) ) {
			$COLS[] = '<cols>';
			$CUR_ROW = 0;
			$COL = [];
			foreach( $this->sheets[$idx]['rows'] as $r ) {
				$CUR_ROW++;
				$row = '<row r="'.$CUR_ROW.'">';
				$CUR_COL = 0;
				foreach( $r as $v ) {
					$CUR_COL++;
					if ( !isset($COL[ $CUR_COL ])) {
						$COL[ $CUR_COL ] = 0;
					}
					if ( $v === null || $v === '' ) {
						continue;
					}

					$cname = $this->num2name($CUR_COL) . $CUR_ROW;

					$ct = $cv = null;
					$N = $F = $A = 0;

					if ( is_string($v) ) {

						if ( $v[0] === "\0" ) { // RAW value as string
							$v = substr($v,1);
							$vl = mb_strlen( $v );
						} else {
							if ( strpos( $v, '<' ) !== false ) { // tags?
								if ( strpos( $v, '<b>' ) !== false ) {
									$F += self::F_BOLD;
								}
								if ( strpos( $v, '<i>' ) !== false ) {
									$F += self::F_ITALIC;
								}
								if ( strpos( $v, '<u>' ) !== false ) {
									$F += self::F_UNDERLINE;
								}
								if ( strpos( $v, '<s>' ) !== false ) {
									$F += self::F_STRIKE;
								}
								if ( strpos( $v, '<left>' ) !== false ) {
									$A += self::A_LEFT;
								}
								if ( strpos( $v, '<center>' ) !== false ) {
									$A += self::A_CENTER;
								}
								if ( strpos( $v, '<right>' ) !== false ) {
									$A += self::A_RIGHT;
								}
								if ( preg_match( '/<a href="(https?:\/\/[^"]+)">(.*?)<\/a>/i', $v, $m ) ) {
									$h = explode( '#', $m[1] );
									$this->sheets[ $idx ]['hyperlinks'][] = ['ID' => 'rId' . ( count( $this->sheets[ $idx ]['hyperlinks'] ) + 1 ), 'R' => $cname, 'H' => $h[0], 'L' => isset( $h[1] ) ? $h[1] : ''];
									$F = self::F_HYPERLINK; // Hyperlink
								}
								if ( preg_match( '/<a href="(mailto?:[^"]+)">(.*?)<\/a>/i', $v, $m ) ) {
									$this->sheets[ $idx ]['hyperlinks'][] = ['ID' => 'rId' . ( count( $this->sheets[ $idx ]['hyperlinks'] ) + 1 ), 'R' => $cname, 'H' => $m[1], 'L' => ''];
									$F = self::F_HYPERLINK; // mailto hyperlink
								}
								$v = strip_tags( $v );
							} // tags
							$vl = mb_strlen( $v );
							if ( $v === '0' || preg_match( '/^[-+]?[1-9]\d{0,14}$/', $v ) ) { // Integer as General
								$cv = ltrim( $v, '+' );
								if ( $vl > 10 ) {
									$N = self::N_INT; // [1] 0
								}
							} elseif ( preg_match( '/^[-+]?(0|[1-9]\d*)\.(\d+)$/', $v, $m ) ) {
								$cv = ltrim( $v, '+' );
								if ( strlen( $m[2] ) < 3 ) {
									$N = self::N_DEC;
								}
							} elseif ( preg_match( '/^([-+]?\d+)%$/', $v, $m ) ) {
								$cv = round( $m[1] / 100, 2 );
								$N = self::N_PERCENT_INT; // [9] 0%
							} elseif ( preg_match( '/^([-+]?\d+\.\d+)%$/', $v, $m ) ) {
								$cv = round( $m[1] / 100, 4 );
								$N = self::N_PRECENT_DEC; // [10] 0.00%
							} elseif ( preg_match( '/^(\d\d\d\d)-(\d\d)-(\d\d)$/', $v, $m ) ) {
								$cv = $this->date2excel( $m[1], $m[2], $m[3] );
								$N = self::N_DATE; // [14] mm-dd-yy
							} elseif ( preg_match( '/^(\d\d)\/(\d\d)\/(\d\d\d\d)$/', $v, $m ) ) {
								$cv = $this->date2excel( $m[3], $m[2], $m[1] );
								$N = self::N_DATE; // [14] mm-dd-yy
							} elseif ( preg_match( '/^(\d\d):(\d\d):(\d\d)$/', $v, $m ) ) {
								$cv = $this->date2excel( 0, 0, 0, $m[1], $m[2], $m[3] );
								$N = self::N_TIME; // time
							} elseif ( preg_match( '/^(\d\d\d\d)-(\d\d)-(\d\d) (\d\d):(\d\d):(\d\d)$/', $v, $m ) ) {
								$cv = $this->date2excel( $m[1], $m[2], $m[3], $m[4], $m[5], $m[6] );
								$N = self::N_DATETIME; // [22] m/d/yy h:mm
							} elseif ( preg_match( '/^(\d\d)\/(\d\d)\/(\d\d\d\d) (\d\d):(\d\d):(\d\d)$/', $v, $m ) ) {
								$cv = $this->date2excel( $m[3], $m[2], $m[1], $m[4], $m[5], $m[6] );
								$N = self::N_DATETIME; // [22] m/d/yy h:mm
							} elseif ( preg_match( '/^[0-9+-.]+$/', $v ) ) { // Long ?
								$A = self::A_RIGHT;
							} elseif ( preg_match( '/^https?:\/\/\S+$/i', $v ) ) {
								$h = explode( '#', $v );
								$this->sheets[ $idx ]['hyperlinks'][] = ['ID' => 'rId' . ( count( $this->sheets[ $idx ]['hyperlinks'] ) + 1 ), 'R' => $cname, 'H' => $h[0], 'L' => isset( $h[1] ) ? $h[1] : ''];
								$F = self::F_HYPERLINK; // Hyperlink
							} elseif ( preg_match( "/^[a-zA-Z0-9_\.\-]+@([a-zA-Z0-9][a-zA-Z0-9\-]*\.)+[a-zA-Z]{2,}$/", $v ) ) {
								$this->sheets[ $idx ]['hyperlinks'][] = ['ID' => 'rId' . ( count( $this->sheets[ $idx ]['hyperlinks'] ) + 1 ), 'R' => $cname, 'H' => 'mailto:' . $v, 'L' => ''];
								$F = self::F_HYPERLINK; // Hyperlink
							}
						}
						if ( !$cv) {

							$v = $this->esc( $v );

							if ( mb_strlen( $v ) > 160 ) {
								$ct = 'inlineStr';
								$cv = $v;
							} else {
								$ct = 's'; // shared string
								$cv = false;
								$skey = '~' . $v;
								if ( isset( $this->SI_KEYS[ $skey ] ) ) {
									$cv = $this->SI_KEYS[ $skey ];
								}
								if ( $cv === false ) {
									$this->SI[] = $v;
									$cv = count( $this->SI ) - 1;
									$this->SI_KEYS[ $skey ] = $cv;
								}
							}
						}
					} elseif ( is_int( $v ) ) {
						$vl = mb_strlen( (string) $v );
						$cv = $v;
					} elseif ( is_float( $v ) ) {
						$vl = mb_strlen( (string) $v );
						$cv = $v;
					} elseif ( $v instanceof DateTime ) {
						$vl = 16;
						$cv = $this->date2excel( $v->format('Y'), $v->format('m'), $v->format('d'), $v->format('H'), $v->format('i'), $v->format('s') );
						$N = self::N_DATETIME; // [22] m/d/yy h:mm
					} else {
						continue;
					}

					$COL[ $CUR_COL ] = max( $vl, $COL[ $CUR_COL ] );

					$cs = 0;
					if ( $N + $F + $A > 0 ) {

						if ( isset($this->F_KEYS[ $F ] ) ) {
							$cf = $this->F_KEYS[ $F ];
						} else {
							$cf = count($this->F);
							$this->F_KEYS[$F] = $cf;
							$this->F[] = $F;
						}
						$NFA = 'N' . $N . 'F' . $cf . 'A' . $A;
						if ( isset( $this->XF_KEYS[ $NFA ] ) ) {
							$cs = $this->XF_KEYS[ $NFA ];
						}
						if ( $cs === 0 ) {
							$cs = count( $this->XF );
							$this->XF_KEYS[ $NFA ] = $cs;
							$this->XF[] = [$N, $cf, $A];
						}
					}

					$row .= '<c r="' . $cname . '"'.($ct ? ' t="'.$ct.'"' : '').($cs ? ' s="'.$cs.'"' : '').'>'
					        .($ct === 'inlineStr' ? '<is><t>'.$cv.'</t></is>' : '<v>' . $cv . '</v>')."</c>\r\n";
				}
				$ROWS[] = $row . "</row>\r\n";
			}
			foreach ( $COL as $k => $max ) {
				$COLS[] = '<col min="'.$k.'" max="'.$k.'" width="'.min( $max+1, 60).'" />';
			}
			$COLS[] = '</cols>';
			$REF = 'A1:'.$this->num2name(count($COLS)) . $CUR_ROW;
		} else {
			$ROWS[] = '<row r="1"><c r="A1" t="s"><v>0</v></c></row>';
			$REF = 'A1:A1';
		}
		$HYPERLINKS = [];
		if ( count( $this->sheets[$idx]['hyperlinks']) ) {
			$HYPERLINKS[] = '<hyperlinks>';
			foreach ( $this->sheets[$idx]['hyperlinks'] as $h ) {
				$HYPERLINKS[] = '<hyperlink ref="' . $h['R'] . '" r:id="' . $h['ID'] . '" location="' . $this->esc( $h['L'] ) . '" display="' . $this->esc( $h['H'] . ( $h['L'] ? ' - ' . $h['L'] : '' ) ) . '" />';
			}
			$HYPERLINKS[] = '</hyperlinks>';
		}
		//restore locale
		setlocale(LC_NUMERIC, $_loc);

		return str_replace(['{REF}','{COLS}','{ROWS}','{HYPERLINKS}'],
			[ $REF, implode("\r\n", $COLS), implode("\r\n",$ROWS), implode("\r\n", $HYPERLINKS) ],
			$template );
	}

	public function num2name($num) {
		$numeric = ($num - 1) % 26;
		$letter  = chr( 65 + $numeric );
		$num2    = (int) ( ($num-1) / 26 );
		if ( $num2 > 0 ) {
			return $this->num2name( $num2 ) . $letter;
		}
		return $letter;
	}

	public function date2excel($year, $month, $day, $hours=0, $minutes=0, $seconds=0) {
	    $excelTime = (($hours * 3600) + ($minutes * 60) + $seconds) / 86400;

	    if ( $year === 0 ) {
	        return $excelTime;
        }

		// self::CALENDAR_WINDOWS_1900
		$excel1900isLeapYear = True;
		if (((int)$year === 1900) && ($month <= 2)) { $excel1900isLeapYear = False; }
		$myExcelBaseDate = 2415020;

		//    Julian base date Adjustment
		if ($month > 2) {
			$month -= 3;
		} else {
			$month += 9;
			--$year;
		}
		//    Calculate the Julian Date, then subtract the Excel base date (JD 2415020 = 31-Dec-1899 Giving Excel Date of 0)
		$century = substr($year,0,2);
		$decade = substr($year,2,2);
		$excelDate = floor((146097 * $century) / 4) + floor((1461 * $decade) / 4) + floor((153 * $month + 2) / 5) + $day + 1721119 - $myExcelBaseDate + $excel1900isLeapYear;

		return (float) $excelDate + $excelTime;
	}
	public function setDefaultFont( $name ) {
		$this->defaultFont = $name;
		return $this;
	}
	public function setDefaultFontSize( $size ) {
		$this->defaultFontSize = $size;
		return $this;
	}
	public function esc( $str ) {
		// XML UTF-8: #x9 | #xA | #xD | [#x20-#xD7FF] | [#xE000-#xFFFD] | [#x10000-#x10FFFF]
		// but we use fast version
		return str_replace( ['&', '<', '>', "\x00","\x03","\x0B"], ['&amp;', '&lt;', '&gt;', '', '', ''], $str );
	}
}

Wrap Up

In this tutorial, we learn how to fetch API data using cURL and import the data into an Excel file. I try to make it as simple as possible. Also, I want the code to be lightweight. We don’t discuss the POST, PUT, and DELETE methods. We discuss only the GET method.

There are two things you will need to adjust when you use my code. One is the API data structure accessing, in my case, I use the Fake Store API. For you, the API data structure depends on your API providers. It will be different from my tutorial. You need to print out the API data (after JSON decode) in order to see what data type and data structure it is. Then you will know how to access the data from that data structure (loop array). Secondly, your API may require authentication. In that case, you will need to provide the additional parameters(token, username, password, etc) and add them to the cURL command. These parameters will pass in the HTTPS header of the API.

If you do the research on the internet, you will find two ways to fetch the API data. One is using the file_get_contents function, and another one is using the cURl library. To pick which one fits you, you may read more here.

The source code in this tutorial test works well with PHP 7.3.21.

That’s it for today.

Your support helps keep this blog running! Secure payments via Paypal and Stripe.