Da Clock and Date Stuff
Page last modified: 12 July 2008
Source Code For Getting Data From Microsoft Excel in PHP
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Getting Data out of An Excel Spreadsheet From PHP</title>
</head>
<body>
<h2>Outputting A Table From Microsoft Excel in PHP</h2>
Read <a href="http://www.w3schools.com/PHP/php_db_odbc.asp">http://www.w3schools.com/PHP/php_db_odbc.asp</a> for info on how to set up an ODBC Connection to a Microsoft Excel Spreadsheet. (It is very similar to what we do for a connection to an Access Database.
<p>The Excel file had to be closed for the php program to read it.. And, there was way more fiddling to get this working than we would expect:</p>
<p>The connection to the spreadsheet seems OK -- the drama starts when you try and figure out how to read one of the worksheets, and limit the reading to a certain range of columns or rows...</p>
<p>The spreadsheet must have a name defined covering the rows and columns we wish to read -- Choose Insert, on the Excel Menu, then down to Name, then sideways to Define: you MUST get a name defined, with a range specified -- this is the name we use in the SQL query -- so in this case, there is a name called daRows defined in the Excel Spreadsheet. When you make changes to the range, check that what is written in the box is exactly what you want, then click on OK -- don't trust Excel to give you the range you think you have highlighted -- it has a tendency to revert back to the range you had defined earlier. Once you have the Name defined, SAVE the file, and CLOSE it, before you run your PHP program-page.</p>
<h4>These are the Courses in the Spreadsheet:</h4>
<?php
/* ***********************************************************
* Connect to A Microsoft Excel Spreadsheet -- Note that we have
* to Set up the connection with a DSN in the Control Panel
* via Admin Tools beforehand! There was no scope for adding a
* user name and password -- this is different from MySQL and
* Access
* ***********************************************************/
$conn=odbc_connect('daExcelOne','','');
if (!conn)
{exit("Wiped out trying to connect: " . $conn);}
/* ***************************************************************
* Keep the code I experimented with -- Php 5 seems to have
* problems with first row and last row handling -- going by what
* is on the forums, it is very much hit and miss as to whether
* you get the first row or stop one short of the last row
* **************************************************************
$query = "SELECT * FROM daRows";
$rs = odbc_exec($conn,$query);
echo "Here comes the Lot";
$theLot = odbc_result_all($rs);
echo $theLot;
$query = "SELECT * FROM daRows";
$result = odbc_exec($conn,$query);
if(odbc_fetch_row($result, 1))
{ odbc_fetch_row($result, 0);
echo "We made it to result 0";
echo odbc_result($result,1);
echo odbc_result($result,2);
};
/* *********************************************
* Set up the Query and Run It - Need to fix
* the wipeout if statement!
* *********************************************/
$query = "SELECT * FROM daRows";
$rs = odbc_exec($conn,$query);
if (!conn)
{exit("Wiped out on the Query");
}
/* ******************************
* Set Up A Table For The Results
* ******************************/
echo "<table border='4' bgcolor=#00CCFF><tr>";
echo "<td>Course ID</td><td>Course Name</td><td>Program</td><td>Course Length</td><td>Fees</td></tr>";
while (odbc_fetch_row($rs))
{$courseID = odbc_result($rs,1);
$courseName = odbc_result($rs,2);
$courseProgram = odbc_result($rs,3);
$courseLength = odbc_result($rs,4);
$coursePrice = odbc_result($rs,5);
echo "<tr><td>" .number_format("$courseID",0,'.',','). "</td>";
echo "<td>" ."$courseName". "</td>";
echo "<td>" ."$courseProgram". "</td>";
echo "<td>" ."$courseLength". "</td>";
echo "<td align=right>" ."$".number_format("$coursePrice",2,'.',','). "</td></tr>";
}
echo "</table>";
/* ******************************
* Close The Connection Before We
* Bail Out -- Same as in MySQL
* ******************************/
odbc_close($conn);
?>
</body>
</html>