PHPExcel - creating multiple sheets by iteration

29

I'm trying to create multiple sheets by iteration in phpexcel:

$i=0;

while ($i < 10) {

// Add new sheet
$objWorkSheet = $objPHPExcel->createSheet();

//  Attach the newly-cloned sheet to the $objPHPExcel workbook
$objPHPExcel->addSheet($objWorkSheet);

// Add some data
$objPHPExcel->setActiveSheetIndex($i);

$sheet = $objPHPExcel->getActiveSheet();

$sheet->setCellValue('A1', 'Hello'.$i)
        ->setCellValue('B2', 'world!')
        ->setCellValue('C1', 'Hello')
        ->setCellValue('D2', 'world!');

// Rename sheet
$sheet->setTitle($i);

$i++;
}

Unfortunately this doesn't work. I only get some sheets of this iteration filled with data and renamed and about the half are empty.

So this is the result (sheet titles):

0, 2, 4, 6, 8, 9, and 5 empty sheets

I can't figure out why only even numbered (and sheet 9) are correct in the result.

This question is tagged with php phpexcel

~ Asked on 2012-03-24 07:43:37

The Best Answer is


87

You dont need call addSheet() method. After creating sheet, it already add to excel. Here i fixed some codes:

    //First sheet
    $sheet = $objPHPExcel->getActiveSheet();

    //Start adding next sheets
    $i=0;
    while ($i < 10) {

      // Add new sheet
      $objWorkSheet = $objPHPExcel->createSheet($i); //Setting index when creating

      //Write cells
      $objWorkSheet->setCellValue('A1', 'Hello'.$i)
                   ->setCellValue('B2', 'world!')
                   ->setCellValue('C1', 'Hello')
                   ->setCellValue('D2', 'world!');

      // Rename sheet
      $objWorkSheet->setTitle("$i");

      $i++;
    }

~ Answered on 2012-03-24 08:43:00


9

Complementing the coment of @Mark Baker.
Do as follow:

$titles = array('title 1', 'title 2');
$sheet = 0;
foreach($array as $value){
    if($sheet > 0){
        $objPHPExcel->createSheet();
        $sheet = $objPHPExcel->setActiveSheetIndex($sheet);
        $sheet->setTitle("$value");
        //Do you want something more here
    }else{
        $objPHPExcel->setActiveSheetIndex(0)->setTitle("$value");
    }
    $sheet++;
}  

This worked for me. And hope it works for those who need! :)

~ Answered on 2015-11-30 20:01:02


Most Viewed Questions: