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?

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, Feb 17 '14 at 15:04
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. — Linda Lawton - DaImTo, Feb 17 '14 at 15:06
You're right, of course. Just catching the exception will not really fix anything. Please ignore my comment. — Álvaro González, Feb 17 '14 at 15:07
print out the row causing this error so you can see what's wrong with it — mamdouh alramadan, Feb 17 '14 at 15:13
For reference, the only characters that should throw this exception are `*` `:` `/` `\\` ``?` `[` or `]`, or if the first or last character of the sheet name is a `'`, or if the sheet name exceeds 31 characters — Mark Baker, Feb 17 '14 at 16:03
@MarkBaker I'm not even sure what it is originally. When I open it in Excel 2010, the sheet is named "Recovered_Sheet1", and there's a popup saying "Renamed invalid sheet name.". I'm aware there's an "issue" with the file if even proper Excel warns about the sheet name... but I was more just wanting to know if PHPExcel can just lose the sheet name and continue like Excel can. — BT643, Feb 17 '14 at 16:05
There's nothing built into PHPExcel at present to handle this, but I'll look at what it might be possible to do in the future — Mark Baker, Feb 17 '14 at 16:06
@MarkBaker The filename contains [] so I have a feeling it may be those in the sheet name too. So there's no way currently for me to open this file at all in PHPExcel? — BT643, Feb 17 '14 at 16:07
Not without editing the PHPExcel code, no there isn't - the code in question is the `_checkSheetCodeName()` method in the `PHPExcel_Worksheet` class in `/Classes/PHPExcel/Worksheet.php` — Mark Baker, Feb 17 '14 at 16:09
@MarkBaker Ok, thanks for your help. I'll see if I can put something hacky in to get around it for now. Thanks for the great library though! — BT643, Feb 17 '14 at 16:21

score 24 · Answer 1 · answered Oct 26 '16 at 01:40

24

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)

answered Oct 26 '16 at 01:40

oori

5,533
1
30
37

This should be the correct answer without hacking the core. – Sithu May 02 '19 at 12:53

score 14 · Accepted Answer · answered Feb 17 '14 at 17:10

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

score 0 · Answer 3 · answered Nov 15 '22 at 11:49

0

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

$sheet2->setTitle($sub_menu_title);

answered Nov 15 '22 at 11:49

Nazmul Haque

720
8
13

Invalid character found in sheet title

3 Answers3