Data format when pasting

[Chris]

I have set the cell format to text in my spreadsheet, as I am editing numbers that are stored with greater precision than the spreadsheet can handle. This works for reading and writing in the spreadsheet programatically, but when numbers are pasted in they are rounded to 15 decimal places.

How can I make pasted numeric values respect the cell's text formatting ?

Thanks

[Nikolay Demirev]

Hello Chris,

There is an issue with preserving the original number format of the cells in the document when pasting. Here is the public item in the Feedback portal: https://feedback.telerik.com/wpf/1354120. You could use it to follow it and this way you will receive updates about the progress on the matter.

I could suggest you implement custom paste command and replace the original one. Custom one will first paste the data in order to handle the special cases where the data needs some processing and after that a custom code will get the data from the clipboard, then will check if it is number and if it is with more than 15 decimal places and will set it as a text value of the cell. Here is a sample code showing how you can get the value of the clipboard and split it to rows and columns and set it as values of the cells:

if (!Clipboard.ContainsText())
{
    return;
}

string text = Clipboard.GetText();
string[] rows = text.Split('\n');
List<List<string>> values = new List<List<string>>();
foreach (string row in rows)
{
    string trimmedValue = row.Trim();
    List<string> currentRow = new List<string>();
    values.Add(currentRow);

    string[] cells = trimmedValue.Split('\t');
    foreach (string cell in cells)
    {
        string trimmedCell = cell.Trim();
        currentRow.Add(trimmedCell);
    }
}

Workbook workbook = this.radSpreadsheet.Workbook;
Worksheet activeWorksheet = workbook.ActiveWorksheet;
Selection uiSelection = this.radSpreadsheet.ActiveWorksheetEditor.Selection;
CellRange firstSelectedRange = uiSelection.SelectedRanges.First();
workbook.SuspendLayoutUpdate();
activeWorksheet.BeginUndoGroup();

for (int row = 0; row < values.Count; row++)
{
    List<string> cellValues = values[row];
    for (int col = 0; col < cellValues.Count; col++)
    {
        string value = cellValues[col];
        if (string.IsNullOrEmpty(value))
        {
            continue;
        }

        int rowIndex = firstSelectedRange.FromIndex.RowIndex + row;
        int columnIndex = firstSelectedRange.FromIndex.ColumnIndex + col;
        activeWorksheet.Cells[rowIndex, columnIndex].SetValueAsText(cellValues[col]);
    }
}

activeWorksheet.EndUndoGroup();
workbook.ResumeLayoutUpdate();

After that you could replace the Ctrl+V shortcut using the following code:

this.radSpreadsheet.ActiveWorksheetEditor.KeyBindings.RegisterCommand(yourPasteCommandHere, Key.V, ModifierKeys.Control);
this.radSpreadsheet.ActiveWorksheetEditor.KeyBindings.RegisterCommand(yourPasteCommandHere, Key.Insert, ModifierKeys.Shift);

After that you would have to replace the context menu command and the command in the RibbonView. Here you could read how to customize them: https://docs.telerik.com/devtools/wpf/controls/radspreadsheet/getting-started/spreadsheet-ui#customizing-the-template-of-radspreadsheetribbon

Regards,
Nikolay Demirev
Progress Telerik