5 Ways to Create Multiple Excel Sheets with CodeIgniter

In today's data-driven environment, managing large datasets effectively is crucial, and CodeIgniter emerges as an exemplary PHP framework due to its simplicity and powerful features. One of its capabilities includes creating and manipulating Excel files. Here, we delve into five distinct methods to generate multiple Excel sheets within a single workbook using CodeIgniter, which can significantly enhance your data handling capabilities.

1. Utilizing PHPExcel Library

Before we dive into creating Excel files, installing the PHPExcel library is necessary. This can be achieved using Composer:

composer require phpoffice/phpexcel

• Create a New Workbook: Instantiate a new PHPExcel workbook.
• Add Sheets: Use methods like createSheet() to add multiple sheets.
• Set Data: Populate these sheets with relevant data using methods like setCellValue().
• Save the Workbook: Finally, save the workbook with all the sheets to an Excel file.

Here’s a sample snippet:

require APPPATH . ‘third_party/PHPExcel/Classes/PHPExcel.php’;
$objPHPExcel = new PHPExcel();

objPHPExcel->setActiveSheetIndex(0)->setTitle("Sheet1");
objPHPExcel->createSheet()->setTitle(“Sheet2”);
//… continue for other sheets
$objPHPExcel->getActiveSheet()->setCellValue(‘A1’, ‘Data’);

filename = 'multi_sheet_example.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . filename . ‘“’);
header(‘Cache-Control: max-age=0’);

objWriter = PHPExcel_IOFactory::createWriter(objPHPExcel, ‘Excel2007’);
$objWriter->save(‘php://output’);

⚠️ Note: Ensure the PHPExcel library is installed in your project to use these functionalities.

2. Using Spreadsheet-Excel-Writer

Another approach involves using the Spreadsheet_Excel_Writer library for older versions of Excel. Here’s how you can do it:

• Setup: Include the library files in your CodeIgniter setup.
• Workbook Creation: Begin by creating a new workbook.
• Sheet Addition: Add multiple sheets to this workbook.
• Data Entry: Populate sheets with data as needed.
• File Generation: Send the file to the browser for download.

require_once ‘Spreadsheet/Excel/Writer.php’;
$workbook = new Spreadsheet_Excel_Writer();

sheet1 =& workbook->addWorksheet(‘First Sheet’);
sheet2 =& workbook->addWorksheet(‘Second Sheet’);

sheet1->write(0, 0, 'Hello World');
sheet2->write(0, 0, ‘Another Sheet’);

header(‘Content-type: application/vnd.ms-excel’);
header(‘Content-Disposition: attachment; filename=“example.xls”’);
workbook->send('example.xls');
workbook->close();

3. Automating with Cron Jobs

If you need to generate Excel sheets regularly:

• Schedule a cron job to run a CodeIgniter script.
• The script can create Excel files and update them as required.

4. Exporting Database Data

Exporting data from your database into multiple Excel sheets can be streamlined using PHPExcel:

• Fetch Data: Use CodeIgniter’s database library to fetch required data.
• Create Sheets: Generate sheets corresponding to different data sets.
• Populate Sheets: Fill each sheet with the fetched data.

5. Combining Multiple Data Sources

In scenarios where data from various sources needs to be combined:

• Collect Data: Gather data from different APIs, databases, or user inputs.
• Sheet Segmentation: Create separate sheets for different data categories or sources.

By mastering these methods, you unlock the potential to efficiently manage and manipulate Excel files in your web applications. Each method offers unique advantages, ensuring you can select the one best suited for your specific needs.

What is the difference between PHPExcel and Spreadsheet-Excel-Writer?

+

PHPExcel supports newer Excel formats (like .xlsx) with more features, while Spreadsheet-Excel-Writer works with older formats (.xls) with fewer features.

How often should I run a cron job to generate Excel files?

+

This depends on your data update frequency and real-time data requirements. Daily or hourly updates might suffice for many applications.

Can I customize the appearance of sheets in these libraries?

+

Yes, both PHPExcel and Spreadsheet-Excel-Writer offer methods to modify cell styles, formats, and other visual attributes.