Can sheet name contain the characters '.', '-'  ?
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
Soner · 2018-08-17 09:47

Can sheet name contain the characters '.', '-'  ?
(I mean using this sheet names in formulas.)

When I load Excel workbooks with sheet names like '1.Table-A' that
used in formulas then fpspreadsheet can load and refresh the values
when the referenced cell changed.
But when I try to create formulas with this sheetnames i.e.
"=1.Table-A!A2" or  "='1.Table'-A!A2"  then I get exception:
"Unpected Chrakter Like .". Even with Spready app.

Is this bug or are such table names not allowed in formulas?

I added example worksheet, in list-sheet the columns C,E,F are links
to other sheets.

Edit:
You can also not use sheets that contains '_'-charakter in their names
in formulas. You can't use it as first charakter.

Edit2:
There must be a bug. You can not use names when first char is nummer
in formulas like "1Sheet".
In other Spreedsheet-applications  you can put the name in '' like
"='1Sheet'!A1"

----------------------------------------------------------------------
wp · 2018-08-17 10:51

You are right: escaping characters in sheet names used for other
purposes is not included in the formula parser. Here we have a clear
advantage of the ODS file format which puts references always into
square brackets and greatly facilitates parsing.

I'll try to add it (although this is always hard stuff...)

As for the validity of a sheetname: TsWorkbook has the function
"ValidSheetname", and this rejects the name if it contains at least
one of the following characters (based on a discussion on
stackoverflow):
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
TsWorkbook.ValidWorksheetName(var AName: String; 
ReplaceDuplicateName: Boolean = false): Boolean;// see:
http://stackoverflow.com/questions/451452/valid-characters-for-excel-
sheet-namesconst  INVALID_CHARS: set of char = ['[', ']', ':', '*',
'?', '/', '\'];var  ch: char;  i: Integer;begin  Result := false;  //
Name must not be empty  if (AName = '') then    exit;  // Name must
not contain any of the INVALID_CHARS  for ch in AName  do    if ch in
INVALID_CHARS then      exit;   ...

----------------------------------------------------------------------
Soner · 2018-08-17 11:20

Thanks for the info.
You don't need implement this for me. I will use only names that 
currently accepted by formulas.

----------------------------------------------------------------------
wp · 2018-08-17 12:11

It was not so difficult. ccr-r6607 single-quotes sheet names if they
can be misunderstood by the formula parser. Note that single quotes
still are not allowed  inside a sheet name. Duplicationg inner quotes
like in Pascal, or even Excel, is not supported ATM until somebody
misses it.

Formulas such as "=1.Table-A!A2" or  "='1.Table'-A!A2" or
"='1Sheet'!A1" or "='_Table'!A1" should not be a problem any more.