php read excel - Dev Articles

Dev Articles Community Forums Sponsor:

July 30th, 2004, 11:15 AM

veedee

Registered User

Join Date: Jul 2004

Posts: 1

Time spent in forums: < 1 sec
Reputation Power: 0

php read excel

hii y'all,
simple yet annoying question:
with php, is it possible to read a particular column/row from excel file (*.xls).
Thank you, I really appreciated your helps.

July 30th, 2004, 03:04 PM

dhouston

Contributing User

Dev Articles Beginner (1000 - 1499 posts)

Join Date: May 2003

Location: Tennessee

Posts: 1,355

Time spent in forums: < 1 sec
Reputation Power: 8

I don't know of a native way of doing this, but given that you can use PHP libraries to write Excel files, I suspect an industrious programmer could come up with a way to decode and read a spreadsheet. Give google a shot and see what you can turn up.

__________________
Please don't PM me asking for solutions outside the scope of a thread.
Keeping all responses in a thread stands to help others who come along later,
which is after all what this forum's all about.

July 30th, 2004, 06:14 PM

MadCowDzz

I'm Internet Famous

Dev Articles Frequenter (2500 - 2999 posts)

Join Date: Jan 2003

Location: Toronto, Canada

Posts: 2,890

Time spent in forums: 1 Week 16 h 14 m 9 sec
Reputation Power: 9

After searching Google (using the words PHP and Excel) I found what appears to be an answer in a different forum. I'll paste the code sample here so we can view it with fancy colours, but I give full credit to RQuadling's post in this forum.

PHP Code:


		
			
<?php

set_time_limit(0);

error_reporting(E_ALL);



#Set the workbook to use and its sheet. In this example we use a spreadsheet that

#comes with the Excel installation called: SOLVSAMP.XLS



$workbook = "C:\Program Files\Microsoft office\Office10\Samples\SOLVSAMP.XLS";

$sheet = "Quick Tour";



#Instantiate the spreadsheet component.

    $ex = new COM("Excel.sheet") or Die ("Did not connect");



#Get the application name and version    

    print "Application name:{$ex->Application->value}<BR>";

    print "Loaded version: {$ex->Application->version}<BR>";



#Open the workbook that we want to use.

    $wkb = $ex->application->Workbooks->Open($workbook) or Die ("Did not open");



#Create a copy of the workbook, so the original workbook will be preserved.

    $ex->Application->ActiveWorkbook->SaveAs("Ourtest");  

    #$ex->Application->Visible = 1; #Uncomment to make Excel visible.



# Read and write to a cell in the new sheet

# We want to read the cell E11 (Advertising in the 4th. Quarter)

    $sheets = $wkb->Worksheets($sheet);    #Select the sheet

    $sheets->activate;                 #Activate it

    $cell = $sheets->Cells(11,5) ;    #Select the cell (Row Column number)

    $cell->activate;                #Activate the cell

    print "Old Value = {$cell->value} <BR>";    #Print the value of the cell:10000

    $cell->value = 15000;            #Change it to 15000

    print "New value = {$cell->value}<BR> ";#Print the new value=15000



#Eventually, recalculate the sheet with the new value.

    $sheets->Calculate;            #Necessary only if calc. option is manual

#And see the effect on total cost(Cell E13)

    $cell = $sheets->Cells(13,5) ;    #Select the cell (Row Column number)

    $number = Number_format($cell->value);

    print "New Total cost =\$$number - was \$47,732 before.<BR>";

#Should print $57,809 because the advertising affects the Corporate overhead in the

# cell formula.



#Example of use of the built-in functions in Excel:

#Function: PMT(percent/12 months,Number of payments,Loan amount)

    $pay = $ex->application->pmt(0.08/12,10,10000);

    $pay = sprintf("%.2f",$pay);

        print "Monthly payment for $10,000 loan @8% interest /10 months: \$ $pay<BR>";

#Should print monthly payment = $ -1,037.03    

   

#Optionally, save the modified workbook

    $ex->Application->ActiveWorkbook->SaveAs("Ourtest");                      

#Close all workbooks without questioning

    $ex->application->ActiveWorkbook->Close("False");    

    unset ($ex);



?>

untested sample code

January 1st, 2008, 06:18 PM

OldManRiver

Registered User

