Back To Programming / Scripting / Coding Forum

Reading .XLS files through PHP?

  • Tannu4u
  • Proficient
  • Proficient
  • User avatar
  • Posts: 473
  • Loc: India

Post 3+ Months Ago

Hi,can anyone tell me how can i read .XLS files using PHP.I have to read the .xls files through PHP and put the data to MySql database.Any answers will be greatly acknowledged.thanks.......... :shock:
  • rjstephens
  • Professor
  • Professor
  • User avatar
  • Posts: 753
  • Loc: Brisbane, Australia

Post 3+ Months Ago

you would have to write your own excel library. This would be almost impossible unless microsoft gave you the specifications.

Your best chance is to lead the spreadsheet into excel (or OpenOffice) and save it as CSV
  • RichB
  • Guru
  • Guru
  • User avatar
  • Posts: 1102
  • Loc: Boston

Post 3+ Months Ago

There's a free php library at sourceforge that is supposedly able to do this although I have not used it myself.

http://sourceforge.net/projects/phpexcelreader/
  • Axe
  • Genius
  • Genius
  • User avatar
  • Posts: 5575
  • Loc: Sub-level 28

Post 3+ Months Ago

Your best bet is probably exporting to CSV like rjstephens says.
  • Tannu4u
  • Proficient
  • Proficient
  • User avatar
  • Posts: 473
  • Loc: India

Post 3+ Months Ago

Hi everybody this is the code i have used:
:shock:
PHP Code: [ Select ]
<?
 
$filename = "test.xls";
 
$sheet1 = "sheet1";
 
$sheet2 = "sheet2";
 
$excel_app = new COM("Excel.application") or Die ("Did not connect");
 
print "Application name: {$excel_app->Application->value}<br>\n" ;
 
print "Loaded version: {$excel_app->Application->version}<br>\n";
 
print $filename;
 
$excel_app->Application->Visible = 1; #Make Excel visible.
 
$Workbook = $excel_app->Workbooks->Open($filename) or Die("Did not open $filename $Workbook");
 
$Worksheet = $Workbook->Worksheets($sheet1);
 
echo "AMIT YADAV<br>";
 
$Worksheet->activate;
 
$excel_cell = $Worksheet->Range("C4");
 
$excel_cell->activate;
 
$excel_result = $excel_cell->value;
 
print "$excel_result<br>\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);
 
?>
  1. <?
  2.  
  3. $filename = "test.xls";
  4.  
  5. $sheet1 = "sheet1";
  6.  
  7. $sheet2 = "sheet2";
  8.  
  9. $excel_app = new COM("Excel.application") or Die ("Did not connect");
  10.  
  11. print "Application name: {$excel_app->Application->value}<br>\n" ;
  12.  
  13. print "Loaded version: {$excel_app->Application->version}<br>\n";
  14.  
  15. print $filename;
  16.  
  17. $excel_app->Application->Visible = 1; #Make Excel visible.
  18.  
  19. $Workbook = $excel_app->Workbooks->Open($filename) or Die("Did not open $filename $Workbook");
  20.  
  21. $Worksheet = $Workbook->Worksheets($sheet1);
  22.  
  23. echo "AMIT YADAV<br>";
  24.  
  25. $Worksheet->activate;
  26.  
  27. $excel_cell = $Worksheet->Range("C4");
  28.  
  29. $excel_cell->activate;
  30.  
  31. $excel_result = $excel_cell->value;
  32.  
  33. print "$excel_result<br>\n";
  34.  
  35. $Worksheet = $Workbook->Worksheets($sheet2);
  36.  
  37. $Worksheet->activate;
  38.  
  39. $excel_cell = $Worksheet->Range("C4");
  40.  
  41. $excel_cell->activate;
  42.  
  43. $excel_result = $excel_cell->value;
  44.  
  45. print "$excel_result\n";
  46.  
  47. #To close all instances of excel:
  48.  
  49. $Workbook->Close;
  50.  
  51. unset($Worksheet);
  52.  
  53. unset($Workbook);
  54.  
  55. $excel_app->Workbooks->Close();
  56.  
  57. $excel_app->Quit();
  58.  
  59. unset($excel_app);
  60.  
  61. ?>



