Invalid character found in sheet title

Question

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?

Answer 1

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)

Answer 2

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;
}

Answer 3

You may passing special character at title. Please check the text before passing it it to title.

$sheet2->setTitle($sub_menu_title);