PHP Hacks
|
|
Free Open Book
PHP Hacks |
Origami Paper Planes
|
Hack 45. Suck Data from Excel Uploads
Using the XML from Excel 2003, you can read data directly from spreadsheets that customers upload to your site. Your customers' data can come from many different sources. Making it easy for them to get their data into your system can mean the difference between getting their business and having them go somewhere else for their data needs (and taking their money with them). Supporting data import from common data sources such as Excel can be a very compelling feature for customers. This hack shows you how to save Excel spreadsheets in the new XML format supported by Excel and Microsoft Office 2003 and how to read that format and display the data back to the user. Figure 5-12 illustrates the flow between the browser (shown here as the computer) and the import system. The first page is index.php, which presents the Browse button. The user then selects an Excel XML file, which is submitted to the import.php page; that page returns an HTML rendering of the data in the file. Figure 5-12. The flow of the Excel XML import
5.13.1. The Codeindex.php (shown in Example 5-34) is responsible for getting the Excel data into your PHP scripts. Example 5-34. The PHP for getting Excel data into your scripts<html> <body> <form enctype="multipart/form-data" action="import.php" method="post"> Excel XML file: <input type="hidden" name="MAX_FILE_SIZE" value="2000000" /> <input type="file" name="file" /><br/> <input type="submit" value="Upload" /> </form> </body> </html> Save the code in Example 5-35 as import.php. It handles the data import. Example 5-35. PHP that handles Excel data import
<html>
<body>
<?php
$data = array();
if ( $_FILES['file']['tmp_name'] )
{
$dom = DOMDocument::load( $_FILES['file']['tmp_name'] );
$rows = $dom->getElementsByTagName( 'Row' );
foreach ($rows as $row)
{
$cells = $row->getElementsByTagName( 'Cell' );
$datarow = array();
foreach ($cells as $cell)
{
$datarow []= $cell->nodeValue;
}
$data []= $datarow;
}
}
?>
<table>
<?php foreach( $data as $row ) { ?>
<tr>
<?php foreach( $row as $item ) { ?>
<td><?php echo( $item ); ?></td>
<?php } ?>
</tr>
<?php } ?>
</table>
</body>
</html>
The import.php page, which is at the heart of this hack, starts by opening up the uploaded file using the XML DOM reader. Then it iterates through each Row element, and within each, it works through the Cell elements. In each Cell, the script finds the actual data, which is stored into an array called $datarow. That stored data is then output as HTML using standard PHP text templating techniques (at the end of the script). 5.13.2. Running the HackRunning this hack begins with creating an Excel spreadsheet. As is my practice, I went to the U.S. Census Bureau (http://www.census.gov) to score some data. In this case, I've used median family income in various brackets in the 50 states. (By the way, for a two-person family, the state with the highest median income was Alaska; go figure!) Anyway, the data looks like Figure 5-13. Figure 5-13. The original Excel spreadsheet
Now I have to convert it to the XML spreadsheet format using Save As, as shown in Figure 5-14. It's interesting to note here that I can keep the spreadsheet in XML format indefinitely; there is no loss of fidelity or precision between the binary version and the XML version. If you have a customer with Office 2003 and you are giving him a spreadsheet to start with, just give him the XML version! That way he can keep working in it and never know the difference. Now, with XML in hand, we can upload the pages to the site; using the browser, surf to index.php. The page is shown in Figure 5-15. Figure 5-14. Saving the spreadsheet in XML format
Figure 5-15. Uploading the spreadsheet to the server
Now click on the Browse button and find the XML spreadsheet. Then click on the Upload button, and that will submit the XML data to the import.php page. This page uses the XML DOM and XPath support in PHP to parse up the XML and extract the table data. Then it uses some simple PHP to format the data as an HTML table. The result for the example census data I showed in Figure 5-13 is shown in Figure 5-16.
Figure 5-16. The spreadsheet data in HTML format
These types of user convenience features can be extremely compelling. Instead of spending hours typing their data into HTML forms and working with a clunky web interface, your customers can simply use tools they are familiar with, such as Excel. 5.13.3. See Also
|
Main Menu |
