Insert Defined Name in formula

[Anthony]

Hi,

Please note that I posted this question a few days ago, but it seems to have been deleted, so I am posting it again.

I have modified the WorksheetEditorContextMenu so that a user can right click on a cell and select from the context menu one of the Workbooks currently Defined Names. I need the selected Defined Name to be inserted at the cursor position of the selected cell.

For example if the cell already has =A1+ typed in it and the user right clicks and selects the Defined Name TotalKMs the cell should then read =A1+TotalKMs with the ability for the user to keep tying the rest of the formula.

I am currently achieving what I require by setting the text on the Clipboard and calling radSpreadsheet.CommandDescriptors.Paste.Command.Execute(null).

This is a poor solution because the clipboard should not be used by applications in this manner.

Is there a better way to achieve this?

Thanks
Anthony

[Boryana]

Hello Anthony,

Thank you for writing.

Your previous post on the same topic was by no means deleted. It was transferred to our support ticketing system in order to allow attachments. You will be able to find it in your Telerik account under Your Support Tickets section.

My answer to your first post suggested to use to the Text and CaretIndex properties of the active cell editor to insert a string exactly at the caret position. The code snippet below demonstrates how to achieve this:

private RadWorksheetEditor worksheetEditor;
private NormalWorksheetEditorPresenter worksheetPresenter;

public MainDemoControl()
{
    this.radSpreadsheet.ActiveSheetEditorChanged += this.RadSpreadsheet_ActiveSheetEditorChanged;
}

private void RadSpreadsheet_ActiveSheetEditorChanged(object sender, EventArgs e)
{
    if (this.worksheetEditor != null)
    {
        worksheetEditor.ActivePresenterChanged += WorksheetEditor_ActivePresenterChanged;
    }

    this.worksheetEditor = this.radSpreadsheet.ActiveWorksheetEditor;

    if (this.worksheetEditor != null)
    {
        worksheetEditor.ActivePresenterChanged += WorksheetEditor_ActivePresenterChanged;

    }
}

private void RadMenuItem_Click(object sender, Telerik.Windows.RadRoutedEventArgs e)
{
    if (this.worksheetPresenter != null)
    {
        CellInputUILayer cellInputUILayer = this.worksheetPresenter.UILayers.GetByName("CellInput") as CellInputUILayer;
        if (cellInputUILayer != null)
        {
            ICellEditor cellEditor = cellInputUILayer.ActiveCellEditor;
            string definedName = ((RadMenuItem)sender).Header.ToString();

            cellEditor.Text = cellEditor.Text.Insert(cellEditor.CaretIndex, definedName);
        }
    }
}

I hope this helps. Let me know if you have further questions on the topic.

Regards,
Boryana
Telerik

 

 

[Anthony]

Thanks, that was what I was after.

The only issue was that the caret went back to the start of the editor. To get around this I changed from using the ICellEditor to be able to set the Caret.

if (cellInputUILayer != null)
{
    if (cellInputUILayer.ActiveCellEditor is FormulaBarFunctionEditor)
    {
        FormulaBarFunctionEditor functionEditor = cellInputUILayer.ActiveCellEditor as FormulaBarFunctionEditor;

        int currentCaretIndex = functionEditor.CaretIndex;

        functionEditor.Text = functionEditor.Text.Insert(currentCaretIndex, definedName);

        functionEditor.CaretIndex = currentCaretIndex + definedName.Length;
    }
    else if (cellInputUILayer.ActiveCellEditor is CellEditor)
    {
        CellEditor cellEditor = cellInputUILayer.ActiveCellEditor as CellEditor;

        int currentCaretIndex = cellEditor.CaretIndex;

        cellEditor.Text = cellEditor.Text.Insert(currentCaretIndex, definedName);

        cellEditor.CaretIndex = currentCaretIndex + definedName.Length;
    }
}

Thanks
Anthony

[Anthony]

Hi,

 This code no longer compiles, the CaretIndex is no longer accessible on ICellEditor.

 Was this change intentional? If so how do I get the current caret position?

 Thanks

Anthony

[Anna]

Hi Anthony,

We have put a start of a process for incorporating rich text in RadSpreadsheet for WPF, which is why we replaced the TextBox of the cell editor with RichTextBox. Unlike TextBox, RichTextBox does not expose CaretIndex and we took the decision to remove this property. I would like to offer my apologies for the inconvenience we've caused for you.

We are currently working on an Insert method, which will insert text at the current caret index and move the index after the inserted text. I believe it should cover your scenario. The only difference should be that instead of ICellEditor, you would need to use the ActiveCellEditor as CellEditorBase. The method should be available in the next LIB and it would be great if you could download it and test it.

Of course, if you encounter any issues or have any other concerns, please don't hesitate to let us know.

Regards,
Anna
Telerik

 

 

[Anthony]

Hi,

I have tested this and it works.

Thanks

Anthony