Invalid character found in sheet title

13
votes

I'm trying to load an Excel file from a certain source with PHPExcel. I have no control over how these Excel files are generated and they need to be opened automatically with PHPExcel without human interaction (re-saving the file, etc).

I'm getting the following error:

Fatal error: Uncaught exception 'Exception' with message 'Invalid character found in sheet title' in C:\path\to\PHPExcel\Worksheet.php on line 418

The error is occurring on the load() line, using the following code to open the file:

$reader = PHPExcel_IOFactory::createReader('Excel5');
$excel = $reader->load($filename_xls);

The sheet title is irrelevant to us, so is it possible to just ignore it? Thus ignoring the error?

2
phpphpexcel
I don't really want to start messing with PHPExcel core files. And I'm guessing if I catch the exception outside of PHPExcel's core files, the file won't load anyway, which I need to happen. - BT643
You need to fix what ever is wrong in the title then. If you catch the exception you can then try and fix the problem, then try and load it again. - DaImTo
You're right, of course. Just catching the exception will not really fix anything. Please ignore my comment. - Álvaro González
What is the worksheet title? - Mark Baker
print out the row causing this error so you can see what's wrong with it - mamdouh alramadan

2 Answers

19
votes

You don't really need to hack the core, just add this in your own code:

// $invalidCharacters = array('*', ':', '/', '\\', '?', '[', ']');
$invalidCharacters = $worksheet->getInvalidCharacters();
$title = str_replace($invalidCharacters, '', $title);

Worksheet.php exposes getInvalidCharacters() public function you can use. or get lazy and use the array() directly (copy&paste from Workbook.php definitions)

14
votes

We've just done this to get it sorted for now. It's probably horribly bad and I wouldn't really advise other people doing it, but hey, it should work for us!

On version 1.7.8 of PHPExcel, in /Classes/PHPExcel/Worksheet.php around line 414 - swap the checkSheetTitle() function for the following:

private static function _checkSheetTitle($pValue)
{
    // Some of the printable ASCII characters are invalid:  * : / \ ? [ ]
    if (str_replace(self::$_invalidCharacters, '', $pValue) !== $pValue) {
        //throw new Exception('Invalid character found in sheet title');

        //Hack to remove bad characters from sheet name instead of throwing an exception
        return str_replace(self::$_invalidCharacters, '', $pValue);
    }

    // Maximum 31 characters allowed for sheet title
    if (PHPExcel_Shared_String::CountCharacters($pValue) > 31) {
        throw new Exception('Maximum 31 characters allowed in sheet title.');
    }

    return $pValue;
}