Passing Too Many Arguments/Characters to the SUM() Function

Topics: Developer Forum
Mar 23, 2010 at 7:17 PM

I've been noticing an issue the past couple of weeks with some of the reports I've put together for the college.

It was pretty similiar to the following issue:

http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=42854

Excel found unreadable content in 'myreport.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

I finally noticed that the formulas at the very end of the document (which contained some grand totals) were converted into straight numbers (no more formula) after I said "yes" to repairing the document.

I finally figured out that the reason why I was having such problems was because I was reaching Excel's 255 parameter (or 1024 character) limit for the SUM() function.

In this case I was unable to use a range of cells, because the cells I was trying to sum were not contiguous within the file.

I did some testing, and it looks like Excel would be able to handle everything just fine if I were to use nested SUM()s.

So I modified my code to do something like this:

            $requestingGrandTotal = '=SUM(SUM(';
            $count = 1;
            foreach($totalsPositionsArray as $position)
            {
                $requestingGrandTotal .= $position['requesting'];

                if ($count === 50) {
                    $requestingGrandTotal .= '), SUM(';
                } else {
                    $requestingGrandTotal .= ', ';
                }

                if ($count === 50) {
                    $count = 1;
                } else {
                    ++$count;
                }
            }
            $requestingGrandTotal .= '))';

And that appears to have solved that problem (at least until I reach a new upper limit).

This might explain what is happening in some particular cases where that Excel Error Message is cropping up.

Coordinator
Mar 25, 2010 at 12:26 AM
Edited Mar 25, 2010 at 12:29 AM

Because a workbook can be written to a number of different output formats with different limits, PHPExcel itself doesn't check for those limits (especially as it would be a big overhead). When writing to Excel5 or Excel2007 formats, you need to ensure that you work within the limits yourself. If writing to HTML, or other formats where the Excel limitations aren't relevant, you are restricted only by PHP's memory restrictions.

  Excel5 Excel2007
Worksheet size 65,536 rows x 256 columns 1,048,576 rows x 16,384 columns
Length of formula contents 1,024 characters 8,192 characters
Arguments in a function 30 255
Nested levels of functions 7 64