How to read through each row of excel spreadsheet using SpreadsheetReader inPHP?

Question

I am using SpreadsheetReader to import excel spreadsheet to mysql server however its reading only the first row. What am I doing wrong in the code and how to make it read through all rows?

<?php
require_once('../spreadsheet-reader-master/php-excel-reader/excel_reader2.php');
require_once('../spreadsheet-reader-master/SpreadsheetReader.php');

if (isset($_POST["import"])) {

    if (!$db) {
        die("Connection failed: " . mysqli_connect_error());
    }
    $device_type_id = $_POST['device_type_id'];
    $allowedFileType = ['application/vnd.ms-excel', 'text/xls', 'text/xlsx', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];


    if (in_array($_FILES["file"]["type"], $allowedFileType)) {

        $targetPath = '../upload/' . $_FILES['file']['name'];
        move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);

        $Reader = new SpreadsheetReader($targetPath);

        $sheetCount = count($Reader->sheets());
        error_reporting(E_ALL ^ E_NOTICE); //this hides all notices that php displays on the page
        for ($i = 0; $i < $sheetCount; $i++) {
            $Reader->ChangeSheet($i);

            foreach ($Reader as $Row) {

                $serial_imei = "";
                if (isset($Row[0])) {
                    $serial_imei = mysqli_real_escape_string($db, $Row[0]);
                }

                $serial_no = "";
                if (isset($Row[1])) {
                    $serial_no = mysqli_real_escape_string($db, $Row[1]);
                }

                if (empty($device_type_id)) {
                    echo "Please select a device type from the dropdown list!";
                }


                if (!empty($serial_no) || !empty($serial_imei) || !empty($device_type_id)) {

                    $query = "INSERT INTO 
                    devices (serial_imei, serial_no, type_id) 
                    VALUES ('$serial_imei', '$serial_no', '$device_type_id')";

                    if (mysqli_multi_query($db, $query)) {
                        echo "New records created successfully";
                    } else {
                        echo "Error: " . $query . "<br>" . mysqli_error($db);
                    }
                    mysqli_close($db);
                }
            }
        }
    } else {
        $type = "error";
        $message = "Invalid File Type. Upload Excel File.";
    }
}
?>

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Oct 30 '19 at 22:18

score 0 · Answer 1 · answered Oct 23 '19 at 11:22

Are you sure that the sheets are using numerical indexes? Looking at the code examples in the readme they use a foreach to access each row. I would try something like:

foreach ( $Reader->sheets() as $index => $name ) {
    $Reader->ChangeSheet( $index );

    foreach ( $Reader as $Row ) {

        $serial_imei = "";
        if ( isset( $Row[0] ) ) {
            $serial_imei = mysqli_real_escape_string( $db, $Row[0] );
        }

        $serial_no = "";
        if ( isset( $Row[1] ) ) {
            $serial_no = mysqli_real_escape_string( $db, $Row[1] );
        }

        if ( empty( $device_type_id ) ) {
            echo "Please select a device type from the dropdown list!";
        }


        if ( ! empty( $serial_no ) || ! empty( $serial_imei ) || ! empty( $device_type_id ) ) {

            $query = "INSERT INTO
                    devices (serial_imei, serial_no, type_id)
                    VALUES ('$serial_imei', '$serial_no', '$device_type_id')";

            if ( mysqli_multi_query( $db, $query ) ) {
                echo "New records created successfully";
            } else {
                echo "Error: " . $query . "<br>" . mysqli_error( $db );
            }
            mysqli_close( $db );
        }
    }
}

my mistake, I was doing mysqli_close($db); and thats why it cut the connection before continuing to the other row. — showtime, Oct 23 '19 at 11:31

How to read through each row of excel spreadsheet using SpreadsheetReader inPHP?

1 Answers1