php read excel - Dev Articles

Dev Articles Community Forums Sponsor:

Ajax Application Generator Generate database and reporting .NET Web apps in minutes. Quickly create visually stunning, feature-rich apps that are easy to customize and ready to deploy. Download Now!

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: 7

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 4 m 48 sec
Reputation Power: 8

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 10 m 59 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 10 m 59 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

Viewing: Dev Articles Community Forums > Programming > PHP Development > php read excel

« Previous Thread | Next Thread »

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:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

More Free IT White Papers!