Solved

php excel cvs

Posted on 2006-11-16
11
431 Views
Last Modified: 2012-06-21
i can read the contents of a basic excel file without much formatting applied in the excel file, but how can i read the worksheet name?

example my excel file contains a worksheet for each week of the year so i have 52 worksheets.  basically all i want to do is read the contents of a specific worksheet.

i want to specify the worksheet name but dont know how to specific or select a specific worksheet within the excel file to read  the data from...
0
Comment
Question by:ellandrd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 3

Assisted Solution

by:incrediblejohn
incrediblejohn earned 250 total points
ID: 17957549
<?php
/* Class for use with PHP4 scripts only*/


/*
 * This is an Excel class to create,load,read,write,save and use some of the internal
 * functionalities of workbooks and sheets.
 * Tested with Windows 98 - MS Office 2000
 * Apache 1.3.9 PHP4.02 Running as CGI
 * (c) Alain M. Samoun 09/2000.
 * alain@sonic.net
 * Gnu GPL code (see www.fsf.org for more information).
 */


class Excel {

        /* variables */

      var $ex;
        var $pathin;
        var $pathout;
        var $workbook;
        var $sheet;
        var $visible;
        var $fformatin;
        var $fformatout;
        var $cell;
        var $rangesens;
        var $range;
        var $ext;
        var $oext;
       

        /* Constructor */

       function excel()
       {
              #Instantiate Excel
                $this->ex = new COM("Excel.sheet") or Die ("Did not instantiate Excel");

                return 1;
       }


      function XL($workbook,$pathin="",$sheet="sheet1")       
       {  
       
             if ($workbook != "")
             {
                            #Load the workbook
                      $wkb = $this->ex->application->Workbooks->Open($pathin.$workbook) or Die ("Did not open $pathin $workbook");
                      
                }else{
                      #New workbook
                      $wkb = $this->ex->application->Workbooks->Add or Die ("Unable to add a workbook");
                }
 
                if ($sheet != "")
                {
                      #Activate the sheet
                           $sheets = $wkb->Worksheets($sheet) or Die ("Unable to activate $sheet");
                           
                     }else{
                           #new sheet
                           $sheet = "sheet1" ;
                           
                     }
                     #Excel Won't prompt the user when replacing or closing workbooks
                     #Comment the line below if you want Excel to prompt
                    $this->ex->application->DisplayAlerts = "False";
            return 1;
               
       }
       

      function readrange($sheet="sheet1",$range)
      {
      
            #Read all the cells in the range to $result and return it
            unset ($result);
            
            $range = trim($range);
            #Determine start and end of range
            $tokstart = strtok($range,":");
            $tokend = strtok(":");
            if ($tokend =="")
            {
                  #Read one single cell
                  $sheets = $this->ex->Application->Worksheets($sheet);      
                  $sheets->activate;                               
                  #Select the cell
                  $selcell = $sheets->Range($range);
                  $selcell->activate;
                  return $selcell->value;
            }
            #Read a range of cells
            #determine column and row numbers
            $sheets = $this->ex->Application->Worksheets($sheet);
            $sheets->activate;
            $rgstart = $sheets->range($tokstart);
            $colstart = $rgstart->column;
            $rowstart = $rgstart->row;
            $rgend = $sheets->range($tokend);
            $colend = $rgend->column;
            $rowend = $rgend->row;
            if ($colstart>$colend or $rowstart>$rowend)
            {
                  Print ("Notation Error! Cell Column/Row should be increasing.");
                  return;
            }
            #Now read each cell
            
            if ($colstart == $colend)
            {
                  #Read Vertically
                  $j=0;
                  For ($i= $rowstart; $i<=$rowend; $i++)
                  {
                  
                        $selcell = $sheets->cells($i,$colstart);
                        $selcell->activate;
                        $result[$j] = $selcell->value;
                        $j++;
                  }
            }else
            {
                  #Read horizontally
                  $j=0;
                  For ($i= $colstart; $i<=$colend; $i++)
                  {
                  
                        $selcell = $sheets->cells($rowstart,$i);
                        $selcell->activate;
                        $result[$j] = $selcell->value;
                        $j++;
                  }
            }

                  return $result;
      }
      
      
      
