Output excel file without formulas from advanced file

May 30, 2011 at 10:50 AM
Edited May 30, 2011 at 10:51 AM

I have an advanced excel file working with two worksheets having lots of formulas and references. I have tested the latest phpExcel and it handles the file very nice. However I have the following question someone might help me with. Can I do the following.

1. I want to open the file with phpExcel, and edit values in spreadsheet.index(0).

2. I want to calculate all the fields so that everything is updated.

3. I want to export the first spreadsheet.index(0), and delete spreadsheet.index(1) - without formulas.

The user should get an excel file with plain values, same formatting without any formulas! The user should also NOT have spreadsheet.index(1) as this index contains business logic NOT for the user.

Is this even possible with phpExcel?

Kim Steinhaug (kim@steinhaug.com, www.steinhaug.com

Jun 3, 2011 at 11:09 PM

The methods exist, but you'd have to do a lot of the work within your own code.

  1. Open the file and edit your values in worksheet
  2. Export the worksheet to an array using the toArray() method with the $calculateFormulas argument set to true to force calculation
  3. Create a new worksheet for the calculated values
  4. Write the data to a new worksheet using the fromArray() method
  5. Delete the worksheets you don't want
  6. Save it


Jun 6, 2011 at 2:13 PM

Thanks for this, I still have a little problem getting my head around this amazing library however I got it to work at the moment. Example.

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load('test.xlsx');

// Do my alterations here

$array = $objPHPExcel->setActiveSheetIndex(0)->toArray();
$excel = new PHPExcel();
$objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');

Surely I will need to do some testing with the fromArray() function as I remember there being some issues with empty cells and NULL values as mentioned in this http://phpexcel.codeplex.com/discussions/240688 thread - but at the moment it seems to work as expected. I will also need now to pull the styles and apply them so that the files look the same.

Jun 6, 2011 at 2:18 PM
Edited Jun 6, 2011 at 2:47 PM

Seems I got a nice idea here to do this, using a temp sheet. This code seems to work as intended. However there seems to be a problem, percentage fields are NOT copied with the toArray() function. The below code therefor almost works... All percentage fields are replaced with %f value. Seems there are info regarding this here: http://phpexcel.codeplex.com/discussions/234560

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load('2007.xlsx');
$array = $objPHPExcel->setActiveSheetIndex(0)->toArray();
$excel = new PHPExcel();
$array = $excel->setActiveSheetIndex(0)->toArray();
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');


1. Open the excel file with 2 worksheets and do the alterations
2. Copy sheet(1) with fromArray which takes the calculated values
3. Write the data to a new worksheet, without formulas
4. Read back the values from the temp sheet and overwrite the old sheet
5. Delete sheet(2) and save.

Thanks for your help Mark!