import-from-excel how - Reading an Excel file in PHP




code data (6)

I'm trying to read an Excel file (Office 2003). There is an Excel file that needs to be uploaded and its contents parsed.

Via Google, I can only find answers to these related (and insufficient topics): generating Excel files, reading Excel XML files, reading Excel CSV files, or incomplete abandoned projects. I own Office 2003 so if I need any files from there, they are available. It's installed on my box but isn't and can't be installed on my shared host.

Edit: so far all answers point to PHP-ExcelReader and/or this additional article about how to use it.


Answers

I use PHP-ExcelReader to read xls files, and works great.


There is a great article to explain how to read/write excel files through php code, They have been recommend to use MS-Excel Stream Handler PHP class, which is one of the top class library for that :)


You have 2 choice as far as I know:

  1. Spreadsheet_Excel_Reader, which knows the Office 2003 binary format
  2. PHPExcel, which knows both Office 2003 as well as Excel 2007 (XML).

PHPExcel uses Spreadsheet_Excel_Reader for the Office 2003 format.

Update: I once had to read some Excel files but I used the Office 2003 XML format in order to read them and told the people that were using the application to save and upload only that type of Excel file.


Try this...

I have used following code to read "xls and xlsx"

    <?php
    include 'excel_reader.php';       // include the class
    $excel = new PhpExcelReader;      // creates object instance of the class
    $excel->read('excel_file.xls');   // reads and stores the excel file data

    // Test to see the excel data stored in $sheets property
    echo '<pre>';
    var_export($excel->sheets);

    echo '</pre>';

    or 

 echo '<pre>';
    print_r($excel->sheets);

    echo '</pre>';

Reference:http://coursesweb.net/php-mysql/read-excel-file-data-php_pc


It depends on how you want to use the data in the excel file. If you want to import it into mysql, you could simply save it as a CSV formatted file and then use fgetcsv to parse it.


Some points to note when working with foreach():

a) foreach works on the prospected copy of the original array. It means foreach() will have SHARED data storage until or unless a prospected copy is not created foreach Notes/User comments.

b) What triggers a prospected copy? Prospected copy is created based on the policy of copy-on-write, that is, whenever an array passed to foreach() is changed, a clone of original array is created.

c) The original array and foreach() iterator will have DISTINCT SENTINEL VARIABLES, that is, one for the original array and other for foreach; see the test code below. SPL , Iterators, and Array Iterator.

Stack Overflow question How to make sure the value is reset in a 'foreach' loop in PHP? addresses the cases (3,4,5) of your question.

The following example show that each() and reset() DOES NOT affect SENTINEL variables (for example, the current index variable) of the foreach() iterator.

$array = array(1, 2, 3, 4, 5);

list($key2, $val2) = each($array);
echo "each() Original (outside): $key2 => $val2<br/>";

foreach($array as $key => $val){
    echo "foreach: $key => $val<br/>";

    list($key2,$val2) = each($array);
    echo "each() Original(inside): $key2 => $val2<br/>";

    echo "--------Iteration--------<br/>";
    if ($key == 3){
        echo "Resetting original array pointer<br/>";
        reset($array);
    }
}

list($key2, $val2) = each($array);
echo "each() Original (outside): $key2 => $val2<br/>";

Output:

each() Original (outside): 0 => 1
foreach: 0 => 1
each() Original(inside): 1 => 2
--------Iteration--------
foreach: 1 => 2
each() Original(inside): 2 => 3
--------Iteration--------
foreach: 2 => 3
each() Original(inside): 3 => 4
--------Iteration--------
foreach: 3 => 4
each() Original(inside): 4 => 5
--------Iteration--------
Resetting original array pointer
foreach: 4 => 5
each() Original(inside): 0=>1
--------Iteration--------
each() Original (outside): 1 => 2




php import-from-excel