ARCHIVED
License: Other
PHPExcel last version, 1.8.1, was released in 2015. The project was officially deprecated in 2017 and permanently archived in 2019.
The project has not be maintained for years and must not be used anymore. All users must migrate to its direct successor PhpSpreadsheet, or another alternative.
PHPExcel is licensed under LGPL (GNU LESSER GENERAL PUBLIC LICENSE)
Hello,
I define a model document without option 'Display zero in null value cells' (see screen.jpg).
I load this model document.
I write this document.
Option I unchecked is now checked.
Could you correct it please ?
Thank you
PS: Where can I post my atached file ?
May I write my issue on PHPExcel old website ?
Some old files created by @maartenba could be deleted. No ?
phpdoc-home.ini
phpdoc.ini
setpath.bat
start_shell.bat
Documentation/assets/*
Build/old/*
A file is duplicate
Diff between README.md and readme.md
And others which contains samples and tests for JAMA
Classes/PHPExcel/Shared/JAMA/examples
Classes/PHPExcel/Shared/JAMA/tests
As discussed with @MarkBaker on Twitter, I think it's interesting to implement in PHPExcel autofilter expressions.
If expressions are defined, autofilter is enabled, and some rows are hidden.
Twitter :
Progi1984 : Is that some commands in #phpexcel for adding expressions in autofilter ?
Mark_Baker : There's nothing for adding the actual autofilter selections, just for defining the range they can be applied to
Mark_Baker : I know how to add it to Excel2007, but not Excel5... but autofilter also manipulates row show/hide state
I would like to avoid any writing on disks and rather have the result returned, so I can process it, send HTTP headers and the document itself, or for example attach it to an e-mail.
The writer implementations of PHPExcel only offer a save() method, it would be nice to have a more generic generate() method, where save() would only be a wrapper that writes the result to disk after generating.
I'd love to include PHPExcel as a submodule in another project I'm working on, but I only need the Classes directory. There's no simple way to include just the Classes subdirectory as a submodule. Is it possible to set up a way to do this? Another branch or repository?
Hello,
I'm trying to insert images, one per row always in the same column.
The excel file is created but when opening the excel says this corrupt and if i recover, pressing yes, the file opens and only shows me the first picture.
This my code to insert the images (excel 2007):
....
$gdImage = imagecreatefromjpeg($this->container->getParameter('product_upload_front_path').$image->getFile());
$objDrawing = new \PHPExcel_Worksheet_MemoryDrawing();
$objDrawing->setName($product->getName());
$objDrawing->setDescription($product->getName());
$objDrawing->setImageResource($gdImage);
$objDrawing->setRenderingFunction(\PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
$objDrawing->setMimeType(\PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
$objDrawing->setHeight(160);
$objDrawing->setCoordinates('F'.$key);
$objDrawing->setWorksheet($sheet);
....
But if i do not use the setCoordinates all the images are inserted, but they are on top of each other in the upper left corner of the sheet.
Can someone help me?!!
Thanks in advance,
Paulo Dias
PHPExcel verrsions: 1.7.6 and 1.7.7
Issue:
The phone number format (000") "000-0000 is not be applied properly.
A cell with value 5555555555 should return (555) 555-5555
but instead the following value is returned: (5555555555) 5555555555-5555555555
Code:
The style code is processed in the PHPExcel/Style/NumberFormat.php file in the toFormattedString() method.
IOFactory tries to create a reader by looking at the file extension. If it guesses the reader incorrectly it walks through all the options.
Howeve this liner:
if ($reader !== $extensionType) {
acts as a check to ignore the original guess to save trying it twice. But if the extension type isn't found by the switch statement, both $reader and $extensionType will be null and it'll never attempt to read with any of the other readers.
I've been having problems recently with converting Unix timestamps to Excel dates, whereby the date stored in Excel is one hour before what it should be (e.g. 01/06/2012 00:00 becomes 31/05/2012 23:00). On examining the code, I noticed that the PHPToExcel function in the PHPExcel_Shared_Date class sets the timezone to UTC before converting the date. My server is in BST (GMT+1) and the timezone is set to Europe/London everywhere else.
Is there a good reason why the function overrides the default timezone? I'm tempted to comment out the lines which do this because it's causing a major problem with the reports we generate, but I don't know whether that will affect other parts of PHPExcel.
Hello,
I have downloaded this project and followed the install.txt instructions.
After tested some examples from Tests folder with success, i decided to execute runall.php and that give me a blank page in processing. I wait a while and as nothing happens i close the page.
After this i try to go to mywebsite.com and get "500 Internal Server Error" .
I contact my hosting provider and they said to me that my account reaches 1GB of memory used, because the process runall.php was calling in a infinite loop, the 01simple.php. At the time of the phone call, it was about 172 processes 01simple.php running.
Sorry for my English, but is not my natural language.
Best regards
Hi,
Subj.
Is it possible to make this?
Thanks.
I have tried using both the built-in HTML Writer class and generating the table myself just by looping over the $sheetData, and I get the same results.
When I set $objReader->setReadDataOnly(false) - meaning that I want PHPExcel to read also the styles, etc, and then write the table to HTMl using HTML Writer class, I do get the table with correct styling, but there are always empty columns appended to the table. With some files it's 1 columns, others have over 20 empty columns.
When I set $objReader->setReadDataOnly(true) - I only get the columns that have data in them, which is great, but then again, I will not get any styling at all.
Note that I have also tried selecting those "extra" columns in Excel and doing Edit->Clear->All and saving the file again, but that does not seem to help.
UPDATE: It seems that when I select "extra" columns in Excel and do Edit->Delete, and save, then PHPExcel renders the HTML table correctly. Any idea why I need to do that? There was no content at all inside those columns.
Hi there, happy to see you on GitHub!
I am not seeing stable version tags. Am I doing something wrong?
Hi there,
I was just wondering why there are no version tags used anywhere. It would be really helpful to have those in order to pull an official version.
Ta, mano
Classes/Worksheet.php line 2781
When cloning a worksheet, the __clone() function finds _autoFilter. However, _autoFilter has a _worksheet variable within it, which causes iteration leading to a drastic memory and cpu usage as the number of worksheets increases.
Changing line 2768 to
if ($key == '_parent' or $key == '_autoFilter') {
fixes the problem, though I don't know how that would affect the auto filter functionality when cloning.
Also note: a similar situation exists in NamedRange.php __clone(). The named range object has a _worksheet variable. Therefore, cloning the named range has an exponential memory and cpu affect as the number of worksheets increases. I got around this in my app by just creating a new NamedRange object with the 3 initial arguments rather than actually cloning the NamedRange.
I believe a fix to both of these issues could avoid trying to serialize and unserialize any _worksheet data members when cloning.
koyama[codeplex] Feb 5 2010 at 1:07 AM
Hello,
As far as I know the conditional formatting is not available in Excel 5.
Am I wrong ?
Will it be in the feature list soon ?
Regards,
Nicolas
pear remote-info pearplex/PHPExcel
Latest 1.7.6
Installed 1.7.6
Package PHPExcel
License LGPL
Category Default
Summary PHP Excel classes
Description Project providing a set of classes for the PHP
programming language, which allow you to write
to Excel 2007 files and read from Excel 2007
files.
Should be 1.7.7 right?
I create an Excel files save with the Writer Excel 5 and the auto-filter is not apply. If I use the Excel 2007 writer no problem
Best regards,
Marc
Original link : http://phpexcel.codeplex.com/workitem/9605
In Excel "Cool" = "COOL"
In PHPExcel "Cool" <> "COOL"
As such, customer had formula in workbook: =IF(A2="NONE",True,False)
In Excel, when A2 = "None" the formula returns True
In PHPExcel the calculated value for the formula cell returns False
I did a cursory scan to try and find the culprit lines in the codebase but the Calculation engine is pretty dense so I figured I'd just toss this one over the wall. Maybe it is this way on purpose due to behavior of other spreadsheet apps - maybe it needs to be a condition just for COMPATIBILITY_EXCEL?
Easy enough to work around (made the customer match case in the workbook values), but I would think a guiding goal for PHPExcel would be to have consistency with Excel results.
Thanks!
At the moment, PHPExcel doesn't support array functions.
The first step to rectifying this is a change to the PHPExcel_Cell object to maintain additional information for array formula. The cell containing the actual array formula needs to maintain a range of cells to which that formula is applied; other affected cells need to maintain a link to the array formula "master" so they can access the formula and work out their offset to that cell.
This will affect the values stored in the following attributes:
/**
* Value of the cell
* The actual cell value for cells that contain a simple value, or the result of the cell
* formula calculation if this cell contains a formula, or is part of an array formula.
*
* @var mixed
*/
private $_value;
It will add the following new attributes:
/**
* Formula of the cell
* NULL for cells that contain a simple value, or where the result is the result of an
* indirect array formula ($_arrayFormulaReference will be NOT NULL in this circumstance);
* or the formula itself, if this cell contains an actual formula
*
* @var mixed
*/
private $_formula;
/**
* Array Formula reference
* If this cell contains an array formula then this will contain the range of cells to which
* that formula is applied; if the cell is the indirect result of an array formula in a
* different cell, then this value will be a simple reference to the cell that holds the
* actual formula; if the cell is a simple value cell, then this will be a NULL value.
*
* @var mixed
*/
private $_arrayFormulaRef;
The following attribute will be dropped from the cell:
/**
* Attributes of the formula
*
*/
private $_formulaAttributes;
as its purpose is superceded by the other changes to the cell structure, but which allow rather for flexibility when working with array functions.
As examples for the changes of attributes related to formulae and array formulae:
Cell A1 contains 1, cell A2 contains 2, cell A3 contains 3, cell A4 contains 4
$_value $_formula $_arrayFormulaRef
-------- ---------- ------------------
Cell C1 "Result" NULL NULL Simple Value, in this case a string
Cell C2 30 =2*3*5 NULL Simple Formula
Cell C3 6 =2*3*A1:A4 C3:C6 Array Formula
Cell C4 12 NULL C3 Result dependent on Array Formula
Cell C5 18 NULL C3 Result dependent on Array Formula
Cell C6 24 NULL C3 Result dependent on Array Formula
To facilitate getting and setting formulae and array formulae, a number of new methods need adding, and other existing methods need modifying.
First and foremost, getFormula() and setFormula() methods will be added as the primary methods for setting and retrieving formulae for cells; isFormula() and isArrayFormula() will identify whether a cell contains a formula or an array formula or not; getArrayFormulaRange() will identify the cell range for an array formula; getValue() will be modified to retrieve calculated values; while getCalculatedValue() will be deprecated; setValue() and setValueExplicit() will still perform the same function as at present, but will be modified to handle storing formulae in the new properties where the data passed is identified as a formula.
These changes to the cell structure and methods will start with version 1.7.9, so deprecated methods will be flagged as deprecated in the 1.7.8 release, but retain their existing functionality until the 1.7.9 release.
With commit 55aa7b8, I get the following just opening and saving an existing XLS with PHPExcel...
Fatal error: Uncaught exception 'PHPExcel_Exception' with message 'Cell coordinate can not be zero-length string' in ...phpexcel/Classes/PHPExcel/Cell.php:528
Stack trace:
#0 ...phpexcel/Classes/PHPExcel/Cell.php(584): PHPExcel_Cell::coordinateFromString('')
#1 ...phpexcel/Classes/PHPExcel/Writer/Excel2007/Workbook.php(364): PHPExcel_Cell::absoluteCoordinate('')
#2 ...phpexcel/Classes/PHPExcel/Writer/Excel2007/Workbook.php(280): PHPExcel_Writer_Excel2007_Workbook->_writeDefinedNameForAutofilter(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), 0)
#3 ...phpexcel/Classes/PHPExcel/Writer/Excel2007/Workbook.php(83): PHPExcel_Writer_Excel2007_Workbook->_writeDefinedNames(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel))
#4 ...ph in ...phpexcel/Classes/PHPExcel/Cell.php on line 528
The title is enough explicative.
Link : https://developers.google.com/google-apps/spreadsheets/
Tried to load a 9000 row spreadsheet (biff8) 1.1MB in size. Used
$objReader = new PHPExcel_Reader_Excel5();
$objPHPExcel = $objReader->load($inputFileName);
from exampleReader02.php
with
ini_set('max_execution_time', '600');
the output is normal, but without the the array output... no error message... seems to time out after 25 secs.
cut the file down the file to 4000 records and that seemed to work ok... prints the data array
ubuntu 12.04, nginx 1.2.3, php5-fpm 5.3.10,
is there a built-in timeout or memory size limit ?
Hello there!
I try to read a XLSX-File with following code:
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
/** Create a new Reader of the type that has been identified /
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/ Load $inputFileName to a PHPExcel Object **/
$objPHPExcel = $objReader->load($inputFileName);
It works fine for Excel2007 files if I create them with Excel.
Now I want to read XLXS files from Suunto movescount.com. You can download XLSX-Files from movescount and save them on harddisk. If I want to read them, I have to open them first with Excel and then save again.
If I do not open with Excel and save again, I can not read these files. Has anybody a solution or idea?
Thanks,
Dominik
Hi,
This is just a small thing but the instructions currently state:
"Copy the "Tests" folder next to your "Classes" folder from above so you end up with:"
The files you then reference now seem to be housed in 'Examples' I presume it was renamed?
After this comment (#5 (comment)) by @MarkBaker, it seems that PHPExcel doesn't read autofilter in Excel5 Reader.
Hi all,
after taking a look at examples, I realize that the examples do not reflect all the features of PHPExcel.
My idea is : one feature = one sample...
In each sample, we generate in each writer (in addition, that permits to see in each writer if a feature lacks).
What do you think about that @MarkBaker & @maartenba ?
I can't convert the generated file-format directly to gSheet - only if opened once with MS-Excel and saved again...
When comparing the extracted folders with UltraCompare, they look quite different... please see the third answer:
I've recently upgraded from 1.7.4 to 1.7.7 and I've found that some Excel 2007 spreadsheets can no longer be loaded by PHPExcel. The code I'm using to open each file is as follows:
$file['file_path'] = '/path/to/file.xlsx';
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
if (!$objReader->canRead($file['file_path'])) {
die("Cannot read file\n");
}
$objPHPExcel = $objReader->load($file['file_path']);
if (!is_object($objPHPExcel)) {
die("Cannot load file\n");
}
$worksheet = $objPHPExcel->getActiveSheet();
if (!is_object($worksheet)) {
die("Cannot read active sheet in file\n");
}Under 1.7.4, the file is loaded without any problems and I can go on to parse it. However, in 1.7.7 I get the following error:
PHP Notice: Undefined offset: -1 in /home/dfi/www/build.directfleetinsurance.co.uk/includes/phpexcel/Classes/PHPExcel.php on line 191
Notice: Undefined offset: -1 in /home/dfi/www/build.directfleetinsurance.co.uk/includes/phpexcel/Classes/PHPExcel.php on line 191
Cannot read active sheet in file
Is there anything which has changed between the versions which might have caused this? The files haven't changed in structure, and if I revert back to 1.7.4 loading the files works again.
Unfortunately I can't upload the file anywhere as it contains sensitive client data, but I'm happy to try any suggested code changes against it.
I could just revert to 1.7.4 as that works, but I'd prefer to keep in step with the stable release if possible. Writing to Excel 2007 spreadsheets doesn't seem to be affected by the upgrade.
Hi Mark,
I worked on latest github code.
I used PHPExcel_Worksheet::insertNewColumnBefore('E',1) function.
I have an error in PHPExcel_ReferenceHelper::insertNewBefore on line 315.
$pSheet->getAutoFilter()->getRange() returns NULL instead of ''.
I think problem derived from PHPExcel_Worksheet constructor line 370.
$this->_autoFilter = new PHPExcel_Worksheet_AutoFilter(NULL, $this); must be replaced by
$this->_autoFilter = new PHPExcel_Worksheet_AutoFilter('', $this);
I hope this correction will help you.
Looking at the extractAllCellReferencesInRange() in PHPExcel. This accepts a range definition and returns a list of the cells in that range. It also supports multiple lists of ranges (e.g. "A2:C4,E6:G8") and range intersections (e.g. "A2:C4 E6:G8"). It's main purpose is for handling cell merges and range-based options such as validation or conditional formatting for blocks of cells.
However, when multiple lists of ranges return intersections, it will return duplicate entries (adding extra overhead to the merging or the validation/conditional formatting setting; and when used with the intersection operator, it doesn't return the intersection but every cell (including duplicates again).
This behaviour should definitely be modified to return only unique references; and also to implement the intersection operator correctly.
This is such a minor bug that I almost don't want to bring it up:
On page 6 of the PHPExcel Developer Documentation, there's a minor typo in the URL for the English PHPExcel tutorial in section 2.4.3 Tutorials. The URL is listed as http://openxmldeveloper
That website actually has a .org on the end.
The proper URL is http://openxmldeveloper.org
With this pull request (#21), the sheet view is defined in Excel2007.
Lacks support in Excel5 (with Page Layout View Settings).
Codeplex pages :
Add a comparison table between all writers & readers
A current problem with setReadDataOnly(TRUE) is that it only reads the raw data values from the cells making it impossible to identify date cells or percentage values. I'm proposing to change the behaviour of this flag so that it will ready the format mask for cells as well, allowing dates, etc to be identified... the affects of this flag do trip people up with this side effect.
Styling features such as font, cell borders and fill will still be ignored.
The question is, should setReadDataOnly(TRUE) also read confitional formatting that changes the format mask of a cell? Should it read merge groups?
So far I maintained a copy of PHPExcel on packagist but this is not a good practice, please add your package to packagist.org, you can see the bundle configuration in my package, it should be pretty complete https://packagist.org/packages/CodePlex/PHPExcel
using 1.7.7 and same trying 1.7.8 RC3-3
I get a cell with a pourcent number format style
This cell get a conditionnal style too.
the background color change is cell is > or < 0
Conditionnal works great but after the conditionnal apply the number format is loose. :(
What about the good pratice about that ?
Thanks a lot for your job
ajouno
Hi, the same formula works with 1.7.7 version but not with a fresh downloaded (yesterday) zip from the development branch.
This is the formula:
=COUNTIFS(ALL!$B:$B,"Full Paper*",ALL!$F:$F,A2)
It happens on a PHPExcel 2007 Writer object calling the save method despite I've called: $oPhpExcelWriter->setPreCalculateFormulas(false);
Thanks for any help
Antonino
In https://github.com/PHPOffice/PHPExcel/blob/develop/Classes/PHPExcel/CachedObjectStorageFactory.php#L103-104 the dirparameter of the DiscISAM method is set to NULL.
In https://github.com/PHPOffice/PHPExcel/blob/develop/Classes/PHPExcel/CachedObjectStorageFactory.php#L199-204 only parameters for which isset() is true are set. isset(NULL)is false, so the dir parameter can never be set.
In https://github.com/PHPOffice/PHPExcel/blob/develop/Classes/PHPExcel/Writer/Excel5/Parser.php#L948-950 and https://github.com/PHPOffice/PHPExcel/blob/develop/Classes/PHPExcel/Writer/Excel5/Parser.php#L982-988 the row index is checked to be not greater than 16384. As we now only use BIFF8 this should be changed to 65536.
The title is enough explicative.
Link : https://developers.google.com/google-apps/spreadsheets/
Hi,
In .../PHPExcel/Classes/PHPExcel/Reader/Excel2007.php
The variable $docSheet is within the scope of
foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
...
}from line 738 to line 1714.
But in line 1749 we have:
if ($worksheet = $docSheet->getParent()->getSheetByName($range[0])) {
$extractedRange = str_replace('$', '', $range[1]);
$scope = $docSheet->getParent()->getSheet((string)$definedName['localSheetId']);
$excel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $worksheet, $extractedRange, true, $scope) );
}So $docSheet is not defined there, and the load of the Excel file fails. Can't provide an example because the excel is big and has sensitive data. But I think with this info you can see the issue and fix it.
Thanks in advance!
Fatal error: Call to a member function cellExists() on a non-object in Calculation.php on line 3243
culprit line is: if ($pCellParent->getParent()->getSheetByName($matches[2])->cellExists($cellRef)) {
The sheet is expected to exist, which is not always the case for example when only loading certain named sheets using setLoadSheetsOnly()
Fix looks as simple as changing line to:
$cellSheet = $pCellParent->getParent()->getSheetByName($matches[2]);
if ($cellSheet && $cellSheet->cellExists($cellRef)) {
Not sure if preferable to throw an error (can errors be ignored?) or not instead of letting it fall through the null assignment.
Hi there;
I just tried to download the documentation file called "PHPExcel User Documentation - Reading Spreadsheet Files.doc",
and it downloads with a *.txt.doc extension.
Here is the file name:
PHPExcel+User+Documentation+-+Reading+Spreadsheet+Files.doc.txt
To open this document, I have to rename the file in windows explorer and remove the .txt from the end of the filename. Once I do that, the document opens in Word just fine.
I know PHPExcel does support Pivot tables and other stuff, but would it be possible to add a way to modify only a single ("data") sheet and leave the others "untouched"?
$pStringTable is not used anywhere in Writer/Excel2007/Worksheet::_writeCell() function and is not needed as a parameter.
Hi, I have 2 php applications, that ruins on diferentes hostings
The application is the same ( 2 clients), so I only copy from one to another, usin my PC as intermediate.
On one hosting the excel looks great, on other the downloaded excel could'n be read by excel, It tell me that the formar is incorrect.
I assume thta the problem is the encoding.
Is there any way to check this ?
Best Regards
HI,
It could be very useful to add a composer config and register it on http://packagist.org/
I believe I have found a bug in InsertNewBefore()
Classes/PHPExcel/ReferenceHelper.php line 315
getRange() is returning null for an Excel 2007 spreadsheet I am opening. The !== '' leads to "Cell coordinate can not be zero-length string" error.
The spreadsheet I am working with does not have any auto filters.
Simply, the commit 660e7e7 breaks the OpenDocument Spreadsheet (ODS) reader.
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
Django
The Web framework for perfectionists with deadlines.
Laravel
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft
Open source projects and samples from Microsoft.
Google
Google ❤️ Open Source for everyone.
Alibaba
Alibaba Open Source for everyone
D3
Data-Driven Documents codes.
Tencent
China tencent open source team.