phpexcel - using it with excel template (chart goes missing) php

Question

I have tried to use phpexcel with my own template file. phpexcel loads the file and writes data to some cells A2, A3, A4 for example.. and opens an output file with the new data.

my template file has chart built-in.. all i want to phpexcel to do is to populate values in cells and don't touch the chart. And, open the new file. (Please note that I don't want to make the chart in code.. I want the chart to pre-exist with in my template in same format as I created originally). Only the data should update.

But, when i try to do this.. the chart itself goes missing from the resulting file. After trying various ways.. still failed.

And, i found the following code from http://phpexcel.codeplex.com/discussions/397263

require_once 'Classes/PHPExcel.php'; 
/** PHPExcel_IOFactory */
include 'Classes/PHPExcel/IOFactory.php';




$target ='Results/';
$fileType = 'Excel2007';   
$InputFileName = $target.'Result.xlsx';   
$OutputFileName =  $target . '_Result.xlsx';

 //Read the file (including chart template) 
$objReader = PHPExcel_IOFactory::createReader($fileType); 
$objReader->setIncludeCharts(TRUE);
$objPHPExcel = $objReader->load($InputFileName); 

 //Change the file 


$objPHPExcel->setActiveSheetIndex(0)
// Add data
            ->setCellValue('C3','10' )
            ->setCellValue('C4','20' )
            ->setCellValue('C5','30')
            ->setCellValue('C5','40' );

 //Write the file (including chart)

PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $fileType); 
$objWriter->setIncludeCharts(TRUE);
$objWriter->save($OutputFileName);

The above code works in excel 2010 and now keeps my chart in tact... but still when I try to use filetype "Excel5" it doesn't work.

It throws the following error:

    Fatal error: Call to undefined method PHPExcel_Reader_Excel5::setIncludeCharts()
 in D:\IT\bfstools\PHPExcel\MyExamples\test1.php on line 16

Please provide a simple solution where I want my template file to work with .xls and .xlsx and all my original chart in the template file should stay intact. I do not want the chart removed it from the resulting file. Neither do I plan to create the chart using phpexcel code. (why write unnecessary code when excel can do all the work for you).

I want the easiest way out which is just to use everything with in my template and just populate cells with new data. And, my existing chart in the template comes live automatically. I don't want to write unnecessary code while I can safely rely on excel template and charting functions.

Please help.

Please read this post from myself and others related to the same topic as it looks a far more capable solution for the above purposes... stackoverflow.com/questions/11561555/… — ihightower
– ihightower, Commented Nov 5, 2012 at 9:43
Here's a proper solution: stackoverflow.com/questions/25628205/… Hint: It uses another library. — nawfal
– nawfal, Commented Apr 2, 2016 at 6:40

Mark Baker · Accepted Answer · 2012-11-01 09:56:14Z

10

There's a very good reason for this:

Charting is only implemented in core, and for the Excel2007 Readers and Writers at this point in time, so all of the other readers or writers will ignore charts, treat them as though they simply don't exist. The intention is to roll out charting to the other readers/writers over the coming year.

EDIT

I see from your comment that you don't understand how PHPExcel works at all, so I have a lot of explaining to do.

PHPExcel is not a library for "editing" workbook files: you're not using PHPExcel to change a file, you're changing a PHPExcel object that can be loaded from a file, and can subsequently be written to a file.

PHPExcel Core is an in-memory representation of the spreadsheet, with the different constituent objects such as worksheets, cells, images, styles, etc all represented as PHP Objects.

The PHPExcel Readers parse a spreadsheet file and load all the components from a file that they have been programmed to recognise, and create the appropriate PHPExcel core objects from those file components. If there is no equivalent PHPExcel Core object (such as Pivot Tables), then that file component can't be "loaded"; if the loader hasn't been programmed to recognise a file component, then it can't be loaded. In these cases, those elements from the file are simply ignored. Once the Reader has done it's job, a PHPExcel object exists, and the spreadsheet file is closed and forgotten.

When a PHPExcel Core object exists in memory, you have a set of methods allowing you to manipulate and change it, to add, modify or delete Core elements; but these work purely on the "in memory" collection of worksheet, cell, style objects that comprise the PHPExcel Core. The Core exists without knowledge of having been loaded from a file or having been created using a PHP "new PHPExcel()" statement; it makes no changes to files in any way.

