PHP_XLSXWriter and Code Igniter Corrupted Output Excel File

Question

I'm trying to integrate [PHP_XLSXWriter] (https://github.com/mk-j/PHP_XLSXWriter) with Code Igniter

Here's my controller source code

public function ToExcel(){
    include_once APPPATH.'/third_party/xlsxwriter.class.php';
    $filename = "report-".date('d-m-Y-H-i-s').".xlsx";
    header('Content-disposition: attachment; filename="'.XLSXWriter::sanitize_filename($filename).'"');
    header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    header('Content-Transfer-Encoding: binary');
    header('Cache-Control: must-revalidate');
    header('Pragma: public'); 

    $styles = array('widths'=>[3,20,30,40], 'font'=>'Arial','font-size'=>10,'font-style'=>'bold', 'fill'=>'#eee', 'halign'=>'center', 'border'=>'left,right,top,bottom');
    $styles2 = array( ['font'=>'Arial','font-size'=>10,'font-style'=>'bold', 'fill'=>'#eee', 'halign'=>'left', 'border'=>'left,right,top,bottom','fill'=>'#ffc'],['fill'=>'#fcf'],['fill'=>'#ccf'],['fill'=>'#cff'],);

    $header = array(
    'No 1'=>'string',
    'No 2'=>'string',
    'No 3'=>'string',
    'No 4'=>'string',
    );

    $writer = new XLSXWriter();
    $writer->setAuthor('Human');
    $writer->writeSheetHeader('Sheet1', $header, $styles);
    for($no=1;$no<=10;$no++){
        $writer->writeSheetRow('Sheet1', [$no, $no, $no, $no], $styles2);
    }
    $writer->writeToStdOut();   
}

The Excel file are generated and downloaded successfully, but when I try to open it using Ms Excel, it says that the file was corrupted. The problem is, it turned out that there's empty single line at the source of the generated Excel file

When I delete that empty line, it can be opened without any problem

And also, if I copy that controller code to single php file (without Code Igniter involved), the script and generated Excel file worked like a charm

How do I get rid of that first empty line?

Many thanks for the help

Answer 1

Если вы делали AJAX запрос:

$filename = "example.xlsx";
header('Content-disposition: attachment; filename="'.XLSXWriter::sanitize_filename($filename).'"');
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Pragma: public');

$rows = array(
    array('2003','1','-50.5','2010-01-01 23:00:00','2012-12-31 23:00:00'),
    array('2003','=B1', '23.5','2010-01-01 00:00:00','2012-12-31 00:00:00'),
);

$writer = new XLSXWriter();
$writer->setAuthor('Some Author'); 

//$writer->writeToFile('example.xlsx');
//echo $writer->writeToString();
// создаём файл
ob_start();
foreach($rows as $row)
    $writer->writeSheetRow('Sheet1', $row);
$writer->writeToStdOut();
$xlsData = ob_get_contents();
ob_end_clean();
$response =  array(
    'status' => TRUE,
    'file' => "data:application/vnd.ms-excel;base64,".base64_encode($xlsData)
);

die(json_encode($response));