Edit excel file with PHPExcel

Question

I'm trying to use PHPExcel to edit an excel file, but my end file turns out corrupted. My guess is that it is because the file is too complex, but I'm not sure. A very simple excel file works without any problems.

First, PHPExcel reads the file, alters the data and then recreates it right? It's not just opening and writing to the same file?

I'm getting this error message:

Fatal error: Uncaught exception 'PHPExcel_Exception' with message 'Invalid cell coordinate (MAX(MATCH(REPT("Z"' in /Library/WebServer/Documents/sb/Classes/PHPExcel/Cell.php:594 Stack trace: #0 /Library/WebServer/Documents/sb/Classes/PHPExcel/Cell.php(651): PHPExcel_Cell::coordinateFromString('(MAX(MATCH(REPT...') #1 /Library/WebServer/Documents/sb/Classes/PHPExcel/Cell.php(624): PHPExcel_Cell::absoluteCoordinate('(MAX(MATCH(REPT...') #2 /Library/WebServer/Documents/sb/Classes/PHPExcel/Writer/Excel2007/Workbook.php(438): PHPExcel_Cell::absoluteReference('(MAX(MATCH(REPT...') #3 /Library/WebServer/Documents/sb/Classes/PHPExcel/Writer/Excel2007/Workbook.php(282): PHPExcel_Writer_Excel2007_Workbook->writeDefinedNameForPrintArea(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), 0) #4 /Library/WebServer/Documents/sb/Classes/PHPExcel/Writer/Excel2007/Workbook.php(75): PHPExcel_Writer_Excel2007_Workbook->writeDefinedNames(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel)) #5 /Library/WebServer/Doc in /Library/WebServer/Documents/sb/Classes/PHPExcel/Cell.php on line 594

public static function coordinateFromString($pCoordinateString = 'A1')
{
    if (preg_match("/^([$]?[A-Z]{1,3})([$]?\d{1,7})$/", $pCoordinateString, $matches)) {
        return array($matches[1],$matches[2]);
    } elseif ((strpos($pCoordinateString, ':') !== false) || (strpos($pCoordinateString, ',') !== false)) {
        throw new PHPExcel_Exception('Cell coordinate string can not be a range of cells');
    } elseif ($pCoordinateString == '') {
        throw new PHPExcel_Exception('Cell coordinate can not be zero-length string');
    }

    throw new PHPExcel_Exception('Invalid cell coordinate '.$pCoordinateString); // line 594
}

This is the excel file I'm using. Ideas?

My PHP code:

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('America/Los_Angeles');

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

date_default_timezone_set('America/Los_Angeles');

/**
 * PHPExcel
 *
 * Copyright (c) 2006 - 2015 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
 * @version    ##VERSION##, ##DATE##
 */

/** PHPExcel */
require_once dirname(__FILE__) . '/Classes/PHPExcel.php';

//echo date('H:i:s') , " Load from Excel2007 file" , EOL;
$objReader = PHPExcel_IOFactory::createReader("Excel2007");
$objReader->setIncludeCharts(TRUE);
$objPHPExcel = $objReader->load("templates/Cue_Sheet_Template.xlsx");


echo date('H:i:s') , " Update cell data values that are displayed in the chart" , EOL;
$objWorksheet = $objPHPExcel->getActiveSheet();
/*$objWorksheet->fromArray(
    array(
        array(50-12,   50-15,       50-21),
        array(50-56,   50-73,       50-86),
        array(50-52,   50-61,       50-69),
        array(50-30,   50-32,       50),
    ),
    NULL,
    'B2'
);*/

// Save Excel 2007 file
echo date('H:i:s') , " Write to Excel2007 format" , EOL;
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->setIncludeCharts(TRUE);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;


// Echo memory peak usage
echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;

// Echo done
echo date('H:i:s') , " Done writing file" , EOL;
echo 'File has been created in ' , getcwd() , EOL;

Answer 1

EDIT (Original answer below)

I got a successful result. What I did was copy the content of your xlsx template file. Open a new excel sheet and paste the content from your original template. I removed some of the conditional functions that defined some of the fields and now I get a decent looking result with your formatting in place.

Here's the preedited file.

ORIGINAL

Sorry this isn't the answer that you want. PHPExcel is failing to parse the print range.

Your solution should lay in redefining your print area into something that PHPExcel can parse, or writing your own custom parser for PHPExcel using regex (ie preg_split) that doesn't simply split the range values.

You're basically taking an excel sheet, inserting values and trying to output a new excel sheet.

If you comment out

throw new PHPExcel_Exception('Invalid cell coordinate '.$pCoordinateString); // line 594

then you'll get your new excel sheet with the formatting ruined.

The problem is with your print area

A1:A1,0:0,0:0,(MAX(MATCH(REPT("Z":MAX(MATCH(REPT("Z",29):29),B:B,1):1),MATCH(REPT("Z":MATCH(REPT("Z",29):29),C:C,1):1),MATCH(REPT("Z":MATCH(REPT("Z",29):29),L:L,1):1),MATCH(REPT("Z":MATCH(REPT("Z",29):29),M:M,1):1),MATCH(REPT("Z":MATCH(REPT("Z",29):29),N:N,1):1),MATCH(REPT("Z":MATCH(REPT("Z",29):29),O:O,1))):1))),18):18)

which PHPExcel decides to explode by the ':' that's right after the 'Z'.

I tried redefining your print area, but haven't been able to. I'm out of time to try this for now. Hope this gets you off in the right direction.