Support for Column and Row references?

[mwhiting]

I'm currently using ver 4 and need the ability to add internal names like the following:

Code: Select all

    with XLS.InternalNames.Add do begin
      Name := 'MyColumnNameForColC';
      Definition := '$C:$C';
    end;

I get the following exception message when running this: 'Error in formula $C:$C Invalid name '

I assume I would get the same behavior for the following also:

Code: Select all

    with XLS.InternalNames.Add do begin
      Name := 'MyRowNameForRow88';
      Definition := '$88:$88';
    end;

Does version 5 support this?

Also, does version 5 support using the R1C1 cell references in names?

[larsa]

Hello

Column/Row names are supported. Use: XLS.InternalNames.Add('MyColumnNameForColC','$C:$C');

R1C1 is not supported.

[mwhiting]

Lars, Thank you for the speedy reply!

Please consider adding R1C1 support in a future release.
R1C1 format is essentially how all your code already works so to not support it in the formulas requires conversion to A1 format.

The use main use I had was for a named cell that always referenced the cell above it. I had to use the Indirect function ('INDIRECT("R[-1]C",FALSE)'), but that meant when editing the cell (F2) in Excel it does not highlight the named cell. It will when R1C1 reference is used directly.

Code I used for A1 format conversion:

Code: Select all

//AColIndex must be a zero based index for the column
function ColStrFromIndex(AColIndex: Integer) : string;
begin
  if AColIndex < 26 then
    Result := Chr(AColIndex + Ord('A'))
  else
    Result := Chr((AColIndex div 26) - 1 + Ord('A')) + Chr(AColIndex mod 26 + Ord('A'));
end;

function CellRefInA1Format(
  ARow: Integer; 
  ACol: Integer; 
  AbsRow: Boolean;
  AbsCol: Boolean; 
  ACellDescription : string): string;
begin
  Result := ColStrFromIndex(ACol);

  if AbsCol then
    Result := '$' + Result;
  if AbsRow then
    Result := Result + '$' + ARow
  else
    Result := Result + ARow;

{$IfDef Debug}
  Assert((ARow >= 1) and (ACol >= 0),format('Invalid cell reference created for "%s": %s',[ACellDescription,Result]));
{$EndIf}
end;

[mwhiting]

Using version 50.20.41. Column names are corrupting the xlsx and not allowing it to be opened. Excel's recovery of this is to remove the names and clear cells that referenced. Based on the xml generated, it appears that any sheet specific names will corrupt the xlsx but I haven't tested that out.

Code: Select all

XLS.Names.Add(INPUT_CELL,'INDIRECT("R[-1]C",FALSE)');
XLS.Names.Add('_TotalPremium', '$C:$C', 0);
XLS.Names.Add('_Dwelling_CovA_', '$D:$D', 0);
...

Code above is currently generating:

Code: Select all

<sheets>
<sheet name="Q1 Homeowners" sheetId="1" r:id="rId2" />
<sheet name="Q2 Homeowners" sheetId="2" r:id="rId3" />
</sheets>
<definedNames>
<definedName name="InputCell" >INDIRECT("R[-1]C",FALSE)</definedName>
<definedName name="_TotalPremium" localSheetId="0" >$C1:$C1048576</definedName>
<definedName name="_Dwelling_CovA_" localSheetId="0" >$D1:$D1048576</definedName>
...
</definedNames>

Following how Excel generates the xml, I need the names to generate as follows:

Code: Select all

<definedNames>
<definedName name="InputCell">INDIRECT("R[-1]C",FALSE)</definedName>
<definedName name="_TotalPremium">'Q1 Homeowners'!$C:$C</definedName>
<definedName name="_Dwelling_CovA_">'Q2 Homeowners'!$D:$D</definedName>
...
</definedNames>

Lars, As your earlier post stating that this was supported was the reason why I've spent the time (and money) to upgrade to ver 5, could you please give me an estimate on when this bug will be fixed so I know whether I need to code a work around?

Thanks
-Matt

[mwhiting]

I was incorrect in the xml generation needed. Below is correct and when I alter TCT_DefinedNames.Write to generate this way the xlsx pulls up correctly.

Code: Select all

<definedName name="InputCell" >INDIRECT("R[-1]C",FALSE)</definedName>
<definedName name="_TotalPremium" localSheetId="0" >&apos;Q1 Homeowners&apos;!$C:$C</definedName>
<definedName name="_Dwelling_CovA_" localSheetId="0" >&apos;Q1 Homeowners&apos;!$D:$D</definedName>

The localSheetId is needed to specify the name only applies to one sheet instead of the whole workbook. The generation of the sheetname that will be AWriter.Text needs to either be a separate stored value, so it can be adjusted if sheet are renamed (I am renaming sheets) or the names need to be searched when a sheet rename occurs to replace the names here also. (localSheetId will not always point to the same sheet as the name formula points to, even though in my example they do.).

-Matt

[mwhiting]

Lars, I apologize for being so quick to label this as a bug. Here is what I've found.

Specifying the column format does work if the sheet name is also passed in. The sheet name is correctly adjusted in the name definition when a sheet is renamed. From my code example above, the following correctly generates the definedName tag.

Code: Select all

XLS.Names.Add(INPUT_CELL,'INDIRECT("R[-1]C",FALSE)');
XLS.Names.Add('_TotalPremium', '''' + XLS[0].Name + '''!' + '$C:$C', 0);
XLS.Names.Add('_Dwelling_CovA_', '''' + XLS[0].Name + '''!' + '$D:$D', 0);

Hopefully this thread will help others.

-A more humble Matt