php PHPExcel - 通过迭代创建多个工作表

PHPExcel - creating multiple sheets by iteration

提问by Chris

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

我正在尝试在 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

0、2、4、6、8、9 和 5 张空纸

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

我无法弄清楚为什么结果中只有偶数编号（和第 9 页）是正确的。

回答by safarov

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

你不需要调用addSheet()方法。创建工作表后，它已经添加到excel。在这里我修复了一些代码：

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

回答by Mark Baker

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".

当您第一次实例化 $objPHPExcel 时，它已经有一个工作表（工作表 0）；然后，您将添加一个新工作表（将成为工作表 1），但将活动工作表设置为工作表 $i（当 $i 为 0 时）...因此您正在重命名并填充在实例化 $objPHPExcel 时创建的原始工作表而不是您刚刚添加的那个……这是您的标题“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.

您还使用了 createSheet() 方法，该方法既创建了一个新工作表，又将其添加到工作簿中……但您自己也再次添加了它，这有效地将工作表添加到了两个位置。

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

所以第一次迭代，你已经有了 sheet0，在索引 1 和 2 上添加一个新表，并编辑/标题表 0。第二次迭代，你在索引 3 和 4 和编辑/标题表 1 处添加一个新表，但是因为您在索引 1 和 2 处有相同的工作表，这有效地写入了索引 2 的工作表。第三次迭代，您在索引 5 和 6 处添加一个新工作表，然后编辑/标题工作表 2，覆盖您之前的工作表编辑/标题1 对工作表 2 采取了行动……等等

回答by AD Henrique

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

补充@Mark Ba​​ker 的评论。
请按以下步骤操作：

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

这对我有用。并希望它对需要的人有用！:)

回答by ABDUL JAMAL

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');

回答by Brandon Asaph

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.

如果您还没有得出结论......我接受了 Henrique 的回答并给出了一个更好的逻辑解决方案。这与 PHPSpreadSheet 完全兼容，以防有人使用 PHPSpreadSheet 或 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;
}