Reading an Excel file in PHP

Question

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.

Ionut_G_Stan_58808 · Answer

You have 2 choices as far as I know:

Spreadsheet_Excel_Reader, which knows the Office 2003 binary format
PHPExcel, which knows both Office 2003 as well as Excel 2007 (XML). (Follow the link, and you'll see they upgraded this library to PHPSpreadSheet)

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.

Luis_Melgratti_17032 · Answer

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

user_594867 · Answer

Read XLSX (Excel 2003+)
https://github.com/shuchkin/simplexlsx

if ( $xlsx = SimpleXLSX::parse('book.xlsx') ) {
    print_r( $xlsx->rows() );
} else {
    echo SimpleXLSX::parseError();
}

Output

Array (
    [0] => Array
        (
            [0] => ISBN
            [1] => title
            [2] => author
            [3] => publisher
            [4] => ctry
        )
    [1] => Array
        (
            [0] => 618260307
            [1] => The Hobbit
            [2] => J. R. R. Tolkien
            [3] => Houghton Mifflin
            [4] => USA
       )

)

Read XLS (Excel 97-2003)
https://github.com/shuchkin/simplexls

if ( $xls = SimpleXLS::parse('book.xls') ) {
    print_r( $xls->rows() );
} else {
    echo SimpleXLS::parseError();
}

CSV php reader
https://github.com/shuchkin/simplecsv

if ( $csv = SimpleCSV::import('book.csv') ) {
    print_r( $csv );
}

user_10552968 · Answer

I'm using below excel file url: https://github.com/inventorbala/Sample-Excel-files/blob/master/sample-excel-files.xlsx

Output:

user_10552968 · Answer

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

    include 'PHPExcel/IOFactory.php';

    $location='sample-excel-files.xlsx';

    $objPHPExcel = PHPExcel_IOFactory::load($location);
    $sheet = $objPHPExcel->getSheet(0);
    $total_rows = $sheet->getHighestRow();
    $total_columns = $sheet->getHighestColumn();
    $set_excel_query_all=array();
    for($row =2; $row <= $total_rows; $row++) {
        $singlerow = $sheet->rangeToArray('A' . $row . ':' . $total_columns . $row, NULL, TRUE, FALSE);
        $single_row=$singlerow[0];
        $set_excel_query['store_id']=$single_row[0];
        $set_excel_query['employee_uid']=$single_row[1];
        $set_excel_query['opus_id']=$single_row[2];
        $set_excel_query['item_description']=$single_row[3];
        if($single_row[4])
        {
            $set_excel_query['opus_transaction_date']= date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($single_row[4]));
        }
        $set_excel_query['opus_transaction_num']=$single_row[5];
        $set_excel_query['opus_invoice_num']=$single_row[6];
        $set_excel_query['customer_name']=$single_row[7];
        $set_excel_query['mobile_num']=$single_row[8];
        $set_excel_query['opus_amount']=$single_row[9];
        $set_excel_query['rq4_amount']=$single_row[10];
        $set_excel_query['difference']=$single_row[11];
        $set_excel_query['ocomment']=$single_row[12];
        $set_excel_query['mark_delete']=$single_row[13];
        if($single_row[14])
        {
            $set_excel_query['upload_date']= date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($single_row[14]));
        }
        $set_excel_query_all[]=$set_excel_query;
    }

   print_r($set_excel_query_all);

Reading an Excel file in PHP

8 Answers