PHPExcel - OpenXML - Create Excel2007 documents in PHP - Spreadsheet engine

New Post: File upload security? Proving a spreadsheet is really a spreadsheet

koyama — Sat, 04 Jul 2009 23:24:55 GMT

>> Are there any other security tips?

We should probably not call this a security tip. Rather it's about providing good error messages to the user if they upload wrong files accidentally. The above should suffice.

New Post: File upload security? Proving a spreadsheet is really a spreadsheet

cdevidal — Sat, 04 Jul 2009 23:11:23 GMT

I really like 2. better!

Are there any other security tips?

New Post: File upload security? Proving a spreadsheet is really a spreadsheet

koyama — Sat, 04 Jul 2009 22:43:11 GMT

1.
With Excel5 reader, if the file is not a valid BIFF5 or BIFF8 Excel file (covering Excel 5.0, 95, 97, 2000, XP, 2003 formats), then it will throw an exception.

Therefore you can do like this:

$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objReader->setReadDataOnly(true);

try {
    $objPHPExcel = $objReader->load($_FILES['uploadedfile']['name']);
} catch (Exception $e) {
    // something went wrong
    echo $e->getMessage();
    exit;
}

It may not be good to discard uploaded files just because file extension is not as expected. Users can accidentally alter file extension to something other than xls. The above will read the file even with wrong file extension.

2.
You may instead want to use the below method. It works similarly, but will automatically resolve file type and invoke the correct reader. You probably also want to accept Excel 2007 (xlsx files). With the below code you don't have to worry whether users have saved as xlsx or xls (or even csv).

try {
    $objPHPExcel = PHPExcel_IOFactory::load($_FILES['uploadedfile']['name']);
} catch (Exception $e) {
    // something went wrong
    echo $e->getMessage();
    exit;
}

The drawback with this method is that you cannot use setReadDataOnly(true), but with recent improvements in speed (since PHPExcel 1.6.7), there is not much gain anymore using that so I wouldn't worry about that.

Download latest source code here:
http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

New Post: File upload security? Proving a spreadsheet is really a spreadsheet

cdevidal — Sat, 04 Jul 2009 22:05:32 GMT

Has anyone written any code which can verify that the file uploaded really is an Excel document? I have this but I don't know if it's enough:

if ( strToLower ( $path_info['extension'] ) != 'xls' )

{

echo "You did not upload an Excel 2000/XP/2003 (.xls) file, please go back and try again!<br />\n";

end_HTML ( );

}

$objReader = PHPExcel_IOFactory::createReader('Excel5');

$objReader->setReadDataOnly(true);

if ( ! $objPHPExcel = $objReader->load($_FILES['uploadedfile']['name']) )

{

echo "There was a problem reading the Excel file, please check the format and contents of this file and try again!<br />\n";

end_HTML ( );

}

Source code checked in, #28100

maartenba — Sat, 04 Jul 2009 07:39:58 GMT

Export SVN to CodePlex

New Post: Excel5 Sheet->copy() memory usage

robdann — Sat, 04 Jul 2009 06:40:23 GMT

Brilliant, thanks Koyama!

New Post: Excel5 Sheet->copy() memory usage

koyama — Sat, 04 Jul 2009 06:11:49 GMT

Ok, I see the problem. If you are copying sheet from one file to another, instead of this:

$objPHPExcel->addSheet($copiedSheet);

you need this:

$objPHPExcel->addExternalSheet($copiedSheet);

New Post: Excel5 Sheet->copy() memory usage

robdann — Sat, 04 Jul 2009 05:56:32 GMT

Trying the following code I lose all styles:

<?
ini_set( 'memory_limit', '40M' );
set_include_path(get_include_path() . PATH_SEPARATOR . '.');
require_once("./PHPExcel/IOFactory.php");

$objPHPExcel = new PHPExcel();
$objPHPExcel->removeSheetByIndex(0);

$reader = PHPExcel_IOFactory::createReader('Excel5');
$excel = $reader->load('Template.xls');
$sheet = $excel->getActiveSheet();
$copiedSheet = $sheet->copy();
$objPHPExcel->addSheet($copiedSheet);

header("Content-Type: application/x-excel5");
header("Content-Disposition: attachment;filename=Template.xls");
header("Content-Transfer-Encoding: binary ");

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
?>

New Post: Excel5 Sheet->copy() memory usage

koyama — Sat, 04 Jul 2009 05:46:45 GMT

This is strange. And you were getting styles before? Can you try to do a plain read / write of your template workbook and see if you lose styles?

Closed Issue: Excel5 reader OLE read failure with small Mac BIFF5 Excel files

koyama — Sat, 04 Jul 2009 05:40:17 GMT

From thread:
http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=60074

PHPExcel_Reader_Excel5 fails completely with attached file.

There is a similar issue for the php-excel-reader project on Google Code:
http://code.google.com/p/php-excel-reader/issues/detail?id=25

PHPExcel_Reader_Excel5 and php-excel-reader are based on the same library:
https://sourceforge.net/projects/phpexcelreader/

so it is not surprising that they share the same bug.
Comments:

Fixed in SVN

New Post: Excel5 Sheet->copy() memory usage

robdann — Sat, 04 Jul 2009 05:37:58 GMT

Hi Koyama,

