Create Multiple Excel Sheets Easily with PhpSpreadsheet

The PhpSpreadsheet library offers an extensive set of functionalities for handling Excel files in PHP, making it an invaluable tool for developers working with spreadsheets. This tutorial will guide you through the process of creating multiple Excel sheets using PhpSpreadsheet, enabling you to efficiently manage and export data into different sheets within a single Excel workbook.

Introduction to PhpSpreadsheet

PhpSpreadsheet is a PHP library that enables developers to read, write, and manipulate spreadsheets in various formats including Excel (XLSX, XLS), OpenDocument (ODS), and CSV. Its wide range of features includes:

• Reading from and writing to spreadsheets
• Support for formulas
• Formatting cells
• Working with charts
• Handling large datasets

💡 Note: To proceed with this tutorial, you should have PhpSpreadsheet installed in your project. If you haven’t installed it yet, you can do so via Composer by running the command: composer require phpoffice/phpspreadsheet

Creating Your First Workbook

The journey of creating multiple sheets in Excel starts with initializing a new workbook:

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

Adding Multiple Sheets

To add multiple sheets within the same workbook:

1. Create the initial sheet:

   
   sheet1 = spreadsheet->getActiveSheet();
   sheet1->setTitle("Sheet1");
       </code></pre>
       </li>
       <li><strong>Add additional sheets:</strong>
       <pre><code>
   sheet2 = spreadsheet->createSheet();
   sheet2->setTitle(“Sheet2”);
       

2. Add more sheets as needed:

   
   sheet3 = spreadsheet->createSheet();
   $sheet3->setTitle(“Sheet3”);
       

Each new sheet created with createSheet() is added to the end of the sheet list.

Populating Sheets with Data

After setting up the sheets, populate them with data:

// Populating Sheet1
sheet1->setCellValue("A1", "Sheet1 Title");
sheet1->setCellValue(“A2”, “Data for Sheet1”);

// Populating Sheet2
sheet2->setCellValue("A1", "Sheet2 Title");
sheet2->setCellValue(“A2”, “Data for Sheet2”);

Styling and Formatting Sheets

You can enhance the visual appeal and organization of your sheets with various styling options:

• Cell Alignment:

  
  sheet1->getStyle('A1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
      </code></pre>
      </li>
      <li><strong>Cell Background and Border:</strong>
      <pre><code>
  sheet2->getStyle(‘A1’)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB(‘FFFF00’);
  sheet2->getStyle('A1')->applyFromArray([
      'borders' => [
          'outline' => [
              'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
              'color' => ['argb' => 'FF000000'],
          ],
      ],
  ]);
      </code></pre>
      </li>
      <li><strong>Font Styling:</strong>
      <pre><code>
  sheet3->getStyle(‘A1’)->getFont()->setSize(14)->setBold(true);
      

Adding Formulas and Functions

PHP your sheets dynamic, you can incorporate Excel formulas:

$sheet3->setCellValue(“A3”, “=SUM(A1:A2)”);

💡 Note: When using formulas, remember that Excel can handle complex calculations automatically, reducing the need for manual computation in your PHP code.

Saving the Workbook

Once you’ve customized your sheets, save the workbook:

writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter(spreadsheet, ‘Xlsx’);
$writer->save(‘filename.xlsx’);

Choose the appropriate writer type for the desired Excel format. Here, we’re using Xlsx for Excel 2007 onwards compatibility.

Handling Large Datasets

For large datasets, consider using memory optimization techniques:

• Memory Usage: Set memory limits and enable chunk reading for very large spreadsheets.
• Writing Large Spreadsheets: Use the MemoryDrawing class for images and adjust settings to minimize memory usage.

By following these steps, you can efficiently create multiple sheets in an Excel workbook using PhpSpreadsheet, significantly streamlining your data management tasks. This ability to organize data into separate sheets makes it easier for users to access and analyze information, and with the power of PHP, you can automate and customize this process further to meet complex requirements.

Can I add charts to individual sheets?

+

Yes, PhpSpreadsheet allows you to add charts to specific sheets. Use the \PhpOffice\PhpSpreadsheet\Chart class to create and configure charts.

How can I set up large datasets in PhpSpreadsheet?

+

For large datasets, ensure your server has enough memory, use memory-saving techniques like chunk reading, or consider implementing external memory handling solutions.

Can I merge cells across different sheets?

+

No, cells cannot be merged across sheets; merging is confined to individual sheets. However, you can replicate cell contents or styling across sheets if needed.