Solved

php excel cvs

Posted on 2006-11-16
11
418 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
  • 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
The viewer will learn how to count occurrences of each item in an array.
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now