PHPExcel not working perfectly

Question

I use PHPExcel library from here to create an excel file extension .xlsx for that i use php and mysql the data of this excel file is from mysql database.

After i create the code i receive this message:

Excel can't open the file 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

To solve this problem i use:

ob_start(); ob_end_clean();

After that i download the file and try to open the excel file i have this message:

We found a problem with some content in your file.Do you want to us to try to recover as much as we can?if you trust the source of this workbook,click yes.

When i click yes i have this message:

Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.

I click close the excel file open but inside this excel file i have some empty rows with my data.

This is my code to download an excel file using PHPExcel

ob_start();
include("includes/connect.php");
require_once'PHPExcel/Classes/PHPExcel.php';
//Create PHPExcel object
$excel=new PHPExcel();
//selecting active sheet
$excel->setActiveSheetIndex(0);
$row=21;
while($data=mysqli_fetch_object($query)){
$excel->getActiveSheet()->setCellvalue('A'.$row,$data->db_maid)
    ->setCellvalue('B'.$row,$data->db_date) 
    ->setCellvalue('C'.$row,$data->db_client)
    ->setCellvalue('D'.$row,$data->db_esid)
    ->setCellvalue('E'.$row,$data->db_type)    
    ->setCellvalue('F'.$row,$data->db_phone)
    ->setCellvalue('G'.$row,$data->db_mobile) 
    ->setCellvalue('H'.$row,$data->db_contactperson)
    ->setCellvalue('I'.$row,$data->db_competetivecompany)
    ->setCellvalue('J'.$row,$data->db_category)    
    ->setCellvalue('K'.$row,$data->db_process)
    ->setCellvalue('L'.$row,$data->db_status) 
    ->setCellvalue('M'.$row,$data->db_rate) 
    ->setCellvalue('N'.$row,$data->db_doc)
    ->setCellvalue('O'.$row,$data->nextDate)
    ->setCellvalue('P'.$row,$data->db_ndstatus)
    ->setCellvalue('Q'.$row,$data->db_pnote) 
    ->setCellvalue('R'.$row,$data->meetingStatus) 
    ->setCellvalue('S'.$row,$data->db_ncam)    
    ->setCellvalue('T'.$row,$data->medit)
    ->setCellvalue('U'.$row,$data->name);
    //incriment the row
$row++;    
}
//set column width
$excel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
$excel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('Q')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('R')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('S')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('T')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('U')->setWidth(20);
//make table headers
$excel->getActiveSheet()
      ->setCellValue('A1','List Of Marketing')
      ->setCellValue('A3','#')
      ->setCellValue('B3','Date')
      ->setCellValue('C3','Client')
      ->setCellValue('D3','ES')
      ->setCellValue('E3','Type')
      ->setCellValue('F3','Phone')
      ->setCellValue('G3','Mobile')
      ->setCellValue('H3','Contact Person')
      ->setCellValue('I3','Competetive Company')
      ->setCellValue('J3','Categoty')
      ->setCellValue('K3','Process')
      ->setCellValue('L3','Status')
      ->setCellValue('M3','Rate')
      ->setCellValue('N3','Date Of Calling')
      ->setCellValue('O3','Next Date')
      ->setCellValue('P3','Next Date Status')
      ->setCellValue('K3','Phone Note')
      ->setCellValue('R3','Meeting Status')
      ->setCellValue('S3','Next Call After Meeting')
      ->setCellValue('T3','Edit Date')
      ->setCellValue('U3','Staff');
//Margin The title
$excel->getActiveSheet()->mergeCells('A1:U1');
//aligning
$excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('center');
//styling
$excel->getActiveSheet()->getStyle('A1')->applyFromArray(
array(
      'font'=>array(
          'size'=>24,
      )
)
);
$excel->getActiveSheet()->getStyle('A3:U3')->applyFromArray(
array(
   'font'=>array(
       'bold'=>true
   ),
    'borders'=>array(
        'allborders'=>array(
            'style'=>PHPExcel_Style_Border::BORDER_THIN
        )
    )
)
);
//give border to data
$excel->getActiveSheet()->getStyle('A4:U'.($row-1))->applyFromArray(
array(
    'borders'=>array(
        'outline'=>array(
            'style'=>PHPExcel_Style_Border::BORDER_THIN
        ),
        'vertical'=>array(
            'style'=>PHPExcel_Style_Border::BORDER_THIN
        )
    )
)
);
//write the result to a file
$file=PHPExcel_IOFactory::createWriter($excel,'Excel2007');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="marketing.xlsx"');
header('Cache-Control:max-age=0');
ob_end_clean();

//output to php output instead of filename
$file->save('php://output');

My question is How to solve this problem i don't want this message appear also i don't want to have an empty rows in my excel also ob_end_clean() is a real solution for the first message because i saw a tutorial on the internet and his code work correctly without using the ob_end_clean() or the ob_start()?

Answer 1

Try removing

break;

in libraries/PHPExcel/Calculation/Functions.php (line 1161)

in public static function TYPE($value = NULL){}

in elseif(is_array($value)) {}

It solved my problem.

Answer 2

You're doing it the other way around. The only part you need to get into the buffer is $file->save("php://output");. If for any reason simply exiting after saving to output is not enough for you, delete the first ob_start(); and last line and try this:

ob_start();
$file->save("php://output");
$content = ob_get_contents();
ob_end_clean();
die($content);