PHPExcel: Set Column Names fromArray using PHP array

Question

I'm using PHPExcel library to export data to excel. I'm able to get all the data to excel as expected. But how can I set the column names from PHP array. Here is the code I'm using. Please help

    $data=(
    array(10) (
      [0] => array(8) (
        [#] => (string)
        [Name] => (string) Student1
        [ID] => (string) 123456
        [Date] => (string) 2016-02-01
        [Group] => (string) Physics
        [Month] => (string) February
        [Year] => (string) 2016
      )
      [1] => array(8) (
        [#] => (string)
        [Name] => (string) Student2
        [ID] => (string) 569874
        [Date] => (string) 2016-02-01
        [Group] => (string) Biology
        [Month] => (string) February
        [Year] => (string) 2016......);

    $objPHPExcel = new PHPExcel();

    $objPHPExcel->setActiveSheetIndex(0);
    $objPHPExcel->getActiveSheet()->setCellValue('A1', "#");
    $objPHPExcel->getActiveSheet()->setCellValue('B1', "Name");
    $objPHPExcel->getActiveSheet()->setCellValue('C1', "ID");
    $objPHPExcel->getActiveSheet()->setCellValue('D1', "Date");
    $objPHPExcel->getActiveSheet()->setCellValue('E1', "Group");
    $objPHPExcel->getActiveSheet()->setCellValue('F1', "Month");
    $objPHPExcel->getActiveSheet()->setCellValue('G1', "Year");

//How to replace/make dynamic lines above to set Cell values in first row based on array data as column names. i.e Name, ID, Date,.....

//Add Data

$objPHPExcel->getActiveSheet()->fromArray($data,NULL,'A2');

score 7 · Answer 1 · edited Sep 28 '16 at 14:53

7

// Header
$objPHPExcel->getActiveSheet()->fromArray(array_keys(current($data)), null, 'A1');
// Data
$objPHPExcel->getActiveSheet()->fromArray($data, null, 'A2');

edited Sep 28 '16 at 14:53

Ben Rhys-Lewis

3,118
8
34
45

answered Jun 28 '16 at 21:24

Blob

71
1
2

Doktor OSwaldo · Accepted Answer · 2016-03-17T10:33:18.090

4

like this ?

$objPHPExcel->getActiveSheet()->fromArray(array_keys($data[0]),NULL,'A2');

now that i know that's what you want to do, a short explain.

Array_keys copy's all keys from an Array as value to a numbered Array, so if you have an Array like this:

[#] => (string)
[Name] => (string) Student1
[ID] => (string) 123456
[Date] => (string) 2016-02-01
[Group] => (string) Physics
[Month] => (string) February
[Year] => (string) 2016

It will return following Array:

[0] = "#"
[1] = "Name"
[2] = "ID"
...

edited Mar 17 '16 at 10:33

answered Mar 17 '16 at 10:22

Doktor OSwaldo

5,732
20
41

@doktoroswaldo can you possibly help me with this question https://stackoverflow.com/questions/54244168/laravel-excel-not-exporting-in-correct-format?noredirect=1#comment95315360_54244168 – Geoff_S Jan 18 '19 at 00:31

Alejandro Aranda · Answer 3 · 2016-09-12T11:34:34.910

This code generates the columns from an array

class excelExport {
 public $columns = array(            
        0 => array('id' => "name", 'name' => 'Name'), 
        1 => array('id' => "tlf", 'name' => 'Telephone'),                                   
    );


 public function export(){      


    /** PHPExcel */
    $objPHPExcel = new PHPExcel();

    $objPHPExcel->setActiveSheetIndex(0);
    $objWorkSheet = $objPHPExcel->getActiveSheet();   

        $row =  1;
        $col = 0;
        for ($column = 'A'; ord($column) != ord('A')+count($this->columns); $column++) {

            $cell = $objWorkSheet->getCell($column.$row);
            $cell->setValue($this->columns[$col]["name"]);
            $col++;
        }

        ...

PHPExcel: Set Column Names fromArray using PHP array

3 Answers3