phpexcel-将它与excel模板一起使用(图表丢失)php [英] phpexcel - using it with excel template (chart goes missing) php

问题描述

我尝试将 phpexcel 与我自己的模板文件一起使用. phpexcel加载文件并将数据写入到某些单元格A2，A3，A4中，例如..并使用新数据打开输出文件.

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.

我的模板文件具有内置的图表..我要phpexcel要做的就是在单元格中填充值，并且不要触摸图表.并且，打开新文件. (请注意，我不想在代码中创建图表.我希望图表以与我最初创建的格式相同的格式预先存在于模板中).只有数据应该更新.

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.

而且，我从 http://phpexcel.codeplex.com/discussions/397263 <中找到了以下代码/a>

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);

上面的代码在excel 2010中有效，现在可以使图表保持完整……但是，当我尝试使用文件类型"Excel5"时，它仍然不起作用.

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.

它引发以下错误:

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

请提供一个简单的解决方案，让我的模板文件与.xls和.xlsx一起使用，并且模板文件中的所有原始图表都应保持不变.我不希望图表从结果文件中将其删除.我也不打算使用phpexcel代码创建图表. (为什么当excel可以为您完成所有工作时为什么要编写不必要的代码).

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).

我想要最简单的方法就是只使用模板中的所有内容，并使用新数据填充单元格.而且，模板中的现有图表会自动生效.我不想编写不必要的代码，而我可以放心地依靠excel模板和图表功能.

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.

请帮助.

推荐答案

有一个很好的理由:

图表仅在核心中实现，并且此时针对Excel2007读写器，因此所有其他读写器将忽略图表，将其视为根本不存在.目的是在来年向其他读者/作家推广图表.

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.

编辑

我从您的评论中看到，您根本不了解PHPExcel的工作原理，因此我有很多解释要做.

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不是不是用于编辑"工作簿文件的库:您不是在使用PHPExcel更改文件，而是在更改 可以的PHPExcel对象 ，然后可以将 写入文件.

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是电子表格的内存表示形式，其中不同的组成对象(如工作表，单元格，图像，样式等)均表示为PHP对象.

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.

PHPExcel Reader解析电子表格文件，并从已被编程识别的文件中加载所有组件，并从这些文件组件中创建适当的PHPExcel核心对象.如果没有等效的PHPExcel Core对象(例如数据透视表)，则无法加载"该文件组件；如果尚未对加载程序进行编程以识别文件组件，则无法加载该文件.在这些情况下，文件中的那些元素将被忽略.阅读器完成工作后，将存在一个PHPExcel对象，并且电子表格文件已关闭并被遗忘.

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.

当内存中存在一个PHPExcel Core对象时，您可以使用一组方法来对其进行操作和更改，添加，修改或删除Core元素；但是这些仅在构成PHPExcel Core的工作表，单元格，样式对象的内存中"集合上起作用.核心的存在是在不知道已经从文件加载或使用PHP"new PHPExcel()"语句创建的情况下进行的；它不会以任何方式更改文件.

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.

在编写时，反之亦然.每个Writer都采用PHPExcel核心对象，并将它们以适当的格式(Excel BIFF，OfficeOpenXML，HTML等)写入文件.像读者一样，每个作者只能编写经过编程编写的那些PHPExcel Core对象.如果尚未编程编写(例如，图表)，则PHPExcel Core中定义的任何图表都将被忽略，因为该编写者根本还不知道如何编写它们.同样，将忽略PHPExcel Core中存在的，写入的文件格式不支持的功能(例如CSV Writer的单元格样式).

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.

因此，为了支持电子表格功能(例如图表)，必须对PHPExcel Core对象集合进行修改，以为不同的Reader提供这些元素的内存中"表示，和已编程以识别它们正在加载的文件中的那些元素，并将其转换为适当的PHPExcel Core对象，和用于编程不同的Writer以将PHPExcel核心表示形式转换为适当的文件表示形式.

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.

每个读取器和每个写入器都需要分别编程.图表是一个相对较新的功能，仅在1.7.7版本中添加到PHPExcel Core中，此时，仅对Excel2007格式的Reader和Writer进行了编程以识别图表元素. 尽管开发人员打算将其扩展为也涵盖其他格式，但并不会自动创建必要的代码.对每个单独的读取器和写入器进行编程需要花费时间和精力.尽管Excel2007 Reader和Writer的图表代码现在已经稳定到不再被认为是实验性"的程度，并且开发重点正在转向在Excel5 Reader和Writer中编写图表处理所需的代码，但这是可行的尚未完成.

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.

这篇关于phpexcel-将它与excel模板一起使用(图表丢失)php的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！