ERROR: Name contains invalid characters

[Jakc95]

Good morning, I am writing to inform you that since version 4.5.3 (also 4.5.3.1) we have encountered the following problem: Name contains invalid characters.

The code that cause the problem is the following: FileInfo _File = new FileInfo(FullFileName); xlPackage = new ExcelPackage(_File); ExcelWorkbook wb = xlPackage.Workbook;

We noticed that the problem is caused by the cells named with the "\" character (supported by Excel) (example Product\1).

With previous versions like 4.5.2.1 (and olders) there wasn't any problem.

Thank you in advance for your support and I'm looking forward to a reply.

[huii97]

Same here. Kept running into Name contains invalid characters problem when reading the Excel.

[ct1000]

I've run into the same issue. Was pulling my hair out trying to figure out why this was happening. Found this thread and sure enough, I don't have any issue with 4.5.2.1 as well. I'm not sure why I'm having an issue with this particular project. I'm using EPPlus 4.5.3.1 in other projects with no issues. Weird!

[Kizaemon]

Somewhat related, sorry for piggy-backing in this issue, rather than creating a new one.

here are the definitions for Defined Name.

From: [MS-XLSB] - v20180828 (Release August 28, 2018)

From MS Excel help:

Learn about syntax rules for names The following is a list of syntax rules that you need to be aware of when you create and edit names.

• Valid characters The first character of a name must be a letter, an underscore character (_), or a backslash (). Remaining characters in the name can be letters, numbers, periods, and underscore characters. Note You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.

• Cell references disallowed Names cannot be the same as a cell reference, such as Z$100 or R1C1.

• Spaces are not valid Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators; for example, Sales_Tax or First.Quarter.

• Name length A name can contain up to 255 characters.

• Case sensitivity Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you created the name Sales and then create another name called SALES in the same workbook, Excel prompts you to choose a unique name.

MS Excel (e.g. 2010) has a bug which prevents creating a new name starting with a backslash \ and having two characters in length (e.g. \a, \q, \0, ...) According to the help, these names are allowed. MS Excel also allows renaming an existing name to the such a two-character name (prefixed with a backslash).

EPPlus cannot parse such files, and fails in OfficeOpenXml.ExcelNamedRangeCollection.Add(String Name, ExcelRangeBase Range) with System.ArgumentException "Name \a contains invalid characters." The IsValidName function tries to mimic Excel functionality.

I think we can safely remove the && name.Length > 2 check from the IsValidName and GetValidName.

public static bool IsValidName(string name)
{
    if (string.IsNullOrEmpty(name))
    {
        return false;
    }
    var fc = name[0];
    if (!(char.IsLetter(fc) || fc == '_' || (fc == '\\' && name.Length > 2)))
    {
        return false;
    }

    if (name.IndexOfAny(NameInvalidChars, 1) > 0)
    {
        return false;
    }

    if(ExcelCellBase.IsValidAddress(name))
    {
        return false;
    }

    //TODO:Add check for functionnames.
    return true;
}