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

Get or set multiple cell values

7 Answers 511 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Robby
Top achievements
Rank 1
Robby asked on 28 Aug 2013, 03:51 PM
I want to get or set multiple cell values.
I have tested with this piece of code:

CellRange cellRange = new CellRange(0, 0, 5, 5);
CellSelection selection2 = worksheet.Cells[cellRange];
var vals = selection2.GetValue();

But the getvalue function does not act as expected.

I need this to cancel some performance issues. I discovered a heavy load when you read and write 100 cell values each second.

7 Answers, 1 is accepted

Sort by
0
Nikolay Demirev
Telerik team
answered on 29 Aug 2013, 11:06 AM
Hi Robby,

RadSpreadsheet stores information about every change you made in order for the Undo/Redo feature to work properly and this is resource consuming. If you write hundreds of values in RadSpreadsheet and you do not need to use undo for them you can disable the History or include all changes in one UndoGroup. This way you will speed up your app. Here you can read about the History feature of RadSpreadsheet.

I hope my answer is helpful. If you have further question do not hesitate to contact us again.

Regards,
Nikolay Demirev
Telerik
TRY TELERIK'S NEWEST PRODUCT - EQATEC APPLICATION ANALYTICS for WPF.
Learn what features your users use (or don't use) in your application. Know your audience. Target it better. Develop wisely.
Sign up for Free application insights >>
0
Robby
Top achievements
Rank 1
answered on 29 Aug 2013, 04:52 PM
Hi Nikolay,
I had already found the disabling of the history. I know from my experience with interop Excel that it if very efficient to read and write arrays from the worksheet. The recalculate of the sheet will be done once in stead of hundredths times.
Good to know is that I will use the component in a parallel way. In my case I will use 20 components that read and write 100 cell values each second.

Regards,
Robby
0
Nikolay Demirev
Telerik team
answered on 30 Aug 2013, 08:42 AM
Hi Robby,

Thank you for getting back to us!

Did disabling the history solve the performance issue you encountered? In case you have any additional feedback or questions do not hesitate to contact us again.

I am looking forward to your reply.
 

Regards,
Nikolay Demirev
Telerik
TRY TELERIK'S NEWEST PRODUCT - EQATEC APPLICATION ANALYTICS for WPF.
Learn what features your users use (or don't use) in your application. Know your audience. Target it better. Develop wisely.
Sign up for Free application insights >>
0
Robby
Top achievements
Rank 1
answered on 30 Aug 2013, 11:36 AM
No,
the load was already too high after disabling the history.

Regards,
Robby
0
Nikolay Demirev
Telerik team
answered on 30 Aug 2013, 01:06 PM
Hello Robby,

We tested RadSpreadsheet by setting and getting 10 000 values in cell range that is 100 x 100 cells. The operations were performed for 619 ms. 

 We used this piece of code to test the performance:
DateTime startTime = DateTime.Now;
 
radSpreadsheet.Workbook.History.IsEnabled = false;
for (int i = 0; i < 100; i++)
{
    for (int j = 0; j < 100; j++)
    {
        radSpreadsheet.ActiveWorksheet.Cells[i, j].SetValue(i + j);
        radSpreadsheet.ActiveWorksheet.Cells[i, j].GetValue().Value.GetResultValueAsString(CellValueFormat.GeneralFormat);
    }
}
radSpreadsheet.Workbook.History.IsEnabled = true;
 
TimeSpan totalTime = DateTime.Now - startTime;
 
MessageBox.Show(totalTime.TotalMilliseconds.ToString());

That said maybe you are setting different kind of data. If so, please open a support ticket and send us a sample project or sample data. This way we will be able to investigate the issue you have encountered on our side.

Thank you in advance for your cooperation!

Regards,
Nikolay Demirev
Telerik
TRY TELERIK'S NEWEST PRODUCT - EQATEC APPLICATION ANALYTICS for WPF.
Learn what features your users use (or don't use) in your application. Know your audience. Target it better. Develop wisely.
Sign up for Free application insights >>
0
Carlos
Top achievements
Rank 1
answered on 11 Sep 2015, 09:18 PM

Hello

I have a problem with your spreadsheet.

Everytime i try to access the cell value i get =a2+b2 instead of the formula value, i've researched how to get the actual formula but i've been unable to do it.

Could you please help me?

Rango = Hoja.Cells[contador, posicion[D]];
                                                Celdas = Rango.GetValue().Value;//.GetResultValueAsString(CellValueFormat.GeneralFormat);                                                
                                                vector_total[D][XY] = Celdas.RawValue;

 

Getting the string value didn't work​

0
Nikolay Demirev
Telerik team
answered on 16 Sep 2015, 08:10 AM
Hi Carlos,

If you want to get the formula expression string like "=1+2" you have to use 
string formulaString = worksheet.Cells[0, 0].GetValue().Value.RawValue;
If you want to get the calculated formula result value as string you have to use:
worksheet.Cells[0, 0].GetValue().Value.GetResultValueAsString(CellValueFormat.GeneralFormat);
If you want to get the formula value as FormulaCellValue object you have to use:
FormulaCellValue formulaValue = worksheet.Cells[0, 0].GetValue().Value as FormulaCellValue;

I hope this helps.

Regards,
Nikolay Demirev
Telerik
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Tags
Spreadsheet
Asked by
Robby
Top achievements
Rank 1
Answers by
Nikolay Demirev
Telerik team
Robby
Top achievements
Rank 1
Carlos
Top achievements
Rank 1
Share this question
or