Recent

Author Topic: Can sheet name contain the characters '.', '-' ?  (Read 4940 times)

Soner

  • Sr. Member
  • ****
  • Posts: 305
Can sheet name contain the characters '.', '-' ?
« on: August 17, 2018, 11:47:44 am »
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"
« Last Edit: August 17, 2018, 12:19:36 pm by Soner »

wp

  • Hero Member
  • *****
  • Posts: 11855
Re: Can sheet name contain the characters '.', '-' ?
« Reply #1 on: August 17, 2018, 12:51:47 pm »
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][+][-]
  1. function TsWorkbook.ValidWorksheetName(var AName: String;
  2.   ReplaceDuplicateName: Boolean = false): Boolean;
  3. // see: http://stackoverflow.com/questions/451452/valid-characters-for-excel-sheet-names
  4. const
  5.   INVALID_CHARS: set of char = ['[', ']', ':', '*', '?', '/', '\'];
  6. var
  7.   ch: char;
  8.   i: Integer;
  9. begin
  10.   Result := false;
  11.   // Name must not be empty
  12.   if (AName = '') then
  13.     exit;
  14.   // Name must not contain any of the INVALID_CHARS
  15.   for ch in AName  do
  16.     if ch in INVALID_CHARS then
  17.       exit;
  18.   ...
  19.  

Soner

  • Sr. Member
  • ****
  • Posts: 305
Re: Can sheet name contain the characters '.', '-' ?
« Reply #2 on: August 17, 2018, 01:20:11 pm »
Thanks for the info.
You don't need implement this for me. I will use only names that  currently accepted by formulas.

wp

  • Hero Member
  • *****
  • Posts: 11855
Re: Can sheet name contain the characters '.', '-' ?
« Reply #3 on: August 17, 2018, 02:11:03 pm »
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.

 

TinyPortal © 2005-2018