I was wondering if I would be able to use the spreadsheet to satisfy the following requirements:
1. Data should be shown to the user in a 2D control, with a finite amount of columns and rows
2. The user should be able to add columns or rows to the control to the beginning, middle of the sheet
3. The user should be able to copy data from the control to excel
4. The user should be able to copy data from excel to the control.
5. The user should be able to mark any row or column as a "header" (Just allows for different formatting and input)
6. The user should be able to save this data from the control to a database and retrieve it at a later date, where each cell will be given a unique id.
7. The user should be able to apply steps 1-6 without overwriting the cell id for any existing cell.
From what I've seen the RadSpreadsheet does everything perfectly other than keeping track of properties on the cell, such as a unique id. I see that I can programmatically set the data with a class that implements ICellValue, but as soon as the user starts typing in the cell that data is overridden to become another type of CellValue, such as TextCellValue or NumericCellValue. Is there a way to force each cell to map to a custom ICellValue?
I see there are events for the WorkbookChanging, WorkbookChanged, and CommandExecuting but they don't really tell you what is changing to override the action. Perhaps using the history to keep track of what data was mutated can help to go back and set Id's on the cells which need an Id.
The reason why the Id is so important is because, for good or bad, once persisted other tables can reference that cell ID and make calculations based off the value of the cell. So if I modify the table to add new rows or columns to the table, or modify the contents of the cell, the ids need to be tracked.
1. Data should be shown to the user in a 2D control, with a finite amount of columns and rows
2. The user should be able to add columns or rows to the control to the beginning, middle of the sheet
3. The user should be able to copy data from the control to excel
4. The user should be able to copy data from excel to the control.
5. The user should be able to mark any row or column as a "header" (Just allows for different formatting and input)
6. The user should be able to save this data from the control to a database and retrieve it at a later date, where each cell will be given a unique id.
7. The user should be able to apply steps 1-6 without overwriting the cell id for any existing cell.
From what I've seen the RadSpreadsheet does everything perfectly other than keeping track of properties on the cell, such as a unique id. I see that I can programmatically set the data with a class that implements ICellValue, but as soon as the user starts typing in the cell that data is overridden to become another type of CellValue, such as TextCellValue or NumericCellValue. Is there a way to force each cell to map to a custom ICellValue?
I see there are events for the WorkbookChanging, WorkbookChanged, and CommandExecuting but they don't really tell you what is changing to override the action. Perhaps using the history to keep track of what data was mutated can help to go back and set Id's on the cells which need an Id.
The reason why the Id is so important is because, for good or bad, once persisted other tables can reference that cell ID and make calculations based off the value of the cell. So if I modify the table to add new rows or columns to the table, or modify the contents of the cell, the ids need to be tracked.
1 Answer, 1 is accepted
0
Hi Thomas,
Yes, it seems RadSpreadsheet can cover out of the box all of the scenarios, without assigning unique ID. Setting custom ICellValue was not considered when exposing the API, so this is not reliable extension point for this scenario.
You could have two approaches for achieving the "unique ID" scenario, as I see it:
- Variant 1: Put all data in one workbook with multiple worksheets. When the user edits the data, it will be presented with only one worksheet, all other would be hidden. This way, when rows are added, the formulas in the other worksheets will be automatically updated (in case they use relative cell references). The problem with this approach is that the performance may diminish when there are multiple worksheets present.
A variant of this would be to have one hidden (or locked/protected) worksheet in each workbook, which contains cells with references to all "external cells" (the ones referenced from other workbooks). The references there will be automatically updated when the user edits the data in the main worksheet, and, for example on document closing, you can get the updated references, and preserve them externally for the document if needed.
- Variant 2: Subscribe to the Cells events, e.g. CellRangeInsertedOrRemoved, and manually update the references according to the changes. This could be tricky, though.
Let us know if you need help with any of the approaches.
Regards,
Boby
Progress Telerik
Yes, it seems RadSpreadsheet can cover out of the box all of the scenarios, without assigning unique ID. Setting custom ICellValue was not considered when exposing the API, so this is not reliable extension point for this scenario.
You could have two approaches for achieving the "unique ID" scenario, as I see it:
- Variant 1: Put all data in one workbook with multiple worksheets. When the user edits the data, it will be presented with only one worksheet, all other would be hidden. This way, when rows are added, the formulas in the other worksheets will be automatically updated (in case they use relative cell references). The problem with this approach is that the performance may diminish when there are multiple worksheets present.
A variant of this would be to have one hidden (or locked/protected) worksheet in each workbook, which contains cells with references to all "external cells" (the ones referenced from other workbooks). The references there will be automatically updated when the user edits the data in the main worksheet, and, for example on document closing, you can get the updated references, and preserve them externally for the document if needed.
- Variant 2: Subscribe to the Cells events, e.g. CellRangeInsertedOrRemoved, and manually update the references according to the changes. This could be tricky, though.
Let us know if you need help with any of the approaches.
Regards,
Boby
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.