CODEWITHSUNDEEP
phpexcelphpspreadsheet
Romain Dub
Romain Dub

Reputation: 31

How to know why an .xls file generated by phpSpreadsheet is corrupted?

I wrote a PHP script that generates XLs files from SQL queries on MariaDB using PhpSpreadSheet. It works really fine most of the time, but I've got issues with my biggest extract: Excel tells me (when I try to open the files) that it is "corrupted". If I skip the alert and open it, all the expected rows are in the file (my Mac users can't open it at all).

Here are the results of my investigations and observations: - for one given query, I can set a SQL "LIMIT" (max number of rows) to have a non-corrupted file again. For one given query, this LIMIT between ok and not-corrupted and corrupted files will always be the same number. - for one given query, this "LIMIT" between corrupted and not corrupted files will be pretty much the same whether the IDs are sort ASCendig or DESCending (in the SQL query. This way, suppose it's not a specific character in one row that breaks the file. This conclusion is validated by the fact that if I exclude the rows around this limit, the problem remains. However, if I replace each value to be written in the cells of the XLs file by a big random string ("abcdefghijklm", slightly bigger than the average length of each cell from my request), the problem disappears.

I'm using PHP V7.0.33 (memory_limit 1024M) / Ubuntu16.04.1 / MariaDB. There is no memory limit warning in the Apache2/log/error.log (no error at all) 

<?php  

    //Initialization
    require '/var/www/html/vendor/autoload.php';    
    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xls;
    ob_clean();

    //Getting data from DB
    $connect = mysqli_connect("localhost", "user", "pass", "base","port");
    $query = "SELECT * FROM ... WHERE ...";
    $result = mysqli_query($connect, $query);
    $filename="...";

    //If data exist
    if(mysqli_num_rows($result) > 0){

        $spreadsheet = new Spreadsheet();  /*----Spreadsheet object-----*/
        $Excel_writer = new Xls($spreadsheet);  /*----- Excel (Xls) Object*/
        $spreadsheet->setActiveSheetIndex(0);
        $activeSheet = $spreadsheet->getActiveSheet();

        $first = true;
        $irow=0;

        //Loop on each row        
        while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){
            //Headers
            if ($first) {
                $irow++;
                $icol=0;
                foreach (array_keys($row) as &$value) {
                    $icol++;
                    $activeSheet->setCellValueByColumnAndRow( $icol,$irow, $value );
                } 
                $first = false;
            }
            //DataBodyRange
            $irow++;
            $icol=0;
            foreach (($row) as &$value) {
                $icol++;
                $activeSheet->setCellValueByColumnAndRow( $icol,$irow, $value );
            }
        }

        //Finalizartion
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.'"'); 
        header('Cache-Control: max-age=0');
        $Excel_writer->save('php://output');
    }        

?>

Upvotes: 3

Views: 923

Answers (1)

Charles Brengel
Charles Brengel

Reputation: 73

Romain Dub, I had pretty much the same problem. Mine was slightly reversed. I wanted an xlsx but kept getting the error as you were. After my spreadsheet was created, I changed the extension to xls and I got the spreadsheet to open and when it opened, I found lines in the spreadsheet about a couple of undefined variables. If you need to resolve your error, possibly check your code for undefined variables being inserted into your spreadsheet. Just an idea that may or may not be the solution to your problem.

Upvotes: 1

Related Questions