When writing, the reverse is true. Each Writer takes the PHPExcel core objects, and writes them to a file in the appropriate format (Excel BIFF, OfficeOpenXML, HTML, etc). Like the Readers, each writer can only write those PHPExcel Core objects that it has been programmed to write. If it has not been programmed to write (for example, charts) then any charts defined in the PHPExcel Core will be ignored because that writer simply doesn't know how to write them yet. Likewise, features that exist in PHPExcel Core that are not supported by the file format that is being written to (such as cell styles for the CSV Writer) are ignored.

So to support a spreadsheet feature such as charts, it is necessary for the PHPExcel Core object collection to have been modified to provide an "in memory" representation of those elements, and for the different Readers to have been programmed to recognise those elements in the file they are loading and to convert them to the appropriate PHPExcel Core objects, and for the different Writers to have been programmed to convert the PHPExcel core representation to the appropriate file representation.

Each Reader and each Writer needs to be programmed individually. Charts is a relatively new feature, only added to the PHPExcel Core in the 1.7.7 release, and at this point only the Reader and Writer for the Excel2007 format have been programmed to recognise chart elements. While it is the intention of the developers to extend this to cover the other formats as well, the necessary code isn't created automagically. Programming each individual Reader and Writer takes time and effort. While the Chart code for the Excel2007 Reader and Writer has now stabilised to the point where it is now no longer considered "experimental", and development focus is turning to writing the necessary code for chart handling in the Excel5 Reader and Writer, it is work that has not yet been completed.

edited Nov 1, 2012 at 9:56

answered Nov 1, 2012 at 7:40

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

ihightower Over a year ago

thanks for a really quick reply, but I don't really understand completely of what is being said. but from what i understand... why ignore (or remove) the chart on the template. Why can't phpexcel just leave it alone (that is NOT remove it).. and carry on with its work of populating the template cells only. And, my chart will automatically get updated by the power of Excel itself with no support required from phpexcel. phpexcel removing the chart doesn't make any sense to me. Is there any technical reason? Why can't phpexcel just leave it alone.

Raffael Over a year ago

@Mark Baker: I basically get your point - thanks for clarifying. But what I do not understand is why the charts left even though I communicated to PHPExcel that I am dealing with a spreadsheet of Excel 2007 or higher. Because I used PHPExcel 1.7.7 and according to your last paragraph (if I understand it right) the proper handling of charts should have been available already for 2007 or higher. ?

Mark Baker Over a year ago

Quoting from your original question (my emphasis): "The above code works in excel 2010 and now keeps my chart in tact... but still when I try to use filetype Excel5 it doesn't work.".... Excel5 is BIFF files (.xls); Excel2007 is OfficeOpenXML Files (.xlsx)

ihightower Over a year ago

Thank you so much for your explanation and well understood now. It has been only 2 days since I know about phpexcel and I hope your detailed explanation is available with in your documentation or faq somewhere for other readers as to "what phpexcel really is and is not". Is it available in documentation? other than my requirements, phpexcel is an excellent piece of work.. thank you so much for your contribution.

ihightower Over a year ago

on the other hand.. Can you also let me know if there are any other choices available for me where I can do what I wanted.. that is open the excel template file (.xlsx or even .xlsm) in php and populate the cells and keep all the charts, pivot table, even macros, forms, buttons, etc. intact. All the php will do is just populate the cells with new data from database. In other words, using the same terminology that you have used.. please point me to the library for which I can use to "edit" workbook files. Thank you so much again.

|

xuri · Accepted Answer · 2017-07-18 06:56:45Z

0

If you can use Golang, try Excelize. Support save file without losing original charts of XLSX.

edited Jul 18, 2017 at 6:56

answered Jan 4, 2017 at 7:16

xuri

9037 silver badges20 bronze badges

Comments

xuri · Accepted Answer · 2015-09-21 07:45:57Z

-1

Try set setIncludeCharts

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
//  Tell the reader to include charts when it loads a file
$objReader->setIncludeCharts(TRUE);
//  Load the file
$objPHPExcel = $objReader->load($filePath);

answered Sep 21, 2015 at 7:45

xuri

9037 silver badges20 bronze badges

Collectives™ on Stack Overflow

phpexcel - using it with excel template (chart goes missing) php

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related