      function writerange($sheet="sheet1",$range,$value)
      {
      
            #Fill up all the cells in the range with array
            
            $range = trim($range);
            #Determine start and end of range
            $tokstart = strtok($range,":");
            $tokend = strtok(":");
            if ($tokend =="")
            {
            
                  # Write to a single cell in the active sheet
                  $cell = trim($range);
                  #Select the sheet
                  $sheets = $this->ex->Application->Worksheets($sheet);
                  $sheets->activate;
                  #Select the cell
                  #print "cell:$cell";
                  $selcell = $sheets->Range($cell);
                  $selcell->activate;
                  #print "value:$value <BR>";
                  $selcell->value = $value;
                  return;
            }
            
            #determine column and row numbers
            $sheets = $this->ex->Application->Worksheets($sheet);
            $sheets->activate;
            $rgstart = $sheets->range($tokstart);
            $colstart = $rgstart->column;
            $rowstart = $rgstart->row;
            $rgend = $sheets->range($tokend);
            $colend = $rgend->column;
            $rowend = $rgend->row;
            if ($colstart>$colend or $rowstart>$rowend)
            {
                  Print ("Notation Error! Cell Column/Row should be increasing.");
                  return;
            }
            #Now write each cell
            
            if ($colstart == $colend)
            {
                  #write Vertically
                  $j=0;
                  For ($i= $rowstart; $i<=$rowend; $i++)
                  {
                  
                        $selcell = $sheets->cells($i,$colstart);
                        $selcell->activate;
                        $selcell->value = $value[$j];
                        $j++;
                  }
            }else
            {
                  #Write horizontally
                  $j=0;
                  For ($i= $colstart; $i<=$colend; $i++)
                  {
                  
                        $selcell = $sheets->cells($rowstart,$i);
                        $selcell->activate;
                        $selcell->value = $value[$j];
                        $j++;
                  }
            }

            return 1;
      }      

        function saveas($workbook,$pathout,$ext)  
        {
       
              
              #First get the file format code for the extension $ext
              $code = $this->fileformater($ext);
              $basefile = strtok($workbook,".");
              $newworkbook = $basefile."."."$ext";
              
              
              #If no prompt and file exists it will be replaced.
              
            
              #Save the current workbook as new workbook
                    #The following line will work for converting spreadsheets file to xls
                    #but if the original is an excel file and the new file another format
                    #then it may not work because limitations of excel.(See excel doc)
                    
                    $this->ex->Application->ActiveWorkbook->SaveAS($pathout.$newworkbook,$code);
              
              
              return 1;
       }
       


        function fileformater($ext)
        {

            switch(strtolower($ext))
            {
            
                  case   "slk":
                        return  2;
                        break;
                        
                  case   "xlt":
                        return  -4143;
                        break;
                        
                  case   "txt":
                        return  -4158;
                        break;
                        
                  case   "csv":
                        return  6;
                        break;
                        
                  case   "xlw":
                        return  35;
                        break;
                        
                  case   "wk4":
                        return  38;
                        break;
                        
                  case   "wq1":
                        return  -4158; #was 34
                        break;
                        
                  case   "dif":
                        return  9;
                        break;
                        
                  case   "xla":
                        return  -4143;
                        break;
                        
                  case   "wk3":
                        return  32;#doesn't work anymore
                        break;
                        
                  case   "xls":
                        return -4143;
                        break;
                        
                  case   "htm":
                        return  44;
                        break;
                        
                  case   "wks":
                        return  4;
                        break;
                        
                  default:
                        return  -4143;
                        
                        
            }
      }
      
      function XLTranslate($pathin,$pathout,$oext,$ext,$kill=0)
      {
      
            #This function will translate automatically all spreadsheets files, with the
            #$oext extension, in the $pathin directory, to another spreadsheet file,
            #with the $ext extension, to the $pathout directory.
            #It will erase the original file if $kill switch = 1.
            #Limitations: Will work always when translating none excel files to
            #excel files(Extension=xl*) and with the translation xls->htm . It will
            #not generally work when translating excel files to other formats because
            #the questions asked by the excel program stop the script.
      
            #Get all files in the source directory $pathin to the $filelist array
            chdir($pathin);
            $dir=dir (".");
            $i=1;
            while($file=$dir->read())
            {
                  $filelist [$i] = $file;
                  $i++;
            }
            $dir->close;
      
            #Translate each file, with the original extension $oext, in the $filelist
            #to the needed extension $ext.
            
            for ($i=1;$i<= sizeof($filelist); $i++)
            {
            
                  $file = $filelist[$i];
                  
                  $basefile =  strtok($file,".");
                  $extension = strtok(".");
                  
                        
                  if (strtolower($extension) == strtolower($oext))
                  {
                        echo "<BR> $file";
                        $this->XL($file,$pathin,$sheet="");
                        $this->saveas($file,$pathout,$ext,"");
                        #Erase the original file if $kill=1
                        if ($kill)
                        {
                              chmod ($file,0777);
                              unlink ($pathin.$file);
                        }
                        #Close the new workbook
                        $this->closeXL();
                    }
                   
            }
            
            return 1;      
      }
              
      
       
             function closexl()
             {
             
                   #Close active workbook without prompt from Excel
             
            $this->ex->application->ActiveWorkbook->Close("False");      
            return 1;
            
      }
       
      function runfunction($funct,$arrayparam)
      {
            #Run and return value of an excel function
            
            $params = implode(",",$arrayparam);
            
            eval ("\$result = \$this->ex->application->$funct($params);");
            
            return $result;
            
      }
      
      Function runmacro($workbook,$macroname)
      {
            $this->ex->application->Run("$workbook!$macroname");
            
            return 1;
      }
      
