Yeah, you can talk to excel with ODBC. I'll try and find some of my old code, but it'll be rubbish as it really was my first learn to swim experience of php. Here's some functions to connect and stuff
<?php
/* This contains
list_tables_link($dbcon, $type="TABLE"); shows table names and hrefs them
list_tables($dbcon);
get_field_names($dbcon, $table) returns array of names
get_table_names($dbcon); // returns array of types that are TABLE - not System Table
show_table($res, $fmt); may need work on null result stuff
read_sql($filename="sql.txt"); returns array of queries
$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=$directory\\$filename";
$db_connstr = "DRIVER={Microsoft Excel Driver (*.xls)}; DBQ=$directory\\$filename";
$db_connstr = "DRIVER={Microsoft Excel Driver (*.xls)};FirstRowHasNames=0;DBQ=$directory\\$filename;DriverID=790";
*/
function odbc_connect_mdb($filename, $user="", $password="")
{
$connStr="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=$filename";
return odbc_connect($connStr, $user, $password);
}
function odbc_connect_xls($filename, $user="", $password="")
{
$connStr="DRIVER={Microsoft Excel Driver (*.xls)};DBQ=$filename";
return odbc_connect($connStr, $user, $password);
}
// this is crap - filename and thisfile should be passed
function list_tables_link($dbcon, $type="TABLE")
{
global $thisfile, $filename;
echo "FILENAME: $filename<br>";
echo "<b>WARNING:</b> Currently do not select tables with text after the $, e.g. 'This table$'devtab";
$tables=odbc_tables($dbcon);
$tNames=array();
echo "<table>";
echo "<tr><th>Table Type</th><th>Workbook Name</th></tr>";
while(odbc_fetch_row($tables) )
{
echo "<tr>";
$tName=odbc_result($tables, 3);
$tRef="filename=$filename&table=".urlencode( $tName );
echo "<td>".odbc_result($tables, 4)."</td><td><a href=\"$thisfile?$tRef\">$tName</a></td>";
}
}
// returns an array of tables, and prints stuff MODIFY
function list_tables($dbcon, $type="TABLE")
{
$tables=odbc_tables($dbcon);
$tNames=array();
while(odbc_fetch_row($tables) )
{
// if ( odbc_result($tables, 4) == $type)
echo "Type: ".odbc_result($tables, 4)." - ".odbc_result($tables, 3)."<br>";
$tNames[]=odbc_result($tables, 3);
}
return $tNames;
}
// returns table types that are TABLE - not System Table - it's ok - soddit, return all
function get_table_names($dbcon)
{
$tables=odbc_tables($dbcon);
$tNames=array();
while(odbc_fetch_row($tables) )
{
// if (odbc_result($tables, 4) == "TABLE")
if (odbc_result($tables, 4) != "SYSTEM TABLE")
$tNames[]=odbc_result($tables, 3);
}
return $tNames;
}
// this should be ok
function get_field_names($dbcon, $table)
{
$fNames=array();
$query="SELECT * FROM [$table] WHERE 0"; //dys?
// $query="SELECT * FROM $table WHERE 0"; //dys?
$sth=odbc_exec($dbcon, $query) or die(odbc_errormsg()); //perhaps dying here is a bit harsh
for($i=1;$i<=odbc_num_fields($sth);$i++)
$fNames[]=odbc_field_name($sth,$i);
odbc_free_result($sth);
return $fNames;
}
// I'm satisfiled with this func
function show_table($res, $fmt="")
{
if(!$res) return;
$nf = odbc_num_fields($res);
echo "<table $fmt><tr>";
for ($c=1; $c<=$nf; $c++) echo "<th>".odbc_field_name($res, $c)."</th>";
echo "</tr>";
while ( odbc_fetch_into($res, $row) )
{
echo "<tr>";
for ($c=0; $c<$nf; $c++)
{
if ( !$cell=$row[$c] ) $cell=" ";
echo "<td>$cell</td>";
}
echo "</tr>";
}
echo "</table>";
}
Don't know if any of that will help, because as soon as I could get clear of the blast radius and onto 'proper' databases I never looked back. I do remember that result indexes start at 1 and not zero
I used this Jet SQL reference at the time http://www.devguru.com/Technologies/jetsql/quickref/jet_sql_list.html
