Array to Excel 2007 using PHPExcel

Question

I have this 2 dimensional array that I want to export as an excel file using PHPExcel.

// create a simple 2-dimensional array
$data = array(
   1 => array ('Name', 'Surname'),
   array('Schwarz', 'Oliver'),
   array('Test', 'Peter')
);

The problem is that I cannot predict the number of keys in the array so it becomes hard to use this method

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Hello');
$objPHPExcel->getActiveSheet()->SetCellValue('B2', 'world!');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Hello');
$objPHPExcel->getActiveSheet()->SetCellValue('D2', 'world!');

I am trying to generate a for loop that will do this, any help?

Have you looked at the fromArray() method rather than looping through your array and setting each individual cell? — Mark Baker, Oct 03 '13 at 09:45
Now imagine a case where you have `array(1=>array('Schwarz', 'Oliver','Schwarz', 'Oliver','Schwarz', 'Oliver','Schwarz', 'Oliver','Schwarz', 'Oliver','Schwarz', 'Oliver','Schwarz', 'Oliver','Schwarz', 'Oliver','Schwarz', 'Oliver','Schwarz', 'Oliver'))` How would you go about it? — Njuguna Mureithi, Oct 03 '13 at 09:45
`$objPHPExcel->getActiveSheet()->fromArray($myArray, null, 'A1')` — Mark Baker, Oct 03 '13 at 09:46

score 24 · Accepted Answer · answered Oct 03 '13 at 09:50

24

PHPExcel has a built-in method for setting cells from an array in a single step:

$data = array(
    array ('Name', 'Surname'),
    array('Schwarz', 'Oliver'),
    array('Test', 'Peter')
);
$objPHPExcel->getActiveSheet()->fromArray($data, null, 'A1');

answered Oct 03 '13 at 09:50

Mark Baker

209,507
32
346
385

1

Why null and A1(may used for starting) at fromArray() ? – 151291 Apr 01 '17 at 07:25
1

@151291 - [reading the PHPExcel docs would tell you](http://hitautodestruct.github.io/PHPExcelAPIDocs/classes/PHPExcel_Worksheet.html#method_fromArray) – Mark Baker Apr 01 '17 at 09:35

score 3 · Answer 2 · answered Oct 03 '13 at 09:36

3

for this I would use the function

$objPHPExcel->getActiveSheet()->SetCellValueByColumnAndRow($column, $row, $text)

and run the foreach on the arrays indexes. Columns start at 0, rows at 1.

answered Oct 03 '13 at 09:36

gregory

826
1
6
6

score 1 · Answer 3 · answered Oct 03 '13 at 09:34

1

this one may help:

$i = 0;
foreach ($data as $key => $value){
    $objPHPExcel->getActiveSheet()->SetCellValue(PHPExcel_Cell::stringFromColumnIndex($i).'1', $key);
    $objPHPExcel->getActiveSheet()->SetCellValue(PHPExcel_Cell::stringFromColumnIndex($i+1).'2', $value);
    $i++;
}

answered Oct 03 '13 at 09:34

k102

7,861
7
49
69

No. Please, post your actual data (or a part of it) here – k102 Oct 03 '13 at 10:22

score 0 · Answer 4 · answered Oct 03 '13 at 09:34

0

You can use foreach instead when traversing an array , it will give you key=>value pair.

Please let me know if expect anything different.

answered Oct 03 '13 at 09:34

Nilesh

442
1
4
22

No, it wont work, i thought you were getting associative array. – Nilesh Oct 03 '13 at 13:38

Array to Excel 2007 using PHPExcel

4 Answers4