PHP create xls from array

Question

I try to create xls file from array and download it with the browser with this code:

$sheet = array(
    array(
      'a1 data',
      'b1 data',
      'c1 data',
      'd1 data',
    )
  );

  $doc = new PHPExcel();
  $doc->getActiveSheet()->fromArray($sheet, null, 'A1');

  header('Content-Type: application/vnd.ms-excel');
  header('Content-Disposition: attachment;filename="your_name.xls"');
  header('Cache-Control: max-age=0');

  // Do your stuff here

  $writer = PHPExcel_IOFactory::createWriter($doc, 'Excel5');

The problem is that i get a empty file.Any idea what can be the issue?

It looks like you're missing a line of code: you're creating an object $writer, but then not actually doing anything with it. — IMSoP
– IMSoP, Commented Apr 28, 2014 at 18:25

Anbuselvan Annamalai · Accepted Answer · 2017-10-15 00:01:21Z

24

Please try : As per official documentation, you first need to save the file with the object writer

Please let me know if this is what you wanted

<?php
date_default_timezone_set('America/Los_Angeles');

require_once('PHPExcel.php');

$sheet = array(
    array(
      'a1 data',
      'b1 data',
      'c1 data',
      'd1 data',
    )
  );

  $doc = new PHPExcel();
  $doc->setActiveSheetIndex(0);

  $doc->getActiveSheet()->fromArray($sheet, null, 'A1');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="your_name.xls"');
header('Cache-Control: max-age=0');

  // Do your stuff here
  $writer = PHPExcel_IOFactory::createWriter($doc, 'Excel5');

$writer->save('php://output');
?>

edited Oct 15, 2017 at 0:01

Anbuselvan Annamalai

6247 silver badges23 bronze badges

answered Apr 28, 2014 at 18:29

Paul Bele

1,5341 gold badge11 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alejandro Over a year ago

Hi guys this example saved my ass but it do not work in firefox, and safari do you know what can I do about it?

You Old Fool Over a year ago

mimetype is wrong for Excel5 it should be application/vnd.ms-excel. application/vnd.openxmlformats-officedocument.spreadsheetml.sheet is for Excel2007 files with xlsx extension.

Paulo Victor · Accepted Answer · 2017-08-09 13:09:37Z

8

Sometimes we just can't use an external library/class like PHPExcel, in this case, I recommend you to use HTML table and change headers as xls, you can create it easily from array, very simple example:

<?php
date_default_timezone_set('America/Los_Angeles');

$sheet = array(
    array(
      'a1 data',
      'b1 data',
      'c1 data',
      'd1 data',
    )
);

$table = '<table><tbody><tr><td>A1</td><td>B1</td><td>C1</td><td>D1</td></tr>';
foreach ($sheet as $row) {
    $table.= '<tr><td>'.  implode('</td><td>', $row) . '</td></tr>';
}
$table.= '</tbody></table>';

header('Content-Encoding: UTF-8');
header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
header ("Cache-Control: no-cache, must-revalidate");
header ("Pragma: no-cache");
header ("Content-type: application/x-msexcel;charset=UTF-8");
header ("Content-Disposition: attachment; filename=productsExport.xls" );

echo $table;
?>

answered Aug 9, 2017 at 13:09

Paulo Victor

4,1422 gold badges30 silver badges29 bronze badges

3 Comments

You Old Fool Over a year ago

This is pretty cool, I never knew excel would open table markup like this

Drunken M Over a year ago

very simple and useful

Falgantil Over a year ago

For a simple one-off, this is an excellent solution that doesn't require any external dependencies. Would recommend.

Saurabh Chandra Patel · Accepted Answer · 2017-11-16 05:23:29Z

        $header = ['c1.', 'c2', 'c3', 'c4', 'c5', 'c6', 'c7', 'c8', 'c9', 'c10', 'c11']; 
        header('Content-Disposition: attachment; filename="data'.date('d-m-y').'.xls"');
        header("Content-Type: application/vnd.ms-excel");

        $output = fopen('php://output', 'w');
        fputcsv($output,  $header  , "\t");
        foreach ($Data as $value) { 
            $tmp_data = [];  
            array_push($tmp_data, $value['c1']);
            array_push($tmp_data, $value['c2']);
            array_push($tmp_data, $value['c3']);
            array_push($tmp_data, $value['c4']);
            array_push($tmp_data, $value['c5'] );
            array_push($tmp_data, $value['c6']);
            array_push($tmp_data, $value['c7']);
            array_push($tmp_data, $value['c8']);
            array_push($tmp_data,  $value['c9']);
            array_push($tmp_data, $value['c10']); 
            array_push($tmp_data, $value['c11']);
            fputcsv($output,  $tmp_data ,  "\t" );  
        }
        fclose($output);

nisanth A.N · Accepted Answer · 2016-06-25 09:55:45Z

-1

        $list=array();
        $list['tableheading'] =array('No','Name','Age','Sex');
        $fichier = 'sample.xls';
        header( "Content-Type: text/csv;charset=utf-8" );
        header( "Content-Disposition: attachment;filename=\"$fichier\"" );
        header("Pragma: no-cache");
        header("Expires: 0");
        $fp= fopen('php://output', 'w');
        fputcsv($fp,$list['tableheading'] );
        $slno=1;
        foreach($databasefetch AS $tabledata) {              
            $data['list']=array($slno,$tabledata['name'],$tabledata['Age'],$tabledata['sex']);
            fputcsv($fp, $data['list']);
            $slno++;
        }
        fclose($fp);
        exit();

answered Jun 25, 2016 at 9:55

nisanth A.N

71 bronze badge

2 Comments

Enigo Over a year ago

code-only answers are discouraged. Please, consider adding some information.

Daantje Over a year ago

Also this is generating a csv file, not a real xls format. So this method will fail when the viewer is expecting a real xls format.

Collectives™ on Stack Overflow

PHP create xls from array

4 Answers 4

2 Comments

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related