Write data from array to sheet using phpspreadsheet library

Question

How can I create excel sheet column headers from array using phpspreadsheet library?

Below is the code I am trying but it's not working:

    // $header is an array containing column headers
    $header = array("Customer Number", "Customer Name", "Address", "City", "State", "Zip");
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->fromArray($header, NULL, 'A1');     

    // redirect output to client browser
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="myfile.xlsx"');
    header('Cache-Control: max-age=0');

    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');

What do you mean with "not working"? Your code creates an attachment with the name "myfile.xlsx" and the content is your header array. — Chris
– Chris, Commented Feb 8, 2018 at 9:44
It's always a goog idea, to let the community know, what you did, to solve a problem. That may help others, which is the pupose of stackoverflow. — Chris
– Chris, Commented Feb 8, 2018 at 9:56

N3R4ZZuRR0 · Accepted Answer · 2019-09-10 08:51:35Z

36

You need to write

$sheet->fromArray([$header], NULL, 'A1');

edited Sep 10, 2019 at 8:51

N3R4ZZuRR0

2,4224 gold badges21 silver badges32 bronze badges

answered Apr 28, 2018 at 7:16

Denis

3693 silver badges3 bronze badges

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

1 Comment

Timberman Over a year ago

Would you elaborate why this works?

xayer · Accepted Answer · 2020-10-04 16:11:33Z

Also, you have the wrong Content-Type header...

For BIFF .xls files

application/vnd.ms-excel

For Excel2007 and above .xlsx files

application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

So, fully working code should looks like this:

    // $header is an array containing column headers
    $header = [array("Customer Number", "Customer Name", "Address", "City", "State", "Zip")];

    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->fromArray($header, NULL, 'A1');     

    // redirect output to client browser
    header('Content-Disposition: attachment;filename="myfile.xlsx"');
    header('Cache-Control: max-age=0');

    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');

Mykola Veryha · Accepted Answer · 2021-12-03 03:05:51Z

An example with more details

$database = [
    [ 'Tree',  'Height', 'Age', 'Yield', 'Profit' ],
    [ 'Apple',  18,       20,    14,      105.00  ],
    [ 'Pear',   12,       12,    10,       96.00  ],
    [ 'Cherry', 13,       14,     9,      105.00  ],
    [ 'Apple',  14,       15,    10,       75.00  ],
    [ 'Pear',    9,        8,     8,       76.80  ],
    [ 'Apple',   8,        9,     6,       45.00  ],
];

$criteria = [
    [ 'Tree',      'Height', 'Age', 'Yield', 'Profit', 'Height' ],
    [ '="=Apple"', '>10',    NULL,  NULL,    NULL,     '<16'    ],
    [ '="=Pear"',  NULL,     NULL,  NULL,    NULL,     NULL     ],
];

$worksheet->fromArray( $criteria, NULL, 'A1' )
    ->fromArray( $database, NULL, 'A4' );

$worksheet->setCellValue('A12', '=DCOUNT(A4:E10,"Height",A1:B3)');

$retVal = $worksheet->getCell('A12')->getCalculatedValue();

// $retVal = 3

There're more good examples https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine/#examples_1

Aro Wolfgang Micheal · Accepted Answer · 2019-05-17 14:58:23Z

1

You need to include the namespace

        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);

answered May 17, 2019 at 14:58

Aro Wolfgang Micheal

791 silver badge3 bronze badges

1 Comment

Milad Safaei Over a year ago

It's not needed to include the namespace, if you "use" the namespace above your class.

Collectives™ on Stack Overflow

Write data from array to sheet using phpspreadsheet library

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Linked

Related