I am getting the error--> :( :!:

Application name: Microsoft Excel
Loaded version: 9.0
test.xls
Warning: (null)(): Invoke() failed: Exception occurred. Source: Microsoft Excel Description: 'test.xls' could not be found. Check the spelling of the file name, and verify that the file location is correct. If you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted. in C:\Program Files\BadBlue\PE\Temp\phpxls.php on line 11
Did not open test.xls

:roll: any clue :roll:
  • Tannu4u
  • Proficient
  • Proficient
  • User avatar
  • Posts: 473
  • Loc: India

Post 3+ Months Ago

hi guy's finally i got it :lol: , here is the code again which i used :

PHP Code: [ Select ]
<?
 
$filename = "c:\spreadhseet\\test.xls";
 
$sheet1 = 1;
 
$arr=array(1=>'a','b','c','d');
 
$sheet2 = "sheet2";
 
$excel_app = new COM("Excel.application") or Die ("Did not connect");
 
print "Application name: {$excel_app->Application->value}<br>\n" ;
 
print "Loaded version: {$excel_app->Application->version}<br>\n";
 
print $filename;
 
//$excel_app->Application->Visible = 1; #Make Excel visible.
 
$Workbook = $excel_app->Workbooks->Open($filename) or Die("Did not open $filename $Workbook");
 
$Worksheet = $Workbook->Worksheets($sheet1);
 
echo "AMIT YADAV<br>";
 
$Worksheet->activate;
 
$excel_cell = $Worksheet->Range("C1");
 
$excel_cell->activate;
 
for($i=1; $i<4;$i++)
 
   {
 
   for($j=1; $j<4;$j++)
 
      {
 
         $excel_cell = $Worksheet->Range($arr[$i].$j);
 
         echo "<br> The value is $arr[$i].$j  => " . $excel_cell->value;
 
      }
 
   }
 
     
 
   
 
   
 
 
 
 
 
$excel_result = $excel_cell->value;
 
 
 
$Workbook->Close;
 
unset($Worksheet);
 
unset($Workbook);
 
$excel_app->Workbooks->Close();
 
$excel_app->Quit();
 
unset($excel_app);
 
?>
  1. <?
  2.  
  3. $filename = "c:\spreadhseet\\test.xls";
  4.  
  5. $sheet1 = 1;
  6.  
  7. $arr=array(1=>'a','b','c','d');
  8.  
  9. $sheet2 = "sheet2";
  10.  
  11. $excel_app = new COM("Excel.application") or Die ("Did not connect");
  12.  
  13. print "Application name: {$excel_app->Application->value}<br>\n" ;
  14.  
  15. print "Loaded version: {$excel_app->Application->version}<br>\n";
  16.  
  17. print $filename;
  18.  
  19. //$excel_app->Application->Visible = 1; #Make Excel visible.
  20.  
  21. $Workbook = $excel_app->Workbooks->Open($filename) or Die("Did not open $filename $Workbook");
  22.  
  23. $Worksheet = $Workbook->Worksheets($sheet1);
  24.  
  25. echo "AMIT YADAV<br>";
  26.  
  27. $Worksheet->activate;
  28.  
  29. $excel_cell = $Worksheet->Range("C1");
  30.  
  31. $excel_cell->activate;
  32.  
  33. for($i=1; $i<4;$i++)
  34.  
  35.    {
  36.  
  37.    for($j=1; $j<4;$j++)
  38.  
  39.       {
  40.  
  41.          $excel_cell = $Worksheet->Range($arr[$i].$j);
  42.  
  43.          echo "<br> The value is $arr[$i].$j  => " . $excel_cell->value;
  44.  
  45.       }
  46.  
  47.    }
  48.  
  49.      
  50.  
  51.    
  52.  
  53.    
  54.  
  55.  
  56.  
  57.  
  58.  
  59. $excel_result = $excel_cell->value;
  60.  
  61.  
  62.  
  63. $Workbook->Close;
  64.  
  65. unset($Worksheet);
  66.  
  67. unset($Workbook);
  68.  
  69. $excel_app->Workbooks->Close();
  70.  
  71. $excel_app->Quit();
  72.  
  73. unset($excel_app);
  74.  
  75. ?>




THERE ARE SOMETHING TO KEEP IN MIND =>
1) THE PROBLEM WAS THAT THA PATH WAS NOT CORRECT TAKE CARE OF
THE PATH.

2) SECOND IS THE ARRAY SEE $arr... IN THE CODE SET IT TO UR
REQUIREMENT.

THATS ALL.......... :lol:
  • vijaya
  • Newbie
  • Newbie
  • vijaya
  • Posts: 7

Post 3+ Months Ago

Hi
I get the following error with the code below.
I think this is because php is loaded on linux

Do you know how to read a .xls file, when the php is installed on linux?
your response is greatly appreciated.
Thanks
Vijaya

Fatal error: Cannot instantiate non-existent class: com
PHP Code: [ Select ]
 
 $excel_app = new COM("Excel.application") or Die ("Did not connect");
 
print "Application name: {$excel_app->Application->value}\n" ;
 
 
  1.  
  2.  $excel_app = new COM("Excel.application") or Die ("Did not connect");
  3.  
  4. print "Application name: {$excel_app->Application->value}\n" ;
  5.  
  6.  
  • Cafu
  • Student
  • Student
  • Cafu
  • Posts: 97

Post 3+ Months Ago

I am not an expert on COM objects, but I seriously doubt you can use them from linux.

If there is a way, I would guess that it involves making a com object available to be accessed remotely on a windows machine. But, you would still need to have a windows machine involved if it is possible.


CSV files are more standard and excel can export to CSV. Go with that suggestion.
  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1206
  • Loc: UK

Post 3+ Months Ago

fopen?
  • vijaya
  • Newbie
  • Newbie
  • vijaya
  • Posts: 7

Post 3+ Months Ago

Hi
Thanks a lot for the reply.
To convert the excel to a csv
I have more than 1 sheet of data.
The other sheets have reference data in them...
Thank you again...
Vijaya
  • raoisrar
  • Born
  • Born
  • raoisrar
  • Posts: 1

Post 3+ Months Ago

you can read xls with Spreadsheet_Excel_Reader class.
  • spork
  • Silver Member
  • Silver Member
  • spork
  • Posts: 6128
  • Loc: Seattle, WA

Post 3+ Months Ago

Quote:
Jul 19, 2004

This thread is nearly 4 years old. I'm sure the issue has been resolved for quite some time now.

Page 1 of 1

Post Information

  • Total Posts in this topic: 12 posts
  • Users browsing this forum: No registered users and 64 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2021. Ozzu® is a registered trademark of Unmelted, LLC.