login/register

Snip!t from collection of Alan Dix

see all channels for Alan Dix

Snip
summary

Many of my customers hold data in Excel files. Then, whe...
Since the PHP allows you to create an OLE compatible COM ...
Before we start, let’s introduce input parameters of t ...
* $file – (string) absolute path to the Excel data ...
... 36.}

PHP and Excel
http://www.webdevelopmentstuff.com/87/php-and-excel.html

Categories

/Channels/techie/web development

[ go to category ]

For Snip

loading snip actions ...

For Page

loading url actions ...

Many of my customers hold data in Excel files. Then, when they decide to build a web based, database driven project controlled by PHP, ask me to move their Excel data into the MySQL. Whether it’s possible and how to do it, will be the content of this post.

Since the PHP allows you to create an OLE compatible COM object with its methods and properties, the solution is more than easy. All you need is take a brief look into the PHP COM Manual Pages to be able to understand the following explanatory example. I wrote this code as a PHP CLI script which seems to me more usable for interaction with other applications.

Before we start, let’s introduce input parameters of the function which is responsible for data retrieve from an Excel file and output it as a matrix, representing the Excel table.

  • $file – (string) absolute path to the Excel data file
  • $sheet – (int) order number of the sheet which data we want to extract
  • $rows – (array) rows selected from the sheet
  • $cols – (array) columns selected from the sheet

The names of variables were selected to represent their meaning (semantic names) and facilitate the understanding of script work. But if you’re still confused of input parameters or output, don’t be affraid, following examples will clarify it more. So, let’s move forward to the PHP and Excel interaction.

·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150
01.function getDataFromExcel($file, $sheet, $rows, $cols)
02.{
03.    // COM CREATE
04.    fwrite(STDOUT, "----------------------------------------\r\n");
05.    $excel = new COM("Excel.application") or die ("ERROR: Unable to instantaniate COM!\r\n");
06.    fwrite(STDOUT, "Application name: {$excel->Application->value}\r\n") ;
07.    fwrite(STDOUT, "Loaded version: {$excel->Application->version}\r\n");
08.    fwrite(STDOUT, "----------------------------------------\r\n\r\n");
09. 
10.    // DATA RETRIEVAL
11.    $Workbook = $excel->Workbooks->Open($file) or die("ERROR: Unable to open " . $file . "!\r\n");
12.    $Worksheet = $Workbook->Worksheets($sheet);
13.    $Worksheet->Activate;
14.    $i = 0;
15.    foreach ($rows as $row)
16.    {
17.        $i++; $j = 0;
18.        foreach ($cols as $col)
19.        {
20.            $j++;
21.            $cell = $Worksheet->Range($col . $row);
22.            $cell->activate();
23.            $matrix[$i][$j] = $cell->value;
24.        }
25.    }
26. 
27.    // COM DESTROY
28.    $Workbook->Close();
29.    unset($Worksheet);
30.    unset($Workbook);
31.    $excel->Workbooks->Close();
32.    $excel->Quit();
33.    unset($excel);
34. 
35.    return $matrix;
36.}

HTML

