How to open an Excel file with PHPExcel for both reading and writing?

PHPExcel allows you to read an existing Excel file, modify its contents, and save it back with the same filename. This effectively overwrites the original file with your changes, providing read-write functionality.

Installation: Download PHPExcel library and ensure the Classes/PHPExcel/ directory is accessible in your project.

Reading and Writing Excel Files

The following example demonstrates how to load an Excel file, modify cell values, and save the changes back to the same file −

<?php
error_reporting(E_ALL);
set_time_limit(0);
date_default_timezone_set('Europe/London');

// Include PHPExcel library
set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');
include 'PHPExcel/IOFactory.php';

$fileType = 'Excel5';
$fileName = 'example.xls';

// Read the existing file
$objReader = PHPExcel_IOFactory::createReader($fileType);
$objPHPExcel = $objReader->load($fileName);

// Modify the file content
$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A1', 'Hello')
    ->setCellValue('B1', 'World!')
    ->setCellValue('C1', 'PHPExcel');

// Write changes back to the same file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $fileType);
$objWriter->save($fileName);

echo "File updated successfully!";
?>

How It Works

The process involves three main steps:

1. Load: createReader() creates a reader object for the specified file type, and load() reads the Excel file into memory
2. Modify: setActiveSheetIndex(0) selects the first worksheet, then setCellValue() updates specific cells
3. Save: createWriter() creates a writer object, and save() writes the modified data back to the original file

Output

File updated successfully!
Changes to cells A1, B1, C1 are reflected in the example.xls file.

Key Points

• Set time_limit(0) to prevent script timeout when processing large files
• Configure timezone to ensure proper date/time handling in Excel
• The same filename for loading and saving effectively overwrites the original file
• Use setActiveSheetIndex() to select the worksheet you want to modify

Conclusion

PHPExcel provides seamless read-write functionality by loading an existing file, modifying its contents, and saving it with the same filename. This approach effectively overwrites the original file with your changes.