This is a migrated thread and some comments may be shown as answers.

Data format when pasting

1 Answer 23 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Chris
Top achievements
Rank 1
Chris asked on 02 Aug 2019, 10:22 AM

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

1 Answer, 1 is accepted

Sort by
0
Accepted
Nikolay Demirev
Telerik team
answered on 06 Aug 2019, 02:39 PM
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
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
Tags
Spreadsheet
Asked by
Chris
Top achievements
Rank 1
Answers by
Nikolay Demirev
Telerik team
Share this question
or