<p>Many of my customers hold data in Excel files. Then, when they decide to build a web based, database driven project controlled by PHP, ask me to move their Excel data into the MySQL. Whether it&#x2019;s possible and how to do it, will be the content of this post.<br> <span id="more-87"></span><br> Since the PHP allows you to create an OLE compatible COM object with its methods and properties, the solution is more than easy. All you need is take a brief look into the <a href="http://www.php.net/manual/en/class.com.php" target="_blank">PHP COM Manual Pages</a> to be able to understand the following explanatory example. I wrote this code as a <a href="http://www.php-cli.com" target="_blank">PHP CLI</a> script which seems to me more usable for interaction with other applications.</p> <p>Before we start, let&#x2019;s introduce input parameters of the function which is responsible for data retrieve from an Excel file and output it as a matrix, representing the Excel table.</p> <ul> <li>$file &#x2013; <strong>(string)</strong> absolute path to the Excel data file</li> <li>$sheet &#x2013; <strong>(int)</strong> order number of the sheet which data we want to extract</li> <li>$rows &#x2013; <strong>(array)</strong> rows selected from the sheet</li> <li>$cols &#x2013; <strong>(array)</strong> columns selected from the sheet</li> </ul> <p>The names of variables were selected to represent their meaning (semantic names) and facilitate the understanding of script work. But if you&#x2019;re still confused of input parameters or output, don&#x2019;t be affraid, following examples will clarify it more. So, let&#x2019;s move forward to the PHP and Excel interaction.</p> <div class="syntaxhighlighter" id="highlighter_813968"><div class="bar"><div class="toolbar"><a class="item viewSource" style="width: 16px; height: 16px;" title="view source" href="#viewSource">view source</a><div class="item copyToClipboard"><embed id="highlighter_813968_clipboard" type="application/x-shockwave-flash" title="copy to clipboard" allowscriptaccess="always" wmode="transparent" flashvars="highlighterId=highlighter_813968" menu="false" src="http://www.webdevelopmentstuff.com/wp-content/plugins/syntaxhighlighter/syntaxhighlighter/scripts/clipboard.swf" height="16" width="16"></div><a class="item printSource" style="width: 16px; height: 16px;" title="print" href="#printSource">print</a><a class="item about" style="width: 16px; height: 16px;" title="?" href="#about">?</a></div></div><div class="ruler line"><div>&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;10&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;20&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;30&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;40&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;50&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;60&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;70&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;80&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;90&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;100&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;110&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;120&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;130&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;140&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;&#xb7;150</div></div><div class="lines no-wrap"><div class="line alt1"><code class="number">01.</code><span class="content"><span class="block" style="margin-left: 0px ! important;"><code class="keyword">function</code> <code class="plain">getDataFromExcel(</code><code class="variable">$file</code><code class="plain">, </code><code class="variable">$sheet</code><code class="plain">, </code><code class="variable">$rows</code><code class="plain">, </code><code class="variable">$cols</code><code class="plain">)</code></span></span></div><div class="line alt2"><code class="number">02.</code><span class="content"><span class="block" style="margin-left: 0px ! important;"><code class="plain">{</code></span></span></div><div class="line alt1"><code class="number">03.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="comments">// COM CREATE</code></span></span></div><div class="line alt2"><code class="number">04.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="plain">fwrite(STDOUT, </code><code class="string">"----------------------------------------\r\n"</code><code class="plain">);</code></span></span></div><div class="line alt1"><code class="number">05.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="variable">$excel</code> <code class="plain">= </code><code class="keyword">new</code> <code class="plain">COM(</code><code class="string">"Excel.application"</code><code class="plain">) </code><code class="keyword">or</code> <code class="keyword">die</code> <code class="plain">(</code><code class="string">"ERROR: Unable to instantaniate COM!\r\n"</code><code class="plain">);</code></span></span></div><div class="line alt2"><code class="number">06.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="plain">fwrite(STDOUT, </code><code class="string">"Application name: {$excel-&gt;Application-&gt;value}\r\n"</code><code class="plain">) ;</code></span></span></div><div class="line alt1"><code class="number">07.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="plain">fwrite(STDOUT, </code><code class="string">"Loaded version: {$excel-&gt;Application-&gt;version}\r\n"</code><code class="plain">);</code></span></span></div><div class="line alt2"><code class="number">08.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="plain">fwrite(STDOUT, </code><code class="string">"----------------------------------------\r\n\r\n"</code><code class="plain">);</code></span></span></div><div class="line alt1"><code class="number">09.</code><span class="content"><span class="block" style="margin-left: 0px ! important;">&nbsp;</span></span></div><div class="line alt2"><code class="number">10.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="comments">// DATA RETRIEVAL</code></span></span></div><div class="line alt1"><code class="number">11.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="variable">$Workbook</code> <code class="plain">= </code><code class="variable">$excel</code><code class="plain">-&gt;Workbooks-&gt;Open(</code><code class="variable">$file</code><code class="plain">) </code><code class="keyword">or</code> <code class="keyword">die</code><code class="plain">(</code><code class="string">"ERROR: Unable to open "</code> <code class="plain">. </code><code class="variable">$file</code> <code class="plain">. </code><code class="string">"!\r\n"</code><code class="plain">);</code></span></span></div><div class="line alt2"><code class="number">12.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="variable">$Worksheet</code> <code class="plain">= </code><code class="variable">$Workbook</code><code class="plain">-&gt;Worksheets(</code><code class="variable">$sheet</code><code class="plain">);</code></span></span></div><div class="line alt1"><code class="number">13.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="variable">$Worksheet</code><code class="plain">-&gt;Activate;</code></span></span></div><div class="line alt2"><code class="number">14.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="variable">$i</code> <code class="plain">= 0;</code></span></span></div><div class="line alt1"><code class="number">15.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="keyword">foreach</code> <code class="plain">(</code><code class="variable">$rows</code> <code class="keyword">as</code> <code class="variable">$row</code><code class="plain">)</code></span></span></div><div class="line alt2"><code class="number">16.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="plain">{</code></span></span></div><div class="line alt1"><code class="number">17.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 56px ! important;"><code class="variable">$i</code><code class="plain">++; </code><code class="variable">$j</code> <code class="plain">= 0;</code></span></span></div><div class="line alt2"><code class="number">18.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 56px ! important;"><code class="keyword">foreach</code> <code class="plain">(</code><code class="variable">$cols</code> <code class="keyword">as</code> <code class="variable">$col</code><code class="plain">)</code></span></span></div><div class="line alt1"><code class="number">19.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 56px ! important;"><code class="plain">{</code></span></span></div><div class="line alt2"><code class="number">20.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 84px ! important;"><code class="variable">$j</code><code class="plain">++;</code></span></span></div><div class="line alt1"><code class="number">21.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 84px ! important;"><code class="variable">$cell</code> <code class="plain">= </code><code class="variable">$Worksheet</code><code class="plain">-&gt;Range(</code><code class="variable">$col</code> <code class="plain">. </code><code class="variable">$row</code><code class="plain">);</code></span></span></div><div class="line alt2"><code class="number">22.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 84px ! important;"><code class="variable">$cell</code><code class="plain">-&gt;activate();</code></span></span></div><div class="line alt1"><code class="number">23.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 84px ! important;"><code class="variable">$matrix</code><code class="plain">[</code><code class="variable">$i</code><code class="plain">][</code><code class="variable">$j</code><code class="plain">] = </code><code class="variable">$cell</code><code class="plain">-&gt;value;</code></span></span></div><div class="line alt2"><code class="number">24.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 56px ! important;"><code class="plain">}</code></span></span></div><div class="line alt1"><code class="number">25.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="plain">}</code></span></span></div><div class="line alt2"><code class="number">26.</code><span class="content"><span class="block" style="margin-left: 0px ! important;">&nbsp;</span></span></div><div class="line alt1"><code class="number">27.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="comments">// COM DESTROY</code></span></span></div><div class="line alt2"><code class="number">28.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="variable">$Workbook</code><code class="plain">-&gt;Close();</code></span></span></div><div class="line alt1"><code class="number">29.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="plain">unset(</code><code class="variable">$Worksheet</code><code class="plain">);</code></span></span></div><div class="line alt2"><code class="number">30.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="plain">unset(</code><code class="variable">$Workbook</code><code class="plain">);</code></span></span></div><div class="line alt1"><code class="number">31.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="variable">$excel</code><code class="plain">-&gt;Workbooks-&gt;Close();</code></span></span></div><div class="line alt2"><code class="number">32.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="variable">$excel</code><code class="plain">-&gt;Quit();</code></span></span></div><div class="line alt1"><code class="number">33.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="plain">unset(</code><code class="variable">$excel</code><code class="plain">);</code></span></span></div><div class="line alt2"><code class="number">34.</code><span class="content"><span class="block" style="margin-left: 0px ! important;">&nbsp;</span></span></div><div class="line alt1"><code class="number">35.</code><span class="content"><code class="spaces">&nbsp;&nbsp;&nbsp;&nbsp;</code><span class="block" style="margin-left: 28px ! important;"><code class="keyword">return</code> <code class="variable">$matrix</code><code class="plain">;</code></span></span></div><div class="line alt2"><code class="number">36.</code><span class="content"><span class="block" style="margin-left: 0px ! important;"><code class="plain">}</code></span></span></div></div></div> <p></p>