Excel reading script

[mikanmao]

Just a simple stupid question.
Is MS Excel need to be installed if i want to run some excel reading script like the following?

<?php
// starting excel
$excel = new COM("excel.application") or die("Unable to instanciate excel");
print "Loaded excel, version {$excel->Version}\n";

//bring it to front
#$excel->Visible = 1;//NOT
//dont want alerts ... run silent
$excel->DisplayAlerts = 0;

//open document
$excel->Workbooks->Open("C:\test.xls");
//XlFileFormat.xlcsv file format is 6
//saveas command (file,format ......)
$excel->Workbooks[1]->SaveAs("c:\test.csv",6);

//closing excel
$excel->Quit();

//free the object
$excel->Release();
$excel = null;

$row = 1;
$handle = fopen("C:\test.csv", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
$num = count($data);
echo "$i<br><p> $num fields in line $row: <br /></p>\n";
$row++;
for ($c=0; $c < $num; $c++)
{
echo $data[$c] . "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;\n";
}
}

fclose($handle);

?>

[ahundiak]

Yep. Need Excel installed to use the COM object.

This might suit your needs:
http://sourceforge.net/projects/phpexcelreader

No Excel (or even Windows) required.

[mikanmao]

Thank you ahundiak, this is great.
But i cannot get the array values insert into the database correctly.
What's wrong with my code?

<?php

require_once 'Excel/reader.php';

//Connect to Database
$user = "user";
$pass = "pass";
$db = "db";
$link = mysql_connect("localhost","$user","$pass");
if (!link)
die( "Couldn't connect to Mysql");
print "Successfully connected to db server</p>";
mysql_select_db ($db)
or die( "Couldn't open $db: ".mysql_error());
print "Successfully selected database \"$db\"<p>";

// ExcelFile($filename, $encoding);
$data = new Spreadsheet_Excel_Reader();

// Set output Encoding.
$data->setOutputEncoding('CP1251');

/
if you want you can change 'iconv' to mb_convert_encoding:
$data->setUTFEncoder('mb');

/

/
By default rows & cols indeces start with 1
For change initial index use:
$data->setRowColOffset(0);
*
/

/
Some function for formatting output.
$data->setDefaultFormat('%.2f');
setDefaultFormat - set format for columns with unknown formatting

$data->setColumnFormat(4, '%.3f');
setColumnFormat - set format for column (apply only to number fields)

/

$data->read('upload/items.xls');

/*

$data->sheets[0]['numRows'] - count rows
$data->sheets[0]['numCols'] - count columns
$data->sheets[0]['cells'][$i][$j] - data from $i-row $j-column

$data->sheets[0]['cellsInfo'][$i][$j] - extended info about cell

$data->sheets[0]['cellsInfo'][$i][$j]['type'] = "date" | "number" | "unknown"
    if 'type' == "unknown" - use 'raw' value, because  cell contain value with format '0.00';
$data->sheets[0]['cellsInfo'][$i][$j]['raw'] = value if cell without format 
$data->sheets[0]['cellsInfo'][$i][$j]['colspan'] 
$data->sheets[0]['cellsInfo'][$i][$j]['rowspan'] 

*/

error_reporting(E_ALL ^ E_NOTICE);

$a = array();
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++)

{
echo "Record".$i."<br>";
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++)
{
$a[$j] = $data->sheets[0]['cells'][$i][$j];
}

$query = "INSERT INTO product (Item_no,Desp,Inner_unit,Outer_unit,Unit,Cu_ft,DI_price,UPC_code,Vend_no) values ";
$query .= "('$a[2]','$a[5]','$a[8]','$a[9]','$a[7]','$a[10]','$a[17]','$a[33]','$a[1]')";
mysql_query ($query)
or die ("Couldn't add data<br>".mysql_error());

echo "End of line.<br><br>";
}

mysql_close($link);
?>