Excel is a widely used spreadsheet software that allows users to organize data in a structured and visually appealing format. PHPExcel is a PHP library that provides an easy-to-use interface for generating Excel spreadsheets. In this tutorial, you will learn how to create multiple worksheets in one Excel file using PHPExcel.
How to Create Multiple Work Sheets in single Excel using PHP Excel
With PHPExcel, you can easily create multiple worksheets in a single Excel file. Check out the following step-by-step tutorial to get started.
- Step 1: Install PHPExcel
- Step 2: Create a new Excel file
- Step 3: Add multiple worksheets
- Step 4: Set the active worksheet
- Step 5: Add data to the worksheets
- Step 6: Save the Excel file
- Step 7: Complete code example
Step 1: Install PHPExcel
Before you can create multiple worksheets in an Excel file using PHPExcel, you need to install the library. You can do this by downloading the latest version of PHPExcel from the official website and extracting the files to a directory in our project.
Step 2: Create a new Excel file
To create a new Excel file, you need to instantiate the PHPExcel object. This object represents a new Excel file that and can work with. You can do this by adding the following code at the beginning of our PHP script:
require_once 'path/to/PHPExcel/Classes/PHPExcel.php'; // Create a new PHPExcel object $objPHPExcel = new PHPExcel();
Step 3: Add multiple worksheets
Once you have created a new Excel file, and can add multiple worksheets to it. you can do this by calling the createSheet() method on our PHPExcel object. This method takes an optional parameter that specifies the index of the new worksheet. If you do not specify an index, the new worksheet will be added to the end of the workbook. Here’s an example:
// Add a new worksheet to the workbook $objPHPExcel->createSheet(); // Add another worksheet to the workbook, at index 0 $objPHPExcel->createSheet(0);
In this example, you have added two worksheets to the workbook. The first worksheet will be added to the end of the workbook, and the second worksheet will be added at index 0, which means it will be the first worksheet in the workbook.
Step 4: Set the active worksheet
By default, the first worksheet in the workbook is the active worksheet. To set a different worksheet as the active worksheet, you can call the setActiveSheetIndex() method on our PHPExcel object. This method takes an integer parameter that specifies the index of the worksheet that you want to set as the active worksheet. Here’s an example:
// Set the second worksheet as the active worksheet $objPHPExcel->setActiveSheetIndex(1);
In this example, you have set the second worksheet in the workbook (which you added at index 0) as the active worksheet.
Step 5: Add data to the worksheets
Now that you have created multiple worksheets in our Excel file, you can start adding data to them. To add data to a worksheet, and need to first set the active worksheet (if it is not already set), and then use the setCellValue() method to set the value of a cell. Here’s an example:
// Set the active worksheet
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Name of Sheet 1');
// Add some data to cell A1
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello, world!');
// Set the active worksheet to the second worksheet
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setTitle('Name of Sheet 2');
// Add some data to cell B2
$objPHPExcel->getActiveSheet()->setCellValue('B2', 'Goodbye, world!');
In this example, you have set the value of cell A1 in the first worksheet to “Hello, world!”, and the value of cell B2 in the second worksheet to “Goodbye, world!”.
Step 6: Save the Excel file
Once you have added data to our worksheets, and can save the Excel file using the save() method on our PHPExcel object. This method takes a filename as an optional parameter. If you do not specify a filename, the Excel file will be saved to the output stream. Here’s an example:
// Save the Excel file to the output stream
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
In this example, you have created a new PHPExcel_Writer_Excel2007 object, which is a writer that can write Excel 2007 files. You then use this writer to save the Excel file to the output stream.
Step 7: Complete code example
Here’s a complete code example that shows how to create multiple worksheets in one Excel file using PHPExcel:
require_once 'path/to/PHPExcel/Classes/PHPExcel.php';
// Create a new PHPExcel object
$objPHPExcel = new PHPExcel();
// Add two new worksheets to the workbook
$objPHPExcel->createSheet();
$objPHPExcel->createSheet(0);
// Set the second worksheet as the active worksheet
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setTitle('Name of Sheet 1');
// Add some data to cell B2
$objPHPExcel->getActiveSheet()->setCellValue('B2', 'Goodbye, world!');
// Set the active worksheet
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Name of Sheet 2');
// Add some data to cell A1
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello, world!');
// Save the Excel file to the output stream
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
In this example, you have created a new PHPExcel object, added two new worksheets to the workbook, set the second worksheet as the active worksheet, added some data to cells in both worksheets, and saved the Excel file to the output stream.
Conclusion
In this tutorial, you have learned how to create multiple worksheets in one Excel file using PHPExcel. And you have seen how to add new worksheets, set the active worksheet, add data to cells in the worksheets, and save the Excel file. With these techniques, you can create complex Excel files with multiple worksheets and populate them with data programmatically using PHP.