      Function createhyperlink($sheet="sheet1",$cell,$hyperl)
      {
            #Not working as 9/2/00 4:57PM
            print "<br>link = $hyperl <br>";
            $sheets = $this->ex->Application->Worksheets($sheet);
            $sheets->activate;
                  #Select the cell
                  $selcell = $sheets->Range($cell);
                  
                  $sheets->hyperlinks->add($selcell,$hyperl);
                  
                  
                  return 1;
      }
      
      function calculate($sheet="sheet1")
      {
            #Calculate (update) the current sheet
            
            $sheets = $this->ex->Application->Worksheets($sheet);      #Select the sheet
            $sheets->activate;
            $sheets->Calculate;
            return 1;
            
      }
      
      function writetoname($sheet,$name,$value)# Added on 9/12/00 7:19PM
      {
      
      #Will change the value of the cell called $name
      
            $sheets = $this->ex->Application->Worksheets($sheet);
            $selcell = $sheets->Range($name);
            $selcell->activate;
            @$selcell->value = $value; #@: Some values produced variant not supported 9/13/00 2:49PM
            
       }
       
       
       function detectcode($sheet,$ext) # Added on 9/12/00 10:00PM
       {
       
             #To test the code for each file type.
       
             print "<br>Format for $ext is: ".$this->ex->application->ActiveWorkbook->FileFormat;      
             
       }
/*
      function search($wkb,$sheet,$range,$value) #Doesn't work9/29/00 1:00PM
       {
             $sheets = $this->ex->Application->Worksheets($sheet);
             #$rangesel = $sheets->Range("A1");
             $i=1;
                   print empty($rangesel->Find($value));
             while ($i<2)
             {
                   $rangesel = $sheets->Range("A$i:A$i");
                   if (empty($rangesel->Find($value)))
                   {
                         print "$gotit->row <BR>";
                         print "$gotit->column <BR>";
                   }else{
                         print "Non object";
                   }
                   
                         $i++;
             }      
                   
             return ;
             
       }
*/       

} /* end of Excel class */

?>

0
 
LVL 16

Author Comment

by:ellandrd
ID: 17957570
Mmmm looks good - have you tested this?

Ellandrd
0
 
LVL 10

Expert Comment

by:dennis_maeder
ID: 17957621
Well he certainly didn't write it in less than 10 minutes!
:D
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 3

Expert Comment

by:incrediblejohn
ID: 17957894
<you wrote>Mmmm looks good - have you tested this?</wrote>

nope
I didn't write it. Just thought I would send you what I had in my library. I'm suprised I even found something for you. Take it or leave it, I'm going back to work. Have a nice day.
0
 
LVL 14

Accepted Solution

by:
Aamir Saeed earned 250 total points
ID: 17960010
Have you looked into using the Excel file reader proposed package that's in PEAR?
0
 
LVL 16

Author Comment

by:ellandrd
ID: 17963432
i_m_aamir

yes, thats what i was using before i decided to ask this question.  that package is OK, but it dont allow me retreive the worksheet name of a excel file - this package is only for generating excel files.  it wont allow you specify a excel file, read its contents, manipulate it and write it back to the excel file....

incrediblejohn

>>Take it or leave it, I'm going back to work. Have a nice day.

all i asked was - was it tested? no need to get sour at anybody!  since you said you got this from your library, where did it orignate from so i can get the documentation as im not sure how i use it or what all the functions are for as it looks like the snippets code you posted was customised to what you were using it for...

bascially i need a break down of what each function does and outputs... ;-)

Ellandrd
0
 
LVL 16

Author Comment

by:ellandrd
ID: 17963529
by any chance you did get your library code from : http://www.phpclasses.org ??
0
 
LVL 16

Author Comment

by:ellandrd
ID: 17963574
ok - small bit of trouble - i found the docs for the above code and it will only work for PHP4.x...  im running PHP 5.1.4 on my server & PHP6 on my laptop...
0
 
LVL 16

Author Comment

by:ellandrd
ID: 17965311
thanks i ended up hacking away at the PHP classes from the PEAR resources and got a "kinda solution" working.
0
 
LVL 14

Expert Comment

by:Aamir Saeed
ID: 17965638
ellandrd: Thanks for Grade.
Cheers!
0
 
LVL 3

Expert Comment

by:incrediblejohn
ID: 17968285
I meant no hostility in saying I was going back to work. I just was. At the very top of the script was the information on who built it. I'm glad you found a solution. Excel automation can cause tremendous hair loss. Any chance you could post your solution so I could put it in my library of code? Who knows, 4 years from now someone else might need it. . . . . . .   :-)

<?php
/* Class for use with PHP4 scripts only*/


/*
 * This is an Excel class to create,load,read,write,save and use some of the internal
 * functionalities of workbooks and sheets.
 * Tested with Windows 98 - MS Office 2000
 * Apache 1.3.9 PHP4.02 Running as CGI
 * (c) Alain M. Samoun 09/2000.
 * alain@sonic.net
 * Gnu GPL code (see www.fsf.org for more information).
 */
 
 
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question