How to upload a large Excel file using PHPExcel library?

Question

When I'm uploading excel file using php in database(phpMySql), I'm getting error as

Error loading file "": Could not open for reading! File does not exist

and there are almost more than 40000 rows in Excel. If I'm uploading the excel file which has 400-1000 rows then it's working.

I'm using PHPExcel library.

Here is my code :

<html>
    <head>
        <title>Import Excel</title>
        <style>
            table {
                border-collapse: collapse;
                width: 100%;
            }

            th, td {
                text-align: left;
                padding: 8px;
            }

            tr:nth-child(even) {background-color: #f2f2f2;}
    </style>

    </head>

    <?php
        require 'Classes/PHPExcel/IOFactory.php';

        $servername = "localhost";
        $username = "root";
        $password = "";
        $dbname = "office";

        if(isset($_POST['upload'])){
            $inputfilename = $_FILES['file']['tmp_name'];
            $exceldata = array();

            $conn = mysqli_connect($servername, $username, $password, $dbname);

            if(!$conn){
                die("Connection Failed: " . mysqli_connect_error());
            }

            try {
                $inputfiletype = PHPExcel_IOFactory::identify($inputfilename);
                $objReader = PHPExcel_IOFactory::createReader($inputfiletype);
                $objPHPExcel = $objReader->load($inputfilename);
            } catch(Exception $e){
                die('Error loading file "'.pathinfo($inputfilename,PATHINFO_BASENAME).'": '.$e->getMessage());
            }

        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();

        for($row = 2; $row <= $highestRow; $row++) {
            $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);

            $sql = "INSERT INTO allsec (emp_code, emp_full_name, request_type, leave_status, leave_from, leave_to, days, year, month, pl, pm, lpm, status, emp_name, wg, leave_type)
        VALUES ('".$rowData[0][0]."', '".$rowData[0][1]."', '".$rowData[0][2]."', '".$rowData[0][3]."', '".$rowData[0][4]."', '".$rowData[0][5]."', '".$rowData[0][6]."', '".$rowData[0][7]."', '".$rowData[0][8]."', '".$rowData[0][9]."', '".$rowData[0][10]."', '".$rowData[0][11]."', '".$rowData[0][12]."', '".$rowData[0][13]."', '".$rowData[0][14]."', '".$rowData[0][15]."')";

            if(mysqli_query($conn, $sql)){
                $exceldata[] = $rowData[0];
            } else{
                echo "Error: " .$sql . "<br>" . mysqli_error($conn);
            }

        }


        echo "<table border='1'>";
        foreach($exceldata as $index => $excelraw){
            echo "<tr>";
            foreach($excelraw as $excelcolumn){
                echo "<td>".$excelcolumn."</td>";
            }
            echo "</tr>";
        }
        echo "</table>";

        mysqli_close($conn);

    }
    ?>
    <body>
        <form action="" method="POST" enctype="multipart/form-data">
                <input type="file" name="file" >
                <input type="submit" name ="upload" value="upload">
        </form> 
    </body>
</html>

Try changing the php.ini settings as Pankaj says. If you then have errors regarding memory, consider using the currently active project - phpSpreadsheet. — Slinky Sloth, Feb 06 '19 at 07:35

score 1 · Answer 1 · answered Feb 06 '19 at 04:03

If the file size is too large, then you need to modify the php.ini file. By default, you can only upload a file of size 8MB. Try changing the following values to 40 M and re-start your server.

; Maximum allowed size for uploaded files. upload_max_filesize = 40M

; Must be greater than or equal to upload_max_filesize post_max_size = 40M

Another reason could be that your data (40000 excel rows) is taking more time to execute than the maximum time allocated for execution of a script on server. To change the default maximum time, you need to change the max_input_time setting on Apache server (search for similar setting if you are using a different server).

This link will help you to change the server timeout settings- https://stackoverflow.com/a/8744184/10602679

Additionally, PHPExcel also provides configuration settings for caching cell data, reducing its memory footprint, but at a cost in speed — Mark Baker, Feb 07 '19 at 18:46

How to upload a large Excel file using PHPExcel library?

1 Answers1