Join Date: Mar 2004

Posts: 5

Time spent in forums: 1 h 12 m 52 sec
Reputation Power: 0

Tried ==> Not Working

Quote:

Originally Posted by MadCowDzz

After searching Google (using the words PHP and Excel) I found what appears to be an answer in a different forum.

MCD,

Tried this solution, but not able to make it work. Something is missing, maybe explaination of how to implement or another class needed to run.

I noticed all the original posts on this, from my inet searches, show these calling PEAR, but some of the later classes, have been scrubbed to be PEAR free, so maybe that is what is needed?

Please add details needed to run this correctly, PEAR free.

Thanks!

OMR

January 1st, 2008, 07:27 PM

OldManRiver

Registered User

Join Date: Mar 2004

Posts: 5

Time spent in forums: 1 h 12 m 52 sec
Reputation Power: 0

Excel Classes Tried

All,

Have dowloaded and tried the following classes and/or excel tools for PHP:

Code:

Excel Class        No example file to test
Excel Reader       Test 1 fails
Excel Reader       Test 2 writes code and errors to screen
Excel XML          Opens Excel but writes crap in the file
EZ Excel           Only show three java gen'd image markers on page
PHP Excel          Shows only text on page
PHP Excel 1.5.5    Shows only text on page
SAM Excel          Give COM error
TBS Excel          Shows good test page, links open Excel, but file has garbage in it
XLS Gen Test       Only shows PHP code as text
XLS Gen SQL 1      Only shows PHP code as text
XLS Stream D       Opens Excel but writes crap in the file 
XLS Stream E       Gives PHP error
XMerge             Displays XML code

I downloaded all the source files into the dir "/Zips" under localhost and then extracted all the files into their distinct folders and called all this with the following HTML file:

Code:

<html>
<table border=2>
   <tr>
   <td>Excel Class<p>No example file to test</td>
   </tr>

   <tr>
   <td><a href="Zips/Excel Reader/example.php">Excel Reader T1</a></td>
   </tr>

   <tr>
   <td><a href="Zips/Excel Reader/example2.php">Excel Reader T2</a></td>
   </tr>

   <tr>
   <td><a href="Zips/Excel XML Parse/sample.php">Excel XML</a></td>
   </tr>

   <tr>
   <td><a href="Zips/EZ Excel/test_easyexcel.html">EZ Excel</a></td>
   </tr>

   <tr>
   <td><a href="Zips/PHP Excel/Tests/runall.php">PHP Excel</a></td>
   </tr>

   <tr>
   <td><a href="Zips/PHP Excel 1.5.5/Tests/runall.php">PHP Excel 1.5.5</a></td>
   </tr>

   <tr>
   <td><a href="Zips/SAM Excel/testclass.php">SAM Excel</a></td>
   </tr>

   <tr>
   <td><a href="Zips/TBS Excel/demo_main.htm">TBS Excel</a></td>
   </tr>

   <tr>
   <td><a href="Zips/XLS Gen/test.phtml">XLS Gen Test</a></td>
   </tr>

   <tr>
   <td><a href="Zips/XLS Gen/test_mysql_1.phtml">XLS Gen SQL 1</a></td>
   </tr>

   <tr>
   <td><a href="Zips/XLS Stream/example_download.php">XLS Stream Download</a></td>
   </tr>

   <tr>
   <td><a href="Zips/XLS Stream/example_export.php">XLS Stream Export</a></td>
   </tr>

   <tr>
   <td><a href="Zips/XMerge/build.xml">XMerge</a></td>
   </tr>
</table>
</html>

The three (3) showing promise are:
Excel XML
TBS Excel
XLS Stream
but more work is needed to find out why they read or write garbage into their files.

OMR

March 26th, 2008, 09:07 AM

GregOP

Registered User

Join Date: Mar 2008

Posts: 1

Time spent in forums: 14 m 48 sec
Reputation Power: 0

If by "excel read" you mean the phpexcelread class you can download from sourceforge,

I have some insight on why it doesn't work for you. There are two files, reader.php and oleread.inc. In reader.php, there is a line that says include_once and it has oleread.php listed, when it should be oleread.inc. I'm completely unsure why he released it with that mistake.

Also, the path for the file was wrong.