I've updated to the latest source code and without changing my code I'm not getting the memory error anymore, but I don't get any styles either - is there an option I need to set to enable styles now?

Thanks,

Rob

Commented Issue: Excel5 reader OLE read failure with small Mac BIFF5 Excel files

koyama — Sat, 04 Jul 2009 05:29:09 GMT

There is already a bug report + fix for the PHP-ExcelReader project at SourceForge here:
https://sourceforge.net/tracker/?func=detail&aid=1058233&group_id=99160&atid=623332

Bug is caused by root entry labeled by "R" in BIFF5 on Mac instead of "Root entry".

Closed Feature: Eliminate dependency of an include path pointing to class directory

koyama — Sat, 04 Jul 2009 00:10:29 GMT

From thread:
http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=61324

Having to set up an include_path pointing to the directory containing PHPExcel.php is not very convenient. It has been noted that an include path is not strictly necessary.

For example, if one replaced lines like this in all the PHPExcel class files:

require_once 'PHPExcel/Cell.php';

with absolute paths

require_once dirname(__FILE__) . '/PHPExcel/Cell.php';

then the library will function without the include_path.

What are advantages / disadvantages?
Comments:

Fixed in SVN

Commented Feature: Eliminate dependency of an include path pointing to class directory

koyama — Sat, 04 Jul 2009 00:10:14 GMT

- Class files updated
- Examples in the "Tests" folder updated
- One paragraph removed in "PHPExcel developer documentation" 2.2 "Installation instructions"

Closed Issue: iconv notices when reading hyperlinks with PHPExcel_Reader_Excel5

koyama — Fri, 03 Jul 2009 20:12:10 GMT

PHPExcel_Reader_Excel5 in 1.6.7 uses mbstring if it is available. If mbstring is not available it uses iconv.
This has been changed in PHPExcel 1.7.0 because some character sets no Mac are not supported by mbstring. iconv is the preferred library for handling strings.

There is a bug in PHPExcel_Reader_Excel5 when reading hyperlinks. One sees notices like these when iconv is being used:

Notice: iconv() [function.iconv]: Detected an incomplete multibyte character in input string in C:\xampp\htdocs\_phpexcel\PHPExcelsvn\Classes\PHPExcel\Shared\String.php on line 259

Test:
----
$reader = PHPExcel_IOFactory::createReader('Excel5');
$excel = $reader->load('hyperlink.xls');

Expected result:
----
File is read with no problems

Actual result:
----
Notice: iconv() [function.iconv]: Detected an incomplete multibyte character in input string in C:\xampp\htdocs\_phpexcel\PHPExcelsvn\Classes\PHPExcel\Shared\String.php on line 259

(this happens in PHPExcel 1.6.7 when mbstring is not present)
Comments:

Fixed in SVN

Created Issue: iconv notices when reading hyperlinks with PHPExcel_Reader_Excel5

koyama — Fri, 03 Jul 2009 20:10:06 GMT

Commented Feature: Eliminate dependency of an include path pointing to class directory

maartenba — Fri, 03 Jul 2009 16:36:45 GMT

Looks perfect!

Commented Feature: Eliminate dependency of an include path pointing to class directory

koyama — Fri, 03 Jul 2009 16:16:01 GMT

I did a quick test and it looks ok, Maarten. (except there should be an extra slash at the beginning: '/../../../etc/etc/etc/')

Attached sample patch for PHPExcel/Worksheet.php

If ok, we can assign this to me and I will update all files.

Commented Feature: Eliminate dependency of an include path pointing to class directory

maartenba — Fri, 03 Jul 2009 12:27:00 GMT

Current require path styles is based on Zend Framework.

We can perhaps go for something like this:

if(!defined('PHPEXCEL_ROOT')) {
define('PHPEXCEL_ROOT', dirname(__FILE__) . '../../../etc/etc/etc/');
}
require_once PHPEXCEL_ROOT . 'xxx.php';

The define will of course be different for each file, but should get the right root folder every time.

Commented Issue: Excel Reader 2007 problem with "shared" formulae when "master" is an error

MarkBaker — Fri, 03 Jul 2009 07:32:49 GMT

Excel Reader 2007 problem with "shared" formulae when master is an error

The Excel 2007 reader identifies shared formulae and stores details of the first (master) formula in an array ($sharedFormulas). Subsequent references to this shared formula have a formula value of an empty string, and the value read from the $sharedFormulas array is used to populate the actual formula for these cells.
When the calculation of this formula returns a valid result, this is not a problem.
However, if the calculated value of the first (master) is an error, so $cellDataType is 'e', then the formula is not written to the $sharedFormulas array. Only when the first _valid_ (non-erroneous) result of a shared formula is encountered is it written to the $sharedFormulas array, but the formula value in this case is an empty string, because this cell isn't the _real_ master.

This can be fixed by commenting out the special logic for erroneous cells between lines 592 and 600 of the Excel2007.php reader; and this fix appears to work. However, I don't know if this simple solution is likely to have any side effects.
Comments: ** Comment from web user: MarkBaker **

Note that this applies to all types that are explicitly processed by the case statements for switch($cellDataType) in the load() method. e.g. As with results that have a $cellDataType of error, if $cellDataType is 'b', and a "master" shared formula gives a boolean result, the boolean case logic will be processed, ignoring the shared formula.

The fix will be rather more complex than simply commenting out a few lines.