Re: Invalid Characters in Worksheet Name (International)

Tech-Archive recommends: Fix windows errors by optimizing your registry

Here's the updated code that checks for the wide versions of all invalid
work*** characters:

private string ValidSheetName(string sheetName)
{
string validSheetName = String.Empty;
char [] invalidChars = new char [] {':', '\\', '/', '?', '*', '[', ']'};
const string truncate = "...";
const char replace = '_';
const int maxSheetNameLen = 31;

if (sheetName.Length > maxSheetNameLen)
{
// Character limit on *** names would be exceeded, truncate
validSheetName = sheetName.Substring(0, maxSheetNameLen -
truncate.Length) + truncate;
}
else
{
validSheetName = sheetName;
}

// Some characters are invalid in work*** names in all locales
for (int i = 0; i < invalidChars.Length; i++)
{
validSheetName = validSheetName.Replace(invalidChars[i], replace);
}

try
{
string narrowSheetName =
Microsoft.VisualBasic.Strings.StrConv(validSheetName,
Microsoft.VisualBasic.VbStrConv.Narrow,
System.Threading.Thread.CurrentThread.CurrentCulture.LCID);

// If narrowing changed something, look for the narrowed invalid
characters
if (narrowSheetName.Length > 0 && narrowSheetName != validSheetName)
{
for (int i = 0; i < invalidChars.Length; i++)
{
int invalidIndex = narrowSheetName.IndexOf(invalidChars[i]);

if (invalidIndex >= 0)
{
// Found a wide version of the invalid character,
replace them all
validSheetName =
validSheetName.Replace(validSheetName[invalidIndex], replace);
}
}
}
}
catch
{
// Narrowing doesn't apply to all locales, nothing to do
}

// Check for blank name
if (validSheetName.Trim().Length == 0)
{
validSheetName = replace.ToString();
}

return validSheetName;
}

"kounoike" wrote:

Hi Drew

I don't have C#, so i can't test your code. but your code seems like to
check only path separator(yen charactor in Japanese) wide or not. but
besides yen charactor, Japanese version has a wide charactor to each {':',
'/', '?', '*', '[', ']'} and these wide charactors are also not valid in
*** name in Japanese version.

keizi

"Drew Lettington" <DrewLettington@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:ADA8268E-3EFD-41E6-8D57-A03BB88DBB70@xxxxxxxxxxxxxxxx
Thank you to everyone who replied. For your information, here is the
final
C# code I added to my application. It uses Microsoft.VisualBasic to
access
the VB string conversion code to 'narrow' the wide yen character. This
works
for Japanese and I assume will work for all Asian languages but I can't
test
since I don't have access to those Office versions.

private string ValidSheetName(string sheetName)
{
string validSheetName = String.Empty;
char [] invalidChars = new char [] {':', '\\', '/', '?', '*', '[',
']'};
const string truncate = "...";
const char replace = '_';
const int maxSheetNameLen = 31;

if (sheetName.Length > maxSheetNameLen)
{
// Character limit on *** names would be exceeded, truncate
validSheetName = sheetName.Substring(0, maxSheetNameLen
-truncate.Length) + truncate;
}
else
{
validSheetName = sheetName;
}

// Some characters are invalid in work*** names in all locales
for (int i = 0; i < invalidChars.Length; i++)
{
validSheetName = validSheetName.Replace(invalidChars[i], replace);
}

try
{
string narrowSheetName =
Microsoft.VisualBasic.Strings.StrConv(validSheetName,
Microsoft.VisualBasic.VbStrConv.Narrow,
System.Threading.Thread.CurrentThread.CurrentCulture.LCID);

if (narrowSheetName != validSheetName)
{
// Something was narrowed, check for separator again
for (int i = 0; i < narrowSheetName.Length; i++)
{
if (narrowSheetName[i] == Path.DirectorySeparatorChar)
{
// Found the wide separator, now replace them all
validSheetName =
validSheetName.Replace(validSheetName[i], replace);
break;
}
}
}
}
catch
{
// Narrowing doesn't apply to all locales, nothing to do
}

// Check for blank name
if (validSheetName.Trim().Length == 0)
{
validSheetName = replace.ToString();
}

return validSheetName;
}

"Drew Lettington" wrote:

I have some code that creates a new work*** from a name provided by a
user.
The code checks the user input for invalid characters before creating
the
name, eliminating : @ \ / ? * [ ]. The code worked fine running English
Excel but when I ran using Japanese Excel I had a problem.

In the Japanese environment the user provided a name that contained the
yen
(￥) character. My code checked for \ which is the directory path
separator
in Japanese and displays as the yen symbol. But since the user's name
had
the actual yen character my code failed to create a valid work*** name.

I could modify my code to explicitly check for the yen character and it
would fix the problem. However, yen character is valid in English Excel
so I
don't want to replace it in that environment. I also can't just check
thread
locale or regional settings or something similar as English Excel could
be
running on a Japanese system.

My question then, is it possible to programatically determine at runtime
which characters are invalid in work*** names? For example, is there
an
Excel call that would return a list of invalid characters which I could
then
removed from any proposed work*** names?

- Drew


.

Quantcast