PHPExcel技术问询：如何为多个工作表添加模板

Let's break down what's going wrong in your current code and fix it to properly add multiple sheets based on a template:

Issues in Your Original Code #

• You're reloading the template every loop iteration: This overwrites the entire $objPHPExcel object each time, so you never build up multiple sheets—you just keep resetting to the template.
• No increment for $i: Your while loop will run infinitely because $i stays at 0 forever.
• createSheet makes an empty sheet: You're not actually applying the template's formatting/content to the new sheet; you're just making a blank one.

Correct Implementation #

Here's how to properly create multiple sheets using your template:

1. Load the template once outside the loop
2. For each new sheet, copy the template sheet (to preserve its styles and structure)
3. Rename/reindex the new sheet as needed
4. Modify the content of the copied sheet
5. Make sure to increment your loop counter

// Load the template file ONCE, outside the loop
$templateReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $templateReader->load('template.xlsx');

// Get the original template sheet to copy from
$templateSheet = $objPHPExcel->getActiveSheet();

// Create 10 sheets based on the template
for ($i = 0; $i < 10; $i++) {
    // Copy the template sheet to a new sheet
    $newSheet = clone $templateSheet;
    // Add the copied sheet to the workbook, set its index and title
    $objPHPExcel->addSheet($newSheet, $i + 1); // +1 to keep the original template sheet at index 0 (optional)
    
    // Rename the new sheet for clarity
    $newSheet->setTitle('Sheet ' . ($i + 1));
    
    // Modify the content of the new sheet
    $newSheet->setCellValue('A1', 'Hello' . $i)
             ->setCellValue('B2', 'world!')
             ->setCellValue('C1', 'Hello');
}

// Optional: If you don't want to keep the original template sheet in the final file
// $objPHPExcel->removeSheetByIndex(0);

// Save the final workbook
$writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$writer->save('final_output.xlsx');

Key Notes #

• Cloning the template sheet ensures all styles, formulas, and existing content from your template are preserved in each new sheet.
• Using a for loop instead of while makes it easier to manage the counter without forgetting to increment.
• You can choose to keep or remove the original template sheet from the final output using removeSheetByIndex.

内容的提问来源于stack exchange，提问作者Kurnia Rocki