...

View Full Version : Excel sheet connection from php code ....



romram
04-18-2004, 01:59 PM
Can I get a php code that reads a selected row from an excel sheet and print it in the page that is using this code ... (The excel sheet is in the server) :confused:

firepages
04-18-2004, 02:51 PM
unless the server is a windows server no ( unless you can find some *NIX excel reader and exec() it )

on win32 look at COM (http://www.php.net/com)

alternately save the excel document in csv format (or XML with latest versions) either of which php can get at with fgetcsv (http://www.php.net/fgetcsv) or xml (http://www.php.net/xml) respectively

romram
04-18-2004, 06:37 PM
if you ignore the server can you give me a solution?

firepages
04-18-2004, 07:46 PM
you can't ignore the server .... a COM answer ? (for win32) , probably not , too many headaches ;) though a quick google should bring up some references.

*NIX , yes if we know the format the file is stored in and an example of such , e.g. a few sample CSV lines or an XML node.

as for an .xls reader for *NIX I assume such exist though I don;t know of any myself.

note that the csv/xml answer would be x-platform

Darknight
04-20-2004, 02:19 AM
This is easy.
Just make an ODBC connection to the .xls file and run sql to it.

missing-score
04-20-2004, 08:49 AM
can you do that? becuase an excel sheet is not a database. Im not ruling out the possibility but I very much doubt it will be possible like that.

Darknight
04-20-2004, 05:47 PM
can you do that? becuase an excel sheet is not a database. Im not ruling out the possibility but I very much doubt it will be possible like that.
You sure can.
The trick is to set whats called a 'Named range' in the xls file first. This becomes the name of the table in the sql statement. You then make the ODBC connection using the xls driver and connect it to the workbook.

I do this every day at work, but I connect to the odbc data with coldfusion not php so I would need to look up the php functions for that part.

missing-score
04-20-2004, 05:49 PM
hmm.. sounds pretty cool... that would be much easier than using COM, just a question though... what type of server are you running at work?

Darknight
04-20-2004, 05:54 PM
*thinks oh great I better get ready for this*
I run IIS on Microsoft systems.
*ducks the flying pies*

missing-score
04-20-2004, 06:52 PM
I wondered... Im not saying this wouldnt work on Apache/*NIX, but i wonder whether it is this easy becuase you are on a windows server.

romram
04-20-2004, 06:52 PM
This is what I found



<?PHP
$filename = "c:/spreadhseet/test.xls";
$sheet1 = 1;
$sheet2 = "sheet2";
$excel_app = new COM("Excel.application") or Die ("Did not connect");
print "Application name: {$excel_app->Application->value}\n" ;
print "Loaded version: {$excel_app->Application->version}\n";
$Workbook = $excel_app->Workbooks->Open("$filename") or Die("Did not open $filename $Workbook");
$Worksheet = $Workbook->Worksheets($sheet1);
$Worksheet->activate;
$excel_cell = $Worksheet->Range("C4");
$excel_cell->activate;
$excel_result = $excel_cell->value;
print "$excel_result\n";
$Worksheet = $Workbook->Worksheets($sheet2);
$Worksheet->activate;
$excel_cell = $Worksheet->Range("C4");
$excel_cell->activate;
$excel_result = $excel_cell->value;
print "$excel_result\n";
#To close all instances of excel:
$Workbook->Close;
unset($Worksheet);
unset($Workbook);
$excel_app->Workbooks->Close();
$excel_app->Quit();
unset($excel_app);
?>



I didn't test it yet...
but I want to ask what is the COM class? does it need a special library or something to use it? :confused:

missing-score
04-20-2004, 06:55 PM
I believe the COM class is built into PHP, as I installed my php distribution and was able to use COM without installing anything further.

firepages
04-21-2004, 01:13 AM
COM functions are bundled with PHP win32 build , e.g built in , but not available in any form on *NIX , in saying that perhaps with a *NIX ODBC driver you can do as Darknight suggests, a good point worth looking at anyway !

romram
04-21-2004, 06:30 AM
I tried the code and I ggot the following error:

Fatal error: Maximum execution time of 30 seconds exceeded in C:\sokkit\site\read_excel.php on line 5 :confused: :confused: :confused:

romram
04-21-2004, 07:04 AM
it works now... thankyou all... :p
ammmm... but can somebody tell me how can I modify the code so that it can check if the excel sheet is empty...

mgo
04-23-2004, 01:41 PM
Hi, I have the same error message, could you tell me what you have changed that make it work's after. thank you

romram
04-23-2004, 06:47 PM
Try to change this:
c:/spreadhseet/test.xls
to
c:\spreadhseet\\test.xls

mgo
04-24-2004, 08:55 PM
no it is the same error mail one thik I have to write this even I have the excel file in the same folder as my php page (with the code in it)in the server
<code>
$filename = "c:/spreadhseet/test.xls";

</code>
I have tried this but it dosn't work too
<code>
$filename = "test.xls";
</code>

romram
04-25-2004, 08:08 AM
change this:
$sheet1 = 1;
to
$sheet1 = "sheet1";// or to the name of thye sheet in your excel file..

mgo
04-26-2004, 07:31 AM
Hi,
I am sorry but it dosen't work I have done all the changes that you have manchend but the error message did not change: Fatal error: Cannot instantiate non-existent class: com in /home/sites/site34/web/testxls.php on line 5

thank's

firepages
04-26-2004, 07:45 AM
the COM extension is windows only , it is not installed on *NIX systems

mgo
04-27-2004, 09:33 AM
so what I have to do?

thank's

firepages
04-27-2004, 10:10 AM
you can't use COM on linux so there is little you can do about it short of moving to a windows server.

mgo
04-27-2004, 11:40 AM
even with easyphp???

firepages
04-27-2004, 12:53 PM
ok now you are confusing me ;)

"/home/sites/site34/web/testxls.php"
is a unix type path ,
but I thought easyphp was for windows ?
or is that just the way you setup your document_root ?

mgo
04-27-2004, 01:00 PM
yes I thik so that Easyphp is for Windows, but it dosen't work with me in local even in my hosting server(I don't know if it'is Linux or Windows)

thank you



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum