Generating corrupted file.

[chandni21]

I use this library to format my excel report i.e.generated by FileMaker software. When I generate this formatted report for the first time in day , it download file with 1 KB. When I open the file , it shows below error.

Excel cannot open the 'test.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

$file=$drive.$_GET['file'];
    if(file_exists($file)) {
    //echo $file;
    //echo file_exists($file);exit;
    include 'phpexcel/Classes/PHPExcel.php';
    //--------------READING FROM EXCEL FILE-------------
        $objReader = PHPExcel_IOFactory::createReader('Excel2007');
        $objReader->setReadDataOnly(true);

        $objPHPExcel = $objReader->load($file);
        $objWorksheet = $objPHPExcel->getActiveSheet();

        $highestRow = $objWorksheet->getHighestRow(); 
        $highestColumn = $objWorksheet->getHighestColumn(); 

        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); 

        //--------------WRITING IN EXCEL FILE-------------
        try{
        // Create new PHPExcel object

        $str = str_replace(".xlsx", "",$file);
        $str = str_replace("excel/", "",$str);
        $filename = $_GET['file'];
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);

        $styleArray = array(
            'font'  => array(
                'bold'  => true,
                'color' => array('rgb' => 'FFFFFF'),
                'size'  => 10,
            ),
            'fill'  => array(
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'color' => array('rgb' => '00008B'),
            ));
        $objPHPExcel->getActiveSheet()->getStyle('A1:W1')->applyFromArray($styleArray);

        $objPHPExcel->getActiveSheet()->setCellValue('A1', "ID");
        $objPHPExcel->getActiveSheet()->setCellValue('B1', "TestID");
        $objPHPExcel->getActiveSheet()->setCellValue('C1', "TYPE");
        $objPHPExcel->getActiveSheet()->setCellValue('D1', "NAME");
        $objPHPExcel->getActiveSheet()->setCellValue('E1', "POSTCODE");
        $objPHPExcel->getActiveSheet()->setCellValue('F1', "CARD NUMBER");
        $objPHPExcel->getActiveSheet()->setCellValue('G1', "APP DATE");
        $objPHPExcel->getActiveSheet()->setCellValue('H1', "LATEST LOT");
        $objPHPExcel->getActiveSheet()->setCellValue('I1', "LETTER1");
        $objPHPExcel->getActiveSheet()->setCellValue('J1', "APT1");
        $objPHPExcel->getActiveSheet()->setCellValue('K1', "LETTER2");
        $objPHPExcel->getActiveSheet()->setCellValue('L1', "APT2");
        $objPHPExcel->getActiveSheet()->setCellValue('M1', "LETTER3");
        $objPHPExcel->getActiveSheet()->setCellValue('N1', "APT3");
        $objPHPExcel->getActiveSheet()->setCellValue('O1', "LAST SERVICE DATE");
        $objPHPExcel->getActiveSheet()->setCellValue('P1', "DAYS TILL NEXT SERVICE");
        $objPHPExcel->getActiveSheet()->setCellValue('Q1', "R DATE");
        $objPHPExcel->getActiveSheet()->setCellValue('R1', "R NOTES");
        $objPHPExcel->getActiveSheet()->setCellValue('S1', "V STATUS");
        $objPHPExcel->getActiveSheet()->setCellValue('T1', "S STATUS");
        $objPHPExcel->getActiveSheet()->setCellValue('U1', "ON CON");
        $objPHPExcel->getActiveSheet()->setCellValue('V1', "AS");

        $objPHPExcel->getActiveSheet()->setAutoFilter('A1:V1');
        $rows = array();

        for ($row = 2; $row <= $highestRow; ++$row) {
          for ($col = 0; $col <= $highestColumnIndex; ++$col) {
            $rows[$col] = ($objWorksheet->getCellByColumnAndRow($col, $row)->getValue());
          }
                    $j = 0;
                    $objPHPExcel->getActiveSheet()->setCellValue('A' . $row, (($rows[$j] != '') ? $rows[$j] : '' ));
                    $j++;
                    $objPHPExcel->getActiveSheet()->setCellValue('B' . $row, (($rows[$j] != '') ? $rows[$j] : '' ));
                    $j++;
                    $objPHPExcel->getActiveSheet()->setCellValue('C' . $row, (($rows[$j] != '') ? $rows[$j] : '' ));
                    $j++;
                    $objPHPExcel->getActiveSheet()->setCellValue('D' . $row, (($rows[$j] != '') ? $rows[$j] : '' ));
                    $j++;
                    $objPHPExcel->getActiveSheet()->setCellValue('E' . $row, (($rows[$j] != '') ? $rows[$j] : '' ));
                    $j++;
                    $objPHPExcel->getActiveSheet()->setCellValue('F' . $row, (($rows[$j] != '') ? $rows[$j] : '' ));
                    $j++;
                    //date formate changes
                    $date='';
                    if(!empty($rows[$j]))
                    {   $rows[$j] = date('d/m/Y', PHPExcel_Shared_Date::ExcelToPHP($rows[$j]));
                        $tmp = explode('/',$rows[$j]);
                        $date = PHPExcel_Shared_Date::FormattedPHPToExcel($tmp[2], $tmp[1], $tmp[0]);
                    }
                    $objPHPExcel->getActiveSheet()->setCellValue('G' . $row, (($date != '') ? $date : '' ));
                    $j++;
                    $objPHPExcel->getActiveSheet()->setCellValue('H' . $row, (($rows[$j] != '') ? $rows[$j] : '' ));
                    $j++;
                    //date formate changes
                    $date='';
                    if(!empty($rows[$j]))
                    {   $rows[$j] = date('d/m/Y', PHPExcel_Shared_Date::ExcelToPHP($rows[$j]));
                        $tmp = explode('/',$rows[$j]);
                        $date = PHPExcel_Shared_Date::FormattedPHPToExcel($tmp[2], $tmp[1], $tmp[0]);
                    }
                    $objPHPExcel->getActiveSheet()->setCellValue('I' . $row, (($date != '') ? $date : '' ));
                    $j++;
                    //date formate changes
                    $date='';
                    if(!empty($rows[$j]))
                    {   $rows[$j] = date('d/m/Y', PHPExcel_Shared_Date::ExcelToPHP($rows[$j]));
                        $tmp = explode('/',$rows[$j]);
                        $date = PHPExcel_Shared_Date::FormattedPHPToExcel($tmp[2], $tmp[1], $tmp[0]);
                    }
                    $objPHPExcel->getActiveSheet()->setCellValue('J' . $row, (($date != '') ? $date : '' ));
                    $j++;
                    //date formate changes
                    $date='';
                    if(!empty($rows[$j]))
                    {   $rows[$j] = date('d/m/Y', PHPExcel_Shared_Date::ExcelToPHP($rows[$j]));
                        $tmp = explode('/',$rows[$j]);
                        $date = PHPExcel_Shared_Date::FormattedPHPToExcel($tmp[2], $tmp[1], $tmp[0]);
                    }
                    $objPHPExcel->getActiveSheet()->setCellValue('K' . $row, (($date != '') ? $date : '' ));
                    $j++;
                    //date formate changes
                    $date='';
                    if(!empty($rows[$j]))
                    {   $rows[$j] = date('d/m/Y', PHPExcel_Shared_Date::ExcelToPHP($rows[$j]));
                        $tmp = explode('/',$rows[$j]);
                        $date = PHPExcel_Shared_Date::FormattedPHPToExcel($tmp[2], $tmp[1], $tmp[0]);
                    }
                    $objPHPExcel->getActiveSheet()->setCellValue('L' . $row, (($date != '') ? $date : '' ));
                    $j++;
                    //date formate changes
                    $date='';
                    if(!empty($rows[$j]))
                    {   $rows[$j] = date('d/m/Y', PHPExcel_Shared_Date::ExcelToPHP($rows[$j]));
                        $tmp = explode('/',$rows[$j]);
                        $date = PHPExcel_Shared_Date::FormattedPHPToExcel($tmp[2], $tmp[1], $tmp[0]);
                    }
                    $objPHPExcel->getActiveSheet()->setCellValue('M' . $row, (($date != '') ? $date : '' ));
                    $j++;
                    //date formate changes
                    $date='';
                    if(!empty($rows[$j]))
                    {   $rows[$j] = date('d/m/Y', PHPExcel_Shared_Date::ExcelToPHP($rows[$j]));
                        $tmp = explode('/',$rows[$j]);
                        $date = PHPExcel_Shared_Date::FormattedPHPToExcel($tmp[2], $tmp[1], $tmp[0]);
                    }
                    $objPHPExcel->getActiveSheet()->setCellValue('N' . $row, (($date != '') ? $date : '' ));
                    $j++;
                    //date formate changes
                    $date='';
                    if(!empty($rows[$j]))
                    {   $rows[$j] = date('d/m/Y', PHPExcel_Shared_Date::ExcelToPHP($rows[$j]));
                        $tmp = explode('/',$rows[$j]);
                        $date = PHPExcel_Shared_Date::FormattedPHPToExcel($tmp[2], $tmp[1], $tmp[0]);
                    }
                    $objPHPExcel->getActiveSheet()->setCellValue('O' . $row, (($date != '') ? $date : '' ));
                    $j++;
                    $objPHPExcel->getActiveSheet()->setCellValue('P' . $row, (($rows[$j] != '') ? $rows[$j] : '' ));
                    $j++;
                    //date formate changes
                    $date='';
                    if(!empty($rows[$j]))
                    {   $rows[$j] = date('d/m/Y', PHPExcel_Shared_Date::ExcelToPHP($rows[$j]));
                        $tmp = explode('/',$rows[$j]);
                        $date = PHPExcel_Shared_Date::FormattedPHPToExcel($tmp[2], $tmp[1], $tmp[0]);
                    }
                    $objPHPExcel->getActiveSheet()->setCellValue('Q' . $row, (($date != '') ? $date : '' ));
                    $j++;
                    $objPHPExcel->getActiveSheet()->setCellValue('R' . $row, (($rows[$j] != '') ? $rows[$j] : '' ));
                    $j++;
                    $objPHPExcel->getActiveSheet()->setCellValue('S' . $row, (($rows[$j] != '') ? $rows[$j] : '' ));
                    $j++;
                    $objPHPExcel->getActiveSheet()->setCellValue('T' . $row, (($rows[$j] != '') ? $rows[$j] : '' ));
                    $j++;
                    $objPHPExcel->getActiveSheet()->setCellValue('U' . $row, (($rows[$j] != '') ? $rows[$j] : '' ));
                    $j++;
                    $objPHPExcel->getActiveSheet()->setCellValue('V' . $row, (($rows[$j] != '') ? $rows[$j] : '' ));
                    $j++;
                    //$objPHPExcel->getActiveSheet()->setCellValue('W' . $row, (($rows[$j] != '') ? $rows[$j] : '' ));
                    //$j++;

        }
        for ($row = 2; $row <= $highestRow; ++$row) {

            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(6, $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(8, $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(9, $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(10, $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(11, $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(12, $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(13, $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(14, $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(16, $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);

        }
        foreach (range('A', $objPHPExcel->getActiveSheet()->getHighestDataColumn()) as $col) {
                $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
        } 
        $objPHPExcel->getActiveSheet()->calculateColumnWidths();
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
        sleep(2);
        header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        header('Content-Disposition: attachment; filename="'.$filename.'"');
        header("Cache-Control: max-age=0");
        ob_clean();
        $objWriter->save("php://output");

        }catch(Exception $e){
                $class->create_errorlog($e); 
        }
    }

[PowerKiKi]

Please provide a Minimal, Complete, and Verifiable example of code that exhibits this issue without relying on an external file. This look far from minimal code to trigger the issue.

[chandni21]

Well, It's not like I have copy paste whole code. As there are many calculation on the column ,, Is that the reason downloading 1 KB file or what ?

[nslocomotives]

@chandni21 your probelm may be: $objPHPExcel->getActiveSheet()->setCellValue('A1', "ID") try changing it to lowercase "id". $objPHPExcel->getActiveSheet()->setCellValue('A1', "id")

this is the case for CSV i don't know if its the same in this case but worth a try i think.. https://support.microsoft.com/en-us/help/323626/-sylk-file-format-is-not-valid-error-message-when-you-open-file

[yedort]

I had the same issue. Maybe it helps you.

I already had the htmlspecialchars_decode and the strip_tags functions wrapped around the string I wanted to write to the document, then added the trim function and the problem was solved.