Edit an excel file using PHPExcel

November 30, 2013 | Last tested: May 2015 | 8912 views | Comments

Simply modify an existing excel file using PHPExcel in three steps. First, read the existing excel file. Second, update it's data. Last, generate an updated excel file.

For example.

Update all column headers, prepend to each one the word "Edited".

To make it even more easier, the following PHPExcel Tutorials are nice to be familiarized with before modifying an existing excel file.

Better to proceed with the detailed examples below when a successful process of reading and writing excel files are already achieved.

1. Read the existing excel file.


include_once('PHPExcel_1.8.0_doc/Classes/PHPExcel/IOFactory.php');

$inputFileName = 'edit-an-excel-file-using-phpexcel.xlsx';

/*check point*/

$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);

2. Update it's data.


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Add column headers
$objPHPExcel->getActiveSheet()
			->setCellValue('A1', 'EDITED Last Name')
			->setCellValue('B1', 'EDITED First Name')
			->setCellValue('C1', 'EDITED Age')
			->setCellValue('D1', 'EDITED Sex')
			->setCellValue('E1', 'EDITED Location')
			;

3. Generate an updated excel file.


// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $inputFileName . '"');
header('Cache-Control: max-age=0');

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

All code together in a single script.

include_once('PHPExcel_1.8.0_doc/Classes/PHPExcel/IOFactory.php');

$inputFileName = 'edit-an-excel-file-using-phpexcel.xlsx';

/*check point*/

// Read the existing excel file
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);

// Update it's data
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Add column headers
$objPHPExcel->getActiveSheet()
			->setCellValue('A1', 'EDITED Last Name')
			->setCellValue('B1', 'EDITED First Name')
			->setCellValue('C1', 'EDITED Age')
			->setCellValue('D1', 'EDITED Sex')
			->setCellValue('E1', 'EDITED Location')
			;
			
// Generate an updated excel file
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $inputFileName . '"');
header('Cache-Control: max-age=0');

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

Result:

Before editing.

Please mouse over here to view the image.

After editing.

Please mouse over here to view the image.