Once those two things are fixed, it works great. I however was having problems still, and it turned out the excel files I was trying to read were encrypted. Just right click, properties, go to advanced and disable it. Sorry for reviving a dead thread

July 9th, 2008, 08:52 PM

jasonwoon

Registered User

Join Date: Jul 2008

Posts: 1

Time spent in forums: 25 m 49 sec
Reputation Power: 0

I've tried using phpexcelread but i get this error
Fatal error: Allowed memory size of 83886080 bytes exhausted (tried to allocate 16 bytes)
Cos my excel file is about 70MB with 29,000 rows of data's..
any other suggestion?

July 12th, 2008, 10:04 AM

JohnSeito

Registered User

Join Date: Jul 2008

Posts: 1

Time spent in forums: 13 m 15 sec
Reputation Power: 0

I inputted this code

PHP Code:


		
			
<?php

set_time_limit(0);

error_reporting(E_ALL);



#Set the workbook to use and its sheet. In this example we use a spreadsheet that

#comes with the Excel installation called: SOLVSAMP.XLS



$workbook = "C:\Program Files\Microsoft office\Office10\Samples\SOLVSAMP.XLS";

$sheet = "Quick Tour";



#Instantiate the spreadsheet component.

    $ex = new COM("Excel.sheet") or Die ("Did not connect");



#Get the application name and version    

    print "Application name:{$ex->Application->value}<BR>";

    print "Loaded version: {$ex->Application->version}<BR>";



#Open the workbook that we want to use.

    $wkb = $ex->application->Workbooks->Open($workbook) or Die ("Did not open");



#Create a copy of the workbook, so the original workbook will be preserved.

    $ex->Application->ActiveWorkbook->SaveAs("Ourtest");  

    #$ex->Application->Visible = 1; #Uncomment to make Excel visible.



# Read and write to a cell in the new sheet

# We want to read the cell E11 (Advertising in the 4th. Quarter)

    $sheets = $wkb->Worksheets($sheet);    #Select the sheet

    $sheets->activate;                 #Activate it

    $cell = $sheets->Cells(11,5) ;    #Select the cell (Row Column number)

    $cell->activate;                #Activate the cell

    print "Old Value = {$cell->value} <BR>";    #Print the value of the cell:10000

    $cell->value = 15000;            #Change it to 15000

    print "New value = {$cell->value}<BR> ";#Print the new value=15000



#Eventually, recalculate the sheet with the new value.

    $sheets->Calculate;            #Necessary only if calc. option is manual

#And see the effect on total cost(Cell E13)

    $cell = $sheets->Cells(13,5) ;    #Select the cell (Row Column number)

    $number = Number_format($cell->value);

    print "New Total cost =\$$number - was \$47,732 before.<BR>";

#Should print $57,809 because the advertising affects the Corporate overhead in the

# cell formula.



#Example of use of the built-in functions in Excel:

#Function: PMT(percent/12 months,Number of payments,Loan amount)

    $pay = $ex->application->pmt(0.08/12,10,10000);

    $pay = sprintf("%.2f",$pay);

        print "Monthly payment for $10,000 loan @8% interest /10 months: \$ $pay<BR>";

#Should print monthly payment = $ -1,037.03    

   

#Optionally, save the modified workbook

    $ex->Application->ActiveWorkbook->SaveAs("Ourtest");                      

#Close all workbooks without questioning

    $ex->application->ActiveWorkbook->Close("False");    

    unset ($ex);



?>

and the only result I get in my browse are:

Application name:Microsoft Excel
Loaded version: 11.0

why is that?

October 13th, 2008, 04:46 PM

Mark Baker

Registered User

Join Date: Oct 2008

Posts: 11

Time spent in forums: 1 h 27 m 42 sec
Reputation Power: 0

Quote:

Originally Posted by OldManRiver

PHP Excel Shows only text on page
PHP Excel 1.5.5 Shows only text on page

PHPExcel will only display text when you execute runall.php because it's a command line script to test the code.

There's a whole host of examples in the zip that you downloaded that both read and write Excel files to demonstrate the capabilities of the package that are executed by the runall.php script; and these all write their output to file.
Opening these generated files in Excel (or other appropriate packages such as Adobe Acrobat for the PDF test) would show you just a small example of what the package can actually do.

---
Mark Baker
Developer: PHPExcel

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: