Chart support

Topics: Developer Forum, User Forum
Feb 24, 2009 at 9:07 PM
Is there any future plans to add Chart support for Excel 2007?
Has anybody started working on chart support?
I am just looking to use existing charts in template and not create new ones.
Thanks

Coordinator
Feb 24, 2009 at 10:10 PM
Edited Feb 24, 2009 at 10:22 PM
It's something planned for this year. I've been reading the relevant sections of the MS file format specifications that have recently been released into the public domain, and Maarten "twittered" that he would be reading them tonight as well... that's all about identifying how Excel maintains it's definition of charts.
And the trend classes included in the current SVN code are intended to provide the framework for calculating trend lines (currently there's basic code there for Linear, Logarithmic, Exponential, Power and 2nd-6th order Polynomials). While I've been implementing the functions such as LINEST and TREND that use the Linear trend class, the other trends are explicitely intended as a first step toward providing the underlying functions needed to replicate Excel chart features.
Initial tests of these methods have been plotted in my testbed using jpGraph, and look quite good; but we'll almost certainly need to provide wrappers/drivers for several different charting libraries such as jpGraph, pChart, Graphpite or LibChart (different developers all have their own preferences: we'd prefer not to write our own library, and it's unlikely that we'd bundle a 3rd-party charting library with PHPExcel).

However, it's quite a complicated sub-project, so it's likely to be the tail-end of the year before there's much to show.
Mar 4, 2009 at 7:33 PM

Thanks for the reply.  I found a temp solution that seems to work.  Use PHPExcel to update the data and create a new excel document, then modify all the sheets sheetData in the original workbook with the updated file.  Finally replace the original's file sharedStrings.xml with the updated sharedStrings.xml.  Seems to work so far.

      public function updateOriginalFile($originalFile, $updatedFile) {<o:p></o:p>

           $zipUpdated= new ZipArchive ( );<o:p></o:p>

           $zipUpdated->open ( $updatedFile );<o:p></o:p>

           $zipOriginal= new ZipArchive ( );<o:p></o:p>

           $zipOriginal->open ( $originalFile );<o:p></o:p>

           $xmlWorkbook=simplexml_load_string($zipUpdated->getFromName("xl/_rels/workbook.xml.rels"));<o:p></o:p>

           foreach( $xmlWorkbook->Relationshipas $ele ) {<o:p></o:p>

                 if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") {<o:p></o:p>

                       $currentSheetName= "xl/". $ele["Target"];<o:p></o:p>

                       $updatedSheet=simplexml_load_string ( $zipUpdated->getFromName ( $currentSheetName ) );<o:p></o:p>

                       $origSheet=simplexml_load_string ( $zipOriginal->getFromName ( $currentSheetName ) );<o:p></o:p>

                       $origSheet->sheetData ="";<o:p></o:p>

                       $str=str_replace ( "<sheetData></sheetData>", $updatedSheet->sheetData->asXml(), $origSheet->asXml () );<o:p></o:p>

                       $zipOriginal->addFromString( $currentSheetName, $str );<o:p></o:p>

                 }<o:p></o:p>

           }<o:p></o:p>

           $updatedStrings=simplexml_load_string ( $zipUpdated->getFromName ( "xl/sharedStrings.xml"));<o:p></o:p>

           $zipOriginal->addFromString( "xl/sharedStrings.xml", $updatedStrings->asXML () );<o:p></o:p>

           $zipOriginal->close ();<o:p></o:p>

           $zipUpdated->close();<o:p></o:p>

      }<o:p></o:p>

Jul 22, 2010 at 1:19 AM

The above function works for us as well.  We did come across an issue where named ranges were not being updated when using this function.  A similar approach to updating the worksheet's sheetData element resolved the problem:

 

  $workbookXML = "xl/workbook.xml";
  $origNames = simplexml_load_string($zipOriginal->getFromName($workbookXML));
  $updatedNames = simplexml_load_string($zipUpdated->getFromName($workbookXML));

  $origNames->definedNames = "";
  $nRanges = str_replace("<definedNames></definedNames>", $updatedNames->definedNames->asXML(), $origNames->asXML());
  $boolResult = $zipOriginal->addFromString($workbookXML, $nRanges);

Jul 22, 2010 at 2:08 AM
It worked for many workbooks. But as they got more complicated, I found that I would eventually get corrupt workbooks due to the styles being the same but the strings referencing new style ids. In the end I found that PHPExcel removes all functionality that it does not know with charts just being one of them, causing what seemed like an endless amount of issues when attempting to open the workbook after the save. I just needed to open an existing unknown workbook and insert data into the cells, and not change, or remove anything else in the workbook. My end solution was to use PHPExcel to read the file, evaluate all the named ranges, build rich text cells, and attempt to evaluate all the functions in the cells that I needed to get. Then once the workbook was updated in memory, I used some private code that opened the original workbook. rebuilt the sheet.xml and the SharedStrings.xml with the new updated values. This left the workbook with all the features it started with such as charts and chart sheets and all my problems were gone :)
Jul 27, 2010 at 4:27 PM
Edited Jul 27, 2010 at 4:27 PM
I tried the solution of frank_s, but when I try to open the $updatedFile I get an error that this file is no zip file, even it is saved as zip, at least it seems so, when I looked at the Writer2007 class. Is there anything I missed ? This is the code:
.....
$writer = PHPExcel_IOFactory::createWriter($this->xls, "Excel2007");
...
$sendFile = tempnam(sys_get_temp_dir(), 'sendxls');
$writer->save($sendFile);

$this->updateOriginalFile($file, $sendFile);
....
I am running PHP5.3.2 on a Ubuntu 64Bit. Regards Martin
Sep 27, 2010 at 9:21 PM

@MarkBaker, has this been started yet?

I keep looking for it in the change logs over the last few months haven't seen anything come through. If it is a lot of work maybe you could publish what you have and have the community help out.

Coordinator
Sep 27, 2010 at 9:41 PM
jshaw86 wrote:

@MarkBaker, has this been started yet?

I keep looking for it in the change logs over the last few months haven't seen anything come through. If it is a lot of work maybe you could publish what you have and have the community help out.

 I updated the Work Item with the current status just a few days ago, and have been working on the Excel2007 Chart Writer over the weekend (when my laptop wasn't furry, feline and purring). I hope to have the basis of that writer completed by the end of next weekend, whereupon I'll be checking the whole into SVN. This is very much a first pass and just for Excel2007 initially, with only very basic methods for building/editing charts, and minimal validation (and no documentation until I've written that).

Sep 28, 2010 at 7:05 PM

Great thanks for the update and the Work Item link. Looking forward to this functionality.

Oct 5, 2010 at 11:26 AM

Hi!

And what about situation when I'll create template with table and chart for this table manualy?  Is graph will be changed when I'll set new data to table by means PHPExcel?

Coordinator
Oct 6, 2010 at 9:33 PM
Castro wrote:

And what about situation when I'll create template with table and chart for this table manualy?  Is graph will be changed when I'll set new data to table by means PHPExcel?

The Excel xlsx format holds both formulae and values for all appropriate chart elements (dataseries, labels, title, etc). The model that I've adopted for reading from file reads both sets of values to store in the chart objects. The purpose in storing both is to simplify use of the object. If you call the render() method for the chart, it will generate a gif/jpg file using the stored values (far quicker than processing all the stored formulae. When saving a workbook with charts, then the formulae will take precedence, and the values will be recalculated based on the formula at that point.

I'm providing public methods for setting either values or formulae within user code, and these methods will automatically refresh the other set of stored data. (e.g. if you set a dataseries by calling the setValues() method of the dataseries object, then the method also builds a formula from those values; while if you set the dataseries using the setFormula() method, then the values will be automatically populated from that formula based on the current data in the workbook... unless you explicitly choose to suppress this behaviour. A refresh() method refreshes all values from the stored formulae when called, and this method is always called by the writer before saving the chart data, so it's saved with the correct values at the point of saving.

This means that if you load a template that contains charts, and populate data cells within a worksheet that affect the chart, you'll need to execute a manual refresh() if you plan to render it as an image file within your code (I may reconsider this and force a refresh later, but my current code requires a manual refresh); but if you save the workbook to file (for file formats that support charts such as Excel2007, Excel5, OOCalc and Gnumeric), then the chart data will be refreshed automatically before the save so the file will be populated with the correct chart data.

Note that at present I've implemented charts in the Excel2007 Reader and am working on completing the Excel2007 Writer so that I can get this code integrated with the current SVN code. My target for this was last weekend, but unfortunately I've been distracted by a few things (and I'll be attending the PHPNW 2010 conference this weekend, so it's likely to be another two weeks before I can integrate it all into SVN). Other Readers/Writers will follow: probably OOCalc and Gnumeric first (Readers then Writers), as these are relatively straightforward to code; and I'm hoping that I'll be able to make both of these formats available by the end of December. I'm still trying to understand the way charts are stored for Excel5, so that is significantly more complicated and is unlikely to be available until next year. I'm debating with myself whether I want to target writing graph data as embedded image data for the HTML Writer; and may choose to make this a configurable option, disabled by default... but I'm not making any firm commitments for this.

 

Charting implements quite a complex set of inter-related objects, and will need extensive documentation and examples for creating or modifying charts from within user code, so I'll be taking advantage to do an overhaul of the PHPExcel documentation over the next six months. Charting will get its own dedicated document, and I'll be doing some major rewriting of the core PHPExcel documentation and the calculation engine and function reference document as well.

Jan 17, 2011 at 2:40 AM

Has their been any updates with this? I'm dying for chart/graph functionality or just the ability for my charts to stay in my excel files I edited instead of it disappearing.

Coordinator
Jan 17, 2011 at 1:57 PM

Charts for the Excel2007 format are targetted for the next release, within the next month.

Mar 10, 2011 at 9:14 AM

Thanks mark, we are waiting for a great code.

Thanks a lot for give such a wonderful code.

 

thanks in advance,

thenndral.

Feb 6, 2013 at 3:45 PM
Hi !

Does Charts for Excel2007 format was include in the last release ?

Charts are still missing when i open then write a template with 1.7.8 version.

Thanks
Feb 7, 2013 at 6:07 AM
Edited Feb 7, 2013 at 7:21 AM
..
Feb 7, 2013 at 7:20 AM
Edited Feb 7, 2013 at 7:22 AM
MarkBaker wrote:
Charts for the Excel2007 format are targetted for the next release, within the next month.
Charts are still missing when i open then write a template with 1.7.8 version
Please help
Coordinator
Feb 7, 2013 at 7:33 AM
The read the examples that use charts, and look at my other answer to this question