Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to parse and Excel file in PHP

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 306
    Comment on it

    Hello Reader! If you want to read a Excel file in PHP then you can learn the library code below

    read('excel_file.xls'); // reads and stores the excel file data

    // Test to see the excel data stored in $sheets property
    
    var_export($excel->sheets);
    
    or 
    
    print_r($excel->sheets);
    

    The $sheets property will store this array:

    array (
     0 => array (
      'maxrow' => 0,
      'maxcol' => 0,
      'numRows' => 8,
      'numCols' => 4,
      'cells' => array (
        2 => array ( 2 => 'Web sites data' ),
         4 => array (
          1 => 'Title',
          2 => 'Url',
          3 => 'Visitors',
          4 => 'Accesses'
        ),
        5 => array (
         1 => 'Web Programming Courses',
         2 => 'http://coursesweb.net/',
         3 => '5000',
         4 => '9800'
        ),
        6 => array (
         1 => 'Courses Games and Anime',
         2 => 'http://www.marplo.net/',
         3 => '6000',
         4 => '22000'
        ),
        7 => array (
         1 => 'PHP: Hypertext Processor',
         2 => 'http://php.net/',
         3 => '30000',
         4 => '92000'
        ),
        8 => array (
         1 => 'Yahoo!',
         2 => 'http://yahoo.com/',
         3 => '100000',
         4 => '650000'
        ),
       ),
       'cellsInfo' =>  array (
         5 => array (
          3 => array (
          'raw' => 5000,
          'type' => 'unknown'
         ),
         4 => array (
          'raw' => 9800,
          'type' => 'unknown'
         ),
        ),
        6 => array (
         3 => array (
          'raw' => 6000,
          'type' => 'unknown'
         ),
         4 => array (
          'raw' => 22000,
          'type' => 'unknown'
         ),
        ),
        7 => array (
         3 => array (
          'raw' => 30000,
          'type' => 'unknown'
         ),
         4 => array (
          'raw' => 92000,
          'type' => 'unknown'
         ),
        ),
        8 => array (
         3 => array (
          'raw' => 100000,
          'type' => 'unknown'
         ),
         4 => array (
          'raw' => 650000,
          'type' => 'unknown'
         ),
        ),
        2 => array (
         2 => array ( 'colspan' => 3 ),
        ),
       ),
     )
    )
    

    The data is stored in 'cells' and the meta-data is stored in an array called 'cellsInfo'.

    $sheets[index]  -->  'cells'  -->  row --> column --> Interpreted value
                 -->  'cellsInfo' --> row --> column --> 'type' (Can be 'date', 'number', or 'unknown')
                                                    --> 'raw' (The raw data that Excel stores for that data cell)
    

    Here is an example that creates and outputs HTML table with excel data from each sheet.

    <?php
    include 'excel_reader.php';     // include the class
    
    // creates an object instance of the class, and read the excel file data
    $excel = new PHPExcelParser;
    $excel->read('test.xls');
    
    // this function creates and returns a HTML table with excel rows and columns data
    // Parameter - array with excel worksheet data
    function sheetData($sheet) {
      $re = '<table>';     // starts html table
    
      $x = 1;
      while($x <= $sheet['numRows']) {
        $re .= "<tr>\n";
        $y = 1;
        while($y <= $sheet['numCols']) {
          $cell = isset($sheet['cells'][$x][$y]) ? $sheet['cells'][$x][$y] : '';
          $re .= " <td>$cell</td>\n";  
          $y++;
        }  
        $re .= "</tr>\n";
        $x++;
      }
    
      return $re .'</table>';     // ends and returns the html table
    }
    
    $nr_sheets = count($excel->sheets);       // gets the number of worksheets
    $excel_data = '';              // to store the the html tables with data of each sheet
    
    // traverses the number of sheets and sets html table with each sheet data in $excel_data
    for($i=0; $i<$nr_sheets; $i++) {
      $excel_data .= '<h4>Sheet '. ($i + 1) .' (<em>'. $excel->boundsheets[$i]['name'] .'</em>)</h4>'. sheetData($excel->sheets[$i]) .'<br/>';  
    }
    
    echo $excel_data;      // outputs HTML tables with excel file data
    

    Sourcr : coursesweb

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: