PHPExcel - creating multiple sheets by iteration

问题:

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.

回答1:

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++;
    }

回答2:

When you first instantiate the $objPHPExcel, it already has a single sheet (sheet 0); you're then adding a new sheet (which will become sheet 1), but setting active sheet to sheet $i (when $i is 0)... so you're renaming and populating the original worksheet created when you instantiated $objPHPExcel rather than the one you've just added... this is your title "0".

You're also using the createSheet() method, which both creates a new worksheet and adds it to the workbook... but you're also adding it again yourself which is effectively adding the sheet in two position.

So first iteration, you already have sheet0, add a new sheet at both indexes 1 and 2, and edit/title sheet 0. Second iteration, you add a new sheet at both indexes 3 and 4, and edit/title sheet 1, but because you have the same sheet at indexes 1 and 2 this effectively writes to the sheet at index 2. Third iteration, you add a new sheet at indexes 5 and 6, and edit/title sheet 2, overwriting your earlier editing/titleing of sheet 1 which acted against sheet 2 instead.... and so on

回答3:

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! :)

回答4:

In case you haven't come to a conclusion... I took Henrique's answer and gave a better logic solution. This is completely compatible with PHPSpreadSheet in case someone is using PHPSpreadSheet or PHPExcel.

$spreadOrPhpExcel = new SpreadSheet(); // or new PHPExcel();
print_in_sheet($spreadOrPhpExcel);

function print_in_sheet($spread)
{
    $sheet = 0;

    foreach( getData() as $report => $value ){
        # If number of sheet is 0 then no new worksheets are created
        if( $sheet > 0 ){
            $spread->createSheet();
        }

        # Index for the worksheet is setted and a title is assigned
        $wSheet = $spread->setActiveSheetIndex($sheet)->setTitle($report);

        # Printing data
        $wSheet->setCellValue("A1", "Hello World!");

        # Index number is incremented for the next worksheet
        $sheet++;
    }

    return $spread;
}

回答5:

You can write different sheets as follows

$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("creater");
$objPHPExcel->getProperties()->setLastModifiedBy("Middle field");
$objPHPExcel->getProperties()->setSubject("Subject");
$objWorkSheet = $objPHPExcel->createSheet();
$work_sheet_count=3;//number of sheets you want to create
$work_sheet=0;
while($work_sheet<=$work_sheet_count){ 
     if($work_sheet==0){
         $objWorkSheet->setTitle("Worksheet$work_sheet");
         $objPHPExcel->setActiveSheetIndex($work_sheet)->setCellValue('A1', 'SR No. In sheet 1')->getStyle('A1')->getFont()->setBold(true);
         $objPHPExcel->setActiveSheetIndex($work_sheet)->setCellValueByColumnAndRow($col++, $row++, $i++);//setting value by column and row indexes if needed
     }
     if($work_sheet==1){
         $objWorkSheet->setTitle("Worksheet$work_sheet");
         $objPHPExcel->setActiveSheetIndex($work_sheet)->setCellValue('A1', 'SR No. In sheet 2')->getStyle('A1')->getFont()->setBold(true);
         $objPHPExcel->setActiveSheetIndex($work_sheet)->setCellValueByColumnAndRow($col++, $row++, $i++);//setting value by column and row indexes if needed
     }
     if($work_sheet==2){
         $objWorkSheet = $objPHPExcel->createSheet($work_sheet_count);
         $objWorkSheet->setTitle("Worksheet$work_sheet");
         $objPHPExcel->setActiveSheetIndex($work_sheet)->setCellValue('A1', 'SR No. In sheet 3')->getStyle('A1')->getFont()->setBold(true);
         $objPHPExcel->setActiveSheetIndex($work_sheet)->setCellValueByColumnAndRow($col++, $row++, $i++);//setting value by column and row indexes if needed
     }
     $work_sheet++;
}

$filename='file-name'.'.xls'; //save our workbook as this file name
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cach

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');